MATERIALIZED VIEW: APPS.ISC_FS_012_MV

Source

select /* 12.0: bug#4526784 */
  f.time_id
, f.period_type_id
, decode( den.record_type, 'GROUP', den.parent_prg_id, f.parent_district_id ) parent_district_id
, decode( den.record_type, 'GROUP', den.record_type, f.record_type ) record_type
, decode( den.record_type, 'GROUP', den.prg_id, f.district_id ) district_id
, decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(f.district_id) || '.' || f.parent_district_id ) district_id_c
, sum(f.ftf_count)     ftf_count
, sum(f.non_ftf_count) non_ftf_count
/* the following columns prefixed with mv_ exist solely to enable
   MV to be fast refreshable */
, grouping_id( f.time_id
             , f.period_type_id
             , decode( den.record_type, 'GROUP', den.parent_prg_id, f.parent_district_id )
             , decode( den.record_type, 'GROUP', den.record_type, f.record_type )
             , decode( den.record_type, 'GROUP', den.prg_id, f.district_id )
             , decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(f.district_id) || '.' || f.parent_district_id )
             )                   mv_grp_id
, count(*)                       mv_count
, count(f.ftf_count)             mv_ftf_count
, count(f.non_ftf_count)         mv_non_ftf_count
/* end of fast refreshable needed columns */
from
  isc_fs_011_mv f
, isc_fs_002_mv den
where
    f.grp_id = 7
and f.parent_district_id = den.rg_id
group by
  f.time_id
, f.period_type_id
, decode( den.record_type, 'GROUP', den.parent_prg_id, f.parent_district_id )
, decode( den.record_type, 'GROUP', den.record_type, f.record_type )
, decode( den.record_type, 'GROUP', den.prg_id, f.district_id )
, decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(f.district_id) || '.' || f.parent_district_id )