(
SELECT /* 12.0: bug#4526784 */ ee_cpp.asnd_lf_evt_dt,
ee_cpp.plip_id,
ee_cpp.effective_start_date,
ee_cpp.effective_end_date,
ee_cpp.elig_count,
ee_cpp.enrt_count,
ROUND((CASE WHEN ee_cpp.elig_count <= 0
THEN 0
ELSE ee_cpp.enrt_count/ee_cpp.elig_count END )*100 , 1) enrt_per,
ee_cpp.waive_expl_count,
ee_cpp.waive_dflt_count,
ee_cpp.waive_dflt_count + ee_cpp.waive_expl_count waive_total_count,
ROUND((CASE WHEN ee_cpp.elig_count <= 0
THEN 0
ELSE ee_cpp.waive_expl_count/ee_cpp.elig_count END )*100 , 1) waive_expl_per,
ROUND((CASE WHEN ee_cpp.elig_count <= 0
THEN 0
ELSE ee_cpp.waive_dflt_count/ee_cpp.elig_count END )*100 , 1) waive_dflt_per,
ROUND((CASE WHEN ee_cpp.elig_count <= 0
THEN 0
ELSE (ee_cpp.waive_expl_count + ee_cpp.waive_dflt_count)/ee_cpp.elig_count END )*100 , 1) waive_total_per
FROM (
SELECT SUM(SUM(pcpp.elig_ind)) OVER
(PARTITION BY pcpp.asnd_lf_evt_dt, pcpp.plip_id
ORDER BY pcpp.asnd_lf_evt_dt, pcpp.plip_id, pcpp.change_date) elig_count,
SUM(SUM(pcpp.enrt_ind)) OVER
(PARTITION BY pcpp.asnd_lf_evt_dt, pcpp.plip_id
ORDER BY pcpp.asnd_lf_evt_dt, pcpp.plip_id, pcpp.change_date) enrt_count,
SUM(SUM(pcpp.dflt_ind)) OVER
(PARTITION BY pcpp.asnd_lf_evt_dt, pcpp.plip_id
ORDER BY pcpp.asnd_lf_evt_dt, pcpp.plip_id, pcpp.change_date) dflt_count,
SUM(SUM(pcpp.waive_expl_ind)) OVER
(PARTITION BY pcpp.asnd_lf_evt_dt, pcpp.plip_id
ORDER BY pcpp.asnd_lf_evt_dt, pcpp.plip_id, pcpp.change_date) waive_expl_count,
SUM(SUM(pcpp.waive_dflt_ind)) OVER
(PARTITION BY pcpp.asnd_lf_evt_dt, pcpp.plip_id
ORDER BY pcpp.asnd_lf_evt_dt, pcpp.plip_id, pcpp.change_date) waive_dflt_count,
pcpp.asnd_lf_evt_dt,
pcpp.plip_id,
pcpp.change_date,
pcpp.change_date effective_start_date,
NVL(LEAD(pcpp.change_date-1) OVER
(PARTITION BY pcpp.asnd_lf_evt_dt, pcpp.plip_id
ORDER BY pcpp.asnd_lf_evt_dt, pcpp.plip_id, pcpp.change_date),TO_DATE('31-12-4712','DD-MM-YYYY')) effective_end_date
FROM (
SELECT pen_cpp.elig_ind -
NVL(LAG(pen_cpp.elig_ind) OVER
(PARTITION BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id
ORDER BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id, pen_cpp.change_date),0) elig_ind,
pen_cpp.enrt_ind -
NVL(LAG(pen_cpp.enrt_ind) OVER
(PARTITION BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id
ORDER BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id, pen_cpp.change_date),0) enrt_ind,
pen_cpp.dflt_ind -
NVL(LAG(pen_cpp.dflt_ind) OVER
(PARTITION BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id
ORDER BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id, pen_cpp.change_date),0) dflt_ind,
pen_cpp.waive_expl_ind -
NVL(LAG(pen_cpp.waive_expl_ind) OVER
(PARTITION BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id
ORDER BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id, pen_cpp.change_date),0) waive_expl_ind,
pen_cpp.waive_dflt_ind -
NVL(LAG(pen_cpp.waive_dflt_ind) OVER
(PARTITION BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id
ORDER BY pen_cpp.asnd_lf_evt_dt, pen_cpp.person_id, pen_cpp.plip_id, pen_cpp.change_date),0) waive_dflt_ind,
pen_cpp.asnd_lf_evt_dt,
pen_cpp.plip_id,
pen_cpp.person_id,
pen_cpp.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,
copd.plip_id,
penc.person_id,
penc.change_date
FROM hri_mb_ben_eligenrl_evnt_ct penc,
hri_cs_compobj_ct copd
WHERE copd.compobj_sk_pk = penc.compobj_sk_pk
GROUP BY penc.asnd_lf_evt_dt, copd.plip_id, penc.person_id, penc.change_date) pen_cpp
) pcpp
GROUP BY pcpp.asnd_lf_evt_dt, pcpp.plip_id, pcpp.change_date
) ee_cpp
)
[Home] [Help]