VIEW: APPS.IBE_SHIP_DLVY_DTL_V

Source

View Text - Preformatted

SELECT dl.delivery_id, dd.delivery_detail_id, dd.source_line_id line_id, dd.source_header_id header_id, oh.order_number, NVL(wt.ship_method_code, dd.ship_method_code) ship_method_code, dd.INVENTORY_ITEM_ID, dd.ORGANIZATION_ID, NVL( lv2.meaning, lv1.meaning) SHIP_METHOD_CODE_MEANING, msi.description, msi.concatenated_segments, dd.shipped_quantity, dd.requested_quantity_uom, msit.unit_of_measure_tl requested_quantity_uom_DESC, dl.initial_pickup_date initial_pickup_date, dd.tracking_number, dl.waybill waybill_number, ts.actual_departure_date date_shipped, dd.container_flag, dd.RELEASED_STATUS delivery_line_status, wl.meaning delivery_line_status_meaning FROM wsh_delivery_details dd, wsh_new_deliveries dl, wsh_lookups wl, wsh_delivery_assignments da, mtl_system_items_vl msi, wsh_delivery_legs wdl, wsh_trip_stops ts, wsh_trips wt, OE_SHIP_METHODS_V lv1, OE_SHIP_METHODS_V lv2 , oe_order_headers_all oh, mtl_units_of_measure_tl msit WHERE dd.delivery_detail_id = da.delivery_detail_id AND dd.source_code = 'OE' AND msi.inventory_item_id = dd.inventory_item_id AND msi.organization_id = dd.organization_id AND da.delivery_id = dl.delivery_id(+) AND wl.lookup_type = 'PICK_STATUS' AND wl.lookup_code = NVL(dd.released_status,'X') AND dl.delivery_id = wdl.delivery_id(+) AND wdl.pick_up_stop_id = ts.stop_id(+) AND ts.trip_id = wt.trip_id(+) AND dl.initial_pickup_location_id = NVL(ts.stop_location_id, dl.initial_pickup_location_id) AND dd.SHIP_METHOD_CODE =LV1.LOOKUP_CODE(+) AND LV1.LOOKUP_TYPE(+) = 'SHIP_METHOD' AND wt.SHIP_METHOD_CODE =LV2.LOOKUP_CODE(+) AND LV2.LOOKUP_TYPE(+) = 'SHIP_METHOD' AND dd.source_header_id = oh.header_id AND dd.requested_quantity_uom = msit.uom_code AND msit.language = userenv('LANG')

View Text - HTML Formatted

SELECT DL.DELIVERY_ID
, DD.DELIVERY_DETAIL_ID
, DD.SOURCE_LINE_ID LINE_ID
, DD.SOURCE_HEADER_ID HEADER_ID
, OH.ORDER_NUMBER
, NVL(WT.SHIP_METHOD_CODE
, DD.SHIP_METHOD_CODE) SHIP_METHOD_CODE
, DD.INVENTORY_ITEM_ID
, DD.ORGANIZATION_ID
, NVL( LV2.MEANING
, LV1.MEANING) SHIP_METHOD_CODE_MEANING
, MSI.DESCRIPTION
, MSI.CONCATENATED_SEGMENTS
, DD.SHIPPED_QUANTITY
, DD.REQUESTED_QUANTITY_UOM
, MSIT.UNIT_OF_MEASURE_TL REQUESTED_QUANTITY_UOM_DESC
, DL.INITIAL_PICKUP_DATE INITIAL_PICKUP_DATE
, DD.TRACKING_NUMBER
, DL.WAYBILL WAYBILL_NUMBER
, TS.ACTUAL_DEPARTURE_DATE DATE_SHIPPED
, DD.CONTAINER_FLAG
, DD.RELEASED_STATUS DELIVERY_LINE_STATUS
, WL.MEANING DELIVERY_LINE_STATUS_MEANING
FROM WSH_DELIVERY_DETAILS DD
, WSH_NEW_DELIVERIES DL
, WSH_LOOKUPS WL
, WSH_DELIVERY_ASSIGNMENTS DA
, MTL_SYSTEM_ITEMS_VL MSI
, WSH_DELIVERY_LEGS WDL
, WSH_TRIP_STOPS TS
, WSH_TRIPS WT
, OE_SHIP_METHODS_V LV1
, OE_SHIP_METHODS_V LV2
, OE_ORDER_HEADERS_ALL OH
, MTL_UNITS_OF_MEASURE_TL MSIT
WHERE DD.DELIVERY_DETAIL_ID = DA.DELIVERY_DETAIL_ID
AND DD.SOURCE_CODE = 'OE'
AND MSI.INVENTORY_ITEM_ID = DD.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = DD.ORGANIZATION_ID
AND DA.DELIVERY_ID = DL.DELIVERY_ID(+)
AND WL.LOOKUP_TYPE = 'PICK_STATUS'
AND WL.LOOKUP_CODE = NVL(DD.RELEASED_STATUS
, 'X')
AND DL.DELIVERY_ID = WDL.DELIVERY_ID(+)
AND WDL.PICK_UP_STOP_ID = TS.STOP_ID(+)
AND TS.TRIP_ID = WT.TRIP_ID(+)
AND DL.INITIAL_PICKUP_LOCATION_ID = NVL(TS.STOP_LOCATION_ID
, DL.INITIAL_PICKUP_LOCATION_ID)
AND DD.SHIP_METHOD_CODE =LV1.LOOKUP_CODE(+)
AND LV1.LOOKUP_TYPE(+) = 'SHIP_METHOD'
AND WT.SHIP_METHOD_CODE =LV2.LOOKUP_CODE(+)
AND LV2.LOOKUP_TYPE(+) = 'SHIP_METHOD'
AND DD.SOURCE_HEADER_ID = OH.HEADER_ID
AND DD.REQUESTED_QUANTITY_UOM = MSIT.UOM_CODE
AND MSIT.LANGUAGE = USERENV('LANG')