VIEW: APPS.MST_CR_SUMMARY_V

Source

View Text - Preformatted

SELECT mp.plan_id , mp.compile_designator , mp.description , mt.carrier_id , wc.freight_code , 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 WHERE mp.plan_id = mt.plan_id and mt.carrier_id = wc.carrier_id group by mp.plan_id , mp.compile_designator , mp.description , mt.carrier_id , wc.freight_code , mp.currency_uom , mp.weight_uom , mp.volume_uom UNION ALL select mp.plan_id , mp.compile_designator , mp.description , wc.carrier_id , wc.freight_code , 0 , 0 , mp.currency_uom , mp.weight_uom , mp.volume_uom from mst_plans mp , wsh_carriers wc where wc.carrier_id not in (select mt.carrier_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
, 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
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.CARRIER_ID = WC.CARRIER_ID GROUP BY MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, MT.CARRIER_ID
, WC.FREIGHT_CODE
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM UNION ALL SELECT MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, WC.CARRIER_ID
, WC.FREIGHT_CODE
, 0
, 0
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, WSH_CARRIERS WC
WHERE WC.CARRIER_ID NOT IN (SELECT MT.CARRIER_ID
FROM MST_TRIPS MT
WHERE MT.PLAN_ID = MP.PLAN_ID)