CST_COST_SOURCES_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
TRANSACTION_COST_ID TABLE_SOURCE TRANSACTIONS_SOURCE_TYPE TRANSACTION_SOURCE TRANSACTION_NUMBER

Query

SQL_Statement

SELECT TransactionCostsEO.transaction_cost_id, CostSourcesEO.table_source, CASE WHEN CostSourcesEO.table_source = 'ADJUST' THEN 'Adjustment Cost' ELSE 'Incoming Cost' END transactions_Source_type, CASE WHEN CostSourcesEO.cmr_po_distribution_id != -1 THEN 'PO' ELSE 'AP' END transaction_source, CASE WHEN CostSourcesEO.cmr_po_distribution_id != -1 THEN PurchaseOrderDetailsEO.po_number ELSE ApInvoiceDtlsEO.invoice_number END transaction_number FROM cst_transaction_costs TransactionCostsEO, cmr_purchase_order_dtls PurchaseOrderDetailsEO, cmr_ap_invoice_dtls ApInvoiceDtlsEO, ( SELECT adjustment_transaction_id transaction_id, cmr_po_distribution_id, cmr_ap_invoice_dist_id, po_event_date, 'ADJUST' table_source from cst_acq_cost_adj_sources UNION ALL SELECT cst_inv_transaction_id transaction_id , CMR_PO_DISTRIBUTION_ID, cmr_ap_invoice_dist_id, po_event_date, 'INCOMING' table_source from cst_incoming_txn_cost_sources ) CostSourcesEO WHERE (TransactionCostsEO.cst_inv_transaction_id != 0 or TransactionCostsEO.adjustment_transaction_id !=0) AND CostSourcesEO.transaction_id = DECODE(TransactionCostsEO.adjustment_transaction_id,0,TransactionCostsEO.cst_inv_transaction_id,TransactionCostsEO.adjustment_transaction_id) AND (CostSourcesEO.table_source = CASE WHEN TransactionCostsEO.adjustment_transaction_id !=0 THEN 'ADJUST' ELSE 'INCOMING' END) AND PurchaseOrderDetailsEO.cmr_po_distribution_id(+) = CostSourcesEO.cmr_po_distribution_id AND (PurchaseOrderDetailsEO.event_date = CostSourcesEO.po_event_date OR CostSourcesEO.po_event_date IS NULL) AND ApInvoiceDtlsEO.cmr_ap_invoice_dist_id(+) = CostSourcesEO.cmr_ap_invoice_dist_id