View: IBE_SHIP_DLVY_DTL_V

Product:	IBE - iStore
Description:	Store information about delivery details
Implementation/DBA Data:	APPS.IBE_SHIP_DLVY_DTL_V

View Text

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')

Columns