CST_B_COSTED_INTR_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Query

SQL_Statement
SELECT 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 cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, cost_element_id ORDER BY snapshot_date ), TO_DATE('4712-12-31','yyyy-mm-dd') ) eff_to_date, SUM(onhand_qty) OVER( PARTITION BY cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, cost_element_id ORDER BY snapshot_date ) onhand_qty, SUM(onhand_value) OVER( PARTITION BY cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, cost_element_id ORDER BY snapshot_date ) onhand_value, currency_code, uom_code, cost_method_code FROM ( SELECT ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.inventory_org_id, lc.expense_pool_id, lc.cost_element_id, trunc(lc.cost_date) snapshot_date, SUM(DECODE(lc.cost_transaction_type,'ADJUST',0,lc.quantity) ) onhand_qty, SUM(lc.unit_cost * lc.quantity) onhand_value, 0 unit_cost, lc.currency_code, ct.uom_code, 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, absorption_type, cost_transaction_type FROM cst_wo_osp_costs ) lc, cst_transactions ct WHERE nvl(lc.absorption_type,1) = 1 AND ct.transaction_id = lc.transaction_id AND ct.additional_processing_code = 'TRANSFER' AND ct.transaction_group_id IS NOT NULL AND (ct.base_txn_type_id, ct.base_txn_source_type_id) NOT IN ( (34,0), (74,1), (75,13) ) AND ct.transaction_flow_type IN ( 1, 2, 3, 4, 7, 11, 12, 14, 15, 16, 17 ) AND ct.transaction_id IN ( SELECT dest_transaction_id FROM cst_transfer_src_txns UNION ALL SELECT source_transaction_id FROM cst_transfer_src_txns ) GROUP BY ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.inventory_org_id, lc.expense_pool_id, lc.cost_element_id, trunc(lc.cost_date), lc.currency_code, ct.uom_code, ct.cost_method_code HAVING SUM(lc.unit_cost * lc.quantity) != 0 OR SUM(DECODE(lc.cost_transaction_type,'ADJUST',0,lc.quantity) ) != 0 )

SQL_Statement

SELECT

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 cost_org_id,

cost_book_id,

inventory_item_id,

inventory_org_id,

cost_element_id

ORDER BY

snapshot_date

), TO_DATE('4712-12-31','yyyy-mm-dd') ) eff_to_date,

SUM(onhand_qty) OVER(

PARTITION BY cost_org_id,

cost_book_id,

inventory_item_id,

inventory_org_id,

cost_element_id

ORDER BY

snapshot_date

) onhand_qty,

SUM(onhand_value) OVER(

PARTITION BY cost_org_id,

cost_book_id,

inventory_item_id,

inventory_org_id,

cost_element_id

ORDER BY

snapshot_date

) onhand_value,

currency_code,

uom_code,

cost_method_code

FROM

(

SELECT

ct.cost_org_id,

ct.cost_book_id,

ct.inventory_item_id,

ct.inventory_org_id,

lc.expense_pool_id,

lc.cost_element_id,

trunc(lc.cost_date) snapshot_date,

SUM(DECODE(lc.cost_transaction_type,'ADJUST',0,lc.quantity) ) onhand_qty,

SUM(lc.unit_cost * lc.quantity) onhand_value,

0 unit_cost,

lc.currency_code,

ct.uom_code,

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,

absorption_type,

cost_transaction_type

FROM

cst_wo_osp_costs

) lc,

cst_transactions ct

WHERE

nvl(lc.absorption_type,1) = 1

AND ct.transaction_id = lc.transaction_id

AND ct.additional_processing_code = 'TRANSFER'

AND ct.transaction_group_id IS NOT NULL

AND (ct.base_txn_type_id, ct.base_txn_source_type_id) NOT IN (

(34,0),

(74,1),

(75,13)

)

AND ct.transaction_flow_type IN (

1,

2,

3,

4,

7,

11,

12,

14,

15,

16,

17

)

AND ct.transaction_id IN (

SELECT

dest_transaction_id

FROM

cst_transfer_src_txns

UNION ALL

SELECT

source_transaction_id

FROM

cst_transfer_src_txns

)

GROUP BY

ct.cost_org_id,

ct.cost_book_id,

ct.inventory_item_id,

ct.inventory_org_id,

lc.expense_pool_id,

lc.cost_element_id,

trunc(lc.cost_date),

lc.currency_code,

ct.uom_code,

ct.cost_method_code

HAVING SUM(lc.unit_cost * lc.quantity) != 0

OR SUM(DECODE(lc.cost_transaction_type,'ADJUST',0,lc.quantity) ) != 0

)