PJC_XLA_ORIG_RAW_REF

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
SOURCE_RAW_DR_ACCOUNT_CCID SOURCE_RAW_CR_ACCOUNT_CCID SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_2 SOURCE_DISTRIBUTION_TYPE APPLICATION_ID LEDGER_ID

Name

SOURCE_RAW_DR_ACCOUNT_CCID

SOURCE_RAW_CR_ACCOUNT_CCID

SOURCE_DISTRIBUTION_ID_NUM_1

SOURCE_DISTRIBUTION_ID_NUM_2

SOURCE_DISTRIBUTION_TYPE

APPLICATION_ID

LEDGER_ID

Query

SQL_Statement
SELECT MAX(DECODE(DECODE(xaca.accounting_class_code , 'DISCOUNT',DECODE(gl.sla_ledger_cash_basis_flag, 'Y', DECODE (xpap.program_code, 'PJC_RAW_COST_CREDIT', 'PJC_RAW_COST_DEBIT', 'PJC_RAW_COST_DEBIT', '') ,xpap.program_code) ,xpap.program_code), 'PJC_RAW_COST_DEBIT',code_combination_id, NULL)) SOURCE_RAW_DR_ACCOUNT_CCID, MAX(DECODE(xpap.program_code, 'PJC_RAW_COST_CREDIT',code_combination_id, NULL)) SOURCE_RAW_CR_ACCOUNT_CCID, xdl.source_distribution_id_num_1, nvl(xdl.source_distribution_id_num_2,-1) source_distribution_id_num_2, xdl.source_distribution_type, xdl.application_id, gl.ledger_id FROM XLA_Distribution_Links xdl, XLA_Ae_Headers aeh , XLA_Ae_Lines ael , XLA_Acct_Class_Assgns xaca , XLA_Assignment_Defns_b xad , XLA_Post_acct_Progs_b xpap , gl_ledgers gl WHERE xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND xdl.ae_header_id = ael.ae_header_id AND aeh.application_id = ael.application_id AND ael.application_id = xdl.application_id AND aeh.balance_type_code = 'A' AND NVL(aeh.multiperiod_flag,'N') = 'N' AND aeh.ledger_id = gl.ledger_id AND ael.accounting_class_code = xaca.accounting_class_code AND xaca.program_code = xad.program_code AND xaca.program_owner_code = xad.program_owner_code AND xad.program_code = xpap.program_code AND xpap.program_owner_code = 'S' AND xaca.assignment_code = xad.assignment_code AND xaca.assignment_owner_code = xad.assignment_owner_code AND xpap.application_id = 10036 AND (xad.ledger_id IS NULL OR xad.ledger_id = gl.ledger_id) AND xad.enabled_flag = 'Y' AND xpap.program_code IN ('PJC_RAW_COST_DEBIT','PJC_RAW_COST_CREDIT') AND (NOT EXISTS (Select 1 from xla_ae_headers h, xla_ae_lines l,xla_distribution_links d where h.ae_header_id = l.ae_header_id and h.application_id = l.application_id and h.event_id = aeh.event_id and h.ledger_id = aeh.ledger_id AND NVL(h.multiperiod_flag,'N') = 'N' and l.overridden_code_combination_id is not null and l.accounting_class_code = ael.accounting_class_code and d.ae_header_id=l.ae_header_id and d.ae_line_num =l.ae_line_num and d.source_distribution_id_num_1= xdl.source_distribution_id_num_1 ) OR ( ael.overridden_code_combination_id is not null AND ael.override_reason is not null AND not exists (SELECT 1 FROM xla_ae_headers aeh1, xla_distribution_links d, xla_ae_lines ael1 WHERE d.ae_header_id = aeh1.ae_header_id AND d.source_distribution_id_num_1= xdl.source_distribution_id_num_1 AND aeh1.ledger_id = aeh.ledger_id AND NVL(aeh1.multiperiod_flag,'N') = 'N' AND d.SOURCE_DISTRIBUTION_type = xdl.SOURCE_DISTRIBUTION_type AND ael1.ae_header_id = d.ae_header_id AND ael1.ae_line_num = d.ae_line_num AND ael1.accounting_class_code = ael.accounting_class_code AND aeh1.event_id = aeh.event_id AND aeh1.ae_header_id > aeh.ae_header_id))) GROUP BY xdl.source_distribution_id_num_1, xdl.source_distribution_id_num_2 , xdl.source_distribution_type , xdl.application_id , gl.ledger_id

