VIEW: APPS.CST_MTL_RCV_TXN_V

Source

View Text - Preformatted

SELECT mmt.ROWID , xfi.legal_entity_id, xfi.name , ccg.cost_group_id , ccg.cost_group , ccg.organization_id , mmt.organization_id , NULL, haou1.name, mp1.organization_code, gll.primary_ledger_id, mmt.transfer_organization_id , haou2.name, mp2.organization_code, mmt.transfer_subinventory , mmt.transfer_locator_id , mil1.CONCATENATED_SEGMENTS, mmt.shipment_number , mmt.transfer_transaction_id , mmt.transaction_id , mmt.transaction_date , mmt.transaction_source_type_id , mtst.transaction_source_type_name , mmt.transaction_source_id , decode(mmt.transaction_source_type_id, 1, pha.segment1, 2, msok.concatenated_segments, 3, gcck.concatenated_segments, 4, mtrh.request_number, 5, we.wip_entity_name, 6, gcck2.concatenated_segments, 7, prha.segment1, 8, msok.concatenated_segments, 9, mcch.cycle_count_header_name, 10, mpi.physical_inventory_name, 11, ccu.description, 12, msok.concatenated_segments, mmt.transaction_source_name), mmt.transaction_quantity , mmt.transaction_uom , mmt.primary_quantity , msv.primary_uom_code , mmt.transaction_action_id , lu1.meaning transaction_action , mmt.transaction_type_id , mtt.transaction_type_name , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , NULL , NULL , NULL , TO_NUMBER ( NULL ) , mmt.inventory_item_id , msv.description , mmt.revision , mmt.waybill_airbill , mmt.freight_code , mmt.number_of_containers , mmt.subinventory_code , mmt.locator_id , mil2.CONCATENATED_SEGMENTS, mtr.reason_name , mmt.transaction_reference , mmt.last_update_date , mmt.last_updated_by , mmt.creation_date , mmt.created_by , mmt.last_update_login , mmt.request_id , mmt.program_application_id , mmt.program_id , mmt.program_update_date , mmt.attribute_category , mmt.attribute1 , mmt.attribute2 , mmt.attribute3 , mmt.attribute4 , mmt.attribute5 , mmt.attribute6 , mmt.attribute7 , mmt.attribute8 , mmt.attribute9 , mmt.attribute10 , mmt.attribute11 , mmt.attribute12 , mmt.attribute13 , mmt.attribute14 , mmt.attribute15 , 'MMT' FROM cst_cost_groups ccg , cst_cost_group_assignments ccga , mtl_material_transactions mmt , mtl_txn_source_types mtst , mtl_system_items_vl msv , mtl_transaction_types mtt , mtl_transaction_reasons mtr , xle_firstparty_information_v xfi, gl_ledger_le_v gll, hr_all_organization_units_tl haou1, hr_all_organization_units_tl haou2, mtl_parameters mp, mtl_parameters mp1, mtl_parameters mp2, mfg_lookups lu1, po_headers_all pha, mtl_txn_request_headers mtrh, wip_entities we, mtl_sales_orders_kfv msok, gl_code_combinations_kfv gcck, mtl_cycle_count_headers mcch, mtl_physical_inventories mpi, po_requisition_headers_all prha, cst_cost_updates ccu, gl_code_combinations_kfv gcck2, mtl_generic_dispositions mgd, mtl_item_locations_kfv mil1, mtl_item_locations_kfv mil2 WHERE mmt.transaction_type_id != 26 AND mmt.organization_id = decode(mmt.transaction_action_id, 21,decode(mmt.fob_point, 1,mmt.organization_id, ccga.organization_id), 12,decode(mmt.fob_point, 2,mmt.organization_id, ccga.organization_id), ccga.organization_id) AND mmt.organization_id = decode(mmt.transaction_action_id, 21,decode(mmt.fob_point, 1,mmt.organization_id, NVL(mmt.owning_organization_id, mmt.organization_id)), 12,decode(mmt.fob_point, 2,mmt.organization_id, NVL(mmt.owning_organization_id, mmt.organization_id)), NVL(mmt.owning_organization_id, mmt.organization_id)) AND nvl(mmt.owning_tp_type,2) = 2 AND ( (ccga.organization_id = mmt.organization_id) OR (ccga.organization_id = mmt.transfer_organization_id AND ccga.cost_group_id NOT IN (SELECT cost_group_id FROM cst_cost_group_assignments ccga2 WHERE ccga2.organization_id = mmt.organization_id) AND ( (mmt.transaction_action_id = 21 AND mmt.fob_point = 1) OR (mmt.transaction_action_id = 12 AND mmt.fob_point = 2) ) ) ) AND ccga.cost_group_id = ccg.cost_group_id AND xfi.legal_entity_id = ccg.legal_entity AND gll.legal_entity_id = xfi.legal_entity_id AND gll.relationship_enabled_flag = 'Y' AND gll.ledger_category_code = 'PRIMARY' AND mmt.transfer_locator_id = mil1.inventory_location_id(+) AND mmt.locator_id = mil2.inventory_location_id (+) AND mmt.transfer_organization_id = mil1.organization_id (+) AND mmt.organization_id = mil2.organization_id (+) AND mp1.organization_id = ccga.organization_id AND haou1.organization_id = ccga.organization_id and haou1.language = userenv('LANG') and msv.organization_id = mmt.organization_id AND msv.inventory_item_id = mmt.inventory_item_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND mmt.transaction_type_id = mtt.transaction_type_id AND lu1.lookup_type = 'MTL_TRANSACTION_ACTION' AND lu1.lookup_code = mmt.transaction_action_id AND mp2.organization_id (+) = mmt.transfer_organization_id AND haou2.organization_id(+) = mmt.transfer_organization_id and nvl(haou2.language,userenv('LANG')) = userenv('LANG') and mtr.reason_id (+) = mmt.reason_id and pha.po_header_id (+) = mmt.transaction_source_id and we.wip_entity_id (+) = mmt.transaction_source_id and mtrh.header_id (+) = mmt.transaction_source_id and msok.sales_order_id (+) = mmt.transaction_source_id and gcck.code_combination_id (+) = mmt.transaction_source_id and mcch.cycle_count_header_id (+) = mmt.transaction_source_id and mpi.physical_inventory_id (+) = mmt.transaction_source_id and mpi.organization_id (+) = mmt.organization_id and prha.requisition_header_id (+) = mmt.transaction_source_id and ccu.cost_update_id (+) = mmt.transaction_source_id and mgd.disposition_id (+) = mmt.transaction_source_id and gcck2.code_combination_id (+) = mgd.distribution_account and mmt.organization_id = mp.organization_id and nvl(mp.process_enabled_flag,'N') <> 'Y' UNION SELECT mmt.ROWID , xfi.legal_entity_id, xfi.name , ccg.cost_group_id , ccg.cost_group , ccg.organization_id , mmt.organization_id , NULL, haou1.name, mp1.organization_code, gll.primary_ledger_id, mmt.transfer_organization_id , haou2.name, mp2.organization_code, mmt.transfer_subinventory , mmt.transfer_locator_id , mil1.CONCATENATED_SEGMENTS, mmt.shipment_number , mmt.transfer_transaction_id , mmt.transaction_id , mmt.transaction_date , mmt.transaction_source_type_id , mtst.transaction_source_type_name , mmt.transaction_source_id , mmt.transaction_source_name , mmt.transaction_quantity , mmt.transaction_uom , mmt.primary_quantity , msv.primary_uom_code , mmt.transaction_action_id , lu1.meaning transaction_action , mmt.transaction_type_id , mtt.transaction_type_name , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL) , NULL , NULL , NULL , TO_NUMBER ( NULL ) , mmt.inventory_item_id , msv.description , mmt.revision , mmt.waybill_airbill , mmt.freight_code , mmt.number_of_containers , mmt.subinventory_code , mmt.locator_id , mil2.CONCATENATED_SEGMENTS, mtr.reason_name , mmt.transaction_reference , mmt.last_update_date , mmt.last_updated_by , mmt.creation_date , mmt.created_by , mmt.last_update_login , mmt.request_id , mmt.program_application_id , mmt.program_id , mmt.program_update_date , mmt.attribute_category , mmt.attribute1 , mmt.attribute2 , mmt.attribute3 , mmt.attribute4 , mmt.attribute5 , mmt.attribute6 , mmt.attribute7 , mmt.attribute8 , mmt.attribute9 , mmt.attribute10 , mmt.attribute11 , mmt.attribute12 , mmt.attribute13 , mmt.attribute14 , mmt.attribute15 , 'MMT' FROM cst_cost_groups ccg , mtl_material_transactions mmt , mtl_txn_source_types mtst , mtl_system_items_vl msv , mtl_transaction_types mtt , mtl_transaction_reasons mtr , xle_firstparty_information_v xfi, gl_ledger_le_v gll, hr_all_organization_units_tl haou1, hr_all_organization_units_tl haou2, mtl_parameters mp1, mtl_parameters mp2, mfg_lookups lu1 , mtl_item_locations_kfv mil1, mtl_item_locations_kfv mil2 WHERE mmt.transaction_type_id = 26 AND mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) AND nvl(mmt.owning_tp_type,2) = 2 AND mmt.org_cost_group_id = ccg.cost_group_id AND xfi.legal_entity_id = ccg.legal_entity AND gll.legal_entity_id = xfi.legal_entity_id AND gll.relationship_enabled_flag = 'Y' AND gll.ledger_category_code = 'PRIMARY' AND mmt.transfer_locator_id = mil1.inventory_location_id(+) AND mmt.locator_id = mil2.inventory_location_id (+) AND mmt.transfer_organization_id = mil1.organization_id (+) AND mmt.organization_id = mil2.organization_id (+) AND mp1.organization_id = mmt.organization_id AND haou1.organization_id = mmt.organization_id and haou1.language = userenv('LANG') and msv.organization_id = mmt.organization_id AND msv.inventory_item_id = mmt.inventory_item_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND mmt.transaction_type_id = mtt.transaction_type_id AND lu1.lookup_type = 'MTL_TRANSACTION_ACTION' AND lu1.lookup_code = mmt.transaction_action_id AND mp2.organization_id (+) = mmt.transfer_organization_id AND haou2.organization_id(+) = mmt.transfer_organization_id and nvl(haou2.language,userenv('LANG')) = userenv('LANG') and mtr.reason_id ( + ) = mmt.reason_id union SELECT rt.ROWID , xfi.legal_entity_id, xfi.name, ccg.cost_group_id , ccg.cost_group , ccg.organization_id , rt.organization_id , ph.org_id, haou.name, mp.organization_code, gll.primary_ledger_id, TO_NUMBER ( NULL ) , NULL , NULL , NULL , TO_NUMBER ( NULL ) , NULL, NULL , TO_NUMBER ( NULL ) , rt.transaction_id , rt.transaction_date , TO_NUMBER ( NULL ) , rt.source_document_code , TO_NUMBER ( NULL ) , NULL , rt.quantity , rt.uom_code , rt.primary_quantity , rt.primary_unit_of_measure , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , rt.transaction_type , rt.po_header_id , ph.segment1 , rt.po_line_id , pl.line_num , prh.requisition_header_id , prh.segment1 , prl.requisition_line_id , prl.line_num , rt.vendor_id , pv.vendor_name , rt.vendor_site_id , pvs.vendor_site_code , rsh.receipt_num , rsh.shipment_num , rt.location_id , rsl.item_id , rsl.item_description , rsl.item_revision , NULL , NULL , to_number ( NULL ) , rt.subinventory , rt.locator_id , mil.CONCATENATED_SEGMENTS, mtr.reason_name , rt.comments , rt.last_update_date , rt.last_updated_by , rt.creation_date , rt.created_by , rt.last_update_login , rt.request_id , rt.program_application_id , rt.program_id , rt.program_update_date , rt.attribute_category , rt.attribute1 , rt.attribute2 , rt.attribute3 , rt.attribute4 , rt.attribute5 , rt.attribute6 , rt.attribute7 , rt.attribute8 , rt.attribute9 , rt.attribute10 , rt.attribute11 , rt.attribute12 , rt.attribute13 , rt.attribute14 , rt.attribute15 , 'RCV' FROM cst_cost_groups ccg , cst_cost_group_assignments ccga , rcv_transactions rt , po_headers_all ph , po_lines_all pl , po_vendors pv , po_vendor_sites_all pvs, rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl , mtl_transaction_reasons mtr , xle_firstparty_information_v xfi, gl_ledger_le_v gll, mtl_parameters mp, hr_all_organization_units_tl haou, mtl_item_locations_kfv mil WHERE rt.organization_id = ccga.organization_id AND NVL(rt.consigned_flag,'N') = 'N' AND ccga.cost_group_id = ccg.cost_group_id AND xfi.legal_entity_id = ccg.legal_entity AND gll.legal_entity_id = xfi.legal_entity_id AND gll.relationship_enabled_flag = 'Y' AND gll.ledger_category_code = 'PRIMARY' AND mil.organization_id (+) = rt.organization_id AND mil.inventory_location_id (+) = rt.locator_id AND mp.organization_id = ccga.organization_id AND haou.organization_id = ccga.organization_id and haou.language = userenv('LANG') AND rt.po_header_id is not null and ph.po_header_id = rt.po_header_id AND pl.po_line_id = rt.po_line_id AND mtr.reason_id ( + ) = rt.reason_id AND prl.requisition_line_id ( + ) = rt.requisition_line_id AND prh.requisition_header_id ( + ) = prl.requisition_header_id AND pv.vendor_id (+) = rt.vendor_id AND pvs.vendor_site_id = rt.vendor_site_id and pvs.org_id = ph.org_id and rsh.shipment_header_id = rsl.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id and rt.organization_id is not null and rt.destination_type_code != 'INVENTORY' union SELECT rt.ROWID , xfi.legal_entity_id, xfi.name , ccg.cost_group_id , ccg.cost_group , ccg.organization_id , rt.organization_id , prh.org_id, haou.name, mp.organization_code, gll.primary_ledger_id, TO_NUMBER ( NULL ) , NULL , NULL , NULL , TO_NUMBER ( NULL ) , NULL , NULL, TO_NUMBER ( NULL ) , rt.transaction_id , rt.transaction_date , TO_NUMBER ( NULL ) , rt.source_document_code , TO_NUMBER ( NULL ) , NULL , rt.quantity , rt.uom_code , rt.primary_quantity , rt.primary_unit_of_measure , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , rt.transaction_type , rt.po_header_id , null, rt.po_line_id , to_number(null), prh.requisition_header_id , prh.segment1 , prl.requisition_line_id , prl.line_num , rt.vendor_id , pv.vendor_name , rt.vendor_site_id , null, rsh.receipt_num , rsh.shipment_num , rt.location_id , rsl.item_id , rsl.item_description , rsl.item_revision , NULL , NULL , to_number ( NULL ) , rt.subinventory , rt.locator_id , mil.CONCATENATED_SEGMENTS, mtr.reason_name , rt.comments , rt.last_update_date , rt.last_updated_by , rt.creation_date , rt.created_by , rt.last_update_login , rt.request_id , rt.program_application_id , rt.program_id , rt.program_update_date , rt.attribute_category , rt.attribute1 , rt.attribute2 , rt.attribute3 , rt.attribute4 , rt.attribute5 , rt.attribute6 , rt.attribute7 , rt.attribute8 , rt.attribute9 , rt.attribute10 , rt.attribute11 , rt.attribute12 , rt.attribute13 , rt.attribute14 , rt.attribute15 , 'RCV' FROM cst_cost_groups ccg , cst_cost_group_assignments ccga , rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_vendors pv , po_requisition_headers_all prh , po_requisition_lines_all prl , mtl_transaction_reasons mtr , xle_firstparty_information_v xfi, gl_ledger_le_v gll, mtl_parameters mp, hr_all_organization_units_tl haou, mtl_item_locations_kfv mil WHERE rt.organization_id = ccga.organization_id AND NVL(rt.consigned_flag,'N') = 'N' AND ccga.cost_group_id = ccg.cost_group_id AND xfi.legal_entity_id = ccg.legal_entity AND gll.legal_entity_id = xfi.legal_entity_id AND gll.relationship_enabled_flag = 'Y' AND gll.ledger_category_code = 'PRIMARY' AND mil.organization_id (+) = rt.organization_id AND mil.inventory_location_id (+) = rt.locator_id AND mp.organization_id = ccga.organization_id AND haou.organization_id = ccga.organization_id and haou.language = userenv('LANG') AND rt.po_header_id is null AND mtr.reason_id ( + ) = rt.reason_id AND prl.requisition_line_id ( + ) = rt.requisition_line_id AND prh.requisition_header_id ( + ) = prl.requisition_header_id AND pv.vendor_id ( + ) = rt.vendor_id AND rsh.shipment_header_id = rt.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id and rt.organization_id is not null and rt.destination_type_code != 'INVENTORY' union SELECT rae.ROWID , xfi.legal_entity_id, xfi.name , ccg.cost_group_id , ccg.cost_group , ccg.organization_id , rae.organization_id , ph.org_id, haou.name, mp.organization_code, gll.primary_ledger_id, TO_NUMBER ( NULL ) , NULL , NULL , NULL , TO_NUMBER ( NULL ) , NULL , NULL, TO_NUMBER ( NULL ) , rae.accounting_event_id , rae.transaction_date , TO_NUMBER ( NULL ) , rt.source_document_code , TO_NUMBER ( NULL ) , NULL , rae.transaction_quantity, rt.uom_code , rae.primary_quantity , rae.primary_unit_of_measure , TO_NUMBER ( NULL ) , NULL , TO_NUMBER ( NULL ) , decode(rae.event_type_id,18,raet.description, 19,raet.description, 20,raet.description, raet.event_type_name) event_type_name, rae.po_header_id , ph.segment1 , rt.po_line_id , pl.line_num , prh.requisition_header_id , prh.segment1 , prl.requisition_line_id , prl.line_num , rt.vendor_id , pv.vendor_name , rt.vendor_site_id , null , rsh.receipt_num , rsh.shipment_num , rt.location_id , rsl.item_id , rsl.item_description , rsl.item_revision , NULL , NULL , to_number ( NULL ) , rt.subinventory , rt.locator_id , mil.CONCATENATED_SEGMENTS, mtr.reason_name , rt.comments , rt.last_update_date , rt.last_updated_by , rt.creation_date , rt.created_by , rt.last_update_login , rt.request_id , rt.program_application_id , rt.program_id , rt.program_update_date , rt.attribute_category , rt.attribute1 , rt.attribute2 , rt.attribute3 , rt.attribute4 , rt.attribute5 , rt.attribute6 , rt.attribute7 , rt.attribute8 , rt.attribute9 , rt.attribute10 , rt.attribute11 , rt.attribute12 , rt.attribute13 , rt.attribute14 , rt.attribute15 , 'RAE' FROM cst_cost_groups ccg , cst_cost_group_assignments ccga , rcv_transactions rt , rcv_accounting_events rae, rcv_accounting_event_types raet, po_headers_all ph , po_lines_all pl , po_vendors pv , rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_requisition_headers_all prh , po_requisition_lines_all prl , mtl_transaction_reasons mtr , xle_firstparty_information_v xfi, gl_ledger_le_v gll, mtl_parameters mp, hr_all_organization_units_tl haou, mtl_item_locations_kfv mil WHERE rae.organization_id = ccga.organization_id AND rae.event_type_id = raet.event_type_id AND NVL(rt.consigned_flag,'N') = 'N' AND ccga.cost_group_id = ccg.cost_group_id AND xfi.legal_entity_id = ccg.legal_entity AND gll.legal_entity_id = xfi.legal_entity_id AND gll.relationship_enabled_flag = 'Y' AND gll.ledger_category_code = 'PRIMARY' AND mil.organization_id (+) = rt.organization_id AND mil.inventory_location_id (+) = rt.locator_id AND mp.organization_id = ccga.organization_id AND haou.organization_id = ccga.organization_id and haou.language = userenv('LANG') AND rae.rcv_transaction_id = rt.transaction_id AND rt.po_header_id is not null AND ph.po_header_id = rt.po_header_id AND pl.po_line_id = rt.po_line_id AND mtr.reason_id ( + ) = rt.reason_id AND prl.requisition_line_id ( + ) = rt.requisition_line_id AND prh.requisition_header_id ( + ) = prl.requisition_header_id AND pv.vendor_id (+) = rt.vendor_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id and rt.organization_id is not null and rae.event_type_id in (7,9,10,18,19,20) and rt.destination_type_code != 'INVENTORY'

