select /* 12.0: bug#4526784 */
decode(grouping_id( c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, c.report_date_julian
, 1, c.week_id
, 3, c.ent_period_id
, 7, c.ent_qtr_id
, 15, c.ent_year_id) time_id
, decode(grouping_id( c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, 1
, 1, 16
, 3, 32
, 7, 64
, 15, 128) period_type_id
, decode( den.record_type, 'GROUP', den.parent_prg_id, ta.district_id ) parent_district_id
-- R12 resource type impact
, decode( den.record_type, 'GROUP', den.record_type, ta.resource_type ) record_type
, decode( den.record_type, 'GROUP', den.prg_id, ta.resource_id ) district_id
, den.record_type den_record_type
, count(ta.actual_travel_duration_min) task_duration_count
, sum(ta.sched_travel_duration_min) sched_travel_duration_min
, sum(ta.actual_travel_duration_min) actual_travel_duration_min
, case
when (b.range1_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range1_high)
then 1
when (b.range2_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range2_high)
then 2
when (b.range3_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range3_high)
then 3
when (b.range4_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range4_high)
then 4
when (b.range5_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range5_high)
then 5
when (b.range6_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range6_high)
then 6
when (b.range7_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range7_high)
then 7
when (b.range8_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range8_high)
then 8
when (b.range9_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range9_high)
then 9
when (b.range10_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range10_high)
then 10
end bucket_num
/* the following columns prefixed with mv_ exist solely to enable
MV to be fast refreshable */
, c.ent_year_id mv_ent_year_id
, c.ent_qtr_id mv_ent_qtr_id
, c.ent_period_id mv_period_id
, c.week_id mv_week_id
, c.report_date_julian mv_day_id
, count(ta.sched_travel_duration_min) mv_count_sched_duration_min
, count(ta.actual_travel_duration_min) mv_count_actual_duration_min
, grouping_id( c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian
-- R12 resource type impact
, decode( den.record_type, 'GROUP', den.record_type, ta.resource_type )
, decode( den.record_type, 'GROUP', den.parent_prg_id, ta.district_id )
, decode( den.record_type, 'GROUP', den.prg_id, ta.resource_id )
, den.record_type
, case
when (b.range1_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range1_high)
then 1
when (b.range2_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range2_high)
then 2
when (b.range3_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range3_high)
then 3
when (b.range4_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range4_high)
then 4
when (b.range5_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range5_high)
then 5
when (b.range6_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range6_high)
then 6
when (b.range7_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range7_high)
then 7
when (b.range8_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range8_high)
then 8
when (b.range9_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range9_high)
then 9
when (b.range10_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range10_high)
then 10
end
) mv_grp_id
, count(*) mv_count
/* end of fast refreshable needed columns */
from
ISC.ISC_FS_TASK_ASSIGNMNTS_F ta
, ISC.ISC_FS_TASKS_F t
, FII.FII_TIME_DAY c
, BIS.BIS_BUCKET bb
, BIS.BIS_BUCKET_CUSTOMIZATIONS b
, isc_fs_002_mv den
where
t.task_type_rule = 'DISPATCH'
and t.source_object_type_code = 'SR'
and t.task_id = ta.task_id
and ta.deleted_flag <> 'Y' -- check for the deletion
and ta.actual_travel_duration_min is not null
and nvl(ta.sched_travel_duration_min,0) > 0
and ta.actual_travel_distance_km is not null
and nvl(ta.sched_travel_distance_km,0) > 0
and ta.report_date = c.report_date
and bb.short_name = 'BIV_FS_TRVL_TIME_VAR'
and bb.bucket_id = b.bucket_id
and ta.district_id = den.rg_id
group by
c.ent_year_id
, rollup ( c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian )
, case
when (b.range1_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range1_high)
then 1
when (b.range2_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range2_high)
then 2
when (b.range3_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range3_high)
then 3
when (b.range4_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range4_high)
then 4
when (b.range5_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range5_high)
then 5
when (b.range6_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range6_high)
then 6
when (b.range7_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range7_high)
then 7
when (b.range8_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range8_high)
then 8
when (b.range9_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range9_high)
then 9
when (b.range10_high is null or 100*(ta.actual_travel_duration_min - ta.sched_travel_duration_min )/ta.sched_travel_duration_min < b.range10_high)
then 10
end
-- R12 resource type impact
, decode( den.record_type, 'GROUP', den.record_type, ta.resource_type )
, decode( den.record_type, 'GROUP', den.parent_prg_id, ta.district_id )
, decode( den.record_type, 'GROUP', den.prg_id, ta.resource_id )
, den.record_type
[Home] [Help]