CST_XLA_SEED_DATA

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
EVENT_CLASS_CODE EVENT_TYPE_CODE ACCOUNTING_LINE_CODE

Name

EVENT_CLASS_CODE

EVENT_TYPE_CODE

ACCOUNTING_LINE_CODE

Query

SQL_Statement
SELECT DISTINCT te2.event_class_code, te2.event_type_code event_type_code, DECODE (A.accounting_definition_id, 5, 'EXPENSE', 7, 'EXPENSE', A.accounting_line_type) accounting_line_code FROM cst_transaction_events te2, cst_all_txn_types_v t, (SELECT ad.accounting_definition_id, ad.object_version_number, te.event_class_code, te.event_type_code event_type_code, ad.event_type_code ad_event_type_code, vt.code expense_transaction_flag, DECODE (ad.source_table, 'cst_transfer_costs', inv.code, NULL) intercompany_invoice_flag, NVL(ad.overhead_flag, ohd.code) overhead_flag, tt.code transaction_type, ad.line_no, ad.accounting_line_type, DECODE(ad.transaction_type, NULL, DECODE(tt.code, 'ISSUE', DECODE(ad.dr_cr_sign, 'DR', 'CR', 'DR'), ad.dr_cr_sign), ad.dr_cr_sign) dr_cr_sign, ad.reverse_sign_flag, ad.source_table, ad.use_txn_curr_amount, ad.use_txn_account, ad.created_by, ad.creation_date, ad.last_updated_by, ad.last_update_date, ad.last_update_login FROM cst_acct_definitions ad, ( SELECT 'EXPENSE' code_type, 'Y' code FROM dual UNION SELECT 'EXPENSE' code_type, 'N' code FROM dual ) vt, ( SELECT 'TRANS_TYPE' code_type, 'ISSUE' code FROM dual UNION SELECT 'TRANS_TYPE' code_type, 'RECEIPT' code FROM dual ) tt, ( SELECT 'INVOICE' code_type, 'Y' code FROM dual UNION SELECT 'INVOICE' code_type, 'N' code FROM dual ) inv, ( SELECT 'OVERHEAD' code_type, 'N' code FROM dual UNION SELECT 'OVERHEAD' code_type, 'P' code FROM dual ) ohd, ( SELECT DISTINCT event_class_code, event_type_code FROM cst_transaction_events WHERE transaction_flow_type <> -99 ) te WHERE vt.code_type = 'EXPENSE' AND NVL(ad.expense_transaction_flag, vt.code) = vt.code AND tt.code_type = 'TRANS_TYPE' AND NVL(ad.transaction_type, tt.code) = tt.code AND inv.code_type = 'INVOICE' AND DECODE (ad.source_table, 'cst_transfer_costs', NVL(ad.intercompany_invoice_flag, inv.code), 'N') = inv.code AND ohd.code_type = 'OVERHEAD' AND DECODE (ad.source_table, 'cst_transfer_costs', DECODE(ad.overhead_flag, NULL, ohd.code, 'N'), 'N')= ohd.code AND NVL(ad.event_class_code, te.event_class_code) = te.event_class_code AND NVL(ad.event_type_code, te.event_type_code) = te.event_type_code AND NVL(ad.transaction_flow_type, -1) <> -99 ) A WHERE t.base_txn_source_type_id (+) = te2.transaction_source_type_id AND t.base_txn_action_id (+) = te2.transaction_action_id AND te2.event_class_code = A.event_class_code AND te2.event_type_code = A.event_type_code AND NVL(te2.default_cost_txn_type, A.transaction_type) = A.transaction_type AND NVL(te2.cost_transaction_type, A.transaction_type) = A.transaction_type AND NVL(te2.source_table, A.source_table) = A.source_table AND te2.transaction_flow_type <> -99 AND ( NVL(transaction_source_type_id,0), NVL(transaction_action_id,3)) NOT IN (SELECT base_txn_source_type_id, base_txn_action_id FROM cst_txn_source_actions WHERE inactive_flag = 'Y' ) AND NVL(t.user_defined_flag, 'N') = 'N' AND NOT EXISTS (SELECT 1 FROM cst_txn_source_actions sa WHERE sa.intransit_flag = 'N' AND sa.base_txn_action_id = te2.transaction_action_id AND sa.base_txn_source_type_id = te2.transaction_source_type_id AND A.source_table = 'cst_transfer_costs' ) AND EXISTS (SELECT 1 FROM cst_acct_definitions ad1 WHERE ad1.event_class_code = te2.event_class_code AND NVL(ad1.event_type_code,te2.event_type_code) = te2.event_type_code AND ad1.source_table = A.source_table AND NVL(ad1.transaction_flow_type, -1) <> -99 AND DECODE (A.overhead_flag, 'Y', DECODE (A.source_table, 'cst_layer_costs', ad1.accounting_line_type, 'INVENTORY_VALUATION'), 'INVENTORY_VALUATION') = 'INVENTORY_VALUATION' ) ORDER BY event_class_code, event_type_code, accounting_line_code

