CST_B_COSTED_INTR_ONHAND_V

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, snapshot_date, nvl(LEAD(snapshot_date,1) OVER( PARTITION BY cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, expense_transaction_flag ORDER BY snapshot_date ),TO_DATE('4712-12-31','yyyy-mm-dd') ) eff_to_date, SUM(quantity) OVER( PARTITION BY cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, expense_transaction_flag ORDER BY snapshot_date ) quantity, uom_code, expense_transaction_flag FROM ( SELECT ct.cost_org_id, ct.cost_book_id, ct.inventory_org_id, ct.inventory_item_id, trunc(nvl(ctx.cost_date,nvl(ctl.cost_date,nvl(ct.cost_date,ct.transaction_date) ) ) ) snapshot_date, ct.uom_code, ct.expense_transaction_flag, SUM(nvl(ctx.quantity,-ctl.quantity) ) quantity FROM cst_transfers ctx, cst_transactions ct, cst_transaction_layers ctl WHERE nvl(ctx.posted_flag,'Y') NOT IN ( 'N' ) 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_group_id IS NOT NULL AND ctx.transaction_id (+) = ct.transaction_id AND ct.additional_processing_code = 'TRANSFER' AND ctl.transaction_id (+) = ct.transaction_id AND nvl(ctl.posted_flag,'Y') NOT IN ( 'N', 'X', 'W' ) 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, expense_transaction_flag, trunc(nvl(ctx.cost_date,nvl(ctl.cost_date,nvl(ct.cost_date,ct.transaction_date) ) ) ), ct.uom_code HAVING SUM(nvl(ctx.quantity,-ctl.quantity) ) != 0 )