VIEW: APPS.POS_PO_DETAILS_V

Source

View Text - Preformatted

SELECT distinct PLL.DAYS_EARLY_RECEIPT_ALLOWED DAYS_EARLY_RECEIPT_ALLOWED , PLL.PO_LINE_ID PO_LINE_ID , PLL.CREATION_DATE CREATION_DATE , DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', NULL, PLL.QUANTITY - PLL.QUANTITY_CANCELLED) QUANTITY , PLL.QUANTITY_REJECTED QUANTITY_REJECTED , PLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID , PLL.NEED_BY_DATE NEED_BY_DATE , PLL.LAST_ACCEPT_DATE LAST_ACCEPT_DATE , DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', POL.UNIT_PRICE, PLL.PRICE_OVERRIDE) PRICE_OVERRIDE , NVL(PLL.CLOSED_CODE,'OPEN') CLOSED_CODE , PLL.CLOSED_DATE CLOSED_DATE , PLL.CLOSED_REASON CLOSED_REASON , PLL.SHIPMENT_NUM SHIPMENT_NUM , PLL.DAYS_LATE_RECEIPT_ALLOWED DAYS_LATE_RECEIPT_ALLOWED , PLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE , PLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG , PLL.RECEIPT_REQUIRED_FLAG 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)) MATCHING_TYPE , PLL.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID , PLL.RECEIPT_DAYS_EXCEPTION_CODE RECEIPT_DAYS_EXCEPTION_CODE , PLL.CLOSED_BY CLOSED_BY , PLL.APPROVED_FLAG APPROVED_FLAG , PLL.CLOSED_FLAG CLOSED_FLAG , DECODE(PLL.CANCEL_FLAG, 'I', NULL, PLL.CANCEL_FLAG) CANCEL_FLAG , PLL.CANCELLED_BY CANCELLED_BY , PLL.CANCEL_DATE CANCEL_DATE , PLL.CANCEL_REASON CANCEL_REASON , PLL.LINE_LOCATION_ID LINE_LOCATION_ID , PLL.PO_HEADER_ID PO_HEADER_ID , PLL.CREATED_BY CREATED_BY , PLL.QUANTITY_RECEIVED QUANTITY_RECEIVED , PLL.QUANTITY_ACCEPTED QUANTITY_ACCEPTED , PLL.QUANTITY_BILLED QUANTITY_BILLED , PLL.QUANTITY_CANCELLED QUANTITY_CANCELLED , PLL.PO_RELEASE_ID PO_RELEASE_ID , NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE) PROMISED_DATE , PLL.TAXABLE_FLAG TAXABLE_FLAG , ATC.NAME TAX_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 , PLL2.SHIPMENT_NUM SOURCE_SHIPMENT_NUM , PO_INQ_SV.GET_PERSON_NAME(PLL.CANCELLED_BY) CANCELLED_BY_NAME , PO_INQ_SV.GET_PERSON_NAME(PLL.CLOSED_BY) CLOSED_BY_NAME , POH.CLM_DOCUMENT_NUMBER PO_NUM , POH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE , POR.RELEASE_NUM RELEASE_NUM , POR.RELEASE_TYPE RELEASE_TYPE , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.AGENT_ID, NULL, TO_NUMBER(NULL), POH.AGENT_ID), POR.AGENT_ID) AGENT_ID , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.AGENT_ID, NULL, NULL, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID)), PO_INQ_SV.GET_PERSON_NAME(POR.AGENT_ID)) AGENT_NAME , 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) VENDOR_CONTACT , POH.CURRENCY_CODE , DECODE(PLL.PO_RELEASE_ID, NULL, NVL(POH.AUTHORIZATION_STATUS, 'INCOMPLETE'), NVL(POR.AUTHORIZATION_STATUS,'INCOMPLETE')) AUTHORIZATION_STATUS , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.REVISION_NUM, NULL, TO_NUMBER(NULL), POH.REVISION_NUM), POR.REVISION_NUM) REVISION_NUM , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.CREATION_DATE, NULL, TO_DATE(NULL), POH.CREATION_DATE), POR.CREATION_DATE) ORDER_DATE , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.APPROVED_DATE, NULL, TO_DATE(NULL), POH.APPROVED_DATE), POR.APPROVED_DATE) APPROVED_DATE , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.REVISED_DATE, NULL, NULL, POH.REVISED_DATE), POR.REVISED_DATE) 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)) SHIPMENT_AMOUNT , NVL(POL.ATTRIBUTE14,MSI.ATTRIBUTE14) ITEM_URL , POV.ATTRIBUTE14 SUPPLIER_URL , MSI.CONCATENATED_SEGMENTS ITEM_NUM , DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', PLL.QUANTITY, NULL) PRICE_BREAK_QUANTITY , DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', PLL.PRICE_OVERRIDE, NULL) BREAK_PRICE , DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', PLL.PRICE_DISCOUNT, NULL) PRICE_DISCOUNT , HRL1.LOCATION_CODE SHIP_TO_LOCATION FROM PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_VENDOR_CONTACTS POVC, PO_LINE_LOCATIONS_ALL PLL2, HR_LOCATIONS_ALL HRL1, MTL_SYSTEM_ITEMS_KFV MSI, PO_RELEASES_ALL POR, PO_HEADERS_ALL POH, FINANCIALS_SYSTEM_PARAMS_ALL FSP, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL, AP_TAX_CODES ATC WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND POR.PO_RELEASE_ID (+)= PLL.PO_RELEASE_ID AND PLL2.LINE_LOCATION_ID (+) = PLL.SOURCE_SHIPMENT_ID AND HRL1.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID 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 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)

