1. Administering Fast Formulas
  2. Coverage Amount Calculation

Coverage Amount Calculation

This formula type can be used to calculate coverage for a 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_CVG Number Y Coverage value

Errors

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

Sample Formula 1:

FORMULA NAME: BEN_CVG_CALC

FORMULA TYPE: Coverage Amount Calculation

DESCRIPTION: Coverage amount is determined based on persons DOB.

/ DEFAULT for PER_PER_DATE_OF_BIRTH is '01-Jan-1951' (date)
l_start_date = PER_PER_DATE_OF_BIRTH
l_cvg = 0
if ( l_start_date >= TO_DATE('01-Jan-1961') and l_start_date <= TO_DATE('31-Dec-1974') ) then (l_cvg = 800 )
if ( l_start_date >= TO_DATE('01-Jan-1975') and l_start_date <= TO_DATE('31-Dec-1989') ) then (l_cvg = 1000 )
if ( l_start_date >= TO_DATE('01-Jan-1990') and l_start_date <= TO_DATE('31-Dec-2010') ) then ( l_cvg = 1500 )
return l_cvg

Sample Formula 2:

FORMULA NAME: BEN_CVG_CALC2

FORMULA TYPE: Coverage Amount Calculation

DESCRIPTION: Formula derives coverage as factor of person’s age.

Default for PER_PER_DATE_OF_BIRTH is '01-JAN-1901' (date)
l_dob = PER_PER_DATE_OF_BIRTH
l_effective_date = GET_CONTEXT(EFFECTIVE_DATE,to_date('01-JAN-2012'))
if not PER_PER_DATE_OF_BIRTH was defaulted then (
l_age = trunc(ROUNDUP(months_between(l_effective_date , l_dob)/12))
l_cvg = l_age * 2500 )
else
( l_cvg = 10000 )
return l_cvg

Sample Formula 3:

FORMULA TYPE: Coverage Amount Calculation

DESCRIPTION: Calculate coverage for retirees based on their retirement period.

DEFAULT for CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT is 0
DEFAULT for PER_PER_LATEST_TERMINATION_DATE is '1951/01/01 00:00:00' (date)
l_sal = 0
l_yrs = 0
l_fctr = 0
l_val = 0
l_eff_date = GET_CONTEXT(EFFECTIVE_DATE, to_date('1951/01/01 0:00:00'))
IF PER_PER_LATEST_TERMINATION_DATE WAS NOT DEFAULTED
THEN
(
l_term_dt = PER_PER_LATEST_TERMINATION_DATE
l_sal_dt = add_days(l_term_dt,-1)
/* get the salary as of the term date -1 */
CHANGE_CONTEXTS (EFFECTIVE_DATE=l_sal_dt)
(
l_sal = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT
) /* end change contexts */
/* calculate the time passed since the employee retired */
l_yrs = Round(MONTHS_BETWEEN(l_eff_date,l_term_dt)/12)
if (l_yrs>=0 and l_yrs < 5) then
( l_fctr = 1 )
else if (l_yrs >= 5 ) then
( l_fctr = 0.5 )
l_val = ROUND(l_sal * l_fctr)
) /* end defaulted */
return l_val