VIEW: APPS.GMF_XLA_SO_TXNS_V

Source

View Text - Preformatted

SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, oh.order_number, oh.ordered_date, oh.flow_status_code order_status_code, ol.line_number order_line_number, ol.ordered_quantity, ol.unit_selling_price, ol.flow_status_code order_line_status, mmt.shipment_number shipment_number, hz.party_number customer_number, hz.party_name customer_name FROM gmf_xla_extract_headers eh, oe_order_headers_all oh, oe_order_lines_all ol, mtl_material_transactions mmt , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp, hz_parties hz WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_line_id = mmt.trx_source_line_id AND eh.transaction_source_type_id IN ( 2,8) /* 2-SO 8-Internal Order */ AND eh.transaction_action_id IN (1, 21) /* 1-Issue from stores,21-Intransit Shipment */ AND oh.header_id = ol.header_id AND ol.line_id = mmt.trx_source_line_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id AND oh.sold_to_org_id = hz.party_id(+) UNION /* 2 RMA Transactions, Logical Receipt , RMA Receipt Query 2 & 3 from Pre-processor */ SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, oh.order_number, oh.ordered_date, oh.flow_status_code order_status_code, ol.line_number order_line_number, ol.ordered_quantity, ol.unit_selling_price, ol.flow_status_code order_line_status, mmt.shipment_number shipment_number, hz.party_number customer_number, hz.party_name customer_name FROM gmf_xla_extract_headers eh, rcv_transactions rt, mtl_material_transactions mmt, oe_order_headers_all oh, oe_order_lines_all ol , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp, hz_parties hz WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_document_id = rt.shipment_header_id AND eh.source_line_id = mmt.trx_source_line_id AND eh.transaction_source_type_id = 12 AND eh.transaction_action_id IN ( 1, 26, 27 ) /* 1-Issue from stores, 26-Logical Receipt,27-RMA receipt */ AND rt.transaction_id = mmt.rcv_transaction_id AND rt.oe_order_line_id = ol.line_id AND ol.line_id = mmt.trx_source_line_id AND oh.header_id = ol.header_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id AND oh.sold_to_org_id = hz.party_id(+) UNION /* 3 COGS Recognition transactions For Query 4 from pre-processor */ SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, oh.order_number, oh.ordered_date, oh.flow_status_code order_status_code, ol.line_number order_line_number, ol.ordered_quantity, ol.unit_selling_price, ol.flow_status_code order_line_status, mmt.shipment_number shipment_number, hz.party_number customer_number, hz.party_name customer_name FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt , oe_order_headers_all oh, oe_order_lines_all ol , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp, hz_parties hz WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_document_id = ol.header_id AND eh.source_line_id = mmt.trx_source_line_id AND eh.transaction_source_type_id = 2 /* Sales Order */ AND eh.transaction_action_id = 36 /* COGS Recognition */ AND oh.header_id = ol.header_id AND ol.line_id = mmt.trx_source_line_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id AND oh.sold_to_org_id = hz.party_id(+) UNION /*4 drop shipments trans, SO Issue, Logical Issue, Bill only SO for Query 5 and 6, 7 in Pre-processor */ SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, oh.order_number, oh.ordered_date, oh.flow_status_code order_status_code, ol.line_number order_line_number, ol.ordered_quantity, ol.unit_selling_price, ol.flow_status_code order_line_status, mmt.shipment_number shipment_number, hz.party_number customer_number, hz.party_name customer_name FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt , oe_order_headers_all oh, oe_order_lines_all ol , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp, hz_parties hz WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_document_id = mmt.transaction_source_id AND eh.source_line_id = nvl(mmt.trx_source_line_id,mmt.transaction_id) AND mmt.trx_source_line_id = ol.line_id AND ol.line_id = mmt.trx_source_line_id AND eh.transaction_source_type_id IN (2, 8) /* Sales Order and Internal Orders */ AND eh.transaction_action_id IN (1,7) /* 1-Issue from stores 7-Logical Issue */ AND oh.header_id = ol.header_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id AND oh.sold_to_org_id = hz.party_id(+) UNION /*5 Intransit-Shipment txn to book intransit entries For 8 from Pre-processor */ SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, oh.order_number, oh.ordered_date, oh.flow_status_code order_status_code, ol.line_number order_line_number, ol.ordered_quantity, ol.unit_selling_price, ol.flow_status_code order_line_status, mmt.shipment_number shipment_number, hz.party_number customer_number, hz.party_name customer_name FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt , oe_order_headers_all oh, oe_order_lines_all ol , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp, hz_parties hz WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_line_id = nvl(mmt.trx_source_line_id, mmt.transaction_id) AND mmt.trx_source_line_id = ol.line_id AND eh.transaction_source_type_id = 8 /* Internal Orders */ AND eh.transaction_action_id = 21 /* Intransit shipment */ AND mmt.trx_source_line_id = ol.line_id AND ol.line_id = mmt.trx_source_line_id AND oh.header_id = ol.header_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id AND oh.sold_to_org_id = hz.party_id(+) UNION /* 6 Direct Internal Order Xfer For 9 from Pre-processor */ SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, oh.order_number, oh.ordered_date, oh.flow_status_code order_status_code, ol.line_number order_line_number, ol.ordered_quantity, ol.unit_selling_price, ol.flow_status_code order_line_status, mmt.shipment_number shipment_number, hz.party_number customer_number, hz.party_name customer_name FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt , oe_order_headers_all oh, oe_order_lines_all ol , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp, hz_parties hz WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_document_id = mmt.transaction_source_id AND eh.source_line_id = mmt.trx_source_line_id AND mmt.trx_source_line_id = ol.line_id AND eh.transaction_source_type_id = 8 /* Internal Orders */ AND eh.transaction_action_id = 3 /* Direct Xfer */ AND oh.header_id = ol.header_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id AND oh.sold_to_org_id = hz.party_id(+) UNION /* 7 Logical Intransit Receipt. Transaction Action Id 15, For 10 from Pre-processor */ SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, oh.order_number, oh.ordered_date, oh.flow_status_code order_status_code, ol.line_number order_line_number, ol.ordered_quantity, ol.unit_selling_price, ol.flow_status_code order_line_status, mmt.shipment_number shipment_number, hz.party_number customer_number, hz.party_name customer_name FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt , oe_order_headers_all oh, oe_order_lines_all ol , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp, hz_parties hz WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_document_id = mmt.transaction_id AND mmt.trx_source_line_id = ol.line_id AND eh.transaction_source_type_id = 7 /* Internal requisition */ AND eh.transaction_action_id = 15 /* Logical Intransit Receipt */ AND oh.header_id = ol.header_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id AND oh.sold_to_org_id = hz.party_id(+) UNION /*8 Direct org transafer, Intransit shipment, for Query 1 ,2 and 3 in preprocesor */ SELECT eh.reference_no, eh.ledger_id, eh.transaction_id, eh.event_class_code, eh.operating_unit, eh.transaction_date, eh.transaction_quantity ||' '|| msi.primary_uom_code primary_quantity, msi.primary_uom_code, msi.secondary_uom_code, eh.transaction_uom, eh.valuation_cost_type_id , eh.valuation_cost_type , eh.lot_number, eh.source_document_id, eh.source_line_id, eh.legal_entity_id, le.name legal_entity_name, eh.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, eh.organization_id, mp.organization_code, -1 order_number, eh.transaction_date ordered_date, '' order_status_code, -1 order_line_number, -1 ordered_quantity, -1 unit_selling_price, '' order_line_status, mmt.shipment_number, '' customer_number, '' customer_name FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt , mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp WHERE eh.inventory_item_id = msi.inventory_item_id AND eh.organization_id = msi.organization_id AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = mmt.transaction_id AND eh.source_document_id = mmt.transaction_id AND eh.source_line_id = nvl(mmt.source_line_id, mmt.transaction_id) AND eh.transaction_source_type_id = 13 /* Inventory */ AND eh.transaction_action_id IN (3,21,15) /* 3-Direct organization transfer, 21-Intransit shipment,15-Logical Intransit Receipt */ AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id

