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_MAS_CARR_TL_V

Source

View Text - Preformatted


SELECT mt.plan_id , wlo.owner_type , fte.facility_id , 0 , md.organization_id , md.customer_id , md.supplier_id , mt.trip_start_date , mt.carrier_id , wc.freight_code , wcs.service_level , mt.trip_number , mts.stop_sequence_number , msikfv.concatenated_segments , mt.total_trip_distance , (select nvl(sum(md.gross_weight),0) from mst_deliveries md , mst_delivery_legs mdl WHERE md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_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 = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select count(distinct mdl.pick_up_stop_id) from mst_delivery_legs mdl where mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select count(distinct mdl.drop_off_stop_id) from mst_delivery_legs mdl where mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id) , (select mp.currency_uom from mst_plans mp where mp.plan_id = mt.plan_id) , nvl(mt.total_handling_cost,0) , mst_wb_util.r_get_canonical_number(nvl(mt.total_handling_cost,0),1) , nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_accessorial_cost,0) + nvl(mt.total_handling_cost,0) + nvl(mt.total_layover_cost,0) + nvl(mt.total_load_unload_cost,0) + nvl(mt.total_stop_cost,0) ,mst_wb_util.r_get_canonical_number( nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_accessorial_cost,0) + nvl(mt.total_handling_cost,0) + nvl(mt.total_layover_cost,0) + nvl(mt.total_load_unload_cost,0) + nvl(mt.total_stop_cost,0),1) , nvl(mt.total_basic_transport_cost,0) , mst_wb_util.r_get_canonical_number(nvl(mt.total_basic_transport_cost,0),1) , nvl(mt.total_stop_cost,0) , mst_wb_util.r_get_canonical_number(nvl(mt.total_stop_cost,0),1) , nvl(total_accessorial_cost,0) + nvl(mt.total_load_unload_cost,0) , mst_wb_util.r_get_canonical_number ( nvl(total_accessorial_cost,0) + nvl(mt.total_load_unload_cost,0), 1) , mt.continuous_move_id , mt.continuous_move_sequence , (select count(trip_id) from mst_trips mt1 where mt1.plan_id = mt.plan_id and mt1.continuous_move_id = mt.continuous_move_id) , DECODE(mts.stop_id,mdl.pick_up_stop_id,'L','U') , mt.origin_location_id , mt.destination_location_id , wl.location_code , substr(mst_wb_util.get_name(wl.wsh_location_id),1,80) , wl.address1 , wl.city , wl.state , wl.postal_code , wl.country , mst_wb_util.Get_Contact_Name(wl.wsh_location_id) , mst_wb_util.get_phone_number(wl.wsh_location_id) , substr(mst_wb_util.get_local_chardt(wl.wsh_location_id, mts.planned_arrival_date),1,20) , substr(mst_wb_util.get_local_chardt(wl.wsh_location_id, mts.planned_departure_date),1,20) , substr(mst_wb_util.get_local_chardt(wl.wsh_location_id, (select min(md.latest_acceptable_date) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.drop_off_stop_id = mts.stop_id)),1,20) , substr(mst_wb_util.get_local_chardt(wl.wsh_location_id, (select max(md.earliest_pickup_date) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.pick_up_stop_id = mts.stop_id)),1,20) , (select sum(md.number_of_pieces) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.drop_off_stop_id = mts.stop_id) , (select sum(md.gross_weight) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.drop_off_stop_id = mts.stop_id) , (select sum(md.volume) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.drop_off_stop_id = mts.stop_id) , nvl(mts.carrier_unloading_cost,0) + (select sum(mdl.allocated_fac_unloading_cost) from mst_delivery_legs mdl where mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.drop_off_stop_id = mts.stop_id) , substr(mst_wb_util.get_hr_min((mts.pln_unloading_end_time - mts.pln_unloading_start_time)*24),1,20) , (select sum(md.number_of_pieces) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.pick_up_stop_id = mts.stop_id) , (select sum(md.gross_weight) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.pick_up_stop_id = mts.stop_id) , (select sum(md.volume) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.pick_up_stop_id = mts.stop_id) , nvl(mts.carrier_loading_cost,0) + (select sum(mdl.allocated_fac_loading_cost) from mst_delivery_legs mdl where mdl.plan_id = mts.plan_id and mdl.trip_id = mts.trip_id and mdl.pick_up_stop_id = mts.stop_id) , substr(mst_wb_util.get_hr_min((mts.pln_loading_end_time - mts.pln_loading_start_time)*24),1,20) , mst_wb_util.r_get_prev_trip_detail(mts.plan_id, mts.trip_id, mts.stop_sequence_number,'D') , substr(mst_wb_util.get_hr_min(mst_wb_util.r_get_prev_trip_detail(mts.plan_id , mts.trip_id , mts.stop_sequence_number ,'T')),1,20) , mst_wb_util.r_get_prev_trip_detail(mts.plan_id, mts.trip_id, mts.stop_sequence_number,'C') , substr(mst_reports_pkg.get_wait_time_at_stop ( mts.plan_id, mts.trip_id, mts.stop_id ),1,12) , 0 , 0 , (select mdd.tp_refer_header_number from mst_delivery_details mdd where mdd.plan_id = mt.plan_id and mdd.delivery_detail_id = (select min(mdd1.delivery_detail_id) from mst_delivery_details mdd1 , mst_delivery_assignments mda where mdd1.plan_id = mt.plan_id and mda.plan_id = mt.plan_id and mda.delivery_id = md.delivery_id and mdd1.delivery_detail_id = mda.delivery_detail_id)) , (select mdd.source_header_number from mst_delivery_details mdd where mdd.plan_id = mt.plan_id and mdd.delivery_detail_id = (select min(mdd1.delivery_detail_id) from mst_delivery_details mdd1 , mst_delivery_assignments mda where mdd1.plan_id = mt.plan_id and mda.plan_id = mt.plan_id and mda.delivery_id = md.delivery_id and mdd1.delivery_detail_id = mda.delivery_detail_id)) , DECODE(mts.stop_id,mdl.pick_up_stop_id,'L','U') , mt.trip_number , DECODE(mts.stop_location_id ,md.pickup_location_id ,null ,md.dropoff_location_id ,null ,DECODE(mts.stop_id ,mdl.pick_up_stop_id ,mst_wb_util.r_get_pool_loc_detail('O',md.pickup_location_id) ,mst_wb_util.r_get_pool_loc_detail('O',md.dropoff_location_id) ) ) , DECODE(mts.stop_location_id ,md.pickup_location_id ,null ,md.dropoff_location_id ,null ,DECODE(mts.stop_id ,mdl.pick_up_stop_id ,mst_wb_util.r_get_pool_loc_detail('N',md.pickup_location_id) ,mst_wb_util.r_get_pool_loc_detail('N',md.dropoff_location_id) ) ) , DECODE(mts.stop_location_id ,md.pickup_location_id ,null ,md.dropoff_location_id ,null ,DECODE(mts.stop_id ,mdl.pick_up_stop_id ,mst_wb_util.r_get_pool_loc_detail('A',md.pickup_location_id) ,mst_wb_util.r_get_pool_loc_detail('A',md.dropoff_location_id) ) ) , DECODE(mts.stop_location_id ,md.pickup_location_id ,null ,md.dropoff_location_id ,null ,DECODE(mts.stop_id ,mdl.pick_up_stop_id ,mst_wb_util.r_get_pool_loc_detail('C',md.pickup_location_id) ,mst_wb_util.r_get_pool_loc_detail('C',md.dropoff_location_id) ) ) , DECODE(mts.stop_location_id ,md.pickup_location_id ,null ,md.dropoff_location_id ,null ,DECODE(mts.stop_id ,mdl.pick_up_stop_id ,mst_wb_util.r_get_pool_loc_detail('S',md.pickup_location_id) ,mst_wb_util.r_get_pool_loc_detail('S',md.dropoff_location_id) ) ) , DECODE(mts.stop_location_id ,md.pickup_location_id ,null ,md.dropoff_location_id ,null ,DECODE(mts.stop_id ,mdl.pick_up_stop_id ,mst_wb_util.r_get_pool_loc_detail('Z',md.pickup_location_id) ,mst_wb_util.r_get_pool_loc_detail('Z',md.dropoff_location_id) ) ) , mst_wb_util.r_get_prev_carr_detail (mdl.plan_id, mdl.delivery_id, mdl.trip_id, mts.stop_location_id, 'C') , mst_wb_util.r_get_prev_carr_detail (mdl.plan_id, mdl.delivery_id, mdl.trip_id, mts.stop_location_id, 'F') from mst_trips mt , mst_trip_stops mts , mst_delivery_legs mdl , mst_deliveries md , wsh_carriers wc , wsh_carrier_services wcs , fte_vehicle_types fvt , fte_location_parameters fte , wsh_location_owners wlo , mtl_system_items_kfv msikfv , wsh_locations wl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and (mdl.pick_up_stop_id = mts.stop_id or mdl.drop_off_stop_id = mts.stop_id ) and mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id and mt.mode_of_transport = 'TRUCK' and mt.carrier_id = wc.carrier_id and mt.carrier_service_id = wcs.carrier_service_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 and fte.location_id = mts.stop_location_id and wlo.wsh_location_id = mts.stop_location_id and mts.stop_location_id = wl.wsh_location_id

