eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MST_R_SHUTTLE_ACTIVITY_V

Source

View Text - Preformatted


SELECT 'FACILITY' , mt.plan_id , fte.facility_id , mt.carrier_id , (select wc.freight_code from wsh_carriers wc WHERE wc.carrier_id = mt.carrier_id) , mt.trip_number , msikfv.concatenated_segments , (select wl.location_code from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select substr(mst_wb_util.get_name(wl.wsh_location_id),1,30) from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.address1 from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.postal_code from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select mst_wb_util.get_phone_number(wl.wsh_location_id) from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select substr(mst_wb_util.get_name(wl.wsh_location_id),1,30) from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.address1 from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.postal_code from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select mst_wb_util.get_phone_number(wl.wsh_location_id) from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , substr(mst_wb_util.get_local_chardt(mts.stop_location_id , (select mts.planned_departure_date from mst_trip_stops mts where mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id and mts.stop_location_id = mt.origin_location_id)),1,20) , substr(mst_wb_util.get_local_chardt(mts.stop_location_id, mts.planned_arrival_date),1,20) , nvl(mt.total_basic_transport_cost,0) , nvl(mt.total_trip_distance,0) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mt.plan_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mdl.trip_id = mt.trip_id) , (select nvl(sum(md.gross_weight),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mt.plan_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mdl.trip_id = mt.trip_id) , (select nvl(sum(md.volume),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mt.plan_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mdl.trip_id = mt.trip_id) , '' , '' , '' , '' , '' , (select mt1.trip_number from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , substr(mst_wb_util.get_local_chardt(mts.stop_location_id ,(select mts1.planned_departure_date from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id)),1,20) , (select mts1.planned_departure_date from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select sum ( nvl ( mdd.requested_quantity, 0 ) ) from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select sum ( nvl ( mdd.gross_weight, 0 ) ) from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select sum ( nvl ( mdd.volume, 0 ) ) from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select wl.location_code from wsh_locations wl , mst_trip_stops mts1 , mst_trip_stops mts2 , mst_delivery_legs mdl1 where wl.wsh_location_id = mts1.stop_location_id and mts1.plan_id = mdl1.plan_id and mts1.trip_id = mdl1.trip_id and mts1.stop_id = mdl1.drop_off_stop_id and mdl1.plan_id = mdl.plan_id and mdl1.delivery_id = mdl.delivery_id and mts2.stop_id = mdl1.pick_up_stop_id and mts2.plan_id = mdl1.plan_id and mts2.trip_id = mdl1.trip_id and mts2.stop_location_id = mts.stop_location_id) , (select wc.freight_code|| ' -' || (select wl.location_code from wsh_locations wl where wl.wsh_location_id = mdd.ship_to_location_id) from mst_delivery_legs mdl1 , mst_delivery_legs mdl2 , mst_trips mt1 , wsh_carriers wc , mst_trip_stops mts1 , mst_trip_stops mts2 where wc.carrier_id = mt1.carrier_id and mt1.plan_id = mdl2.plan_id and mt1.trip_id = mdl2.trip_id and mdl2.plan_id = mdl1.plan_id and mdl2.delivery_id = mdl1.delivery_id and mts2.stop_id = mdl2.pick_up_stop_id and mts2.plan_id = mdl2.plan_id and mts2.trip_id = mdl2.trip_id and mts2.stop_location_id = ( select stop_location_id from mst_trip_stops where plan_id = mdl1.plan_id and trip_id = mdl1.trip_id and stop_id = mdl1.drop_off_stop_id ) and mdl1.plan_id = mdl.plan_id and mdl1.delivery_id = mdl.delivery_id and mts1.stop_id = mdl1.pick_up_stop_id and mts1.plan_id = mdl1.plan_id and mts1.trip_id = mdl1.trip_id and mts1.stop_location_id = mts.stop_location_id) , (select mdd.source_header_number from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) from mst_trips mt , mst_trip_stops mts , mst_delivery_legs mdl , mst_deliveries md , mst_delivery_assignments mda , mst_delivery_details mdd , fte_location_parameters fte , fte_vehicle_types fvt , mtl_system_items_kfv msikfv where mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id and md.plan_id = mt.plan_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id and mdl.delivery_id = md.delivery_id and mts.stop_id = mdl.drop_off_stop_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mts.stop_location_id = fte.location_id and mt.vehicle_type_id = fvt.vehicle_type_id and fvt.inventory_item_id = msikfv.inventory_item_id and fvt.organization_id = msikfv.organization_id union all SELECT 'CARRIER' , mt.plan_id , fte.facility_id , mt.carrier_id , (select wc.freight_code from wsh_carriers wc WHERE wc.carrier_id = mt.carrier_id) , mt.trip_number , msikfv.concatenated_segments , (select wl.location_code from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select substr(mst_wb_util.get_name(wl.wsh_location_id),1,30) from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.address1 from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select wl.postal_code from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select mst_wb_util.get_phone_number(wl.wsh_location_id) from wsh_locations wl where wl.wsh_location_id = mt.origin_location_id) , (select substr(mst_wb_util.get_name(wl.wsh_location_id),1,30) from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.address1 from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.city from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.state from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select wl.postal_code from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , (select mst_wb_util.get_phone_number(wl.wsh_location_id) from wsh_locations wl where wl.wsh_location_id = mts.stop_location_id) , substr(mst_wb_util.get_local_chardt(mts.stop_location_id , (select mts.planned_departure_date from mst_trip_stops mts where mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id and mts.stop_location_id = mt.origin_location_id)),1,20) , substr(mst_wb_util.get_local_chardt(mts.stop_location_id, mts.planned_arrival_date),1,20) , nvl(mt.total_basic_transport_cost,0) , nvl(mt.total_trip_distance,0) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mt.plan_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mdl.trip_id = mt.trip_id) , (select nvl(sum(md.gross_weight),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mt.plan_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mdl.trip_id = mt.trip_id) , (select nvl(sum(md.volume),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mt.plan_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and mdl.trip_id = mt.trip_id) , '' , '' , '' , '' , '' , (select mt1.trip_number from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , substr(mst_wb_util.get_local_chardt(mts.stop_location_id ,(select mts1.planned_departure_date from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id)),1,20) , (select mts1.planned_departure_date from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select sum ( nvl ( mdd.requested_quantity, 0 ) ) from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select sum ( nvl ( mdd.gross_weight, 0 ) ) from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select sum ( nvl ( mdd.volume, 0 ) ) from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) , (select wl.location_code from wsh_locations wl , mst_trip_stops mts1 , mst_trip_stops mts2 , mst_delivery_legs mdl1 where wl.wsh_location_id = mts1.stop_location_id and mts1.plan_id = mdl1.plan_id and mts1.trip_id = mdl1.trip_id and mts1.stop_id = mdl1.drop_off_stop_id and mdl1.plan_id = mdl.plan_id and mdl1.delivery_id = mdl.delivery_id and mts2.stop_id = mdl1.pick_up_stop_id and mts2.plan_id = mdl1.plan_id and mts2.trip_id = mdl1.trip_id and mts2.stop_location_id = mts.stop_location_id) , (select wc.freight_code|| ' -' || (select wl.location_code from wsh_locations wl where wl.wsh_location_id = mdd.ship_to_location_id) from mst_delivery_legs mdl1 , mst_delivery_legs mdl2 , mst_trips mt1 , wsh_carriers wc , mst_trip_stops mts1 , mst_trip_stops mts2 where wc.carrier_id = mt1.carrier_id and mt1.plan_id = mdl2.plan_id and mt1.trip_id = mdl2.trip_id and mdl2.plan_id = mdl1.plan_id and mdl2.delivery_id = mdl1.delivery_id and mts2.stop_id = mdl2.pick_up_stop_id and mts2.plan_id = mdl2.plan_id and mts2.trip_id = mdl2.trip_id and mts2.stop_location_id = ( select stop_location_id from mst_trip_stops where plan_id = mdl1.plan_id and trip_id = mdl1.trip_id and stop_id = mdl1.drop_off_stop_id ) and mdl1.plan_id = mdl.plan_id and mdl1.delivery_id = mdl.delivery_id and mts1.stop_id = mdl1.pick_up_stop_id and mts1.plan_id = mdl1.plan_id and mts1.trip_id = mdl1.trip_id and mts1.stop_location_id = mts.stop_location_id) , (select mdd.source_header_number from mst_trips mt1 , mst_trip_stops mts1 , mst_delivery_legs mdl1 where mt1.plan_id = mt.plan_id and mt1.trip_id <> mt.trip_id and mts1.plan_id = mts.plan_id and mts1.trip_id = mt1.trip_id and mts1.stop_location_id = mts.stop_location_id and mdl1.plan_id = mt.plan_id and mdl1.delivery_id = mdl.delivery_id and mdl1.trip_id = mt1.trip_id and mdl1.pick_up_stop_id = mts1.stop_id) from mst_trips mt , mst_trip_stops mts , mst_delivery_legs mdl , mst_deliveries md , mst_delivery_assignments mda , mst_delivery_details mdd , fte_location_parameters fte , fte_vehicle_types fvt , mtl_system_items_kfv msikfv where mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id and md.plan_id = mt.plan_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id and mdl.delivery_id = md.delivery_id and mts.stop_id = mdl.drop_off_stop_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mts.stop_location_id = fte.location_id and mt.vehicle_type_id = fvt.vehicle_type_id and fvt.inventory_item_id = msikfv.inventory_item_id and fvt.organization_id = msikfv.organization_id

View Text - HTML Formatted

SELECT 'FACILITY'
, MT.PLAN_ID
, FTE.FACILITY_ID
, MT.CARRIER_ID
, (SELECT WC.FREIGHT_CODE
FROM WSH_CARRIERS WC
WHERE WC.CARRIER_ID = MT.CARRIER_ID)
, MT.TRIP_NUMBER
, MSIKFV.CONCATENATED_SEGMENTS
, (SELECT WL.LOCATION_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT SUBSTR(MST_WB_UTIL.GET_NAME(WL.WSH_LOCATION_ID)
, 1
, 30)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.ADDRESS1
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.POSTAL_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT MST_WB_UTIL.GET_PHONE_NUMBER(WL.WSH_LOCATION_ID)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT SUBSTR(MST_WB_UTIL.GET_NAME(WL.WSH_LOCATION_ID)
, 1
, 30)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.ADDRESS1
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.POSTAL_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT MST_WB_UTIL.GET_PHONE_NUMBER(WL.WSH_LOCATION_ID)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, (SELECT MTS.PLANNED_DEPARTURE_DATE
FROM MST_TRIP_STOPS MTS
WHERE MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND MTS.STOP_LOCATION_ID = MT.ORIGIN_LOCATION_ID))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0)
, NVL(MT.TOTAL_TRIP_DISTANCE
, 0)
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, ''
, ''
, ''
, ''
, ''
, (SELECT MT1.TRIP_NUMBER
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, (SELECT MTS1.PLANNED_DEPARTURE_DATE
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID))
, 1
, 20)
, (SELECT MTS1.PLANNED_DEPARTURE_DATE
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT SUM ( NVL ( MDD.REQUESTED_QUANTITY
, 0 ) )
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT SUM ( NVL ( MDD.GROSS_WEIGHT
, 0 ) )
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT SUM ( NVL ( MDD.VOLUME
, 0 ) )
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT WL.LOCATION_CODE
FROM WSH_LOCATIONS WL
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
, MST_DELIVERY_LEGS MDL1
WHERE WL.WSH_LOCATION_ID = MTS1.STOP_LOCATION_ID
AND MTS1.PLAN_ID = MDL1.PLAN_ID
AND MTS1.TRIP_ID = MDL1.TRIP_ID
AND MTS1.STOP_ID = MDL1.DROP_OFF_STOP_ID
AND MDL1.PLAN_ID = MDL.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MTS2.STOP_ID = MDL1.PICK_UP_STOP_ID
AND MTS2.PLAN_ID = MDL1.PLAN_ID
AND MTS2.TRIP_ID = MDL1.TRIP_ID
AND MTS2.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WC.FREIGHT_CODE|| ' -' || (SELECT WL.LOCATION_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MDD.SHIP_TO_LOCATION_ID)
FROM MST_DELIVERY_LEGS MDL1
, MST_DELIVERY_LEGS MDL2
, MST_TRIPS MT1
, WSH_CARRIERS WC
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
WHERE WC.CARRIER_ID = MT1.CARRIER_ID
AND MT1.PLAN_ID = MDL2.PLAN_ID
AND MT1.TRIP_ID = MDL2.TRIP_ID
AND MDL2.PLAN_ID = MDL1.PLAN_ID
AND MDL2.DELIVERY_ID = MDL1.DELIVERY_ID
AND MTS2.STOP_ID = MDL2.PICK_UP_STOP_ID
AND MTS2.PLAN_ID = MDL2.PLAN_ID
AND MTS2.TRIP_ID = MDL2.TRIP_ID
AND MTS2.STOP_LOCATION_ID = ( SELECT STOP_LOCATION_ID
FROM MST_TRIP_STOPS
WHERE PLAN_ID = MDL1.PLAN_ID
AND TRIP_ID = MDL1.TRIP_ID
AND STOP_ID = MDL1.DROP_OFF_STOP_ID )
AND MDL1.PLAN_ID = MDL.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MTS1.STOP_ID = MDL1.PICK_UP_STOP_ID
AND MTS1.PLAN_ID = MDL1.PLAN_ID
AND MTS1.TRIP_ID = MDL1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT MDD.SOURCE_HEADER_NUMBER
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
FROM MST_TRIPS MT
, MST_TRIP_STOPS MTS
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_DETAILS MDD
, FTE_LOCATION_PARAMETERS FTE
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MTS.STOP_ID = MDL.DROP_OFF_STOP_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MTS.STOP_LOCATION_ID = FTE.LOCATION_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID UNION ALL SELECT 'CARRIER'
, MT.PLAN_ID
, FTE.FACILITY_ID
, MT.CARRIER_ID
, (SELECT WC.FREIGHT_CODE
FROM WSH_CARRIERS WC
WHERE WC.CARRIER_ID = MT.CARRIER_ID)
, MT.TRIP_NUMBER
, MSIKFV.CONCATENATED_SEGMENTS
, (SELECT WL.LOCATION_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT SUBSTR(MST_WB_UTIL.GET_NAME(WL.WSH_LOCATION_ID)
, 1
, 30)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.ADDRESS1
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT WL.POSTAL_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT MST_WB_UTIL.GET_PHONE_NUMBER(WL.WSH_LOCATION_ID)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MT.ORIGIN_LOCATION_ID)
, (SELECT SUBSTR(MST_WB_UTIL.GET_NAME(WL.WSH_LOCATION_ID)
, 1
, 30)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.ADDRESS1
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.CITY
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.STATE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WL.POSTAL_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT MST_WB_UTIL.GET_PHONE_NUMBER(WL.WSH_LOCATION_ID)
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, (SELECT MTS.PLANNED_DEPARTURE_DATE
FROM MST_TRIP_STOPS MTS
WHERE MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND MTS.STOP_LOCATION_ID = MT.ORIGIN_LOCATION_ID))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0)
, NVL(MT.TOTAL_TRIP_DISTANCE
, 0)
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, ''
, ''
, ''
, ''
, ''
, (SELECT MT1.TRIP_NUMBER
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, (SELECT MTS1.PLANNED_DEPARTURE_DATE
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID))
, 1
, 20)
, (SELECT MTS1.PLANNED_DEPARTURE_DATE
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT SUM ( NVL ( MDD.REQUESTED_QUANTITY
, 0 ) )
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT SUM ( NVL ( MDD.GROSS_WEIGHT
, 0 ) )
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT SUM ( NVL ( MDD.VOLUME
, 0 ) )
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
, (SELECT WL.LOCATION_CODE
FROM WSH_LOCATIONS WL
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
, MST_DELIVERY_LEGS MDL1
WHERE WL.WSH_LOCATION_ID = MTS1.STOP_LOCATION_ID
AND MTS1.PLAN_ID = MDL1.PLAN_ID
AND MTS1.TRIP_ID = MDL1.TRIP_ID
AND MTS1.STOP_ID = MDL1.DROP_OFF_STOP_ID
AND MDL1.PLAN_ID = MDL.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MTS2.STOP_ID = MDL1.PICK_UP_STOP_ID
AND MTS2.PLAN_ID = MDL1.PLAN_ID
AND MTS2.TRIP_ID = MDL1.TRIP_ID
AND MTS2.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT WC.FREIGHT_CODE|| ' -' || (SELECT WL.LOCATION_CODE
FROM WSH_LOCATIONS WL
WHERE WL.WSH_LOCATION_ID = MDD.SHIP_TO_LOCATION_ID)
FROM MST_DELIVERY_LEGS MDL1
, MST_DELIVERY_LEGS MDL2
, MST_TRIPS MT1
, WSH_CARRIERS WC
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
WHERE WC.CARRIER_ID = MT1.CARRIER_ID
AND MT1.PLAN_ID = MDL2.PLAN_ID
AND MT1.TRIP_ID = MDL2.TRIP_ID
AND MDL2.PLAN_ID = MDL1.PLAN_ID
AND MDL2.DELIVERY_ID = MDL1.DELIVERY_ID
AND MTS2.STOP_ID = MDL2.PICK_UP_STOP_ID
AND MTS2.PLAN_ID = MDL2.PLAN_ID
AND MTS2.TRIP_ID = MDL2.TRIP_ID
AND MTS2.STOP_LOCATION_ID = ( SELECT STOP_LOCATION_ID
FROM MST_TRIP_STOPS
WHERE PLAN_ID = MDL1.PLAN_ID
AND TRIP_ID = MDL1.TRIP_ID
AND STOP_ID = MDL1.DROP_OFF_STOP_ID )
AND MDL1.PLAN_ID = MDL.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MTS1.STOP_ID = MDL1.PICK_UP_STOP_ID
AND MTS1.PLAN_ID = MDL1.PLAN_ID
AND MTS1.TRIP_ID = MDL1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID)
, (SELECT MDD.SOURCE_HEADER_NUMBER
FROM MST_TRIPS MT1
, MST_TRIP_STOPS MTS1
, MST_DELIVERY_LEGS MDL1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.TRIP_ID <> MT.TRIP_ID
AND MTS1.PLAN_ID = MTS.PLAN_ID
AND MTS1.TRIP_ID = MT1.TRIP_ID
AND MTS1.STOP_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MDL1.PLAN_ID = MT.PLAN_ID
AND MDL1.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL1.TRIP_ID = MT1.TRIP_ID
AND MDL1.PICK_UP_STOP_ID = MTS1.STOP_ID)
FROM MST_TRIPS MT
, MST_TRIP_STOPS MTS
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_DETAILS MDD
, FTE_LOCATION_PARAMETERS FTE
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND MD.PLAN_ID = MT.PLAN_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MTS.STOP_ID = MDL.DROP_OFF_STOP_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MTS.STOP_LOCATION_ID = FTE.LOCATION_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID