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
, decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(ta.resource_id) || '.' || ta.district_id ) district_id_c
, count( ta.actual_travel_distance_km ) task_dist_count
, sum( ta.sched_travel_distance_km ) sched_travel_distance_km
, sum( ta.actual_travel_distance_km ) actual_travel_distance_km
, sum( case
when (b.range1_low is null or ta.actual_travel_distance_km >= b.range1_low) and
(b.range1_high is null or ta.actual_travel_distance_km < b.range1_high) then 1
else 0
end ) actual_travel_dist_km_b1
, sum( case
when (ta.actual_travel_distance_km >= b.range2_low) and
(b.range2_high is null or ta.actual_travel_distance_km < b.range2_high) then 1
else 0
end ) actual_travel_dist_km_b2
, sum( case
when (ta.actual_travel_distance_km >= b.range3_low) and
(b.range3_high is null or ta.actual_travel_distance_km < b.range3_high) then 1
else 0
end ) actual_travel_dist_km_b3
, sum( case
when (ta.actual_travel_distance_km >= b.range4_low) and
(b.range4_high is null or ta.actual_travel_distance_km < b.range4_high) then 1
else 0
end ) actual_travel_dist_km_b4
, sum( case
when (ta.actual_travel_distance_km >= b.range5_low) and
(b.range5_high is null or ta.actual_travel_distance_km < b.range5_high) then 1
else 0
end ) actual_travel_dist_km_b5
, sum( case
when (ta.actual_travel_distance_km >= b.range6_low) and
(b.range6_high is null or ta.actual_travel_distance_km < b.range6_high) then 1
else 0
end ) actual_travel_dist_km_b6
, sum( case
when (ta.actual_travel_distance_km >= b.range7_low) and
(b.range7_high is null or ta.actual_travel_distance_km < b.range7_high) then 1
else 0
end ) actual_travel_dist_km_b7
, sum( case
when (ta.actual_travel_distance_km >= b.range8_low) and
(b.range8_high is null or ta.actual_travel_distance_km < b.range8_high) then 1
else 0
end ) actual_travel_dist_km_b8
, sum( case
when (ta.actual_travel_distance_km >= b.range9_low) and
(b.range9_high is null or ta.actual_travel_distance_km < b.range9_high) then 1
else 0
end ) actual_travel_dist_km_b9
, sum( case
when (ta.actual_travel_distance_km >= b.range10_low) and
(b.range10_high is null or ta.actual_travel_distance_km < b.range10_high) then 1
else 0
end ) actual_travel_dist_km_b10
---- these are the dist km related buckets ---- end here ----------------
, sum( case
when (b.range1_low is null or ta.actual_travel_distance_km*0.62137 >= b.range1_low) and
(b.range1_high is null or ta.actual_travel_distance_km*0.62137 < b.range1_high) then 1
else 0
end ) actual_travel_dist_mi_b1
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range2_low) and
(b.range2_high is null or ta.actual_travel_distance_km*0.62137 < b.range2_high) then 1
else 0
end ) actual_travel_dist_mi_b2
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range3_low) and
(b.range3_high is null or ta.actual_travel_distance_km*0.62137 < b.range3_high) then 1
else 0
end ) actual_travel_dist_mi_b3
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range4_low) and
(b.range4_high is null or ta.actual_travel_distance_km*0.62137 < b.range4_high) then 1
else 0
end ) actual_travel_dist_mi_b4
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range5_low) and
(b.range5_high is null or ta.actual_travel_distance_km*0.62137 < b.range5_high) then 1
else 0
end ) actual_travel_dist_mi_b5
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range6_low) and
(b.range6_high is null or ta.actual_travel_distance_km*0.62137< b.range6_high) then 1
else 0
end ) actual_travel_dist_mi_b6
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range7_low) and
(b.range7_high is null or ta.actual_travel_distance_km*0.62137 < b.range7_high) then 1
else 0
end ) actual_travel_dist_mi_b7
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range8_low) and
(b.range8_high is null or ta.actual_travel_distance_km*0.62137 < b.range8_high) then 1
else 0
end ) actual_travel_dist_mi_b8
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range9_low) and
(b.range9_high is null or ta.actual_travel_distance_km*0.62137 < b.range9_high) then 1
else 0
end ) actual_travel_dist_mi_b9
, sum( case
when (ta.actual_travel_distance_km*0.62137 >= b.range10_low) and
(b.range10_high is null or ta.actual_travel_distance_km*0.62137 < b.range10_high) then 1
else 0
end ) actual_travel_dist_mi_b10
---- these are the dist mile related buckets ---- end here ----------------
---- the columns with mv_ prefixed exist to enable the fast refresh of the mv---
, 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_distance_km ) mv_count_sched_travel_dist_km
, count( ta.actual_travel_distance_km ) mv_count_actual_travel_dist_km
, count( case
when (b.range1_low is null or ta.actual_travel_distance_km >= b.range1_low) and
(b.range1_high is null or ta.actual_travel_distance_km < b.range1_high) then 1
else 0
end ) mv_count_dist_km_b1
, count( case
when (ta.actual_travel_distance_km >= b.range2_low) and
(b.range2_high is null or ta.actual_travel_distance_km < b.range2_high) then 1
else 0
end ) mv_count_dist_km_b2
, count( case
when (ta.actual_travel_distance_km >= b.range3_low) and
(b.range3_high is null or ta.actual_travel_distance_km < b.range3_high) then 1
else 0
end ) mv_count_dist_km_b3
, count( case
when (ta.actual_travel_distance_km >= b.range4_low) and
(b.range4_high is null or ta.actual_travel_distance_km < b.range4_high) then 1
else 0
end ) mv_count_dist_km_b4
, count( case
when (ta.actual_travel_distance_km >= b.range5_low) and
(b.range5_high is null or ta.actual_travel_distance_km < b.range5_high) then 1
else 0
end ) mv_count_dist_km_b5
, count( case
when (ta.actual_travel_distance_km >= b.range6_low) and
(b.range6_high is null or ta.actual_travel_distance_km < b.range6_high) then 1
else 0
end ) mv_count_dist_km_b6
, count( case
when (ta.actual_travel_distance_km >= b.range7_low) and
(b.range7_high is null or ta.actual_travel_distance_km < b.range7_high) then 1
else 0
end ) mv_count_dist_km_b7
, count( case
when (ta.actual_travel_distance_km >= b.range8_low) and
(b.range8_high is null or ta.actual_travel_distance_km < b.range8_high) then 1
else 0
end ) mv_count_dist_km_b8
, count( case
when (ta.actual_travel_distance_km >= b.range9_low) and
(b.range9_high is null or ta.actual_travel_distance_km < b.range9_high) then 1
else 0
end ) mv_count_dist_km_b9
, count( case
when (ta.actual_travel_distance_km >= b.range10_low) and
(b.range10_high is null or ta.actual_travel_distance_km < b.range10_high) then 1
else 0
end ) mv_count_dist_km_b10
, count( case
when (b.range1_low is null or ta.actual_travel_distance_km*0.62137 >= b.range1_low) and
(b.range1_high is null or ta.actual_travel_distance_km*0.62137 < b.range1_high) then 1
else 0
end ) mv_count_dist_mi_b1
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range2_low) and
(b.range2_high is null or ta.actual_travel_distance_km*0.62137 < b.range2_high) then 1
else 0
end ) mv_count_dist_mi_b2
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range3_low) and
(b.range3_high is null or ta.actual_travel_distance_km*0.62137 < b.range3_high) then 1
else 0
end ) mv_count_dist_mi_b3
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range4_low) and
(b.range4_high is null or ta.actual_travel_distance_km*0.62137 < b.range4_high) then 1
else 0
end ) mv_count_dist_mi_b4
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range5_low) and
(b.range5_high is null or ta.actual_travel_distance_km*0.62137 < b.range5_high) then 1
else 0
end ) mv_count_dist_mi_b5
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range6_low) and
(b.range6_high is null or ta.actual_travel_distance_km*0.62137 < b.range6_high) then 1
else 0
end ) mv_count_dist_mi_b6
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range7_low) and
(b.range7_high is null or ta.actual_travel_distance_km*0.62137 < b.range7_high) then 1
else 0
end ) mv_count_dist_mi_b7
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range8_low) and
(b.range8_high is null or ta.actual_travel_distance_km*0.62137 < b.range8_high) then 1
else 0
end ) mv_count_dist_mi_b8
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range9_low) and
(b.range9_high is null or ta.actual_travel_distance_km*0.62137 < b.range9_high) then 1
else 0
end ) mv_count_dist_mi_b9
, count( case
when (ta.actual_travel_distance_km*0.62137 >= b.range10_low) and
(b.range10_high is null or ta.actual_travel_distance_km*0.62137 < b.range10_high) then 1
else 0
end ) mv_count_dist_mi_b10
, 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 )
, decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(ta.resource_id) || '.' || ta.district_id )
) mv_grp_id
, count(*) mv_count
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.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_DIST'
and bb.bucket_id = b.bucket_id
and ta.district_id = den.rg_id
and t.source_object_type_code = 'SR'
group by
c.ent_year_id
, rollup( 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 )
, decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(ta.resource_id) || '.' || ta.district_id )
[Home] [Help]