VIEW: APPS.MST_INSUFF_STOP_TIME_LOAD_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , mt.trip_id , mts.stop_id , flp.facility_id , med.exception_detail_id , mt.mode_of_transport , mt.trip_number , 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 , substr(mst_wb_util.get_local_chardtzone(mts.stop_location_id, mts.planned_arrival_date),1,40) , mts.planned_arrival_date , substr(mst_wb_util.get_local_chardtzone(mts.stop_location_id, mts.planned_departure_date),1,40) , mts.planned_departure_date , 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(nvl(med.number1,0)),1,20) , substr(mst_wb_util.get_hr_min(nvl(med.number1,0) - ((mts.planned_departure_date - mts.planned_arrival_date) * 24)),1,20) , nvl(med.number2,0) , mp.weight_uom , nvl(med.number3,0) , mp.volume_uom , nvl(med.number4,0) , nvl(med.number5,0) , 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_exception_details med , mst_trips mt , mst_trip_stops mts , mst_plans mp , mfg_lookups ml , wsh_locations wl , fte_location_parameters flp 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 = mts.plan_id and med.stop_id1 = mts.stop_id and mts.stop_location_id = wl.wsh_location_id and mts.stop_location_id = flp.location_id

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MT.TRIP_ID
, MTS.STOP_ID
, FLP.FACILITY_ID
, MED.EXCEPTION_DETAIL_ID
, MT.MODE_OF_TRANSPORT
, MT.TRIP_NUMBER
, 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
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_ARRIVAL_DATE)
, 1
, 40)
, MTS.PLANNED_ARRIVAL_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MTS.STOP_LOCATION_ID
, MTS.PLANNED_DEPARTURE_DATE)
, 1
, 40)
, MTS.PLANNED_DEPARTURE_DATE
, 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(NVL(MED.NUMBER1
, 0))
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER1
, 0) - ((MTS.PLANNED_DEPARTURE_DATE - MTS.PLANNED_ARRIVAL_DATE) * 24))
, 1
, 20)
, NVL(MED.NUMBER2
, 0)
, MP.WEIGHT_UOM
, NVL(MED.NUMBER3
, 0)
, MP.VOLUME_UOM
, NVL(MED.NUMBER4
, 0)
, NVL(MED.NUMBER5
, 0)
, 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_EXCEPTION_DETAILS MED
, MST_TRIPS MT
, MST_TRIP_STOPS MTS
, MST_PLANS MP
, MFG_LOOKUPS ML
, WSH_LOCATIONS WL
, FTE_LOCATION_PARAMETERS FLP
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 = MTS.PLAN_ID
AND MED.STOP_ID1 = MTS.STOP_ID
AND MTS.STOP_LOCATION_ID = WL.WSH_LOCATION_ID
AND MTS.STOP_LOCATION_ID = FLP.LOCATION_ID