Introduction
In Oracle Advanced Benefits (OAB), handling changes in dependent eligibility such as a child "aging out" on their 26th birthday is a frequent operational challenge. If left to standard system configurations, processing a Loss of Eligibility (LOE) life event can terminate coverage entirely or drop the employee into incorrect enrollment tiers. This creates significant downstream reconciliation issues for both HR and Payroll.
To maintain compliance and reduce manual work, organizations can implement an upgrade safe automation framework using a Default Enrollment Fast Formula. This technical guide explains how to design an enterprise-grade cascade system across multiple plan types (including Medical, HSA, Voluntary Life, Accidental Insurance and Critical Illness) based on the number and relationship type of remaining eligible dependents.
Business Requirement:
To handle these transitions correctly, the system must process two distinct dependent termination timelines and coordinate complex tier reductions across several coverage plans.
- End-of-Month (EOM) Horizon: Coverage for Medical, Dental, and Vision plans terminates at the end of the calendar month in which the dependent turns 26.
- Exact-Day Horizon: Coverage for HSA, Voluntary Life, Critical Illness, and Accidental Insurance terminates exactly on the dependent's 26th birthday.
Dependent types are eligible as Dependents:
- Child
- Step Child
- Adopted child
- Foster child
- Domestic partner child
Medical /Dental / Vision:
- When processing Life Events, the option should be automatically changed or defaulted to next lower option for plans (Medical plan/ Dental plan / Vision Plan Types) based on eligibility.
- If employee is enrolled in “Employee + Family” then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 2 or more eligible dependents, the enrollment would remain in “Employee + Family” option itself
- If employee is enrolled in “Employee + Family” and has 2 dependents enrolled (one is spouse, one is child), then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee + Spouse” option if remaining eligible dependent has relationship type “spouse” OR “Employee + Child(ren)” option If remaining eligible dependent has relationship type “Child”/”Step child”/”Adopted Child”/”Foster child”/” Domestic partner child”
- If employee is enrolled in “Employee + Spouse” and has spouse enrolled, then spouse loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
- If employee is enrolled in “Employee + Child(ren)” and has 1 child enrolled, then child loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
- If employee is enrolled in “Employee + Child(ren)” and has more than 2 dependents then one of the dependent loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee + Child(ren)” option
- If employee is enrolled in “Employee + Child(ren)” and 1 dependent then the dependent loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in in “Employee Only” option
- If employee is enrolled in “Employee Only” and has more than 2 dependents but has not enrolled any dependent, then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee Only” option
Health Savings Account:
The Health Saving Account (HSA) plan does not enroll dependents. The options which are related to Plan would get enabled based on the number of dependents available for employee.
NOTE: HSA plan needs to be validated only when CDHP is elected or else this might be always Waive. During Waive. No need to perform anything.
When processing Life Events, the option should be automatically changed or defaulted to next lower option for the Health Savings Account plan based on eligibility.
- If employee is enrolled in “Health Savings Account Contribution - Family” then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 1 or more eligible dependents, the enrollment would remain in “Health Savings Account Contribution - Family” option itself
- If employee is enrolled in “Health Savings Account Contribution - Family” and has 1 dependent, the the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Health Savings Account Contribution - Individual” option
- If employee is enrolled in “Health Savings Account Contribution - Individual” and has 1 or more dependents but has not enrolled any dependent, then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Health Savings Account Contribution - Individual” option
Voluntary Life Dependent Children Insurance:
The Voluntary Life Dependent Children Insurance do enroll dependents. The options which are related to plan would get enabled based on the number/type of dependents available for employee.
When processing Life Events, the option should be automatically changed or defaulted to next lower option for Voluntary Spouse Life Insurance and Voluntary Spouse AD&D Insurance Plans based on eligibility.
- If employee is enrolled in any coverage option of Voluntary Child Life Insurance or Voluntary Child AD&D Insurance, then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 1 or more eligible dependents, the enrollment would remain in their original option itself
- If employee is enrolled in “Voluntary Child Life Insurance” or “Voluntary Child AD&D Insurance then their child loses eligibility, on processing the Life events (Loss of Eligibility), the enrollment should automatically enroll in “Waive” option itself
Critical Illness:
- When processing Life Events, the option should be automatically changed or defaulted to next lower option for 3 Critical Illness plans based on eligibility.
- If employee is enrolled in any coverage option of Critical Illness Insurance, then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 1 or more eligible dependents, the enrollment would remain in their original option itself
- If employee is enrolled in Critical Illness Insurance then their child loses eligibility, on processing the Life events (Loss of Eligibility), the enrollment should automatically enroll in “Waive” option itself Note: There are 3 critical illness plans - Critical Illness, Critical Illness - Employee, Critical Illness – Spouse.
Accidental Insurance:
When processing Life Events, the option should be automatically changed or defaulted to next lower option for Accidental Insurance Plans based on eligibility.
- If employee is enrolled in “Employee + Family” then one of the dependents loses eligibility and on processing the Life events (Loss of Eligibility), the employee still has 2 or more eligible dependents, the enrollment would remain in “Employee + Family” option itself
- If employee is enrolled in “Employee + Family” and has 2 dependents enrolled (one is spouse, one is child), then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee + Spouse” option if remaining eligible dependent has relationship type “spouse” OR “Employee + Child(ren)” option If remaining eligible dependent has relationship type “Child”/”Step child”/”Adopted Child”/”Foster child”/” Domestic partner child”
- If employee is enrolled in “Employee + Spouse” and has spouse enrolled, then spouse loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
- If employee is enrolled in “Employee + Child(ren)” and has 1 child enrolled, then child loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in “Employee Only” option
- If employee is enrolled in “Employee + Child(ren)” and has more than 2 dependents then one of the dependents loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee + Child(ren)” option
- If employee is enrolled in “Employee + Child(ren)” and 1 dependent then the dependent loses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should automatically enroll in in “Employee Only” option
- If employee is enrolled in “Employee Only” and has more than 2 dependents but has not enrolled any dependent, then one of the dependent losses eligibility, on processing the Life events (Loss of Eligibility) the enrollment should remain in “Employee Only” option
Development Information:
To Handle this requirement, I've created 5 Fast Formulas for different plans and 2 value Sets to check the dependent's eligibility and are as follows.
Value Sets:
- ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS: This value set is responsible to fetch the count of Eligible dependents available for an employee.
SQL Query:
select
to_char(bed.dpnt_person_id)||'|'||pcrf.contact_type||'|'||bplhcf.registered_disabled_flag
from
per_contact_relships_f pcrf,
ben_elig_dpnt bed,
per_all_people_f papf,
ben_per_le_habits_cov_f bplhcf
where
1=1
and pcrf.contact_person_id = bed.dpnt_person_id
and papf.person_id = pcrf.person_id
and pcrf.contact_type in ('S','C','A','O','T','R')
and pcrf.person_id = :{PARAMETER.P_PERSON_ID}
and pcrf.contact_person_id = bplhcf.person_id(+)
and :{PARAMETER.P_EFFECTIVE_DATE} between bed.elig_strt_dt and bed.elig_thru_dt
and :{PARAMETER.P_EFFECTIVE_DATE} between pcrf.effective_start_date and pcrf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between bplhcf.effective_start_date(+) and bplhcf.effective_end_date(+)
and bed.dpnt_person_id > :{PARAMETER.P_LIMITER}
order by
bed.dpnt_person_id
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Value Set Format:
Value Set Code : ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS
Description :
Module : Benefits
Validation Type : Table
Value Data Type : Character
FROM Clause : per_contact_relships_f pcrf, ben_elig_dpnt bed, per_all_people_f papf, ben_per_le_habits_cov_f bplhcf
Value Column Name : to_char(bed.dpnt_person_id)||'|'||pcrf.contact_type||'|'||bplhcf.registered_disabled_flag
ID Column Name : to_char(bed.dpnt_person_id)||'|'||pcrf.contact_type||'|'||bplhcf.registered_disabled_flag
WHERE Clause : 1=1
and pcrf.contact_person_id = bed.dpnt_person_id
and papf.person_id = pcrf.person_id
and pcrf.contact_type in ('S','C','A','O','T','R')
and pcrf.person_id = :{PARAMETER.P_PERSON_ID}
and pcrf.contact_person_id = bplhcf.person_id(+)
and :{PARAMETER.P_EFFECTIVE_DATE} between bed.elig_strt_dt and bed.elig_thru_dt
and :{PARAMETER.P_EFFECTIVE_DATE} between pcrf.effective_start_date and pcrf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between papf.effective_start_date and papf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between bplhcf.effective_start_date(+) and bplhcf.effective_end_date(+)
and bed.dpnt_person_id > :{PARAMETER.P_LIMITER}
ORDER BY Clause : bed.dpnt_person_id
- ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS: This value set is responsible to fetch the count of enrolled dependents available for an employee.
SQL Query:
select
to_char(becp.dpnt_person_id)
from
ben_prtt_enrt_rslt bper,
ben_pl_f bpf,
ben_opt_f bof,
ben_ler_f blf,
ben_per_in_ler bpil,
ben_pl_typ_f bptf,
ben_elig_cvrd_dpnt becp
where
1=1
and bper.pl_id = bpf.pl_id(+)
and bper.opt_id = bof.opt_id(+)
and bper.pl_typ_id = bptf.pl_typ_id
and bper.ler_id = blf.ler_id
and bper.ler_id = blf.ler_id
and bper.ler_id = blf.ler_id
and blf.ler_id = bpil.ler_id
and bper.per_in_ler_id = bpil.per_in_ler_id
and bper.prtt_enrt_rslt_id = becp.prtt_enrt_rslt_id
and :{PARAMETER.P_EFFECTIVE_DATE} between bpf.effective_start_date(+) and bpf.effective_end_date(+)
and :{PARAMETER.P_EFFECTIVE_DATE} between bof.effective_start_date(+) and bof.effective_end_date(+)
and :{PARAMETER.P_EFFECTIVE_DATE} between bptf.effective_start_date and bptf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between blf.effective_start_date and blf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between becp.cvg_strt_dt and becp.cvg_thru_dt
and bpil.per_in_ler_stat_cd in ('STRTD','PROCD')
and bper.person_id = :{PARAMETER.P_PERSON_ID}
and bper.pl_typ_id = :{PARAMETER.P_PLAN_TYPE_ID}
and bper.pl_id = :{PARAMETER.P_PLAN_ID}
and becp.dpnt_person_id > :{PARAMETER.P_LIMITER}
order by
becp.dpnt_person_id
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Value Set Format:
Value Set Code : ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS
Description :
Module : Benefits
Validation Type : Table
Value Data Type : Character
FROM Clause : ben_prtt_enrt_rslt bper, ben_pl_f bpf, ben_opt_f bof, ben_ler_f blf, ben_per_in_ler bpil, ben_pl_typ_f bptf, ben_elig_cvrd_dpnt becp
Value Column Name : to_char(becp.dpnt_person_id)
ID Column Name : to_char(becp.dpnt_person_id)
WHERE Clause : 1=1
and bper.pl_id = bpf.pl_id(+)
and bper.opt_id = bof.opt_id(+)
and bper.pl_typ_id = bptf.pl_typ_id
and bper.ler_id = blf.ler_id
and bper.ler_id = blf.ler_id
and bper.ler_id = blf.ler_id
and blf.ler_id = bpil.ler_id
and bper.per_in_ler_id = bpil.per_in_ler_id
and bper.prtt_enrt_rslt_id = becp.prtt_enrt_rslt_id
and :{PARAMETER.P_EFFECTIVE_DATE} between bpf.effective_start_date(+) and bpf.effective_end_date(+)
and :{PARAMETER.P_EFFECTIVE_DATE} between bof.effective_start_date(+) and bof.effective_end_date(+)
and :{PARAMETER.P_EFFECTIVE_DATE} between bptf.effective_start_date and bptf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between blf.effective_start_date and blf.effective_end_date
and :{PARAMETER.P_EFFECTIVE_DATE} between becp.cvg_strt_dt and becp.cvg_thru_dt
and bpil.per_in_ler_stat_cd in ('STRTD','PROCD')
and bper.person_id = :{PARAMETER.P_PERSON_ID}
and bper.pl_typ_id = :{PARAMETER.P_PLAN_TYPE_ID}
and bper.pl_id = :{PARAMETER.P_PLAN_ID}
and becp.dpnt_person_id > :{PARAMETER.P_LIMITER}
ORDER BY Clause : becp.dpnt_person_id
Fast Formulas:
- ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Medical, Dental and Vision Plans.
/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:
====================================================================================
Change History:
Name Date Version Comments
Oracle 2025-06-17 1.0 Created.
------------------------------------------------------------------------------------
***********************************************************************************/
/*@section - Default for DBIs*/
default for BEN_OPT_NAME is 'XX'
default for BEN_PLN_NAME is 'XX'
default for BEN_PTP_NAME is 'XX'
default for PER_PER_DATE_OF_BIRTH is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME is 'XX'
/* @Section - input variables */
/* No Inputs*/
/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL = 'N'
l_cur_option_name = BEN_OPT_NAME
l_cur_plan_name = BEN_PLN_NAME
l_plan_type = BEN_PTP_NAME
l_plan_enrolled = 'N'
l_person_id = get_context(PERSON_ID,0)
l_plan_type_id = get_context(PL_TYP_ID,0)
l_plan_id = get_context(PL_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date = add_days(l_effective_date, -1)
l_enrolled_option = 'N'
l_spouse_flag = 'N'
CARRY_FORWARD_ELIG_DPNT = 'CFWP'
log = ess_log_write('--------------ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)
/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
(
if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and (BEN_EXT_RSLT_PTP_NAME[l_index] = 'Medical' or BEN_EXT_RSLT_PTP_NAME[l_index] = 'Dental' or BEN_EXT_RSLT_PTP_NAME[l_index] = 'Vision'))then
(
l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
l_plan_enrolled = BEN_EXT_RSLT_PL_NAME[l_index]
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
if l_plan_enrolled = l_cur_plan_name then
(
i=1
limiter = 0
l_enrolled_data = ' '
l_enrolled_dependents_count = 0
l_enrolled_flag = 'N'
while isnull(l_enrolled_data) = 'Y' loop
(
l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
if isnull(l_enrolled_data) = 'Y' then
(
l_enrolled_flag = 'Y'
l_enrolled_dpnt_per_id[i] = l_enrolled_data
l_enrolled_dependents_count = l_enrolled_dependents_count + 1
limiter = to_number(l_enrolled_data)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
i = i + 1
)
)
j = 1
limiter = 0
l_eligible_data = ' '
l_eligible_flag = 'N'
while isnull(l_eligible_data) = 'Y' loop
(
l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
if isnull(l_eligible_data) = 'Y' then
(
l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
(
l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
if (((l_dpnt_age < 26 or (l_dpnt_age >= 26 and l_dpnt_age < 27 and (to_number(to_char(l_effective_date,'MM')) = to_number(to_char(PER_PER_DATE_OF_BIRTH,'MM'))) and last_day(l_effective_date)!= l_effective_date )) and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
(
l_eligible_flag = 'Y'
l_eligible_dpnt_per_id[j] = l_dpnt_person_id
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
j = j + 1
)
)
if (l_dpnt_contact_type = 'S' and l_plan_enrolled != 'EE+Child') then
(
l_spouse_flag = 'Y'
)
limiter = to_number(l_dpnt_person_id)
)
)
i = 1
l_loss_of_eligibility = 'N'
l_dpnt_loss_count = 0
if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
(
while( l_enrolled_dpnt_per_id.exists(i)) loop
(
j = 1
while(l_eligible_dpnt_per_id.exists(j)) loop
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
(
exit
)
j = j + 1
if j > l_eligible_dpnt_per_id.count then
(
l_loss_of_eligibility = 'Y'
l_dpnt_loss_count = l_dpnt_loss_count + 1
)
)
i = i + 1
)
)
else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
(
l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))
l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
if l_cur_dependent_count < 0 then
(
l_cur_dependent_count = 0
)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_cur_dependent_count ='+to_char(l_cur_dependent_count))
if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name)
if isnull(l_cur_dependent_count) = 'Y' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
if l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL1'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE Only' and l_cur_dependent_count = 0 and l_enrolled_flag = 'Y' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE Only' and l_enrolled_flag = 'N' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Waive Coverage' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
if (l_cur_option_name = 'EE Only' and (l_cur_dependent_count = 0 or l_enrolled_flag = 'N')) then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
)
l_index = l_index + 1
)
)
log = ess_log_write('ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_BEN_MED_DEN_VIS_PLN_DFLT_ENRT_FF Formula Ends--------------')
/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT
- ORA_BEN_HSA_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for HSA Plan.
/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_BEN_HSA_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:
====================================================================================
Change History:
Name Date Version Comments
Oracle 2025-06-17 1.0 Created.
------------------------------------------------------------------------------------
***********************************************************************************/
/*@section - Default for DBIs*/
default for BEN_OPT_NAME is 'XX'
default for BEN_PLN_NAME is 'XX'
default for BEN_PTP_NAME is 'XX'
default for PER_PER_DATE_OF_BIRTH is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME is 'XX'
/* @Section - input variables */
/* No Inputs*/
/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL = 'N'
l_cur_option_name = BEN_OPT_NAME
l_cur_plan_name = BEN_PLN_NAME
l_plan_type = BEN_PTP_NAME
l_plan_enrolled = 'N'
l_person_id = get_context(PERSON_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date = add_days(l_effective_date, -1)
l_enrolled_option = 'N'
l_spouse_flag = 'N'
CARRY_FORWARD_ELIG_DPNT = 'CFWP'
log = ess_log_write('--------------ORA_BEN_HSA_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)
/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
a_index = BEN_EXT_RSLT_PL_NAME.first(-1)
l_plan_counter = 0
while(BEN_EXT_RSLT_PL_NAME.exists(a_index))loop
(
if (BEN_EXT_RSLT_PL_NAME[a_index] = 'UHC Consumer Driven Health Plan (CDHP)') then
(
l_plan_counter = 1
exit
)
a_index = a_index + 1
)
if (l_plan_counter = 1) then
(
l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
(
l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
l_plan_enrolled = BEN_EXT_RSLT_PL_NAME[l_index]
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'FSA / HSA' and l_plan_enrolled = 'HSA')then
(
j = 1
limiter = 0
l_eligible_data = ' '
l_eligible_flag = 'N'
while isnull(l_eligible_data) = 'Y' loop
(
l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
if isnull(l_eligible_data) = 'Y' then
(
l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
(
l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
(
l_eligible_flag = 'Y'
l_eligible_dpnt_per_id[j] = l_dpnt_person_id
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
j = j + 1
)
)
if (l_dpnt_contact_type = 'S') then
(
l_spouse_flag = 'Y'
)
limiter = to_number(l_dpnt_person_id)
)
)
i = 1
l_elig_dependent_count = 0
if l_eligible_flag = 'Y' then
(
while( l_eligible_dpnt_per_id.exists(i)) loop
(
l_elig_dependent_count = l_eligible_dpnt_per_id.count
i = i + 1
)
)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_elig_dependent_count ='+to_char(l_elig_dependent_count))
if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name)
if isnull(l_elig_dependent_count) = 'Y' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
if l_cur_option_name = 'Family Only Coverage' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL1'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Self-Only Coverage' and l_elig_dependent_count = 0 then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Per Pay Period Amount - EE+Family' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Per Pay Period Amount - EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Per Pay Period Amount - EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL7'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Per Pay Period Amount - Self Only' and l_elig_dependent_count = 0 then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL8'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Waive Coverage' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' and l_enrolled_option not like 'Per Pay Period Amount%' then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
if (l_cur_option_name = 'Self-Only Coverage' and l_elig_dependent_count = 0) then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL10'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if (l_cur_option_name = 'EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y') then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL11'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if (l_cur_option_name = 'EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N') then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL12'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if (l_cur_option_name = 'Family Only Coverage' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y') then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL12'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
)
else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' and l_enrolled_option like 'Per Pay Period Amount%' then
(
if (l_cur_option_name = 'Per Pay Period Amount - Self Only' and l_elig_dependent_count = 0) then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL13'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if (l_cur_option_name = 'Per Pay Period Amount - EE+Spouse' and l_elig_dependent_count = 1 and l_spouse_flag = 'Y') then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL14'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if (l_cur_option_name = 'Per Pay Period Amount - EE+Child' and l_elig_dependent_count >= 1 and l_spouse_flag = 'N') then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL15'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if (l_cur_option_name = 'Per Pay Period Amount - EE+Family' and l_elig_dependent_count >= 2 and l_spouse_flag = 'Y') then
(
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL15'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
l_index = l_index + 1
)
)
)
log = ess_log_write('ORA_BEN_HSA_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_BEN_HSA_PLN_DFLT_ENRT_FF Formula Ends--------------')
/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT
- ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Voluntary Life Plan.
/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:
====================================================================================
Change History:
Name Date Version Comments
Oracle 2025-06-17 1.0 Created.
------------------------------------------------------------------------------------
***********************************************************************************/
/*@section - Default for DBIs*/
default for BEN_OPT_NAME is 'XX'
default for BEN_PLN_NAME is 'XX'
default for BEN_PTP_NAME is 'XX'
default for PER_PER_DATE_OF_BIRTH is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME is 'XX'
/* @Section - input variables */
/* No Inputs*/
/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL = 'N'
l_cur_option_name = BEN_OPT_NAME
l_cur_plan_name = BEN_PLN_NAME
l_plan_type = BEN_PTP_NAME
l_plan_enrolled = 'N'
l_person_id = get_context(PERSON_ID,0)
l_plan_type_id = get_context(PL_TYP_ID,0)
l_plan_id = get_context(PL_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date = add_days(l_effective_date, -1)
l_enrolled_option = 'N'
l_spouse_flag = 'N'
CARRY_FORWARD_ELIG_DPNT = 'CFWP'
log = ess_log_write('--------------ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)
/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
(
l_plan_enrolled = BEN_EXT_RSLT_PL_NAME[l_index]
l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'Optional Life' and l_plan_enrolled = 'Voluntary Life - Dependent Children')then
(
i=1
limiter = 0
l_enrolled_data = ' '
l_enrolled_dependents_count = 0
l_enrolled_flag = 'N'
while isnull(l_enrolled_data) = 'Y' loop
(
l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
if isnull(l_enrolled_data) = 'Y' then
(
l_enrolled_flag = 'Y'
l_enrolled_dpnt_per_id[i] = l_enrolled_data
l_enrolled_dependents_count = l_enrolled_dependents_count + 1
limiter = to_number(l_enrolled_data)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
i = i + 1
)
)
j = 1
limiter = 0
l_eligible_data = ' '
l_eligible_flag = 'N'
while isnull(l_eligible_data) = 'Y' loop
(
l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
if isnull(l_eligible_data) = 'Y' then
(
l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
(
l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
(
l_eligible_flag = 'Y'
l_eligible_dpnt_per_id[j] = l_dpnt_person_id
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
j = j + 1
)
)
if l_dpnt_contact_type = 'S' then
(
l_spouse_flag = 'Y'
l_spouse_dep_id = l_dpnt_person_id
)
limiter = to_number(l_dpnt_person_id)
)
)
i = 1
l_loss_of_eligibility = 'N'
l_dpnt_loss_count = 0
if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
(
while( l_enrolled_dpnt_per_id.exists(i)) loop
(
j = 1
while(l_eligible_dpnt_per_id.exists(j)) loop
(
if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
(
if l_enrolled_dpnt_per_id[i] = l_spouse_dep_id then
(
l_dpnt_loss_count = l_dpnt_loss_count + 1
)
else
(
exit
)
)
j = j + 1
if j > l_eligible_dpnt_per_id.count then
(
l_loss_of_eligibility = 'Y'
l_dpnt_loss_count = l_dpnt_loss_count + 1
)
)
i = i + 1
)
)
else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
(
l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))
*/
l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
if l_cur_dependent_count < 0 then
(
l_cur_dependent_count = 0
)
if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
(
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name)
if isnull(l_cur_dependent_count) = 'Y' then
(
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
if (l_cur_option_name = 'Elect' and l_cur_dependent_count >= 1) then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Waive Coverage' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
else if l_plan_enrolled = l_cur_plan_name then
(
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
if (l_cur_option_name = 'Elect' and l_cur_dependent_count >= 1) then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if (l_cur_option_name = 'Waive Coverage' and l_cur_dependent_count = 0) then
(
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
l_index = l_index + 1
)
)
log = ess_log_write('ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_BEN_VOL_CHILD_PLN_DFLT_ENRT_FF Formula Ends--------------')
/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT
- ORA_CRT_ILL_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Critical Illness Plan.
/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_CRT_ILL_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:
====================================================================================
Change History:
Name Date Version Comments
Oracle 2025-06-17 1.0 Created.
------------------------------------------------------------------------------------
***********************************************************************************/
/*@section - Default for DBIs*/
default for BEN_OPT_NAME is 'XX'
default for BEN_PLN_NAME is 'XX'
default for BEN_PTP_NAME is 'XX'
default for PER_PER_DATE_OF_BIRTH is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME is 'XX'
/* @Section - input variables */
/* No Inputs*/
/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL = 'N'
l_cur_option_name = BEN_OPT_NAME
l_cur_plan_name = BEN_PLN_NAME
l_plan_type = BEN_PTP_NAME
l_plan_enrolled = 'N'
l_person_id = get_context(PERSON_ID,0)
l_plan_type_id = get_context(PL_TYP_ID,0)
l_plan_id = get_context(PL_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date = add_days(l_effective_date, -1)
l_enrolled_option = 'N'
l_spouse_flag = 'N'
CARRY_FORWARD_ELIG_DPNT = 'CFWP'
log = ess_log_write('--------------ORA_CRT_ILL_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)
/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
(
l_plan_enrolled = BEN_EXT_RSLT_PL_NAME[l_index]
l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'Misc Plans' and l_plan_enrolled = 'Critical Illness')then
(
i=1
limiter = 0
l_enrolled_data = ' '
l_enrolled_dependents_count = 0
l_enrolled_flag = 'N'
while isnull(l_enrolled_data) = 'Y' loop
(
l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
if isnull(l_enrolled_data) = 'Y' then
(
l_enrolled_flag = 'Y'
l_enrolled_dpnt_per_id[i] = l_enrolled_data
l_enrolled_dependents_count = l_enrolled_dependents_count + 1
limiter = to_number(l_enrolled_data)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
i = i + 1
)
)
j = 1
limiter = 0
l_eligible_data = ' '
l_eligible_flag = 'N'
while isnull(l_eligible_data) = 'Y' loop
(
l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
if isnull(l_eligible_data) = 'Y' then
(
l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
(
l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
(
l_eligible_flag = 'Y'
l_eligible_dpnt_per_id[j] = l_dpnt_person_id
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
j = j + 1
)
)
if (l_dpnt_contact_type = 'S' and l_plan_enrolled != 'EE+Child') then
(
l_spouse_flag = 'Y'
)
limiter = to_number(l_dpnt_person_id)
)
)
i = 1
l_loss_of_eligibility = 'N'
l_dpnt_loss_count = 0
if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
(
while( l_enrolled_dpnt_per_id.exists(i)) loop
(
j = 1
while(l_eligible_dpnt_per_id.exists(j)) loop
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
(
exit
)
j = j + 1
if j > l_eligible_dpnt_per_id.count then
(
l_loss_of_eligibility = 'Y'
l_dpnt_loss_count = l_dpnt_loss_count + 1
)
)
i = i + 1
)
)
else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
(
l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))
l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
if l_cur_dependent_count < 0 then
(
l_cur_dependent_count = 0
)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_cur_dependent_count ='+to_char(l_cur_dependent_count))
if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name)
if isnull(l_cur_dependent_count) = 'Y' then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE Only' and l_cur_dependent_count = 0 and l_enrolled_flag = 'Y' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE Only' and l_enrolled_flag = 'N' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Waive Coverage' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
if (l_cur_option_name = 'EE Only' and (l_cur_dependent_count = 0 or l_enrolled_flag = 'N')) then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
l_index = l_index + 1
)
)
log = ess_log_write('ORA_CRT_ILL_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_CRT_ILL_PLN_DFLT_ENRT_FF Formula Ends--------------')
/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT
- ORA_ACC_INS_PLN_DFLT_ENRT_FF: This formula is used to determine the loss of eligibility and defaults to next lower option for Accidental Insurance Plan.
/***********************************************************************************
------------------------------------------------------------------------------------
FORMULA NAME: ORA_ACC_INS_PLN_DFLT_ENRT_FF
FORMULA TYPE: Default Enrollment Formula
DESCRIPTION:
====================================================================================
Change History:
Name Date Version Comments
Oracle 2025-06-17 1.0 Created.
------------------------------------------------------------------------------------
***********************************************************************************/
/*@section - Default for DBIs*/
default for BEN_OPT_NAME is 'XX'
default for BEN_PLN_NAME is 'XX'
default for BEN_PTP_NAME is 'XX'
default for PER_PER_DATE_OF_BIRTH is '1901/01/01'(date)
default_data_value for BEN_EXT_RSLT_PL_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_PTP_NAME is 'XX'
default_data_value for BEN_EXT_RSLT_OPT_NAME is 'XX'
/* @Section - input variables */
/* No Inputs*/
/*@section - Local variable declaration abd initialization*/
AUTO_DFLT_VAL = 'N'
l_cur_option_name = BEN_OPT_NAME
l_cur_plan_name = BEN_PLN_NAME
l_plan_type = BEN_PTP_NAME
l_plan_enrolled = 'N'
l_person_id = get_context(PERSON_ID,0)
l_plan_type_id = get_context(PL_TYP_ID,0)
l_plan_id = get_context(PL_ID,0)
l_effective_date = get_context(EFFECTIVE_DATE,'1901/01/01'(date))
l_enrt_check_date = add_days(l_effective_date, -1)
l_enrolled_option = 'N'
l_spouse_flag = 'N'
CARRY_FORWARD_ELIG_DPNT = 'CFWP'
log = ess_log_write('--------------ORA_ACC_INS_PLN_DFLT_ENRT_FF Formula Begins--------------')
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrt_check_date ='+to_char(l_enrt_check_date))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_person_id ='+to_char(l_person_id))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_type_id ='+to_char(l_plan_type_id))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_id ='+to_char(l_plan_id))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_effective_date ='+to_char(l_effective_date))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_cur_plan_name ='+l_cur_plan_name)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_cur_option_name ='+l_cur_option_name)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_type ='+l_plan_type)
/*@section - Business Logic and Interface*/
change_contexts(EFFECTIVE_DATE = l_enrt_check_date)
(
l_index = BEN_EXT_RSLT_PTP_NAME.first(-1)
while(BEN_EXT_RSLT_PTP_NAME.exists(l_index))loop
(
l_plan_enrolled = BEN_EXT_RSLT_PL_NAME[l_index]
l_enrolled_option = BEN_EXT_RSLT_OPT_NAME[l_index]
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled ='+l_plan_enrolled)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_option ='+l_enrolled_option)
if(BEN_EXT_RSLT_PTP_NAME[l_index] = l_plan_type and BEN_EXT_RSLT_PTP_NAME[l_index] = 'Misc Plans' and l_plan_enrolled = 'Accident Insurance')then
(
i=1
limiter = 0
l_enrolled_data = ' '
l_enrolled_dependents_count = 0
l_enrolled_flag = 'N'
while isnull(l_enrolled_data) = 'Y' loop
(
l_enrolled_data = get_value_set('ORA_BEN_ENROLLED_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_enrt_check_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''|| '|P_PLAN_TYPE_ID='''||to_char(l_plan_type_id)||''''|| '|P_PLAN_ID='''||to_char(l_plan_id)||'''' || '|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_data ='+l_enrolled_data)
if isnull(l_enrolled_data) = 'Y' then
(
l_enrolled_flag = 'Y'
l_enrolled_dpnt_per_id[i] = l_enrolled_data
l_enrolled_dependents_count = l_enrolled_dependents_count + 1
limiter = to_number(l_enrolled_data)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_dpnt_per_id['+to_char(i)+'] ='+l_enrolled_dpnt_per_id[i])
i = i + 1
)
)
j = 1
limiter = 0
l_eligible_data = ' '
l_eligible_flag = 'N'
while isnull(l_eligible_data) = 'Y' loop
(
l_eligible_data = get_value_set('ORA_BEN_ELIGIBLE_DPNT_PERSON_ID_VS', '|=P_EFFECTIVE_DATE='''||to_char(l_effective_date,'YYYY/MM/DD')||''''|| '|P_PERSON_ID='''||to_char(l_person_id)||''''||'|P_LIMITER='''||to_char(limiter)||'''')
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_eligible_data ='+l_eligible_data)
if isnull(l_eligible_data) = 'Y' then
(
l_dpnt_person_id = substr(l_eligible_data,1,instr(l_eligible_data,'|')-1)
l_dpnt_contact_type = substr(l_eligible_data,instr(l_eligible_data,'|',1,1)+1,instr(l_eligible_data,'|',1,2)-instr(l_eligible_data,'|',1,1)-1)
l_disabled_flag = substr(l_eligible_data,instr(l_eligible_data,'|',1,2)+1,length(l_eligible_data))
change_contexts(PERSON_ID = to_number(l_dpnt_person_id))
(
l_dpnt_age = months_between(l_effective_date,PER_PER_DATE_OF_BIRTH)/12
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF PER_PER_DATE_OF_BIRTH ='+to_char(PER_PER_DATE_OF_BIRTH))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_dpnt_age ='+to_char(l_dpnt_age))
if ((l_dpnt_age < 26 and l_dpnt_contact_type <> 'S') or l_dpnt_contact_type = 'S' or ((l_disabled_flag = 'P' or l_disabled_flag = 'Y' or l_disabled_flag = 'F')and l_dpnt_contact_type <> 'S')) then
(
l_eligible_flag = 'Y'
l_eligible_dpnt_per_id[j] = l_dpnt_person_id
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
j = j + 1
)
)
if (l_dpnt_contact_type = 'S' and l_plan_enrolled != 'EE+Child') then
(
l_spouse_flag = 'Y'
)
limiter = to_number(l_dpnt_person_id)
)
)
i = 1
l_loss_of_eligibility = 'N'
l_dpnt_loss_count = 0
if l_eligible_flag = 'Y' and l_enrolled_flag = 'Y' then
(
while( l_enrolled_dpnt_per_id.exists(i)) loop
(
j = 1
while(l_eligible_dpnt_per_id.exists(j)) loop
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_eligible_dpnt_per_id['+to_char(j)+'] ='+l_eligible_dpnt_per_id[j])
if l_enrolled_dpnt_per_id[i] = l_eligible_dpnt_per_id[j] then
(
exit
)
j = j + 1
if j > l_eligible_dpnt_per_id.count then
(
l_loss_of_eligibility = 'Y'
l_dpnt_loss_count = l_dpnt_loss_count + 1
)
)
i = i + 1
)
)
else if l_enrolled_flag = 'Y' and l_eligible_flag = 'N' then
(
l_dpnt_loss_count = l_enrolled_dpnt_per_id.count
)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_loss_of_eligibility ='+l_loss_of_eligibility)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_dpnt_loss_count ='+to_char(l_dpnt_loss_count))
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_enrolled_dependents_count ='+to_char(l_enrolled_dependents_count))
l_cur_dependent_count = l_enrolled_dependents_count - l_dpnt_loss_count
if l_cur_dependent_count < 0 then
(
l_cur_dependent_count = 0
)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_cur_dependent_count ='+to_char(l_cur_dependent_count))
if l_plan_enrolled = l_cur_plan_name and l_enrolled_option = l_cur_option_name then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes'+l_plan_enrolled + l_cur_option_name)
if isnull(l_cur_dependent_count) = 'Y' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes1'+l_plan_enrolled + l_cur_option_name)
if l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL1'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL2'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL3'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE Only' and l_cur_dependent_count = 0 and l_enrolled_flag = 'Y' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE Only' and l_enrolled_flag = 'N' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Waive Coverage' then
(
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
else if l_plan_enrolled = l_cur_plan_name and l_enrolled_option != 'Waive Coverage' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =comes2'+l_plan_enrolled + l_cur_option_name)
if (l_cur_option_name = 'EE Only' and (l_cur_dependent_count = 0 or l_enrolled_flag = 'N')) then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL4'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Spouse' and l_cur_dependent_count = 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL5'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'EE+Child' and l_cur_dependent_count >= 1 and l_enrolled_flag = 'Y' and l_spouse_flag = 'N' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
else if l_cur_option_name = 'Family' and l_cur_dependent_count >= 2 and l_enrolled_flag = 'Y' and l_spouse_flag = 'Y' then
(
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF l_plan_enrolled =AUTO_DFLT_VAL6'+AUTO_DFLT_VAL )
AUTO_DFLT_VAL = 'Y'
exit
)
)
)
l_index = l_index + 1
)
)
log = ess_log_write('ORA_ACC_INS_PLN_DFLT_ENRT_FF AUTO_DFLT_VAL ='+AUTO_DFLT_VAL)
log = ess_log_write('--------------ORA_ACC_INS_PLN_DFLT_ENRT_FF Formula Ends--------------')
/* @Section - return variables */
return AUTO_DFLT_VAL,CARRY_FORWARD_ELIG_DPNT
Configuration:
Navigation for Plan Setup:
Benefits Administration > Plan Configuration > Programs > Search > Select Program Name > Enrollment > Select Plan Type > Life Events > Select Life Event (Loss of Eligibility)
- Configure the Loss of Eligibility life event at the Program level. Ensure that the session effective date is set to 1/1/1951. Navigate to the Enrollment train stop, select the Program Standard line underneath the program hierarchy, and then Add Life Event: Loss of Eligibility.
- Configure the following fields for the loss of eligibility life event on the program level and save it.
- Next, configure the Enrollment Period for Plan – this is the list of all plans in which the coverage for the dependent needs to end on the day they turn 26, rather than ending coverage at the end of the month. Underneath the section you just added the loss of eligibility life event on the program, please add the following configuration.
- Next, navigate to the Benefit Plan: Benefits Administration > Plan Configuration > Plans > Effective As-Of Date [1/1/1951] > Search > Select Plan Name > Enrollment Train Stop > Complete configuration in the screenshot below for the Waive Coverage option.
Similarly, do the same configuration for rest of the options in the plan and repeat the same steps for different plans.
Scheduling the Reevaluate Designee Eligibility process:
- Login as the account that owns all scheduled process – this should not be a personal account.
- Navigator > Tools > Scheduled Processes > Schedule New Process > Type “Reevaluate Designee Eligibility” > select continue
- Parameters tab: Ensure the Loss of Eligibility Tracking Life Event field is “Remain in started status”.
- Optional: Set Program Name so that it’s only checking benefits eligible employees.
- Select Advanced at the top
- Select the Schedule tab
- Run: Using a schedule
- Frequency: Daily, set your start & end times
- Click Submit.
Testing:
- Change dependents DOB to make them 26 as of the date you want to test.
- Run Reevaluate Designee Eligibility Process with the following parameters:
- Effective Date: Dependents 26th Birthday
- Validate: Save
- Person Name
- Loss of Eligibility Tracking Life Event: Remain in Started Status
Click Search until all 3 processes have completed successfully. Review Log for Evaluate Life Event Participation Process.
After the process is completed, you can review the changes by navigating to Benefits Administration-> Enrollment -> search for an Employee -> select effective date.
Change your effective date to the day before the life event to see enrollments prior to loss of eligibility. Change your date to the first of the month following the life event to review enrollments after life event processed. Validate that enrollments are correct as of the first of the next month.
Top comments (0)