View: SO_DELIVERIES_V

Product:	OE - Order Entry
Description:
Implementation/DBA Data:	APPS.SO_DELIVERIES_V

View Text

SELECT DISTINCT /* ASSIGNED
, UNRELEASED LINE DETAILS */ SH.ORDER_NUMBER ORDER_NUMBER
, SLD.WAREHOUSE_ID WAREHOUSE_ID
, ORG.ORGANIZATION_CODE ORGANIZATION_CODE
, SH.CUSTOMER_ID CUSTOMER_ID
, SLD.DEPARTURE_ID DEPARTURE_ID
, DEP.NAME DEPARTURE_NAME
, DEP.STATUS_CODE DEPARTURE_STATUS_CODE
, LUP1.MEANING DEPARTURE_STATUS
, DECODE(LUP1.MEANING
, 'CLOSED'
, DEP.ACTUAL_DEPARTURE_DATE
, 'PLANNED'
, DEP.PLANNED_DEPARTURE_DATE
, 'OPEN'
, DEP.PLANNED_DEPARTURE_DATE
, DEP.PLANNED_DEPARTURE_DATE) DEPARTURE_DATE
, SLD.DELIVERY_ID DELIVERY_ID
, DEL.NAME DELIVERY_NAME
, DEL.STATUS_CODE DELIVERY_STATUS_CODE
, LUP2.MEANING DELIVERY_STATUS
, DEL.WAYBILL WAYBILL
, DEL.EXPECTED_ARRIVAL_DATE EXPECTED_ARRIVAL_DATE
, DEP.BILL_OF_LADING BILL_OF_LADING
, SLD.DPW_ASSIGNED_FLAG DPW_ASSIGNED_FLAG
, NVL(SL.SHIP_METHOD_CODE
, SH.SHIP_METHOD_CODE) FREIGHT_CARRIER_CODE
, NVL(SL.SHIP_TO_SITE_USE_ID
, SH.SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_USE_ID
, NVL(SL.SHIP_TO_CONTACT_ID
, SH.SHIP_TO_CONTACT_ID) SHIP_TO_CONTACT_ID
FROM MTL_PARAMETERS ORG
, SO_HEADERS_ALL SH
, SO_LINES_ALL SL
, SO_LINE_DETAILS SLD
, WSH_DELIVERIES DEL
, WSH_DEPARTURES DEP
, SO_LOOKUPS LUP1
, SO_LOOKUPS LUP2
WHERE SL.HEADER_ID = SH.HEADER_ID
AND SL.LINE_ID = SLD.LINE_ID
AND SLD.DEPARTURE_ID = DEP.DEPARTURE_ID (+)
AND SLD.DELIVERY_ID = DEL.DELIVERY_ID
AND ORG.ORGANIZATION_ID = SLD.WAREHOUSE_ID
AND LUP1.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND LUP1.LOOKUP_CODE(+) = DEP.STATUS_CODE
AND LUP2.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND LUP2.LOOKUP_CODE(+) = DEL.STATUS_CODE
AND ((NVL(SLD.SHIPPABLE_FLAG
, 'N') = 'Y'
AND NVL(SL.ATO_FLAG
, 'N') = 'N' ) OR ( NVL(SL.ATO_FLAG
, 'N') = 'Y'
AND SL.ATO_LINE_ID IS NULL
AND NVL(SLD.CONFIGURATION_ITEM_FLAG
, 'N') = 'N' ) )
AND SH.S1||'' = 1
AND SH.ORDER_CATEGORY IN ('P'
, 'R')
AND NVL(SH.S4
, -1) != 11
AND NVL(SL.S9
, -1) != 11
AND NVL(SL.ORDERED_QUANTITY
, 0) > NVL(SL.CANCELLED_QUANTITY
, 0)
AND SL.ITEM_TYPE_CODE != 'SERVICE'
AND SL.LINE_TYPE_CODE IN ('REGULAR'
, 'DETAIL')
AND SL.SOURCE_TYPE_CODE = 'INTERNAL'
AND SLD.DPW_ASSIGNED_FLAG IS NULL
AND NVL(SLD.RELEASED_FLAG
, 'N') != 'Y' UNION SELECT DISTINCT /* ASSIGNED BACKORDERED/RELEASED LINES */ SH.ORDER_NUMBER ORDER_NUMBER
, PLD.WAREHOUSE_ID WAREHOUSE_ID
, ORG.ORGANIZATION_CODE ORGANIZATION_CODE
, SH.CUSTOMER_ID CUSTOMER_ID
, PLD.DEPARTURE_ID DEPARTURE_ID
, DEP.NAME DEPARTURE_NAME
, DEP.STATUS_CODE DEPARTURE_STATUS_CODE
, LUP1.MEANING DEPARTURE_STATUS
, DECODE(LUP1.MEANING
, 'CLOSED'
, DEP.ACTUAL_DEPARTURE_DATE
, 'PLANNED'
, DEP.PLANNED_DEPARTURE_DATE
, 'OPEN'
, DEP.PLANNED_DEPARTURE_DATE
, DEP.PLANNED_DEPARTURE_DATE) DEPARTURE_DATE
, PLD.DELIVERY_ID DELIVERY_ID
, DEL.NAME DELIVERY_NAME
, DEL.STATUS_CODE DELIVERY_STATUS_CODE
, LUP2.MEANING DELIVERY_STATUS
, DEL.WAYBILL WAYBILL
, DEL.EXPECTED_ARRIVAL_DATE EXPECTED_ARRIVAL_DATE
, DEP.BILL_OF_LADING BILL_OF_LADING
, PLD.DPW_ASSIGNED_FLAG DPW_ASSIGNED_FLAG
, NVL(PL.SHIP_METHOD_CODE
, PH.SHIP_METHOD_CODE) FREIGHT_CARRIER_CODE
, NVL(SL.SHIP_TO_SITE_USE_ID
, SH.SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_USE_ID
, NVL(SL.SHIP_TO_CONTACT_ID
, SH.SHIP_TO_CONTACT_ID) SHIP_TO_CONTACT_ID
FROM MTL_PARAMETERS ORG
, SO_HEADERS_ALL SH
, SO_LINES_ALL SL
, SO_PICKING_HEADERS_ALL PH
, SO_PICKING_LINES_ALL PL
, SO_PICKING_LINE_DETAILS PLD
, WSH_DEPARTURES DEP
, WSH_DELIVERIES DEL
, SO_LOOKUPS LUP1
, SO_LOOKUPS LUP2
WHERE PH.ORDER_HEADER_ID = SH.HEADER_ID
AND PH.PICKING_HEADER_ID = PL.PICKING_HEADER_ID
AND PLD.PICKING_LINE_ID = PL.PICKING_LINE_ID
AND SH.HEADER_ID = SL.HEADER_ID
AND SL.LINE_ID = PL.ORDER_LINE_ID
AND PLD.DEPARTURE_ID = DEP.DEPARTURE_ID (+)
AND PLD.DELIVERY_ID = DEL.DELIVERY_ID
AND ORG.ORGANIZATION_ID = PLD.WAREHOUSE_ID
AND LUP1.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND LUP1.LOOKUP_CODE(+) = DEP.STATUS_CODE
AND LUP2.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND LUP2.LOOKUP_CODE(+) = DEL.STATUS_CODE
AND SH.ORDER_CATEGORY IN ('P'
, 'R')
AND NVL(SH.S4
, -1) != 11
AND NVL(SL.S9
, -1) != 11
AND NVL(PL.REQUESTED_QUANTITY
, 0) >= NVL(PL.SHIPPED_QUANTITY
, 0)
AND PLD.DPW_ASSIGNED_FLAG IS NULL
AND PL.PICKING_HEADER_ID >= 0

Columns