[Home] [Help]
SELECT r.GROUP_ID GROUP_ID, r.interface_transaction_id interface_transaction_id, sh.receipt_num receipt_num, NULL line_num, msi.CONCATENATED_SEGMENTS item_no, NVL (r.item_description, msi.description) item_description, r.transaction_date transaction_date, pv.vendor_name vendor_name, pv.vendor_id vendor_id, pv.segment1 vendor_no, r.subinventory subinventory, mil.description locator, r.locator_id locator_id, r.quantity quantity, r.unit_of_measure uom, r.secondary_quantity secondary_quantity, r.secondary_unit_of_measure secondary_uom, mp.organization_code organization_code, h.NAME organization_name, r.vendor_lot_num vendor_lot_num FROM rcv_transactions_interface r, rcv_shipment_headers sh, mtl_system_items_kfv msi, po_vendors pv, mtl_item_locations mil, hr_all_organization_units h, mtl_parameters mp WHERE r.shipment_header_id = sh.shipment_header_id AND r.vendor_id = pv.vendor_id(+) AND r.locator_id = mil.inventory_location_id(+) AND r.item_id = msi.inventory_item_id AND r.to_organization_id = msi.organization_id AND r.to_organization_id = h.organization_id AND h.organization_id = mp.organization_id AND r.destination_type_code = 'INVENTORY' AND msi.lot_control_code = 2 AND EXISTS (SELECT 1 FROM mtl_lot_eres_gtmp ml WHERE r.interface_transaction_id =ml.transaction_temp_id)
SELECT R.GROUP_ID GROUP_ID
, R.INTERFACE_TRANSACTION_ID INTERFACE_TRANSACTION_ID
, SH.RECEIPT_NUM RECEIPT_NUM
, NULL LINE_NUM
, MSI.CONCATENATED_SEGMENTS ITEM_NO
, NVL (R.ITEM_DESCRIPTION
, MSI.DESCRIPTION) ITEM_DESCRIPTION
, R.TRANSACTION_DATE TRANSACTION_DATE
, PV.VENDOR_NAME VENDOR_NAME
, PV.VENDOR_ID VENDOR_ID
, PV.SEGMENT1 VENDOR_NO
, R.SUBINVENTORY SUBINVENTORY
, MIL.DESCRIPTION LOCATOR
, R.LOCATOR_ID LOCATOR_ID
, R.QUANTITY QUANTITY
, R.UNIT_OF_MEASURE UOM
, R.SECONDARY_QUANTITY SECONDARY_QUANTITY
, R.SECONDARY_UNIT_OF_MEASURE SECONDARY_UOM
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, H.NAME ORGANIZATION_NAME
, R.VENDOR_LOT_NUM VENDOR_LOT_NUM
FROM RCV_TRANSACTIONS_INTERFACE R
, RCV_SHIPMENT_HEADERS SH
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_VENDORS PV
, MTL_ITEM_LOCATIONS MIL
, HR_ALL_ORGANIZATION_UNITS H
, MTL_PARAMETERS MP
WHERE R.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID
AND R.VENDOR_ID = PV.VENDOR_ID(+)
AND R.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND R.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND R.TO_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND R.TO_ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND R.DESTINATION_TYPE_CODE = 'INVENTORY'
AND MSI.LOT_CONTROL_CODE = 2
AND EXISTS (SELECT 1
FROM MTL_LOT_ERES_GTMP ML
WHERE R.INTERFACE_TRANSACTION_ID =ML.TRANSACTION_TEMP_ID)
|
|
|
|