Formula – FAQ

The following sections will provide you information about using formulas.

How you use arrays within formulas

FORMULA Name : ELIG1

FORMULA TYPE : Participation and Rate Eligibility

Default for LOCATIONS is EMPTY_NUMBER_NUMBER
I=1
ELIGIBLE='N'
LOCATIONS[1]=1625
LOCATIONS[2]=1630
l_job_code=123 /* to be obtained from a database item */
WHILE (LOCATIONS.EXISTS(I)) LOOP
(
if (LOCATIONS[I] = l_job_code) then
(ELIGIBLE='Y'
/* If found eligible you need not iterate */
EXIT
)
/* increment the counter*/
I=I+1
) /* end loop */
Return ELIGIBLE

How you use User defined tables within benefits formulas

Formula Name: HRA_ER_CONTRIBUTION

Formula Type: Post Enrollment Rate Calculation

er_contribution = 0 hra_single_amt = 0 hra_family_amt = 0
BEN_TABLE = 'ORCL_GLOBAL_VALUES' BEN_TABLE_COLUMN = 'Global_Value' BEN_ROW_VALUE = 'HRA_SINGLE_AMT'
hra_single_amt = to_num(get_table_value ( BEN_TABLE, BEN_TABLE_COLUMN, BEN_ROW_VALUE))
/* determine medical plan option */
EE_only = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'Medical', 'Employee') EE_Waive = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT','ENROLLED', 'Medical', 'Waive') /* Use the option from above code to determine the ER contribution */ IF EE_Waive = 'Y' THEN
( er_contribution = 0 )
ELSE
( er_contribution = hra_single_amt )
l_result = er_contribution / 12
Return l_result

How you use element entry values within benefits rates

FORMULA NAME: RT_VALUE_CALC

FORMULA TYPE: Rate Value Calculation

Default_data_value for ORCL_PP_ELEMENT_2_AMOUNT_ENTRY_VALUE is 0
Default for BEN_OPT_NAME is 'NA'
/* Initialization section */
i=1
l_val=0
l_ret=0
if (ORCL_PP_ELEMENT_2_AMOUNT_ENTRY_VALUE.exists(i)) then (l_val=ORCL_PP_ELEMENT_2_AMOUNT_ENTRY_VALUE[i])
if (BEN_OPT_NAME='ACT_ZHRX_PLAN_1_EMP') then ( l_ret=750-l_val )
else if (BEN_OPT_NAME='ACT_ZHRX_PLAN_1_FAM' OR BEN_OPT_NAME='ACT_ZHRX_PLAN_1_CHILD') then ( l_ret=1500-l_val )
if (l_ret<0) then
( l_ret=0 )
return l_ret

How you access pay periods within rate periodization rules to compute remaining contributions

FORMULA NAME: RT_PERIOD_CALC

FORMULA TYPE: Rate Periodization Rule

