(
SELECT /* 12.0: bug#4526784 */ ee_opt.asnd_lf_evt_dt,
ee_opt.effective_start_date,
ee_opt.effective_end_date,
ee_opt.oipl_id,
ee_opt.rptgtyp_id,
ee_opt.elig_count,
ee_opt.enrt_count,
ROUND((CASE WHEN ee_opt.elig_count <= 0
THEN 0
ELSE ee_opt.enrt_count/ee_opt.elig_count END )*100 , 1) enrt_per,
ee_opt.waive_expl_count,
ee_opt.waive_dflt_count,
ee_opt.waive_dflt_count + ee_opt.waive_expl_count waive_total_count,
ROUND((CASE WHEN ee_opt.elig_count <= 0
THEN 0
ELSE ee_opt.waive_expl_count/ee_opt.elig_count END )*100 , 1) waive_expl_per,
ROUND((CASE WHEN ee_opt.elig_count <= 0
THEN 0
ELSE ee_opt.waive_dflt_count/ee_opt.elig_count END )*100 , 1) waive_dflt_per,
ROUND((CASE WHEN ee_opt.elig_count <= 0
THEN 0
ELSE (ee_opt.waive_expl_count + ee_opt.waive_dflt_count)/ee_opt.elig_count END )*100 , 1) waive_total_per
FROM (
SELECT SUM(SUM(prgr.elig_ind)) OVER
(PARTITION BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id
ORDER BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id, prgr.change_date) elig_count,
SUM(SUM(prgr.enrt_ind)) OVER
(PARTITION BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id
ORDER BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id, prgr.change_date) enrt_count,
SUM(SUM(prgr.dflt_ind)) OVER
(PARTITION BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id
ORDER BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id, prgr.change_date) dflt_count,
SUM(SUM(prgr.waive_expl_ind)) OVER
(PARTITION BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id
ORDER BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id, prgr.change_date) waive_expl_count,
SUM(SUM(prgr.waive_dflt_ind)) OVER
(PARTITION BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id
ORDER BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id, prgr.change_date) waive_dflt_count,
prgr.asnd_lf_evt_dt,
prgr.rptgtyp_id,
prgr.oipl_id,
prgr.change_date,
prgr.change_date effective_start_date,
NVL(LEAD(prgr.change_date-1) OVER
(PARTITION BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id
ORDER BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id, prgr.change_date), TO_DATE('31-12-4712','DD-MM-YYYY')) effective_end_date
FROM (
SELECT pen_rgr.elig_ind -
NVL(LAG(pen_rgr.elig_ind) OVER
(PARTITION BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id
ORDER BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id, pen_rgr.change_date),0) elig_ind,
pen_rgr.enrt_ind -
NVL(LAG(pen_rgr.enrt_ind) OVER
(PARTITION BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id
ORDER BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id, pen_rgr.change_date),0) enrt_ind,
pen_rgr.dflt_ind -
NVL(LAG(pen_rgr.dflt_ind) OVER
(PARTITION BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id
ORDER BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id, pen_rgr.change_date),0) dflt_ind,
pen_rgr.waive_expl_ind -
NVL(LAG(pen_rgr.waive_expl_ind) OVER
(PARTITION BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id
ORDER BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id, pen_rgr.change_date),0) waive_expl_ind,
pen_rgr.waive_dflt_ind -
NVL(LAG(pen_rgr.waive_dflt_ind) OVER
(PARTITION BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id
ORDER BY pen_rgr.asnd_lf_evt_dt, pen_rgr.person_id, pen_rgr.rptgtyp_id, pen_rgr.oipl_id, pen_rgr.change_date),0) waive_dflt_ind,
pen_rgr.asnd_lf_evt_dt,
pen_rgr.rptgtyp_id,
pen_rgr.oipl_id,
pen_rgr.person_id,
pen_rgr.change_date
FROM (
SELECT CEIL(AVG(penc.elig_ind)) elig_ind,
CEIL(AVG(penc.enrt_ind)) enrt_ind,
CEIL(AVG(penc.dflt_ind)) dflt_ind,
CEIL(AVG(penc.waive_expl_ind)) waive_expl_ind,
CEIL(AVG(penc.waive_dflt_ind)) waive_dflt_ind,
penc.asnd_lf_evt_dt,
rgrd.rptgtyp_id,
copd.oipl_id,
penc.person_id,
penc.change_date
FROM hri_mb_ben_eligenrl_evnt_ct penc,
hri_cs_compobj_ct copd,
hri_cs_co_rpgh_pirg_ct rgrd
WHERE penc.compobj_sk_pk = copd.compobj_sk_pk
AND copd.pgm_id = rgrd.pgm_id
GROUP BY penc.asnd_lf_evt_dt, rgrd.rptgtyp_id, copd.oipl_id, penc.person_id, penc.change_date) pen_rgr
) prgr
GROUP BY prgr.asnd_lf_evt_dt, prgr.rptgtyp_id, prgr.oipl_id, prgr.change_date
) ee_opt
)
[Home] [Help]