VIEW: APPS.POR_RCV_ORD_REQUESTER_V

Source

View Text - Preformatted

SELECT SYSDATE RECEIPT_DATE, POD.DELIVER_TO_PERSON_ID REQUESTOR_ID, PPF.FULL_NAME REQUESTER, POLL.PO_HEADER_ID PO_HEADER_ID, POH.SEGMENT1 PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, POL.LINE_NUM PO_LINE_NUMBER, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID, POL.ITEM_ID ITEM_ID, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUM.UOM_CLASS UOM_CLASS, POD.QUANTITY_DELIVERED, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0)),0) EXPECTED_RECEIPT_QTY, SUBSTR( POL.ITEM_DESCRIPTION, 1, 240) ITEM_DESCRIPTION, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, POH.VENDOR_ID VENDOR_ID, POV.VENDOR_NAME SOURCE, POH.VENDOR_SITE_ID VENDOR_SITE_ID, POVS.VENDOR_SITE_CODE SUPPLIER_SITE, POLL.NEED_BY_DATE NEED_BY_DATE, POLL.PROMISED_DATE PROMISED_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, POD.QUANTITY_ORDERED ORDERED_QTY, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POD.PO_DISTRIBUTION_ID, POD.DELIVER_TO_LOCATION_ID, POD.REQ_DISTRIBUTION_ID, POD.DISTRIBUTION_NUM, POL.VENDOR_PRODUCT_NUM, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) PO_NUM_REL_NUM, TO_NUMBER(NULL), TO_NUMBER(NULL), POR_RCV_TRANSACTION_SV.GET_SHIPMENT_NUM('PO', POLL.LINE_LOCATION_ID), 'PO' FROM PO_RELEASES POR, MTL_UNITS_OF_MEASURE MUM, PO_VENDORS POV, PO_VENDOR_SITES POVS, PO_LINES POL, PO_DISTRIBUTIONS POD, PO_HEADERS POH, PO_LINE_LOCATIONS POLL, PER_PEOPLE_F PPF WHERE POD.DELIVER_TO_PERSON_ID IS NOT NULL AND PPF.PERSON_ID = POD.DELIVER_TO_PERSON_ID AND PPF.EFFECTIVE_END_DATE = (SELECT MAX(PPF.EFFECTIVE_END_DATE) FROM PER_PEOPLE_F PPF WHERE PPF.PERSON_ID = POD.DELIVER_TO_PERSON_ID) AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED, 0) - NVL(POD.QUANTITY_CANCELLED, 0)) > 0 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 POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE UNION ALL SELECT /*+ INDEX(POH PO_HEADERS_N3) */ SYSDATE RECEIPT_DATE, POH.AGENT_ID REQUESTOR_ID, PPF.FULL_NAME REQUESTER, POLL.PO_HEADER_ID PO_HEADER_ID, POH.SEGMENT1 PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, POL.LINE_NUM PO_LINE_NUMBER, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID, POL.ITEM_ID ITEM_ID, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUM.UOM_CLASS UOM_CLASS, POD.QUANTITY_DELIVERED, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0)),0) EXPECTED_RECEIPT_QTY, SUBSTR( POL.ITEM_DESCRIPTION, 1, 240) ITEM_DESCRIPTION, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, POH.VENDOR_ID VENDOR_ID, POV.VENDOR_NAME SOURCE, POH.VENDOR_SITE_ID VENDOR_SITE_ID, POVS.VENDOR_SITE_CODE SUPPLIER_SITE, POLL.NEED_BY_DATE NEED_BY_DATE, POLL.PROMISED_DATE PROMISED_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, POD.QUANTITY_ORDERED ORDERED_QTY, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, POD.PO_DISTRIBUTION_ID, POD.DELIVER_TO_LOCATION_ID, POD.REQ_DISTRIBUTION_ID, POD.DISTRIBUTION_NUM, POL.VENDOR_PRODUCT_NUM, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) PO_NUM_REL_NUM, TO_NUMBER(NULL), TO_NUMBER(NULL), POR_RCV_TRANSACTION_SV.GET_SHIPMENT_NUM('PO', POLL.LINE_LOCATION_ID), 'PO' FROM PO_RELEASES POR, MTL_UNITS_OF_MEASURE MUM, PO_VENDORS POV, PO_VENDOR_SITES POVS, PO_LINES POL, PO_DISTRIBUTIONS POD, PO_HEADERS POH, PO_LINE_LOCATIONS POLL, PER_PEOPLE_F PPF WHERE POD.DELIVER_TO_PERSON_ID IS NULL AND PPF.PERSON_ID = POH.AGENT_ID AND PPF.EFFECTIVE_END_DATE = (SELECT MAX(PPF.EFFECTIVE_END_DATE) FROM PER_PEOPLE_F PPF WHERE PPF.PERSON_ID = POH.AGENT_ID) AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED, 0) - NVL(POD.QUANTITY_CANCELLED, 0)) > 0 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 POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE UNION ALL SELECT SYSDATE RECEIPT_DATE, PORL.TO_PERSON_ID REQUESTOR_ID, PPF.FULL_NAME REQUESTER, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), PORL.DESTINATION_ORGANIZATION_ID TO_ORGANIZATION_ID, PORL.ITEM_ID ITEM_ID, PORL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUM.UOM_CLASS UOM_CLASS, PORL.QUANTITY_DELIVERED, PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED, 0) - NVL(PORL.QUANTITY_DELIVERED, 0) EXPECTED_RECEIPT_QTY, PORL.ITEM_DESCRIPTION, PORL.DELIVER_TO_LOCATION_ID SHIP_TO_LOCATION_ID, TO_NUMBER(NULL), OOD.NAME, TO_NUMBER(NULL), NULL, PORL.NEED_BY_DATE NEED_BY_DATE, TO_DATE(NULL), PORL.NEED_BY_DATE EXPECTED_RECEIPT_DATE, PORL.QUANTITY ORDERED_QTY, PORL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM, PORL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, TO_NUMBER(NULL), PORL.DELIVER_TO_LOCATION_ID, TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PORL.REQUISITION_HEADER_ID), PORL.REQUISITION_LINE_ID) PO_NUM_REL_NUM, TO_NUMBER(PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PORL.REQUISITION_HEADER_ID), PORL.REQUISITION_LINE_ID)) ORDER_NUMBER, PORL.REQUISITION_LINE_ID, POR_RCV_TRANSACTION_SV.GET_SHIPMENT_NUM('REQ', PORL.REQUISITION_LINE_ID), 'REQ' FROM PO_REQUISITION_LINES PORL, PER_PEOPLE_F PPF, MTL_UNITS_OF_MEASURE MUM, HR_ALL_ORGANIZATION_UNITS_TL OOD WHERE PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED, 0) - NVL(PORL.QUANTITY_DELIVERED, 0) > 0 AND PORL.SOURCE_TYPE_CODE = 'INVENTORY' AND PPF.PERSON_ID = PORL.TO_PERSON_ID AND PPF.EFFECTIVE_END_DATE = (SELECT MAX(PPF.EFFECTIVE_END_DATE) FROM PER_PEOPLE_F PPF WHERE PPF.PERSON_ID = PORL.TO_PERSON_ID) AND MUM.UNIT_OF_MEASURE (+) = PORL.UNIT_MEAS_LOOKUP_CODE AND OOD.ORGANIZATION_ID = PORL.SOURCE_ORGANIZATION_ID AND OOD.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)

