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_TL_SUMMARY_V

Source

View Text - Preformatted


SELECT mp.plan_id , mp.compile_designator , mp.description , mt.trip_id , mt.carrier_id , mt.mode_of_transport , mt.trip_number , mts1.rowid , mts2.rowid , mts1.stop_id , mts2.stop_id , mts1.stop_sequence_number , (select count(*) from mst_trip_stops mts WHERE mts.plan_id = mts1.plan_id and mts.trip_id = mt.trip_id and mts.stop_sequence_number <= mts1.stop_sequence_number ) , wc.freight_code , mt.service_level , wlk.meaning , msikfv.concatenated_segments , nvl(mts1.distance_to_next_stop,0) , mp.distance_uom , nvl(mts1.distance_cost,0) , mp.currency_uom , mts1.stop_location_id , mts2.stop_location_id , substr(mst_wb_util.get_name(mts1.stop_location_id),1,80) , flp1.facility_code , flp1.description , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mts2.stop_location_id),1,80) , flp2.facility_code , flp2.description , wl2.city , wl2.state , wl2.postal_code , wl2.country , nvl(mts1.departure_gross_weight,0) , nvl(mts1.departure_volume,0) , nvl(mts1.departure_pallets,0) , nvl(mts1.departure_pieces,0) , nvl((select count(distinct nvl(mdd.split_from_delivery_detail_id,mdd.delivery_detail_id)) from mst_delivery_details mdd , mst_delivery_assignments mda , mst_deliveries md , mst_delivery_legs mdl where mdd.split_from_delivery_detail_id is null and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.parent_delivery_detail_id is null and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts1.plan_id and mdl.pick_up_stop_id IN (select mts3.stop_id from mst_trip_stops mts3 where mts3.plan_id = mts1.plan_id and mts3.trip_id = mts1.trip_id and mts3.stop_sequence_number <= mts1.stop_sequence_number) ),0) - nvl((select count(distinct nvl(mdd.split_from_delivery_detail_id,mdd.delivery_detail_id)) from mst_delivery_details mdd , mst_delivery_assignments mda , mst_deliveries md , mst_delivery_legs mdl where mdd.split_from_delivery_detail_id is null and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.parent_delivery_detail_id is null and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts1.plan_id and mdl.drop_off_stop_id IN (select mts3.stop_id from mst_trip_stops mts3 where mts3.plan_id = mts1.plan_id and mts3.trip_id = mts1.trip_id and mts3.stop_sequence_number <= mts1.stop_sequence_number) ) ,0) , nvl(msi.maximum_load_weight,0) , nvl(mt.effective_vol_capacity,0) , nvl(fvt.pallet_floor_space,0) * nvl(fvt.pallet_stacking_height,0) , mp.weight_uom , mp.volume_uom , substr(mst_wb_util.get_local_chardt(mts1.stop_location_id, mts1.planned_departure_date),1,40) , substr(mst_geocoding.get_timezone_code(mts1.stop_location_id, mts1.planned_departure_date),1,20) , substr(mst_wb_util.get_local_chardt(mts2.stop_location_id, mts2.planned_arrival_date),1,40) , substr(mst_geocoding.get_timezone_code(mts2.stop_location_id, mts2.planned_arrival_date),1,20) , substr(mst_wb_util.get_hr_min (mts1.drv_time_to_next_stop),1,20) , mp.time_uom , substr(mst_wb_util.get_hr_min (mts1.total_layover_duration),1,20) , mp.time_uom , substr(mst_wb_util.get_hr_min (decode(sign((mts2.planned_arrival_date - mts1.planned_departure_date)*24 - mts1.total_layover_duration - mts1.drv_time_to_next_stop) ,-1,0,((mts2.planned_arrival_date - mts1.planned_departure_date)*24 - mts1.total_layover_duration - mts1.drv_time_to_next_stop))),1,20) , mp.time_uom from mst_plans mp , mst_trips mt , mst_trip_stops mts1 , mst_trip_stops mts2 , wsh_locations wl1 , wsh_locations wl2 , wsh_carriers wc , mtl_system_items msi , fte_location_parameters flp1 , fte_location_parameters flp2 , fte_vehicle_types fvt , mtl_system_items_kfv msikfv , wsh_lookups wlk where mp.plan_id = mt.plan_id and mt.plan_id = mts1.plan_id and mt.trip_id = mts1.trip_id and mt.plan_id = mts2.plan_id and mt.trip_id = mts2.trip_id and mts1.stop_location_id = wl1.wsh_location_id and mts2.stop_location_id = wl2.wsh_location_id and mt.carrier_id = wc.carrier_id and mt.vehicle_type_id = fvt.vehicle_type_id and fvt.inventory_item_id = msi.inventory_item_id and fvt.organization_id = msi.organization_id and fvt.inventory_item_id = msikfv.inventory_item_id and fvt.organization_id = msikfv.organization_id and mts1.stop_location_id = flp1.location_id and mts2.stop_location_id = flp2.location_id and mt.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS'

