VIEW: APPS.MST_VIO_CAR_RULE_TIME_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , mt.trip_id , mt.mode_of_transport , mt.carrier_id , med.exception_detail_id , mt.trip_number , wc.freight_code , mt.service_level , wlk.meaning , decode(med.number4, null, null,substr(mst_wb_util.get_hr_min(med.number4),1,20)) , decode(med.number4, null, null,decode(wc.max_total_time, null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.max_total_time,wc.time_uom,fnd_profile.value('MST_HOUR_UOM'))),1,20))) , decode(med.number1, null, null, substr(mst_wb_util.get_hr_min(med.number1),1,20)) , decode(med.number1, null, null,decode(wc.max_driving_time_in_24hr , null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.max_driving_time_in_24hr, wc.time_uom,fnd_profile.value('MST_HOUR_UOM'))),1,20))) , decode(med.number2, null, null, substr(mst_wb_util.get_hr_min(med.number2),1,20)) , decode(med.number2, null, null,decode(wc.max_duty_time_in_24hr, null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.max_duty_time_in_24hr, wc.time_uom,fnd_profile.value('MST_HOUR_UOM'))),1,20))) , decode(med.number3, null, null, substr(mst_wb_util.get_hr_min(med.number3),1,20)) , decode(med.number3, null, null,decode(wc.min_layover_time, null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.min_layover_time,wc.time_uom,fnd_profile.value('MST_HOUR_UOM'))),1,20))) , ml.meaning , substr(mst_wb_util.get_threshold_value(med.exception_type),1,20) , 'MST_TRIPS' , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mst_trips mt , wsh_carriers wc , mfg_lookups ml , wsh_lookups wlk WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mt.plan_id and med.trip_id1 = mt.trip_id and mt.carrier_id = wc.carrier_id and mt.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS' UNION ALL select med.plan_id , med.exception_type , mct.continuous_move_id , 'TRUCK' , mct.carrier_id , med.exception_detail_id , mct.cm_trip_number , wc.freight_code , mct.service_level , wlk.meaning , decode(med.number4, null, null, substr(mst_wb_util.get_hr_min(med.number4),1,20)) , decode(med.number4, null, null, decode(wc.max_total_time, null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.max_total_time,wc.time_uom,fnd_profile.value('MST_HOUR_UOM'))),1,20))) , decode(med.number1, null, null, substr(mst_wb_util.get_hr_min(med.number1),1,20)) , decode(med.number1, null, null, decode(wc.max_driving_time_in_24hr, null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.max_driving_time_in_24hr, wc.time_uom, fnd_profile.value('MST_HOUR_UOM'))),1,20))) , decode(med.number2, null, null, substr(mst_wb_util.get_hr_min(med.number2),1,20)) , decode(med.number2, null, null, decode(wc.max_duty_time_in_24hr, null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.max_duty_time_in_24hr, wc.time_uom, fnd_profile.value('MST_HOUR_UOM'))),1,20))) , decode(med.number3, null, null, substr(mst_wb_util.get_hr_min(med.number3),1,20)) , decode(med.number3, null, null, decode(wc.min_layover_time, null, null, substr(mst_wb_util.get_hr_min(mst_wb_util.convert_time(wc.min_layover_time,wc.time_uom,fnd_profile.value('MST_HOUR_UOM'))),1,20))) , ml.meaning , substr(mst_wb_util.get_threshold_value(med.exception_type),1,20) , 'MST_CM_TRIPS' , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mst_cm_trips mct , wsh_carriers wc , mfg_lookups ml , wsh_lookups wlk where med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mct.plan_id and med.continuous_move_id = mct.continuous_move_id and mct.carrier_id = wc.carrier_id and mct.service_level = wlk.lookup_code and wlk.lookup_type = 'WSH_SERVICE_LEVELS'

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MT.TRIP_ID
, MT.MODE_OF_TRANSPORT
, MT.CARRIER_ID
, MED.EXCEPTION_DETAIL_ID
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK.MEANING
, DECODE(MED.NUMBER4
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER4)
, 1
, 20))
, DECODE(MED.NUMBER4
, NULL
, NULL
, DECODE(WC.MAX_TOTAL_TIME
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MAX_TOTAL_TIME
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, DECODE(MED.NUMBER1
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER1)
, 1
, 20))
, DECODE(MED.NUMBER1
, NULL
, NULL
, DECODE(WC.MAX_DRIVING_TIME_IN_24HR
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MAX_DRIVING_TIME_IN_24HR
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, DECODE(MED.NUMBER2
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER2)
, 1
, 20))
, DECODE(MED.NUMBER2
, NULL
, NULL
, DECODE(WC.MAX_DUTY_TIME_IN_24HR
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MAX_DUTY_TIME_IN_24HR
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, DECODE(MED.NUMBER3
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER3)
, 1
, 20))
, DECODE(MED.NUMBER3
, NULL
, NULL
, DECODE(WC.MIN_LAYOVER_TIME
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MIN_LAYOVER_TIME
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_THRESHOLD_VALUE(MED.EXCEPTION_TYPE)
, 1
, 20)
, 'MST_TRIPS'
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MST_TRIPS MT
, WSH_CARRIERS WC
, MFG_LOOKUPS ML
, WSH_LOOKUPS WLK
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MT.PLAN_ID
AND MED.TRIP_ID1 = MT.TRIP_ID
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS' UNION ALL SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MCT.CONTINUOUS_MOVE_ID
, 'TRUCK'
, MCT.CARRIER_ID
, MED.EXCEPTION_DETAIL_ID
, MCT.CM_TRIP_NUMBER
, WC.FREIGHT_CODE
, MCT.SERVICE_LEVEL
, WLK.MEANING
, DECODE(MED.NUMBER4
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER4)
, 1
, 20))
, DECODE(MED.NUMBER4
, NULL
, NULL
, DECODE(WC.MAX_TOTAL_TIME
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MAX_TOTAL_TIME
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, DECODE(MED.NUMBER1
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER1)
, 1
, 20))
, DECODE(MED.NUMBER1
, NULL
, NULL
, DECODE(WC.MAX_DRIVING_TIME_IN_24HR
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MAX_DRIVING_TIME_IN_24HR
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, DECODE(MED.NUMBER2
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER2)
, 1
, 20))
, DECODE(MED.NUMBER2
, NULL
, NULL
, DECODE(WC.MAX_DUTY_TIME_IN_24HR
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MAX_DUTY_TIME_IN_24HR
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, DECODE(MED.NUMBER3
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MED.NUMBER3)
, 1
, 20))
, DECODE(MED.NUMBER3
, NULL
, NULL
, DECODE(WC.MIN_LAYOVER_TIME
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(MST_WB_UTIL.CONVERT_TIME(WC.MIN_LAYOVER_TIME
, WC.TIME_UOM
, FND_PROFILE.VALUE('MST_HOUR_UOM')))
, 1
, 20)))
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_THRESHOLD_VALUE(MED.EXCEPTION_TYPE)
, 1
, 20)
, 'MST_CM_TRIPS'
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MST_CM_TRIPS MCT
, WSH_CARRIERS WC
, MFG_LOOKUPS ML
, WSH_LOOKUPS WLK
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MCT.PLAN_ID
AND MED.CONTINUOUS_MOVE_ID = MCT.CONTINUOUS_MOVE_ID
AND MCT.CARRIER_ID = WC.CARRIER_ID
AND MCT.SERVICE_LEVEL = WLK.LOOKUP_CODE
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'