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_CR_SERVICES_V

Source

View Text - Preformatted


SELECT a.plan_id , a.carrier_id , a.service_level , wlk1.meaning , a.mode_of_transport , wlk.meaning , a.total_loads , a.total_cost , mp.currency_uom , (select nvl(sum(md.gross_weight),0) from mst_deliveries md where md.plan_id = a.plan_id and md.delivery_id IN ( select mdl.delivery_id from mst_trips mt , mst_delivery_legs mdl WHERE mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id and mt.plan_id = a.plan_id and mt.carrier_id = a.carrier_id and mt.service_level = a.service_level and mt.mode_of_transport = a. mode_of_transport ) ) , mp.weight_uom , (select nvl(sum(md.volume),0) from mst_deliveries md where md.plan_id = a.plan_id and md.delivery_id IN (select mdl.delivery_id from mst_trips mt , mst_delivery_legs mdl where mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id and mt.plan_id = a.plan_id and mt.carrier_id = a.carrier_id and mt.service_level = a.service_level and mt.mode_of_transport = a. mode_of_transport ) ) , mp.volume_uom , (select nvl(sum(md.number_of_pallets),0) from mst_deliveries md where md.plan_id = a.plan_id and md.delivery_id IN (select mdl.delivery_id from mst_trips mt , mst_delivery_legs mdl where mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id and mt.plan_id = a.plan_id and mt.carrier_id = a.carrier_id and mt.service_level = a.service_level and mt.mode_of_transport = a. mode_of_transport ) ) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md where md.plan_id = a.plan_id and md.delivery_id IN (select mdl.delivery_id from mst_trips mt , mst_delivery_legs mdl where mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id and mt.plan_id = a.plan_id and mt.carrier_id = a.carrier_id and mt.service_level = a.service_level and mt.mode_of_transport = a. mode_of_transport ) ) , (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 where mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and md.plan_id = a.plan_id and mda.parent_delivery_detail_id is null and md.delivery_id IN (select mdl.delivery_id from mst_trips mt , mst_delivery_legs mdl where mdl.plan_id = mt.plan_id and mdl.trip_id = mt.trip_id and mt.plan_id = a.plan_id and mt.carrier_id = a.carrier_id and mt.service_level = a.service_level and mt.mode_of_transport = a. mode_of_transport ) ) from mst_plans mp , (select mt.plan_id , mt.carrier_id , mt.service_level , mt.mode_of_transport , count(nvl(mt.trip_id,0)) total_loads , nvl(sum(mt.total_basic_transport_cost),0) + nvl(sum(mt.total_accessorial_cost),0) + nvl(sum(mt.total_stop_cost),0) + nvl(sum(mt.total_load_unload_cost),0) + nvl(sum(mt.total_layover_cost),0) + nvl(sum(mt.total_handling_cost),0) total_cost from mst_trips mt group by mt.plan_id , mt.carrier_id , mt.service_level , mt.mode_of_transport ) a , wsh_lookups wlk , wsh_lookups wlk1 where mp.plan_id = a.plan_id and a.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and a.service_level = wlk1.lookup_code and wlk1.lookup_type = 'WSH_SERVICE_LEVELS' UNION ALL select mp.plan_id , wcs.carrier_id , wcs.service_level , wlk1.meaning , wcs.mode_of_transport , wlk.meaning , 0 , 0 , mp.currency_uom , 0 , mp.weight_uom , 0 , mp.volume_uom , 0 , 0 , 0 from wsh_carrier_services wcs , wsh_lookups wlk , wsh_lookups wlk1 , mst_plans mp where wcs.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and wcs.service_level = wlk1.lookup_code and wlk1.lookup_type = 'WSH_SERVICE_LEVELS' and wcs.carrier_service_id not in (select mt.carrier_service_id from mst_trips mt where mt.plan_id = mp.plan_id)

View Text - HTML Formatted

SELECT A.PLAN_ID
, A.CARRIER_ID
, A.SERVICE_LEVEL
, WLK1.MEANING
, A.MODE_OF_TRANSPORT
, WLK.MEANING
, A.TOTAL_LOADS
, A.TOTAL_COST
, MP.CURRENCY_UOM
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = A.PLAN_ID
AND MD.DELIVERY_ID IN ( SELECT MDL.DELIVERY_ID
FROM MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID
AND MT.PLAN_ID = A.PLAN_ID
AND MT.CARRIER_ID = A.CARRIER_ID
AND MT.SERVICE_LEVEL = A.SERVICE_LEVEL
AND MT.MODE_OF_TRANSPORT = A. MODE_OF_TRANSPORT ) )
, MP.WEIGHT_UOM
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = A.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT MDL.DELIVERY_ID
FROM MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID
AND MT.PLAN_ID = A.PLAN_ID
AND MT.CARRIER_ID = A.CARRIER_ID
AND MT.SERVICE_LEVEL = A.SERVICE_LEVEL
AND MT.MODE_OF_TRANSPORT = A. MODE_OF_TRANSPORT ) )
, MP.VOLUME_UOM
, (SELECT NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = A.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT MDL.DELIVERY_ID
FROM MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID
AND MT.PLAN_ID = A.PLAN_ID
AND MT.CARRIER_ID = A.CARRIER_ID
AND MT.SERVICE_LEVEL = A.SERVICE_LEVEL
AND MT.MODE_OF_TRANSPORT = A. MODE_OF_TRANSPORT ) )
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = A.PLAN_ID
AND MD.DELIVERY_ID IN (SELECT MDL.DELIVERY_ID
FROM MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID
AND MT.PLAN_ID = A.PLAN_ID
AND MT.CARRIER_ID = A.CARRIER_ID
AND MT.SERVICE_LEVEL = A.SERVICE_LEVEL
AND MT.MODE_OF_TRANSPORT = A. MODE_OF_TRANSPORT ) )
, (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
WHERE MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PLAN_ID = A.PLAN_ID
AND MDA.PARENT_DELIVERY_DETAIL_ID IS NULL
AND MD.DELIVERY_ID IN (SELECT MDL.DELIVERY_ID
FROM MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MT.PLAN_ID
AND MDL.TRIP_ID = MT.TRIP_ID
AND MT.PLAN_ID = A.PLAN_ID
AND MT.CARRIER_ID = A.CARRIER_ID
AND MT.SERVICE_LEVEL = A.SERVICE_LEVEL
AND MT.MODE_OF_TRANSPORT = A. MODE_OF_TRANSPORT ) )
FROM MST_PLANS MP
, (SELECT MT.PLAN_ID
, MT.CARRIER_ID
, MT.SERVICE_LEVEL
, MT.MODE_OF_TRANSPORT
, COUNT(NVL(MT.TRIP_ID
, 0)) TOTAL_LOADS
, NVL(SUM(MT.TOTAL_BASIC_TRANSPORT_COST)
, 0) + NVL(SUM(MT.TOTAL_ACCESSORIAL_COST)
, 0) + NVL(SUM(MT.TOTAL_STOP_COST)
, 0) + NVL(SUM(MT.TOTAL_LOAD_UNLOAD_COST)
, 0) + NVL(SUM(MT.TOTAL_LAYOVER_COST)
, 0) + NVL(SUM(MT.TOTAL_HANDLING_COST)
, 0) TOTAL_COST
FROM MST_TRIPS MT GROUP BY MT.PLAN_ID
, MT.CARRIER_ID
, MT.SERVICE_LEVEL
, MT.MODE_OF_TRANSPORT ) A
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
WHERE MP.PLAN_ID = A.PLAN_ID
AND A.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND A.SERVICE_LEVEL = WLK1.LOOKUP_CODE
AND WLK1.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS' UNION ALL SELECT MP.PLAN_ID
, WCS.CARRIER_ID
, WCS.SERVICE_LEVEL
, WLK1.MEANING
, WCS.MODE_OF_TRANSPORT
, WLK.MEANING
, 0
, 0
, MP.CURRENCY_UOM
, 0
, MP.WEIGHT_UOM
, 0
, MP.VOLUME_UOM
, 0
, 0
, 0
FROM WSH_CARRIER_SERVICES WCS
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
, MST_PLANS MP
WHERE WCS.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND WCS.SERVICE_LEVEL = WLK1.LOOKUP_CODE
AND WLK1.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND WCS.CARRIER_SERVICE_ID NOT IN (SELECT MT.CARRIER_SERVICE_ID
FROM MST_TRIPS MT
WHERE MT.PLAN_ID = MP.PLAN_ID)