View Text - HTML Formatted

SELECT SYSDATE RECEIPT_DATE
, POD.DELIVER_TO_PERSON_ID REQUESTOR_ID
, PPF.FULL_NAME REQUESTER
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, POL.LINE_NUM PO_LINE_NUMBER
, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, POL.ITEM_ID ITEM_ID
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUM.UOM_CLASS UOM_CLASS
, POD.QUANTITY_DELIVERED
, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))
, 0) EXPECTED_RECEIPT_QTY
, SUBSTR( POL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, POH.VENDOR_ID VENDOR_ID
, POV.VENDOR_NAME SOURCE
, POH.VENDOR_SITE_ID VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE SUPPLIER_SITE
, POLL.NEED_BY_DATE NEED_BY_DATE
, POLL.PROMISED_DATE PROMISED_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.QUANTITY_ORDERED ORDERED_QTY
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POD.PO_DISTRIBUTION_ID
, POD.DELIVER_TO_LOCATION_ID
, POD.REQ_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, POL.VENDOR_PRODUCT_NUM
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM) PO_NUM_REL_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, POR_RCV_TRANSACTION_SV.GET_SHIPMENT_NUM('PO'
, POLL.LINE_LOCATION_ID)
, 'PO'
FROM PO_RELEASES POR
, MTL_UNITS_OF_MEASURE MUM
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
, PER_PEOPLE_F PPF
WHERE POD.DELIVER_TO_PERSON_ID IS NOT NULL
AND PPF.PERSON_ID = POD.DELIVER_TO_PERSON_ID
AND PPF.EFFECTIVE_END_DATE = (SELECT MAX(PPF.EFFECTIVE_END_DATE)
FROM PER_PEOPLE_F PPF
WHERE PPF.PERSON_ID = POD.DELIVER_TO_PERSON_ID)
AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED
, 0) - NVL(POD.QUANTITY_CANCELLED
, 0)) > 0
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 POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE UNION ALL SELECT /*+ INDEX(POH PO_HEADERS_N3) */ SYSDATE RECEIPT_DATE
, POH.AGENT_ID REQUESTOR_ID
, PPF.FULL_NAME REQUESTER
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, POL.LINE_NUM PO_LINE_NUMBER
, POLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, POLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, POL.ITEM_ID ITEM_ID
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUM.UOM_CLASS UOM_CLASS
, POD.QUANTITY_DELIVERED
, NVL((POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))
, 0) EXPECTED_RECEIPT_QTY
, SUBSTR( POL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, POH.VENDOR_ID VENDOR_ID
, POV.VENDOR_NAME SOURCE
, POH.VENDOR_SITE_ID VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE SUPPLIER_SITE
, POLL.NEED_BY_DATE NEED_BY_DATE
, POLL.PROMISED_DATE PROMISED_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.QUANTITY_ORDERED ORDERED_QTY
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, POLL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POD.PO_DISTRIBUTION_ID
, POD.DELIVER_TO_LOCATION_ID
, POD.REQ_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, POL.VENDOR_PRODUCT_NUM
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM) PO_NUM_REL_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, POR_RCV_TRANSACTION_SV.GET_SHIPMENT_NUM('PO'
, POLL.LINE_LOCATION_ID)
, 'PO'
FROM PO_RELEASES POR
, MTL_UNITS_OF_MEASURE MUM
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
, PER_PEOPLE_F PPF
WHERE POD.DELIVER_TO_PERSON_ID IS NULL
AND PPF.PERSON_ID = POH.AGENT_ID
AND PPF.EFFECTIVE_END_DATE = (SELECT MAX(PPF.EFFECTIVE_END_DATE)
FROM PER_PEOPLE_F PPF
WHERE PPF.PERSON_ID = POH.AGENT_ID)
AND (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_DELIVERED
, 0) - NVL(POD.QUANTITY_CANCELLED
, 0)) > 0
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 POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE UNION ALL SELECT SYSDATE RECEIPT_DATE
, PORL.TO_PERSON_ID REQUESTOR_ID
, PPF.FULL_NAME REQUESTER
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PORL.DESTINATION_ORGANIZATION_ID TO_ORGANIZATION_ID
, PORL.ITEM_ID ITEM_ID
, PORL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUM.UOM_CLASS UOM_CLASS
, PORL.QUANTITY_DELIVERED
, PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED
, 0) - NVL(PORL.QUANTITY_DELIVERED
, 0) EXPECTED_RECEIPT_QTY
, PORL.ITEM_DESCRIPTION
, PORL.DELIVER_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, TO_NUMBER(NULL)
, OOD.NAME
, TO_NUMBER(NULL)
, NULL
, PORL.NEED_BY_DATE NEED_BY_DATE
, TO_DATE(NULL)
, PORL.NEED_BY_DATE EXPECTED_RECEIPT_DATE
, PORL.QUANTITY ORDERED_QTY
, PORL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, PORL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, TO_NUMBER(NULL)
, PORL.DELIVER_TO_LOCATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PORL.REQUISITION_HEADER_ID)
, PORL.REQUISITION_LINE_ID) PO_NUM_REL_NUM
, TO_NUMBER(PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PORL.REQUISITION_HEADER_ID)
, PORL.REQUISITION_LINE_ID)) ORDER_NUMBER
, PORL.REQUISITION_LINE_ID
, POR_RCV_TRANSACTION_SV.GET_SHIPMENT_NUM('REQ'
, PORL.REQUISITION_LINE_ID)
, 'REQ'
FROM PO_REQUISITION_LINES PORL
, PER_PEOPLE_F PPF
, MTL_UNITS_OF_MEASURE MUM
, HR_ALL_ORGANIZATION_UNITS_TL OOD
WHERE PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED
, 0) - NVL(PORL.QUANTITY_DELIVERED
, 0) > 0
AND PORL.SOURCE_TYPE_CODE = 'INVENTORY'
AND PPF.PERSON_ID = PORL.TO_PERSON_ID
AND PPF.EFFECTIVE_END_DATE = (SELECT MAX(PPF.EFFECTIVE_END_DATE)
FROM PER_PEOPLE_F PPF
WHERE PPF.PERSON_ID = PORL.TO_PERSON_ID)
AND MUM.UNIT_OF_MEASURE (+) = PORL.UNIT_MEAS_LOOKUP_CODE
AND OOD.ORGANIZATION_ID = PORL.SOURCE_ORGANIZATION_ID
AND OOD.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)