View Text - HTML Formatted

SELECT MMT.ROWID
, XFI.LEGAL_ENTITY_ID
, XFI.NAME
, CCG.COST_GROUP_ID
, CCG.COST_GROUP
, CCG.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, NULL
, HAOU1.NAME
, MP1.ORGANIZATION_CODE
, GLL.PRIMARY_LEDGER_ID
, MMT.TRANSFER_ORGANIZATION_ID
, HAOU2.NAME
, MP2.ORGANIZATION_CODE
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MIL1.CONCATENATED_SEGMENTS
, MMT.SHIPMENT_NUMBER
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_ID
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 1
, PHA.SEGMENT1
, 2
, MSOK.CONCATENATED_SEGMENTS
, 3
, GCCK.CONCATENATED_SEGMENTS
, 4
, MTRH.REQUEST_NUMBER
, 5
, WE.WIP_ENTITY_NAME
, 6
, GCCK2.CONCATENATED_SEGMENTS
, 7
, PRHA.SEGMENT1
, 8
, MSOK.CONCATENATED_SEGMENTS
, 9
, MCCH.CYCLE_COUNT_HEADER_NAME
, 10
, MPI.PHYSICAL_INVENTORY_NAME
, 11
, CCU.DESCRIPTION
, 12
, MSOK.CONCATENATED_SEGMENTS
, MMT.TRANSACTION_SOURCE_NAME)
, MMT.TRANSACTION_QUANTITY
, MMT.TRANSACTION_UOM
, MMT.PRIMARY_QUANTITY
, MSV.PRIMARY_UOM_CODE
, MMT.TRANSACTION_ACTION_ID
, LU1.MEANING TRANSACTION_ACTION
, MMT.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, NULL
, NULL
, NULL
, TO_NUMBER ( NULL )
, MMT.INVENTORY_ITEM_ID
, MSV.DESCRIPTION
, MMT.REVISION
, MMT.WAYBILL_AIRBILL
, MMT.FREIGHT_CODE
, MMT.NUMBER_OF_CONTAINERS
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL2.CONCATENATED_SEGMENTS
, MTR.REASON_NAME
, MMT.TRANSACTION_REFERENCE
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_LOGIN
, MMT.REQUEST_ID
, MMT.PROGRAM_APPLICATION_ID
, MMT.PROGRAM_ID
, MMT.PROGRAM_UPDATE_DATE
, MMT.ATTRIBUTE_CATEGORY
, MMT.ATTRIBUTE1
, MMT.ATTRIBUTE2
, MMT.ATTRIBUTE3
, MMT.ATTRIBUTE4
, MMT.ATTRIBUTE5
, MMT.ATTRIBUTE6
, MMT.ATTRIBUTE7
, MMT.ATTRIBUTE8
, MMT.ATTRIBUTE9
, MMT.ATTRIBUTE10
, MMT.ATTRIBUTE11
, MMT.ATTRIBUTE12
, MMT.ATTRIBUTE13
, MMT.ATTRIBUTE14
, MMT.ATTRIBUTE15
, 'MMT'
FROM CST_COST_GROUPS CCG
, CST_COST_GROUP_ASSIGNMENTS CCGA
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_SYSTEM_ITEMS_VL MSV
, MTL_TRANSACTION_TYPES MTT
, MTL_TRANSACTION_REASONS MTR
, XLE_FIRSTPARTY_INFORMATION_V XFI
, GL_LEDGER_LE_V GLL
, HR_ALL_ORGANIZATION_UNITS_TL HAOU1
, HR_ALL_ORGANIZATION_UNITS_TL HAOU2
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MFG_LOOKUPS LU1
, PO_HEADERS_ALL PHA
, MTL_TXN_REQUEST_HEADERS MTRH
, WIP_ENTITIES WE
, MTL_SALES_ORDERS_KFV MSOK
, GL_CODE_COMBINATIONS_KFV GCCK
, MTL_CYCLE_COUNT_HEADERS MCCH
, MTL_PHYSICAL_INVENTORIES MPI
, PO_REQUISITION_HEADERS_ALL PRHA
, CST_COST_UPDATES CCU
, GL_CODE_COMBINATIONS_KFV GCCK2
, MTL_GENERIC_DISPOSITIONS MGD
, MTL_ITEM_LOCATIONS_KFV MIL1
, MTL_ITEM_LOCATIONS_KFV MIL2
WHERE MMT.TRANSACTION_TYPE_ID != 26
AND MMT.ORGANIZATION_ID = DECODE(MMT.TRANSACTION_ACTION_ID
, 21
, DECODE(MMT.FOB_POINT
, 1
, MMT.ORGANIZATION_ID
, CCGA.ORGANIZATION_ID)
, 12
, DECODE(MMT.FOB_POINT
, 2
, MMT.ORGANIZATION_ID
, CCGA.ORGANIZATION_ID)
, CCGA.ORGANIZATION_ID)
AND MMT.ORGANIZATION_ID = DECODE(MMT.TRANSACTION_ACTION_ID
, 21
, DECODE(MMT.FOB_POINT
, 1
, MMT.ORGANIZATION_ID
, NVL(MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID))
, 12
, DECODE(MMT.FOB_POINT
, 2
, MMT.ORGANIZATION_ID
, NVL(MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID))
, NVL(MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID))
AND NVL(MMT.OWNING_TP_TYPE
, 2) = 2
AND ( (CCGA.ORGANIZATION_ID = MMT.ORGANIZATION_ID) OR (CCGA.ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND CCGA.COST_GROUP_ID NOT IN (SELECT COST_GROUP_ID
FROM CST_COST_GROUP_ASSIGNMENTS CCGA2
WHERE CCGA2.ORGANIZATION_ID = MMT.ORGANIZATION_ID)
AND ( (MMT.TRANSACTION_ACTION_ID = 21
AND MMT.FOB_POINT = 1) OR (MMT.TRANSACTION_ACTION_ID = 12
AND MMT.FOB_POINT = 2) ) ) )
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND XFI.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND GLL.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND MMT.TRANSFER_LOCATOR_ID = MIL1.INVENTORY_LOCATION_ID(+)
AND MMT.LOCATOR_ID = MIL2.INVENTORY_LOCATION_ID (+)
AND MMT.TRANSFER_ORGANIZATION_ID = MIL1.ORGANIZATION_ID (+)
AND MMT.ORGANIZATION_ID = MIL2.ORGANIZATION_ID (+)
AND MP1.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU1.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU1.LANGUAGE = USERENV('LANG')
AND MSV.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND LU1.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
AND LU1.LOOKUP_CODE = MMT.TRANSACTION_ACTION_ID
AND MP2.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID
AND HAOU2.ORGANIZATION_ID(+) = MMT.TRANSFER_ORGANIZATION_ID
AND NVL(HAOU2.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND MTR.REASON_ID (+) = MMT.REASON_ID
AND PHA.PO_HEADER_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND WE.WIP_ENTITY_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND MTRH.HEADER_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND MSOK.SALES_ORDER_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND GCCK.CODE_COMBINATION_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND MCCH.CYCLE_COUNT_HEADER_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND MPI.PHYSICAL_INVENTORY_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND MPI.ORGANIZATION_ID (+) = MMT.ORGANIZATION_ID
AND PRHA.REQUISITION_HEADER_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND CCU.COST_UPDATE_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND MGD.DISPOSITION_ID (+) = MMT.TRANSACTION_SOURCE_ID
AND GCCK2.CODE_COMBINATION_ID (+) = MGD.DISTRIBUTION_ACCOUNT
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND NVL(MP.PROCESS_ENABLED_FLAG
, 'N') <> 'Y' UNION SELECT MMT.ROWID
, XFI.LEGAL_ENTITY_ID
, XFI.NAME
, CCG.COST_GROUP_ID
, CCG.COST_GROUP
, CCG.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, NULL
, HAOU1.NAME
, MP1.ORGANIZATION_CODE
, GLL.PRIMARY_LEDGER_ID
, MMT.TRANSFER_ORGANIZATION_ID
, HAOU2.NAME
, MP2.ORGANIZATION_CODE
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MIL1.CONCATENATED_SEGMENTS
, MMT.SHIPMENT_NUMBER
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_QUANTITY
, MMT.TRANSACTION_UOM
, MMT.PRIMARY_QUANTITY
, MSV.PRIMARY_UOM_CODE
, MMT.TRANSACTION_ACTION_ID
, LU1.MEANING TRANSACTION_ACTION
, MMT.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL)
, NULL
, NULL
, NULL
, TO_NUMBER ( NULL )
, MMT.INVENTORY_ITEM_ID
, MSV.DESCRIPTION
, MMT.REVISION
, MMT.WAYBILL_AIRBILL
, MMT.FREIGHT_CODE
, MMT.NUMBER_OF_CONTAINERS
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL2.CONCATENATED_SEGMENTS
, MTR.REASON_NAME
, MMT.TRANSACTION_REFERENCE
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_LOGIN
, MMT.REQUEST_ID
, MMT.PROGRAM_APPLICATION_ID
, MMT.PROGRAM_ID
, MMT.PROGRAM_UPDATE_DATE
, MMT.ATTRIBUTE_CATEGORY
, MMT.ATTRIBUTE1
, MMT.ATTRIBUTE2
, MMT.ATTRIBUTE3
, MMT.ATTRIBUTE4
, MMT.ATTRIBUTE5
, MMT.ATTRIBUTE6
, MMT.ATTRIBUTE7
, MMT.ATTRIBUTE8
, MMT.ATTRIBUTE9
, MMT.ATTRIBUTE10
, MMT.ATTRIBUTE11
, MMT.ATTRIBUTE12
, MMT.ATTRIBUTE13
, MMT.ATTRIBUTE14
, MMT.ATTRIBUTE15
, 'MMT'
FROM CST_COST_GROUPS CCG
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_SYSTEM_ITEMS_VL MSV
, MTL_TRANSACTION_TYPES MTT
, MTL_TRANSACTION_REASONS MTR
, XLE_FIRSTPARTY_INFORMATION_V XFI
, GL_LEDGER_LE_V GLL
, HR_ALL_ORGANIZATION_UNITS_TL HAOU1
, HR_ALL_ORGANIZATION_UNITS_TL HAOU2
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MFG_LOOKUPS LU1
, MTL_ITEM_LOCATIONS_KFV MIL1
, MTL_ITEM_LOCATIONS_KFV MIL2
WHERE MMT.TRANSACTION_TYPE_ID = 26
AND MMT.ORGANIZATION_ID = NVL(MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID)
AND NVL(MMT.OWNING_TP_TYPE
, 2) = 2
AND MMT.ORG_COST_GROUP_ID = CCG.COST_GROUP_ID
AND XFI.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND GLL.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND MMT.TRANSFER_LOCATOR_ID = MIL1.INVENTORY_LOCATION_ID(+)
AND MMT.LOCATOR_ID = MIL2.INVENTORY_LOCATION_ID (+)
AND MMT.TRANSFER_ORGANIZATION_ID = MIL1.ORGANIZATION_ID (+)
AND MMT.ORGANIZATION_ID = MIL2.ORGANIZATION_ID (+)
AND MP1.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND HAOU1.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND HAOU1.LANGUAGE = USERENV('LANG')
AND MSV.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND LU1.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
AND LU1.LOOKUP_CODE = MMT.TRANSACTION_ACTION_ID
AND MP2.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID
AND HAOU2.ORGANIZATION_ID(+) = MMT.TRANSFER_ORGANIZATION_ID
AND NVL(HAOU2.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND MTR.REASON_ID ( + ) = MMT.REASON_ID UNION SELECT RT.ROWID
, XFI.LEGAL_ENTITY_ID
, XFI.NAME
, CCG.COST_GROUP_ID
, CCG.COST_GROUP
, CCG.ORGANIZATION_ID
, RT.ORGANIZATION_ID
, PH.ORG_ID
, HAOU.NAME
, MP.ORGANIZATION_CODE
, GLL.PRIMARY_LEDGER_ID
, TO_NUMBER ( NULL )
, NULL
, NULL
, NULL
, TO_NUMBER ( NULL )
, NULL
, NULL
, TO_NUMBER ( NULL )
, RT.TRANSACTION_ID
, RT.TRANSACTION_DATE
, TO_NUMBER ( NULL )
, RT.SOURCE_DOCUMENT_CODE
, TO_NUMBER ( NULL )
, NULL
, RT.QUANTITY
, RT.UOM_CODE
, RT.PRIMARY_QUANTITY
, RT.PRIMARY_UNIT_OF_MEASURE
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, RT.TRANSACTION_TYPE
, RT.PO_HEADER_ID
, PH.SEGMENT1
, RT.PO_LINE_ID
, PL.LINE_NUM
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, RT.VENDOR_ID
, PV.VENDOR_NAME
, RT.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RT.LOCATION_ID
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.ITEM_REVISION
, NULL
, NULL
, TO_NUMBER ( NULL )
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS
, MTR.REASON_NAME
, RT.COMMENTS
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
, RT.REQUEST_ID
, RT.PROGRAM_APPLICATION_ID
, RT.PROGRAM_ID
, RT.PROGRAM_UPDATE_DATE
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, 'RCV'
FROM CST_COST_GROUPS CCG
, CST_COST_GROUP_ASSIGNMENTS CCGA
, RCV_TRANSACTIONS RT
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, MTL_TRANSACTION_REASONS MTR
, XLE_FIRSTPARTY_INFORMATION_V XFI
, GL_LEDGER_LE_V GLL
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS_TL HAOU
, MTL_ITEM_LOCATIONS_KFV MIL
WHERE RT.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND NVL(RT.CONSIGNED_FLAG
, 'N') = 'N'
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND XFI.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND GLL.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND MIL.ORGANIZATION_ID (+) = RT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = RT.LOCATOR_ID
AND MP.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU.LANGUAGE = USERENV('LANG')
AND RT.PO_HEADER_ID IS NOT NULL
AND PH.PO_HEADER_ID = RT.PO_HEADER_ID
AND PL.PO_LINE_ID = RT.PO_LINE_ID
AND MTR.REASON_ID ( + ) = RT.REASON_ID
AND PRL.REQUISITION_LINE_ID ( + ) = RT.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID ( + ) = PRL.REQUISITION_HEADER_ID
AND PV.VENDOR_ID (+) = RT.VENDOR_ID
AND PVS.VENDOR_SITE_ID = RT.VENDOR_SITE_ID
AND PVS.ORG_ID = PH.ORG_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND RT.ORGANIZATION_ID IS NOT NULL
AND RT.DESTINATION_TYPE_CODE != 'INVENTORY' UNION SELECT RT.ROWID
, XFI.LEGAL_ENTITY_ID
, XFI.NAME
, CCG.COST_GROUP_ID
, CCG.COST_GROUP
, CCG.ORGANIZATION_ID
, RT.ORGANIZATION_ID
, PRH.ORG_ID
, HAOU.NAME
, MP.ORGANIZATION_CODE
, GLL.PRIMARY_LEDGER_ID
, TO_NUMBER ( NULL )
, NULL
, NULL
, NULL
, TO_NUMBER ( NULL )
, NULL
, NULL
, TO_NUMBER ( NULL )
, RT.TRANSACTION_ID
, RT.TRANSACTION_DATE
, TO_NUMBER ( NULL )
, RT.SOURCE_DOCUMENT_CODE
, TO_NUMBER ( NULL )
, NULL
, RT.QUANTITY
, RT.UOM_CODE
, RT.PRIMARY_QUANTITY
, RT.PRIMARY_UNIT_OF_MEASURE
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, RT.TRANSACTION_TYPE
, RT.PO_HEADER_ID
, NULL
, RT.PO_LINE_ID
, TO_NUMBER(NULL)
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, RT.VENDOR_ID
, PV.VENDOR_NAME
, RT.VENDOR_SITE_ID
, NULL
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RT.LOCATION_ID
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.ITEM_REVISION
, NULL
, NULL
, TO_NUMBER ( NULL )
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS
, MTR.REASON_NAME
, RT.COMMENTS
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
, RT.REQUEST_ID
, RT.PROGRAM_APPLICATION_ID
, RT.PROGRAM_ID
, RT.PROGRAM_UPDATE_DATE
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, 'RCV'
FROM CST_COST_GROUPS CCG
, CST_COST_GROUP_ASSIGNMENTS CCGA
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_VENDORS PV
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, MTL_TRANSACTION_REASONS MTR
, XLE_FIRSTPARTY_INFORMATION_V XFI
, GL_LEDGER_LE_V GLL
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS_TL HAOU
, MTL_ITEM_LOCATIONS_KFV MIL
WHERE RT.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND NVL(RT.CONSIGNED_FLAG
, 'N') = 'N'
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND XFI.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND GLL.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND MIL.ORGANIZATION_ID (+) = RT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = RT.LOCATOR_ID
AND MP.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU.LANGUAGE = USERENV('LANG')
AND RT.PO_HEADER_ID IS NULL
AND MTR.REASON_ID ( + ) = RT.REASON_ID
AND PRL.REQUISITION_LINE_ID ( + ) = RT.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID ( + ) = PRL.REQUISITION_HEADER_ID
AND PV.VENDOR_ID ( + ) = RT.VENDOR_ID
AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND RT.ORGANIZATION_ID IS NOT NULL
AND RT.DESTINATION_TYPE_CODE != 'INVENTORY' UNION SELECT RAE.ROWID
, XFI.LEGAL_ENTITY_ID
, XFI.NAME
, CCG.COST_GROUP_ID
, CCG.COST_GROUP
, CCG.ORGANIZATION_ID
, RAE.ORGANIZATION_ID
, PH.ORG_ID
, HAOU.NAME
, MP.ORGANIZATION_CODE
, GLL.PRIMARY_LEDGER_ID
, TO_NUMBER ( NULL )
, NULL
, NULL
, NULL
, TO_NUMBER ( NULL )
, NULL
, NULL
, TO_NUMBER ( NULL )
, RAE.ACCOUNTING_EVENT_ID
, RAE.TRANSACTION_DATE
, TO_NUMBER ( NULL )
, RT.SOURCE_DOCUMENT_CODE
, TO_NUMBER ( NULL )
, NULL
, RAE.TRANSACTION_QUANTITY
, RT.UOM_CODE
, RAE.PRIMARY_QUANTITY
, RAE.PRIMARY_UNIT_OF_MEASURE
, TO_NUMBER ( NULL )
, NULL
, TO_NUMBER ( NULL )
, DECODE(RAE.EVENT_TYPE_ID
, 18
, RAET.DESCRIPTION
, 19
, RAET.DESCRIPTION
, 20
, RAET.DESCRIPTION
, RAET.EVENT_TYPE_NAME) EVENT_TYPE_NAME
, RAE.PO_HEADER_ID
, PH.SEGMENT1
, RT.PO_LINE_ID
, PL.LINE_NUM
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, RT.VENDOR_ID
, PV.VENDOR_NAME
, RT.VENDOR_SITE_ID
, NULL
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RT.LOCATION_ID
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.ITEM_REVISION
, NULL
, NULL
, TO_NUMBER ( NULL )
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS
, MTR.REASON_NAME
, RT.COMMENTS
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
, RT.REQUEST_ID
, RT.PROGRAM_APPLICATION_ID
, RT.PROGRAM_ID
, RT.PROGRAM_UPDATE_DATE
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, 'RAE'
FROM CST_COST_GROUPS CCG
, CST_COST_GROUP_ASSIGNMENTS CCGA
, RCV_TRANSACTIONS RT
, RCV_ACCOUNTING_EVENTS RAE
, RCV_ACCOUNTING_EVENT_TYPES RAET
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_VENDORS PV
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, MTL_TRANSACTION_REASONS MTR
, XLE_FIRSTPARTY_INFORMATION_V XFI
, GL_LEDGER_LE_V GLL
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS_TL HAOU
, MTL_ITEM_LOCATIONS_KFV MIL
WHERE RAE.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND RAE.EVENT_TYPE_ID = RAET.EVENT_TYPE_ID
AND NVL(RT.CONSIGNED_FLAG
, 'N') = 'N'
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND XFI.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND GLL.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND MIL.ORGANIZATION_ID (+) = RT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = RT.LOCATOR_ID
AND MP.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND HAOU.LANGUAGE = USERENV('LANG')
AND RAE.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.PO_HEADER_ID IS NOT NULL
AND PH.PO_HEADER_ID = RT.PO_HEADER_ID
AND PL.PO_LINE_ID = RT.PO_LINE_ID
AND MTR.REASON_ID ( + ) = RT.REASON_ID
AND PRL.REQUISITION_LINE_ID ( + ) = RT.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID ( + ) = PRL.REQUISITION_HEADER_ID
AND PV.VENDOR_ID (+) = RT.VENDOR_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND RT.ORGANIZATION_ID IS NOT NULL
AND RAE.EVENT_TYPE_ID IN (7
, 9
, 10
, 18
, 19
, 20)
AND RT.DESTINATION_TYPE_CODE != 'INVENTORY'