[Home] [Help]
SELECT mt.plan_id , mt.trip_id , mts.stop_id , mt.origin_location_id , mt.destination_location_id , mt.carrier_id , mt.vehicle_type_id , mts.stop_location_id , mt.service_level , mt.trip_number , mts.stop_sequence_number , flp1.facility_code , wl1.city , wl1.state , wl1.postal_code , wl1.country , flp2.facility_code , wl2.city , wl2.state , wl2.postal_code , wl2.country , wc.freight_code , wlk.meaning , msikfv.concatenated_segments , MST_AGG_PKG.get_total_trip_weight(mt.plan_id, mt.trip_id) , mp.weight_uom , MST_AGG_PKG.get_total_trip_volume(mt.plan_id, mt.trip_id) , mp.volume_uom , MST_AGG_PKG.get_total_trip_pallets(mt.plan_id, mt.trip_id) , MST_AGG_PKG.get_total_trip_pieces(mt.plan_id, mt.trip_id) , MST_WB_UTIL.get_trip_orders(mt.plan_id, mt.trip_id) , nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_stop_cost, 0) + nvl(mt.total_load_unload_cost,0) + nvl(mt.total_layover_cost, 0) + nvl(mt.total_accessorial_cost, 0) + nvl(mt.total_handling_cost, 0) , mp.currency_uom from mst_plans mp , mst_trips mt , mst_trip_stops mts , fte_location_parameters flp1 , fte_location_parameters flp2 , wsh_locations wl1 , wsh_locations wl2 , wsh_carriers wc , wsh_lookups wlk , fte_vehicle_types fvt , mtl_system_items_kfv msikfv WHERE mp.plan_id = mt.plan_id and mt.plan_id = mts.plan_id and mt.trip_id = mts.trip_id and mt.origin_location_id = flp1.location_id and flp1.location_id = wl1.wsh_location_id and mt.destination_location_id = flp2.location_id and flp2.location_id = wl2.wsh_location_id and mt.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS' and mt.vehicle_type_id = fvt.vehicle_type_id and fvt.organization_id = msikfv.organization_id and fvt.inventory_item_id = msikfv.inventory_item_id and mt.carrier_id = wc.carrier_id and nvl(mt.planned_flag,3) = 3 and (mt.plan_id, mt.trip_id) in (select mts1.plan_id, mts1.trip_id from mst_trip_stops mts1 group by mts1.plan_id, mts1.trip_id having count(mts1.trip_id) > 2)
SELECT MT.PLAN_ID
, MT.TRIP_ID
, MTS.STOP_ID
, MT.ORIGIN_LOCATION_ID
, MT.DESTINATION_LOCATION_ID
, MT.CARRIER_ID
, MT.VEHICLE_TYPE_ID
, MTS.STOP_LOCATION_ID
, MT.SERVICE_LEVEL
, MT.TRIP_NUMBER
, MTS.STOP_SEQUENCE_NUMBER
, FLP1.FACILITY_CODE
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, FLP2.FACILITY_CODE
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, WC.FREIGHT_CODE
, WLK.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, MST_AGG_PKG.GET_TOTAL_TRIP_WEIGHT(MT.PLAN_ID
, MT.TRIP_ID)
, MP.WEIGHT_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_VOLUME(MT.PLAN_ID
, MT.TRIP_ID)
, MP.VOLUME_UOM
, MST_AGG_PKG.GET_TOTAL_TRIP_PALLETS(MT.PLAN_ID
, MT.TRIP_ID)
, MST_AGG_PKG.GET_TOTAL_TRIP_PIECES(MT.PLAN_ID
, MT.TRIP_ID)
, MST_WB_UTIL.GET_TRIP_ORDERS(MT.PLAN_ID
, MT.TRIP_ID)
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_STOP_COST
, 0) + NVL(MT.TOTAL_LOAD_UNLOAD_COST
, 0) + NVL(MT.TOTAL_LAYOVER_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0) + NVL(MT.TOTAL_HANDLING_COST
, 0)
, MP.CURRENCY_UOM
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_TRIP_STOPS MTS
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, WSH_CARRIERS WC
, WSH_LOOKUPS WLK
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MTS.PLAN_ID
AND MT.TRIP_ID = MTS.TRIP_ID
AND MT.ORIGIN_LOCATION_ID = FLP1.LOCATION_ID
AND FLP1.LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = FLP2.LOCATION_ID
AND FLP2.LOCATION_ID = WL2.WSH_LOCATION_ID
AND MT.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND NVL(MT.PLANNED_FLAG
, 3) = 3
AND (MT.PLAN_ID
, MT.TRIP_ID) IN (SELECT MTS1.PLAN_ID
, MTS1.TRIP_ID
FROM MST_TRIP_STOPS MTS1 GROUP BY MTS1.PLAN_ID
, MTS1.TRIP_ID HAVING COUNT(MTS1.TRIP_ID) > 2)
|
|
|
|