VIEW: APPS.RCV_RECEIPT_CONFIRMATION_LPN_V

Source

View Text - Preformatted

SELECT DISTINCT wlpn1.license_plate_number lpn, msibk.concatenated_segments item, mp.organization_code, wlpn2.license_plate_number parent_lpn, wlpn1.gross_weight_uom_code, wlpn1.gross_weight, wlpn1.content_volume_uom_code, wlpn1.content_volume, wlpn1.tare_weight_uom_code, wlpn1.tare_weight, wlpn1.lpn_context, wlpn3.license_plate_number outermost_lpn, rsh.shipment_header_id, rt.quantity, rt.unit_of_measure, wlpn1.attribute_category, wlpn1.attribute1, wlpn1.attribute2, wlpn1.attribute3, wlpn1.attribute4, wlpn1.attribute5, wlpn1.attribute6, wlpn1.attribute7, wlpn1.attribute8, wlpn1.attribute9, wlpn1.attribute10, wlpn1.attribute11, wlpn1.attribute12, wlpn1.attribute13, wlpn1.attribute14, wlpn1.attribute15, rsl.item_id, rt.xml_document_id, rt.receipt_confirmation_extracted FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions rt, wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2, wms_license_plate_numbers wlpn3, mtl_material_transactions mmt, mtl_system_items_b_kfv msibk, mtl_parameters mp WHERE rsh.shipment_header_id = rt.shipment_header_id AND rsl.shipment_header_id = rt.shipment_header_id AND rt.transfer_lpn_id IS NOT NULL AND rt.transfer_lpn_id = wlpn1.lpn_id (+) AND wlpn1.parent_lpn_id = wlpn2.lpn_id (+) AND wlpn1.outermost_lpn_id = wlpn3.lpn_id (+) AND rt.transaction_id = mmt.rcv_transaction_id (+) AND wlpn1.inventory_item_id = msibk.inventory_item_id (+) AND wlpn1.organization_id = msibk.organization_id (+) AND wlpn1.organization_id = mp.organization_id (+) AND ( rt.transaction_type IN ( 'DELIVER' ) OR ( rt.transaction_type IN ( 'CORRECT', 'RETURN TO RECEIVING' ) AND EXISTS (SELECT '1' FROM rcv_transactions rt2 WHERE rt.parent_transaction_id = rt2.transaction_id AND rt2.transaction_type = 'DELIVER') ) OR ( rt.transaction_type IN ( 'CORRECT' ) AND EXISTS (SELECT '1' FROM rcv_transactions rt3 WHERE rt.parent_transaction_id = rt3.transaction_id AND rt3.transaction_type = 'RETURN TO RECEIVING') ) )

View Text - HTML Formatted

SELECT DISTINCT WLPN1.LICENSE_PLATE_NUMBER LPN
, MSIBK.CONCATENATED_SEGMENTS ITEM
, MP.ORGANIZATION_CODE
, WLPN2.LICENSE_PLATE_NUMBER PARENT_LPN
, WLPN1.GROSS_WEIGHT_UOM_CODE
, WLPN1.GROSS_WEIGHT
, WLPN1.CONTENT_VOLUME_UOM_CODE
, WLPN1.CONTENT_VOLUME
, WLPN1.TARE_WEIGHT_UOM_CODE
, WLPN1.TARE_WEIGHT
, WLPN1.LPN_CONTEXT
, WLPN3.LICENSE_PLATE_NUMBER OUTERMOST_LPN
, RSH.SHIPMENT_HEADER_ID
, RT.QUANTITY
, RT.UNIT_OF_MEASURE
, WLPN1.ATTRIBUTE_CATEGORY
, WLPN1.ATTRIBUTE1
, WLPN1.ATTRIBUTE2
, WLPN1.ATTRIBUTE3
, WLPN1.ATTRIBUTE4
, WLPN1.ATTRIBUTE5
, WLPN1.ATTRIBUTE6
, WLPN1.ATTRIBUTE7
, WLPN1.ATTRIBUTE8
, WLPN1.ATTRIBUTE9
, WLPN1.ATTRIBUTE10
, WLPN1.ATTRIBUTE11
, WLPN1.ATTRIBUTE12
, WLPN1.ATTRIBUTE13
, WLPN1.ATTRIBUTE14
, WLPN1.ATTRIBUTE15
, RSL.ITEM_ID
, RT.XML_DOCUMENT_ID
, RT.RECEIPT_CONFIRMATION_EXTRACTED
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_PARAMETERS MP
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RT.TRANSFER_LPN_ID IS NOT NULL
AND RT.TRANSFER_LPN_ID = WLPN1.LPN_ID (+)
AND WLPN1.PARENT_LPN_ID = WLPN2.LPN_ID (+)
AND WLPN1.OUTERMOST_LPN_ID = WLPN3.LPN_ID (+)
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID (+)
AND WLPN1.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID (+)
AND WLPN1.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID (+)
AND WLPN1.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)
AND ( RT.TRANSACTION_TYPE IN ( 'DELIVER' ) OR ( RT.TRANSACTION_TYPE IN ( 'CORRECT'
, 'RETURN TO RECEIVING' )
AND EXISTS (SELECT '1'
FROM RCV_TRANSACTIONS RT2
WHERE RT.PARENT_TRANSACTION_ID = RT2.TRANSACTION_ID
AND RT2.TRANSACTION_TYPE = 'DELIVER') ) OR ( RT.TRANSACTION_TYPE IN ( 'CORRECT' )
AND EXISTS (SELECT '1'
FROM RCV_TRANSACTIONS RT3
WHERE RT.PARENT_TRANSACTION_ID = RT3.TRANSACTION_ID
AND RT3.TRANSACTION_TYPE = 'RETURN TO RECEIVING') ) )