CST_B_COSTED_INTR_TXN_ONHAND

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, 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, transfer_txn_id ORDER BY snapshot_date ), to_date('4712-12-31', 'yyyy-mm-dd')) eff_to_date, SUM(quantity) 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, transfer_txn_id ORDER BY snapshot_date ) quantity, SUM(cmr_rcv_quantity) 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, transfer_txn_id ORDER BY snapshot_date ) cmr_rcv_quantity, uom_code, expense_transaction_flag, consigned_flag, accounted_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_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, ct.consigned_flag, SUM(nvl(ctx.quantity, -ctl.quantity)) quantity, SUM(crt.cmr_rcv_quantity) cmr_rcv_quantity, MAX(DECODE(ct.accounting_status, 'F', 'F', 'X', 'F', 'N')) accounted_flag FROM cst_transfers ctx, cst_transactions ct, cst_transaction_layers ctl, cst_inv_transactions cit, (SELECT inv_shipping_transaction_id, ship_to_organization_id, primary_uom_code, SUM(-primary_qty) cmr_rcv_quantity FROM cmr_transactions WHERE destination_type_code = 'EXPENSE' AND transaction_type = 'TRANSFER_ORDER_DELIVER_TO_EXP' AND inv_shipping_transaction_id IS NOT NULL AND transfer_order_line_id IS NOT NULL GROUP BY inv_shipping_transaction_id, ship_to_organization_id, primary_uom_code) crt WHERE nvl(ctx.posted_flag, 'Y') NOT IN ( 'N' ) 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_group_id IS NOT NULL AND ctx.transaction_id (+) = ct.transaction_id AND ct.additional_processing_code = 'TRANSFER' AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id 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 Commented for FROM cst_transfer_src_txns Bug 32219003 UNION ALL SELECT source_transaction_id FROM cst_transfer_src_txns) */ AND cit.transfer_transaction_id = crt.inv_shipping_transaction_id (+) AND cit.inventory_org_id = crt.ship_to_organization_id (+) AND cit.transaction_uom_code = crt.primary_uom_code (+) 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.transaction_flow_type, ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.inventory_org_id, ct.expense_transaction_flag, ct.consigned_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) + nvl(crt.cmr_rcv_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,

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

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

SUM(quantity) 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, transfer_txn_id

ORDER BY snapshot_date ) quantity,

SUM(cmr_rcv_quantity) 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, transfer_txn_id

ORDER BY snapshot_date ) cmr_rcv_quantity,

uom_code,

expense_transaction_flag,

consigned_flag,

accounted_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_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,

ct.consigned_flag,

SUM(nvl(ctx.quantity, -ctl.quantity)) quantity,

SUM(crt.cmr_rcv_quantity) cmr_rcv_quantity,

MAX(DECODE(ct.accounting_status, 'F', 'F', 'X', 'F', 'N')) accounted_flag

FROM cst_transfers ctx,

cst_transactions ct,

cst_transaction_layers ctl,

cst_inv_transactions cit,

(SELECT inv_shipping_transaction_id,

ship_to_organization_id,

primary_uom_code,

SUM(-primary_qty) cmr_rcv_quantity

FROM cmr_transactions

WHERE destination_type_code = 'EXPENSE'

AND transaction_type = 'TRANSFER_ORDER_DELIVER_TO_EXP'

AND inv_shipping_transaction_id IS NOT NULL

AND transfer_order_line_id IS NOT NULL

GROUP BY inv_shipping_transaction_id,

ship_to_organization_id,

primary_uom_code) crt

WHERE nvl(ctx.posted_flag, 'Y') NOT IN ( 'N' )

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_group_id IS NOT NULL

AND ctx.transaction_id (+) = ct.transaction_id

AND ct.additional_processing_code = 'TRANSFER'

AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id

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

** Commented for FROM cst_transfer_src_txns

** Bug 32219003 UNION ALL

** SELECT source_transaction_id

** FROM cst_transfer_src_txns) */

AND cit.transfer_transaction_id = crt.inv_shipping_transaction_id (+)

AND cit.inventory_org_id = crt.ship_to_organization_id (+)

AND cit.transaction_uom_code = crt.primary_uom_code (+)

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.transaction_flow_type,

ct.cost_org_id,

ct.cost_book_id,

ct.inventory_item_id,

ct.inventory_org_id,

ct.expense_transaction_flag,

ct.consigned_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) + nvl(crt.cmr_rcv_quantity, 0)) != 0)