1. Administering Fast Formulas
  2. Enrollment Opportunity

Enrollment Opportunity

This formula type can be used to determine whether a benefits object should be an electable choice for the person.

Contexts

The following contexts are available to formulas of this type:

  • BUSINESS_GROUP_ID ( ENTERPRISE_ID)
  • EFFECTIVE_DATE
  • HR_ASSIGNMENT_ID
  • PERSON_ID
  • LER_ID
  • ORGANIZATION_ID
  • JURISDICTION_CODE
  • PGM_ID
  • PL_ID
  • PL_TYP_ID
  • OPT_ID
  • LER_ID

Database Items

Use only the following database items that are available to formulas of this type:

  • All columns except attribute columns from tables: ben_ler_f, ben_pgm_f, ben_pl_f, ben_opt_f, ben_pl_typ_f
  • Database items based on person’s assignment – HR_ASSIGNMENT_ID AND PERSON_ID

Input Variables

Input variables are not available to formulas of this type.

Return Variables

Use predefined names for return variables. The following return variables are available to formulas of this type.

Return variables table

Return Value Data Type Required Description
L_ENRT_OPP Char Y

Y - Yes

A - Leave as choice and use elctbl_flag

L – Lose only condition, if enrolled, will be de-enrolled

N – No

Errors

If any other output value is returned then the participation process errors with BEN_91329_FORMULA_RETURN.

Sample Formula 1:

FORMULA NAME: BEN_ENRT_OPTY

FORMULA TYPE: Enrollment Opportunity

DESCRIPTION: Formula to determine whether a compensation object should be an electable choice for the person.

DEFAULT for BEN_LER_NAME is 'ABC'
DEFAULT for BEN_PLN_NAME is 'XYZ'
DEFAULT for BEN_OPT_NAME is 'XYZ'
l_enrt_opp = 'A'
l_ler_name = BEN_LER_NAME
l_pl_name = BEN_PLN_NAME
l_opt_name = BEN_OPT_NAME
If ( l_pl_name = 'Aetna Medical'
and ( l_opt_name = 'Domestic Partner' or l_opt_name = 'Domestic Partner + one')
and ( l_ler_name = 'Open' or l_ler_name = 'New Hire' ) ) then
( l_enrt_opp = 'N' )
If ( l_pl_name = 'Be Healthy HMO'
and ( l_opt_name = 'Employee + family' or l_opt_name = 'Employee + spouse')
and ( l_ler_name <> 'Open' ) ) then
(l_enrt_opp = 'A' )
Return l_enrt_opp

Sample Formula 2:

FORMULA NAME: BEN_ENROLLMENT_OPPORTUNITY

FORMULA TYPE: Enrollment Opportunity

Default for BEN_PLN_NAME is 'Default'
Default for BEN_LER_NAME is 'Default'
l_return='N'
l_med_wv='N'
l_pt2_p1='N'
l_pt2_p1_opt1='N'
l_pt2_p1_opt2='N'
l_pt2_p1_opt3='N'
l_pt2_p2='N'
l_pt2_p2_opt1='N'
l_pt2_p2_opt2='N'
l_pt2_p2_opt3='N'
l_pt2_p2='N'
/* Below is a function which returns Y if there is an enrollment */
l_med_wv=ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'SO_CHANGE_PT2_PLAN_3_WAIVE'
,'NA'
)
if (BEN_PLN_NAME='SO_CHANGE_PT2_PLAN_1') then (
l_pt2_p1_opt1=ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'SO_CHANGE_PT2_PLAN_1'
,'SO_CHANGE_PT2_PLAN_1_OPT_1'
)
l_pt2_p1_opt2=ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'SO_CHANGE_PT2_PLAN_1'
,'SO_CHANGE_PT2_PLAN_1_OPT_2'
)
l_pt2_p1_opt3=ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'SO_CHANGE_PT2_PLAN_1'
,'SO_CHANGE_PT2_PLAN_1_OPT_3'
)
) /* end if for SO_CHANGE_PT2_PLAN_1 */
if (l_pt2_p1_opt1='Y' or l_pt2_p1_opt2='Y' or l_pt2_p1_opt3='Y') then (l_pt2_p1='Y')
58 Oracle Benefits Fast Formula Reference Guide / Version 1.6
Copyright © 2021, Oracle and/or its affiliates / Public
if (BEN_PLN_NAME='SO_CHANGE_PT2_PLAN_2') then (
l_pt2_p2_opt1=ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'SO_CHANGE_PT2_PLAN_2'
,'SO_CHANGE_PT2_PLAN_2_OPT_1'
)
l_pt2_p2_opt2=ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'SO_CHANGE_PT2_PLAN_2'
,'SO_CHANGE_PT2_PLAN_2_OPT_2'
)
l_pt2_p2_opt3=ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'SO_CHANGE_PT2_PLAN_2'
,'SO_CHANGE_PT2_PLAN_2_OPT_3'
)
) /*end if SO_CHANGE_PT2_PLAN_2 */
if (l_pt2_p2_opt1='Y' or l_pt2_p2_opt2='Y' or l_pt2_p2_opt3='Y') then (l_pt2_p2='Y')
if (l_med_wv='Y') then
(
l_return='Y'
)
else if ( (l_med_wv='N') and (BEN_LER_NAME='SO_MARRIAGE_1') ) then
(
if ( (BEN_PLN_NAME='SO_CHANGE_PT2_PLAN_1') and (l_pt2_p1='Y') ) then (l_return='Y')
if ( (BEN_PLN_NAME='SO_CHANGE_PT2_PLAN_2') and (l_pt2_p2='Y') ) then (l_return='Y')
) /*end else if */
return l_return