View Text - HTML Formatted

SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS_CODE
, OL.LINE_NUMBER ORDER_LINE_NUMBER
, OL.ORDERED_QUANTITY
, OL.UNIT_SELLING_PRICE
, OL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_NAME CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
, HZ_PARTIES HZ
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID IN ( 2
, 8) /* 2-SO 8-INTERNAL ORDER */
AND EH.TRANSACTION_ACTION_ID IN (1
, 21) /* 1-ISSUE
FROM STORES
, 21-INTRANSIT SHIPMENT */
AND OH.HEADER_ID = OL.HEADER_ID
AND OL.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND OH.SOLD_TO_ORG_ID = HZ.PARTY_ID(+) UNION /* 2 RMA TRANSACTIONS
, LOGICAL RECEIPT
, RMA RECEIPT QUERY 2 & 3
FROM PRE-PROCESSOR */ SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS_CODE
, OL.LINE_NUMBER ORDER_LINE_NUMBER
, OL.ORDERED_QUANTITY
, OL.UNIT_SELLING_PRICE
, OL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_NAME CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, RCV_TRANSACTIONS RT
, MTL_MATERIAL_TRANSACTIONS MMT
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
, HZ_PARTIES HZ
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_DOCUMENT_ID = RT.SHIPMENT_HEADER_ID
AND EH.SOURCE_LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 12
AND EH.TRANSACTION_ACTION_ID IN ( 1
, 26
, 27 ) /* 1-ISSUE
FROM STORES
, 26-LOGICAL RECEIPT
, 27-RMA RECEIPT */
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
AND RT.OE_ORDER_LINE_ID = OL.LINE_ID
AND OL.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND OH.HEADER_ID = OL.HEADER_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND OH.SOLD_TO_ORG_ID = HZ.PARTY_ID(+) UNION /* 3 COGS RECOGNITION TRANSACTIONS FOR QUERY 4
FROM PRE-PROCESSOR */ SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS_CODE
, OL.LINE_NUMBER ORDER_LINE_NUMBER
, OL.ORDERED_QUANTITY
, OL.UNIT_SELLING_PRICE
, OL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_NAME CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
, HZ_PARTIES HZ
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_DOCUMENT_ID = OL.HEADER_ID
AND EH.SOURCE_LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 2 /* SALES ORDER */
AND EH.TRANSACTION_ACTION_ID = 36 /* COGS RECOGNITION */
AND OH.HEADER_ID = OL.HEADER_ID
AND OL.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND OH.SOLD_TO_ORG_ID = HZ.PARTY_ID(+) UNION /*4 DROP SHIPMENTS TRANS
, SO ISSUE
, LOGICAL ISSUE
, BILL ONLY SO FOR QUERY 5
AND 6
, 7 IN PRE-PROCESSOR */ SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS_CODE
, OL.LINE_NUMBER ORDER_LINE_NUMBER
, OL.ORDERED_QUANTITY
, OL.UNIT_SELLING_PRICE
, OL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_NAME CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
, HZ_PARTIES HZ
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_DOCUMENT_ID = MMT.TRANSACTION_SOURCE_ID
AND EH.SOURCE_LINE_ID = NVL(MMT.TRX_SOURCE_LINE_ID
, MMT.TRANSACTION_ID)
AND MMT.TRX_SOURCE_LINE_ID = OL.LINE_ID
AND OL.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID IN (2
, 8) /* SALES ORDER
AND INTERNAL ORDERS */
AND EH.TRANSACTION_ACTION_ID IN (1
, 7) /* 1-ISSUE
FROM STORES 7-LOGICAL ISSUE */
AND OH.HEADER_ID = OL.HEADER_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND OH.SOLD_TO_ORG_ID = HZ.PARTY_ID(+) UNION /*5 INTRANSIT-SHIPMENT TXN TO BOOK INTRANSIT ENTRIES FOR 8
FROM PRE-PROCESSOR */ SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS_CODE
, OL.LINE_NUMBER ORDER_LINE_NUMBER
, OL.ORDERED_QUANTITY
, OL.UNIT_SELLING_PRICE
, OL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_NAME CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
, HZ_PARTIES HZ
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_LINE_ID = NVL(MMT.TRX_SOURCE_LINE_ID
, MMT.TRANSACTION_ID)
AND MMT.TRX_SOURCE_LINE_ID = OL.LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 8 /* INTERNAL ORDERS */
AND EH.TRANSACTION_ACTION_ID = 21 /* INTRANSIT SHIPMENT */
AND MMT.TRX_SOURCE_LINE_ID = OL.LINE_ID
AND OL.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND OH.HEADER_ID = OL.HEADER_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND OH.SOLD_TO_ORG_ID = HZ.PARTY_ID(+) UNION /* 6 DIRECT INTERNAL ORDER XFER FOR 9
FROM PRE-PROCESSOR */ SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS_CODE
, OL.LINE_NUMBER ORDER_LINE_NUMBER
, OL.ORDERED_QUANTITY
, OL.UNIT_SELLING_PRICE
, OL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_NAME CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
, HZ_PARTIES HZ
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_DOCUMENT_ID = MMT.TRANSACTION_SOURCE_ID
AND EH.SOURCE_LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND MMT.TRX_SOURCE_LINE_ID = OL.LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 8 /* INTERNAL ORDERS */
AND EH.TRANSACTION_ACTION_ID = 3 /* DIRECT XFER */
AND OH.HEADER_ID = OL.HEADER_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND OH.SOLD_TO_ORG_ID = HZ.PARTY_ID(+) UNION /* 7 LOGICAL INTRANSIT RECEIPT. TRANSACTION ACTION ID 15
, FOR 10
FROM PRE-PROCESSOR */ SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS_CODE
, OL.LINE_NUMBER ORDER_LINE_NUMBER
, OL.ORDERED_QUANTITY
, OL.UNIT_SELLING_PRICE
, OL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER SHIPMENT_NUMBER
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_NAME CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
, HZ_PARTIES HZ
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_DOCUMENT_ID = MMT.TRANSACTION_ID
AND MMT.TRX_SOURCE_LINE_ID = OL.LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 7 /* INTERNAL REQUISITION */
AND EH.TRANSACTION_ACTION_ID = 15 /* LOGICAL INTRANSIT RECEIPT */
AND OH.HEADER_ID = OL.HEADER_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND OH.SOLD_TO_ORG_ID = HZ.PARTY_ID(+) UNION /*8 DIRECT ORG TRANSAFER
, INTRANSIT SHIPMENT
, FOR QUERY 1
, 2
AND 3 IN PREPROCESOR */ SELECT EH.REFERENCE_NO
, EH.LEDGER_ID
, EH.TRANSACTION_ID
, EH.EVENT_CLASS_CODE
, EH.OPERATING_UNIT
, EH.TRANSACTION_DATE
, EH.TRANSACTION_QUANTITY ||' '|| MSI.PRIMARY_UOM_CODE PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, EH.TRANSACTION_UOM
, EH.VALUATION_COST_TYPE_ID
, EH.VALUATION_COST_TYPE
, EH.LOT_NUMBER
, EH.SOURCE_DOCUMENT_ID
, EH.SOURCE_LINE_ID
, EH.LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, EH.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, EH.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, -1 ORDER_NUMBER
, EH.TRANSACTION_DATE ORDERED_DATE
, '' ORDER_STATUS_CODE
, -1 ORDER_LINE_NUMBER
, -1 ORDERED_QUANTITY
, -1 UNIT_SELLING_PRICE
, '' ORDER_LINE_STATUS
, MMT.SHIPMENT_NUMBER
, '' CUSTOMER_NUMBER
, '' CUSTOMER_NAME
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
WHERE EH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_DOCUMENT_ID = MMT.TRANSACTION_ID
AND EH.SOURCE_LINE_ID = NVL(MMT.SOURCE_LINE_ID
, MMT.TRANSACTION_ID)
AND EH.TRANSACTION_SOURCE_TYPE_ID = 13 /* INVENTORY */
AND EH.TRANSACTION_ACTION_ID IN (3
, 21
, 15) /* 3-DIRECT ORGANIZATION TRANSFER
, 21-INTRANSIT SHIPMENT
, 15-LOGICAL INTRANSIT RECEIPT */
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID