eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.RCV_RECEIPT_CONFIRMATION_V

Source

View Text - Preformatted


SELECT distinct mp1.organization_code Warehouse, mp2.organization_code Transfer_Warehouse, rsh.receipt_num Receipt, mck.concatenated_segments Category, rt.source_document_code Document_Type, decode (rt.source_document_code, 'PO',poh.segment1, 'RMA', oeh.order_number, 'REQ', prh.segment1, 'INVENTORY', rsh.shipment_num, null) Document_Number, decode (rt.source_document_code, 'PO',pol.line_num, 'RMA', oel.line_number, 'REQ', prl.line_num, 'INVENTORY', rsl.line_num, null) Document_Line_Number, rsh.shipment_num Shipment, rsh.asn_type Shipment_Type, rsh.shipped_date Shipped_Date, pov.vendor_name Vendor_Name, pvs. vendor_site_code Vendor_Site_Code, hzp.party_name Customer_Name, hzca.account_number Customer_Number, rsl.line_num Shipment_Line_Number, msibk.concatenated_segments Item, rsl.item_id Item_ID, rsl.item_description Item_Description, rsl.item_revision Revision, rt.transaction_type Transaction_Type, rt.transaction_date Transaction_Date, rt.creation_date Creation_date, rt.transaction_id Transaction_ID, rt.parent_transaction_id Parent_Transaction_ID, hre.full_name Deliver_To_Person_Name, hrl.location_code Deliver_To_Location_Code, rt.inspection_status_code Inspection_Status, inv.transaction_id MMT_Transaction_ID, inv.subinventory Subinventory, inv. transfer_subinventory Transfer_Subinventory, milk1.concatenated_segments Locator, milk2.concatenated_segments Transfer_Locator, wlpn1.license_plate_number LPN, wlpn2.license_plate_number Transfer_LPN, wlpn3.license_plate_number Parent_LPN, inv.mmt_qty Transaction_Quantity, tuom.unit_of_measure_tl Transaction_UOM, inv.primary_quantity Primary_Quantity, puom.unit_of_measure_tl Primary_UOM, Decode(msibk.tracking_quantity_ind,'PS',inv.secondary_quantity,NULL) Secondary_Quantity, Decode(msibk.tracking_quantity_ind,'PS',suom.unit_of_measure_tl,null) Secondary_UOM, Decode(inv.serial_number, Null,0, abs(inv.mmt_qty)/ inv.mmt_qty) Serial_Quantity, inv.lot_number Lot, inv.mtln_qty Lot_Quantity, inv.serial_number Serial, rt.receipt_confirmation_extracted RC_Extracted from ( select distinct mmt.transaction_id, mmt.Transaction_Source_Type_id Transaction_Source_Type, mmt.Transaction_Type_id Transaction_Type, mmt.Transaction_Action_id Transaction_Action, mmt.rcv_transaction_id, mmt.inventory_item_id, mmt.organization_id, mmt.transfer_organization_id, mmt.subinventory_code Subinventory, mmt.transfer_subinventory, mmt.locator_id, mmt.transfer_locator_id, mmt.lpn_id, decode(mmt.transaction_type_id,1, mmt.content_lpn_id, mmt.transfer_lpn_id) transfer_lpn_id, mmt.transaction_quantity mmt_qty, mmt.transaction_uom, mmt.primary_quantity, mmt.secondary_transaction_quantity secondary_quantity, mtln.lot_number, mtln.transaction_quantity mtln_qty, mut.serial_number from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln, mtl_unit_transactions mut WHERE mmt.transaction_id = mtln.transaction_id (+) and mmt.transaction_id = mut.transaction_id (+) and mtln.serial_transaction_id is null UNION select distinct mmt.transaction_id, mmt.Transaction_Source_Type_id Transaction_Source_Type, mmt.Transaction_Type_id Transaction_Type, mmt.Transaction_Action_id Transaction_Action, mmt.rcv_transaction_id, mmt.inventory_item_id, mmt.organization_id, mmt.transfer_organization_id, mmt.subinventory_code Subinventory, mmt.transfer_subinventory, mmt.locator_id, mmt.transfer_locator_id, mmt.lpn_id, decode(mmt.transaction_type_id,1, mmt.content_lpn_id, mmt.transfer_lpn_id) transfer_lpn_id, mmt.transaction_quantity mmt_qty, mmt.transaction_uom, mmt.primary_quantity, mmt.secondary_transaction_quantity secondary_quantity, mtln.lot_number, mtln.transaction_quantity mtln_qty, mut.serial_number from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln, mtl_unit_transactions mut where mmt.transaction_id = mtln.transaction_id (+) and mtln.serial_transaction_id is not null and mtln.serial_transaction_id = mut.transaction_id (+) ) inv, rcv_transactions rt, rcv_shipment_lines rsl, rcv_shipment_headers rsh, mtl_item_categories mic, mtl_categories_kfv mck, mtl_system_items_b_kfv msibk, mtl_parameters mp1, mtl_parameters mp2, wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2, wms_license_plate_numbers wlpn3, mtl_units_of_measure tuom, mtl_units_of_measure puom, mtl_units_of_measure suom, mtl_item_locations_kfv milk1, mtl_item_locations_kfv milk2, po_headers_all poh, po_lines_all pol, po_requisition_headers_all prh, po_requisition_lines_all prl, oe_order_headers_all oeh, oe_order_lines_all oel, hr_employees hre, hr_locations_all hrl, po_vendors pov, po_vendor_sites_all pvs, hz_parties hzp, hz_cust_accounts hzca where ((inv.Transaction_Source_Type = 1 and inv.Transaction_Type = 18 and inv.Transaction_Action = 27) or (inv.Transaction_Source_Type = 1 and inv.Transaction_Type = 71 and inv.Transaction_Action = 29) or (inv.Transaction_Source_Type = 1 and inv.Transaction_Type = 36 and inv.Transaction_Action = 1) or (inv.Transaction_Source_Type = 7 and inv.Transaction_Type = 61 and inv.Transaction_Action = 12) or (inv.Transaction_Source_Type = 7 and inv.Transaction_Type = 72 and inv.Transaction_Action = 29) or (inv.Transaction_Source_Type = 12 and inv.Transaction_Type = 15 and inv.Transaction_Action = 27) or (inv.Transaction_Source_Type = 13 and inv.Transaction_Type = 12 and inv.Transaction_Action = 12) or (inv.Transaction_Source_Type = 13 and inv.Transaction_Type = 70 and inv.Transaction_Action = 29)) and inv.rcv_transaction_id = rt.transaction_id and rt.shipment_line_id = rsl.shipment_line_id and rt.shipment_header_id = rsh.shipment_header_id and inv.inventory_item_id = mic.inventory_item_id and inv.organization_id = mic.organization_id and mic.category_id = mck.category_id and inv.organization_id = mp1.organization_id and inv.transfer_organization_id = mp2.organization_id (+) and inv.lpn_id = wlpn1.lpn_id (+) and inv. transfer_lpn_id = wlpn2.lpn_id (+) and wlpn2.parent_lpn_id = wlpn3.lpn_id (+) and inv.transaction_uom = tuom. uom_code and inv.inventory_item_id = msibk.inventory_item_id and inv.organization_id = msibk.organization_id and msibk.primary_uom_code = puom.uom_code and msibk.secondary_uom_code = suom.uom_code (+) and inv.locator_id = milk1.inventory_location_id (+) and inv.organization_id = milk1.organization_id (+) and inv.transfer_locator_id = milk2.inventory_location_id (+) and inv.transfer_organization_id = milk2.organization_id (+) and rt.po_header_id = poh.po_header_id (+) and rt.po_line_id = pol.po_line_id (+) and rt.oe_order_header_id = oeh.header_id (+) and rt.oe_order_line_id = oel.line_id (+) and rt.requisition_line_id = prl.requisition_line_id (+) and prl.requisition_header_id = prh.requisition_header_id (+) and rt.deliver_to_person_id = hre.employee_id (+) and rt.deliver_to_location_id = hrl.location_id (+) and rt.organization_id = hrl.inventory_organization_id (+) and rt.vendor_id = pov.vendor_id (+) and rt.vendor_site_id = pvs.vendor_site_id (+) and rt.vendor_id = pvs.vendor_id (+) and rt.customer_id = hzca.cust_account_id (+) and hzca.party_id = hzp.party_id (+)

