PJC_XLA_ADJ_BURD_BURDND_REF

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
ADJ_BURDEN_DR_ACC_CCID ADJ_BURDEN_CR_ACC_CCID ADJ_BURDENED_DR_ACC_CCID ADJ_BURDENED_CR_ACC_CCID SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_2 SOURCE_DISTRIBUTION_TYPE LEDGER_ID

Name

ADJ_BURDEN_DR_ACC_CCID

ADJ_BURDEN_CR_ACC_CCID

ADJ_BURDENED_DR_ACC_CCID

ADJ_BURDENED_CR_ACC_CCID

SOURCE_DISTRIBUTION_ID_NUM_1

SOURCE_DISTRIBUTION_ID_NUM_2

SOURCE_DISTRIBUTION_TYPE

LEDGER_ID

Query

SQL_Statement
SELECT MAX(DECODE(xpap.program_code, 'PJC_BURDEN_COST_DEBIT',code_combination_id, NULL)) ADJ_BURDEN_DR_ACC_CCID, MAX(DECODE(xpap.program_code, 'PJC_BURDEN_COST_CREDIT',code_combination_id, NULL)) ADJ_BURDEN_CR_ACC_CCID , MAX(DECODE(xpap.program_code, 'PJC_BURDENED_COST_DEBIT',code_combination_id, NULL)) ADJ_BURDENED_DR_ACC_CCID , MAX(DECODE(xpap.program_code, 'PJC_BURDENED_COST_CREDIT',code_combination_id, NULL)) ADJ_BURDENED_CR_ACC_CCID , xdl.source_distribution_id_num_1 , xdl.source_distribution_id_num_2 , xdl.source_distribution_type , 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.application_id = 10036 AND 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 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 xdl.application_id = xpap.application_id AND (xad.ledger_id IS NULL OR xad.ledger_id = gl.ledger_id) AND xad.enabled_flag = 'Y' AND xpap.program_code IN ('PJC_BURDENED_COST_DEBIT','PJC_BURDENED_COST_CREDIT' ,'PJC_BURDEN_COST_DEBIT','PJC_BURDEN_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 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 d.SOURCE_DISTRIBUTION_type = xdl.SOURCE_DISTRIBUTION_type AND ael1.ae_header_id =aeh1.ae_header_id 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 , gl.ledger_id

SQL_Statement

SELECT MAX(DECODE(xpap.program_code, 'PJC_BURDEN_COST_DEBIT',code_combination_id, NULL)) ADJ_BURDEN_DR_ACC_CCID,

MAX(DECODE(xpap.program_code, 'PJC_BURDEN_COST_CREDIT',code_combination_id, NULL)) ADJ_BURDEN_CR_ACC_CCID ,

MAX(DECODE(xpap.program_code, 'PJC_BURDENED_COST_DEBIT',code_combination_id, NULL)) ADJ_BURDENED_DR_ACC_CCID ,

MAX(DECODE(xpap.program_code, 'PJC_BURDENED_COST_CREDIT',code_combination_id, NULL)) ADJ_BURDENED_CR_ACC_CCID ,

xdl.source_distribution_id_num_1 ,

xdl.source_distribution_id_num_2 ,

xdl.source_distribution_type ,

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.application_id = 10036

AND 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 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 xdl.application_id = xpap.application_id

AND (xad.ledger_id IS NULL

OR xad.ledger_id = gl.ledger_id)

AND xad.enabled_flag = 'Y'

AND xpap.program_code IN ('PJC_BURDENED_COST_DEBIT','PJC_BURDENED_COST_CREDIT' ,'PJC_BURDEN_COST_DEBIT','PJC_BURDEN_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 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 d.SOURCE_DISTRIBUTION_type = xdl.SOURCE_DISTRIBUTION_type

AND ael1.ae_header_id =aeh1.ae_header_id

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 ,

gl.ledger_id