VIEW: APPS.SO_DELIVERIES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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