CST_XLA_ACQ_COST_ADJ_TXNS_V

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

Query

SQL_Statement

SELECT DISTINCT

ccd.distribution_id distribution_id,

cco.cost_org_name cost_org_name,

ccb.cost_book_code cost_book_code,

esi.item_number item_number,

ct.quantity quantity,

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 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 ct.transaction_id = ccd.transaction_id

AND iuom.uom_code = ct.uom_code

AND ct.cst_work_order_id = cwo.cst_work_order_id (+)