View: WSH_DELIVERY_LINE_STATUS_V

Product:	WSH - Shipping Execution
Description:
Implementation/DBA Data:	APPS.WSH_DELIVERY_LINE_STATUS_V

View Text

SELECT DD.DELIVERY_DETAIL_ID
, DD.SOURCE_CODE
, DD.SOURCE_LINE_ID
, DD.SOURCE_HEADER_ID
, DD.SOURCE_HEADER_NUMBER
, DD.SOURCE_LINE_NUMBER
, NVL(DL.SHIP_METHOD_CODE
, DD.SHIP_METHOD_CODE)
, DD.INVENTORY_ITEM_ID
, NVL(WSN.QUANTITY
, DD.REQUESTED_QUANTITY)
, NVL(WSN.QUANTITY
, DD.SHIPPED_QUANTITY)
, DD.REQUESTED_QUANTITY_UOM
, DD.SRC_REQUESTED_QUANTITY_UOM
, DD.REQUESTED_QUANTITY2
, DD.SHIPPED_QUANTITY2
, DD.REQUESTED_QUANTITY_UOM2
, DD.SRC_REQUESTED_QUANTITY_UOM2
, DD.SHIP_SET_ID
, DD.REVISION
, DD.LOT_NUMBER
, NVL(WSN.FM_SERIAL_NUMBER
, DECODE(DD.OE_INTERFACED_FLAG
, 'Y'
, DD.SERIAL_NUMBER
, NULL ) ) SERIAL_NUMBER
, DD.RELEASED_STATUS
, WL2.MEANING
, DL.DELIVERY_ID
, DL.NAME
, DL.CURRENCY_CODE
, DL.STATUS_CODE
, DECODE(NVL(DL.STATUS_CODE
, '-99')
, '-99'
, NULL
, WL1.MEANING)
, DD.ORGANIZATION_ID
, WLF.SOURCE_LOCATION_ID
, WLF.UI_LOCATION_CODE
, WLT.SOURCE_LOCATION_ID
, WLT.UI_LOCATION_CODE
, DD.GROSS_WEIGHT
, DD.WEIGHT_UOM_CODE
, DL.INITIAL_PICKUP_DATE
, DL.ULTIMATE_DROPOFF_DATE
, DD.TRACKING_NUMBER
, DL.WAYBILL
, NVL(WSN.TO_SERIAL_NUMBER
, DD.TO_SERIAL_NUMBER) TO_SERIAL_NUMBER
FROM WSH_LOOKUPS WL1
, WSH_LOOKUPS WL2
, WSH_NEW_DELIVERIES DL
, WSH_DELIVERY_ASSIGNMENTS_V DA
, WSH_DELIVERY_DETAILS DD
, WSH_LOCATIONS WLF
, WSH_LOCATIONS WLT
, WSH_SERIAL_NUMBERS WSN
WHERE DD.DELIVERY_DETAIL_ID = DA.DELIVERY_DETAIL_ID
AND DA.DELIVERY_ID = DL.DELIVERY_ID(+)
AND NVL(DL.STATUS_CODE
, 'OP') = WL1.LOOKUP_CODE
AND WL1.LOOKUP_TYPE = 'DELIVERY_STATUS'
AND ( ( WL2.LOOKUP_CODE = 'K'
AND DD.RELEASED_STATUS = 'S'
AND DD.MOVE_ORDER_LINE_ID IS NULL ) OR ( WL2.LOOKUP_CODE = DD.RELEASED_STATUS
AND ( (DD.MOVE_ORDER_LINE_ID IS NOT NULL) OR (DD.RELEASED_STATUS <> 'S'))
AND (DD.REPLENISHMENT_STATUS IS NULL) ) OR ( WL2.LOOKUP_CODE = 'E'
AND DD.RELEASED_STATUS IN ('R'
, 'B')
AND DD.REPLENISHMENT_STATUS ='R' ) OR ( WL2.LOOKUP_CODE = 'F'
AND DD.RELEASED_STATUS IN ('R'
, 'B')
AND DD.REPLENISHMENT_STATUS ='C') )
AND WL2.LOOKUP_TYPE = 'PICK_STATUS'
AND DD.SHIP_FROM_LOCATION_ID = WLF.WSH_LOCATION_ID (+)
AND DD.SHIP_TO_LOCATION_ID = WLT.WSH_LOCATION_ID (+)
AND DD.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID (+)
AND NVL(DD.LINE_DIRECTION
, 'O') IN ('O'
, 'IO')
AND NVL(DL.DELIVERY_TYPE
, 'STANDARD') = 'STANDARD'

Columns