This formula calculates the maximum allowed contribution to an HSA (Health Savings Account) for an employee over 55, depending on which quarter of the year the employee is in. It adjusts contributions based on the current year’s quarter and the employee’s enrollment status for HSA Saver plans, ensuring that the upper limit of contributions is not exceeded.
/*****************************************************************************
FORMULA NAME: HSA_EMP_55_UPPER_LIMIT
FORMULA TYPE: COVERAGE UPPER LIMIT
DESCRIPTION:
Change History:
Name Date Comments
-----------------------------------------------
*******************************************************************************/
DEFAULT FOR BEN_IV_RT_STRT_DT is '1900/01/01 00:00:00' (date)
INPUTS ARE BEN_IV_RT_STRT_DT(date)
l_eff_date=GET_CONTEXT(EFFECTIVE_DATE, to_date('1951/01/01 0:00:00'))
l_Current_year=TO_NUMBER(TO_CHAR(l_eff_date,'YYYY'))
Q1_dt=to_date('31-03-'||to_char(l_Current_year),'DD-MM-YYYY')
Q2_dt=to_date('30-06-'||to_char(l_Current_year),'DD-MM-YYYY')
Q3_dt=to_date('30-09-'||to_char(l_Current_year),'DD-MM-YYYY')
Q4_dt=to_date('31-12-'||to_char(l_Current_year),'DD-MM-YYYY')
l_catchup=1000
l_ee_limit=2800
l_er_limit=1500
l_Q3_ER=0
l_Q2_ER=0
l_Q1_ER=0
L_Pending_ER=0
l_HSA_EO_Q3='N'
l_HSA_EF_Q3='N'
l_HSA_EO_Q2='N'
l_HSA_EF_Q2='N'
l_HSA_EO_Q1='N'
l_HSA_EF_Q1='N'
IF l_eff_date > Q3_dt
THEN
(
L_Pending_ER=375
CHANGE_CONTEXTS(EFFECTIVE_DATE=Q3_dt)
(
l_HSA_EO_Q3 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Employee')
l_HSA_EF_Q3 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Family ')
IF l_HSA_EO_Q3='Y'
THEN
(
l_Q3_ER=375
)
ELSE IF l_HSA_EF_Q3='Y'
THEN
(
l_Q3_ER=750
)
ELSE
(
l_Q3_ER=0
)
)
CHANGE_CONTEXTS(EFFECTIVE_DATE=Q2_dt)
(
l_HSA_EO_Q2 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Employee')
l_HSA_EF_Q2 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Family ')
IF l_HSA_EO_Q2='Y'
THEN
(
l_Q2_ER=375
)
ELSE IF l_HSA_EF_Q2='Y'
THEN
(
l_Q2_ER=750
)
ELSE
(
l_Q2_ER=0
)
)
CHANGE_CONTEXTS(EFFECTIVE_DATE=Q1_dt)
(
l_HSA_EO_Q1 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Employee')
l_HSA_EF_Q1 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Family ')
IF l_HSA_EO_Q1='Y'
THEN
(
l_Q1_ER=375
)
ELSE IF l_HSA_EF_Q1='Y'
THEN
(
l_Q1_ER=750
)
(
l_Q1_ER=0
)
)
)
ELSE IF l_eff_date > Q2_dt
THEN
(
L_Pending_ER=750
CHANGE_CONTEXTS(EFFECTIVE_DATE=Q2_dt)
(
l_HSA_EO_Q2 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Employee')
l_HSA_EF_Q2 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Family ')
IF l_HSA_EO_Q2='Y'
THEN
(
l_Q2_ER=375
)
ELSE IF l_HSA_EF_Q2='Y'
THEN
(
l_Q2_ER=750
)
ELSE
(
l_Q2_ER=0
)
)
CHANGE_CONTEXTS(EFFECTIVE_DATE=Q1_dt)
(
l_HSA_EO_Q1 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Employee')
l_HSA_EF_Q1 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Family ')
IF l_HSA_EO_Q1='Y'
THEN
(
l_Q1_ER=375
)
ELSE IF l_HSA_EF_Q1='Y'
THEN
(
l_Q1_ER=750
)
ELSE
(
l_Q1_ER=0
)
)
)
ELSE IF l_eff_date > Q1_dt
THEN
(
L_Pending_ER=1125
CHANGE_CONTEXTS(EFFECTIVE_DATE=Q1_dt)
(
l_HSA_EO_Q1 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Employee')
l_HSA_EF_Q1 = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'HSA Saver', 'Family ')
IF l_HSA_EO_Q1='Y'
THEN
(
l_Q1_ER=375
)
ELSE IF l_HSA_EF_Q1='Y'
THEN
(
l_Q1_ER=750
)
ELSE
(
l_Q1_ER=0
)
)
)
ELSE
(
L_Pending_ER=1500
)
log = ess_log_write('l_Q3_ER =>' + to_char(l_Q3_ER))
log = ess_log_write('l_Q2_ER =>' + to_char(l_Q2_ER))
log = ess_log_write('l_Q1_ER =>' + to_char(l_Q1_ER))
log = ess_log_write('L_Pending_ER =>' + to_char(L_Pending_ER))
log = ess_log_write('BEN_IV_RT_STRT_DT =>' + to_char(BEN_IV_RT_STRT_DT))
log = ess_log_write('l_er_limit =>' + to_char(l_er_limit))
log = ess_log_write('l_er_limit =>' + to_char(l_er_limit))
log = ess_log_write('l_HSA_EO_Q3 =>' + l_HSA_EO_Q3)
log = ess_log_write('l_HSA_EF_Q3 =>' + l_HSA_EF_Q3)
log = ess_log_write('l_HSA_EO_Q2 =>' + l_HSA_EO_Q2)
log = ess_log_write('l_HSA_EF_Q2 =>' + l_HSA_EF_Q2)
log = ess_log_write('l_HSA_EO_Q1 =>' + l_HSA_EO_Q1)
log = ess_log_write('l_HSA_EF_Q1 =>' + l_HSA_EF_Q1)
log = ess_log_write('l_eff_date =>' + to_char(l_eff_date))
L_MX_CVG_ALWD_AMT=l_catchup+l_ee_limit + l_er_limit -( l_Q3_ER+ l_Q2_ER + l_Q1_ER + L_Pending_ER)
RETURN L_MX_CVG_ALWD_AMT
