VIEW: APPS.IBE_SHIP_DETAIL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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