View Text - HTML Formatted

SELECT DISTINCT PLL.DAYS_EARLY_RECEIPT_ALLOWED DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID PO_LINE_ID
, PLL.CREATION_DATE CREATION_DATE
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, NULL
, PLL.QUANTITY - PLL.QUANTITY_CANCELLED) QUANTITY
, PLL.QUANTITY_REJECTED QUANTITY_REJECTED
, PLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE LAST_ACCEPT_DATE
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, POL.UNIT_PRICE
, PLL.PRICE_OVERRIDE) PRICE_OVERRIDE
, NVL(PLL.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, PLL.CLOSED_DATE CLOSED_DATE
, PLL.CLOSED_REASON CLOSED_REASON
, PLL.SHIPMENT_NUM SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG 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)) MATCHING_TYPE
, PLL.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID
, PLL.RECEIPT_DAYS_EXCEPTION_CODE RECEIPT_DAYS_EXCEPTION_CODE
, PLL.CLOSED_BY CLOSED_BY
, PLL.APPROVED_FLAG APPROVED_FLAG
, PLL.CLOSED_FLAG CLOSED_FLAG
, DECODE(PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG) CANCEL_FLAG
, PLL.CANCELLED_BY CANCELLED_BY
, PLL.CANCEL_DATE CANCEL_DATE
, PLL.CANCEL_REASON CANCEL_REASON
, PLL.LINE_LOCATION_ID LINE_LOCATION_ID
, PLL.PO_HEADER_ID PO_HEADER_ID
, PLL.CREATED_BY CREATED_BY
, PLL.QUANTITY_RECEIVED QUANTITY_RECEIVED
, PLL.QUANTITY_ACCEPTED QUANTITY_ACCEPTED
, PLL.QUANTITY_BILLED QUANTITY_BILLED
, PLL.QUANTITY_CANCELLED QUANTITY_CANCELLED
, PLL.PO_RELEASE_ID PO_RELEASE_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) PROMISED_DATE
, PLL.TAXABLE_FLAG TAXABLE_FLAG
, ATC.NAME TAX_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
, PLL2.SHIPMENT_NUM SOURCE_SHIPMENT_NUM
, PO_INQ_SV.GET_PERSON_NAME(PLL.CANCELLED_BY) CANCELLED_BY_NAME
, PO_INQ_SV.GET_PERSON_NAME(PLL.CLOSED_BY) CLOSED_BY_NAME
, POH.CLM_DOCUMENT_NUMBER PO_NUM
, POH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE
, POR.RELEASE_NUM RELEASE_NUM
, POR.RELEASE_TYPE RELEASE_TYPE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.AGENT_ID
, NULL
, TO_NUMBER(NULL)
, POH.AGENT_ID)
, POR.AGENT_ID) AGENT_ID
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.AGENT_ID
, NULL
, NULL
, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID))
, PO_INQ_SV.GET_PERSON_NAME(POR.AGENT_ID)) AGENT_NAME
, 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) VENDOR_CONTACT
, POH.CURRENCY_CODE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')) AUTHORIZATION_STATUS
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.REVISION_NUM
, NULL
, TO_NUMBER(NULL)
, POH.REVISION_NUM)
, POR.REVISION_NUM) REVISION_NUM
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.CREATION_DATE
, NULL
, TO_DATE(NULL)
, POH.CREATION_DATE)
, POR.CREATION_DATE) ORDER_DATE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.APPROVED_DATE
, NULL
, TO_DATE(NULL)
, POH.APPROVED_DATE)
, POR.APPROVED_DATE) APPROVED_DATE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.REVISED_DATE
, NULL
, NULL
, POH.REVISED_DATE)
, POR.REVISED_DATE) 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)) SHIPMENT_AMOUNT
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14) ITEM_URL
, POV.ATTRIBUTE14 SUPPLIER_URL
, MSI.CONCATENATED_SEGMENTS ITEM_NUM
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, PLL.QUANTITY
, NULL) PRICE_BREAK_QUANTITY
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, PLL.PRICE_OVERRIDE
, NULL) BREAK_PRICE
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, PLL.PRICE_DISCOUNT
, NULL) PRICE_DISCOUNT
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
FROM PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
, PO_LINE_LOCATIONS_ALL PLL2
, HR_LOCATIONS_ALL HRL1
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, AP_TAX_CODES ATC
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND POR.PO_RELEASE_ID (+)= PLL.PO_RELEASE_ID
AND PLL2.LINE_LOCATION_ID (+) = PLL.SOURCE_SHIPMENT_ID
AND HRL1.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
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 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)