SQL_Statement

SELECT DISTINCT te2.event_class_code,

te2.event_type_code event_type_code,

DECODE (A.accounting_definition_id, 5, 'EXPENSE', 7, 'EXPENSE', A.accounting_line_type) accounting_line_code

FROM cst_transaction_events te2,

cst_all_txn_types_v t,

(SELECT ad.accounting_definition_id,

ad.object_version_number,

te.event_class_code,

te.event_type_code event_type_code,

ad.event_type_code ad_event_type_code,

vt.code expense_transaction_flag,

DECODE (ad.source_table, 'cst_transfer_costs', inv.code, NULL) intercompany_invoice_flag,

NVL(ad.overhead_flag, ohd.code) overhead_flag,

tt.code transaction_type,

ad.line_no,

ad.accounting_line_type,

DECODE(ad.transaction_type, NULL, DECODE(tt.code, 'ISSUE', DECODE(ad.dr_cr_sign, 'DR', 'CR', 'DR'), ad.dr_cr_sign), ad.dr_cr_sign) dr_cr_sign,

ad.reverse_sign_flag,

ad.source_table,

ad.use_txn_curr_amount,

ad.use_txn_account,

ad.created_by,

ad.creation_date,

ad.last_updated_by,

ad.last_update_date,

ad.last_update_login

FROM cst_acct_definitions ad,

( SELECT 'EXPENSE' code_type, 'Y' code FROM dual

UNION

SELECT 'EXPENSE' code_type, 'N' code FROM dual

) vt,

( SELECT 'TRANS_TYPE' code_type, 'ISSUE' code FROM dual

UNION

SELECT 'TRANS_TYPE' code_type, 'RECEIPT' code FROM dual

) tt,

( SELECT 'INVOICE' code_type, 'Y' code FROM dual

UNION

SELECT 'INVOICE' code_type, 'N' code FROM dual

) inv,

( SELECT 'OVERHEAD' code_type, 'N' code FROM dual

UNION

SELECT 'OVERHEAD' code_type, 'P' code FROM dual

) ohd,

( SELECT DISTINCT event_class_code,

event_type_code

FROM cst_transaction_events

WHERE transaction_flow_type <> -99

) te

WHERE vt.code_type = 'EXPENSE'

AND NVL(ad.expense_transaction_flag, vt.code) = vt.code

AND tt.code_type = 'TRANS_TYPE'

AND NVL(ad.transaction_type, tt.code) = tt.code

AND inv.code_type = 'INVOICE'

AND DECODE (ad.source_table, 'cst_transfer_costs', NVL(ad.intercompany_invoice_flag, inv.code), 'N') = inv.code

AND ohd.code_type = 'OVERHEAD'

AND DECODE (ad.source_table, 'cst_transfer_costs', DECODE(ad.overhead_flag, NULL, ohd.code, 'N'), 'N')= ohd.code

AND NVL(ad.event_class_code, te.event_class_code) = te.event_class_code

AND NVL(ad.event_type_code, te.event_type_code) = te.event_type_code

AND NVL(ad.transaction_flow_type, -1) <> -99

) A

WHERE t.base_txn_source_type_id (+) = te2.transaction_source_type_id

AND t.base_txn_action_id (+) = te2.transaction_action_id

AND te2.event_class_code = A.event_class_code

AND te2.event_type_code = A.event_type_code

AND NVL(te2.default_cost_txn_type, A.transaction_type) = A.transaction_type

AND NVL(te2.cost_transaction_type, A.transaction_type) = A.transaction_type

AND NVL(te2.source_table, A.source_table) = A.source_table

AND te2.transaction_flow_type <> -99

AND ( NVL(transaction_source_type_id,0), NVL(transaction_action_id,3)) NOT IN

(SELECT base_txn_source_type_id,

base_txn_action_id

FROM cst_txn_source_actions

WHERE inactive_flag = 'Y'

)

AND NVL(t.user_defined_flag, 'N') = 'N'

AND NOT EXISTS

(SELECT 1

FROM cst_txn_source_actions sa

WHERE sa.intransit_flag = 'N'

AND sa.base_txn_action_id = te2.transaction_action_id

AND sa.base_txn_source_type_id = te2.transaction_source_type_id

AND A.source_table = 'cst_transfer_costs'

)

AND EXISTS

(SELECT 1

FROM cst_acct_definitions ad1

WHERE ad1.event_class_code = te2.event_class_code

AND NVL(ad1.event_type_code,te2.event_type_code) = te2.event_type_code

AND ad1.source_table = A.source_table

AND NVL(ad1.transaction_flow_type, -1) <> -99

AND DECODE (A.overhead_flag, 'Y', DECODE (A.source_table, 'cst_layer_costs', ad1.accounting_line_type, 'INVENTORY_VALUATION'), 'INVENTORY_VALUATION') = 'INVENTORY_VALUATION'

)

ORDER BY event_class_code, event_type_code, accounting_line_code