[Home] [Help]
| Product: | ICX - Oracle iProcurement |
|---|---|
| Description: | Purchase Order Shipments View |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID
, PLL.CREATION_DATE
, PLL.QUANTITY - PLL.QUANTITY_CANCELLED
, PLL.QUANTITY_REJECTED
, PLL.SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE
, PLL.PRICE_OVERRIDE
, NVL(PLL.CLOSED_CODE
, 'OPEN')
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG
, DECODE (PLL.RECEIPT_REQUIRED_FLAG
, 'N'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '2-WAY'
, NULL)
, 'Y'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '3-WAY'
, 'Y'
, '4-WAY'
, NULL))
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.CLOSED_BY
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE(PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, PLL.LINE_LOCATION_ID
, POH.PO_HEADER_ID
, PLL.CREATED_BY
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_ACCEPTED
, PLL.QUANTITY_BILLED
, PLL.QUANTITY_CANCELLED
, TO_NUMBER(NULL)
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.TAXABLE_FLAG
, ATC.NAME
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, POLC4.DISPLAYED_FIELD
, NULL
, HROU.NAME
, PO_INQ_SV.GET_PERSON_NAME(PLL.CANCELLED_BY)
, PO_INQ_SV.GET_PERSON_NAME(PLL.CLOSED_BY)
, POH.SEGMENT1
, POH.TYPE_LOOKUP_CODE
, TO_NUMBER(NULL)
, NULL
, POH.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID)
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE (POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME||'
, '|| POVC.FIRST_NAME)
, POH.CURRENCY_CODE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.REVISION_NUM
, PDT.TYPE_NAME
, POH.CREATION_DATE
, POH.APPROVED_DATE
, POH.REVISED_DATE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.NOTE_TO_VENDOR
, POL.VENDOR_PRODUCT_NUM
, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0) )
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, MSI.CONCATENATED_SEGMENTS
, PLL.QUANTITY
, PLL.PRICE_OVERRIDE
, PLL.PRICE_DISCOUNT
FROM PO_LOOKUP_CODES POLC4
, HR_ALL_ORGANIZATION_UNITS_TL HROU
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_VENDOR_CONTACTS POVC
, PO_DOCUMENT_TYPES_TL PDT
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_HEADERS POH
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LINES POL
, PO_LINE_LOCATIONS PLL
, AP_TAX_CODES ATC
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND PLL.SHIPMENT_TYPE = 'PRICE BREAK'
AND PLL.PO_RELEASE_ID IS NULL
AND POLC4.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND POLC4.LOOKUP_CODE (+) = DECODE(POL.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, NVL(POL.CLOSED_CODE
, 'OPEN'))
AND HROU.ORGANIZATION_ID = POH.ORG_ID
AND HROU.LANGUAGE (+) = USERENV('LANG')
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND PDT.DOCUMENT_TYPE_CODE ='PA'
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.LANGUAGE = USERENV('LANG')
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND PLL.TAX_CODE_ID = ATC.TAX_ID (+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) UNION ALL SELECT TO_NUMBER(NULL)
, POL.PO_LINE_ID
, POL.CREATION_DATE
, POL.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, POL.UNIT_PRICE
, NVL(POL.CLOSED_CODE
, 'OPEN')
, POL.CLOSED_DATE
, POL.CLOSED_REASON
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, POL.CLOSED_BY
, NULL
, NULL
, DECODE(POL.CANCEL_FLAG
, 'I'
, NULL
, POL.CANCEL_FLAG)
, POL.CANCELLED_BY
, POL.CANCEL_DATE
, POL.CANCEL_REASON
, TO_NUMBER(NULL)
, POH.PO_HEADER_ID
, POL.CREATED_BY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, POLC4.DISPLAYED_FIELD
, NULL
, HROU.NAME
, NULL
, NULL
, POH.SEGMENT1
, POH.TYPE_LOOKUP_CODE
, TO_NUMBER(NULL)
, NULL
, POH.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID)
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE(POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME||'
, '|| POVC.FIRST_NAME)
, POH.CURRENCY_CODE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.REVISION_NUM
, PDT.TYPE_NAME
, POH.CREATION_DATE
, POH.APPROVED_DATE
, POH.REVISED_DATE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.NOTE_TO_VENDOR
, POL.VENDOR_PRODUCT_NUM
, TO_NUMBER(NULL)
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, MSI.CONCATENATED_SEGMENTS
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM PO_LOOKUP_CODES POLC4
, HR_ALL_ORGANIZATION_UNITS_TL HROU
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_VENDOR_CONTACTS POVC
, PO_DOCUMENT_TYPES_TL PDT
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_HEADERS POH
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LINES POL
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POLC4.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND POLC4.LOOKUP_CODE (+) = DECODE(POL.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, NVL(POL.CLOSED_CODE
, 'OPEN'))
AND HROU.ORGANIZATION_ID = POH.ORG_ID
AND HROU.LANGUAGE (+) = USERENV('LANG')
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND PDT.DOCUMENT_TYPE_CODE = 'PA'
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.LANGUAGE = USERENV('LANG')
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)
AND NOT EXISTS ( SELECT 1
FROM PO_LINE_LOCATIONS PLL
WHERE PLL.PO_LINE_ID = POL.PO_LINE_ID ) UNION ALL SELECT PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID
, PLL.CREATION_DATE
, PLL.QUANTITY - PLL.QUANTITY_CANCELLED
, PLL.QUANTITY_REJECTED
, PLL.SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE
, DECODE(PLL.SHIPMENT_TYPE
, NULL
, POL.UNIT_PRICE
, PLL.PRICE_OVERRIDE)
, NVL(PLL.CLOSED_CODE
, 'OPEN')
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG
, DECODE(PLL.RECEIPT_REQUIRED_FLAG
, 'N'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '2-WAY'
, NULL)
, 'Y'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '3-WAY'
, 'Y'
, '4-WAY'
, NULL))
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.CLOSED_BY
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE(PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, PLL.LINE_LOCATION_ID
, POH.PO_HEADER_ID
, PLL.CREATED_BY
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_ACCEPTED
, PLL.QUANTITY_BILLED
, PLL.QUANTITY_CANCELLED
, PLL.PO_RELEASE_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.TAXABLE_FLAG
, ATC.NAME
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, POLC4.DISPLAYED_FIELD
, HRL.LOCATION_CODE
, HROU.NAME
, PO_INQ_SV.GET_PERSON_NAME(PLL.CANCELLED_BY)
, PO_INQ_SV.GET_PERSON_NAME(PLL.CLOSED_BY)
, POH.SEGMENT1
, POH.TYPE_LOOKUP_CODE
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, POR.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(POR.AGENT_ID)
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE(POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME||'
, '|| POVC.FIRST_NAME)
, POH.CURRENCY_CODE
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POR.REVISION_NUM
, PDT2.TYPE_NAME
, POR.CREATION_DATE
, POR.APPROVED_DATE
, POR.REVISED_DATE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.NOTE_TO_VENDOR
, POL.VENDOR_PRODUCT_NUM
, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0) )
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, MSI.CONCATENATED_SEGMENTS
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM PO_LOOKUP_CODES POLC4
, HR_LOCATIONS_ALL_TL HRL
, HR_ALL_ORGANIZATION_UNITS_TL HROU
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_VENDOR_CONTACTS POVC
, PO_DOCUMENT_TYPES PDT
, PO_DOCUMENT_TYPES PDT2
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_RELEASES POR
, PO_HEADERS POH
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LINES POL
, PO_LINE_LOCATIONS PLL
, AP_TAX_CODES ATC
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND POR.PO_RELEASE_ID = PLL.PO_RELEASE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE'
AND POLC4.LOOKUP_CODE = DECODE(PLL.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, NVL(PLL.CLOSED_CODE
, 'OPEN'))
AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE (+) = USERENV('LANG')
AND HROU.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
AND HROU.LANGUAGE (+) = USERENV('LANG')
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT2.DOCUMENT_TYPE_CODE (+) = 'RELEASE'
AND PDT2.DOCUMENT_SUBTYPE (+) = POR.RELEASE_TYPE
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND PLL.TAX_CODE_ID = ATC.TAX_ID (+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) UNION ALL SELECT PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID
, PLL.CREATION_DATE
, PLL.QUANTITY - PLL.QUANTITY_CANCELLED
, PLL.QUANTITY_REJECTED
, PLL.SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE
, PLL.PRICE_OVERRIDE
, NVL(PLL.CLOSED_CODE
, 'OPEN')
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG
, DECODE(PLL.RECEIPT_REQUIRED_FLAG
, 'N'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '2-WAY'
, NULL)
, 'Y'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '3-WAY'
, 'Y'
, '4-WAY'
, NULL))
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.CLOSED_BY
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE(PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, PLL.LINE_LOCATION_ID
, POH.PO_HEADER_ID
, PLL.CREATED_BY
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_ACCEPTED
, PLL.QUANTITY_BILLED
, PLL.QUANTITY_CANCELLED
, TO_NUMBER(NULL)
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.TAXABLE_FLAG
, ATC.NAME
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, POLC4.DISPLAYED_FIELD
, HRL.LOCATION_CODE
, HROU.NAME
, PO_INQ_SV.GET_PERSON_NAME(PLL.CANCELLED_BY)
, PO_INQ_SV.GET_PERSON_NAME(PLL.CLOSED_BY)
, POH.SEGMENT1
, POH.TYPE_LOOKUP_CODE
, TO_NUMBER(NULL)
, NULL
, POH.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID)
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE (POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME||'
, '|| POVC.FIRST_NAME)
, POH.CURRENCY_CODE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, POH.REVISION_NUM
, PDT.TYPE_NAME
, POH.CREATION_DATE
, POH.APPROVED_DATE
, POH.REVISED_DATE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.NOTE_TO_VENDOR
, POL.VENDOR_PRODUCT_NUM
, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0) )
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, MSI.CONCATENATED_SEGMENTS
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM PO_LOOKUP_CODES POLC4
, HR_LOCATIONS_ALL_TL HRL
, HR_ALL_ORGANIZATION_UNITS_TL HROU
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_VENDOR_CONTACTS POVC
, PO_DOCUMENT_TYPES PDT
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_HEADERS POH
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_LINES POL
, PO_LINE_LOCATIONS PLL
, AP_TAX_CODES ATC
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'PLANNED')
AND PLL.PO_RELEASE_ID IS NULL
AND POLC4.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND POLC4.LOOKUP_CODE (+) = DECODE(PLL.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, NVL(PLL.CLOSED_CODE
, 'OPEN'))
AND HRL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE (+) = USERENV('LANG')
AND HROU.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
AND HROU.LANGUAGE (+) = USERENV('LANG')
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND PDT.DOCUMENT_TYPE_CODE = 'PO'
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND PLL.TAX_CODE_ID = ATC.TAX_ID (+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)