Sample Formula 3:

FORMULA TYPE : Enrollment Opportunity

DESCRIPTION: Provide enrollment opportunity if person is rehired within 30 days of termination.

DEFAULT for PER_PER_LATEST_REHIRE_DATE is '1951/01/01 00:00:00' (date)
DEFAULT for PER_PER_LATEST_TERMINATION_DATE is '1951/01/01 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_ASSIGNMENT_ID is 99
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_START_DATE is '1951/01/01 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_END_DATE is '1951/01/01 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_ASSIGNMENT_TYPE is 'NA'
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_STATUS_TYPE is 'NA'
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_LATEST_CHANGE is 'NA'
/* if the person is a first time hire, the PER_PER_LATEST_REHIRE_DATE will return the hire date */
l_hire_dt = PER_PER_LATEST_REHIRE_DATE
l_term_dt = PER_PER_LATEST_TERMINATION_DATE
l_prev_asg_id=9999
l_enrt_opportunity = 'N'
if PER_PER_LATEST_TERMINATION_DATE was not defaulted then
(
if l_hire_dt < add_days(l_term_dt,30)
then
(
/* person is rehired within 30 days */
/* get the assignment_id */
l_prev_asg_dt = add_days(l_term_dt,-1)
i=1
while ( PER_HIST_ASG_ASSIGNMENT_ID.exists(i) ) loop
(
if ( (PER_HIST_ASG_EFFECTIVE_START_DATE[i] < l_prev_asg_dt)
AND (PER_HIST_ASG_EFFECTIVE_END_DATE[i] >= l_prev_asg_dt)
AND (PER_HIST_ASG_ASSIGNMENT_TYPE[i] = 'ET')
AND (PER_HIST_ASG_STATUS_TYPE[i] = 'ACTIVE')
AND (PER_HIST_ASG_EFFECTIVE_LATEST_CHANGE[i] = 'Y' )
)
then
(
l_prev_asg_id = PER_HIST_ASG_ASSIGNMENT_ID[i]
) /* end if */
i=i+1
) /* end while */
/* check if the person is enrolled in any of the medical plan */
CHANGE_CONTEXTS(HR_ASSIGNMENT_ID=l_prev_asg_id, EFFECTIVE_DATE=l_prev_asg_dt)
(
l_med_enroll = ben_fn_get_char_value(
'BEN_PRTT_ENRT_RSLT'
,'ENROLLED'
,'Healthy HMO'
,'NA'
)
if (l_med_enroll = 'Y') then
(
l_enrt_opportunity = 'Y'
)
) /* end change contexts */
) /* < 30 days */
) /* end not defaulted */
return l_enrt_opportunity

Sample Formula 4:

FORMULA_NAME: ADD_CHANGE_BENEFICIARY

DESCRIPTION: Provide opportunity only if already enrolled.

DEFAULT for BEN_LER_NAME is 'NA'
DEFAULT for BEN_PLN_NAME is 'NA'
DEFAULT for BEN_OPT_NAME is 'NA'
l_opp = 'N'
l_flag = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT','ENROLLED',BEN_PLN_NAME,BEN_OPT_NAME)
IF ( l_flag = 'Y') then
(
l_opp = 'Y'
)
return l_opp