View: POS_SHIPMENTS_V

Product:	POS - iSupplier Portal
Description:	Displays Shipments
Implementation/DBA Data:	APPS.POS_SHIPMENTS_V

View Text

SELECT POH.SEGMENT1 PO_NUMBER
, PRL.RELEASE_NUM
, DECODE(PRL.PO_RELEASE_ID
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM)) DISPLAY_PO_NUMBER
, POH.PO_HEADER_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.VENDOR_CONTACT_ID
, POL.LINE_NUM
, POV.VENDOR_NAME
, POL.ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUM
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER
, POL.UNIT_PRICE
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) DUE_DATE
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, POL.UNIT_MEAS_LOOKUP_CODE
, MUM.UOM_CLASS
, TO_NUMBER(NULL) SHIPMENT_QUANTITY
, TO_NUMBER(NULL) TOLERABLE_QUANTITY
, PLL.QUANTITY QUANTITY_ORDERED
, PLL.QUANTITY_RECEIVED
, NVL(HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) SHIP_TO_LOCATION
, PVS.VENDOR_SITE_CODE
, PVS.PAY_SITE_FLAG
, POH.CURRENCY_CODE
, POH.RATE_TYPE
, POH.RATE
, POH.RATE_DATE
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID ORG_ID
, PLL.SHIPMENT_NUM
, PLL.QTY_RCV_EXCEPTION_CODE
, PLL.TAXABLE_FLAG
, '' TAX_NAME
, PLL.ESTIMATED_TAX_AMOUNT
, POH.REVISION_NUM
, NULL ORGANIZATION_CODE
, HRO2.NAME SHIP_TO_ORG_NAME
, HRO.NAME ORGANIZATION_NAME
, PLL.ORG_ID
, PLL.PO_RELEASE_ID
, PLL.MATCH_OPTION
, PVS.PAY_ON_CODE
, PLL.CONSIGNED_FLAG
, PLL.SHIP_TO_LOCATION_ID
, POL.SUPPLIER_REF_NUMBER
, POH.SHIPPING_CONTROL TRANSPORTATION_ARRANGED_BY
, PLC.DISPLAYED_FIELD TAB_DISPLAY
, PLL.PROMISED_DATE
, PLL.NEED_BY_DATE
, NVL(MSI.PRIMARY_UNIT_OF_MEASURE
, POL.UNIT_MEAS_LOOKUP_CODE)
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PRL
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL_TL HRL
, HZ_LOCATIONS HZ
, PO_VENDORS POV
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_UNITS_OF_MEASURE MUM
, HR_ALL_ORGANIZATION_UNITS_TL HRO
, HR_ALL_ORGANIZATION_UNITS_TL HRO2
, PO_LOOKUP_CODES PLC
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.AUTHORIZATION_STATUS = 'APPROVED'
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE (+)
AND PLL.PO_RELEASE_ID = PRL.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 NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POH.FROZEN_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CANCELLED')
AND (PLL.QUANTITY*(1+NVL(PLL.QTY_RCV_TOLERANCE
, 0)/100)) > (NVL(PLL.QUANTITY_RECEIVED
, 0) + NVL(PLL.QUANTITY_SHIPPED
, 0) + NVL(PLL.QUANTITY_CANCELLED
, 0))
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND NVL(MSI.ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID) = PLL.SHIP_TO_ORGANIZATION_ID
AND HRO2.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_ID
AND HRO2.LANGUAGE (+)= USERENV('LANG')
AND HRO.ORGANIZATION_ID (+) = POH.ORG_ID
AND HRO.LANGUAGE (+)= USERENV('LANG')
AND DECODE(PLL.PO_RELEASE_ID
, NULL
, NVL(POH.CONSIGNED_CONSUMPTION_FLAG
, 'N')
, NVL(PRL.CONSIGNED_CONSUMPTION_FLAG
, 'N')) != 'Y'
AND NVL(PLL.PAYMENT_TYPE
, ' ') NOT IN ('ADVANCE'
, 'DELIVERY')
AND PLC.LOOKUP_TYPE='SHIPPING CONTROL'
AND NVL(POH.SHIPPING_CONTROL
, 'SUPPLIER') = PLC.LOOKUP_CODE
AND PLL.OUTSOURCED_ASSEMBLY <> 1
AND PLL.PAYMENT_TYPE IS NULL

Columns