View: POS_CONSIGNED_SHIPMENTS_V

Product:	POS - iSupplier Portal
Description:	Used to select consigned shipments.
Implementation/DBA Data:	APPS.POS_CONSIGNED_SHIPMENTS_V

View Text

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')

Columns