View: WMS_WP_WWB_LINES_V

Product:	WMS - Warehouse Management
Description:
Implementation/DBA Data:	APPS.WMS_WP_WWB_LINES_V

View Text

SELECT /*+ USE_NL(MSIV) */ DISTINCT WDD.SOURCE_HEADER_NUMBER ORDER_NUMBER
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS
WHERE WSH_LOCATION_ID=OOL.DELIVER_TO_ORG_ID) DELIVER_TO
, WND.NAME DELIVERY
, TO_NUMBER(NULL) EXCEPTION_ID
, MSIV.CONCATENATED_SEGMENTS ITEM
, WDD.REQUESTED_QUANTITY REQUESTED_QTY
, WWL.PLANNED_FILL_RATE
, WWL.RELEASE_FILL_RATE
, WWL.PICK_FILL_RATE
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS
WHERE WSH_LOCATION_ID=WDD.SHIP_TO_LOCATION_ID) SHIP_TO
, WDD.SHIPPED_QUANTITY SHIPPED_QTY
, WDD.DATE_SCHEDULED
, WDD.BATCH_ID BATCH
, WC.FREIGHT_CODE CARRIER
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS
WHERE WSH_LOCATION_ID=OOL.SHIP_TO_CONTACT_ID) LINE_SHIP_TO_CONTACT
, OOL.CUST_MODEL_SERIAL_NUMBER
, OOL.REQUEST_DATE LINE_REQUEST_DATE
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS
WHERE WSH_LOCATION_ID=OOL.DELIVER_TO_CONTACT_ID) DELIVER_TO_CONTACT
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS WHERE WSH_LOCATION_ID=WDD.DELIVER_TO_LOCATION_ID) DELIVER_TO_LOCATION
, WDD.DELIVERED_QUANTITY
, WDD.EARLIEST_DROPOFF_DATE
, OOL.EARLIEST_SHIP_DATE
, WDD.FOB_CODE
, WDD.FREIGHT_TERMS_CODE
, WDD.PREFERRED_GRADE
, WDD.GROSS_WEIGHT
, WDD.HAZARD_CLASS_ID
, WDD.IGNORE_FOR_PLANNING
, WDD.INSPECTION_FLAG
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS
WHERE WSH_LOCATION_ID=WDD.INTMED_SHIP_TO_LOCATION_ID)
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS
WHERE WSH_LOCATION_ID=WDD.INTMED_SHIP_TO_CONTACT_ID)
, WDD.ITEM_DESCRIPTION
, WDD.LATEST_DROPOFF_DATE
, WDD.LOAD_SEQ_NUMBER
, MIL.CONCATENATED_SEGMENTS
, WDD.LOT_NUMBER
, WDD.MAXIMUM_LOAD_WEIGHT
, WDD.MAXIMUM_VOLUME
, WDD.MINIMUM_FILL_PERCENT
, WDD.MODE_OF_TRANSPORT
, WDD.NET_WEIGHT
, WDD.SOURCE_HEADER_TYPE_NAME "ORDER TYPE"
, MP.ORGANIZATION_CODE
, WDD.PACKING_INSTRUCTIONS
, WDD.REQUESTED_QUANTITY_UOM
, WDD.REVISION
, WDD.SEAL_CODE
, WDD.CANCELLED_QUANTITY2
, WDD.DELIVERED_QUANTITY2
, WDD.REQUESTED_QUANTITY2
, WDD.REQUESTED_QUANTITY_UOM2
, WDD.SHIPPED_QUANTITY2
, WDD.SRC_REQUESTED_QUANTITY2
, WDD.SRC_REQUESTED_QUANTITY_UOM2
, WDD.SERVICE_LEVEL
, WDD.SHIP_METHOD_CODE SHIP_METHOD
, WDD.SHIP_MODEL_COMPLETE_FLAG
, WDD.SHIP_SET_ID
, (SELECT UI_LOCATION_CODE
FROM WSH_LOCATIONS
WHERE WSH_LOCATION_ID=WDD.SHIP_TO_CONTACT_ID)
, (SELECT P.PARTY_NAME
FROM HZ_PARTY_SITES PS
, HZ_PARTIES P
WHERE P.PARTY_ID = PS.PARTY_ID
AND PS.LOCATION_ID = WDD.SHIP_TO_LOCATION_ID
AND PARTY_TYPE = 'ORGANIZATION'
AND ROWNUM=1) SHIP_TO_CUSTOMER
, WDD.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY
, WDD.SHIPPING_INSTRUCTIONS
, WDD.SOURCE_LINE_NUMBER
, WDD.SRC_REQUESTED_QUANTITY
, WDD.SRC_REQUESTED_QUANTITY_UOM
, WLPN.TARE_WEIGHT
, WDD.TASK_ID TASK_NUMBER
, WDD.TRACKING_NUMBER
, WDD.UNIT_NUMBER
, WDD.VOLUME
, WDD.VOLUME_UOM_CODE
, WDD.WEIGHT_UOM_CODE
, HP.PARTY_NAME CUSTOMER
, WDD.PROJECT_ID PROJECT
, CUST_ACCT.CUSTOMER_CLASS_CODE CUSTOMER_CLASS
, WTS.TRIP_ID TRIP
, DECODE(WDD.RELEASED_STATUS
, 'S'
, 'RELEASED TO WAREHOUSE'
, 'D'
, 'CANCELLED'
, 'Y'
, 'STAGED/PICK CONFIRMED'
, 'C'
, 'SHIPPED'
, 'B'
, 'BACKORDERED'
, 'X'
, 'NOT APPLICABLE'
, 'N'
, 'NOT READY TO RELEASE'
, 'R'
, 'READY TO RELEASE'
, 'I'
, 'INTERFACED') LINE_STATUS
, DECODE(WDD.RELEASED_STATUS
, 'S'
, DECODE(WMS_WAVE_PLANNING_PVT.GET_LOADED_STATUS(WDD.DELIVERY_DETAIL_ID)
, 1
, 'PARTIALLY PICKED'
, 2
, 'PICKED'
, DECODE(NVL(WDD.MOVE_ORDER_LINE_ID
, -99)
, -99
, 'CROSSDOCK PLANNED'
, 'TASKED'))
, 'C'
, 'SHIPPED'
, 'B'
, DECODE(WDD.REPLENISHMENT_STATUS
, 'R'
, 'REPLENISHMENT CREATED'
, 'BACKORDERED')
, 'R'
, 'READY TO RELEASE'
, 'Y'
, DECODE(WLPN.LPN_CONTEXT
, 11
, DECODE(MIL.INVENTORY_LOCATION_TYPE
, 2
, 'STAGED'
, 4
, 'IN PACKING'
, 5
, 'IN PACKING')
, 9
, 'LOADED TO DOCK')) LINE_PROGRESS
, DECODE(WDD.RELEASED_STATUS
, 'S'
, DECODE(WMS_WAVE_PLANNING_PVT.GET_LOADED_STATUS(WDD.DELIVERY_DETAIL_ID)
, 1
, 6
, 2
, 7
, DECODE(NVL(WDD.MOVE_ORDER_LINE_ID
, -99)
, -99
, 3
, 5))
, 'C'
, 11
, 'B'
, DECODE(WDD.REPLENISHMENT_STATUS
, 'R'
, 4
, 2)
, 'R'
, 1
, 'K'
, 3
, 'Y'
, DECODE(WLPN.LPN_CONTEXT
, 11
, DECODE(MIL.INVENTORY_LOCATION_TYPE
, 2
, 8
, 4
, 9
, 5
, 9)
, 9
, 10)) LINE_PROGRESS_ID
, WWH.WAVE_HEADER_ID
, WWL.REMOVE_FROM_WAVE_FLAG
, WWH.WAVE_FIRMED_FLAG
, WND.DELIVERY_ID
, WDD.DELIVERY_DETAIL_ID
, WDD.SOURCE_HEADER_ID
, WDD.SOURCE_LINE_ID
, WDD.ORGANIZATION_ID
, WWL.MESSAGE FROM WSH_DELIVERY_DETAILS WDD
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS CUST_ACCT
, OE_ORDER_LINES_ALL OOL
, WMS_WP_WAVE_HEADERS_VL WWH
, WMS_WP_WAVE_LINES WWL
, WSH_NEW_DELIVERIES WND
, MTL_SYSTEM_ITEMS_KFV MSIV
, WSH_CARRIERS WC
, MTL_PARAMETERS MP
, WMS_LICENSE_PLATE_NUMBERS WLPN
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_TRIP_STOPS WTS
, WSH_DELIVERY_LEGS WLG
, MTL_ITEM_LOCATIONS_KFV MIL
, WSH_DELIVERY_DETAILS WDD2 WHERE HP.PARTY_ID = CUST_ACCT.PARTY_ID AND CUST_ACCT.STATUS = 'A' AND CUST_ACCT.CUST_ACCOUNT_ID= WDD.CUSTOMER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND WDD.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WDD.CARRIER_ID = WC.CARRIER_ID(+) AND WDA.PARENT_DELIVERY_DETAIL_ID = WDD2.DELIVERY_DETAIL_ID(+) AND WDD.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+) AND WWH.WAVE_HEADER_ID = WWL.WAVE_HEADER_ID AND WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WWL.ORGANIZATION_ID = WDD.ORGANIZATION_ID AND WTS.STOP_ID(+) = WLG.PICK_UP_STOP_ID AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID AND WLPN.LPN_ID(+) = WDD2.LPN_ID AND WDD.ORGANIZATION_ID = MSIV.ORGANIZATION_ID