MATERIALIZED VIEW: APPS.ISC_FS_013_MV

Source

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
, grouping_id( t.task_type_id ) grp_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
, t.task_type_id
, sum(ta.actual_effort_hrs) actual_effort_hrs
, sum((ta.actual_travel_duration_min / 60)) actual_travel_duration_hrs
/* the following columns prefixed with mv_ exist solely to enable MV to be fast refreshable */
, 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), t.task_type_id
             ) mv_grp_id
, c.report_date_julian mv_day_id
, c.week_id mv_week_id
, c.ent_period_id mv_period_id
, c.ent_qtr_id mv_ent_qtr_id
, c.ent_year_id mv_ent_year_id
, count(*) mv_count
, count(ta.actual_effort_hrs) mv_actual_effort_hrs
, count((ta.actual_travel_duration_min / 60)) mv_actual_travel_duration_hrs
/* 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
, 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.report_date = c.report_date
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)
-- 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 )
, rollup( t.task_type_id )