[Home] [Help]
76: -- Global warning indicator
77: --
78: g_raise_warning VARCHAR2(1);
79: --
80: g_eligenrl_evnt_table VARCHAR2(50) := 'HRI_MB_BEN_ELIGENRL_EVNT_CT';
81: g_elctn_evnt_table VARCHAR2(50) := 'HRI_MB_BEN_ELCTN_EVNT_CT';
82: --
83: g_elctn_evts_eq_table VARCHAR2(50) := 'HRI_EQ_BEN_ELCTN_EVTS';
84: g_eligenrl_evts_eq_table VARCHAR2(50) := 'HRI_EQ_BEN_ELIGENRL_EVTS';
305:
306: BEGIN
307: -- dbg('Entering ' || l_procedure);
308:
309: INSERT INTO HRI_MB_BEN_ELIGENRL_EVNT_CT
310: (change_date
311: ,effective_start_date
312: ,effective_end_date
313: ,compobj_sk_pk
681: IF (l_pil_rec.per_in_ler_stat_cd IN ('BCKDT','VOIDD','MNL')) THEN
682: -- For Backed/Voided Elections ...
683:
684: -- 1. Delete Eligibility/Enrollment events ...
685: DELETE FROM hri_mb_ben_eligenrl_evnt_ct penc
686: WHERE penc.person_id = p_person_id
687: AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
688: AND penc.per_in_ler_id = p_per_in_ler_id;
689:
709: -- For Started/Process Elections ...
710:
711: FOR i IN c_bckdt_void_pil LOOP
712: -- 1. Delete Any Back-out / Voided Enrollment Events
713: DELETE FROM hri_mb_ben_eligenrl_evnt_ct penc
714: WHERE penc.person_id = p_person_id
715: AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
716: AND penc.per_in_ler_id = i.per_in_ler_id;
717:
767: END IF;
768: CLOSE c_elcn_evt;
769:
770: -- 5. If Elections Made Date is populated, set the Default Flag in Eligible Events to 0.
771: UPDATE hri_mb_ben_eligenrl_evnt_ct penc
772: SET penc.dflt_ind = 0
773: ,waive_dflt_ind = 0
774: ,waive_expl_ind = DECODE(penc.waive_dflt_ind,1,1,0)
775: WHERE penc.enrt_ind = 1
817: AND penq.pgm_id = copd.pgm_id
818: AND copd.oipl_id = NVL(penq.oipl_id, -1)
819: AND copd.pl_id = penq.pl_id
820: GROUP BY penq.lf_evt_ocrd_dt, copd.compobj_sk_pk ) PRTTQ,
821: hri_mb_ben_eligenrl_evnt_ct penc
822: WHERE penc.asnd_lf_evt_dt = PRTTQ.lf_evt_ocrd_dt
823: AND penc.person_id = p_person_id
824: AND penc.per_in_ler_id = p_per_in_ler_id
825: AND (PRTTQ.eee_end_dt + 1) > penc.effective_start_date
837: BEGIN
838: -- dbg('Entering ' || l_procedure);
839: --1. Update Records for which Results were ZAPPED / DE-ENROLLED
840: -- 4579556 - Changed this from Delete to Update.
841: UPDATE hri_mb_ben_eligenrl_evnt_ct penc
842: SET enrt_ind = 0
843: ,not_enrt_ind = 1
844: ,dflt_ind = 0
845: ,waive_expl_ind = 0
865: CLOSE c_eee_end_date;
866: --
867: IF (l_eee_end_dt_tbl.COUNT > 0) THEN
868: FORALL i IN l_eee_end_dt_tbl.FIRST..l_eee_end_dt_tbl.LAST SAVE EXCEPTIONS
869: UPDATE hri_mb_ben_eligenrl_evnt_ct penc
870: SET penc.effective_end_date = l_eee_end_dt_tbl(i)
871: WHERE ROWID = l_row_id_tbl(i);
872: END IF;
873: --
871: WHERE ROWID = l_row_id_tbl(i);
872: END IF;
873: --
874: --3. Merge remaining events.
875: MERGE INTO HRI_MB_BEN_ELIGENRL_EVNT_CT penc
876: USING (
877: SELECT copd.compobj_sk_pk
878: ,enpd.enrt_perd_id
879: ,penq.lf_evt_ocrd_dt
973: ,PRTTQ.waive_expl_ind
974: ,PRTTQ.waive_dflt_ind);
975:
976: -- 4. Delete Events which are VOIDED.
977: DELETE FROM HRI_MB_BEN_ELIGENRL_EVNT_CT penc
978: WHERE penc.person_id = p_person_id
979: AND penc.per_in_ler_id = p_per_in_ler_id
980: AND penc.effective_start_date > penc.effective_end_date;
981: --NULL;
1005: --
1006: IF (g_collect_oe_only = 'Y') THEN
1007: -- If Only Current Open Enrollment is being Collected,
1008: -- Delete any stray records, if present.
1009: DELETE from hri_mb_ben_eligenrl_evnt_ct
1010: WHERE person_id = p_pil_rec.person_id
1011: AND asnd_lf_evt_dt = p_pil_rec.lf_evt_ocrd_dt
1012: AND per_in_ler_id = p_pil_rec.per_in_ler_id;
1013: --
1013: --
1014: END IF;
1015: --
1016: -- Populate Benefits Eligbility and Enrollment Fact
1017: INSERT INTO HRI_MB_BEN_ELIGENRL_EVNT_CT
1018: (change_date
1019: ,effective_start_date
1020: ,effective_end_date
1021: ,compobj_sk_pk