VIEW: APPS.MST_VIO_LAYOVER_REGULATION_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , mt.trip_id , mt.mode_of_transport , med.exception_detail_id , mt.carrier_id , mt.trip_number , wc.freight_code , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , med.number4 , decode(med.number4,null,null,mparam.max_driving_distance) , mp.distance_uom , decode(med.number2,null,null,substr(mst_wb_util.get_hr_min(med.number2),1,20)) , decode(med.number2,null,null,decode(mparam.max_drive_time,null,null,substr(mst_wb_util.get_hr_min(mparam.max_drive_time),1,20))) , decode(med.number1,null,null,substr(mst_wb_util.get_hr_min(med.number1),1,20)) , decode(med.number1,null,null,decode(mparam.minimum_lay_time,null,null,substr(mst_wb_util.get_hr_min(mparam.minimum_lay_time),1,20))) , decode(med.number3,null,null,substr(mst_wb_util.get_hr_min(med.number3),1,20)) , decode(med.number3,null,null,decode(mparam.max_duty_time,null,null,substr(mst_wb_util.get_hr_min(mparam.max_duty_time),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 , wsh_carriers wc , mfg_lookups ml , wsh_locations wl1 , wsh_locations wl2 , mst_parameters mparam 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 mt.carrier_id = wc.carrier_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and mparam.user_id = -9999

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MT.TRIP_ID
, MT.MODE_OF_TRANSPORT
, MED.EXCEPTION_DETAIL_ID
, MT.CARRIER_ID
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, MED.NUMBER4
, DECODE(MED.NUMBER4
, NULL
, NULL
, MPARAM.MAX_DRIVING_DISTANCE)
, MP.DISTANCE_UOM
, DECODE(MED.NUMBER2
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER2)
, 1
, 20))
, DECODE(MED.NUMBER2
, NULL
, NULL
, DECODE(MPARAM.MAX_DRIVE_TIME
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MPARAM.MAX_DRIVE_TIME)
, 1
, 20)))
, DECODE(MED.NUMBER1
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER1)
, 1
, 20))
, DECODE(MED.NUMBER1
, NULL
, NULL
, DECODE(MPARAM.MINIMUM_LAY_TIME
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MPARAM.MINIMUM_LAY_TIME)
, 1
, 20)))
, DECODE(MED.NUMBER3
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER3)
, 1
, 20))
, DECODE(MED.NUMBER3
, NULL
, NULL
, DECODE(MPARAM.MAX_DUTY_TIME
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MPARAM.MAX_DUTY_TIME)
, 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
, WSH_CARRIERS WC
, MFG_LOOKUPS ML
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, MST_PARAMETERS MPARAM
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 MT.CARRIER_ID = WC.CARRIER_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MPARAM.USER_ID = -9999