CST_B_COSTED_INTR_TXN_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Query

SQL_Statement
SELECT txn_source_ref_doc_number, txn_source_ref_doc_type, txn_source_doc_number, txn_source_doc_type, transfer_txn_id, transaction_flow_type, cost_org_id, cost_book_id, inventory_org_id, inventory_item_id, cost_element_id, snapshot_date, nvl(lead(snapshot_date, 1) over( PARTITION BY txn_source_ref_doc_number, txn_source_ref_doc_type, txn_source_doc_number, txn_source_doc_type, cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, expense_transaction_flag, consigned_flag, cost_element_id, transfer_txn_id ORDER BY snapshot_date), to_date('4712-12-31', 'yyyy-mm-dd')) eff_to_date, SUM(onhand_value) over( PARTITION BY txn_source_ref_doc_number, txn_source_ref_doc_type, txn_source_doc_number, txn_source_doc_type, cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, expense_transaction_flag, consigned_flag, cost_element_id, transfer_txn_id ORDER BY snapshot_date) onhand_value, SUM(acctd_onhand_value) over( PARTITION BY txn_source_ref_doc_number, txn_source_ref_doc_type, txn_source_doc_number, txn_source_doc_type, cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, expense_transaction_flag, consigned_flag, cost_element_id, transfer_txn_id ORDER BY snapshot_date) acctd_onhand_value, currency_code, uom_code, cost_method_code, expense_transaction_flag, consigned_flag FROM (SELECT ct.txn_source_ref_doc_number, ct.txn_source_ref_doc_type, CASE WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL ELSE ct.txn_source_doc_number END txn_source_doc_number, CASE WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL ELSE ct.txn_source_doc_type END txn_source_doc_type, CASE WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NULL THEN to_number(cit.external_system_ref_id) WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NOT NULL THEN cit.transfer_transaction_id ELSE to_number(NULL) END transfer_txn_id, min(ct.transaction_flow_type) transaction_flow_type, ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.inventory_org_id, lc.cost_element_id, trunc(lc.cost_date) snapshot_date, SUM(DECODE(ct.intransit_flag, 'Y', 1, -1) * lc.unit_cost * lc.quantity) onhand_value, SUM(decode(ct.accounting_status, 'F', DECODE(ct.intransit_flag, 'Y', 1, -1) * lc.unit_cost * lc.quantity, 0)) acctd_onhand_value, 0 unit_cost, lc.currency_code, ct.uom_code, ct.expense_transaction_flag, ct.consigned_flag, ct.cost_method_code FROM (SELECT transaction_id, cost_date, expense_pool_id, cost_element_id, unit_cost, quantity, currency_code, absorption_type, cost_transaction_type FROM cst_transfer_costs WHERE posted_flag NOT IN ( 'Z' ) UNION ALL SELECT l.transaction_id, l.cost_date, l.expense_pool_id, l.cost_element_id, l.unit_cost, l.quantity, l.currency_code, l.absorption_type, cost_transaction_type FROM cst_layer_costs l UNION ALL SELECT transaction_id, cost_date, expense_pool_id, cost_element_id, unit_cost, quantity, currency_code, absorption_type, cost_transaction_type FROM cst_variance_costs UNION ALL SELECT transaction_id, cost_date, expense_pool_id, cost_element_id, unit_cost, quantity, currency_code, '1' absorption_type, cost_transaction_type FROM cst_writeoff_costs WHERE base_txn_type_id in (45, 47)) lc, cst_transactions ct, cst_inv_transactions cit WHERE nvl(lc.absorption_type, 1) = 1 AND (ct.intransit_flag = 'Y' OR nvl(lc.expense_pool_id, 0) = 0) AND ct.transaction_id = lc.transaction_id AND ct.additional_processing_code = 'TRANSFER' AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id AND ct.transaction_group_id IS NOT NULL AND ( ct.base_txn_source_type_id, ct.base_txn_action_id ) NOT IN ((0, 34), (1, 6), (13, 6)) AND ct.transaction_flow_type IN (4, 7, 13, 14) /* AND ct.transaction_id IN (SELECT dest_transaction_id FROM cst_transfer_src_txns commented for UNION ALL bug 32219003 SELECT source_transaction_id FROM cst_transfer_src_txns) / GROUP BY ct.txn_source_ref_doc_number, ct.txn_source_ref_doc_type, CASE WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL ELSE ct.txn_source_doc_number END, CASE WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL ELSE ct.txn_source_doc_type END, CASE WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NULL THEN to_number(cit.external_system_ref_id) WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NOT NULL THEN cit.transfer_transaction_id ELSE to_number(NULL) END, ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.inventory_org_id, trunc(lc.cost_date), lc.currency_code, ct.uom_code, ct.expense_transaction_flag, ct.consigned_flag, lc.cost_element_id, ct.cost_method_code HAVING SUM(lc.unit_cost lc.quantity) != 0 OR SUM(decode(ct.accounting_status, 'F', lc.unit_cost * lc.quantity, 'X', lc.unit_cost * lc.quantity, 0)) != 0)

SQL_Statement

SELECT txn_source_ref_doc_number,

txn_source_ref_doc_type,

txn_source_doc_number,

txn_source_doc_type,

transfer_txn_id,

transaction_flow_type,

cost_org_id,

cost_book_id,

inventory_org_id,

inventory_item_id,

cost_element_id,

snapshot_date,

nvl(lead(snapshot_date, 1) over(

PARTITION BY txn_source_ref_doc_number, txn_source_ref_doc_type, txn_source_doc_number, txn_source_doc_type, cost_org_id,

cost_book_id, inventory_item_id, inventory_org_id, expense_transaction_flag, consigned_flag, cost_element_id, transfer_txn_id

ORDER BY snapshot_date), to_date('4712-12-31', 'yyyy-mm-dd')) eff_to_date,

SUM(onhand_value) over(

PARTITION BY txn_source_ref_doc_number, txn_source_ref_doc_type, txn_source_doc_number, txn_source_doc_type, cost_org_id, cost_book_id,

inventory_item_id, inventory_org_id, expense_transaction_flag, consigned_flag, cost_element_id, transfer_txn_id

ORDER BY snapshot_date) onhand_value,

SUM(acctd_onhand_value) over(

PARTITION BY txn_source_ref_doc_number, txn_source_ref_doc_type, txn_source_doc_number, txn_source_doc_type, cost_org_id, cost_book_id,

inventory_item_id, inventory_org_id, expense_transaction_flag, consigned_flag, cost_element_id, transfer_txn_id

ORDER BY snapshot_date) acctd_onhand_value,

currency_code,

uom_code,

cost_method_code,

expense_transaction_flag,

consigned_flag

FROM (SELECT ct.txn_source_ref_doc_number,

ct.txn_source_ref_doc_type,

CASE

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL

ELSE ct.txn_source_doc_number

END txn_source_doc_number,

CASE

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL

ELSE ct.txn_source_doc_type

END txn_source_doc_type,

CASE

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NULL THEN to_number(cit.external_system_ref_id)

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NOT NULL THEN cit.transfer_transaction_id

ELSE to_number(NULL)

END transfer_txn_id,

min(ct.transaction_flow_type) transaction_flow_type,

ct.cost_org_id,

ct.cost_book_id,

ct.inventory_item_id,

ct.inventory_org_id,

lc.cost_element_id,

trunc(lc.cost_date) snapshot_date,

SUM(DECODE(ct.intransit_flag, 'Y', 1, -1) * lc.unit_cost * lc.quantity) onhand_value,

SUM(decode(ct.accounting_status, 'F', DECODE(ct.intransit_flag, 'Y', 1, -1) * lc.unit_cost * lc.quantity, 0)) acctd_onhand_value,

0 unit_cost,

lc.currency_code,

ct.uom_code,

ct.expense_transaction_flag,

ct.consigned_flag,

ct.cost_method_code

FROM (SELECT transaction_id,

cost_date,

expense_pool_id,

cost_element_id,

unit_cost,

quantity,

currency_code,

absorption_type,

cost_transaction_type

FROM cst_transfer_costs

WHERE posted_flag NOT IN ( 'Z' )

UNION ALL

SELECT l.transaction_id,

l.cost_date,

l.expense_pool_id,

l.cost_element_id,

l.unit_cost,

l.quantity,

l.currency_code,

l.absorption_type,

cost_transaction_type

FROM cst_layer_costs l

UNION ALL

SELECT transaction_id,

cost_date,

expense_pool_id,

cost_element_id,

unit_cost,

quantity,

currency_code,

absorption_type,

cost_transaction_type

FROM cst_variance_costs

UNION ALL

SELECT transaction_id,

cost_date,

expense_pool_id,

cost_element_id,

unit_cost,

quantity,

currency_code,

'1' absorption_type,

cost_transaction_type

FROM cst_writeoff_costs

WHERE base_txn_type_id in (45, 47)) lc,

cst_transactions ct,

cst_inv_transactions cit

WHERE nvl(lc.absorption_type, 1) = 1

AND (ct.intransit_flag = 'Y' OR nvl(lc.expense_pool_id, 0) = 0)

AND ct.transaction_id = lc.transaction_id

AND ct.additional_processing_code = 'TRANSFER'

AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id

AND ct.transaction_group_id IS NOT NULL

AND ( ct.base_txn_source_type_id, ct.base_txn_action_id ) NOT IN ((0, 34), (1, 6), (13, 6))

AND ct.transaction_flow_type IN (4, 7, 13, 14)

/* AND ct.transaction_id IN (SELECT dest_transaction_id

** FROM cst_transfer_src_txns

** commented for UNION ALL

** bug 32219003 SELECT source_transaction_id

** FROM cst_transfer_src_txns) */

GROUP BY ct.txn_source_ref_doc_number,

ct.txn_source_ref_doc_type,

CASE

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL

ELSE ct.txn_source_doc_number

END,

CASE

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) THEN NULL

ELSE ct.txn_source_doc_type

END,

CASE

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NULL THEN to_number(cit.external_system_ref_id)

WHEN cit.transaction_flow_type IN (4, 7, 13, 14) AND cit.transfer_transaction_id IS NOT NULL THEN cit.transfer_transaction_id

ELSE to_number(NULL)

END,

ct.cost_org_id,

ct.cost_book_id,

ct.inventory_item_id,

ct.inventory_org_id,

trunc(lc.cost_date),

lc.currency_code,

ct.uom_code,

ct.expense_transaction_flag,

ct.consigned_flag,

lc.cost_element_id,

ct.cost_method_code

HAVING SUM(lc.unit_cost * lc.quantity) != 0 OR SUM(decode(ct.accounting_status, 'F', lc.unit_cost * lc.quantity, 'X', lc.unit_cost * lc.quantity, 0)) != 0)