VIEW: APPS.MST_ACT_ALLTL_V

Source

View Text - Preformatted

SELECT mt.plan_id , mt.trip_id , mt.trip_number , mt.carrier_id , mt.origin_location_id , mt.destination_location_id , flp1.facility_id , flp2.facility_id , wc.freight_code , mt.service_level , mt.mode_of_transport , 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 , mst_wb_util.Get_Trip_Stops(mt.plan_id, mt.trip_id) , MST_AGG_PKG.get_total_trip_weight(mt.plan_id, mt.trip_id), mp.weight_uom, msi.maximum_load_weight max_weight, MST_AGG_PKG.get_total_trip_volume(mt.plan_id, mt.trip_id), mp.volume_uom, mt.effective_vol_capacity max_volume, MST_AGG_PKG.get_total_trip_pallets(mt.plan_id, mt.trip_id), fvt.pallet_floor_space*fvt.pallet_stacking_height max_pallets, GREATEST(mt.peak_volume_utilization, mt.peak_weight_utilization, mt.peak_pallet_utilization) * 100, mt.planned_flag , mt.release_date, fnd_date.date_to_chardt(mt.release_date) from mst_plans mp , mst_trips mt , wsh_carriers wc , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , fte_vehicle_types fvt , mtl_system_items_kfv msi WHERE mp.plan_id = mt.plan_id and mt.mode_of_transport = 'TRUCK' and mt.carrier_id = wc.carrier_id and mt.vehicle_type_id = fvt.vehicle_type_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and mt.origin_location_id = flp1.location_id and mt.destination_location_id = flp2.location_id and fvt.organization_id = msi.organization_id and fvt.inventory_item_id = msi.inventory_item_id and (mt.continuous_move_id is null or (mt.continuous_move_id is not null and MST_AGG_PKG.Get_Trip_Orders(mt.plan_id, mt.trip_id) > 0) )

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, MT.CARRIER_ID
, MT.ORIGIN_LOCATION_ID
, MT.DESTINATION_LOCATION_ID
, FLP1.FACILITY_ID
, FLP2.FACILITY_ID
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, MT.MODE_OF_TRANSPORT
, 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
, MST_WB_UTIL.GET_TRIP_STOPS(MT.PLAN_ID
, MT.TRIP_ID)
, MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT.PLAN_ID
, MT.TRIP_ID)
, MP.WEIGHT_UOM
, MSI.MAXIMUM_LOAD_WEIGHT MAX_WEIGHT
, MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT.PLAN_ID
, MT.TRIP_ID)
, MP.VOLUME_UOM
, MT.EFFECTIVE_VOL_CAPACITY MAX_VOLUME
, MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT.PLAN_ID
, MT.TRIP_ID)
, FVT.PALLET_FLOOR_SPACE*FVT.PALLET_STACKING_HEIGHT MAX_PALLETS
, GREATEST(MT.PEAK_VOLUME_UTILIZATION
, MT.PEAK_WEIGHT_UTILIZATION
, MT.PEAK_PALLET_UTILIZATION) * 100
, MT.PLANNED_FLAG
, MT.RELEASE_DATE
, FND_DATE.DATE_TO_CHARDT(MT.RELEASE_DATE)
FROM MST_PLANS MP
, MST_TRIPS MT
, WSH_CARRIERS WC
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.MODE_OF_TRANSPORT = 'TRUCK'
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MT.ORIGIN_LOCATION_ID = FLP1.LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = FLP2.LOCATION_ID
AND FVT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND (MT.CONTINUOUS_MOVE_ID IS NULL OR (MT.CONTINUOUS_MOVE_ID IS NOT NULL
AND MST_AGG_PKG.GET_TRIP_ORDERS(MT.PLAN_ID
, MT.TRIP_ID) > 0) )