CST_XLA_PURCHASE_ORDER_TXNS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

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

Name

DISTRIBUTION_ID

COST_ORG_NAME

COST_BOOK_CODE

INV_ORG_CODE

ITEM_NUMBER

PO_NUMBER

QUANTITY

RECEIPT_NUMBER

TRANSACTION_DATE

UOM_CODE

VAL_UNIT_CODE

WORK_ORDER_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 , iop.organization_code inv_org_code , esi.item_number item_number , cpod.po_number po_number , ct.quantity quantity , cit.txn_source_doc_number receipt_number , ct.transaction_date transaction_date , iuom.unit_of_measure uom_code , cvu.val_unit_code val_unit_code , cwo.work_order_number work_order_number FROM cst_transactions ct , cst_inv_transactions cit , cst_cost_orgs_v cco , cst_cost_books_b ccb , cst_val_units_b cvu , egp_system_items_vl esi , inv_org_parameters iop , cmr_purchase_order_dtls cpod , poz_suppliers_v psv , hr_all_organization_units haous , hr_all_organization_units haoud , cst_cost_distributions ccd, inv_units_of_measure_vl iuom, cst_work_orders cwo WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id AND ct.cost_org_id = cco.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 = ct.inventory_org_id AND iop.organization_id = ct.inventory_org_id AND cpod.external_system_ref_id = to_char(cit.po_distribution_id) AND cpod.external_system_reference = decode(cit.external_system_reference, 'RCV_INTERNAL', 'FUSION', cit.external_system_reference) AND cpod.active_flag = 'Y' AND psv.vendor_id (+) = cpod.vendor_id AND haous.organization_id (+) = cpod.sold_to_business_unit_id AND haoud.organization_id (+) = cpod.deliver_to_business_unit_id AND ccd.transaction_id = ct.transaction_id AND iuom.uom_code = ct.uom_code AND ct.cst_work_order_id = cwo.cst_work_order_id(+) AND ccd.event_type_code <> 'INVOICE_PRICE_CLEARING' UNION ALL SELECT DISTINCT ccd.distribution_id distribution_id, cco.cost_org_name cost_org_name, ccb.cost_book_code cost_book_code, iop.organization_code inv_org_code, esi.item_number item_number, cpod.po_number po_number, cpod.quantity_ordered quantity, cit.txn_source_doc_number receipt_number, caot.txn_account_date transaction_date, cpod.uom_code uom_code, NULL val_unit_code, NULL work_order_number FROM cst_acctg_only_txns caot, cst_inv_transactions cit, cst_cost_orgs_v cco, cst_cost_books_b ccb, egp_system_items_vl esi, inv_org_parameters iop, cmr_purchase_order_dtls cpod, poz_suppliers_v psv, hr_all_organization_units haous, hr_all_organization_units haoud, cst_cost_distributions ccd WHERE caot.cost_org_id = cco.cost_org_id AND caot.cost_book_id = ccb.cost_book_id AND esi.inventory_item_id = caot.inventory_item_id AND esi.organization_id = caot.inventory_org_id AND iop.organization_id = caot.inventory_org_id AND cpod.external_system_ref_id = to_char(cit.po_distribution_id) AND cpod.external_system_reference = decode(cit.external_system_reference, 'RCV_INTERNAL', 'FUSION', cit.external_system_reference) AND cpod.active_flag = 'Y' AND psv.vendor_id (+) = cpod.vendor_id AND haous.organization_id (+) = cpod.sold_to_business_unit_id AND haoud.organization_id (+) = cpod.deliver_to_business_unit_id AND ccd.transaction_id = caot.acctg_only_txns_id AND cpod.inventory_item_id = caot.inventory_item_id AND cit.inventory_item_id = caot.inventory_item_id AND cit.inventory_org_id = caot.inventory_org_id AND ccd.cost_organization_id = caot.cost_org_id AND ccd.cost_book_id = caot.cost_book_id AND ccd.event_type_code = 'INVOICE_PRICE_CLEARING' AND ccd.transaction_number = cpod.po_number

SQL_Statement

