VIEW: APPS.POR_RCV_REQS_ALL_V

Source

View Text - Preformatted

SELECT PRH.REQUISITION_HEADER_ID, PRH.PREPARER_ID, PRL.TO_PERSON_ID, POH.PO_HEADER_ID PO_HEADER_ID, POR.PO_RELEASE_ID PO_RELEASE_ID, TO_NUMBER(NULL) ORDER_ID, POH.VENDOR_ID VENDOR_ID FROM PO_REQUISITION_HEADERS PRH, PO_REQUISITION_LINES PRL, PO_REQ_DISTRIBUTIONS PRD, PO_RELEASES_ALL POR, PO_DISTRIBUTIONS_ALL POD, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL POLL WHERE 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 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 POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POD.PO_LINE_ID = POL.PO_LINE_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID AND PRD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID UNION ALL SELECT PRH.REQUISITION_HEADER_ID, PRH.PREPARER_ID, PRL.TO_PERSON_ID, OSH.HEADER_ID PO_HEADER_ID, TO_NUMBER(NULL) PO_RELEASE_ID, OSH.HEADER_ID ORDER_ID, TO_NUMBER(NULL) VENDOR_ID FROM PO_REQUISITION_HEADERS PRH, PO_REQUISITION_LINES PRL, OE_ORDER_HEADERS_ALL OSH, PO_SYSTEM_PARAMETERS POSP, RCV_SHIPMENT_LINES RSL WHERE PRL.SOURCE_TYPE_CODE = 'INVENTORY' AND OSH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID AND OSH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1 AND OSH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND RSL.ROUTING_HEADER_ID = 3 AND NVL(RSL.QUANTITY_SHIPPED, 0) > NVL(RSL.QUANTITY_RECEIVED, 0)

View Text - HTML Formatted

SELECT PRH.REQUISITION_HEADER_ID
, PRH.PREPARER_ID
, PRL.TO_PERSON_ID
, POH.PO_HEADER_ID PO_HEADER_ID
, POR.PO_RELEASE_ID PO_RELEASE_ID
, TO_NUMBER(NULL) ORDER_ID
, POH.VENDOR_ID VENDOR_ID
FROM PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS PRD
, PO_RELEASES_ALL POR
, PO_DISTRIBUTIONS_ALL POD
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
WHERE 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 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 POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PRD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID UNION ALL SELECT PRH.REQUISITION_HEADER_ID
, PRH.PREPARER_ID
, PRL.TO_PERSON_ID
, OSH.HEADER_ID PO_HEADER_ID
, TO_NUMBER(NULL) PO_RELEASE_ID
, OSH.HEADER_ID ORDER_ID
, TO_NUMBER(NULL) VENDOR_ID
FROM PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, OE_ORDER_HEADERS_ALL OSH
, PO_SYSTEM_PARAMETERS POSP
, RCV_SHIPMENT_LINES RSL
WHERE PRL.SOURCE_TYPE_CODE = 'INVENTORY'
AND OSH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID
AND OSH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1
AND OSH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND RSL.ROUTING_HEADER_ID = 3
AND NVL(RSL.QUANTITY_SHIPPED
, 0) > NVL(RSL.QUANTITY_RECEIVED
, 0)