Rate Periodization

This formula gives complete control for users to divide the rate values into periods. User can define their one rule using the contexts and input values available to distribute the rates among defined amounts, annual amounts, and communicated amounts.

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
• PGM_ID
• PL_ID
• PL_TYP_ID

Database Items

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

• Database items based on person’s assignment: HR_ASSIGNMENT_ID AND PERSON_ID

Input Variables

Input variables are available to this formula type.

Input variables table

Name	Data Type	Remarks
BEN_IV_CONVERT_FROM	Char	Possible values are DEFINED, CMCD, and ANNUAL. If BEN_IV_CONVERT_FROM is DEFINED, then the rule needs to compute the annual and communicated values using the input value BEN_IV_CONVERT_FROM_VAL. If BEN_IV_CONVERT_FROM is ANNUAL, then the rule needs to compute the defined and communicated values using the input value BEN_IV_CONVERT_FROM_VAL.
BEN_IV_CONVERT_FROM_VAL	Number	Value passed to the formula

Return Variables

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

Return variables table

Out Variable name	Data Type	Remarks
DFND_VAL	Number	mandatory out variable
ANN_VAL	Number	mandatory out variable
CMCD_VAL	Number	mandatory out variable

Errors

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

Sample Formula 1:

FORMULA Name : RATE_PERIODIZATION_1

FORMULA TYPE : Rate Periodization

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_char='NA'
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_prev_bnft_amt_chr='N'
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_perd=1
l_pay_cur_perd=1
/* input values */
l_iv_period=BEN_IV_CONVERT_FROM
l_iv_val=to_number(BEN_IV_CONVERT_FROM_VAL)
/* Main () */
j=1
/* change contexts for accommodating waiting periods or future rate, coverage start dates */ l_eff_date=GET_CONTEXT(EFFECTIVE_DATE, to_date('1951/01/01 0:00:00'))
l_new_eff_date=add_days(l_eff_date,15)
CHANGE_CONTEXTS(EFFECTIVE_DATE=l_new_eff_date)
(/* get the benefit amount and the enrollment coverage start date within this block. Replace plan and option names below */
while (BEN_PEN_PL_NAME_TN.exists(j)) loop (
if ((BEN_PEN_PL_NAME_TN[j]='ABC_PT1_PLAN1') and
(BEN_PEN_OPT_NAME_TN[j]='ABC_PT1_PLAN1_OPT1')) 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
l_new_eff_date=add_days(l_eff_date,-1)
CHANGE_CONTEXTS(EFFECTIVE_DATE=l_new_eff_date)
(/* get the previous benefit amount within this block. Replace plan and option names below. */ l_prev_bnft_amt_chr=BEN_FN_GET_CHAR_VALUE( 'BEN_PRTT_ENRT_RSLT'
,'BNFT_AMT'
,'ABC_PT1_PLAN1'
,'ABC_PT1_PLAN1_OPT1'
)
if ( l_prev_bnft_amt_chr <> ' ' and l_prev_bnft_amt_chr <> 'NO_DATA_FOUND' and
l_prev_bnft_amt_chr <> 'N' ) then
(l_prev_bnft_amt=to_number(l_prev_bnft_amt_chr))
) /*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 = 300100083634356,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_perd= to_number(BEN_FN_GET_CHAR_VALUE( 'PAY_TIME_PERIODS'
,'PERIOD_NUM'
,' '
,' '
,'START_DATE'
,to_char(l_chk_date)
))
/* Get the current running pay periods by passing the effective date */
l_pay_cur_perd= to_number(BEN_FN_GET_CHAR_VALUE( 'PAY_TIME_PERIODS'
,'PERIOD_NUM'
,' '
,' '
,'START_DATE'
,to_char(l_new_eff_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 ) THEN
(
l_prev_cntbn = (l_prev_bnft_amt/(l_total_tp_in_cal_year-l_pay_perd+1))*(l_pay_cur_perd - l_pay_perd )
)
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