SELECT DISTINCT

ccd.distribution_id distribution_id,

cco.cost_org_name cost_org_name ,

ccb.cost_book_code cost_book_code ,

iop.organization_code inv_org_code ,

esi.item_number item_number ,

cpod.po_number po_number ,

ct.quantity quantity ,

cit.txn_source_doc_number receipt_number ,

ct.transaction_date transaction_date ,

iuom.unit_of_measure uom_code ,

cvu.val_unit_code val_unit_code ,

cwo.work_order_number work_order_number

FROM cst_transactions ct ,

cst_inv_transactions cit ,

cst_cost_orgs_v cco ,

cst_cost_books_b ccb ,

cst_val_units_b cvu ,

egp_system_items_vl esi ,

inv_org_parameters iop ,

cmr_purchase_order_dtls cpod ,

poz_suppliers_v psv ,

hr_all_organization_units haous ,

hr_all_organization_units haoud ,

cst_cost_distributions ccd,

inv_units_of_measure_vl iuom,

cst_work_orders cwo

WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id

AND ct.cost_org_id = cco.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 = ct.inventory_org_id

AND iop.organization_id = ct.inventory_org_id

AND cpod.external_system_ref_id = to_char(cit.po_distribution_id)

AND cpod.external_system_reference = decode(cit.external_system_reference, 'RCV_INTERNAL', 'FUSION', cit.external_system_reference)

AND cpod.active_flag = 'Y'

AND psv.vendor_id (+) = cpod.vendor_id

AND haous.organization_id (+) = cpod.sold_to_business_unit_id

AND haoud.organization_id (+) = cpod.deliver_to_business_unit_id

AND ccd.transaction_id = ct.transaction_id

AND iuom.uom_code = ct.uom_code

AND ct.cst_work_order_id = cwo.cst_work_order_id(+)

AND ccd.event_type_code <> 'INVOICE_PRICE_CLEARING'

UNION ALL

SELECT DISTINCT

ccd.distribution_id distribution_id,

cco.cost_org_name cost_org_name,

ccb.cost_book_code cost_book_code,

iop.organization_code inv_org_code,

esi.item_number item_number,

cpod.po_number po_number,

cpod.quantity_ordered quantity,

cit.txn_source_doc_number receipt_number,

caot.txn_account_date transaction_date,

cpod.uom_code uom_code,

NULL val_unit_code,

NULL work_order_number

FROM

cst_acctg_only_txns caot,

cst_inv_transactions cit,

cst_cost_orgs_v cco,

cst_cost_books_b ccb,

egp_system_items_vl esi,

inv_org_parameters iop,

cmr_purchase_order_dtls cpod,

poz_suppliers_v psv,

hr_all_organization_units haous,

hr_all_organization_units haoud,

cst_cost_distributions ccd

WHERE

caot.cost_org_id = cco.cost_org_id

AND caot.cost_book_id = ccb.cost_book_id

AND esi.inventory_item_id = caot.inventory_item_id

AND esi.organization_id = caot.inventory_org_id

AND iop.organization_id = caot.inventory_org_id

AND cpod.external_system_ref_id = to_char(cit.po_distribution_id)

AND cpod.external_system_reference = decode(cit.external_system_reference, 'RCV_INTERNAL', 'FUSION', cit.external_system_reference)

AND cpod.active_flag = 'Y'

AND psv.vendor_id (+) = cpod.vendor_id

AND haous.organization_id (+) = cpod.sold_to_business_unit_id

AND haoud.organization_id (+) = cpod.deliver_to_business_unit_id

AND ccd.transaction_id = caot.acctg_only_txns_id

AND cpod.inventory_item_id = caot.inventory_item_id

AND cit.inventory_item_id = caot.inventory_item_id

AND cit.inventory_org_id = caot.inventory_org_id

AND ccd.cost_organization_id = caot.cost_org_id

AND ccd.cost_book_id = caot.cost_book_id

AND ccd.event_type_code = 'INVOICE_PRICE_CLEARING'

AND ccd.transaction_number = cpod.po_number