VIEW: APPS.POS_CONSIGNED_SHIPMENTS_V

Source

View Text - Preformatted

SELECT DECODE(POR.PO_RELEASE_ID, NULL, POH.SEGMENT1, POH.SEGMENT1 || '-' || TO_CHAR(POR.RELEASE_NUM)) PO_NUMBER, POH.PO_HEADER_ID, PLL.PO_RELEASE_ID, POL.LINE_NUM, POL.PO_LINE_ID, PLL.SHIPMENT_NUM, PLL.LINE_LOCATION_ID, PLL.ORG_ID, POH.VENDOR_ID, POH.VENDOR_SITE_ID, POH.VENDOR_CONTACT_ID, POV.VENDOR_NAME, PVS.VENDOR_SITE_CODE, POL.ITEM_ID, MSI.CONCATENATED_SEGMENTS ITEM_NUM, POL.ITEM_REVISION, POL.ITEM_DESCRIPTION, POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUM, NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE) DUE_DATE, POL.UNIT_MEAS_LOOKUP_CODE, PLL.QUANTITY QUANTITY_ORDERED, PLL.QUANTITY_RECEIVED, PLL.SHIP_TO_LOCATION_ID, NVL(HRL.LOCATION_CODE, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) SHIP_TO_LOCATION, HRO.NAME ORG_NAME, ORG.ORGANIZATION_CODE, PLL.VMI_FLAG, PLL.CONSIGNED_FLAG FROM PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL, PO_RELEASES_ALL POR, PO_VENDOR_SITES_ALL PVS, HR_LOCATIONS_ALL_TL HRL, HZ_LOCATIONS HZ, PO_VENDORS POV, MTL_SYSTEM_ITEMS_KFV MSI, HR_ALL_ORGANIZATION_UNITS_TL HRO, ORG_ORGANIZATION_DEFINITIONS ORG WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POH.AUTHORIZATION_STATUS = 'APPROVED' AND POL.PO_LINE_ID = PLL.PO_LINE_ID AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND POV.VENDOR_ID = POH.VENDOR_ID AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV('LANG') AND HZ.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID AND HZ.LANGUAGE(+) = USERENV('LANG') AND NVL(PLL.APPROVED_FLAG, 'N') = 'Y' AND NVL(PLL.CANCEL_FLAG, 'N') = 'N' AND NVL(POH.FROZEN_FLAG, 'N') = 'N' AND NVL(PLL.CONSIGNED_FLAG, 'N') = 'Y' AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED', 'PLANNED') AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+) AND NVL(MSI.ORGANIZATION_ID, PLL.SHIP_TO_ORGANIZATION_ID) = PLL.SHIP_TO_ORGANIZATION_ID AND ORG.ORGANIZATION_ID (+) = PLL.ORG_ID AND HRO.ORGANIZATION_ID (+) = PLL.ORG_ID AND HRO.LANGUAGE (+)= USERENV('LANG')

View Text - HTML Formatted

SELECT DECODE(POR.PO_RELEASE_ID
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || TO_CHAR(POR.RELEASE_NUM)) PO_NUMBER
, POH.PO_HEADER_ID
, PLL.PO_RELEASE_ID
, POL.LINE_NUM
, POL.PO_LINE_ID
, PLL.SHIPMENT_NUM
, PLL.LINE_LOCATION_ID
, PLL.ORG_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.VENDOR_CONTACT_ID
, POV.VENDOR_NAME
, PVS.VENDOR_SITE_CODE
, POL.ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUM
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUM
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) DUE_DATE
, POL.UNIT_MEAS_LOOKUP_CODE
, PLL.QUANTITY QUANTITY_ORDERED
, PLL.QUANTITY_RECEIVED
, PLL.SHIP_TO_LOCATION_ID
, NVL(HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) SHIP_TO_LOCATION
, HRO.NAME ORG_NAME
, ORG.ORGANIZATION_CODE
, PLL.VMI_FLAG
, PLL.CONSIGNED_FLAG
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL POR
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL_TL HRL
, HZ_LOCATIONS HZ
, PO_VENDORS POV
, MTL_SYSTEM_ITEMS_KFV MSI
, HR_ALL_ORGANIZATION_UNITS_TL HRO
, ORG_ORGANIZATION_DEFINITIONS ORG
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.AUTHORIZATION_STATUS = 'APPROVED'
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND POV.VENDOR_ID = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND HZ.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND HZ.LANGUAGE(+) = USERENV('LANG')
AND NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POH.FROZEN_FLAG
, 'N') = 'N'
AND NVL(PLL.CONSIGNED_FLAG
, 'N') = 'Y'
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED'
, 'PLANNED')
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND NVL(MSI.ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID) = PLL.SHIP_TO_ORGANIZATION_ID
AND ORG.ORGANIZATION_ID (+) = PLL.ORG_ID
AND HRO.ORGANIZATION_ID (+) = PLL.ORG_ID
AND HRO.LANGUAGE (+)= USERENV('LANG')