VIEW: APPS.MST_EXCEED_CAR_VEH_LOADS_V

Source

View Text - Preformatted

SELECT mt.plan_id , mt.trip_id , mt.trip_number , mt.trip_start_date , mt.carrier_id , mt.service_level , wlk.meaning , mt.mode_of_transport , mt.planned_flag , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , nvl(sum(mdd.gross_weight),0) , mp.weight_uom , nvl(sum(mdd.volume),0) , mp.volume_uom , nvl(sum(mdd.number_of_pallets),0) , nvl(sum(mdd.requested_quantity),0) , count(distinct mdd.source_code||mdd.source_header_number) from mst_plans mp , mst_trips mt , mst_delivery_legs mdl , mst_deliveries md , mst_delivery_assignments mda , mst_delivery_details mdd , wsh_locations wl1 , wsh_locations wl2 , wsh_lookups wlk WHERE mp.plan_id = mt.plan_id and mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mdl.plan_id = md.plan_id and mdl.delivery_id = md.delivery_id and md.plan_id = mda.plan_id and md.delivery_id = mda.delivery_id and mda.plan_id = mdd.plan_id and mda.delivery_detail_id = mdd.delivery_detail_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' group by mt.plan_id , mt.trip_id , mt.trip_number , mt.trip_start_date , mt.carrier_id , mt.service_level , wlk.meaning , mt.mode_of_transport , mt.planned_flag , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , mp.weight_uom , mp.volume_uom

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, MT.TRIP_START_DATE
, MT.CARRIER_ID
, MT.SERVICE_LEVEL
, WLK.MEANING
, MT.MODE_OF_TRANSPORT
, MT.PLANNED_FLAG
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, NVL(SUM(MDD.GROSS_WEIGHT)
, 0)
, MP.WEIGHT_UOM
, NVL(SUM(MDD.VOLUME)
, 0)
, MP.VOLUME_UOM
, NVL(SUM(MDD.NUMBER_OF_PALLETS)
, 0)
, NVL(SUM(MDD.REQUESTED_QUANTITY)
, 0)
, COUNT(DISTINCT MDD.SOURCE_CODE||MDD.SOURCE_HEADER_NUMBER)
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERY_DETAILS MDD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, WSH_LOOKUPS WLK
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MDL.PLAN_ID
AND MT.TRIP_ID = MDL.TRIP_ID
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID
AND MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_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' GROUP BY MT.PLAN_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, MT.TRIP_START_DATE
, MT.CARRIER_ID
, MT.SERVICE_LEVEL
, WLK.MEANING
, MT.MODE_OF_TRANSPORT
, MT.PLANNED_FLAG
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, MP.WEIGHT_UOM
, MP.VOLUME_UOM