MATERIALIZED VIEW: APPS.ISC_FS_004_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.first_opened) first_opened
, sum(f.reopened) reopened
, sum(f.closed) closed
/* 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.first_opened)        mv_first_opened
, count(f.reopened)            mv_reopened
, count(f.closed)              mv_closed
/* end of fast refreshable needed columns */
from
  isc_fs_003_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 )