VIEW: APPS.POR_RCV_EXPRESS_ITEMS_V

Source

View Text - Preformatted

SELECT SYSDATE RECEIPT_DATE, PORL.NEED_BY_DATE EXPECTED_RECEIPT_DATE, PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED, 0) - NVL(PORL.QUANTITY_DELIVERED, 0) EXPECTED_RECEIPT_QUANTITY, PORL.UNIT_MEAS_LOOKUP_CODE RECEIPT_UOM, MUOM.UNIT_OF_MEASURE_TL RECEIPT_UOM_TL, MUOM.UOM_CLASS RECEIPT_UOM_CLASS, HOU.NAME SOURCE, TO_NUMBER(NULL) SUPPLIER_ID, PORL.ITEM_DESCRIPTION, PORL.ITEM_ID ITEM_ID, PORH.SEGMENT1 REQUISITION_NUMBER, PORL.REQUISITION_LINE_ID, PORL.REQUISITION_HEADER_ID REQUISITION_HEADER_ID, TO_NUMBER(NULL) PO_HEADER_ID, TO_NUMBER(NULL) PO_LINE_LOCATION_ID, TO_NUMBER(NULL) PO_DISTRIBUTION_ID, PORL.DESTINATION_ORGANIZATION_ID ORGANIZATION_ID, PORL.TO_PERSON_ID REQUESTOR_ID, 'REQ' ORDER_TYPE_CODE, PORL.REQUISITION_LINE_ID KEY_ID, TO_CHAR(NULL) FROM PO_REQUISITION_LINES PORL, PO_REQUISITION_HEADERS PORH, OE_ORDER_HEADERS_ALL OSH, MTL_UNITS_OF_MEASURE MUOM, HR_ALL_ORGANIZATION_UNITS_TL HOU, PO_SYSTEM_PARAMETERS POSP WHERE PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED, 0) - NVL(PORL.QUANTITY_DELIVERED, 0) > 0 AND PORL.SOURCE_TYPE_CODE = 'INVENTORY' AND OSH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID AND OSH.ORIG_SYS_DOCUMENT_REF = PORH.SEGMENT1 AND OSH.SOURCE_DOCUMENT_ID = PORH.REQUISITION_HEADER_ID AND PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID AND MUOM.UNIT_OF_MEASURE (+) = PORL.UNIT_MEAS_LOOKUP_CODE AND HOU.ORGANIZATION_ID = PORL.SOURCE_ORGANIZATION_ID AND HOU.LANGUAGE(+) = USERENV('LANG') AND EXISTS (SELECT 1 FROM RCV_SHIPMENT_LINES RSL WHERE RSL.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID AND RSL.ROUTING_HEADER_ID = 3) UNION ALL SELECT SYSDATE RECEIPT_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED, 0) - NVL(POD.AMOUNT_DELIVERED, 0), POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0)) EXPECTED_RECEIPT_QUANTITY, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POH.CURRENCY_CODE, POL.UNIT_MEAS_LOOKUP_CODE) RECEIPT_UOM, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POH.CURRENCY_CODE, MUOM.UNIT_OF_MEASURE_TL) RECEIPT_UOM_TL, MUOM.UOM_CLASS RECEIPT_UOM_CLASS, POV.VENDOR_NAME SOURCE, POH.VENDOR_ID SUPPLIER_ID, SUBSTR( POL.ITEM_DESCRIPTION, 1, 240) ITEM_DESCRIPTION, POL.ITEM_ID ITEM_ID, PORH.SEGMENT1 REQUISITION_NUMBER, PORL.REQUISITION_LINE_ID REQUISITION_LINE_ID, PORH.REQUISITION_HEADER_ID REQUISITION_HEADER_ID, POH.PO_HEADER_ID, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POD.PO_DISTRIBUTION_ID, POLL.SHIP_TO_ORGANIZATION_ID ORGANIZATION_ID, NVL(POD.DELIVER_TO_PERSON_ID, POH.AGENT_ID) REQUESTOR_ID, 'PO' ORDER_TYPE_CODE, POD.PO_DISTRIBUTION_ID KEY_ID, POL.MATCHING_BASIS FROM MTL_UNITS_OF_MEASURE MUOM, PO_DISTRIBUTIONS_ALL POD, PO_HEADERS_ALL POH, PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL, PO_REQUISITION_HEADERS PORH, PO_REQUISITION_LINES PORL, PO_REQ_DISTRIBUTIONS PORD, PO_VENDORS POV WHERE PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID AND PORL.SOURCE_TYPE_CODE = 'VENDOR' AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID AND PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND POLL.RECEIVING_ROUTING_ID = 3 AND POLL.PAYMENT_TYPE IS NULL AND NVL(POL.ORDER_TYPE_LOOKUP_CODE, 'QUANTITY') <> 'RATE' AND DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_DELIVERED, 0) - NVL(POD.AMOUNT_CANCELLED, 0), POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED, 0) - NVL(POD.QUANTITY_CANCELLED, 0)) > 0 AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID AND POH.VENDOR_ID = POV.VENDOR_ID AND POLL.PO_LINE_ID = POL.PO_LINE_ID AND POL.UNIT_MEAS_LOOKUP_CODE = MUOM.UNIT_OF_MEASURE(+)

