Product: | IBE - iStore |
---|---|
Description: | |
Implementation/DBA Data: | APPS.IBE_SHIP_DETAIL_V |
SELECT DL.DELIVERY_ID
, DL.NAME
, DD.DELIVERY_DETAIL_ID
, OL.LINE_ID LINE_ID
, OL.HEADER_ID HEADER_ID
, NVL(WT.SHIP_METHOD_CODE
, DD.SHIP_METHOD_CODE) SHIP_METHOD_CODE
, NVL( LV2.MEANING
, LV1.MEANING) SHIP_METHOD_CODE_MEANING
, MSI.DESCRIPTION
, MSI.CONCATENATED_SEGMENTS
, DD.SHIPPED_QUANTITY
, DD.REQUESTED_QUANTITY
, DD.REQUESTED_QUANTITY_UOM
, MSIT.UNIT_OF_MEASURE_TL SHIPPED_QUANTITY_UOM_DESC
, DL.INITIAL_PICKUP_DATE INITIAL_PICKUP_DATE
, DL.STATUS_CODE
, WL.MEANING
, DD.TRACKING_NUMBER
, DL.WAYBILL WAYBILL_NUMBER
, TS.ACTUAL_DEPARTURE_DATE DATE_SHIPPED
, OL.LINE_NUMBER
, OL.SHIPMENT_NUMBER
, OL.OPTION_NUMBER
, OL.COMPONENT_NUMBER
, OL.SERVICE_NUMBER
, OL.TOP_MODEL_LINE_ID
, OL.LINK_TO_LINE_ID
, OL.ATO_LINE_ID
, OL.FLOW_STATUS_CODE
, OEL.MEANING LINE_STATUS
, OL.LINE_CATEGORY_CODE
FROM OE_ORDER_LINES_ALL OL
, WSH_DELIVERY_DETAILS DD
, WSH_NEW_DELIVERIES DL
, WSH_LOOKUPS WL
, OE_LOOKUPS OEL
, 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
, MTL_UNITS_OF_MEASURE_TL MSIT
WHERE OL.HEADER_ID = DD.SOURCE_HEADER_ID(+)
AND OL.LINE_ID = DD.SOURCE_LINE_ID(+)
AND DD.DELIVERY_DETAIL_ID = DA.DELIVERY_DETAIL_ID (+)
AND MSI.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OE_PROFILE.VALUE('OE_ORGANIZATION_ID'
, OL.ORG_ID)
AND DA.DELIVERY_ID = DL.DELIVERY_ID(+)
AND WL.LOOKUP_TYPE(+) = 'DELIVERY_STATUS'
AND WL.LOOKUP_CODE(+) = DL.STATUS_CODE
AND OEL.LOOKUP_CODE(+) = OL.FLOW_STATUS_CODE
AND OEL.LOOKUP_TYPE(+) = 'LINE_FLOW_STATUS'
AND DL.DELIVERY_ID = WDL.DELIVERY_ID(+)
AND WDL.PICK_UP_STOP_ID = TS.STOP_ID(+)
AND TS.TRIP_ID = WT.TRIP_ID(+)
AND NVL(DL.INITIAL_PICKUP_LOCATION_ID
, -99) = NVL(NVL(TS.STOP_LOCATION_ID
, DL.INITIAL_PICKUP_LOCATION_ID)
, -99)
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.REQUESTED_QUANTITY_UOM = MSIT.UOM_CODE(+)
AND MSIT.LANGUAGE(+) = USERENV('LANG')
AND ( OL.SHIPPABLE_FLAG = 'Y' OR ( OL.TOP_MODEL_LINE_ID = OL.LINE_ID
AND OL.LINK_TO_LINE_ID IS NULL ) OR ( OL.TOP_MODEL_LINE_ID IS NOT NULL
AND OL.ATO_LINE_ID IS NOT NULL
AND OL.ATO_LINE_ID = OL.LINE_ID ) )