VIEW: APPS.POR_RCV_PURCHASE_ITEMS_V

Source

View Text - Preformatted

SELECT SYSDATE RECEIPT_DATE, NVL(POD.DELIVER_TO_PERSON_ID, POH.AGENT_ID) REQUESTOR_ID, POLL.PO_HEADER_ID PO_HEADER_ID, POH.CLM_DOCUMENT_NUMBER PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POLL.PO_RELEASE_ID PO_RELEASE_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, PRL.REQUISITION_HEADER_ID REQ_HEADER_ID, PRH.SEGMENT1, 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, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POH.CURRENCY_CODE, POL.UNIT_MEAS_LOOKUP_CODE) PRIMARY_UOM, DECODE(POL.MATCHING_BASIS, 'AMOUNT', NULL, MUM.UOM_CLASS) UOM_CLASS, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_DELIVERED, POD.QUANTITY_DELIVERED) QUANTITY_DELIVERED, LEAST(DECODE(POL.MATCHING_BASIS, 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED, 0) - NVL(POLL.AMOUNT_RECEIVED, 0), POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED, 0) - NVL(POLL.QUANTITY_RECEIVED, 0)), DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED, 0) - NVL(POD.AMOUNT_DELIVERED, 0), POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0))) EXPECTED_RECEIPT_QUANTITY, 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, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, POD.QUANTITY_ORDERED) QUANTITY_ORDERED, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POH.CURRENCY_CODE, POL.UNIT_MEAS_LOOKUP_CODE) ORDERED_UOM, POD.PO_DISTRIBUTION_ID, POD.DELIVER_TO_LOCATION_ID, POD.REQ_DISTRIBUTION_ID, POD.DISTRIBUTION_NUM, POL.VENDOR_PRODUCT_NUM, POH.CLM_DOCUMENT_NUMBER ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) PO_NUM_REL_NUM, TO_NUMBER(NULL), TO_NUMBER(NULL), PRL.REQUISITION_LINE_ID, 'PO', POL.MATCHING_BASIS, PRL.MANUFACTURER_PART_NUMBER, HRO.NAME, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_BILLED, POD.QUANTITY_BILLED) QUANTITY_INVOICED, POD.GL_ENCUMBERED_DATE FROM PO_RELEASES_ALL POR, MTL_UNITS_OF_MEASURE MUM, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_REQ_DISTRIBUTIONS PRD, PO_REQUISITION_LINES PRL, PO_REQUISITION_HEADERS PRH, PO_LINES_TRX_V POL, PO_DISTRIBUTIONS_TRX_V POD, PO_HEADERS_ALL POH, PO_LINE_LOCATIONS_TRX_V POLL, HR_ALL_ORGANIZATION_UNITS_TL HRO /*bug 14669194 begin*/ ,MTL_SYSTEM_ITEMS MSI /*bug 14669194 end*/ WHERE POD.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_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 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 POLL.PAYMENT_TYPE IS NULL AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_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 POH.ORG_ID = POVS.ORG_ID AND POD.ORG_ID = HRO.ORGANIZATION_ID AND HRO.LANGUAGE = USERENV('LANG') AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+) AND NVL(POH.TYPE_LOOKUP_CODE, 'STANDARD') NOT IN ('CONTRACT', 'QUOTATION', 'RFQ') AND NVL(POH.CANCEL_FLAG,'N') = 'N' /*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 SYSDATE RECEIPT_DATE, NVL(POD.DELIVER_TO_PERSON_ID, POH.AGENT_ID) REQUESTOR_ID, POLL.PO_HEADER_ID PO_HEADER_ID, POH.CLM_DOCUMENT_NUMBER PO_NUMBER, POLL.PO_LINE_ID PO_LINE_ID, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID, POLL.PO_RELEASE_ID PO_RELEASE_ID, POR.RELEASE_NUM PO_RELEASE_NUMBER, TO_NUMBER(NULL) REQ_HEADER_ID, NULL REQUISITION_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, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POH.CURRENCY_CODE, POL.UNIT_MEAS_LOOKUP_CODE) PRIMARY_UOM, DECODE(POL.MATCHING_BASIS, 'AMOUNT', NULL, MUM.UOM_CLASS) UOM_CLASS, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_DELIVERED, POD.QUANTITY_DELIVERED) QUANTITY_DELIVERED, LEAST(DECODE(POL.MATCHING_BASIS, 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED, 0) - NVL(POLL.AMOUNT_RECEIVED, 0), POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED, 0) - NVL(POLL.QUANTITY_RECEIVED, 0)), DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED, 0) - NVL(POD.AMOUNT_DELIVERED, 0), POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED, 0) - NVL(POD.QUANTITY_DELIVERED, 0))) EXPECTED_RECEIPT_QUANTITY, 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, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, POD.QUANTITY_ORDERED) QUANTITY_ORDERED, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POH.CURRENCY_CODE, POL.UNIT_MEAS_LOOKUP_CODE) ORDERED_UOM, POD.PO_DISTRIBUTION_ID, POD.DELIVER_TO_LOCATION_ID, POD.REQ_DISTRIBUTION_ID, POD.DISTRIBUTION_NUM, POL.VENDOR_PRODUCT_NUM, POH.CLM_DOCUMENT_NUMBER ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) PO_NUM_REL_NUM, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), 'PO', POL.MATCHING_BASIS, NULL, HRO.NAME, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_BILLED, POD.QUANTITY_BILLED) QUANTITY_INVOICED, POD.GL_ENCUMBERED_DATE FROM PO_RELEASES POR, MTL_UNITS_OF_MEASURE MUM, PO_VENDORS POV, PO_VENDOR_SITES POVS, PO_LINES_TRX_V POL, PO_DISTRIBUTIONS_TRX_V POD, PO_HEADERS POH, PO_LINE_LOCATIONS_TRX_V POLL, HR_ALL_ORGANIZATION_UNITS_TL HRO WHERE POD.REQ_DISTRIBUTION_ID IS NULL 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 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 POLL.PAYMENT_TYPE IS NULL AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_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 POD.ORG_ID = HRO.ORGANIZATION_ID AND HRO.LANGUAGE = USERENV('LANG') AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+) AND NVL(POH.TYPE_LOOKUP_CODE, 'STANDARD') NOT IN ('CONTRACT', 'QUOTATION', 'RFQ') AND NVL(POH.CANCEL_FLAG,'N') = 'N'

View Text - HTML Formatted

SELECT SYSDATE RECEIPT_DATE
, NVL(POD.DELIVER_TO_PERSON_ID
, POH.AGENT_ID) REQUESTOR_ID
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.CLM_DOCUMENT_NUMBER PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POLL.PO_RELEASE_ID PO_RELEASE_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, PRL.REQUISITION_HEADER_ID REQ_HEADER_ID
, PRH.SEGMENT1
, 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
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POH.CURRENCY_CODE
, POL.UNIT_MEAS_LOOKUP_CODE) PRIMARY_UOM
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, NULL
, MUM.UOM_CLASS) UOM_CLASS
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, POD.QUANTITY_DELIVERED) QUANTITY_DELIVERED
, LEAST(DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED
, 0) - NVL(POLL.AMOUNT_RECEIVED
, 0)
, POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED
, 0) - NVL(POLL.QUANTITY_RECEIVED
, 0))
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0) - NVL(POD.AMOUNT_DELIVERED
, 0)
, POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))) EXPECTED_RECEIPT_QUANTITY
, 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
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED
, POD.QUANTITY_ORDERED) QUANTITY_ORDERED
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POH.CURRENCY_CODE
, POL.UNIT_MEAS_LOOKUP_CODE) ORDERED_UOM
, POD.PO_DISTRIBUTION_ID
, POD.DELIVER_TO_LOCATION_ID
, POD.REQ_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, POL.VENDOR_PRODUCT_NUM
, POH.CLM_DOCUMENT_NUMBER ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM) PO_NUM_REL_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PRL.REQUISITION_LINE_ID
, 'PO'
, POL.MATCHING_BASIS
, PRL.MANUFACTURER_PART_NUMBER
, HRO.NAME
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_BILLED
, POD.QUANTITY_BILLED) QUANTITY_INVOICED
, POD.GL_ENCUMBERED_DATE
FROM PO_RELEASES_ALL POR
, MTL_UNITS_OF_MEASURE MUM
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_REQ_DISTRIBUTIONS PRD
, PO_REQUISITION_LINES PRL
, PO_REQUISITION_HEADERS PRH
, PO_LINES_TRX_V POL
, PO_DISTRIBUTIONS_TRX_V POD
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_TRX_V POLL
, HR_ALL_ORGANIZATION_UNITS_TL HRO /*BUG 14669194 BEGIN*/
, MTL_SYSTEM_ITEMS MSI /*BUG 14669194 END*/
WHERE POD.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_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 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 POLL.PAYMENT_TYPE IS NULL
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_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 POH.ORG_ID = POVS.ORG_ID
AND POD.ORG_ID = HRO.ORGANIZATION_ID
AND HRO.LANGUAGE = USERENV('LANG')
AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
AND NVL(POH.TYPE_LOOKUP_CODE
, 'STANDARD') NOT IN ('CONTRACT'
, 'QUOTATION'
, 'RFQ')
AND NVL(POH.CANCEL_FLAG
, 'N') = 'N' /*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 SYSDATE RECEIPT_DATE
, NVL(POD.DELIVER_TO_PERSON_ID
, POH.AGENT_ID) REQUESTOR_ID
, POLL.PO_HEADER_ID PO_HEADER_ID
, POH.CLM_DOCUMENT_NUMBER PO_NUMBER
, POLL.PO_LINE_ID PO_LINE_ID
, POLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, POLL.PO_RELEASE_ID PO_RELEASE_ID
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, TO_NUMBER(NULL) REQ_HEADER_ID
, NULL REQUISITION_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
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POH.CURRENCY_CODE
, POL.UNIT_MEAS_LOOKUP_CODE) PRIMARY_UOM
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, NULL
, MUM.UOM_CLASS) UOM_CLASS
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, POD.QUANTITY_DELIVERED) QUANTITY_DELIVERED
, LEAST(DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED
, 0) - NVL(POLL.AMOUNT_RECEIVED
, 0)
, POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED
, 0) - NVL(POLL.QUANTITY_RECEIVED
, 0))
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0) - NVL(POD.AMOUNT_DELIVERED
, 0)
, POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0) - NVL(POD.QUANTITY_DELIVERED
, 0))) EXPECTED_RECEIPT_QUANTITY
, 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
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_ORDERED
, POD.QUANTITY_ORDERED) QUANTITY_ORDERED
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POH.CURRENCY_CODE
, POL.UNIT_MEAS_LOOKUP_CODE) ORDERED_UOM
, POD.PO_DISTRIBUTION_ID
, POD.DELIVER_TO_LOCATION_ID
, POD.REQ_DISTRIBUTION_ID
, POD.DISTRIBUTION_NUM
, POL.VENDOR_PRODUCT_NUM
, POH.CLM_DOCUMENT_NUMBER ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM) PO_NUM_REL_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'PO'
, POL.MATCHING_BASIS
, NULL
, HRO.NAME
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_BILLED
, POD.QUANTITY_BILLED) QUANTITY_INVOICED
, POD.GL_ENCUMBERED_DATE
FROM PO_RELEASES POR
, MTL_UNITS_OF_MEASURE MUM
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_LINES_TRX_V POL
, PO_DISTRIBUTIONS_TRX_V POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS_TRX_V POLL
, HR_ALL_ORGANIZATION_UNITS_TL HRO
WHERE POD.REQ_DISTRIBUTION_ID IS NULL
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 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 POLL.PAYMENT_TYPE IS NULL
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_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 POD.ORG_ID = HRO.ORGANIZATION_ID
AND HRO.LANGUAGE = USERENV('LANG')
AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
AND NVL(POH.TYPE_LOOKUP_CODE
, 'STANDARD') NOT IN ('CONTRACT'
, 'QUOTATION'
, 'RFQ')
AND NVL(POH.CANCEL_FLAG
, 'N') = 'N'