View Text - HTML Formatted

SELECT SYSDATE RECEIPT_DATE
, PORL.NEED_BY_DATE EXPECTED_RECEIPT_DATE
, PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED
, 0) - NVL(PORL.QUANTITY_DELIVERED
, 0) EXPECTED_RECEIPT_QUANTITY
, PORL.UNIT_MEAS_LOOKUP_CODE RECEIPT_UOM
, MUOM.UNIT_OF_MEASURE_TL RECEIPT_UOM_TL
, MUOM.UOM_CLASS RECEIPT_UOM_CLASS
, HOU.NAME SOURCE
, TO_NUMBER(NULL) SUPPLIER_ID
, PORL.ITEM_DESCRIPTION
, PORL.ITEM_ID ITEM_ID
, PORH.SEGMENT1 REQUISITION_NUMBER
, PORL.REQUISITION_LINE_ID
, PORL.REQUISITION_HEADER_ID REQUISITION_HEADER_ID
, TO_NUMBER(NULL) PO_HEADER_ID
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, TO_NUMBER(NULL) PO_DISTRIBUTION_ID
, PORL.DESTINATION_ORGANIZATION_ID ORGANIZATION_ID
, PORL.TO_PERSON_ID REQUESTOR_ID
, 'REQ' ORDER_TYPE_CODE
, PORL.REQUISITION_LINE_ID KEY_ID
, TO_CHAR(NULL)
FROM PO_REQUISITION_LINES PORL
, PO_REQUISITION_HEADERS PORH
, OE_ORDER_HEADERS_ALL OSH
, MTL_UNITS_OF_MEASURE MUOM
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PO_SYSTEM_PARAMETERS POSP
WHERE PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED
, 0) - NVL(PORL.QUANTITY_DELIVERED
, 0) > 0
AND PORL.SOURCE_TYPE_CODE = 'INVENTORY'
AND OSH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID
AND OSH.ORIG_SYS_DOCUMENT_REF = PORH.SEGMENT1
AND OSH.SOURCE_DOCUMENT_ID = PORH.REQUISITION_HEADER_ID
AND PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID
AND MUOM.UNIT_OF_MEASURE (+) = PORL.UNIT_MEAS_LOOKUP_CODE
AND HOU.ORGANIZATION_ID = PORL.SOURCE_ORGANIZATION_ID
AND HOU.LANGUAGE(+) = USERENV('LANG')
AND EXISTS (SELECT 1
FROM RCV_SHIPMENT_LINES RSL
WHERE RSL.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID
AND RSL.ROUTING_HEADER_ID = 3) UNION ALL SELECT SYSDATE RECEIPT_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0) - NVL(POD.AMOUNT_DELIVERED
, 0)
, POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0)) EXPECTED_RECEIPT_QUANTITY
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POH.CURRENCY_CODE
, POL.UNIT_MEAS_LOOKUP_CODE) RECEIPT_UOM
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POH.CURRENCY_CODE
, MUOM.UNIT_OF_MEASURE_TL) RECEIPT_UOM_TL
, MUOM.UOM_CLASS RECEIPT_UOM_CLASS
, POV.VENDOR_NAME SOURCE
, POH.VENDOR_ID SUPPLIER_ID
, SUBSTR( POL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, POL.ITEM_ID ITEM_ID
, PORH.SEGMENT1 REQUISITION_NUMBER
, PORL.REQUISITION_LINE_ID REQUISITION_LINE_ID
, PORH.REQUISITION_HEADER_ID REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, POLL.SHIP_TO_ORGANIZATION_ID ORGANIZATION_ID
, NVL(POD.DELIVER_TO_PERSON_ID
, POH.AGENT_ID) REQUESTOR_ID
, 'PO' ORDER_TYPE_CODE
, POD.PO_DISTRIBUTION_ID KEY_ID
, POL.MATCHING_BASIS
FROM MTL_UNITS_OF_MEASURE MUOM
, PO_DISTRIBUTIONS_ALL POD
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL POLL
, PO_LINES_ALL POL
, PO_REQUISITION_HEADERS PORH
, PO_REQUISITION_LINES PORL
, PO_REQ_DISTRIBUTIONS PORD
, PO_VENDORS POV
WHERE PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID
AND PORL.SOURCE_TYPE_CODE = 'VENDOR'
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID
AND PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND NVL(POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CANCELLED')
AND POLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POLL.RECEIVING_ROUTING_ID = 3
AND POLL.PAYMENT_TYPE IS NULL
AND NVL(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY') <> 'RATE'
AND DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_DELIVERED
, 0) - NVL(POD.AMOUNT_CANCELLED
, 0)
, POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED
, 0) - NVL(POD.QUANTITY_CANCELLED
, 0)) > 0
AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MUOM.UNIT_OF_MEASURE(+)