VIEW: APPS.MST_TS_SUMMARY_V

Source

View Text - Preformatted

SELECT mp.plan_id , mp.compile_designator , mp.description , mt.carrier_id , mt.trip_id , mt.trip_number , mts.rowid , mts.stop_id , mts.stop_sequence_number , mts.stop_location_id , substr(mst_wb_util.get_name(mts.stop_location_id),1,80) , flp.facility_code , flp.description , wl.address1 , wl.city , wl.state , wl.postal_code , wl.country , wc.freight_code , mt.service_level , wlk.meaning , mt.mode_of_transport , msikfv.concatenated_segments , substr(mst_wb_util.get_local_chardt(mts.stop_location_id, mts.planned_arrival_date),1,40) , substr(mst_geocoding.get_timezone_code(mts.stop_location_id, mts.planned_arrival_date),1,20) , substr(mst_wb_util.get_local_chardt(mts.stop_location_id, mts.planned_departure_date),1,40) , substr(mst_geocoding.get_timezone_code(mts.stop_location_id, mts.planned_departure_date),1,20) , substr(mst_wb_util.get_hr_min((mts.planned_departure_date - mts.planned_arrival_date)*24),1,20) , mp.time_uom , substr(mst_wb_util.get_hr_min((mts.pln_loading_end_time - mts.pln_loading_start_time)*24),1,20) , mp.time_uom , substr(mst_wb_util.get_hr_min((mts.pln_unloading_end_time - mts.pln_unloading_start_time)*24),1,20) , mp.time_uom , nvl(mts.carrier_loading_cost,0) + (select nvl(sum(mdl.allocated_fac_loading_cost),0) from mst_delivery_legs mdl WHERE mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , mp.currency_uom , nvl(mts.carrier_unloading_cost,0) + (select nvl(sum(mdl.allocated_fac_unloading_cost),0) from mst_delivery_legs mdl where mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , mp.currency_uom , (select nvl(sum(md.gross_weight),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , mp.weight_uom , (select nvl(sum(md.volume),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , mp.volume_uom , (select nvl(sum(md.number_of_pallets),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.drop_off_stop_id = mts.stop_id ) , (select nvl(sum(md.gross_weight),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , mp.weight_uom , (select nvl(sum(md.volume),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , mp.volume_uom , (select nvl(sum(md.number_of_pallets),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) , (select nvl(sum(md.number_of_pieces),0) from mst_deliveries md , mst_delivery_legs mdl where md.plan_id = mdl.plan_id and md.delivery_id = mdl.delivery_id and mdl.plan_id = mts.plan_id and mdl.pick_up_stop_id = mts.stop_id ) from mst_plans mp , mst_trips mt , mst_trip_stops mts , wsh_locations wl , wsh_carriers wc , fte_location_parameters flp , fte_vehicle_types fvt , mtl_system_items_kfv msikfv , wsh_lookups wlk WHERE mp.plan_id = mt.plan_id and mt.plan_id = mts.plan_id and mt.trip_id = mts.trip_id and mts.stop_location_id = wl.wsh_location_id and wl.wsh_location_id = flp.location_id and mt.carrier_id = wc.carrier_id and mt.vehicle_type_id = fvt.vehicle_type_id and fvt.inventory_item_id = msikfv.inventory_item_id and fvt.organization_id = msikfv.organization_id and mt.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS'

View Text - HTML Formatted

SELECT MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, MT.CARRIER_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, MTS.ROWID
, MTS.STOP_ID
, MTS.STOP_SEQUENCE_NUMBER
, MTS.STOP_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS.STOP_LOCATION_ID)
, 1
, 80)
, FLP.FACILITY_CODE
, FLP.DESCRIPTION
, WL.ADDRESS1
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, WL.COUNTRY
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK.MEANING
, MT.MODE_OF_TRANSPORT
, MSIKFV.CONCATENATED_SEGMENTS
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, SUBSTR(MST_GEOCODING.GET_TIMEZONE_CODE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDT(MTS.STOP_LOCATION_ID
, MTS.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, SUBSTR(MST_GEOCODING.GET_TIMEZONE_CODE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_DEPARTURE_DATE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS.PLANNED_DEPARTURE_DATE - MTS.PLANNED_ARRIVAL_DATE)*24)
, 1
, 20)
, MP.TIME_UOM
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS.PLN_LOADING_END_TIME - MTS.PLN_LOADING_START_TIME)*24)
, 1
, 20)
, MP.TIME_UOM
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS.PLN_UNLOADING_END_TIME - MTS.PLN_UNLOADING_START_TIME)*24)
, 1
, 20)
, MP.TIME_UOM
, NVL(MTS.CARRIER_LOADING_COST
, 0) + (SELECT NVL(SUM(MDL.ALLOCATED_FAC_LOADING_COST)
, 0)
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, MP.CURRENCY_UOM
, NVL(MTS.CARRIER_UNLOADING_COST
, 0) + (SELECT NVL(SUM(MDL.ALLOCATED_FAC_UNLOADING_COST)
, 0)
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, MP.CURRENCY_UOM
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, MP.WEIGHT_UOM
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, MP.VOLUME_UOM
, (SELECT NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.GROSS_WEIGHT)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, MP.WEIGHT_UOM
, (SELECT NVL(SUM(MD.VOLUME)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, MP.VOLUME_UOM
, (SELECT NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
, (SELECT NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
FROM MST_DELIVERIES MD
, MST_DELIVERY_LEGS MDL
WHERE MD.PLAN_ID = MDL.PLAN_ID
AND MD.DELIVERY_ID = MDL.DELIVERY_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS.STOP_ID )
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_TRIP_STOPS MTS
, WSH_LOCATIONS WL
, WSH_CARRIERS WC
, FTE_LOCATION_PARAMETERS FLP
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, WSH_LOOKUPS WLK
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MTS.PLAN_ID
AND MT.TRIP_ID = MTS.TRIP_ID
AND MTS.STOP_LOCATION_ID = WL.WSH_LOCATION_ID
AND WL.WSH_LOCATION_ID = FLP.LOCATION_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MT.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'