VIEW: APPS.GMF_XLA_RCV_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, poh.segment1 purchase_number, pv.vendor_name supplier_name, rsh.receipt_num receipt_number, rsl.line_num receipt_line_number, rt.subinventory subinventory FROM gmf_xla_extract_headers eh, rcv_transactions rt , gmf_rcv_accounting_txns grat, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_headers_all poh, po_vendors pv, mtl_system_items_b_kfv msi, xle_entity_profiles le, mtl_parameters mp WHERE nvl(eh.inventory_item_id,0) = msi.inventory_item_id (+) /* B7567558 - Added outer join to msi */ AND eh.organization_id = nvl(msi.organization_id ,eh.organization_id) /* B7567558 - Added NVL*/ AND eh.organization_id = mp.organization_id AND eh.legal_entity_id = le.legal_entity_id AND eh.transaction_id = grat.accounting_txn_id AND eh.source_document_id = rt.shipment_header_id AND eh.source_line_id = rt.transaction_id AND rt.transaction_id = grat.rcv_transaction_id AND rt.source_document_code IN ('PO', 'REQ') AND grat.event_type IN (1,3,6,9,10,7,8,2,5 ) AND rt.shipment_header_id = rsh.shipment_header_id AND rsh.receipt_source_code IN ('VENDOR', 'INTERNAL ORDER') AND rt.shipment_line_id = rsl.shipment_line_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rt.po_header_id = poh.po_header_id (+) AND poh.vendor_id = pv.vendor_id UNION ALL /* 2 Purchasing and Internal requistion from MMT, for Query 4 in gmfxpget.gmfxpurget_rcv_transactions */ 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, poh.segment1 purchase_number, pv.vendor_name supplier_name, rsh.receipt_num receipt_number, rsl.line_num receipt_line_number, mmt.subinventory_code subinventory FROM gmf_xla_extract_headers eh, rcv_transactions rt , mtl_material_transactions mmt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_headers_all poh, po_vendors pv, 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 = rt.shipment_header_id AND eh.source_line_id = rt.transaction_id AND rt.transaction_id = mmt.rcv_transaction_id AND eh.transaction_source_type_id IN (1, 7) /* Purchasing and Internal Req */ AND eh.transaction_action_id IN ( 1 /* Issue from stores */ , 7 /* Logical Issue */ , 11 /* Logical PO Receipt Adjustment,Logical Delivery Adjustments*/ , 12 /* Intransit receipt */ , 26 /* Logical Receipt */ , 27 /* Receipt into stores */ , 29 /* Delivery adjustments */ ) AND rt.shipment_header_id = rsh.shipment_header_id AND rsh.receipt_source_code IN ('VENDOR', 'INTERNAL ORDER') AND rt.source_document_code IN ('PO', 'REQ') AND rt.shipment_line_id = rsl.shipment_line_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rt.po_header_id = poh.po_header_id (+) AND poh.vendor_id = pv.vendor_id UNION ALL /* 3 Internal requistion direct transfers, for Query 5 in gmfxpget.gmfxpurget_rcv_transactions */ 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, NVL(mmt.shipment_number, ' ') purchase_number, '' supplier_name, '' receipt_number, -1 receipt_line_number, mmt.subinventory_code subinventory 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 = NVL(mmt.transaction_source_id, mmt.transaction_id) AND eh.source_line_id = NVL(mmt.transaction_id, mmt.source_line_id) AND eh.transaction_source_type_id IN (7,13) /* 7 Internal Requistion, 13-inventory */ AND eh.transaction_action_id IN (3,22,17) /* 3-Direct organization transfer, 22-logical intransit shipment , 17 Logical Expense Requisition Receipt */ AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id UNION ALL /*5 Intransit Xfer-fob receiving-to book instransit entries for shipping org, for Query 7 in gmfxpget.gmfxpurget_rcv_transactions */ 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, NVL(mmt.shipment_number, '') purchase_number, pv.vendor_name supplier_name, rsh.receipt_num receipt_number, rsl.line_num receipt_line_number, mmt.subinventory_code subinventory FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt, rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_vendors pv, 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_source_id AND eh.source_line_id = mmt.source_line_id AND eh.transaction_source_type_id = 7 /* Internal Requistion */ AND eh.transaction_action_id = 12 /* Intransit receipt */ AND rt.source_document_code IN ('PO', 'REQ') AND rsh.receipt_source_code = 'INTERNAL ORDER' AND rt.transaction_id = mmt.rcv_transaction_id AND rt.shipment_header_id = rsh.shipment_header_id AND rt.shipment_line_id = rsl.shipment_line_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rsh.vendor_id = pv.vendor_id UNION ALL /* 6 Consigned Inventory for Query 8 in gmfxpget.gmfxpurget_rcv_transactions */ 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, NVL(poh.segment1,mmt.shipment_number) purchase_number, pv.vendor_name supplier_name, '' receipt_number, -1 receipt_line_number, mmt.subinventory_code subinventory FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt, po_headers_all poh, po_vendors pv, 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 = mmt.transaction_id AND eh.transaction_source_type_id IN (1,13) /* 1- Purchase order, 13 -Inventory */ AND eh.transaction_action_id = 6 /* Ownership Transfer */ AND mmt.owning_tp_type = 2 AND mmt.transaction_source_id = poh.po_header_id AND poh.vendor_id = pv.vendor_id AND eh.organization_id = mmt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id UNION ALL /*8 Inter organization Transfers , gmfxpurget_unposted_intorg_xfers, query 1 */ /* intransit receipt. action id = 12 */ 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, NVL(mmt.shipment_number, '') purchase_number, pv.vendor_name supplier_name, rsh.receipt_num receipt_number, rsl.line_num receipt_line_number, mmt.subinventory_code subinventory FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt, rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_vendors pv, 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 = NVL(mmt.transaction_source_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 = 12 /* Intransit receipt */ AND rt.transaction_id = mmt.rcv_transaction_id AND rt.shipment_header_id = rsh.shipment_header_id AND rsh.receipt_source_code = 'INVENTORY' AND rt.shipment_line_id = rsl.shipment_line_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rsh.vendor_id = pv.vendor_id AND eh.organization_id = rt.organization_id AND eh.inventory_item_id = mmt.inventory_item_id UNION ALL /*10 Intransit Xfer - fob receiving - to book instransit entries for shipping org */ 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, NVL(mmt.shipment_number, '') purchase_number, pv.vendor_name supplier_name, rsh.receipt_num receipt_number, rsl.line_num receipt_line_number, mmt.subinventory_code subinventory FROM gmf_xla_extract_headers eh, mtl_material_transactions mmt, rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_vendors pv, 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 = NVL(mmt.transaction_source_id, mmt.transaction_id) AND eh.source_line_id = mmt.source_line_id AND eh.transaction_source_type_id = 13 /* Inventory */ AND eh.transaction_action_id = 12 /* Intransit receipt */ AND rt.source_document_code = 'INVENTORY' AND rt.transaction_id = mmt.rcv_transaction_id AND mmt.fob_point = 2 AND rt.shipment_header_id = rsh.shipment_header_id AND rsh.receipt_source_code = 'INVENTORY' AND rt.shipment_line_id = rsl.shipment_line_id AND rsh.shipment_header_id = rsl.shipment_header_id AND pv.vendor_id = rsh.vendor_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
, POH.SEGMENT1 PURCHASE_NUMBER
, PV.VENDOR_NAME SUPPLIER_NAME
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, RT.SUBINVENTORY SUBINVENTORY
FROM GMF_XLA_EXTRACT_HEADERS EH
, RCV_TRANSACTIONS RT
, GMF_RCV_ACCOUNTING_TXNS GRAT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS_ALL POH
, PO_VENDORS PV
, MTL_SYSTEM_ITEMS_B_KFV MSI
, XLE_ENTITY_PROFILES LE
, MTL_PARAMETERS MP
WHERE NVL(EH.INVENTORY_ITEM_ID
, 0) = MSI.INVENTORY_ITEM_ID (+) /* B7567558 - ADDED OUTER JOIN TO MSI */
AND EH.ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, EH.ORGANIZATION_ID) /* B7567558 - ADDED NVL*/
AND EH.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND EH.TRANSACTION_ID = GRAT.ACCOUNTING_TXN_ID
AND EH.SOURCE_DOCUMENT_ID = RT.SHIPMENT_HEADER_ID
AND EH.SOURCE_LINE_ID = RT.TRANSACTION_ID
AND RT.TRANSACTION_ID = GRAT.RCV_TRANSACTION_ID
AND RT.SOURCE_DOCUMENT_CODE IN ('PO'
, 'REQ')
AND GRAT.EVENT_TYPE IN (1
, 3
, 6
, 9
, 10
, 7
, 8
, 2
, 5 )
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('VENDOR'
, 'INTERNAL ORDER')
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RT.PO_HEADER_ID = POH.PO_HEADER_ID (+)
AND POH.VENDOR_ID = PV.VENDOR_ID UNION ALL /* 2 PURCHASING
AND INTERNAL REQUISTION
FROM MMT
, FOR QUERY 4 IN GMFXPGET.GMFXPURGET_RCV_TRANSACTIONS */ 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
, POH.SEGMENT1 PURCHASE_NUMBER
, PV.VENDOR_NAME SUPPLIER_NAME
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, MMT.SUBINVENTORY_CODE SUBINVENTORY
FROM GMF_XLA_EXTRACT_HEADERS EH
, RCV_TRANSACTIONS RT
, MTL_MATERIAL_TRANSACTIONS MMT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS_ALL POH
, PO_VENDORS PV
, 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 = RT.SHIPMENT_HEADER_ID
AND EH.SOURCE_LINE_ID = RT.TRANSACTION_ID
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID IN (1
, 7) /* PURCHASING
AND INTERNAL REQ */
AND EH.TRANSACTION_ACTION_ID IN ( 1 /* ISSUE
FROM STORES */
, 7 /* LOGICAL ISSUE */
, 11 /* LOGICAL PO RECEIPT ADJUSTMENT
, LOGICAL DELIVERY ADJUSTMENTS*/
, 12 /* INTRANSIT RECEIPT */
, 26 /* LOGICAL RECEIPT */
, 27 /* RECEIPT INTO STORES */
, 29 /* DELIVERY ADJUSTMENTS */ )
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('VENDOR'
, 'INTERNAL ORDER')
AND RT.SOURCE_DOCUMENT_CODE IN ('PO'
, 'REQ')
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RT.PO_HEADER_ID = POH.PO_HEADER_ID (+)
AND POH.VENDOR_ID = PV.VENDOR_ID UNION ALL /* 3 INTERNAL REQUISTION DIRECT TRANSFERS
, FOR QUERY 5 IN GMFXPGET.GMFXPURGET_RCV_TRANSACTIONS */ 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
, NVL(MMT.SHIPMENT_NUMBER
, ' ') PURCHASE_NUMBER
, '' SUPPLIER_NAME
, '' RECEIPT_NUMBER
, -1 RECEIPT_LINE_NUMBER
, MMT.SUBINVENTORY_CODE SUBINVENTORY
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 = NVL(MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_ID)
AND EH.SOURCE_LINE_ID = NVL(MMT.TRANSACTION_ID
, MMT.SOURCE_LINE_ID)
AND EH.TRANSACTION_SOURCE_TYPE_ID IN (7
, 13) /* 7 INTERNAL REQUISTION
, 13-INVENTORY */
AND EH.TRANSACTION_ACTION_ID IN (3
, 22
, 17) /* 3-DIRECT ORGANIZATION TRANSFER
, 22-LOGICAL INTRANSIT SHIPMENT
, 17 LOGICAL EXPENSE REQUISITION RECEIPT */
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID UNION ALL /*5 INTRANSIT XFER-FOB RECEIVING-TO BOOK INSTRANSIT ENTRIES FOR SHIPPING ORG
, FOR QUERY 7 IN GMFXPGET.GMFXPURGET_RCV_TRANSACTIONS */ 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
, NVL(MMT.SHIPMENT_NUMBER
, '') PURCHASE_NUMBER
, PV.VENDOR_NAME SUPPLIER_NAME
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, MMT.SUBINVENTORY_CODE SUBINVENTORY
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_VENDORS PV
, 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_SOURCE_ID
AND EH.SOURCE_LINE_ID = MMT.SOURCE_LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 7 /* INTERNAL REQUISTION */
AND EH.TRANSACTION_ACTION_ID = 12 /* INTRANSIT RECEIPT */
AND RT.SOURCE_DOCUMENT_CODE IN ('PO'
, 'REQ')
AND RSH.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSH.VENDOR_ID = PV.VENDOR_ID UNION ALL /* 6 CONSIGNED INVENTORY FOR QUERY 8 IN GMFXPGET.GMFXPURGET_RCV_TRANSACTIONS */ 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
, NVL(POH.SEGMENT1
, MMT.SHIPMENT_NUMBER) PURCHASE_NUMBER
, PV.VENDOR_NAME SUPPLIER_NAME
, '' RECEIPT_NUMBER
, -1 RECEIPT_LINE_NUMBER
, MMT.SUBINVENTORY_CODE SUBINVENTORY
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, PO_HEADERS_ALL POH
, PO_VENDORS PV
, 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 = MMT.TRANSACTION_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID IN (1
, 13) /* 1- PURCHASE ORDER
, 13 -INVENTORY */
AND EH.TRANSACTION_ACTION_ID = 6 /* OWNERSHIP TRANSFER */
AND MMT.OWNING_TP_TYPE = 2
AND MMT.TRANSACTION_SOURCE_ID = POH.PO_HEADER_ID
AND POH.VENDOR_ID = PV.VENDOR_ID
AND EH.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID UNION ALL /*8 INTER ORGANIZATION TRANSFERS
, GMFXPURGET_UNPOSTED_INTORG_XFERS
, QUERY 1 */ /* INTRANSIT RECEIPT. ACTION ID = 12 */ 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
, NVL(MMT.SHIPMENT_NUMBER
, '') PURCHASE_NUMBER
, PV.VENDOR_NAME SUPPLIER_NAME
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, MMT.SUBINVENTORY_CODE SUBINVENTORY
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_VENDORS PV
, 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 = NVL(MMT.TRANSACTION_SOURCE_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 = 12 /* INTRANSIT RECEIPT */
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY'
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSH.VENDOR_ID = PV.VENDOR_ID
AND EH.ORGANIZATION_ID = RT.ORGANIZATION_ID
AND EH.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID UNION ALL /*10 INTRANSIT XFER - FOB RECEIVING - TO BOOK INSTRANSIT ENTRIES FOR SHIPPING ORG */ 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
, NVL(MMT.SHIPMENT_NUMBER
, '') PURCHASE_NUMBER
, PV.VENDOR_NAME SUPPLIER_NAME
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, MMT.SUBINVENTORY_CODE SUBINVENTORY
FROM GMF_XLA_EXTRACT_HEADERS EH
, MTL_MATERIAL_TRANSACTIONS MMT
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_VENDORS PV
, 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 = NVL(MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_ID)
AND EH.SOURCE_LINE_ID = MMT.SOURCE_LINE_ID
AND EH.TRANSACTION_SOURCE_TYPE_ID = 13 /* INVENTORY */
AND EH.TRANSACTION_ACTION_ID = 12 /* INTRANSIT RECEIPT */
AND RT.SOURCE_DOCUMENT_CODE = 'INVENTORY'
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
AND MMT.FOB_POINT = 2
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY'
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND PV.VENDOR_ID = RSH.VENDOR_ID