VIEW: APPS.WSH_DELIVERY_LINE_STATUS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'