View Text - HTML Formatted

SELECT MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, MT.TRIP_ID
, MT.CARRIER_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, MTS1.ROWID
, MTS2.ROWID
, MTS1.STOP_ID
, MTS2.STOP_ID
, MTS1.STOP_SEQUENCE_NUMBER
, (SELECT COUNT(*)
FROM MST_TRIP_STOPS MTS
WHERE MTS.PLAN_ID = MTS1.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID
AND MTS.STOP_SEQUENCE_NUMBER <= MTS1.STOP_SEQUENCE_NUMBER )
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, NVL(MTS1.DISTANCE_TO_NEXT_STOP
, 0)
, MP.DISTANCE_UOM
, NVL(MTS1.DISTANCE_COST
, 0)
, MP.CURRENCY_UOM
, MTS1.STOP_LOCATION_ID
, MTS2.STOP_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS1.STOP_LOCATION_ID)
, 1
, 80)
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS2.STOP_LOCATION_ID)
, 1
, 80)
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, NVL(MTS1.DEPARTURE_GROSS_WEIGHT
, 0)
, NVL(MTS1.DEPARTURE_VOLUME
, 0)
, NVL(MTS1.DEPARTURE_PALLETS
, 0)
, NVL(MTS1.DEPARTURE_PIECES
, 0)
, NVL((SELECT COUNT(DISTINCT NVL(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID))
FROM MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MDD.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PARENT_DELIVERY_DETAIL_ID IS NULL
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS1.PLAN_ID
AND MDL.PICK_UP_STOP_ID IN (SELECT MTS3.STOP_ID
FROM MST_TRIP_STOPS MTS3
WHERE MTS3.PLAN_ID = MTS1.PLAN_ID
AND MTS3.TRIP_ID = MTS1.TRIP_ID
AND MTS3.STOP_SEQUENCE_NUMBER <= MTS1.STOP_SEQUENCE_NUMBER) )
, 0) - NVL((SELECT COUNT(DISTINCT NVL(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID))
FROM MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MDD.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PARENT_DELIVERY_DETAIL_ID IS NULL
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS1.PLAN_ID
AND MDL.DROP_OFF_STOP_ID IN (SELECT MTS3.STOP_ID
FROM MST_TRIP_STOPS MTS3
WHERE MTS3.PLAN_ID = MTS1.PLAN_ID
AND MTS3.TRIP_ID = MTS1.TRIP_ID
AND MTS3.STOP_SEQUENCE_NUMBER <= MTS1.STOP_SEQUENCE_NUMBER) )
, 0)
, NVL(MSI.MAXIMUM_LOAD_WEIGHT
, 0)
, NVL(MT.EFFECTIVE_VOL_CAPACITY
, 0)
, NVL(FVT.PALLET_FLOOR_SPACE
, 0) * NVL(FVT.PALLET_STACKING_HEIGHT
, 0)
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, SUBSTR(MST_GEOCODING.GET_TIMEZONE_CODE(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, SUBSTR(MST_GEOCODING.GET_TIMEZONE_CODE(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN (MTS1.DRV_TIME_TO_NEXT_STOP)
, 1
, 20)
, MP.TIME_UOM
, SUBSTR(MST_WB_UTIL.GET_HR_MIN (MTS1.TOTAL_LAYOVER_DURATION)
, 1
, 20)
, MP.TIME_UOM
, SUBSTR(MST_WB_UTIL.GET_HR_MIN (DECODE(SIGN((MTS2.PLANNED_ARRIVAL_DATE - MTS1.PLANNED_DEPARTURE_DATE)*24 - MTS1.TOTAL_LAYOVER_DURATION - MTS1.DRV_TIME_TO_NEXT_STOP)
, -1
, 0
, ((MTS2.PLANNED_ARRIVAL_DATE - MTS1.PLANNED_DEPARTURE_DATE)*24 - MTS1.TOTAL_LAYOVER_DURATION - MTS1.DRV_TIME_TO_NEXT_STOP)))
, 1
, 20)
, MP.TIME_UOM
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, WSH_CARRIERS WC
, MTL_SYSTEM_ITEMS MSI
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, WSH_LOOKUPS WLK
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MTS1.PLAN_ID
AND MT.TRIP_ID = MTS1.TRIP_ID
AND MT.PLAN_ID = MTS2.PLAN_ID
AND MT.TRIP_ID = MTS2.TRIP_ID
AND MTS1.STOP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MTS2.STOP_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MTS2.STOP_LOCATION_ID = FLP2.LOCATION_ID
AND MT.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'