VIEW: APPS.MST_ACT_OPP_LTL_V

Source

View Text - Preformatted

SELECT mt.plan_id PLAN_ID , mt1.trip_id MASTER_TRIP_ID , mt.trip_id TRIP_ID , mt.mode_of_transport, mt.trip_number TRIP_NUMBER , mt.carrier_id CARRIER_ID , mt.carrier_service_id CARRIER_SERVICE_ID , mt.origin_location_id ORIGIN_LOCATION_ID , mt.destination_location_id DESTINATION_LOCATION_ID , flp1.facility_id ORIGIN_FACILITY_ID , flp2.facility_id DESTINATION_FACILITY_ID , mt.vehicle_type_id VEHICLE_TYPE_ID , wc.freight_code CARRIER_NAME , mt.service_level SERVICE_LEVEL , wlk.meaning SERVICE_LEVEL_TL , msi.concatenated_segments VEHICLE_TYPE , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) ORIGIN_COMPANY , wl1.city ORIGIN_CITY , wl1.state ORIGIN_STATE , wl1.postal_code ORIGIN_ZIP , wl1.country ORIGIN_COUNTRY , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) DESTINATION_COMPANY , wl2.city DESTINATION_CITY , wl2.state DESTINATION_STATE , wl2.postal_code DESTINATION_ZIP , wl2.country DESTINATION_COUNTRY , round((nvl(mt.peak_weight_utilization, 0) * 100), 1) WEIGHT_UTILIZATION , round((nvl(mt.peak_volume_utilization, 0) * 100), 1) VOLUME_UTILIZATION , round((nvl(mt.peak_pallet_utilization, 0) * 100), 1) PALLET_UTILIZATION , nvl( (MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt.plan_id, mt.trip_id) + MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(mt1.plan_id, mt1.trip_id)) / decode(nvl(mt.effective_vol_capacity, 1), 0, 1, nvl(mt.effective_vol_capacity, 1)), 0) * 100 UTILIZATION_FACTOR from mst_trips mt , mst_trips mt1 , wsh_carriers wc , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , wsh_lookups wlk, fte_vehicle_types fvt , mtl_system_items_kfv msi WHERE mt.plan_id = mt1.plan_id and mt1.mode_of_transport in ('LTL','PARCEL') 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 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 fvt.organization_id = msi.organization_id and fvt.inventory_item_id = msi.inventory_item_id and mt.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS' and nvl(mt.planned_flag,3) in (2,3)

View Text - HTML Formatted

SELECT MT.PLAN_ID PLAN_ID
, MT1.TRIP_ID MASTER_TRIP_ID
, MT.TRIP_ID TRIP_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER TRIP_NUMBER
, MT.CARRIER_ID CARRIER_ID
, MT.CARRIER_SERVICE_ID CARRIER_SERVICE_ID
, MT.ORIGIN_LOCATION_ID ORIGIN_LOCATION_ID
, MT.DESTINATION_LOCATION_ID DESTINATION_LOCATION_ID
, FLP1.FACILITY_ID ORIGIN_FACILITY_ID
, FLP2.FACILITY_ID DESTINATION_FACILITY_ID
, MT.VEHICLE_TYPE_ID VEHICLE_TYPE_ID
, WC.FREIGHT_CODE CARRIER_NAME
, MT.SERVICE_LEVEL SERVICE_LEVEL
, WLK.MEANING SERVICE_LEVEL_TL
, MSI.CONCATENATED_SEGMENTS VEHICLE_TYPE
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80) ORIGIN_COMPANY
, WL1.CITY ORIGIN_CITY
, WL1.STATE ORIGIN_STATE
, WL1.POSTAL_CODE ORIGIN_ZIP
, WL1.COUNTRY ORIGIN_COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80) DESTINATION_COMPANY
, WL2.CITY DESTINATION_CITY
, WL2.STATE DESTINATION_STATE
, WL2.POSTAL_CODE DESTINATION_ZIP
, WL2.COUNTRY DESTINATION_COUNTRY
, ROUND((NVL(MT.PEAK_WEIGHT_UTILIZATION
, 0) * 100)
, 1) WEIGHT_UTILIZATION
, ROUND((NVL(MT.PEAK_VOLUME_UTILIZATION
, 0) * 100)
, 1) VOLUME_UTILIZATION
, ROUND((NVL(MT.PEAK_PALLET_UTILIZATION
, 0) * 100)
, 1) PALLET_UTILIZATION
, NVL( (MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT.PLAN_ID
, MT.TRIP_ID) + MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT1.PLAN_ID
, MT1.TRIP_ID)) / DECODE(NVL(MT.EFFECTIVE_VOL_CAPACITY
, 1)
, 0
, 1
, NVL(MT.EFFECTIVE_VOL_CAPACITY
, 1))
, 0) * 100 UTILIZATION_FACTOR
FROM MST_TRIPS MT
, MST_TRIPS MT1
, WSH_CARRIERS WC
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, WSH_LOOKUPS WLK
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE MT.PLAN_ID = MT1.PLAN_ID
AND MT1.MODE_OF_TRANSPORT IN ('LTL'
, 'PARCEL')
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 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 FVT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MT.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND NVL(MT.PLANNED_FLAG
, 3) IN (2
, 3)