SQL_Statement

SELECT MAX(DECODE(DECODE(xaca.accounting_class_code , 'DISCOUNT',DECODE(gl.sla_ledger_cash_basis_flag, 'Y', DECODE (xpap.program_code, 'PJC_RAW_COST_CREDIT', 'PJC_RAW_COST_DEBIT', 'PJC_RAW_COST_DEBIT', '') ,xpap.program_code) ,xpap.program_code), 'PJC_RAW_COST_DEBIT',code_combination_id, NULL)) SOURCE_RAW_DR_ACCOUNT_CCID,

MAX(DECODE(xpap.program_code, 'PJC_RAW_COST_CREDIT',code_combination_id, NULL)) SOURCE_RAW_CR_ACCOUNT_CCID,

xdl.source_distribution_id_num_1,

nvl(xdl.source_distribution_id_num_2,-1) source_distribution_id_num_2,

xdl.source_distribution_type,

xdl.application_id,

gl.ledger_id

FROM XLA_Distribution_Links xdl,

XLA_Ae_Headers aeh ,

XLA_Ae_Lines ael ,

XLA_Acct_Class_Assgns xaca ,

XLA_Assignment_Defns_b xad ,

XLA_Post_acct_Progs_b xpap ,

gl_ledgers gl

WHERE xdl.ae_header_id = aeh.ae_header_id

AND xdl.ae_line_num = ael.ae_line_num

AND xdl.ae_header_id = ael.ae_header_id

AND aeh.application_id = ael.application_id

AND ael.application_id = xdl.application_id

AND aeh.balance_type_code = 'A'

AND NVL(aeh.multiperiod_flag,'N') = 'N'

AND aeh.ledger_id = gl.ledger_id

AND ael.accounting_class_code = xaca.accounting_class_code

AND xaca.program_code = xad.program_code

AND xaca.program_owner_code = xad.program_owner_code

AND xad.program_code = xpap.program_code

AND xpap.program_owner_code = 'S'

AND xaca.assignment_code = xad.assignment_code

AND xaca.assignment_owner_code = xad.assignment_owner_code

AND xpap.application_id = 10036

AND (xad.ledger_id IS NULL

OR xad.ledger_id = gl.ledger_id)

AND xad.enabled_flag = 'Y'

AND xpap.program_code IN ('PJC_RAW_COST_DEBIT','PJC_RAW_COST_CREDIT')

AND (NOT EXISTS

(Select 1

from xla_ae_headers h, xla_ae_lines l,xla_distribution_links d

where h.ae_header_id = l.ae_header_id

and h.application_id = l.application_id

and h.event_id = aeh.event_id

and h.ledger_id = aeh.ledger_id

AND NVL(h.multiperiod_flag,'N') = 'N'

and l.overridden_code_combination_id is not null

and l.accounting_class_code = ael.accounting_class_code

and d.ae_header_id=l.ae_header_id

and d.ae_line_num =l.ae_line_num

and d.source_distribution_id_num_1= xdl.source_distribution_id_num_1 )

OR

( ael.overridden_code_combination_id is not null

AND ael.override_reason is not null

AND not exists (SELECT 1 FROM xla_ae_headers aeh1, xla_distribution_links d, xla_ae_lines ael1

WHERE d.ae_header_id = aeh1.ae_header_id

AND d.source_distribution_id_num_1= xdl.source_distribution_id_num_1

AND aeh1.ledger_id = aeh.ledger_id

AND NVL(aeh1.multiperiod_flag,'N') = 'N'

AND d.SOURCE_DISTRIBUTION_type = xdl.SOURCE_DISTRIBUTION_type

AND ael1.ae_header_id = d.ae_header_id

AND ael1.ae_line_num = d.ae_line_num

AND ael1.accounting_class_code = ael.accounting_class_code

AND aeh1.event_id = aeh.event_id

AND aeh1.ae_header_id > aeh.ae_header_id)))

GROUP BY xdl.source_distribution_id_num_1,

xdl.source_distribution_id_num_2 ,

xdl.source_distribution_type ,

xdl.application_id ,

gl.ledger_id