Inputs are BEN_IV_CONVERT_FROM (text), BEN_IV_CONVERT_FROM_VAL (text)
DEFAULT_DATA_VALUE FOR BEN_PEN_PL_NAME_TN is 'My-Default'
DEFAULT_DATA_VALUE FOR BEN_PEN_ENRT_CVG_STRT_DT_DN is '1951/01/01 0:00:00'(date)
DEFAULT_DATA_VALUE FOR BEN_PEN_ORGNL_ENRT_DT_DN is '1951/01/01 0:00:00'(date)
DEFAULT FOR BEN_YRP_START_DATE is '1951/01/01 0:00:00'(date) DEFAULT_DATA_VALUE FOR BEN_PEN_OPT_NAME_TN is 'My-Default' DEFAULT_DATA_VALUE FOR BEN_PEN_BNFT_AMT_NN is 0
l_eff_date='1951/01/01 0:00:00' (date)
l_new_eff_date='1951/01/01 0:00:00' (date)
l_enrt_cvg_strt_dt='1951/01/01 0:00:00' (date)
l_ori_enrt_cvg_strt_dt='1951/01/01 0:00:00' (date)
l_chk_date='1951/01/01 0:00:00' (date)
l_yr_perd_st_date='1951/01/01 0:00:00' (date)
l_bnft_amt=0
l_prev_bnft_amt=0
l_prev_cntbn=0
DFND_VAL=0
ANN_VAL=0
CMCD_VAL=0
i=1
j=1
l_total_tp_in_cal_year=1
l_pay_perd_totper_rem=99
l_pay_cur_perd=1
/* input values */
l_iv_period=BEN_IV_CONVERT_FROM
l_iv_val=to_number(BEN_IV_CONVERT_FROM_VAL)
/* change contexts for accomodating waiting period */
l_eff_date=GET_CONTEXT(EFFECTIVE_DATE, to_date('1951/01/01 0:00:00'))
l_eff_date=add_days(l_eff_date,30)
l_new_eff_date=add_days(last_day(l_eff_date),1)
CHANGE_CONTEXTS(EFFECTIVE_DATE=l_new_eff_date)
(/* get the bnft amount and the enrt cvg strt date within this block */
while (BEN_PEN_PL_NAME_TN.exists(j)) loop
(
if ((BEN_PEN_PL_NAME_TN[j]='Dependent Care FSA') and (BEN_PEN_OPT_NAME_TN[j]='Annual Goal Amount')) then (
l_bnft_amt=BEN_PEN_BNFT_AMT_NN[j]
l_enrt_cvg_strt_dt=BEN_PEN_ENRT_CVG_STRT_DT_DN[j] l_ori_enrt_cvg_strt_dt=BEN_PEN_ORGNL_ENRT_DT_DN[j] )
j=j+1
) /*end loop plan option */
) /*end change contexts */
j=1
CHANGE_CONTEXTS(EFFECTIVE_DATE=l_new_eff_date-1)
(/* get the previous bnft amount within this block */
while (BEN_PEN_PL_NAME_TN.exists(j)) loop
(
if ((BEN_PEN_PL_NAME_TN[j]='Dependent Care FSA') and (BEN_PEN_OPT_NAME_TN[j]='Annual Goal Amount')) then (
l_prev_bnft_amt=BEN_PEN_BNFT_AMT_NN[j]
)
j=j+1
) /*end loop plan option */
) /*end change contexts */
IF (l_bnft_amt = 0 and l_iv_period = 'ANNUAL' ) THEN ( l_bnft_amt = l_iv_val)
/* check if enrolled earlier in same year */
IF (l_ori_enrt_cvg_strt_dt <= l_enrt_cvg_strt_dt) THEN ( l_chk_date = l_ori_enrt_cvg_strt_dt )
ELSE
( l_chk_date = l_enrt_cvg_strt_dt )
/* substitute the program id in below clause to pick year period start date */ CHANGE_CONTEXTS(PGM_ID = 202,EFFECTIVE_DATE=l_new_eff_date) (
l_yr_perd_st_date = BEN_YRP_START_DATE
)
/* If enrolled in prior year, use this year period start date */ IF ( l_chk_date < l_yr_perd_st_date ) THEN (
l_chk_date = l_yr_perd_st_date
)
l_pay_cur_perd= to_number(BEN_FN_GET_CHAR_VALUE('PAY_TIME_PERIODS','PERIOD_NUM',' ',' ','START_DATE',to_char(l_chk_date)))
l_total_tp_in_cal_year =
to_number(BEN_FN_GET_CHAR_VALUE('PAY_TIME_PERIODS','TOTAL_TP_IN_CALENDAR_YEAR',' ',' ','START_DATE',to_char(l_chk_date)))
l_pay_perd_totper_rem =l_total_tp_in_cal_year - l_pay_cur_perd +1
IF (l_prev_bnft_amt <> 0 and l_bnft_amt <> l_prev_bnft_amt ) THEN
(
l_prev_cntbn = (l_prev_bnft_amt/l_total_tp_in_cal_year )*(l_pay_cur_perd - 1)
)
ELSE
(
l_prev_cntbn = 0
)
CMCD_VAL = (l_bnft_amt - l_prev_cntbn )/l_pay_perd_totper_rem DFND_VAL = (CMCD_VAL * l_pay_perd_totper_rem)/12 ANN_VAL = l_bnft_amt
RETURN DFND_VAL,ANN_VAL,CMCD_VAL

