CST_XLA_ACQ_COST_WO_TXNS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
DISTRIBUTION_ID COST_ORG_NAME COST_BOOK_CODE ITEM_NUMBER QUANTITY UOM_CODE TRANSACTION_DATE VAL_UNIT_CODE TRANSACTION_SOURCE TRANSACTION_REFERENCE WORK_ORDER_NUMBER RECEIPT_NUMBER PO_NUMBER

Name

DISTRIBUTION_ID

COST_ORG_NAME

COST_BOOK_CODE

ITEM_NUMBER

QUANTITY

UOM_CODE

TRANSACTION_DATE

VAL_UNIT_CODE

TRANSACTION_SOURCE

TRANSACTION_REFERENCE

WORK_ORDER_NUMBER

RECEIPT_NUMBER

PO_NUMBER

Query

SQL_Statement
SELECT DISTINCT ccd.distribution_id distribution_id, cco.cost_org_name cost_org_name, ccb.cost_book_code cost_book_code, nvl2(ct.po_item_description, ct.po_item_description, esi.item_number) item_number, ct.quantity quantity, nvl2(ct.transaction_currency_code, ct.transaction_currency_code, iuom.unit_of_measure) uom_code, ct.transaction_date transaction_date, cvu.val_unit_code val_unit_code, nvl((SELECT MAX(CASE WHEN cca.adjustment_transaction_id IS NULL AND ctc.base_txn_source_type_id = 0 AND ctc.base_txn_action_id <> 12 THEN 'Invoice Variance Adjustment' WHEN cca.adjustment_transaction_id IS NULL THEN 'Transfer Price Adjustment' WHEN ccas.trade_operation_id <> - 1 THEN 'Landed Cost Adjustment' WHEN ccas.cmr_ap_invoice_dist_id <> - 1 THEN 'AP Invoice Adjustment' WHEN ccas.cmr_po_distribution_id <> - 1 THEN 'Retroactive Price Adjustment' ELSE 'No Reference' END) KEEP(DENSE_RANK FIRST ORDER BY ctc.unit_cost DESC NULLS LAST) FROM cst_transaction_costs ctc, cst_acq_cost_adjustments cca, cst_acq_cost_adj_sources ccas WHERE cca.adjustment_transaction_id(+) = ctc.adjustment_transaction_id AND ccas.adjustment_transaction_id(+) = cca.adjustment_transaction_id AND ctc.transaction_id = ccd.transaction_id AND ctc.eff_date = ccd.eff_date), 'No Reference') transaction_source, 'Reference' transaction_reference, cwo.work_order_number work_order_number, ct.txn_source_doc_number receipt_number, ct.txn_source_ref_doc_number po_number FROM cst_cost_orgs_v cco, cst_cost_books_b ccb, cst_val_units_b cvu, egp_system_items_vl esi, cst_transactions ct, cst_cost_org_parameters ccop, cst_cost_distributions ccd, inv_units_of_measure_vl iuom, cst_work_orders cwo WHERE ct.cost_org_id = cco.cost_org_id AND ccop.cost_org_id = ct.cost_org_id AND ct.cost_book_id = ccb.cost_book_id AND ct.val_unit_id = cvu.val_unit_id (+) AND esi.inventory_item_id = ct.inventory_item_id AND esi.organization_id = ccop.master_organization_id AND iuom.uom_code (+) = ct.uom_code AND ct.cst_work_order_id = cwo.cst_work_order_id (+) AND ct.transaction_id = ccd.transaction_id

SQL_Statement

SELECT DISTINCT

ccd.distribution_id distribution_id,

cco.cost_org_name cost_org_name,

ccb.cost_book_code cost_book_code,

nvl2(ct.po_item_description, ct.po_item_description, esi.item_number) item_number,

ct.quantity quantity,

nvl2(ct.transaction_currency_code, ct.transaction_currency_code, iuom.unit_of_measure) uom_code,

ct.transaction_date transaction_date,

cvu.val_unit_code val_unit_code,

nvl((SELECT MAX(CASE

WHEN cca.adjustment_transaction_id IS NULL AND ctc.base_txn_source_type_id = 0 AND ctc.base_txn_action_id <> 12 THEN 'Invoice Variance Adjustment'

WHEN cca.adjustment_transaction_id IS NULL THEN 'Transfer Price Adjustment'

WHEN ccas.trade_operation_id <> - 1 THEN 'Landed Cost Adjustment'

WHEN ccas.cmr_ap_invoice_dist_id <> - 1 THEN 'AP Invoice Adjustment'

WHEN ccas.cmr_po_distribution_id <> - 1 THEN 'Retroactive Price Adjustment'

ELSE 'No Reference'

END) KEEP(DENSE_RANK FIRST ORDER BY ctc.unit_cost DESC NULLS LAST)

FROM cst_transaction_costs ctc,

cst_acq_cost_adjustments cca,

cst_acq_cost_adj_sources ccas

WHERE cca.adjustment_transaction_id(+) = ctc.adjustment_transaction_id

AND ccas.adjustment_transaction_id(+) = cca.adjustment_transaction_id

AND ctc.transaction_id = ccd.transaction_id

AND ctc.eff_date = ccd.eff_date), 'No Reference') transaction_source,

'Reference' transaction_reference,

cwo.work_order_number work_order_number,

ct.txn_source_doc_number receipt_number,

ct.txn_source_ref_doc_number po_number

FROM

cst_cost_orgs_v cco,

cst_cost_books_b ccb,

cst_val_units_b cvu,

egp_system_items_vl esi,

cst_transactions ct,

cst_cost_org_parameters ccop,

cst_cost_distributions ccd,

inv_units_of_measure_vl iuom,

cst_work_orders cwo

WHERE

ct.cost_org_id = cco.cost_org_id

AND ccop.cost_org_id = ct.cost_org_id

AND ct.cost_book_id = ccb.cost_book_id

AND ct.val_unit_id = cvu.val_unit_id (+)

AND esi.inventory_item_id = ct.inventory_item_id

AND esi.organization_id = ccop.master_organization_id

AND iuom.uom_code (+) = ct.uom_code

AND ct.cst_work_order_id = cwo.cst_work_order_id (+)

AND ct.transaction_id = ccd.transaction_id