Closure of Life Event and Beneficiary Allocation
When you close a life event for a participant, the beneficiary allocations for various plans must add up to 100%. If they don’t add up, you can’t close the life event.
You need to adjust the allocations either through the employee self-service page or through the Benefits Service Center page. If there are several records, adjusting the allocation becomes a time-consuming process. In such cases, you can use the Beneficiary Enrollment HDL loader to adjust the allocation.
You can modify the allocation as required so that it adds up to 100%. You can also make the allocation of a particular beneficiary as zero, but you need to retain that record in the .dat file.
If you don't want the close enrollment process to check the beneficiary allocations, remove the beneficiary designation setup altogether from the plan’s setup pages.
select str1 from ( /* open issues : simplify first name, last name, ben relation name */
select pil.person_id personId, 1 ordr, 'MERGE|BeneficiaryEnrollment|' ||
(SELECT person_number||'|'|| ppn.last_name||'|'||ppn.first_name||'|' ||'|'||'DFLT'
FROM per_all_people_f paf,
per_person_names_f ppn
where paf.person_id = pil.person_id
and ppn.person_id = paf.person_id
and ppn.name_type='GLOBAL'
and sysdate between paf.effective_start_date and paf.effective_end_date
)
||'|'|| lf_evt_ocrd_dt ||'|'||'|' ||
(select name
from ben_ler_f ler
where ler.ler_id = pil.ler_id
and pil.lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date )
||'|'||lf_evt_ocrd_dt str1
from ben_per_in_ler pil
where
pil.per_in_ler_id in (SELECT unique pen1.per_in_ler_id
FROM fusion.ben_pl_bnf pbn1
,fusion.ben_prtt_enrt_rslt pen1
where pbn1.prtt_enrt_rslt_id = pen1.prtt_enrt_rslt_id
and pen1.prtt_enrt_rslt_stat_cd is null
and pen1.enrt_cvg_thru_dt = trunc(to_date('31/12/4712', 'dd/mm/yyyy'))
group by pen1.prtt_enrt_rslt_id, pen1.per_in_ler_id, pbn1.prmry_cntngnt_cd
having sum(pbn1.pct_dsgd_num) <> 100 )
union all
select pen.person_id personId, 2 ordr,
'MERGE|DesignateBeneficiary'||'|'|| pen.person_id||'|'||(select pln.name from ben_pl_f pln where pen.pl_id=pln.pl_id and rownum<2)||'|'||
(select pgm.name from ben_pgm_f pgm where pen.pgm_id=pgm.pgm_id and rownum<2 ) ||'|'||
(select opt.name from ben_opt_f opt where pen.opt_id=opt.opt_id and rownum<2) ||'|'||pbn.pct_dsgd_num||'|'||'|'||'|'||pbn.prmry_cntngnt_cd||'|'||rownum||'|'||
(select person_number
from fusion.per_all_people_f ppf
where ppf.person_id = pen.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date and
rownum < 2 ) ||'|'||
nvl ((select last_name
from fusion.per_person_names_f ppn
where ppn.person_id = pbn.bnf_person_id
and ppn.name_type = 'GLOBAL'
and sysdate between ppn.effective_start_date and ppn.effective_end_date and
rownum < 2), (select name from hr_organization_units hou where hou.organization_id= pbn.organization_id and rownum <2)) ||'|'||
(select first_name
from fusion.per_person_names_f ppn
where ppn.person_id = pbn.bnf_person_id
and ppn.name_type = 'GLOBAL'
and sysdate between ppn.effective_start_date and ppn.effective_end_date and
rownum < 2) str1
from fusion.ben_pl_bnf pbn
,fusion.ben_elig_per_elctbl_chc chc
,fusion.ben_prtt_enrt_rslt pen
,fusion.ben_pl_f pln
where
pbn.elig_per_elctbl_chc_id = chc.elig_per_elctbl_chc_id
and pen.prtt_enrt_rslt_id = chc.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id in (SELECT unique pen1.prtt_enrt_rslt_id
FROM fusion.ben_pl_bnf pbn1
,fusion.ben_prtt_enrt_rslt pen1
where pbn1.prtt_enrt_rslt_id = pen1.prtt_enrt_rslt_id
and pen1.prtt_enrt_rslt_stat_cd is null
and pen1.enrt_cvg_thru_dt = trunc(to_date('31/12/4712', 'dd/mm/yyyy'))
group by pen1.prtt_enrt_rslt_id, pen1.person_id, pbn1.prmry_cntngnt_cd
having sum(pbn1.pct_dsgd_num) <> 100
)
and pen.pl_id = pln.pl_id
and chc.pl_id = pln.pl_id
and chc.pl_id = pen.pl_id
and chc.per_in_ler_id=pen.per_in_ler_id
and sysdate between pln.effective_start_date
and pln.effective_end_date
and pbn.elig_per_elctbl_chc_id=chc.elig_per_elctbl_chc_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id=chc.prtt_enrt_rslt_id
and pen.enrt_cvg_thru_dt = trunc(to_date('31/12/4712', 'dd/mm/yyyy'))
)
order by personId, ordr