View Text - HTML Formatted

SELECT MT.PLAN_ID
, WLO.OWNER_TYPE
, FTE.FACILITY_ID
, 0
, MD.ORGANIZATION_ID
, MD.CUSTOMER_ID
, MD.SUPPLIER_ID
, MT.TRIP_START_DATE
, MT.CARRIER_ID
, WC.FREIGHT_CODE
, WCS.SERVICE_LEVEL
, MT.TRIP_NUMBER
, MTS.STOP_SEQUENCE_NUMBER
, MSIKFV.CONCATENATED_SEGMENTS
, MT.TOTAL_TRIP_DISTANCE
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_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 = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT COUNT(DISTINCT MDL.PICK_UP_STOP_ID)
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT COUNT(DISTINCT MDL.DROP_OFF_STOP_ID)
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID)
, (SELECT MP.CURRENCY_UOM
FROM MST_PLANS MP
WHERE MP.PLAN_ID = MT.PLAN_ID)
, NVL(MT.TOTAL_HANDLING_COST
, 0)
, MST_WB_UTIL.R_GET_CANONICAL_NUMBER(NVL(MT.TOTAL_HANDLING_COST
, 0)
, 1)
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0)
, MST_WB_UTIL.R_GET_CANONICAL_NUMBER( NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0)
, 1)
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0)
, MST_WB_UTIL.R_GET_CANONICAL_NUMBER(NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0)
, 1)
, NVL(MT.TOTAL_STOP_COST
, 0)
, MST_WB_UTIL.R_GET_CANONICAL_NUMBER(NVL(MT.TOTAL_STOP_COST
, 0)
, 1)
, NVL(TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0)
, MST_WB_UTIL.R_GET_CANONICAL_NUMBER ( NVL(TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0)
, 1)
, MT.CONTINUOUS_MOVE_ID
, MT.CONTINUOUS_MOVE_SEQUENCE
, (SELECT COUNT(TRIP_ID)
FROM MST_TRIPS MT1
WHERE MT1.PLAN_ID = MT.PLAN_ID
AND MT1.CONTINUOUS_MOVE_ID = MT.CONTINUOUS_MOVE_ID)
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, 'L'
, 'U')
, MT.ORIGIN_LOCATION_ID
, MT.DESTINATION_LOCATION_ID
, WL.LOCATION_CODE
, SUBSTR(MST_WB_UTIL.GET_NAME(WL.WSH_LOCATION_ID)
, 1
, 80)
, WL.ADDRESS1
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, WL.COUNTRY
, MST_WB_UTIL.GET_CONTACT_NAME(WL.WSH_LOCATION_ID)
, MST_WB_UTIL.GET_PHONE_NUMBER(WL.WSH_LOCATION_ID)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WL.WSH_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WL.WSH_LOCATION_ID
, MTS.PLANNED_DEPARTURE_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WL.WSH_LOCATION_ID
, (SELECT MIN(MD.LATEST_ACCEPTABLE_DATE)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(WL.WSH_LOCATION_ID
, (SELECT MAX(MD.EARLIEST_PICKUP_DATE)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID))
, 1
, 20)
, (SELECT SUM(MD.NUMBER_OF_PIECES)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
, (SELECT SUM(MD.GROSS_WEIGHT)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
, (SELECT SUM(MD.VOLUME)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
, NVL(MTS.CARRIER_UNLOADING_COST
, 0) + (SELECT SUM(MDL.ALLOCATED_FAC_UNLOADING_COST)
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS.PLN_UNLOADING_END_TIME - MTS.PLN_UNLOADING_START_TIME)*24)
, 1
, 20)
, (SELECT SUM(MD.NUMBER_OF_PIECES)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID)
, (SELECT SUM(MD.GROSS_WEIGHT)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID)
, (SELECT SUM(MD.VOLUME)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID)
, NVL(MTS.CARRIER_LOADING_COST
, 0) + (SELECT SUM(MDL.ALLOCATED_FAC_LOADING_COST)
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.TRIP_ID = MTS.TRIP_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS.PLN_LOADING_END_TIME - MTS.PLN_LOADING_START_TIME)*24)
, 1
, 20)
, MST_WB_UTIL.R_GET_PREV_TRIP_DETAIL(MTS.PLAN_ID
, MTS.TRIP_ID
, MTS.STOP_SEQUENCE_NUMBER
, 'D')
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.R_GET_PREV_TRIP_DETAIL(MTS.PLAN_ID
, MTS.TRIP_ID
, MTS.STOP_SEQUENCE_NUMBER
, 'T'))
, 1
, 20)
, MST_WB_UTIL.R_GET_PREV_TRIP_DETAIL(MTS.PLAN_ID
, MTS.TRIP_ID
, MTS.STOP_SEQUENCE_NUMBER
, 'C')
, SUBSTR(MST_REPORTS_PKG.GET_WAIT_TIME_AT_STOP ( MTS.PLAN_ID
, MTS.TRIP_ID
, MTS.STOP_ID )
, 1
, 12)
, 0
, 0
, (SELECT MDD.TP_REFER_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD
WHERE MDD.PLAN_ID = MT.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = (SELECT MIN(MDD1.DELIVERY_DETAIL_ID)
FROM MST_DELIVERY_DETAILS MDD1
, MST_DELIVERY_ASSIGNMENTS MDA
WHERE MDD1.PLAN_ID = MT.PLAN_ID
AND MDA.PLAN_ID = MT.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDD1.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID))
, (SELECT MDD.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD
WHERE MDD.PLAN_ID = MT.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = (SELECT MIN(MDD1.DELIVERY_DETAIL_ID)
FROM MST_DELIVERY_DETAILS MDD1
, MST_DELIVERY_ASSIGNMENTS MDA
WHERE MDD1.PLAN_ID = MT.PLAN_ID
AND MDA.PLAN_ID = MT.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDD1.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID))
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, 'L'
, 'U')
, MT.TRIP_NUMBER
, DECODE(MTS.STOP_LOCATION_ID
, MD.PICKUP_LOCATION_ID
, NULL
, MD.DROPOFF_LOCATION_ID
, NULL
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('O'
, MD.PICKUP_LOCATION_ID)
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('O'
, MD.DROPOFF_LOCATION_ID) ) )
, DECODE(MTS.STOP_LOCATION_ID
, MD.PICKUP_LOCATION_ID
, NULL
, MD.DROPOFF_LOCATION_ID
, NULL
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('N'
, MD.PICKUP_LOCATION_ID)
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('N'
, MD.DROPOFF_LOCATION_ID) ) )
, DECODE(MTS.STOP_LOCATION_ID
, MD.PICKUP_LOCATION_ID
, NULL
, MD.DROPOFF_LOCATION_ID
, NULL
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('A'
, MD.PICKUP_LOCATION_ID)
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('A'
, MD.DROPOFF_LOCATION_ID) ) )
, DECODE(MTS.STOP_LOCATION_ID
, MD.PICKUP_LOCATION_ID
, NULL
, MD.DROPOFF_LOCATION_ID
, NULL
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('C'
, MD.PICKUP_LOCATION_ID)
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('C'
, MD.DROPOFF_LOCATION_ID) ) )
, DECODE(MTS.STOP_LOCATION_ID
, MD.PICKUP_LOCATION_ID
, NULL
, MD.DROPOFF_LOCATION_ID
, NULL
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('S'
, MD.PICKUP_LOCATION_ID)
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('S'
, MD.DROPOFF_LOCATION_ID) ) )
, DECODE(MTS.STOP_LOCATION_ID
, MD.PICKUP_LOCATION_ID
, NULL
, MD.DROPOFF_LOCATION_ID
, NULL
, DECODE(MTS.STOP_ID
, MDL.PICK_UP_STOP_ID
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('Z'
, MD.PICKUP_LOCATION_ID)
, MST_WB_UTIL.R_GET_POOL_LOC_DETAIL('Z'
, MD.DROPOFF_LOCATION_ID) ) )
, MST_WB_UTIL.R_GET_PREV_CARR_DETAIL (MDL.PLAN_ID
, MDL.DELIVERY_ID
, MDL.TRIP_ID
, MTS.STOP_LOCATION_ID
, 'C')
, MST_WB_UTIL.R_GET_PREV_CARR_DETAIL (MDL.PLAN_ID
, MDL.DELIVERY_ID
, MDL.TRIP_ID
, MTS.STOP_LOCATION_ID
, 'F')
FROM MST_TRIPS MT
, MST_TRIP_STOPS MTS
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, WSH_CARRIERS WC
, WSH_CARRIER_SERVICES WCS
, FTE_VEHICLE_TYPES FVT
, FTE_LOCATION_PARAMETERS FTE
, WSH_LOCATION_OWNERS WLO
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, WSH_LOCATIONS WL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND (MDL.PICK_UP_STOP_ID = MTS.STOP_ID OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
AND MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND MT.MODE_OF_TRANSPORT = 'TRUCK'
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.CARRIER_SERVICE_ID = WCS.CARRIER_SERVICE_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
AND FTE.LOCATION_ID = MTS.STOP_LOCATION_ID
AND WLO.WSH_LOCATION_ID = MTS.STOP_LOCATION_ID
AND MTS.STOP_LOCATION_ID = WL.WSH_LOCATION_ID