View: POR_RCV_HOME_V

View Text

SELECT POD.DELIVER_TO_PERSON_ID REQUESTOR_ID
, POH.AGENT_ID AGENT_ID
, 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
, SUBSTR( POL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.PO_DISTRIBUTION_ID
, NULL
, 'PO'
FROM PO_LOOKUP_CODES PLC
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
WHERE (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 PLC.LOOKUP_TYPE = 'PO TYPE'
AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND POD.DELIVER_TO_PERSON_ID IS NOT NULL UNION ALL SELECT /*+ INDEX(POH PO_HEADERS_N3) */ POH.AGENT_ID REQUESTOR_ID
, POH.AGENT_ID AGENT_ID
, 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
, SUBSTR( POL.ITEM_DESCRIPTION
, 1
, 240) ITEM_DESCRIPTION
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, POD.PO_DISTRIBUTION_ID
, NULL
, 'PO'
FROM PO_LOOKUP_CODES PLC
, PO_LINES POL
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
WHERE (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 PLC.LOOKUP_TYPE = 'PO TYPE'
AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND POD.DELIVER_TO_PERSON_ID IS NULL UNION ALL SELECT PORL.TO_PERSON_ID REQUESTOR_ID
, TO_NUMBER(NULL)
, PORL.REQUISITION_HEADER_ID
, PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PORL.REQUISITION_HEADER_ID)
, PORL.REQUISITION_LINE_ID)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PORL.ITEM_DESCRIPTION
, PORL.NEED_BY_DATE EXPECTED_RECEIPT_DATE
, PORL.REQUISITION_LINE_ID
, PORH.SEGMENT1
, 'REQ'
FROM PO_REQUISITION_LINES PORL
, PO_REQUISITION_HEADERS PORH
WHERE PORL.QUANTITY - NVL(PORL.QUANTITY_CANCELLED
, 0) - NVL(PORL.QUANTITY_DELIVERED
, 0) > 0
AND PORL.SOURCE_TYPE_CODE = 'INVENTORY'
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID
AND EXISTS (SELECT 1
FROM RCV_SHIPMENT_LINES RSL
WHERE RSL.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID
AND RSL.ROUTING_HEADER_ID = 3)

Columns