VIEW: APPS.POR_RCV_HOME_REQS_V

Source

View Text - Preformatted

SELECT PRH.REQUISITION_HEADER_ID, PRH.SEGMENT1, PRH.DESCRIPTION, PRH.PREPARER_ID, PRL.TO_PERSON_ID, POH.CLM_DOCUMENT_NUMBER ORDER_NUM, POV.VENDOR_NAME VENDOR, HRO.NAME FROM PO_REQUISITION_HEADERS PRH, PO_REQUISITION_LINES PRL, PO_REQ_DISTRIBUTIONS PRD, PO_LINE_LOCATIONS_TRX_V PLL, PO_LINES_TRX_V POL, PO_HEADERS_ALL POH, PO_DISTRIBUTIONS_TRX_V POD, PO_VENDORS POV, HR_ALL_ORGANIZATION_UNITS_TL HRO /*bug 14669194 begin*/ ,MTL_SYSTEM_ITEMS MSI /*bug 14669194 end*/ WHERE NVL(PLL.APPROVED_FLAG,'N') = 'Y' AND NVL(PLL.CANCEL_FLAG, 'N') = 'N' AND NVL(PLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING') AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID AND POD.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID AND PLL.RECEIVING_ROUTING_ID = 3 AND PLL.PAYMENT_TYPE IS NULL AND POD.PO_LINE_ID = POL.PO_LINE_ID 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 POD.PO_HEADER_ID = POH.PO_HEADER_ID AND POH.VENDOR_ID = POV.VENDOR_ID AND POH.ORG_ID = HRO.ORGANIZATION_ID AND HRO.LANGUAGE = USERENV('LANG') AND prh.creation_date >= Trunc(SYSDATE - 60) AND prl.creation_date >= Trunc(SYSDATE - 60) /*bug 14669194 begin*/ AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND PRL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+) AND (MSI.LOT_CONTROL_CODE <> 2 OR MSI.LOT_CONTROL_CODE IS NULL) /*bug 14669194 end*/ UNION ALL SELECT PRH.REQUISITION_HEADER_ID, PRH.SEGMENT1, PRH.DESCRIPTION, PRH.PREPARER_ID, PRL.TO_PERSON_ID, PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PRH.REQUISITION_HEADER_ID), PRL.REQUISITION_LINE_ID), HAOU.NAME, HRO.NAME FROM PO_REQUISITION_HEADERS PRH, PO_REQUISITION_LINES PRL, RCV_SHIPMENT_LINES RSL, HR_ALL_ORGANIZATION_UNITS_TL HAOU, HR_ALL_ORGANIZATION_UNITS_TL HRO /*bug 14669194 begin*/ ,MTL_SYSTEM_ITEMS MSI /*bug 14669194 end*/ WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND PRL.REQUISITION_LINE_ID = RSL.REQUISITION_LINE_ID AND RSL.ROUTING_HEADER_ID = 3 AND RSL.QUANTITY_SHIPPED > NVL(RSL.QUANTITY_RECEIVED, 0) AND PRL.SOURCE_TYPE_CODE='INVENTORY' AND HAOU.ORGANIZATION_ID = PRL.SOURCE_ORGANIZATION_ID AND HAOU.LANGUAGE = USERENV('LANG') AND PRL.ORG_ID = HRO.ORGANIZATION_ID AND HRO.LANGUAGE = USERENV('LANG') AND prh.creation_date >= Trunc(SYSDATE - 60) AND prl.creation_date >= Trunc(SYSDATE - 60) /*bug 14669194 begin*/ AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND PRL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+) AND (MSI.LOT_CONTROL_CODE <> 2 OR MSI.LOT_CONTROL_CODE IS NULL) /*bug 14669194 end*/

View Text - HTML Formatted

SELECT PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRH.DESCRIPTION
, PRH.PREPARER_ID
, PRL.TO_PERSON_ID
, POH.CLM_DOCUMENT_NUMBER ORDER_NUM
, POV.VENDOR_NAME VENDOR
, HRO.NAME
FROM PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS PRD
, PO_LINE_LOCATIONS_TRX_V PLL
, PO_LINES_TRX_V POL
, PO_HEADERS_ALL POH
, PO_DISTRIBUTIONS_TRX_V POD
, PO_VENDORS POV
, HR_ALL_ORGANIZATION_UNITS_TL HRO /*BUG 14669194 BEGIN*/
, MTL_SYSTEM_ITEMS MSI /*BUG 14669194 END*/
WHERE NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING')
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND POD.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND PLL.RECEIVING_ROUTING_ID = 3
AND PLL.PAYMENT_TYPE IS NULL
AND POD.PO_LINE_ID = POL.PO_LINE_ID
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 POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POH.ORG_ID = HRO.ORGANIZATION_ID
AND HRO.LANGUAGE = USERENV('LANG')
AND PRH.CREATION_DATE >= TRUNC(SYSDATE - 60)
AND PRL.CREATION_DATE >= TRUNC(SYSDATE - 60) /*BUG 14669194 BEGIN*/
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PRL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND (MSI.LOT_CONTROL_CODE <> 2 OR MSI.LOT_CONTROL_CODE IS NULL) /*BUG 14669194 END*/ UNION ALL SELECT PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRH.DESCRIPTION
, PRH.PREPARER_ID
, PRL.TO_PERSON_ID
, PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PRH.REQUISITION_HEADER_ID)
, PRL.REQUISITION_LINE_ID)
, HAOU.NAME
, HRO.NAME
FROM PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, RCV_SHIPMENT_LINES RSL
, HR_ALL_ORGANIZATION_UNITS_TL HAOU
, HR_ALL_ORGANIZATION_UNITS_TL HRO /*BUG 14669194 BEGIN*/
, MTL_SYSTEM_ITEMS MSI /*BUG 14669194 END*/
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = RSL.REQUISITION_LINE_ID
AND RSL.ROUTING_HEADER_ID = 3
AND RSL.QUANTITY_SHIPPED > NVL(RSL.QUANTITY_RECEIVED
, 0)
AND PRL.SOURCE_TYPE_CODE='INVENTORY'
AND HAOU.ORGANIZATION_ID = PRL.SOURCE_ORGANIZATION_ID
AND HAOU.LANGUAGE = USERENV('LANG')
AND PRL.ORG_ID = HRO.ORGANIZATION_ID
AND HRO.LANGUAGE = USERENV('LANG')
AND PRH.CREATION_DATE >= TRUNC(SYSDATE - 60)
AND PRL.CREATION_DATE >= TRUNC(SYSDATE - 60) /*BUG 14669194 BEGIN*/
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PRL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND (MSI.LOT_CONTROL_CODE <> 2 OR MSI.LOT_CONTROL_CODE IS NULL) /*BUG 14669194 END*/