VIEW: APPS.POS_PO_EXPECTED_RECEIPTS_V

Source

View Text - Preformatted

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.UNENCUMBERED_QUANTITY , PLL.FREIGHT_TERMS_LOOKUP_CODE , PLL.ESTIMATED_TAX_AMOUNT , PLL.PRICE_OVERRIDE , PLL.QTY_RCV_EXCEPTION_CODE , NVL(PLL.CLOSED_CODE,'OPEN') , PLL.RECEIVE_CLOSE_TOLERANCE , PLL.PROGRAM_ID , PLL.GOVERNMENT_CONTEXT , PLL.USSGL_TRANSACTION_CODE , PLL.CLOSED_DATE , PLL.CLOSED_REASON , PLL.UNIT_OF_MEASURE_CLASS , PLL.SHIPMENT_NUM , PLL.DAYS_LATE_RECEIPT_ALLOWED , PLL.ENFORCE_SHIP_TO_LOCATION_CODE , PLL.INSPECTION_REQUIRED_FLAG , PLL.QTY_RCV_TOLERANCE , 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)) , NULL, PLL.SHIP_TO_ORGANIZATION_ID , PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG , PLL.RECEIPT_DAYS_EXCEPTION_CODE , PLL.INVOICE_CLOSE_TOLERANCE , PLL.REQUEST_ID , PLL.PROGRAM_APPLICATION_ID , PLL.PROGRAM_UPDATE_DATE , PLL.RECEIVING_ROUTING_ID , PLL.ACCRUE_ON_RECEIPT_FLAG , PLL.CLOSED_BY , PLL.LEAD_TIME , PLL.LEAD_TIME_UNIT , PLL.PRICE_DISCOUNT , PLL.TERMS_ID , PLL.APPROVED_FLAG , PLL.CLOSED_FLAG , DECODE(PLL.CANCEL_FLAG, 'I', NULL, PLL.CANCEL_FLAG) , PLL.CANCELLED_BY , PLL.CANCEL_DATE , PLL.CANCEL_REASON , NVL(PLL.FIRM_STATUS_LOOKUP_CODE, 'N') , PLL.FIRM_DATE , PLL.ENCUMBER_NOW , PLL.SOURCE_SHIPMENT_ID , PLL.SHIPMENT_TYPE , PLL.LINE_LOCATION_ID , PLL.LAST_UPDATE_DATE , PLL.LAST_UPDATED_BY , PLL.PO_HEADER_ID , PLL.LAST_UPDATE_LOGIN , 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) PROMISED_DATE, PLL.ENCUMBERED_FLAG , PLL.ENCUMBERED_DATE , PLL.FOB_LOOKUP_CODE , PLL.TAXABLE_FLAG , NULL, 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 , RRH.ROUTING_NAME , NULL, NVL(HRL.LOCATION_CODE, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) LOCATION_CODE , HROU.NAME , NULL, PLL2.SHIPMENT_NUM , POS_GET.GET_PERSON_NAME(PLL.CANCELLED_BY) , POS_GET.GET_PERSON_NAME(PLL.CLOSED_BY) , DECODE(PLL.PO_RELEASE_ID, NULL, POH.SEGMENT1, POH.SEGMENT1 || '-' || POR.RELEASE_NUM) , POH.TYPE_LOOKUP_CODE , POR.RELEASE_NUM , POR.RELEASE_TYPE , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.AGENT_ID, NULL, TO_NUMBER(NULL), POH.AGENT_ID), POR.AGENT_ID) , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.AGENT_ID, NULL, NULL, POS_GET.GET_PERSON_NAME(POH.AGENT_ID)), POS_GET.GET_PERSON_NAME(POR.AGENT_ID)) , POH.VENDOR_ID , POV.VENDOR_NAME , POV.SEGMENT1 VENDOR_NUMBER, POH.VENDOR_SITE_ID , POVS.VENDOR_SITE_CODE , DECODE(POVC.LAST_NAME, NULL, NULL, POVC.LAST_NAME||', '|| POVC.FIRST_NAME) , POH.BILL_TO_LOCATION_ID , HRL2.LOCATION_CODE , POH.CURRENCY_CODE , DECODE(PLL.PO_RELEASE_ID, NULL, NVL(POH.AUTHORIZATION_STATUS, 'INCOMPLETE'), NVL(POR.AUTHORIZATION_STATUS,'INCOMPLETE')) , DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.REVISION_NUM, NULL, TO_NUMBER(NULL), POH.REVISION_NUM), POR.REVISION_NUM) , NULL, NULL, DECODE(PLL.PO_RELEASE_ID, NULL, POH.CREATION_DATE, POR.CREATION_DATE) , DECODE(PLL.PO_RELEASE_ID, NULL, POH.APPROVED_DATE, POR.APPROVED_DATE) , DECODE(PLL.PO_RELEASE_ID, NULL, POH.REVISED_DATE, POR.REVISED_DATE) , APT.NAME , POH.SHIP_VIA_LOOKUP_CODE , POH.RATE_TYPE , POH.RATE_DATE , POH.RATE , POH.START_DATE , POH.END_DATE , POH.BLANKET_TOTAL_AMOUNT , POH.NOTE_TO_RECEIVER , POH.CONFIRMING_ORDER_FLAG , POH.ACCEPTANCE_DUE_DATE , DECODE(PLL.PO_RELEASE_ID, NULL, POL.LINE_NUM, NULL) , POL.LINE_TYPE_ID , NULL, 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.UN_NUMBER_ID , NULL, POL.HAZARD_CLASS_ID , NULL, POL.NOTE_TO_VENDOR , POL.UNORDERED_FLAG , POL.VENDOR_PRODUCT_NUM , POL.MIN_RELEASE_AMOUNT , NULL, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0) ) , NVL(POL.ATTRIBUTE14,MSI.ATTRIBUTE14) , POV.ATTRIBUTE14 , MSI.CONCATENATED_SEGMENTS, POH.ORG_ID, HOU.NAME, POL.SUPPLIER_REF_NUMBER, NVL( PLL.PROMISED_DATE, PLL.NEED_BY_DATE) PROMISED_DATETIME FROM HR_LOCATIONS_ALL_TL HRL, HR_LOCATIONS_ALL_TL HRL2, HZ_LOCATIONS HZ, HR_ORGANIZATION_UNITS HROU, RCV_ROUTING_HEADERS RRH, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_VENDOR_CONTACTS POVC, AP_TERMS APT, PO_LINE_LOCATIONS_ALL PLL2, MTL_SYSTEM_ITEMS_KFV MSI, PO_RELEASES_ALL POR, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL, HR_ALL_ORGANIZATION_UNITS_TL HOU 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 RRH.ROUTING_HEADER_ID (+) = PLL.RECEIVING_ROUTING_ID AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID AND PLL.SHIP_TO_LOCATION_ID = HZ.LOCATION_ID (+) AND HRL.LANGUAGE (+) = USERENV('LANG') AND HROU.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_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 HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID AND HRL2.LANGUAGE (+) = USERENV('LANG') AND APT.TERM_ID (+) = POH.TERMS_ID AND PLL.SHIPMENT_TYPE != 'PRICE BREAK' AND NVL(PLL.QUANTITY_RECEIVED,0) < (NVL(PLL.QUANTITY,0) - NVL(PLL.QUANTITY_CANCELLED,0 )) AND NVL(PLL.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED', 'CLOSED FOR RECEIVING') AND NVL(PLL.CANCEL_FLAG,'N') != 'Y' AND NVL(PLL.APPROVED_FLAG, 'N') = 'Y' AND DECODE(PLL.PO_RELEASE_ID, NULL, NVL(POH.USER_HOLD_FLAG, 'N'), NVL(POR.HOLD_FLAG, 'N')) = 'N' AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND PLL.SHIP_TO_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID, PLL.SHIP_TO_ORGANIZATION_ID) AND HOU.ORGANIZATION_ID (+) = POH.ORG_ID AND HOU.LANGUAGE (+) = USERENV('LANG') AND DECODE(PLL.PO_RELEASE_ID, NULL, NVL(POH.CONSIGNED_CONSUMPTION_FLAG, 'N'), NVL(POR.CONSIGNED_CONSUMPTION_FLAG, 'N')) != 'Y'

View Text - HTML Formatted

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.UNENCUMBERED_QUANTITY
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.ESTIMATED_TAX_AMOUNT
, PLL.PRICE_OVERRIDE
, PLL.QTY_RCV_EXCEPTION_CODE
, NVL(PLL.CLOSED_CODE
, 'OPEN')
, PLL.RECEIVE_CLOSE_TOLERANCE
, PLL.PROGRAM_ID
, PLL.GOVERNMENT_CONTEXT
, PLL.USSGL_TRANSACTION_CODE
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.UNIT_OF_MEASURE_CLASS
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.QTY_RCV_TOLERANCE
, 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))
, NULL
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.INVOICE_CLOSE_TOLERANCE
, PLL.REQUEST_ID
, PLL.PROGRAM_APPLICATION_ID
, PLL.PROGRAM_UPDATE_DATE
, PLL.RECEIVING_ROUTING_ID
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.CLOSED_BY
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.PRICE_DISCOUNT
, PLL.TERMS_ID
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE(PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, NVL(PLL.FIRM_STATUS_LOOKUP_CODE
, 'N')
, PLL.FIRM_DATE
, PLL.ENCUMBER_NOW
, PLL.SOURCE_SHIPMENT_ID
, PLL.SHIPMENT_TYPE
, PLL.LINE_LOCATION_ID
, PLL.LAST_UPDATE_DATE
, PLL.LAST_UPDATED_BY
, PLL.PO_HEADER_ID
, PLL.LAST_UPDATE_LOGIN
, 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) PROMISED_DATE
, PLL.ENCUMBERED_FLAG
, PLL.ENCUMBERED_DATE
, PLL.FOB_LOOKUP_CODE
, PLL.TAXABLE_FLAG
, NULL
, 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
, RRH.ROUTING_NAME
, NULL
, NVL(HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) LOCATION_CODE
, HROU.NAME
, NULL
, PLL2.SHIPMENT_NUM
, POS_GET.GET_PERSON_NAME(PLL.CANCELLED_BY)
, POS_GET.GET_PERSON_NAME(PLL.CLOSED_BY)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || POR.RELEASE_NUM)
, POH.TYPE_LOOKUP_CODE
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.AGENT_ID
, NULL
, TO_NUMBER(NULL)
, POH.AGENT_ID)
, POR.AGENT_ID)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.AGENT_ID
, NULL
, NULL
, POS_GET.GET_PERSON_NAME(POH.AGENT_ID))
, POS_GET.GET_PERSON_NAME(POR.AGENT_ID))
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE(POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME||'
, '|| POVC.FIRST_NAME)
, POH.BILL_TO_LOCATION_ID
, HRL2.LOCATION_CODE
, POH.CURRENCY_CODE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE'))
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.REVISION_NUM
, NULL
, TO_NUMBER(NULL)
, POH.REVISION_NUM)
, POR.REVISION_NUM)
, NULL
, NULL
, DECODE(PLL.PO_RELEASE_ID
, NULL
, POH.CREATION_DATE
, POR.CREATION_DATE)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, POH.APPROVED_DATE
, POR.APPROVED_DATE)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, POH.REVISED_DATE
, POR.REVISED_DATE)
, APT.NAME
, POH.SHIP_VIA_LOOKUP_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.START_DATE
, POH.END_DATE
, POH.BLANKET_TOTAL_AMOUNT
, POH.NOTE_TO_RECEIVER
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, POL.LINE_NUM
, NULL)
, POL.LINE_TYPE_ID
, NULL
, 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.UN_NUMBER_ID
, NULL
, POL.HAZARD_CLASS_ID
, NULL
, POL.NOTE_TO_VENDOR
, POL.UNORDERED_FLAG
, POL.VENDOR_PRODUCT_NUM
, POL.MIN_RELEASE_AMOUNT
, NULL
, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0) )
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, MSI.CONCATENATED_SEGMENTS
, POH.ORG_ID
, HOU.NAME
, POL.SUPPLIER_REF_NUMBER
, NVL( PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) PROMISED_DATETIME
FROM HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL2
, HZ_LOCATIONS HZ
, HR_ORGANIZATION_UNITS HROU
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
, AP_TERMS APT
, PO_LINE_LOCATIONS_ALL PLL2
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, HR_ALL_ORGANIZATION_UNITS_TL HOU
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 RRH.ROUTING_HEADER_ID (+) = PLL.RECEIVING_ROUTING_ID
AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID
AND PLL.SHIP_TO_LOCATION_ID = HZ.LOCATION_ID (+)
AND HRL.LANGUAGE (+) = USERENV('LANG')
AND HROU.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_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 HRL2.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HRL2.LANGUAGE (+) = USERENV('LANG')
AND APT.TERM_ID (+) = POH.TERMS_ID
AND PLL.SHIPMENT_TYPE != 'PRICE BREAK'
AND NVL(PLL.QUANTITY_RECEIVED
, 0) < (NVL(PLL.QUANTITY
, 0) - NVL(PLL.QUANTITY_CANCELLED
, 0 ))
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, 'FINALLY CLOSED'
, 'CLOSED FOR RECEIVING')
AND NVL(PLL.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND DECODE(PLL.PO_RELEASE_ID
, NULL
, NVL(POH.USER_HOLD_FLAG
, 'N')
, NVL(POR.HOLD_FLAG
, 'N')) = 'N'
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PLL.SHIP_TO_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID)
AND HOU.ORGANIZATION_ID (+) = POH.ORG_ID
AND HOU.LANGUAGE (+) = USERENV('LANG')
AND DECODE(PLL.PO_RELEASE_ID
, NULL
, NVL(POH.CONSIGNED_CONSUMPTION_FLAG
, 'N')
, NVL(POR.CONSIGNED_CONSUMPTION_FLAG
, 'N')) != 'Y'