VIEW: APPS.MST_ACT_EXPENSIVE_LOADS_V

Source

View Text - Preformatted

SELECT mt.plan_id , mt.trip_id , mt.continuous_move_id , mt.trip_number , mt.origin_location_id , mt.destination_location_id , mt.planned_flag , flp1.facility_id , flp2.facility_id , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(mt.origin_location_id , mt.trip_start_date), 1, 40) , substr(mst_wb_util.get_local_chardtzone(mt.destination_location_id , mt.trip_end_date), 1, 40) , MST_AGG_PKG.get_total_trip_weight(mt.plan_id, mt.trip_id) , mp.weight_uom , MST_AGG_PKG.get_total_trip_volume(mt.plan_id, mt.trip_id) , mp.volume_uom , MST_AGG_PKG.get_total_trip_pallets(mt.plan_id, mt.trip_id) , MST_AGG_PKG.get_total_trip_pieces(mt.plan_id, mt.trip_id) , nvl(mt.total_basic_transport_cost,0) , mp.currency_uom from mst_plans mp , mst_trips mt , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 where mp.plan_id = mt.plan_id and mt.mode_of_transport = 'LTL' and mt.origin_location_id = wl1.wsh_location_id and wl1.wsh_location_id = flp1.location_id and mt.destination_location_id = wl2.wsh_location_id and wl2.wsh_location_id = flp2.location_id and mt.planned_flag <> 4 order by mt.total_basic_transport_cost desc

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MT.CONTINUOUS_MOVE_ID
, MT.TRIP_NUMBER
, MT.ORIGIN_LOCATION_ID
, MT.DESTINATION_LOCATION_ID
, MT.PLANNED_FLAG
, FLP1.FACILITY_ID
, FLP2.FACILITY_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MT.ORIGIN_LOCATION_ID
, MT.TRIP_START_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MT.DESTINATION_LOCATION_ID
, MT.TRIP_END_DATE)
, 1
, 40)
, MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT.PLAN_ID
, MT.TRIP_ID)
, MP.WEIGHT_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT.PLAN_ID
, MT.TRIP_ID)
, MP.VOLUME_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT.PLAN_ID
, MT.TRIP_ID)
, MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(MT.PLAN_ID
, MT.TRIP_ID)
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0)
, MP.CURRENCY_UOM
FROM MST_PLANS MP
, MST_TRIPS MT
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.MODE_OF_TRANSPORT = 'LTL'
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND WL1.WSH_LOCATION_ID = FLP1.LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND WL2.WSH_LOCATION_ID = FLP2.LOCATION_ID
AND MT.PLANNED_FLAG <> 4 ORDER BY MT.TOTAL_BASIC_TRANSPORT_COST DESC