MATERIALIZED VIEW: APPS.HRI_MDP_BEN_ENRLACTN_PGM_MV

Source

(
SELECT /* 12.0: bug#4526784 */  SUM(SUM(sspnd_ind)) OVER
           (PARTITION BY asnd_lf_evt_dt, actn_typ_cd, pgm_id
		        ORDER BY asnd_lf_evt_dt, actn_typ_cd, pgm_id, effective_start_date ) sspnd_count,
       SUM(SUM(actn_item_ind)) OVER
           (PARTITION BY asnd_lf_evt_dt, actn_typ_cd, pgm_id
		        ORDER BY asnd_lf_evt_dt, actn_typ_cd, pgm_id, effective_start_date ) actn_item_ind,
	   asnd_lf_evt_dt,
	   actn_typ_cd,
	   pgm_id,
	   effective_start_date,
	   NVL(LEAD(effective_start_date - 1) OVER
       (PARTITION BY asnd_lf_evt_dt, actn_typ_cd, pgm_id
            ORDER BY asnd_lf_evt_dt, actn_typ_cd, pgm_id, effective_start_date),TO_DATE('31-12-4712','DD-MM-YYYY')) effective_end_date
FROM (
    SELECT sspnd_ind - NVL(LAG(sspnd_ind) OVER
            (PARTITION BY asnd_lf_evt_dt, person_id, pgm_id, actn_typ_cd
                 ORDER BY asnd_lf_evt_dt, person_id, pgm_id, actn_typ_cd, effective_start_date),0) sspnd_ind,
           actn_item_ind - NVL(LAG(actn_item_ind) OVER
            (PARTITION BY asnd_lf_evt_dt, person_id, pgm_id, actn_typ_cd
                 ORDER BY asnd_lf_evt_dt, person_id, pgm_id, actn_typ_cd, effective_start_date),0) actn_item_ind,
           asnd_lf_evt_dt,
           actn_typ_cd,
           person_id,
           pgm_id,
           effective_start_date
    FROM (
        SELECT (CASE WHEN CEIL(AVG(sspnd_ind)) > 0
                     THEN 1
                     ELSE 0 END) sspnd_ind,
               (CASE WHEN CEIL(AVG(actn_item_ind)) > 0
                     THEN 1
                     ELSE 0 END) actn_item_ind,
               asnd_lf_evt_dt,
               actn_typ_cd,
               person_id,
               pgm_id,
               effective_start_date
        FROM (
            SELECT SUM(pgm_pea.sspnd_ind) OVER
                    (PARTITION BY pgm_pea.asnd_lf_evt_dt,pgm_pea.person_id, pgm_pea.pgm_id, pgm_pea.actn_typ_cd
                         ORDER BY pgm_pea.asnd_lf_evt_dt,pgm_pea.person_id, pgm_pea.pgm_id, pgm_pea.actn_typ_cd, pgm_pea.effective_start_date) sspnd_ind,
                   SUM(pgm_pea.actn_item_ind) OVER
                    (PARTITION BY pgm_pea.asnd_lf_evt_dt,pgm_pea.person_id, pgm_pea.pgm_id, pgm_pea.actn_typ_cd
                         ORDER BY pgm_pea.asnd_lf_evt_dt,pgm_pea.person_id, pgm_pea.pgm_id, pgm_pea.actn_typ_cd, pgm_pea.effective_start_date) actn_item_ind,
                   pgm_pea.asnd_lf_evt_dt,
                   pgm_pea.actn_typ_cd,
                   pgm_pea.pgm_id,
                   pgm_pea.effective_start_date,
                   pgm_pea.effective_end_date,
                   pgm_pea.person_id
            FROM (
                SELECT copd.pgm_id,
                       pea.compobj_sk_pk,
                       pea.asnd_lf_evt_dt,
                       pea.actn_typ_cd,
                       pea.effective_start_date,
                       pea.effective_end_date,
                       pea.sspnd_ind,
                       pea.actn_item_ind,
                       pea.interim_ind,
                       pea.person_id
                FROM (
                    SELECT peac.compobj_sk_pk,
                           peac.asnd_lf_evt_dt,
                           peac.actn_typ_cd,
                           effective_start_date,
                           effective_end_date,
                           sspnd_ind,
                           actn_item_ind,
                           interim_ind,
                           person_id
                      FROM hri_mb_ben_enrlactn_ct peac
                     UNION
                    SELECT peac.compobj_sk_pk,
                           peac.asnd_lf_evt_dt,
                           peac.actn_typ_cd,
                           effective_end_date+1 effective_start_date,
                           TO_DATE('31-12-4712','DD-MM-YYYY') effective_end_date,
                           DECODE(sspnd_ind,1,-1,0),
                           -1,
                           DECODE(interim_ind,1,-1,0),
                           person_id
                      FROM hri_mb_ben_enrlactn_ct peac
                     WHERE effective_end_date <> TO_DATE('31-12-4712','DD-MM-YYYY')
                       ) pea,
                hri_cs_compobj_ct copd
                where copd.compobj_sk_pk = pea.compobj_sk_pk
            ) pgm_pea
         )
        GROUP BY asnd_lf_evt_dt, person_id, pgm_id, actn_typ_cd, effective_start_date
    )
)
GROUP BY asnd_lf_evt_dt, pgm_id, actn_typ_cd, effective_start_date
)