VIEW: APPS.POR_RCV_ALL_ITEMS_V1

Source

View Text - Preformatted

SELECT SYSDATE RECEIPT_DATE, NVL(POD.DELIVER_TO_PERSON_ID, POH.AGENT_ID) REQUESTOR_ID, PAPF.FULL_NAME REQUESTER, 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, POR.RELEASE_NUM PO_RELEASE_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, 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_QTY, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, POH.VENDOR_ID VENDOR_ID, POV.VENDOR_NAME SOURCE, 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) ORDERED_QTY, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POH.CURRENCY_CODE, MUM.UNIT_OF_MEASURE_TL) PRIMARY_UOM, POD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM, NULL,'','-'||POR.RELEASE_NUM) PO_NUM_REL_NUM, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG, POD.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE, POH.REVISION_NUM REVISION_NUM, POH.CREATION_DATE REQUISITION_CREATION_DATE, POH.CURRENCY_CODE CURRENCY_CODE, POL.UNIT_PRICE UNIT_PRICE, POL.MATCHING_BASIS MATCHING_BASIS, POL.PURCHASE_BASIS PURCHASE_BASIS, DECODE(POL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_BILLED, POD.QUANTITY_BILLED) QUANTITY_INVOICED, PJ.NAME JOB, POL.START_DATE START_DATE, POL.EXPIRATION_DATE EXPIRATION_DATE, POD.WF_ITEM_KEY WF_ITEM_KEY, POD.INVOICED_VAL_IN_NTFN INVOICED_VAL_IN, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM_NON_TL FROM PER_ALL_PEOPLE_F PAPF, PO_RELEASES_ALL POR, PO_LOOKUP_CODES PLC, MTL_UNITS_OF_MEASURE MUM, PO_VENDORS POV, PO_LINES_TRX_V POL, PO_DISTRIBUTIONS_TRX_V POD, PO_HEADERS POH, PO_LINE_LOCATIONS_TRX_V POLL, PER_JOBS PJ WHERE PAPF.PERSON_ID = NVL(POD.DELIVER_TO_PERSON_ID, POH.AGENT_ID) AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE 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') IN ('OPEN', 'CLOSED FOR INVOICE') 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 POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POH.VENDOR_ID = POV.VENDOR_ID AND PLC.LOOKUP_TYPE = 'PO TYPE' AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE AND PJ.JOB_ID (+) = POL.JOB_ID

View Text - HTML Formatted

SELECT SYSDATE RECEIPT_DATE
, NVL(POD.DELIVER_TO_PERSON_ID
, POH.AGENT_ID) REQUESTOR_ID
, PAPF.FULL_NAME REQUESTER
, 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
, POR.RELEASE_NUM PO_RELEASE_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
, 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_QTY
, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, POH.VENDOR_ID VENDOR_ID
, POV.VENDOR_NAME SOURCE
, 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) ORDERED_QTY
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POH.CURRENCY_CODE
, MUM.UNIT_OF_MEASURE_TL) PRIMARY_UOM
, POD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, POH.SEGMENT1 ||DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM) PO_NUM_REL_NUM
, POLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, POD.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE
, POH.REVISION_NUM REVISION_NUM
, POH.CREATION_DATE REQUISITION_CREATION_DATE
, POH.CURRENCY_CODE CURRENCY_CODE
, POL.UNIT_PRICE UNIT_PRICE
, POL.MATCHING_BASIS MATCHING_BASIS
, POL.PURCHASE_BASIS PURCHASE_BASIS
, DECODE(POL.MATCHING_BASIS
, 'AMOUNT'
, POD.AMOUNT_BILLED
, POD.QUANTITY_BILLED) QUANTITY_INVOICED
, PJ.NAME JOB
, POL.START_DATE START_DATE
, POL.EXPIRATION_DATE EXPIRATION_DATE
, POD.WF_ITEM_KEY WF_ITEM_KEY
, POD.INVOICED_VAL_IN_NTFN INVOICED_VAL_IN
, POL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM_NON_TL
FROM PER_ALL_PEOPLE_F PAPF
, PO_RELEASES_ALL POR
, PO_LOOKUP_CODES PLC
, MTL_UNITS_OF_MEASURE MUM
, PO_VENDORS POV
, PO_LINES_TRX_V POL
, PO_DISTRIBUTIONS_TRX_V POD
, PO_HEADERS POH
, PO_LINE_LOCATIONS_TRX_V POLL
, PER_JOBS PJ
WHERE PAPF.PERSON_ID = NVL(POD.DELIVER_TO_PERSON_ID
, POH.AGENT_ID)
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
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') IN ('OPEN'
, 'CLOSED FOR INVOICE')
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 POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID
AND PLC.LOOKUP_TYPE = 'PO TYPE'
AND MUM.UNIT_OF_MEASURE (+) = POL.UNIT_MEAS_LOOKUP_CODE
AND POH.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND PJ.JOB_ID (+) = POL.JOB_ID