VIEW: APPS.MST_DD_DELIVERY_LEGS_V

Source

View Text - Preformatted

SELECT mdl.plan_id , mdl.delivery_id , mdl.sequence_number , mt.carrier_id , wc.freight_code , mts1.stop_location_id , mts2.stop_location_id , mt.trip_id , mt.trip_number , mt.mode_of_transport , wlk.meaning , mt.service_level , wlk1.meaning , substr(mst_wb_util.get_name(mts1.stop_location_id),1,80) , flp1.facility_code , flp1.description , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mts2.stop_location_id),1,80) , flp2.facility_code , flp2.description , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(mts1.stop_location_id, mts1.planned_departure_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mts2.stop_location_id, mts2.planned_arrival_date),1,40) , mts1.planned_departure_date , mts2.planned_arrival_date , nvl(mdl.allocated_transport_cost,0) + nvl(mdl.allocated_fac_shp_hand_cost,0) + nvl(mdl.allocated_fac_rec_hand_cost,0) + nvl(mdl.allocated_fac_loading_cost,0) + nvl(mdl.allocated_fac_unloading_cost,0) , mp.currency_uom from mst_delivery_legs mdl , mst_trip_stops mts1 , mst_trip_stops mts2 , mst_trips mt , mst_plans mp , wsh_carriers wc , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , wsh_lookups wlk , wsh_lookups wlk1 WHERE mdl.plan_id = mts1.plan_id and mdl.pick_up_stop_id = mts1.stop_id and mts1.plan_id = mt.plan_id and mts1.trip_id = mt.trip_id and mts1.stop_location_id = wl1.wsh_location_id and mts1.stop_location_id = flp1.location_id and mt.plan_id = mp.plan_id and mt.carrier_id = wc.carrier_id and mdl.plan_id = mts2.plan_id and mdl.drop_off_stop_id = mts2.stop_id and mts2.plan_id = mt.plan_id and mts2.trip_id = mt.trip_id and mts2.stop_location_id = wl2.wsh_location_id and mts2.stop_location_id = flp2.location_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'

View Text - HTML Formatted

SELECT MDL.PLAN_ID
, MDL.DELIVERY_ID
, MDL.SEQUENCE_NUMBER
, MT.CARRIER_ID
, WC.FREIGHT_CODE
, MTS1.STOP_LOCATION_ID
, MTS2.STOP_LOCATION_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, MT.SERVICE_LEVEL
, WLK1.MEANING
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS1.STOP_LOCATION_ID)
, 1
, 80)
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS2.STOP_LOCATION_ID)
, 1
, 80)
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, MTS1.PLANNED_DEPARTURE_DATE
, MTS2.PLANNED_ARRIVAL_DATE
, NVL(MDL.ALLOCATED_TRANSPORT_COST
, 0) + NVL(MDL.ALLOCATED_FAC_SHP_HAND_COST
, 0) + NVL(MDL.ALLOCATED_FAC_REC_HAND_COST
, 0) + NVL(MDL.ALLOCATED_FAC_LOADING_COST
, 0) + NVL(MDL.ALLOCATED_FAC_UNLOADING_COST
, 0)
, MP.CURRENCY_UOM
FROM MST_DELIVERY_LEGS MDL
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
, MST_TRIPS MT
, MST_PLANS MP
, WSH_CARRIERS WC
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
WHERE MDL.PLAN_ID = MTS1.PLAN_ID
AND MDL.PICK_UP_STOP_ID = MTS1.STOP_ID
AND MTS1.PLAN_ID = MT.PLAN_ID
AND MTS1.TRIP_ID = MT.TRIP_ID
AND MTS1.STOP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MTS1.STOP_LOCATION_ID = FLP1.LOCATION_ID
AND MT.PLAN_ID = MP.PLAN_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MDL.PLAN_ID = MTS2.PLAN_ID
AND MDL.DROP_OFF_STOP_ID = MTS2.STOP_ID
AND MTS2.PLAN_ID = MT.PLAN_ID
AND MTS2.TRIP_ID = MT.TRIP_ID
AND MTS2.STOP_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MTS2.STOP_LOCATION_ID = FLP2.LOCATION_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'