MATERIALIZED VIEW: APPS.HRI_MDP_BEN_ELCTN_RPTG_MV

Source

(
SELECT /* 12.0: bug#4526784 */  pel_rpg.asnd_lf_evt_dt,
       pel_rpg.rptgtyp_id,
       SUM(pel_rpg.elig_ind) elig_count,
       SUM(pel_rpg.enrt_ind) enrt_count,
       SUM(pel_rpg.elig_ind) - (SUM(pel_rpg.enrt_ind) - SUM(pel_rpg.dflt_ind)) not_enrt_count,
       SUM(pel_rpg.dflt_ind) dflt_count
  FROM
   (SELECT CEIL(AVG(elig_ind)) elig_ind,
           CEIL(AVG(enrt_ind)) enrt_ind,
           CEIL(AVG(not_enrt_ind)) not_enrt_ind,
           CEIL(AVG(dflt_ind)) dflt_ind,
           pelc.asnd_lf_evt_dt,
           pelc.person_id,
           rpgd.rptgtyp_id
      FROM hri_mb_ben_elctn_evnt_ct pelc,
           hri_cs_co_rpgh_pirg_ct rpgd
     WHERE rpgd.pgm_id = pelc.pgm_id
     GROUP BY  pelc.asnd_lf_evt_dt, pelc.person_id, rpgd.rptgtyp_id) pel_rpg
GROUP BY asnd_lf_evt_dt, rptgtyp_id
)