View Text - HTML Formatted

SELECT DISTINCT MP1.ORGANIZATION_CODE WAREHOUSE
, MP2.ORGANIZATION_CODE TRANSFER_WAREHOUSE
, RSH.RECEIPT_NUM RECEIPT
, MCK.CONCATENATED_SEGMENTS CATEGORY
, RT.SOURCE_DOCUMENT_CODE DOCUMENT_TYPE
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, 'RMA'
, OEH.ORDER_NUMBER
, 'REQ'
, PRH.SEGMENT1
, 'INVENTORY'
, RSH.SHIPMENT_NUM
, NULL) DOCUMENT_NUMBER
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, 'RMA'
, OEL.LINE_NUMBER
, 'REQ'
, PRL.LINE_NUM
, 'INVENTORY'
, RSL.LINE_NUM
, NULL) DOCUMENT_LINE_NUMBER
, RSH.SHIPMENT_NUM SHIPMENT
, RSH.ASN_TYPE SHIPMENT_TYPE
, RSH.SHIPPED_DATE SHIPPED_DATE
, POV.VENDOR_NAME VENDOR_NAME
, PVS. VENDOR_SITE_CODE VENDOR_SITE_CODE
, HZP.PARTY_NAME CUSTOMER_NAME
, HZCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, RSL.LINE_NUM SHIPMENT_LINE_NUMBER
, MSIBK.CONCATENATED_SEGMENTS ITEM
, RSL.ITEM_ID ITEM_ID
, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, RSL.ITEM_REVISION REVISION
, RT.TRANSACTION_TYPE TRANSACTION_TYPE
, RT.TRANSACTION_DATE TRANSACTION_DATE
, RT.CREATION_DATE CREATION_DATE
, RT.TRANSACTION_ID TRANSACTION_ID
, RT.PARENT_TRANSACTION_ID PARENT_TRANSACTION_ID
, HRE.FULL_NAME DELIVER_TO_PERSON_NAME
, HRL.LOCATION_CODE DELIVER_TO_LOCATION_CODE
, RT.INSPECTION_STATUS_CODE INSPECTION_STATUS
, INV.TRANSACTION_ID MMT_TRANSACTION_ID
, INV.SUBINVENTORY SUBINVENTORY
, INV. TRANSFER_SUBINVENTORY TRANSFER_SUBINVENTORY
, MILK1.CONCATENATED_SEGMENTS LOCATOR
, MILK2.CONCATENATED_SEGMENTS TRANSFER_LOCATOR
, WLPN1.LICENSE_PLATE_NUMBER LPN
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN
, WLPN3.LICENSE_PLATE_NUMBER PARENT_LPN
, INV.MMT_QTY TRANSACTION_QUANTITY
, TUOM.UNIT_OF_MEASURE_TL TRANSACTION_UOM
, INV.PRIMARY_QUANTITY PRIMARY_QUANTITY
, PUOM.UNIT_OF_MEASURE_TL PRIMARY_UOM
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, INV.SECONDARY_QUANTITY
, NULL) SECONDARY_QUANTITY
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, SUOM.UNIT_OF_MEASURE_TL
, NULL) SECONDARY_UOM
, DECODE(INV.SERIAL_NUMBER
, NULL
, 0
, ABS(INV.MMT_QTY)/ INV.MMT_QTY) SERIAL_QUANTITY
, INV.LOT_NUMBER LOT
, INV.MTLN_QTY LOT_QUANTITY
, INV.SERIAL_NUMBER SERIAL
, RT.RECEIPT_CONFIRMATION_EXTRACTED RC_EXTRACTED
FROM ( SELECT DISTINCT MMT.TRANSACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID TRANSACTION_SOURCE_TYPE
, MMT.TRANSACTION_TYPE_ID TRANSACTION_TYPE
, MMT.TRANSACTION_ACTION_ID TRANSACTION_ACTION
, MMT.RCV_TRANSACTION_ID
, MMT.INVENTORY_ITEM_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE SUBINVENTORY
, MMT.TRANSFER_SUBINVENTORY
, MMT.LOCATOR_ID
, MMT.TRANSFER_LOCATOR_ID
, MMT.LPN_ID
, DECODE(MMT.TRANSACTION_TYPE_ID
, 1
, MMT.CONTENT_LPN_ID
, MMT.TRANSFER_LPN_ID) TRANSFER_LPN_ID
, MMT.TRANSACTION_QUANTITY MMT_QTY
, MMT.TRANSACTION_UOM
, MMT.PRIMARY_QUANTITY
, MMT.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY
, MTLN.LOT_NUMBER
, MTLN.TRANSACTION_QUANTITY MTLN_QTY
, MUT.SERIAL_NUMBER
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_UNIT_TRANSACTIONS MUT
WHERE MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND MMT.TRANSACTION_ID = MUT.TRANSACTION_ID (+)
AND MTLN.SERIAL_TRANSACTION_ID IS NULL UNION SELECT DISTINCT MMT.TRANSACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID TRANSACTION_SOURCE_TYPE
, MMT.TRANSACTION_TYPE_ID TRANSACTION_TYPE
, MMT.TRANSACTION_ACTION_ID TRANSACTION_ACTION
, MMT.RCV_TRANSACTION_ID
, MMT.INVENTORY_ITEM_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE SUBINVENTORY
, MMT.TRANSFER_SUBINVENTORY
, MMT.LOCATOR_ID
, MMT.TRANSFER_LOCATOR_ID
, MMT.LPN_ID
, DECODE(MMT.TRANSACTION_TYPE_ID
, 1
, MMT.CONTENT_LPN_ID
, MMT.TRANSFER_LPN_ID) TRANSFER_LPN_ID
, MMT.TRANSACTION_QUANTITY MMT_QTY
, MMT.TRANSACTION_UOM
, MMT.PRIMARY_QUANTITY
, MMT.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY
, MTLN.LOT_NUMBER
, MTLN.TRANSACTION_QUANTITY MTLN_QTY
, MUT.SERIAL_NUMBER
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_UNIT_TRANSACTIONS MUT
WHERE MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND MTLN.SERIAL_TRANSACTION_ID IS NOT NULL
AND MTLN.SERIAL_TRANSACTION_ID = MUT.TRANSACTION_ID (+) ) INV
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, MTL_ITEM_CATEGORIES MIC
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
, MTL_UNITS_OF_MEASURE TUOM
, MTL_UNITS_OF_MEASURE PUOM
, MTL_UNITS_OF_MEASURE SUOM
, MTL_ITEM_LOCATIONS_KFV MILK1
, MTL_ITEM_LOCATIONS_KFV MILK2
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, HR_EMPLOYEES HRE
, HR_LOCATIONS_ALL HRL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
WHERE ((INV.TRANSACTION_SOURCE_TYPE = 1
AND INV.TRANSACTION_TYPE = 18
AND INV.TRANSACTION_ACTION = 27) OR (INV.TRANSACTION_SOURCE_TYPE = 1
AND INV.TRANSACTION_TYPE = 71
AND INV.TRANSACTION_ACTION = 29) OR (INV.TRANSACTION_SOURCE_TYPE = 1
AND INV.TRANSACTION_TYPE = 36
AND INV.TRANSACTION_ACTION = 1) OR (INV.TRANSACTION_SOURCE_TYPE = 7
AND INV.TRANSACTION_TYPE = 61
AND INV.TRANSACTION_ACTION = 12) OR (INV.TRANSACTION_SOURCE_TYPE = 7
AND INV.TRANSACTION_TYPE = 72
AND INV.TRANSACTION_ACTION = 29) OR (INV.TRANSACTION_SOURCE_TYPE = 12
AND INV.TRANSACTION_TYPE = 15
AND INV.TRANSACTION_ACTION = 27) OR (INV.TRANSACTION_SOURCE_TYPE = 13
AND INV.TRANSACTION_TYPE = 12
AND INV.TRANSACTION_ACTION = 12) OR (INV.TRANSACTION_SOURCE_TYPE = 13
AND INV.TRANSACTION_TYPE = 70
AND INV.TRANSACTION_ACTION = 29))
AND INV.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND INV.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND INV.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIC.CATEGORY_ID = MCK.CATEGORY_ID
AND INV.ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND INV.TRANSFER_ORGANIZATION_ID = MP2.ORGANIZATION_ID (+)
AND INV.LPN_ID = WLPN1.LPN_ID (+)
AND INV. TRANSFER_LPN_ID = WLPN2.LPN_ID (+)
AND WLPN2.PARENT_LPN_ID = WLPN3.LPN_ID (+)
AND INV.TRANSACTION_UOM = TUOM. UOM_CODE
AND INV.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND INV.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID
AND MSIBK.PRIMARY_UOM_CODE = PUOM.UOM_CODE
AND MSIBK.SECONDARY_UOM_CODE = SUOM.UOM_CODE (+)
AND INV.LOCATOR_ID = MILK1.INVENTORY_LOCATION_ID (+)
AND INV.ORGANIZATION_ID = MILK1.ORGANIZATION_ID (+)
AND INV.TRANSFER_LOCATOR_ID = MILK2.INVENTORY_LOCATION_ID (+)
AND INV.TRANSFER_ORGANIZATION_ID = MILK2.ORGANIZATION_ID (+)
AND RT.PO_HEADER_ID = POH.PO_HEADER_ID (+)
AND RT.PO_LINE_ID = POL.PO_LINE_ID (+)
AND RT.OE_ORDER_HEADER_ID = OEH.HEADER_ID (+)
AND RT.OE_ORDER_LINE_ID = OEL.LINE_ID (+)
AND RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID (+)
AND RT.DELIVER_TO_PERSON_ID = HRE.EMPLOYEE_ID (+)
AND RT.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID (+)
AND RT.ORGANIZATION_ID = HRL.INVENTORY_ORGANIZATION_ID (+)
AND RT.VENDOR_ID = POV.VENDOR_ID (+)
AND RT.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND RT.VENDOR_ID = PVS.VENDOR_ID (+)
AND RT.CUSTOMER_ID = HZCA.CUST_ACCOUNT_ID (+)
AND HZCA.PARTY_ID = HZP.PARTY_ID (+)