VIEW: APPS.MST_INCONSISTENT_TIME_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , mt.trip_id , med.exception_detail_id , mt.trip_number , wc.freight_code , mt.service_level , wlk1.meaning , mt.mode_of_transport , wlk.meaning , substr(mst_wb_util.get_name(mts1.stop_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mts2.stop_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , decode(mt.mode_of_transport,'TRUCK',nvl(mts1.distance_to_next_stop,0),null) , mp.distance_uom , substr(mst_wb_util.get_local_chardtzone(mts1.stop_location_id, mts1.planned_departure_date),1,40) , mts1.planned_departure_date , substr(mst_wb_util.get_local_chardtzone(mts2.stop_location_id, mts2.planned_arrival_date),1,40) , mts2.planned_arrival_date , substr(mst_wb_util.get_hr_min((mts2.planned_arrival_date - mts1.planned_departure_date)*24),1,20) , substr(mst_wb_util.get_hr_min(nvl(med.number1,0)),1,20) , substr(mst_wb_util.get_hr_min(abs(nvl(med.number1,0) - (mts2.planned_arrival_date - mts1.planned_departure_date)*24)),1,20) , ml.meaning , substr(mst_wb_util.get_threshold_value(med.exception_type),1,20) , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_plans mp , mst_exception_details med , mst_trips mt , mst_trip_stops mts1 , mst_trip_stops mts2 , wsh_carriers wc , mfg_lookups ml , wsh_lookups wlk , wsh_lookups wlk1 , wsh_locations wl1 , wsh_locations wl2 WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mp.plan_id and med.plan_id = mt.plan_id and med.trip_id1 = mt.trip_id and med.plan_id = mts1.plan_id and med.stop_id1 = mts1.stop_id and med.plan_id = mts2.plan_id and med.stop_id2 = mts2.stop_id and mt.carrier_id = wc.carrier_id and mts1.stop_location_id = wl1.wsh_location_id and mts2.stop_location_id = wl2.wsh_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 MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MT.TRIP_ID
, MED.EXCEPTION_DETAIL_ID
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK1.MEANING
, MT.MODE_OF_TRANSPORT
, WLK.MEANING
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS1.STOP_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MTS2.STOP_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, DECODE(MT.MODE_OF_TRANSPORT
, 'TRUCK'
, NVL(MTS1.DISTANCE_TO_NEXT_STOP
, 0)
, NULL)
, MP.DISTANCE_UOM
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS1.STOP_LOCATION_ID
, MTS1.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, MTS1.PLANNED_DEPARTURE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS2.STOP_LOCATION_ID
, MTS2.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, MTS2.PLANNED_ARRIVAL_DATE
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MTS2.PLANNED_ARRIVAL_DATE - MTS1.PLANNED_DEPARTURE_DATE)*24)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER1
, 0))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(ABS(NVL(MED.NUMBER1
, 0) - (MTS2.PLANNED_ARRIVAL_DATE - MTS1.PLANNED_DEPARTURE_DATE)*24))
, 1
, 20)
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_THRESHOLD_VALUE(MED.EXCEPTION_TYPE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_PLANS MP
, MST_EXCEPTION_DETAILS MED
, MST_TRIPS MT
, MST_TRIP_STOPS MTS1
, MST_TRIP_STOPS MTS2
, WSH_CARRIERS WC
, MFG_LOOKUPS ML
, WSH_LOOKUPS WLK
, WSH_LOOKUPS WLK1
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MP.PLAN_ID
AND MED.PLAN_ID = MT.PLAN_ID
AND MED.TRIP_ID1 = MT.TRIP_ID
AND MED.PLAN_ID = MTS1.PLAN_ID
AND MED.STOP_ID1 = MTS1.STOP_ID
AND MED.PLAN_ID = MTS2.PLAN_ID
AND MED.STOP_ID2 = MTS2.STOP_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MTS1.STOP_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MTS2.STOP_LOCATION_ID = WL2.WSH_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'