CST_XLA_SALES_ORDER_TXNS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
DISTRIBUTION_ID COST_BOOK_CODE COST_ORG_NAME CUSTOMER_NAME DOO_ORDER_NUMBER INV_ORG_CODE ITEM_NUMBER QUANTITY TRANSACTION_DATE UOM_CODE VAL_UNIT_CODE

Name

DISTRIBUTION_ID

COST_BOOK_CODE

COST_ORG_NAME

CUSTOMER_NAME

DOO_ORDER_NUMBER

INV_ORG_CODE

ITEM_NUMBER

QUANTITY

TRANSACTION_DATE

UOM_CODE

VAL_UNIT_CODE

Query

SQL_Statement
SELECT DISTINCT ccd.distribution_id distribution_id, ccb.cost_book_code cost_book_code, cco.cost_org_name cost_org_name, hp.party_name customer_name, csod.doo_order_number doo_order_number, iop.organization_code inv_org_code, esi.item_number item_number, ct.quantity quantity, ct.transaction_date transaction_date, iuom.unit_of_measure uom_code, cvu.val_unit_code val_unit_code 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, cst_sales_order_details csod, hr_all_organization_units haouse, hz_parties hp, cst_cost_distributions ccd, inv_units_of_measure_vl iuom 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 csod.external_system_ref_id = case when nvl(cit.sales_order_source_system, cit.external_system_reference) = 'NONFUSION' and cit.base_txn_source_type_id= 2 then cit.source_sales_order_number\|\|'^'\|\|cit.source_sales_order_line_number\|\|'^'\|\|cit.source_so_shipment_number when nvl(cit.sales_order_source_system, cit.external_system_reference) = 'NONFUSION' and cit.base_txn_source_type_id=12 then cit.source_sales_order_number\|\|'^'\|\|cit.source_sales_order_line_number else to_char(cit.doo_fullfill_line_id) end AND csod.external_system_reference = nvl(cit.sales_order_source_system, cit.external_system_reference) AND hp.party_id(+) = csod.customer_id AND haouse.organization_id(+) = csod.selling_org_id AND ct.transaction_id = ccd.transaction_id AND iuom.uom_code = ct.uom_code UNION ALL SELECT DISTINCT ccd.distribution_id distribution_id, ccb.cost_book_code cost_book_code, cco.cost_org_name cost_org_name, hp.party_name customer_name, csod.doo_order_number doo_order_number, iop.organization_code inv_org_code, esi.item_number item_number, ct.quantity quantity, ct.transaction_date transaction_date, iuom.unit_of_measure uom_code, cvu.val_unit_code val_unit_code FROM cst_cogs_transactions cct, 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, cst_sales_order_details csod, hr_all_organization_units haouse, hz_parties hp, cst_cost_distributions ccd, inv_units_of_measure_vl iuom WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id AND cct.cst_transaction_id = ct.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 csod.external_system_ref_id = to_char(cit.doo_fullfill_line_id) AND csod.external_system_reference = cit.external_system_reference AND hp.party_id(+) = csod.customer_id AND haouse.organization_id(+) = csod.selling_org_id AND cct.transaction_id = ccd.transaction_id AND iuom.uom_code = ct.uom_code

SQL_Statement

SELECT DISTINCT

ccd.distribution_id distribution_id,

ccb.cost_book_code cost_book_code,

cco.cost_org_name cost_org_name,

hp.party_name customer_name,

csod.doo_order_number doo_order_number,

iop.organization_code inv_org_code,

esi.item_number item_number,

ct.quantity quantity,

ct.transaction_date transaction_date,

iuom.unit_of_measure uom_code,

cvu.val_unit_code val_unit_code

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,

cst_sales_order_details csod,

hr_all_organization_units haouse,

hz_parties hp,

cst_cost_distributions ccd,

inv_units_of_measure_vl iuom

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 csod.external_system_ref_id = case when nvl(cit.sales_order_source_system, cit.external_system_reference) = 'NONFUSION' and cit.base_txn_source_type_id= 2

then cit.source_sales_order_number||'^'||cit.source_sales_order_line_number||'^'||cit.source_so_shipment_number

when nvl(cit.sales_order_source_system, cit.external_system_reference) = 'NONFUSION' and cit.base_txn_source_type_id=12 then cit.source_sales_order_number||'^'||cit.source_sales_order_line_number

else to_char(cit.doo_fullfill_line_id) end

AND csod.external_system_reference = nvl(cit.sales_order_source_system, cit.external_system_reference)

AND hp.party_id(+) = csod.customer_id

AND haouse.organization_id(+) = csod.selling_org_id

AND ct.transaction_id = ccd.transaction_id

AND iuom.uom_code = ct.uom_code

UNION ALL