VIEW: APPS.MST_UTILIZATION_VEHICLE_CAP_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , mt.trip_id , mt.mode_of_transport , med.carrier_id , med.vehicle_type_id , mt.trip_number , wc.freight_code , mt.service_level , wlk.meaning , msikfv.concatenated_segments , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , (select max(mts.departure_gross_weight) from mst_trip_stops mts where mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id) , mp.weight_uom , med.number2 , nvl(mt.peak_weight_utilization,0) * 100 , (select max(mts.departure_volume) from mst_trip_stops mts where mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id) , mp.volume_uom , med.number1 , nvl(mt.peak_volume_utilization,0) * 100 , (select max(mts.departure_pallets) from mst_trip_stops mts where mts.plan_id = mt.plan_id and mts.trip_id = mt.trip_id) , med.number3 , nvl(mt.peak_pallet_utilization,0) * 100 , greatest(nvl(mt.peak_weight_utilization,0) , nvl(mt.peak_volume_utilization,0) , nvl(mt.peak_pallet_utilization,0) ) * 100 , msikfv.internal_volume * mst_wb_util.get_uom_conversion_rate (msikfv.volume_uom_code, mp.volume_uom, msikfv.organization_id, msikfv.inventory_item_id) , ml.meaning , substr(mst_wb_util.get_threshold_value(med.exception_type),1,20) , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mst_plans mp , mst_trips mt , wsh_locations wl1 , wsh_locations wl2 , wsh_carriers wc , mfg_lookups ml , fte_vehicle_types fvt , mtl_system_items_kfv msikfv , wsh_lookups wlk WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mp.plan_id and med.plan_id = mt.plan_id and med.trip_id1 = mt.trip_id and med.carrier_id = wc.carrier_id and med.vehicle_type_id = fvt.vehicle_type_id and fvt.inventory_item_id = msikfv.inventory_item_id and fvt.organization_id = msikfv.organization_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and mt.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS'

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MT.TRIP_ID
, MT.MODE_OF_TRANSPORT
, MED.CARRIER_ID
, MED.VEHICLE_TYPE_ID
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, (SELECT MAX(MTS.DEPARTURE_GROSS_WEIGHT)
FROM MST_TRIP_STOPS MTS
WHERE MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID)
, MP.WEIGHT_UOM
, MED.NUMBER2
, NVL(MT.PEAK_WEIGHT_UTILIZATION
, 0) * 100
, (SELECT MAX(MTS.DEPARTURE_VOLUME)
FROM MST_TRIP_STOPS MTS
WHERE MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID)
, MP.VOLUME_UOM
, MED.NUMBER1
, NVL(MT.PEAK_VOLUME_UTILIZATION
, 0) * 100
, (SELECT MAX(MTS.DEPARTURE_PALLETS)
FROM MST_TRIP_STOPS MTS
WHERE MTS.PLAN_ID = MT.PLAN_ID
AND MTS.TRIP_ID = MT.TRIP_ID)
, MED.NUMBER3
, NVL(MT.PEAK_PALLET_UTILIZATION
, 0) * 100
, GREATEST(NVL(MT.PEAK_WEIGHT_UTILIZATION
, 0)
, NVL(MT.PEAK_VOLUME_UTILIZATION
, 0)
, NVL(MT.PEAK_PALLET_UTILIZATION
, 0) ) * 100
, MSIKFV.INTERNAL_VOLUME * MST_WB_UTIL.GET_UOM_CONVERSION_RATE (MSIKFV.VOLUME_UOM_CODE
, MP.VOLUME_UOM
, MSIKFV.ORGANIZATION_ID
, MSIKFV.INVENTORY_ITEM_ID)
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_THRESHOLD_VALUE(MED.EXCEPTION_TYPE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MST_PLANS MP
, MST_TRIPS MT
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, WSH_CARRIERS WC
, MFG_LOOKUPS ML
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, WSH_LOOKUPS WLK
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MP.PLAN_ID
AND MED.PLAN_ID = MT.PLAN_ID
AND MED.TRIP_ID1 = MT.TRIP_ID
AND MED.CARRIER_ID = WC.CARRIER_ID
AND MED.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MT.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'