[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id';
p_sqlstr := p_sqlstr || ' (SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt';
p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id ';
p_sqlstr := p_sqlstr || ' SELECT penq.person_id object_id ';
p_sqlstr := p_sqlstr || ' SELECT pelq.person_id object_id ';
INSERT INTO HRI_MB_BEN_ELIGENRL_EVNT_CT
(change_date
,effective_start_date
,effective_end_date
,compobj_sk_pk
,asnd_lf_evt_dt
,person_id
,per_in_ler_id
,enrt_perd_id
,prtt_enrt_rslt_id
,elig_ind
,enrt_ind
,not_enrt_ind
,dflt_ind
,waive_expl_ind
,waive_dflt_ind)
(SELECT ee.change_date change_date,
ee.change_date effective_start_date,
NVL(LEAD(ee.change_date - 1)
OVER (PARTITION BY compobj_sk_pk
ORDER BY compobj_sk_pk, ee.change_date), hr_api.g_eot) effective_end_date,
ee.compobj_sk_pk,
p_pil_rec.lf_evt_ocrd_dt asnd_lf_evt_dt ,
p_pil_rec.person_id person_id,
p_pil_rec.per_in_ler_id per_in_ler_id,
ee.enrt_perd_id,
ee.prtt_enrt_rslt_id,
ee.elig_ind,
ee.enrt_ind,
ee.not_enrt_ind,
ee.dflt_ind,
ee.waive_expl_ind,
ee.waive_dflt_ind
FROM ( -- Retuns all Electable Choices if Enrollments DOES NOT start on the same day.
-- First Part of UNION brings all PLIPs and OIPL IS NULL
SELECT pel.enrt_perd_strt_dt change_date,
copd.compobj_sk_pk compobj_sk_pk,
pel.enrt_perd_id,
epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1 elig_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 1
ELSE 0 END ) enrt_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 0
ELSE 1 END ) not_enrt_ind,
-- DFLT_IND -> If Currently Enrolled and Default Comp Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND epe.dflt_flag = 'Y')
THEN 1
ELSE 0 END) dflt_ind,
-- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND pln.invk_dcln_prtn_pl_flag = 'Y')
THEN 1
ELSE 0 END) waive_expl_ind,
-- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y'
AND pln.invk_dcln_prtn_pl_flag = 'Y')
THEN 1
ELSE 0 END) waive_dflt_ind
FROM ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
hri_cs_compobj_ct copd,
ben_pl_f pln
WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id
AND epe.elctbl_flag = 'Y'
AND epe.elig_flag = 'Y'
AND copd.oipl_id = -1
AND epe.oipl_id IS NULL
AND copd.plip_id = epe.plip_id
AND copd.pgm_id = epe.pgm_id -- As required for Perf.
AND copd.pl_id = epe.pl_id -- As required for Perf.
AND pln.pl_id = copd.pl_id
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND (epe.prtt_enrt_rslt_id IS NULL
OR NOT EXISTS (
SELECT null
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
AND pen.per_in_ler_id = p_pil_rec.per_in_ler_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.effective_end_date = hr_api.g_eot
AND pen.effective_start_date = pel.enrt_perd_strt_dt))
UNION ALL
-- Second Part of UNION brings all OIPLs
SELECT pel.enrt_perd_strt_dt change_date,
copd.compobj_sk_pk compobj_sk_pk,
pel.enrt_perd_id,
epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1 elig_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 1
ELSE 0 END ) enrt_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 0
ELSE 1 END ) not_enrt_ind,
-- DFLT_IND -> If Currently Enrolled and Default Comp Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND epe.dflt_flag = 'Y')
THEN 1
ELSE 0 END) dflt_ind,
-- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND opt.invk_wv_opt_flag = 'Y')
THEN 1
ELSE 0 END) waive_expl_ind,
-- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y'
AND opt.invk_wv_opt_flag = 'Y')
THEN 1
ELSE 0 END) waive_dflt_ind
FROM ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
hri_cs_compobj_ct copd,
ben_opt_f opt
WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id
AND epe.elctbl_flag = 'Y'
AND epe.elig_flag = 'Y'
AND copd.oipl_id = epe.oipl_id
AND copd.plip_id = epe.plip_id
AND copd.pgm_id = epe.pgm_id -- As required for perf.
AND copd.pl_id = epe.pl_id -- As required for perf.
AND opt.opt_id = copd.opt_id
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date
AND opt.effective_end_date
AND (epe.prtt_enrt_rslt_id IS NULL
OR NOT EXISTS (
SELECT null
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
AND pen.per_in_ler_id = p_pil_rec.per_in_ler_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.effective_end_date = hr_api.g_eot
AND pen.effective_start_date = pel.enrt_perd_strt_dt))
) ee
);
DELETE from hri_mb_ben_elctn_evnt_ct
WHERE person_id = p_pil_rec.person_id
AND asnd_lf_evt_dt = p_pil_rec.lf_evt_ocrd_dt;
INSERT INTO hri_mb_ben_elctn_evnt_ct
( elig_ind
,enrt_ind
,not_enrt_ind
,dflt_ind
,ler_status_cd
,voidd_ind
,bckdt_ind
,procd_ind
,strtd_ind
,change_date
,effective_start_date
,effective_end_date
,person_id
,asnd_lf_evt_dt
,enrt_perd_id
,per_in_ler_id
,pgm_id
,pil_elctbl_chc_popl_id )
(SELECT DECODE (pel.pil_elctbl_popl_stat_cd,'BCKDT',0,'VOIDD',0,1) elig_ind
,(CASE WHEN (pel.pil_elctbl_popl_stat_cd NOT IN ('BCKDT','VOIDD')
AND (pel.elcns_made_dt IS NOT NULL)) -- 4721802: Not counting Automatics
THEN 1
ELSE 0 END) enrt_ind
,(CASE WHEN (pel.pil_elctbl_popl_stat_cd NOT IN ('BCKDT','VOIDD')
AND pel.elcns_made_dt IS NULL
-- AND pel.auto_asnd_dt IS NULL -- 4568414
AND pel.dflt_asnd_dt IS NULL)
THEN 1
ELSE 0 END) not_enrt_ind
,(CASE WHEN (pel.pil_elctbl_popl_stat_cd NOT IN ('BCKDT','VOIDD')
AND pel.elcns_made_dt IS NULL
AND pel.auto_asnd_dt IS NULL -- 4568414
AND pel.dflt_asnd_dt IS NOT NULL)
THEN 1
ELSE 0 END) dflt_ind
,(CASE WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
AND ppl.ptnl_ler_for_per_stat_cd = 'MNL') -- 4514159
THEN 'MNL'
WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
AND ppl.ptnl_ler_for_per_stat_cd <> 'MNL') -- 4514159
THEN 'BCKDT'
ELSE pil.per_in_ler_stat_cd END ) per_in_ler_stat_cd
,DECODE(pil.per_in_ler_stat_cd,'VOIDD',1,0) voidd_ind
,DECODE(pil.per_in_ler_stat_cd,'BCKDT',1,0) bckdt_ind
,DECODE(pil.per_in_ler_stat_cd,'PROCD',1,0) procd_ind
,DECODE(pil.per_in_ler_stat_cd,'STRTD',1,0) strtd_ind
,pil.lf_evt_ocrd_dt change_date
,pil.lf_evt_ocrd_dt effective_start_date
,hr_api.g_eot effective_end_date
,pil.person_id person_id
,pil.lf_evt_ocrd_dt asnd_lf_evt_dt
,pel.enrt_perd_id enrt_perd_id
,pil.per_in_ler_id per_in_ler_id
,pel.pgm_id pgm_id
,pel.pil_elctbl_chc_popl_id pil_elctbl_chc_popl_id
FROM ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil,
ben_ptnl_ler_for_per ppl
WHERE pel.per_in_ler_id = pil.per_in_ler_id
AND ppl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id
AND pil.per_in_ler_id = p_pil_rec.per_in_ler_id
);
SELECT pil.per_in_ler_id
,pil.person_id
,pil.lf_evt_ocrd_dt
,(CASE WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
AND ppl.ptnl_ler_for_per_stat_cd = 'MNL') -- 4514159
THEN 'MNL'
WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
AND ppl.ptnl_ler_for_per_stat_cd <> 'MNL') -- 4514159
THEN 'BCKDT'
ELSE pil.per_in_ler_stat_cd END ) per_in_ler_stat_cd
,pil.business_group_id
FROM ben_per_in_ler pil,
ben_ptnl_ler_for_per ppl
WHERE pil.per_in_ler_id = p_per_in_ler_id
AND ppl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id;
SELECT per_in_ler_id,
pil.per_in_ler_stat_cd
FROM ben_per_in_ler pil,
ben_ler_f ler
WHERE pil.lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
AND pil.ler_id = ler.ler_id
AND ler.typ_cd = 'SCHEDDO'
AND pil.person_id = p_person_id
AND pil.per_in_ler_stat_cd IN ('BCKDT','VOIDD')
AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date
AND ler.effective_end_date;
SELECT null
FROM HRI_EQ_BEN_ELCTN_EVTS pelq
WHERE pelq.person_id = p_person_id
AND pelq.lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
AND pelq.per_in_ler_id = p_per_in_ler_id
AND event_cd = 'INSERT';
SELECT 1 elig_ind
,(CASE WHEN (pelq.elcns_made_dt IS NOT NULL )
--OR pelq.auto_asnd_dt IS NOT NULL) -- 4721802: Not Counting Automatics
THEN 1
ELSE 0 END) enrt_ind
,(CASE WHEN (pelq.elcns_made_dt IS NULL
AND pelq.dflt_asnd_dt IS NULL) -- 4721802
--AND pelq.auto_asnd_dt IS NULL)
THEN 1
ELSE 0 END) not_enrt_ind
,(CASE WHEN (pelq.elcns_made_dt IS NULL
AND pelq.auto_asnd_dt IS NULL
AND pelq.dflt_asnd_dt IS NOT NULL)
THEN 1
ELSE 0 END) dflt_ind
,NVL(pelq.pil_elctbl_popl_stat_cd,'STRTD') pil_elctbl_popl_stat_cd
,DECODE(pelq.pil_elctbl_popl_stat_cd,'VOIDD',1,0) voidd_ind
,DECODE(pelq.pil_elctbl_popl_stat_cd,'BCKDT',1,0) bckdt_ind
,DECODE(pelq.pil_elctbl_popl_stat_cd,'PROCD',1,0) procd_ind
,DECODE(pelq.pil_elctbl_popl_stat_cd,'STRTD',1,NULL,1,0) strtd_ind
,pelq.lf_evt_ocrd_dt change_date
,pelq.lf_evt_ocrd_dt effective_start_date
,hr_api.g_eot effective_end_date
,pelq.person_id person_id
,pelq.lf_evt_ocrd_dt asnd_lf_evt_dt
,enpd.enrt_perd_id enrt_perd_id
,pelq.per_in_ler_id per_in_ler_id
,pelq.pgm_id pgm_id
,pelq.pil_elctbl_chc_popl_id pil_elctbl_chc_popl_id
FROM hri_eq_ben_elctn_evts pelq,
hri_cs_time_benrl_prd_ct enpd
WHERE pelq.pil_elctbl_popl_stat_cd IN ('STRTD','PROCD')
AND pelq.event_cd = 'UPDATE'
AND pelq.per_in_ler_id = p_per_in_ler_id
AND pelq.person_id = p_person_id
AND pelq.pgm_id = enpd.pgm_id
AND enpd.asnd_lf_evt_dt = pelq.lf_evt_ocrd_dt
AND pelq.last_update_date =
(SELECT MAX(pelq1.last_update_date)
FROM hri_eq_ben_elctn_evts pelq1
WHERE pelq1.per_in_ler_id = p_per_in_ler_id
AND pelq1.person_id = p_person_id
AND pelq1.pgm_id = pelq.pgm_id
AND pelq1.lf_evt_ocrd_dt = pelq.lf_evt_ocrd_dt
AND pelq1.event_cd = 'UPDATE'
AND pelq1.pil_elctbl_popl_stat_cd IN ('STRTD','PROCD')
);
DELETE FROM hri_mb_ben_eligenrl_evnt_ct penc
WHERE penc.person_id = p_person_id
AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
AND penc.per_in_ler_id = p_per_in_ler_id;
UPDATE hri_mb_ben_elctn_evnt_ct pelc
SET pelc.elig_ind = 0
,pelc.enrt_ind = 0
,pelc.not_enrt_ind = 0
,pelc.dflt_ind = 0
,pelc.ler_status_cd = l_pil_rec.per_in_ler_stat_cd
,pelc.voidd_ind = 0
,pelc.bckdt_ind = 0
,pelc.procd_ind = 0
,pelc.strtd_ind = 0
,pelc.effective_start_date = l_pil_rec.lf_evt_ocrd_dt
,pelc.effective_end_date = hr_api.g_eot
,pelc.pil_elctbl_chc_popl_id = NULL
WHERE pelc.person_id = p_person_id
AND pelc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
AND pelc.per_in_ler_id = l_pil_rec.per_in_ler_id;
DELETE FROM hri_mb_ben_eligenrl_evnt_ct penc
WHERE penc.person_id = p_person_id
AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
AND penc.per_in_ler_id = i.per_in_ler_id;
DELETE FROM hri_mb_ben_elctn_evnt_ct pelc
WHERE pelc.person_id = p_person_id
AND pelc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
AND pelc.per_in_ler_id = i.per_in_ler_id;
UPDATE HRI_MB_BEN_ELCTN_EVNT_CT pelc
SET pelc.elig_ind = l_elcn_evt_tbl(i).elig_ind
,pelc.enrt_ind = l_elcn_evt_tbl(i).enrt_ind
,pelc.not_enrt_ind = l_elcn_evt_tbl(i).not_enrt_ind
,pelc.dflt_ind = l_elcn_evt_tbl(i).dflt_ind
,pelc.ler_status_cd = l_elcn_evt_tbl(i).pil_elctbl_popl_stat_cd
,pelc.voidd_ind = l_elcn_evt_tbl(i).voidd_ind
,pelc.bckdt_ind = l_elcn_evt_tbl(i).bckdt_ind
,pelc.procd_ind = l_elcn_evt_tbl(i).procd_ind
,pelc.strtd_ind = l_elcn_evt_tbl(i).strtd_ind
,pelc.effective_start_date = l_elcn_evt_tbl(i).effective_start_date
,pelc.effective_end_date = l_elcn_evt_tbl(i).effective_end_date
,pelc.enrt_perd_id = l_elcn_evt_tbl(i).enrt_perd_id
,pelc.pil_elctbl_chc_popl_id = l_elcn_evt_tbl(i).pil_elctbl_chc_popl_id
WHERE pelc.person_id = l_elcn_evt_tbl(i).person_id
AND pelc.asnd_lf_evt_dt = l_elcn_evt_tbl(i).asnd_lf_evt_dt
AND pelc.pgm_id = l_elcn_evt_tbl(i).pgm_id;
UPDATE hri_mb_ben_eligenrl_evnt_ct penc
SET penc.dflt_ind = 0
,waive_dflt_ind = 0
,waive_expl_ind = DECODE(penc.waive_dflt_ind,1,1,0)
WHERE penc.enrt_ind = 1
AND penc.person_id = p_person_id
AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
AND penc.per_in_ler_id = l_pil_rec.per_in_ler_id
AND EXISTS
(SELECT NULL
FROM HRI_EQ_BEN_ELCTN_EVTS pelq
WHERE pelq.event_cd = 'UPDATE'
AND pelq.pil_elctbl_popl_stat_cd IN ('STRTD','PROCD')
AND pelq.per_in_ler_id = p_per_in_ler_id
AND pelq.person_id = p_person_id
AND pelq.lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
AND pelq.elcns_made_dt IS NOT NULL);
SELECT PRTTQ.eee_end_dt, penc.rowid row_id
FROM (SELECT NVL(MIN(penq.event_date-1),hr_api.g_eot) eee_end_dt,
copd.compobj_sk_pk,
penq.lf_evt_ocrd_dt
FROM hri_eq_ben_eligenrl_evts penq,
hri_cs_compobj_ct copd
WHERE penq.per_in_ler_id = p_per_in_ler_id
AND penq.pgm_id = copd.pgm_id
AND copd.oipl_id = NVL(penq.oipl_id, -1)
AND copd.pl_id = penq.pl_id
GROUP BY penq.lf_evt_ocrd_dt, copd.compobj_sk_pk ) PRTTQ,
hri_mb_ben_eligenrl_evnt_ct penc
WHERE penc.asnd_lf_evt_dt = PRTTQ.lf_evt_ocrd_dt
AND penc.person_id = p_person_id
AND penc.per_in_ler_id = p_per_in_ler_id
AND (PRTTQ.eee_end_dt + 1) > penc.effective_start_date
AND penc.effective_end_date = hr_api.g_eot
AND PRTTQ.compobj_sk_pk = penc.compobj_sk_pk;
UPDATE hri_mb_ben_eligenrl_evnt_ct penc
SET enrt_ind = 0
,not_enrt_ind = 1
,dflt_ind = 0
,waive_expl_ind = 0
,waive_dflt_ind = 0
WHERE (penc.compobj_sk_pk, penc.asnd_lf_evt_dt, penc.person_id)
IN (SELECT copd.compobj_sk_pk
,penq.lf_evt_ocrd_dt
,penq.person_id
FROM hri_eq_ben_eligenrl_evts penq,
hri_cs_compobj_ct copd
WHERE penq.per_in_ler_id = p_per_in_ler_id
AND penq.event_cd IN ('ZAP','DE-ENRD')
AND penq.pgm_id = copd.pgm_id
AND copd.oipl_id = NVL(penq.oipl_id, -1)
AND copd.pl_id = penq.pl_id);
l_eee_end_dt_tbl.delete;
l_row_id_tbl.delete;
UPDATE hri_mb_ben_eligenrl_evnt_ct penc
SET penc.effective_end_date = l_eee_end_dt_tbl(i)
WHERE ROWID = l_row_id_tbl(i);
SELECT copd.compobj_sk_pk
,enpd.enrt_perd_id
,penq.lf_evt_ocrd_dt
,penq.event_date
,penq.event_date effective_start_date
,NVL(LAG(penq.event_date-1)
OVER (PARTITION BY penq.lf_evt_ocrd_dt, copd.compobj_sk_pk
ORDER BY penq.lf_evt_ocrd_dt, copd.compobj_sk_pk, penq.event_date, penq.creation_date)
, hr_api.g_eot) effective_end_date
,penq.person_id
,penq.prtt_enrt_rslt_id
,penq.per_in_ler_id
,1 elig_ind
,penq.enrt_ind
,(CASE WHEN (penq.event_cd IN ('DE-ENRD','ZAP')) -- Only 'ENRD' events come up.. so this may not be necessary.
THEN 1
ELSE 0 END ) not_enrt_ind
,penq.dflt_ind
,(CASE WHEN (penq.dflt_ind = 0
AND penq.enrt_ind = 1
AND (opt.invk_wv_opt_flag = 'Y'
OR (opt.opt_id IS NULL AND pln.invk_dcln_prtn_pl_flag = 'Y')) )
THEN 1
ELSE 0 END ) waive_expl_ind
,(CASE WHEN (penq.dflt_ind = 1
AND penq.enrt_ind = 1
AND (opt.invk_wv_opt_flag = 'Y'
OR (opt.opt_id IS NULL AND pln.invk_dcln_prtn_pl_flag = 'Y')) )
THEN 1
ELSE 0 END ) waive_dflt_ind
FROM HRI_EQ_BEN_ELIGENRL_EVTS penq,
hri_cs_time_benrl_prd_ct enpd,
hri_cs_compobj_ct copd,
ben_opt_f opt,
ben_pl_f pln
WHERE penq.per_in_ler_id = p_per_in_ler_id
AND penq.event_cd IN ('ENRD') --,'DE-ENRD')
AND opt.opt_id(+) = NVL(copd.opt_id,-1)
AND pln.pl_id = copd.pl_id
AND penq.pgm_id = copd.pgm_id
AND enpd.pgm_id = penq.pgm_id
AND p_lf_evt_ocrd_dt between opt.effective_start_date(+) AND opt.effective_end_date(+)
AND p_lf_evt_ocrd_dt between pln.effective_start_date AND pln.effective_end_date
AND enpd.asnd_lf_evt_dt = penq.lf_evt_ocrd_dt
AND copd.oipl_id = NVL(penq.oipl_id, -1)
AND copd.pl_id = penq.pl_id
AND NOT EXISTS
(SELECT null -- Picks up only the latest Event from queue.
FROM HRI_EQ_BEN_ELIGENRL_EVTS penq1
WHERE penq1.per_in_ler_id = p_per_in_ler_id
AND NVL(penq1.oipl_id,-1) = NVL(penq.oipl_id,-1)
AND penq1.pl_id = penq.pl_id
AND penq1.pgm_id = penq.pgm_id
AND NVL(penq1.last_update_date,TRUNC(SYSDATE)) > NVL(penq.last_update_date,TRUNC(SYSDATE))
)
) PRTTQ
ON (PRTTQ.compobj_sk_pk = penc.compobj_sk_pk
AND PRTTQ.lf_evt_ocrd_dt = penc.asnd_lf_evt_dt
AND PRTTQ.event_date = penc.change_date
AND PRTTQ.person_id = penc.person_id)
WHEN MATCHED THEN
UPDATE SET penc.elig_ind = PRTTQ.elig_ind,
penc.enrt_ind = PRTTQ.enrt_ind,
penc.dflt_ind = PRTTQ.dflt_ind,
penc.not_enrt_ind = PRTTQ.not_enrt_ind,
penc.waive_expl_ind = PRTTQ.waive_expl_ind,
penc.waive_dflt_ind = PRTTQ.waive_dflt_ind
WHEN NOT MATCHED THEN
INSERT (compobj_sk_pk
,enrt_perd_id
,asnd_lf_evt_dt
,change_date
,effective_start_date
,effective_end_date
,person_id
,prtt_enrt_rslt_id
,per_in_ler_id
,elig_ind
,enrt_ind
,not_enrt_ind
,dflt_ind
,waive_expl_ind
,waive_dflt_ind)
VALUES (PRTTQ.compobj_sk_pk
,PRTTQ.enrt_perd_id
,PRTTQ.lf_evt_ocrd_dt
,PRTTQ.event_date
,PRTTQ.effective_start_date
,PRTTQ.effective_end_date
,PRTTQ.person_id
,PRTTQ.prtt_enrt_rslt_id
,PRTTQ.per_in_ler_id
,PRTTQ.elig_ind
,PRTTQ.enrt_ind
,PRTTQ.not_enrt_ind
,PRTTQ.dflt_ind
,PRTTQ.waive_expl_ind
,PRTTQ.waive_dflt_ind);
DELETE FROM HRI_MB_BEN_ELIGENRL_EVNT_CT penc
WHERE penc.person_id = p_person_id
AND penc.per_in_ler_id = p_per_in_ler_id
AND penc.effective_start_date > penc.effective_end_date;
DELETE from hri_mb_ben_eligenrl_evnt_ct
WHERE person_id = p_pil_rec.person_id
AND asnd_lf_evt_dt = p_pil_rec.lf_evt_ocrd_dt
AND per_in_ler_id = p_pil_rec.per_in_ler_id;
INSERT INTO HRI_MB_BEN_ELIGENRL_EVNT_CT
(change_date
,effective_start_date
,effective_end_date
,compobj_sk_pk
,asnd_lf_evt_dt
,person_id
,per_in_ler_id
,enrt_perd_id
,prtt_enrt_rslt_id
,elig_ind
,enrt_ind
,not_enrt_ind
,dflt_ind
,waive_expl_ind
,waive_dflt_ind)
(SELECT ee.change_date change_date,
ee.change_date effective_start_date,
NVL(LEAD(ee.change_date - 1)
OVER (PARTITION BY compobj_sk_pk
ORDER BY compobj_sk_pk, ee.change_date), hr_api.g_eot) effective_end_date,
ee.compobj_sk_pk,
p_pil_rec.lf_evt_ocrd_dt asnd_lf_evt_dt ,
p_pil_rec.person_id person_id,
p_pil_rec.per_in_ler_id per_in_ler_id,
ee.enrt_perd_id,
ee.prtt_enrt_rslt_id,
ee.elig_ind,
ee.enrt_ind,
ee.not_enrt_ind,
ee.dflt_ind,
ee.waive_expl_ind,
ee.waive_dflt_ind
FROM (
-- The FIRST 2 UNIONS.. retuns all Electable Choices if Enrollments DOES NOT start on the same day.
-- First UNION gets PLIPs and OIPL IS NULL
SELECT pel.enrt_perd_strt_dt change_date,
copd.compobj_sk_pk compobj_sk_pk,
pel.enrt_perd_id,
epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1 elig_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 1
ELSE 0 END ) enrt_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 0
ELSE 1 END ) not_enrt_ind,
-- DFLT_IND -> If Currently Enrolled and Default Comp Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND epe.dflt_flag = 'Y')
THEN 1
ELSE 0 END) dflt_ind,
-- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND pln.invk_dcln_prtn_pl_flag = 'Y')
THEN 1
ELSE 0 END) waive_expl_ind,
-- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y'
AND pln.invk_dcln_prtn_pl_flag = 'Y')
THEN 1
ELSE 0 END) waive_dflt_ind
FROM ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
hri_cs_compobj_ct copd,
ben_pl_f pln
WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id
AND epe.elctbl_flag = 'Y'
AND epe.elig_flag = 'Y'
AND copd.oipl_id = -1
AND epe.oipl_id IS NULL
AND copd.plip_id = epe.plip_id
AND copd.pgm_id = epe.pgm_id -- As required for Perf.
AND copd.pl_id = epe.pl_id -- As required for Perf.
AND pln.pl_id = copd.pl_id
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND (epe.prtt_enrt_rslt_id IS NULL
OR NOT EXISTS (
SELECT null
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
AND pen.per_in_ler_Id = p_pil_rec.per_in_ler_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.effective_end_date = hr_api.g_eot
AND pen.effective_start_date = pel.enrt_perd_strt_dt))
UNION ALL
-- Second UNION gets OIPLs
SELECT pel.enrt_perd_strt_dt change_date,
copd.compobj_sk_pk compobj_sk_pk,
pel.enrt_perd_id,
epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1 elig_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 1
ELSE 0 END ) enrt_ind,
(CASE WHEN (epe.crntly_enrd_flag = 'Y')
THEN 0
ELSE 1 END ) not_enrt_ind,
-- DFLT_IND -> If Currently Enrolled and Default Comp Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND epe.dflt_flag = 'Y')
THEN 1
ELSE 0 END) dflt_ind,
-- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NOT NULL
AND epe.crntly_enrd_flag = 'Y'
AND opt.invk_wv_opt_flag = 'Y' )
THEN 1
ELSE 0 END) waive_expl_ind,
-- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y'
AND opt.invk_wv_opt_flag = 'Y')
THEN 1
ELSE 0 END) waive_dflt_ind
FROM ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
hri_cs_compobj_ct copd,
ben_opt_f opt
WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
/* AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id : 4552984 - Perf Fix */
AND EPE.PER_IN_LER_ID = p_pil_rec.per_in_ler_id
AND epe.elctbl_flag = 'Y'
AND epe.elig_flag = 'Y'
AND copd.oipl_id = epe.oipl_id
AND copd.plip_id = epe.plip_id
AND copd.pgm_id = epe.pgm_id
AND copd.pl_id = epe.pl_id
AND opt.opt_id = copd.opt_id
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date
AND opt.effective_end_date
AND (epe.prtt_enrt_rslt_id IS NULL
OR NOT EXISTS (
SELECT null
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
AND pen.per_in_ler_Id = p_pil_rec.per_in_ler_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.effective_end_date = hr_api.g_eot
AND pen.effective_start_date = pel.enrt_perd_strt_dt))
UNION ALL
-- The 3rd and 4th UNIONs.. returns all Enrollment Results.
-- 3rd Union gets all PLIPs and OIPL IS NULL
SELECT /*+ INDEX(epe BEN_ELIG_PER_ELCTBL_CHC_N5) */
pen.effective_start_date change_date,
copd.compobj_sk_pk compobj_sk_pk,
pel.enrt_perd_id,
pen.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1 elig_ind,
1 enrt_ind,
0 not_enrt_ind,
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y')
THEN 1
ELSE 0 END) dflt_ind,
(CASE WHEN (pel.elcns_made_dt IS NOT NULL
AND pln.invk_dcln_prtn_pl_flag = 'Y')
THEN 1
ELSE 0 END) waive_expl_ind,
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y'
AND pln.invk_dcln_prtn_pl_flag = 'Y')
THEN 1
ELSE 0 END) waive_dflt_ind
FROM ben_prtt_enrt_rslt_f pen,
ben_pil_elctbl_chc_popl pel,
hri_cs_compobj_ct copd,
ben_elig_per_elctbl_chc epe,
ben_pl_f pln
WHERE pel.per_in_ler_id = p_pil_rec.per_in_ler_id
AND pen.per_in_ler_id = pel.per_in_ler_id
AND pen.pgm_id = copd.pgm_id
AND pen.pgm_id = pel.pgm_id
AND copd.oipl_id = -1
AND pen.oipl_id IS NULL
AND copd.pl_id = pen.pl_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.effective_end_date = hr_api.g_eot
AND epe.prtt_enrt_rslt_id(+) = pen.prtt_enrt_rslt_id
AND epe.per_in_ler_id(+) = pen.per_in_ler_id
AND pln.pl_id = copd.pl_id
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
AND pln.effective_end_date
UNION ALL
-- 4th Union gets all OIPLs
SELECT /*+ INDEX(epe BEN_ELIG_PER_ELCTBL_CHC_N5) */
pen.effective_start_date change_date,
copd.compobj_sk_pk compobj_sk_pk,
pel.enrt_perd_id,
pen.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1 elig_ind,
1 enrt_ind,
0 not_enrt_ind,
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y')
THEN 1
ELSE 0 END) dflt_ind,
(CASE WHEN (pel.elcns_made_dt IS NOT NULL
AND opt.invk_wv_opt_flag = 'Y')
THEN 1
ELSE 0 END) waive_expl_ind,
(CASE WHEN (pel.elcns_made_dt IS NULL
AND pel.dflt_asnd_dt IS NOT NULL
AND epe.dflt_flag = 'Y'
AND opt.invk_wv_opt_flag = 'Y' )
THEN 1
ELSE 0 END) waive_dflt_ind
FROM ben_prtt_enrt_rslt_f pen,
ben_pil_elctbl_chc_popl pel,
hri_cs_compobj_ct copd,
ben_elig_per_elctbl_chc epe,
ben_opt_f opt
WHERE /* pel.per_in_ler_id = p_pil_rec.per_in_ler_id : 4552984 - Perf Fix */
EPE.PER_IN_LER_ID = p_pil_rec.per_in_ler_id
AND pen.per_in_ler_id = pel.per_in_ler_id
AND pen.pgm_id = copd.pgm_id
AND pen.pgm_id = pel.pgm_id
AND copd.oipl_id = pen.oipl_id
AND copd.pl_id = pen.pl_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.effective_end_date = hr_api.g_eot
AND epe.prtt_enrt_rslt_id(+) = pen.prtt_enrt_rslt_id
AND epe.per_in_ler_id(+) = pen.per_in_ler_id
AND opt.opt_id = copd.opt_id
AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date
AND opt.effective_end_date
) ee
);
SELECT pil.per_in_ler_id,
pil.person_id,
pil.lf_evt_ocrd_dt,
pil.per_in_ler_stat_cd,
pil.business_group_id
FROM ben_per_in_ler pil,
ben_ler_f ler
WHERE pil.ler_id = ler.ler_id
AND ler.typ_cd = 'SCHEDDO'
AND pil.per_in_ler_stat_cd IN ('STRTD','PROCD')
AND pil.lf_evt_ocrd_dt >= g_global_start_date
AND pil.person_id BETWEEN p_start_object_id AND p_end_object_id
UNION
SELECT pil3.per_in_ler_id,
pil3.person_id,
pil3.lf_evt_ocrd_dt,
pil3.per_in_ler_stat_cd,
pil3.business_group_id
FROM ben_per_in_ler pil3,
(SELECT MAX(pil1.per_in_ler_id) per_in_ler_id
FROM ben_per_in_ler pil1,
ben_ler_f ler1
WHERE pil1.ler_id = ler1.ler_id
AND ler1.typ_cd = 'SCHEDDO'
AND pil1.per_in_ler_stat_cd IN ('BCKDT','VOIDD')
AND pil1.lf_evt_ocrd_dt >= g_global_start_date
AND pil1.person_id BETWEEN p_start_object_id AND p_end_object_id
AND NOT EXISTS (SELECT null
-- DO NOT pick up Backed/Voided events, if a Started/Processed Event exists.
FROM ben_per_in_ler pil2,
ben_ler_f ler2
WHERE pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
AND pil2.person_id = pil1.person_id
AND pil2.per_in_ler_stat_cd IN ('STRTD','PROCD')
AND pil2.ler_id = ler2.ler_id
AND ler2.typ_cd = 'SCHEDDO'
AND pil2.lf_evt_ocrd_dt BETWEEN ler2.effective_start_date
AND ler2.effective_end_date)
GROUP BY pil1.person_id, pil1.lf_evt_ocrd_dt
) pil4
WHERE pil4.per_in_ler_id = pil3.per_in_ler_id
AND pil3.lf_evt_ocrd_dt >= g_global_start_date
ORDER BY 2, 3;
SELECT pil.per_in_ler_id,
pil.person_id,
pil.lf_evt_ocrd_dt,
pil.per_in_ler_stat_cd,
pil.business_group_id
FROM ben_per_in_ler pil,
(SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt
, pil.business_group_id
, pil.ler_id
FROM ben_per_in_ler pil,
ben_ler_f ler
WHERE pil.ler_id = ler.ler_id
AND ler.typ_cd = 'SCHEDDO'
AND pil.per_in_ler_stat_cd = 'STRTD'
AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date
AND ler.effective_end_date
GROUP BY pil.business_group_id, pil.ler_id ) pil1
WHERE pil.ler_id = pil1.ler_id
AND pil.business_group_id = pil1.business_group_id
AND pil.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
AND pil.lf_evt_ocrd_dt >= g_global_start_date
AND pil.person_id BETWEEN p_start_object_id AND p_end_object_id
ORDER BY 2, 3;
SELECT DISTINCT penq.person_id, penq.per_in_ler_id, lf_evt_ocrd_dt
FROM HRI_EQ_BEN_ELIGENRL_EVTS penq
WHERE penq.person_id BETWEEN p_start_object_id AND p_end_object_id
ORDER BY penq.person_id, penq.per_in_ler_id;
SELECT DISTINCT pelq.person_id, pelq.per_in_ler_id
FROM HRI_EQ_BEN_ELCTN_EVTS pelq
WHERE pelq.person_id BETWEEN p_start_object_id AND p_end_object_id
ORDER BY pelq.person_id, pelq.per_in_ler_id;
l_person_tbl.delete;
l_pil_tbl.delete;
OUTPUT('Full Refresh selected - Creating indexes');