VIEW: APPS.POR_RCV_REQS_VENDOR_V

Source

View Text - Preformatted

SELECT /*+ INDEX(POV PO_VENDORS_U2) */ PRH.REQUISITION_HEADER_ID, PRH.SEGMENT1, PRH.DESCRIPTION, PRH.PREPARER_ID, PRL.TO_PERSON_ID, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) ORDER_NUM_REL_NUM, POH.SEGMENT1 PO_NUMBER, TO_NUMBER(NULL) ORDER_NUMBER, POR.RELEASE_NUM PO_RELEASE_NUMBER, POV.VENDOR_NAME VENDOR, PRH.CREATION_DATE, PAPF.FULL_NAME, PAPF2.FULL_NAME FROM PER_ALL_PEOPLE_F PAPF, PER_ALL_PEOPLE_F PAPF2, PO_REQUISITION_HEADERS PRH, PO_REQUISITION_LINES PRL, PO_REQ_DISTRIBUTIONS PRD, PO_RELEASES POR, PO_VENDORS POV, PO_DISTRIBUTIONS POD, PO_HEADERS POH, PO_LINE_LOCATIONS POLL WHERE PAPF.PERSON_ID = PRL.TO_PERSON_ID AND PAPF.EFFECTIVE_END_DATE = (SELECT MAX(PAPF.EFFECTIVE_END_DATE) FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PRL.TO_PERSON_ID) AND PAPF2.PERSON_ID = PRH.PREPARER_ID AND PAPF2.EFFECTIVE_END_DATE = (SELECT MAX(PAPF.EFFECTIVE_END_DATE) FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PRH.PREPARER_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 POD.PO_HEADER_ID = POH.PO_HEADER_ID AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID AND PRD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

View Text - HTML Formatted

SELECT /*+ INDEX(POV PO_VENDORS_U2) */ PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRH.DESCRIPTION
, PRH.PREPARER_ID
, PRL.TO_PERSON_ID
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM) ORDER_NUM_REL_NUM
, POH.SEGMENT1 PO_NUMBER
, TO_NUMBER(NULL) ORDER_NUMBER
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, POV.VENDOR_NAME VENDOR
, PRH.CREATION_DATE
, PAPF.FULL_NAME
, PAPF2.FULL_NAME
FROM PER_ALL_PEOPLE_F PAPF
, PER_ALL_PEOPLE_F PAPF2
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS PRD
, PO_RELEASES POR
, PO_VENDORS POV
, PO_DISTRIBUTIONS POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
WHERE PAPF.PERSON_ID = PRL.TO_PERSON_ID
AND PAPF.EFFECTIVE_END_DATE = (SELECT MAX(PAPF.EFFECTIVE_END_DATE)
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = PRL.TO_PERSON_ID)
AND PAPF2.PERSON_ID = PRH.PREPARER_ID
AND PAPF2.EFFECTIVE_END_DATE = (SELECT MAX(PAPF.EFFECTIVE_END_DATE)
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = PRH.PREPARER_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 POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PRD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID