VIEW: APPS.MST_SR_SUMMARY_V

Source

View Text - Preformatted

SELECT mp.plan_id , mp.compile_designator , mp.description , mt.carrier_id , wc.freight_code , mt.service_level , wlk1.meaning , mt.mode_of_transport , wlk.meaning , nvl(count(mt.trip_id),0) , 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) , mp.currency_uom , mp.weight_uom , mp.volume_uom from mst_plans mp , mst_trips mt , wsh_carriers wc , wsh_lookups wlk , wsh_lookups wlk1 WHERE mp.plan_id = mt.plan_id and mt.carrier_id = wc.carrier_id and mt.mode_of_transport = wlk.lookup_code and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT' and mt.service_level = wlk1.lookup_code and wlk1.lookup_type = 'WSH_SERVICE_LEVELS' group by mp.plan_id , mp.compile_designator , mp.description , mt.carrier_id , wc.freight_code , mt.service_level , wlk1.meaning , mt.mode_of_transport , wlk.meaning , mp.currency_uom , mp.weight_uom , mp.volume_uom UNION ALL select mp.plan_id , mp.compile_designator , mp.description , wcs.carrier_id , wc.freight_code , wcs.service_level , wlk1.meaning , wcs.mode_of_transport , wlk.meaning , 0 , 0 , mp.currency_uom , mp.weight_uom , mp.volume_uom from wsh_carrier_services wcs , wsh_carriers wc , 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_id = wc.carrier_id 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 MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, MT.CARRIER_ID
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK1.MEANING
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, NVL(COUNT(MT.TRIP_ID)
, 0)
, 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)
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_TRIPS MT
, WSH_CARRIERS WC
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.MODE_OF_TRANSPORT = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MT.SERVICE_LEVEL = WLK1.LOOKUP_CODE
AND WLK1.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS' GROUP BY MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, MT.CARRIER_ID
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK1.MEANING
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM UNION ALL SELECT MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, WCS.CARRIER_ID
, WC.FREIGHT_CODE
, WCS.SERVICE_LEVEL
, WLK1.MEANING
, WCS.MODE_OF_TRANSPORT
, WLK.MEANING
, 0
, 0
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM WSH_CARRIER_SERVICES WCS
, WSH_CARRIERS WC
, 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_ID = WC.CARRIER_ID
AND WCS.CARRIER_SERVICE_ID NOT IN (SELECT MT.CARRIER_SERVICE_ID
FROM MST_TRIPS MT
WHERE MT.PLAN_ID = MP.PLAN_ID)