Associating HR actions to benefits life events

FORMULA NAME : New Hire Event

FORMULA TYPE : Person Changes Causes Life Event

/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/ DEFAULT for PER_ASG_ACTION_CODE is 'AA'
/*=========== DATABASE ITEM DEFAULTS ENDS======================*/ /*============ INPUT VALUES DEFAULT BEGIN ======================*/ /*============== INPUT VALUES DEFAUT ENDS ======================*/
/*================= INPUTS SECTION BEGIN ========================*/
/*================== INPUTS SECTION ENDS ========================*/
/*================ FORMULA SECTION BEGIN =======================*/
l_action_code = PER_ASG_ACTION_CODE
if NOT PER_ASG_ACTION_CODE was defaulted then
(
if ((l_action_code='HIRE') OR (l_action_code='EMPL_REHIRE') )then
(l_create_ptnl = 'Y')
else
(l_create_ptnl = 'N')
)
return l_create_ptnl
/*================ FORMULA SECTION END =======================*/

Accessing contacts or family member details to determine eligibility

FORMULA NAME : DPNT_ELIG

FORMULA TYPE : Dependent eligibility

DEFAULT_DATA_VALUE for PER_EXT_CONT_CONTACT_TYPE is 'NA'
DEFAULT_DATA_VALUE FOR PER_EXT_CONT_DATE_OF_BIRTH IS '1951/01/01' (DATE)
DEFAULT_DATA_VALUE FOR PER_EXT_CONTACT_ATTRIBUTE1 IS 'NA' DEFAULT_DATA_VALUE FOR PER_EXT_CONT_SEX IS 'NA' ELIGIBLE = 'N'
l_eff_dt = GET_CONTEXT(EFFECTIVE_DATE,to_date('1951/01/01 00:00:00') )
/* S - Spouse(female), 35 yrs age as of event date */
i=1
WHILE PER_EXT_CONT_CONTACT_TYPE.exists(i)
LOOP
(
IF (PER_EXT_CONT_CONTACT_TYPE[i]='S'
AND PER_EXT_CONTACT_ATTRIBUTE1[i] <> 'NA' AND PER_EXT_CONT_SEX[i] = 'F' ) AND (MONTHS_BETWEEN(l_eff_dt, PER_EXT_CONT_DATE_OF_BIRTH[i])) < 4200
THEN
( ELIGIBLE = 'Y' )
i=i+1
)
RETURN ELIGIBLE

How you access benefits data within HCM extracts

FORMULA NAME : ben_ext_rule

FORMULA TYPE : Extract rule

Default for l_Val is 'XX'
DEFAULT_DATA_VALUE for BEN_PBG_BENEFIT_GROUP_NAME_TN is 'aa1'
default for i is 1
i=1
L_BG_ID = GET_CONTEXT(BUSINESS_GROUP_ID, 1)
L_EFF_DATE = GET_CONTEXT(EFFECTIVE_DATE, to_date('1951/01/01 00:00:00'))
L_PERSON_ID = GET_CONTEXT(PERSON_ID, 9999)
CHANGE_CONTEXTS(EFFECTIVE_DATE = L_EFF_DATE, BUSINESS_GROUP_ID = L_BG_ID, PERSON_ID
= L_PERSON_ID )
(
while BEN_PBG_BENEFIT_GROUP_NAME_TN.exists(i) loop
(
l_Val=BEN_PBG_BENEFIT_GROUP_NAME_TN[i] i=i+1
)
)
return l_Val