oab fast formula user guide

7/22/2019 OAB Fast formula user guide

1/184

OAB FASTFORMULA

REFERENCE GUIDE

Prepared by

Authors: Prasad Bodla

Vikas Sethi

Nagaraj Hunur

Trevor Mathers

Ty Hayden

Creation Date: 21 - Feb - 2002

Last Updated: 22 - Aug - 2006

Status:

Version: 1.3

Copyright (C) 2005 Oracle CorporationAll Rights Reserved


2/184

Change History of this Document

Date Section No. Author Remarks/Changes

25-NOV2003

Chapter 2 PrasadBodla

Added entries for 2 new formula types: Person Change Causes LifeEvent and Rate Value Calculation

17-MAR-05

PrasadBodla

Added entries for 3 new formula types for FP J: Range ofScheduled Hours, Manager Hierarchy, Rate Periodization

11-MAY-05

PrasadBodla

Added entries for 2 new formulat types for FP K: Eligibility AccessCalculation Rule and Election Value Range Rule

29-Nov-05

PrasadBodla

New list of product data base items added

10-Feb-06

NagarajHunur

Corrected the sample code for the Fast Formula: BEN_AGE_CALC

10-Feb -06

NagarajHunur

Updated the content for the Mandatory Determination Fast Formula

04May06

NagarajHunur

Updated the default to assign pending action fast formula.

22-Aug_2006

NagarajHunur

Added the Compensation Item rule formula type.


3/184

Chapter 1 : Introduction

FastFormula is a way to customize the existing functionality in Oracle Standardand Advanced Benefits (OAB). Formulas in OAB are pieces of code which canreceive information from calling program (packages), access databaseinformation and return values to the calling program. The calling program canuse this information to modify the process functionality.

Examples: An employee becomes eligible for a Stock Option plan if his or herperformance rating is above 4.The normal rate for an Aetna Dental plan is $25 per month. If the person alsoenrolls in Aetna Medical, he or she receives a 10% discount.

There are 100+ seeded formula types in OAB.

The FastFormula language is simple and hides the complexity of accessing thetables and column.

Formula Usage in Benefits

Although there are 100+ formula types in OAB, there are certain types whichappear more frequently.

Person selection and Compensation object selection rules filter out persons orcompensation objects to be processed in batch processes.

Computation rules replace calculated values, these rules include value calculationrules, and partial month proration rules.

Eligibility rules.

Messaging rules Popup messages.

You select a formula you have written either on the list of values on the Submit

Requests window or else on the applicable plan design window.

Return statements for BEN formulas: A formula can return more than one valueto a calling package. In Benefits, different packages which call the formulaexpect outputs to be returned differently. Based on the formula type, the number


4/184

and data types of values to be returned are predefined. Some packages expect thepredefined names for output variables; the location of a variable in the returnstatement does not matter. Some packages expect the return variables to bereturned by location, in this case the name of output variable does not matter.

If predefined names are expected to be used and the formula does not usethe expected names, the process will error out at run time.

See: Using Oracle FastFormula (A73295-04) for more information

Chapter 2 : OAB FastFormulas

This section provides information on each seeded formula type, database items, seeded contexts for aformula type, and available input values. A formula of a given formula type is expected to returnvalues in a certain format and data type and with a predefined name in certain cases.

A given formula type can be attached at different places in the product. For example, you can use thePerson Selection formula type in several concurrent programs. All the above information isprovided below for each formula type.

Formula Type: Eligibility Access Calculation Rule

1. Functional Usage: This rule will be used in setting up the user-defined criteria. It will beevaluated in the eligibility determination process.

2. Navigation Path: Total Compensation > Eligibility Profiles > Participant Eligibility > Userdefined criteria :

3. Contexts: Business_Group_Id, Assignment Id, Date_Earned (life event occurred date or effectivedate), Organization_Id, pgm_id, pl_id, opt_id, ler_id, pl_typ_id

4. Overloaded Contexts: No

5. Database items available: All columns except attribute columns from tables ben_ler_f,ben_pgm_f, ben_pl_f, ben_pl_typ_f, ben_opt_f and database items based on personsassignment_id.

6. Input values available: No Input values are available.

7. Constraints using Database items: Database items other than the ones mentioned above cannotbe used.

8. Formula output or return values: Formula can return two values. First value is mandatory.Values are returned in character format, but the process will convert returned values based on datatypes.

Out Variable name Data Type Value RemarksCRIT_COL1_VALUE

(ANY NAME CAN BE

USED)

Varchar2 Mandatory out variable. Value passed back willbe converted based on date type specified in


5/184

CRIT_COL1_DATATYPE.

IF RETURNED VALUE IS DATE THE DATA

SHOULD BE RETURNED BACK IN CANONICAL

FORM (YYYY/MM/DD HH24:MI:SS).

CRIT_COL2_VALUE

(ANY NAME CAN BE

USED)

Varchar2 Mandatory only if second criteria is added ongeneric criteria row. Value passed back will be

converted based on date type specified inCRIT_COL2_DATATYPE. IF RETURNED VALUE ISDATE THE DATA SHOULD BE RETURNED BACK

IN CANONICAL FORM (YYYY/MM/DD

HH24:MI:SS).

1. Erroring: If any other output value is passed back then process errors out byraising BEN_91329_FORMULA_RETURN.

2. Remarks: None

3. Sample Formula:

4. Formula functions used in the sample formula: None

13. Unit test script: N.A

Formula Type: Election Value Range Rule

1. Functional Usage: This rule will be used if the user wants to return the min-max values for the

standard rates when the rate is enterable at enrollment. This rule will currently be enabled only for

Activity Type as CWB Worksheet Amount and CWB Recommended Amount.

2. Navigation Path: Total Compensation > Rate/Coverage Definitions > Standard Rates

3. Contexts: Business_Group_Id, Assignment Id, Date_Earned, Organization_Id, Jurisdiction_Code,

pgm_id, pl_id, opt_id, ler_id, pl_typ_id

4. Overloaded Contexts: No

5. Database items available: All columns except attribute columns from tables ben_ler_f,ben_pgm_f, ben_pl_f, ben_pl_typ_f, ben_opt_f and database items based on persons

assignment_id.

6. Input values available: PERSON_ID Input value is available.

7. Constraints using Database items: Database items other than the ones mentioned above cannotbe used.

8. Formula output or return values: Formula should use pre defined names for return variables

Out Variable name Data Type Value Remarks

L_MN_ELCN_VAL Number mandatory out variableL_MX_ELCN_VAL Number mandatory out variable


6/184

L_INCRMT_VAL Number mandatory out variable

L_DFLT_VAL Number mandatory out variable

1. Erroring: If any other output value is passed back then process errors out byraising BEN_92311_FORMULA_VAL_PARAM.

2. Remarks: None

3. Sample Formula:

/**********************************************************************FORMULA NAME: BEN_CWB_MIN_MAX_RLFORMULA TYPE : Oracle Advanced BenefitsDESCRIPTION :***********************************************************************Change History:Name Date Description

-----------------------------------------------------------------------Nagaraj Hunur 22-Jan-2004 Initial Version

***********************************************************************FORMULA TEXT: Logic in simple terms.

**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/DEFAULT for ASG_JOB is ','/*=========== DATABASE ITEM DEFAULTS ENDS======================*/

/*============ INPUT VALUES DEFAULT BEGIN ======================*/

/*============== INPUT VALUES DEFAUT ENDS ======================*/

/*================= INPUTS SECTION BEGIN ========================*//*================== INPUTS SECTION ENDS ========================*/

/*================ FORMULA SECTION BEGIN =======================*/l_job = ASG_JOBif not ASG_JOB was defaulted then(

if l_job = 'Finance Manager' then(L_MN_ELCN_VAL = 100

L_MX_ELCN_VAL = 10000L_INCRMT_VAL = 100

L_DFLT_VAL = 100)else(L_MN_ELCN_VAL = 150L_MX_ELCN_VAL = 15000L_INCRMT_VAL = 10

L_DFLT_VAL = 150))


7/184

return L_MN_ELCN_VAL, L_MX_ELCN_VAL, L_INCRMT_VAL, L_DFLT_VAL

/*================ FORMULA SECTION END =======================*/



Formula Type: Range of scheduled Hours

1. Functional Usage: Range of scheduled hours eligibility criteria can be used createdusing a rule. If customer wants to use complex logic to decide on the number ofscheduled hours, this formula can be used.

2. Navigation Path:Total Compensation > General Definitions > Eligibility Profiles > Participant> Employment Tab > Range of scheduled hours criterion.

3. Contexts: DATE_EARNED (p_effective_date), ASSIGNMENT_ID,ORGANIZATION_ID, BUSINESS_GROUP_ID, PGM_ID, PL_TYP_ID, PL_ID,

OPT_ID, JURISDICTION_CODE.4. OverloadedContexts: No

5. Database items available: All columns except attribute columns from tables ben_ler_f,ben_pl_f, ben_pgm_f, ben_opt_f and database items based on persons assignment_id.

6. Input values available:

Name Data Type Remarks

BEN_IV_RT_STRT_DT Char Rate Start Date passed in formatYYYY/MM/DD HH24:MI:SS

BEN_IV_CVG_STRT_DT Char Coverage Start Date passed informat YYYY/MM/DDHH24:MI:SS

1. Constraints using Database items: Database items other than the ones mentionedabove cant be used.

2. Formula output or return values:


MIN_HOURS (mandatory outvariable, any name can be used)

Number Either MIN_HOURS orMAX_HOURS or both needs tobe specified.

MAX_HOURS Number

FREQUENCY Char Based on lookup FREQUENCY

1. Erroring: If wrong values are passed back from the formula does it gracefully exit or iferrors what kind of error is raised. : If any other output value is passed back then processerrors (BEN_91329_FORMULA_RETURN).If FREQUENCY is not returned then the following error will be returned'BEN_92310_FORMULA_RET_PARAM_.

2. Remarks: None

3. Sample Formula:/**********************************************************************FORMULA NAME: BEN_RNG_SCHED_HRS_RL


8/184

FORMULA TYPE : Oracle Advanced BenefitsDESCRIPTION : Range of scheduled hours***********************************************************************Change History:Name Date Description-----------------------------------------------------------------------

Nagaraj Hunur 22-Dec-2004 Initial Version


**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/Default for ASG_JOB is ' '/*=========== DATABASE ITEM DEFAULTS ENDS======================*/

/*============ INPUT VALUES DEFAULT BEGIN ======================*//*============== INPUT VALUES DEFAUT ENDS ======================*/


/*================ FORMULA SECTION BEGIN =======================*/

l_job = ASG_JOBif l_job = 'Manager' then(MIN_HOURS = 25MAX_HOURS = 40FREQUENCY = H )else

(MIN_HOURS = 20MAX_HOURS = 35FREQUENCY = H)

return MIN_HOURS, MAX_HOURS, FREQUENCY/*================ FORMULA SECTION END =======================*/



Formula Type: Manager Hierarchy

1. Functional Usage: If customer wants to use complex logic to decide on which managerneeds to be providing approvals in the organization hierarchy, this formula can be used.

2. Navigation Path:Total Compensation > Programs and Plans > Plan Enrollment Requirements.

3. Contexts: DATE_EARNED (p_effective_date), ASSIGNMENT_ID,ORGANIZATION_ID, BUSINESS_GROUP_ID, GROUP_PL_ID, LER_ID

4. OverloadedContexts: No


9/184

5. Database items available: All columns except attribute columns from tables ben_ler_f,ben_pl_f and database items based on persons assignment_id.



BEN_IV_PERSON_ID Number Person ID

BEN_IV_ACCESS_CD Char Plan Access code

BEN_IV_END_DT Char End Date passed in formatYYYY/MM/DD HH24:MI:SS

BEN_IV_AUTO_DISTR_FLAG Char Auto Distribute flag Y/N

BEN_IV_WS_UPD_STRT_DT Char Worksheet update start date passedin format YYYY/MM/DDHH24:MI:SS

BEN_IV_WS_UPD_END_DT Char Worksheet update end date passedin format YYYY/MM/DDHH24:MI:SS

BEN_IV_USES_BDGT_FLAG Char Uses budget flag. Values - Y/N




l_manager_id (mandatory outvariable, any name can be used)

Number

1. Erroring: None

2. Remarks: Sample formula which returns a manager ID based on persons organization.

3. Sample Formula:

/**********************************************************************FORMULA NAME: BEN_MGR_HRCHY_RLFORMULA TYPE : Oracle Advanced BenefitsDESCRIPTION :***********************************************************************Change History:Name Date Description-----------------------------------------------------------------------Nagaraj Hunur 22-Dec-2004 Initial Version


**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/DEFAULT for ASG_ORG is ','/*=========== DATABASE ITEM DEFAULTS ENDS======================*/



10/184



l_org = ASG_ORGif not ASG_ORG was defaulted then(

if l_org = 'Finance Department' then( l_manager_id = 100)else(l_manager_id = 101)

)return l_manager_id


4. Formula functions used in the sample formula: None13. Unit test script: N.A

Formula Type: Rate Periodization

1. Functional Usage: This formula gives complete control to the users to periodize therates. User can define their one rule using the contexts and input values available todistribute the rates among defined amount, annual amount and communicated amounts.

2. Navigation Path:Total Compensation > Rate /Coverage Definitions > Standard Rates.

3. Contexts: DATE_EARNED (p_effective_date), ASSIGNMENT_ID,

BUSINESS_GROUP_ID4. OverloadedContexts: No

5. Database items available: Database items based on persons assignment_id.



BEN_IV_CONVERT_FROM Char Possible values are DEFINED, CMCDand ANNUAL.If BEN_IV_CONVERT_FROM isDEFINED rules needs to compute theannual and communicated values usingthe input valueBEN_IV_CONVERT_FROM_VAL.

If BEN_IV_CONVERT_FROM isANNUAL rules needs to compute thedefined and communicated values usingthe input valueBEN_IV_CONVERT_FROM_VAL.


11/184

If BEN_IV_CONVERT_FROM isCMCD rules needs to compute thedefined and annual values using theinput valueBEN_IV_CONVERT_FROM_VAL.

BEN_IV_CONVERT_FROM_VAL Number Value passed in to the formula

BEN_ABR_IV_ACTY_BASE_RT_ID Number Activity base rate ID

BEN_EPE_IV_ELIG_PER_ELCTBL_CHC_ID

Number Electable choice ID

BEN_ECR_IV_ENRT_RT_ID Number This input value is NOT available aspart of Participation process. But thiswill be available in the enrollmentprocess.



Out Variable name Data Type Value RemarksDFND_VAL Number mandatory out variable

ANN_VAL Number mandatory out variable

CMCD_VAL Number mandatory out variable

1. Erroring: If all the three values are not returned by the formula, the process throws anappropriate error message.

2. Remarks:

3. Sample Formula:

/**********************************************************************

FORMULA NAME: BEN_RT_PERIODIZE_RLFORMULA TYPE : Oracle Advanced BenefitsDESCRIPTION :***********************************************************************Change History:Name Date Description-----------------------------------------------------------------------Nagaraj Hunur 22-Dec-2004 Initial Version


**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*//*=========== DATABASE ITEM DEFAULTS ENDS======================*/


/*================= INPUTS SECTION BEGIN ========================*/INPUTS ARE BEN_IV_CONVERT_FROM (text),


12/184

BEN_IV_CONVERT_FROM_VAL,BEN_ABR_IV_ACTY_BASE_RT_ID,BEN_EPE_IV_ELIG_PER_ELCTBL_CHC_ID,BEN_ECR_IV_ENRT_RT_ID

/*================== INPUTS SECTION ENDS ========================*/

/*================ FORMULA SECTION BEGIN =======================*/IF BEN_IV_CONVERT_FROM = 'DEFINED' THEN(

DFND_VAL = BEN_IV_CONVERT_FROM_VALANN_VAL = DFND_VAL * 12CMCD_VAL = ANN_VAL / 24

)ELSE

(IF BEN_IV_CONVERT_FROM = 'ANNUAL' THEN(

ANN_VAL = BEN_IV_CONVERT_FROM_VALDFND_VAL = ANN_VAL / 12

CMCD_VAL = ANN_VAL / 24)ELSE /* COMMUNICATED AMOUNT */(CMCD_VAL = BEN_IV_CONVERT_FROM_VALDFND_VAL = CMCD_VAL * 24 /12ANN_VAL = CMCD_VAL * 24

)

)

Return DFND_VAL, ANN_VAL, CMCD_VAL




Formula Type: Person change causes Life event

Functional Usage: Life events are triggered based on person changes attached to the life event. If

customers need to write code to decide whether data change happened or not even if columnchange occurs, a rule can be attached. Output of rule and column change must be satisfied totrigger life event.

Navigation Path:Person Changes > Define Person Change > "Person Changes Window" (Rule field)Person Changes > Define Related Person Change > "Related Person Changes Window" (Rulefield)

Contexts: Business_Group_Id, DATE_EARNED (p_effective_date), Assignment Id


13/184

Note: For any formula types, under contexts selection, if p_effective_date is indicatedthen this context is same as DATE_EARNED

OverloadedContexts: No

Database items available:Data base items based on persons assignment_id.

Input values available:

Based on the table on which Person changes are defined, different set ofinput values are available for use in a formula. Input values NEW_VAL,

OLD_VAL, COLUMN, PK_ID are available irrespective of table on whichdata changes are defined. Below given example explains what values theseinput variables contain.

Ex., Assume data changes are defined on table PER_ALL_PEOPLE_F and ColumnMARITAL_STAUS

A person's marital status is changed from S (single) to M (Married)COLUMN input value is set with column name i.e., MARITAL_SATUS.NEW_VAL input value is set with new value for column marital_status

for the current person record. it's value is M.OLD_VAL input value is set with old value for column marital_status

for the current person record. it's value is S.

PK_VAL input value is set with person_id (Primary key of the row being

modified.)

Following table describes the input values available for each table.

Table on which person changes are defined

Available input values

PER_ABSENCE_ATTENDANCES PK_VAL contains ABSENCE_ATTENDANCE_IDBEN_ABA_IN_PERSON_ID, BEN_ABA_IN_PERSON_ID,

BEN_ABA_IO_PERSON_ID,BEN_ABA_IN_DATE_START,

BEN_ABA_IO_DATE_START, BEN_ABA_IN_DATE_END,BEN_ABA_IO_DATE_END,

BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID,BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID,

BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID,BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID,

BEN_ABA_IN_SICKNESS_START_DATE,

BEN_ABA_IO_SICKNESS_START_DATE,BEN_ABA_IN_SICKNESS_END_DATE,

BEN_ABA_IO_SICKNESS_END_DATE,BEN_ABA_IN_ABSENCE_DAYS,

BEN_ABA_IO_ABSENCE_DAYS,BEN_ABA_IN_ABSENCE_HOURS,

BEN_ABA_IO_ABSENCE_HOURS,BEN_ABA_IN_DATE_NOTIFICATION,

BEN_ABA_IO_DATE_NOTIFICATION,

BEN_ABA_IN_DATE_PROJECTED_END,BEN_ABA_IO_DATE_PROJECTED_END,

BEN_ABA_IN_DATE_PROJECTED_START,BEN_ABA_IO_DATE_PROJECTED_START,

BEN_ABA_IN_TIME_END, BEN_ABA_IO_TIME_END,BEN_ABA_IN_TIME_PROJECTED_END,


14/184

BEN_ABA_IO_TIME_PROJECTED_END,BEN_ABA_IN_TIME_PROJECTED_START,

BEN_ABA_IO_TIME_PROJECTED_START,BEN_LER_ID,

BEN_ABA_IN_ABS_INFORMATION_CATEGORY,BEN_ABA_IN_ABS_INFORMATION1,

BEN_ABA_IN_ABS_INFORMATION2,BEN_ABA_IN_ABS_INFORMATION3,BEN_ABA_IN_ABS_INFORMATION4,

BEN_ABA_IN_ABS_INFORMATION5,BEN_ABA_IN_ABS_INFORMATION6,




BEN_ABA_IN_ABS_INFORMATION14,






BEN_ABA_IN_ABS_INFORMATION27,

BEN_ABA_IN_ABS_INFORMATION28,BEN_ABA_IN_ABS_INFORMATION29,BEN_ABA_IN_ABS_INFORMATION30,BEN_ABA_IN_ATTRIBUTE_CATEGORY,

BEN_ABA_IN_ATTRIBUTE1,BEN_ABA_IN_ATTRIBUTE2, BEN_ABA_IN_ATTRIBUTE3,




BEN_ABA_IN_ATTRIBUTE13,BEN_ABA_IN_ATTRIBUTE14, BEN_ABA_IN_ATTRIBUTE15

PER_ASSIGNMENT_BUDGET_VALUES_F PK_VAL contains ASSIGNMENT_BUDGET_VALUES_IDBEN_ABV_IN_EFFECTIVE_START_DATE,

BEN_ABV_IO_EFFECTIVE_START_DATE,BEN_ABV_IN_EFFECTIVE_END_DATE,

BEN_ABV_IO_EFFECTIVE_END_DATE

PER_ADDRESSES PK_VAL contains address_idBEN_ADR_IN_DATE_FROM, BEN_ADR_IN_DATE_TO,

BEN_ADR_IN_PRIMARY_FLAG,

BEN_ADR_IN_POSTAL_CODE,


15/184

BEN_ADR_IN_REGION_2,BEN_ADR_IN_ADDRESS_TYPE,

BEN_ADR_IO_POSTAL_CODE,BEN_ADR_IO_DATE_FROM, BEN_ADR_IO_DATE_TO,

BEN_ADR_IO_PRIMARY_FLAG, BEN_ADR_IO_REGION_2,BEN_ADR_IO_ADDRESS_TYPE,

BEN_ADR_IN_PERSON_ID, BEN_ADR_IO_PERSON_IDPER_ALL_ASSIGNMENTS_F PK_VAL contains ASSIGNMENT_ID

BEN_ASG_IN_EFFECTIVE_START_DATE,BEN_ASG_IO_EFFECTIVE_START_DATE,

BEN_ASG_IN_EFFECTIVE_END_DATE,BEN_ASG_IO_EFFECTIVE_END_DATE,

BEN_ASG_IN_ORGANIZATION_ID,BEN_ASG_IO_ORGANIZATION_ID,

BEN_ASG_IN_GRADE_ID, BEN_ASG_IO_GRADE_ID,BEN_ASG_IN_JOB_ID, BEN_ASG_IO_JOB_ID,

BEN_ASG_IN_PAYROLL_ID,BEN_ASG_IN_LOCATION_ID,

BEN_ASG_IO_LOCATION_ID,

BEN_ASG_IN_ASSIGNMENT_STATUS_TYPE_ID,BEN_ASG_IO_ASSIGNMENT_STATUS_TYPE_ID,

BEN_ASG_IN_ASSIGNMENT_TYPE,BEN_ASG_IO_ASSIGNMENT_TYPE,

BEN_ASG_IN_PRIMARY_FLAG,BEN_ASG_IO_PRIMARY_FLAG,

BEN_ASG_IN_EMPLOYMENT_CATEGORY,BEN_ASG_IO_EMPLOYMENT_CATEGORY,

BEN_ASG_IN_BARGAINING_UNIT_CODE,BEN_ASG_IO_BARGAINING_UNIT_CODE,

BEN_ASG_IN_HOURLY_SALARIED_CODE,

BEN_ASG_IO_HOURLY_SALARIED_CODE,BEN_ASG_IN_NORMAl_HOURS,

BEN_ASG_IO_NORMAl_HOURSPER_COMPETENCE_ELEMENTS PK_VAL contains COMPETENCE_ELEMENT_IDBEN_CEL_IN_EFFECTIVE_DATE_FROM,BEN_CEL_IO_EFFECTIVE_DATE_FROM,

BEN_CEL_IN_EFFECTIVE_DATE_TO,BEN_CEL_IO_EFFECTIVE_DATE_TO,

BEN_CEL_IN_COMPETENCE_ID,BEN_CEL_IO_COMPETENCE_ID,

BEN_CEL_IN_PROFICIENCY_LEVEL_ID,BEN_CEL_IO_PROFICIENCY_LEVEL_ID,

BEN_CEL_IN_ATTRIBUTE1, BEN_CEL_IO_ATTRIBUTE1,BEN_CEL_IN_ATTRIBUTE2,

BEN_CEL_IO_ATTRIBUTE2, BEN_CEL_IN_ATTRIBUTE3,

BEN_CEL_IO_ATTRIBUTE3,BEN_CEL_IN_ATTRIBUTE4, BEN_CEL_IO_ATTRIBUTE4,BEN_CEL_IN_ATTRIBUTE5,

BEN_CEL_IO_ATTRIBUTE5, BEN_CEL_IN_ATTRIBUTE6,

BEN_CEL_IO_ATTRIBUTE6,BEN_CEL_IN_ATTRIBUTE7, BEN_CEL_IO_ATTRIBUTE7,

BEN_CEL_IN_ATTRIBUTE8,BEN_CEL_IO_ATTRIBUTE8, BEN_CEL_IN_ATTRIBUTE9,

BEN_CEL_IO_ATTRIBUTE9,

BEN_CEL_IN_ATTRIBUTE10, BEN_CEL_IO_ATTRIBUTE10,


16/184

BEN_CEL_IN_ATTRIBUTE11,BEN_CEL_IO_ATTRIBUTE11

PER_CONTACT_RELATIONSHIPS PK_VAL contains CONTACT_RELATIONSHIP_ID

BEN_CON_IN_PERSON_ID, BEN_CON_IO_PERSON_ID,BEN_CON_IN_CONTACT_PERSON_ID,

BEN_CON_IO_CONTACT_PERSON_ID,

BEN_CON_IN_DATE_START, BEN_CON_IO_DATE_START,BEN_CON_IN_DATE_END, BEN_CON_IO_DATE_END,

BEN_CON_IN_CONTACT_TYPE,BEN_CON_IO_CONTACT_TYPE,

BEN_CON_IN_PERSONAL_FLAG,BEN_CON_IO_PERSONAL_FLAG,

BEN_CON_IN_START_LIFE_REASON_ID,BEN_CON_IO_START_LIFE_REASON_ID,

BEN_CON_IN_END_LIFE_REASON_ID,BEN_CON_IO_END_LIFE_REASON_ID,

BEN_CON_IN_RLTD_PER_RSDS_W_DSGNTR_FLAG,BEN_CON_IO_RLTD_PER_RSDS_W_DSGNTR_FLAG

BEN_CRT_ORDR PK_VAL contains CRT_ORDR_ID

BEN_CRT_IN_APLS_PERD_STRTG_DT,BEN_CRT_IO_APLS_PERD_STRTG_DT,

BEN_CRT_IN_APLS_PERD_ENDG_DT,

BEN_CRT_IO_APLS_PERD_ENDG_DT,BEN_CRT_IN_CRT_ORDR_TYP_CD,

BEN_CRT_IO_CRT_ORDR_TYP_CD,BEN_CRT_IN_RCVD_DT, BEN_CRT_IO_RCVD_DT,

BEN_CRT_IN_PL_ID, BEN_CRT_IO_PL_ID,BEN_CRT_IN_PL_TYP_ID, BEN_CRT_IO_PL_TYP_ID,BEN_CRT_IN_PERSON_ID, BEN_CRT_IO_PERSON_ID

BEN_ELIG_CVRD_DPNT_F PK_VAL contains ELIG_CVRD_DPNT_IDBEN_PDP_IN_PRTT_ENRT_RSLT_ID,BEN_PDP_IO_PRTT_ENRT_RSLT_ID,

BEN_PDP_IN_EFFECTIVE_START_DATE,BEN_PDP_IO_EFFECTIVE_START_DATE,

BEN_PDP_IN_EFFECTIVE_END_DATE,BEN_PDP_IO_EFFECTIVE_END_DATE,

BEN_PDP_IN_CVG_STRT_DT,

BEN_PDP_IO_CVG_STRT_DT, BEN_PDP_IN_CVG_THRU_DT,BEN_PDP_IO_CVG_THRU_DT,

BEN_PDP_IN_OVRDN_FLAG, BEN_PDP_IO_OVRDN_FLAG,BEN_PDP_IN_OVRDN_THRU_DT,

BEN_PDP_IO_OVRDN_THRU_DT

PAY_ELEMENT_ENTRY_VALUES_F None

BEN_ELIG_DPNT PK_VAL contains ELIG_DPNT_ID

BEN_EGD_IN_DPNT_PERSON_ID,

BEN_EGD_IO_DPNT_PERSON_ID,BEN_EGD_IN_ELIG_STRT_DT,

BEN_EGD_IO_ELIG_STRT_DT, BEN_EGD_IN_ELIG_THRU_DT,

BEN_EGD_IO_ELIG_THRU_DT,BEN_EGD_IN_DPNT_INELIG_FLAG,BEN_EGD_IO_DPNT_INELIG_FLAG,

BEN_EGD_IN_INELG_RSN_CD,BEN_EGD_IO_INELG_RSN_CD,

BEN_EGD_IN_ELIG_PER_ELCTBL_CHC_ID,


17/184

BEN_EGD_IO_ELIG_PER_ELCTBL_CHC_ID,BEN_EGD_IN_PER_IN_LER_ID,

BEN_EGD_IO_PER_IN_LER_ID,BEN_EGD_IN_ELIG_PER_ID, BEN_EGD_IO_ELIG_PER_ID,

BEN_EGD_IN_ELIG_PER_OPT_ID,BEN_EGD_IO_ELIG_PER_OPT_ID,

BEN_EGD_IN_ELIG_CVRD_DPNT_ID,BEN_EGD_IO_ELIG_CVRD_DPNT_ID

BEN_PER_BNFTS_BAL_F PK_VAL contains PER_BNFTS_BAL_IDBEN_PBB_IN_PERSON_ID, BEN_PBB_IO_PERSON_ID,BEN_PBB_IN_EFFECTIVE_START_DATE,

BEN_PBB_IO_EFFECTIVE_START_DATE,

BEN_PBB_IN_EFFECTIVE_END_DATE,BEN_PBB_IO_EFFECTIVE_END_DATE,

BEN_PBB_IN_VAL, BEN_PBB_IO_VAL,BEN_PBB_IN_BNFTS_BAL_ID,

BEN_PBB_IO_BNFTS_BAL_ID

BEN_PRTT_ENRT_RSLT_F PK_VAL contains PRTT_ENRT_RSLT_IDBEN_PEN_IN_ENRT_CVG_STRT_DT,

BEN_PEN_IO_ENRT_CVG_STRT_DT,BEN_PEN_IN_ENRT_CVG_THRU_DT,

BEN_PEN_IO_ENRT_CVG_THRU_DT,

BEN_PEN_IN_EFFECTIVE_START_DATE,BEN_PEN_IO_EFFECTIVE_START_DATE,

BEN_PEN_IN_EFFECTIVE_END_DATE,BEN_PEN_IO_EFFECTIVE_END_DATE

PER_ALL_PEOPLE_F PK_VAL contains PERSON_IDBEN_PPF_IN_EFFECTIVE_START_DATE,

BEN_PPF_IO_EFFECTIVE_START_DATE,BEN_PPF_IN_EFFECTIVE_END_DATE,

BEN_PPF_IO_EFFECTIVE_END_DATE,BEN_PPF_IN_DATE_OF_BIRTH,

BEN_PPF_IO_DATE_OF_BIRTH,BEN_PPF_IN_DATE_OF_DEATH,

BEN_PPF_IO_DATE_OF_DEATH,BEN_PPF_IN_MARITAL_STATUS,

BEN_PPF_IO_MARITAL_STATUS,

BEN_PPF_IN_STUDENT_STATUS,BEN_PPF_IO_STUDENT_STATUS,

BEN_PPF_IN_BENEFIT_GROUP_ID,BEN_PPF_IO_BENEFIT_GROUP_ID,

BEN_PPF_IN_ORIGINAL_DATE_OF_HIRE,BEN_PPF_IO_ORIGINAL_DATE_OF_HIRE

PER_PERFORMANCE_REVIEWS PK_VAL contains PERFORMANCE_REVIEW_IDBEN_PPR_IN_PERFORMANCE_RATING,

BEN_PPR_IO_PERFORMANCE_RATING,BEN_PPR_IN_EVENT_ID, BEN_PPR_IO_EVENT_ID,

BEN_PPR_IN_REVIEW_DATE,BEN_PPR_IO_REVIEW_DATE,

BEN_PPR_IN_ATTRIBUTE1,BEN_PPR_IO_ATTRIBUTE1,


BEN_PPR_IN_ATTRIBUTE3,


18/184

BEN_PPR_IO_ATTRIBUTE3,BEN_PPR_IN_ATTRIBUTE4,



BEN_PPR_IO_ATTRIBUTE6,BEN_PPR_IN_ATTRIBUTE7,BEN_PPR_IO_ATTRIBUTE7,





BEN_PPR_IN_ATTRIBUTE12,BEN_PPR_IO_ATTRIBUTE12

PER_PAY_PROPOSALS PK_VAL contains PAY_PROPOSAL_IDBEN_PRO_IN_CHANGE_DATE, BEN_PRO_IO_CHANGE_DATE,

BEN_PRO_IN_LAST_CHANGE_DATE,BEN_PRO_IO_LAST_CHANGE_DATE,

BEN_PRO_IN_NEXT_PERF_REVIEW_DATE,BEN_PRO_IO_NEXT_PERF_REVIEW_DATE,

BEN_PRO_IN_NEXT_SAL_REVIEW_DATE,BEN_PRO_IO_NEXT_SAL_REVIEW_DATE,

BEN_PRO_IN_PERFORMANCE_RATING,BEN_PRO_IO_PERFORMANCE_RATING,

BEN_PRO_IN_PROPOSAL_REASON,

BEN_PRO_IO_PROPOSAL_REASON,BEN_PRO_IN_PROPOSED_SALARY_N,

BEN_PRO_IO_PROPOSED_SALARY_N,BEN_PRO_IN_REVIEW_DATE,

BEN_PRO_IO_REVIEW_DATE,BEN_PRO_IN_APPROVED,

BEN_PRO_IO_APPROVED,BEN_PRO_IN_MULTIPLE_COMPONENTS,

BEN_PRO_IO_MULTIPLE_COMPONENTS,

BEN_PRO_IN_FORCED_RANKING,BEN_PRO_IO_FORCED_RANKING,

BEN_PRO_IN_PERFORMANCE_REVIEW_ID,BEN_PRO_IO_PERFORMANCE_REVIEW_ID,

BEN_PRO_IN_EVENT_ID,BEN_PRO_IO_EVENT_ID,

BEN_PRO_IN_PROPOSED_SALARY,BEN_PRO_IO_PROPOSED_SALARY,BEN_PRO_IN_ATTRIBUTE1,

BEN_PRO_IO_ATTRIBUTE1

BEN_PRTT_RT_VAL PK_VAL contains PRTT_RT_VAL_IDBEN_PRV_IN_PRTT_ENRT_RSLT_ID,BEN_PRV_IO_PRTT_ENRT_RSLT_ID,

BEN_PRV_IN_RT_STRT_DT,BEN_PRV_IO_RT_STRT_DT, BEN_PRV_IN_RT_END_DT,

BEN_PRV_IO_RT_END_DT, BEN_PRV_IN_RT_VAL,


19/184

BEN_PRV_IO_RT_VAL,BEN_PRV_IN_TX_TYP_CD, BEN_PRV_IO_TX_TYP_CD,

BEN_PRV_IN_ACTY_TYP_CD,BEN_PRV_IO_ACTY_TYP_CD,

BEN_PRV_IN_PER_IN_LER_ID, BEN_PRV_IO_PER_IN_LER_ID,BEN_PRV_IN_ACTY_BASE_RT_ID,

BEN_PRV_IO_ACTY_BASE_RT_ID,BEN_PRV_IN_PRTT_RT_VAL_STAT_CD,BEN_PRV_IO_PRTT_RT_VAL_STAT_CD

PER_PERIODS_OF_SERVICE None

PER_PERSON_TYPE_USAGES_F PK_VAL contains PERSON_TYPE_USAGE_IDBEN_PTU_IN_PERSON_TYPE_USAGE_ID,BEN_PTU_IO_PERSON_TYPE_USAGE_ID,

BEN_PTU_IN_EFFECTIVE_START_DATE,BEN_PTU_IO_EFFECTIVE_START_DATE,

BEN_PTU_IN_EFFECTIVE_END_DATE,BEN_PTU_IO_EFFECTIVE_END_DATE,

BEN_PTU_IN_PERSON_ID, BEN_PTU_IO_PERSON_ID

PER_QUALIFICATIONS PK_VAL contains QUALIFICATION_ID

BEN_QUA_IN_QUALIFICATION_TYPE_ID,BEN_QUA_IO_QUALIFICATION_TYPE_ID,

BEN_QUA_IN_TITLE, BEN_QUA_IO_TITLE,BEN_QUA_IN_START_DATE,

BEN_QUA_IO_START_DATE,BEN_QUA_IN_END_DATE, BEN_QUA_IO_END_DATE,BEN_QUA_IN_ATTRIBUTE1,

BEN_QUA_IO_ATTRIBUTE1,BEN_QUA_IN_ATTRIBUTE2,





BEN_QUA_IO_ATTRIBUTE6,

BEN_QUA_IN_ATTRIBUTE7,BEN_QUA_IO_ATTRIBUTE7,




BEN_QUA_IN_ATTRIBUTE11,BEN_QUA_IO_ATTRIBUTE11

PER_PERSON_ANALYSES PK_VAL contains PERSON_ANALYSIS_IDBEN_PAC_IN_SEGMENT1BEN_PAC_IO_SEGMENT1BEN_PAC_IN_SEGMENT2

BEN_PAC_IO_SEGMENT2BEN_PAC_IN_SEGMENT3BEN_PAC_IO_SEGMENT3


20/184

BEN_PAC_IN_SEGMENT4BEN_PAC_IO_SEGMENT4

BEN_PAC_IN_SEGMENT5BEN_PAC_IO_SEGMENT5BEN_PAC_IN_SEGMENT6BEN_PAC_IO_SEGMENT6

BEN_PAC_IN_SEGMENT7BEN_PAC_IO_SEGMENT7BEN_PAC_IN_SEGMENT8BEN_PAC_IO_SEGMENT8BEN_PAC_IN_SEGMENT9

BEN_PAC_IO_SEGMENT9'BEN_PAC_IN_SEGMENT10BEN_PAC_IO_SEGMENT10BEN_PAC_IN_SEGMENT11

BEN_PAC_IO_SEGMENT11BEN_PAC_IN_SEGMENT12BEN_IV_LER_IDBEN_PAC_IN_DATE_FROM

BEN_PAC_IO_DATE_FROMBEN_PAC_IN_DATE_TOBEN_PAC_IO_DATE_TOBEN_PAC_IN_ANALYSIS_CRITERIA_IDBEN_PAC_IO_ANALYSIS_CRITERIA_ID

Naming convention for input values :Ex., BEN_PPF_IN_MARITAL_STATUS - First 3 letter stands for benefits, next 3letters(PPF) stands for the table short name.I - stands for Input valueN - Stands for New value; O Stands for Old value.

Remaining portion of the name stands for the column name.If this input value is used in the formula it will contain new value of marital status column of

the person row which being currently updated.

Constraints using Database items:Database items other than the ones mentioned above cannot be used.

Formula output or return values:No need to use predefined names for return variables.

Out Variable name

Data

Type

Value Remarks

L_RETURN(Any name can be used)

Varchar2 NY

Do not create the potentialIf data changes are satisfied createthe potential life event

Erroring: If wrong values are passed back from the formula value is assumed as Y.Remarks : In case the person changes causes is based on PER_PERSON_ANALYSES , theformula has to return 2 output variables as indicated below.

Out Variable name

Data Type Value Remarks

RETURN_FLAG Varchar2 NY

Do not create the potentialIf data changes are satisfied createthe potential life event.


21/184

LE_OCCURED_DATE Varchar2 Life event occurred date, incanonical form. This variable is not

mandatory.

Sample Formula :/*****************************************************************

FORMULA NAME :FORMULA TYPE : Person Changes CausesDESCRIPTION : Formula to determine whether to trigger potential life event.

*****************************************************************Change History:Name Date Description-----------------------------------------------------------------------

Prasad Bodla 29-Aug-2003 Initial Version

******************************************************************FORMULA TEXT:*****************************************************************/

/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/

/*=========== DATABASE ITEM DEFAULTS ENDS======================*//*============ INPUT VALUES DEFAULT BEGIN ======================*/inputs are NEW_VAL(text), OLD_VAL(text), COLUMN(text), PK_ID(text),

BEN_PPF_IN_MARITAL_STATUS(text), BEN_PPF_IO_MARITAL_STATUS(text)default for BEN_PPF_IN_MARITAL_STATUS is 'AA'default for BEN_PPF_IO_MARITAL_STATUS is 'AA'


/*================= INPUTS SECTION BEGIN ========================*//*================== INPUTS SECTION ENDS ========================*//*================ FORMULA SECTION BEGIN =======================*/

l_new_marital_status = BEN_PPF_IN_MARITAL_STATUS

l_old_marital_status = BEN_PPF_IO_MARITAL_STATUS

/* Create potential life event if person marital status is chaged fromSingle to married */

if l_new_marital_status = 'S' and l_old_marital_status = 'M' thenl_create_ptnl = 'Y'

else

l_create_ptnl = 'N'

return l_create_ptnl


Formula functions used in the sample formula :None

Unit test script :

Formula Type: Rate Value Calculation (When calculation method on standard rates

form is post enrollment calculation rule)


22/184

Functional Usage: When a rate needs to be computed based on several other rates at the time ofparticipation process run and during the enrollments.

Navigation Path: Total Compensation > Rate/Coverage Definitions:Standard Rates > CalculationMethod : Post Enrollment Calculation RuleTotal Compensation > Rate/Coverage Definitions: Coverages > Calculation Method : PostEnrollment Calculation Rule

Contexts: Business_Group_Id, DATE_EARNED (p_effective_date), Assignment Id

Note: For any formula types, under contexts selection, if p_effective_date is indicatedthen this context is same as DATE_EARNED

OverloadedContexts: No

Database items available:Data base items based on persons assignment_id.

Input values available:

Constraints using Database items:Database items other than the ones mentioned above cannot be used.

Formula output or return values:No need to use predefined names for return variables.

Out Variable nameData Type Value Remarks

L_RETURN(Any name can be used)

Number NY

Value of rate

Erroring:

If the value passed back is not a number, error is raised..

Remarks : Formula cant use values of rates attached to same compensation object. If two ratesare defined with post enrollment calculation rule, they cant be referenced in each other.

Sample Formula :/*****************************************************************

FORMULA NAME : VALUE_RULEFORMULA TYPE : Rate Value Calculation

DESCRIPTION : Formula to return a value for rate.*****************************************************************Change History:Name Date Description-----------------------------------------------------------------------

Prasad Bodla 29-Aug-2003 Initial Version

******************************************************************FORMULA TEXT:

*****************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/

/*=========== DATABASE ITEM DEFAULTS ENDS======================*//*============ INPUT VALUES DEFAULT BEGIN ======================*/

/*============== INPUT VALUES DEFAUT ENDS ======================*//*================= INPUTS SECTION BEGIN ========================*//*================== INPUTS SECTION ENDS ========================*//*================ FORMULA SECTION BEGIN =======================*/l_val = ilo_enrt_rt()

return l_val/*================ FORMULA SECTION END =======================*/

Formula functions used in the sample formula :


23/184

Ilo_enrt_rt :create or replace function get_enrt_rt_val(

p_assignment_id in number,p_business_group_id in number,p_effective_date in date

) return number is

--l_ACTY_BASE_RT_ID number;cursor c_abr(cv_abr_name varchar2) is

select ACTY_BASE_RT_IDfrom ben_acty_base_rt_f abrwhere name = cv_abr_name

and abr.business_group_id = p_business_group_id;--

l_elig_per_elctbl_chc_id number;cursor c_epe is

select epe.elig_per_elctbl_chc_idfrom

ben_per_in_ler pil,

per_all_assignments_f asg,ben_elig_per_elctbl_chc epewhere

epe.business_group_id = p_business_group_id

and epe.pl_id = 22 and epe.oipl_id = 24and asg.person_id = pil.person_idand pil.per_in_ler_stat_cd = 'STRTD'-- Also need to filter out the non ben life events.

and pil.per_in_ler_id = epe.per_in_ler_idand asg.assignment_id = p_assignment_idand pil.lf_evt_ocrd_dt between asg.effective_start_date

and asg.effective_end_date;--

cursor c_enrt_rt(cv_abr_name varchar2) isselect ecr.valfrom ben_enrt_rt ecr,

ben_per_in_ler pil,

per_all_assignments_f asg,ben_acty_base_rt_f abr,ben_elig_per_elctbl_chc epe

where abr.name = cv_abr_nameand abr.business_group_id = p_business_group_id

and abr.ACTY_BASE_RT_ID = ecr.ACTY_BASE_RT_IDand epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_idand epe.pl_id = 22 and epe.oipl_id = 24and asg.person_id = pil.person_id

and pil.per_in_ler_stat_cd = 'STRTD'-- Also need to filter out the non ben life events.and pil.per_in_ler_id = epe.per_in_ler_idand asg.assignment_id = p_assignment_idand pil.lf_evt_ocrd_dt between asg.effective_start_date

and asg.effective_end_dateand pil.lf_evt_ocrd_dt between abr.effective_start_date

and abr.effective_end_date;--

--


24/184

l_val number := 0;l_val1 number := 0;l_val2 number := 0;--

begin

--

-- Please note that this is sample code and it contains hard coded values.-- Some of the cursors defined are also used.-- If benefit amounts are defined for a comp objects then above cursors may not work.

--open c_enrt_rt('OAB Stay Health HMO - EE Only ER Contrib');fetch c_enrt_rt into l_val1;close c_enrt_rt;--

open c_enrt_rt('OAB Stay Health Detal EE Only ERContrib');fetch c_enrt_rt into l_val2;close c_enrt_rt;--

l_val := (l_val1 + l_val2)* 0.5;

return l_val;--end get_enrt_rt_val;/

Unit test script :

Formula Type : Evaluate Life Event

1. Functional Usage : A company may want to customize how a potential life event is to beevaluated. For example Return to Work Life event is created by the customer and if theemployee returns to the work, this life event is automatically created. Customer wants a logic tobe executed when this potential life event is evaluated and based on that logic potential life

events occured date have to be modified or this potential life event have to be voided.

2. Navigation Path : Total Compensation > General Definitions > Life Event Reasons : Evaluation Rule

3. Contexts : DATE_EARNED (p_effective_date), LER_ID, ASSIGNMENT_ID,ORGANIZATION_ID, BUSINESS_GROUP_ID

4. OverloadedContexts : No

5. Database items available : All columns from ben_ler_f and data base items based on persons

assignment_id.


Name Data

Type

Remarks

BEN_PPL_IV_PTNL_LER_FOR_PER_ID Char Primary key Potential Life

Event.BEN_PPL_IV_LF_EVT_OCRD_DT Char Life event Occured Date passed

in formatYYYY/MM/DD HH24:MI:SS

BEN_PPL_IV_PTNL_LER_FOR_PER_STAT_CD Char Potential Life event StatusCode

BEN_PPL_IV_NTFN_DT Char Notified Date in format

YYYY/MM/DD HH24:MI:SS

BEN_PPL_IV_DTCTD_DT Char Detected Date in format


25/184


6. Constraints using Database items : Data base items other than the ones mentioned cant be

used.

7. Formula output or return values : Formula should use pre defined names for return variables.

Out Variable name Data

Type

Value Remarks

LIFE_EVENT_OCCURRED_DATE Char - Replaces the original life event occureddate. Format of return value :


LIFE_EVENT_HAPPENED

(manadatory out variable)

Char Y/N If any other value is assigned to this

variable process raises an error -BEN_92143_LIFE_EVENT_HAPPENED

LIFE_EVENT_REASON_ID

LIFE_EVENT_NOTIFICATION_DA

TELIFE_EVENT_DETECTED_DATE

LIFE_EVENT_VOIDED_DATE

LIFE_EVENT_MANUAL_DATE

LIFE_EVENT_STATUS_CODE

8. Erroring : If wrong values are passed back from the formula does it gracefully exit or if errorswhat kind of error is raised. : If any other output variables are used then processerrors(BEN_92310_FORMULA_RET_PARAM).

9. Remarks :

10. Sample Formula :/**********************************************************************FORMULA NAME: BEN_TEST_LE_EVAL_RLFORMULA TYPE : Oracle Advanced BenefitsDESCRIPTION : Formula evaluates the potential life event.

If a life event is notified after 30 days ignore thispotential life event.

***********************************************************************Change History:

Name Date Description-----------------------------------------------------------------------Prasad Bodla 22-Feb-2002 Initial Version

***********************************************************************FORMULA TEXT : Logic in simple terms.

**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/

/*=========== DATABASE ITEM DEFAULTS ENDS======================*/


default for BEN_PPL_IV_LF_EVT_OCRD_DT is ' '


26/184

default for BEN_PPL_IV_PTNL_LER_FOR_STAT_CD is ' 'default for BEN_PPL_IV_NTFN_DT is ' '


/*================= INPUTS SECTION BEGIN ========================*/

Inputs are BEN_PPL_IV_LF_EVT_OCRD_DT (TEXT),BEN_PPL_IV_PTNL_LER_FOR_STAT_CD (TEXT),

BEN_PPL_IV_NTFN_DT (TEXT)

/*================== INPUTS SECTION ENDS ========================*//*================ FORMULA SECTION BEGIN =======================*/

l_lf_evt_dt = to_date(BEN_PPL_IV_LF_EVT_OCRD_DT, 'YYYY/MM/DD HH24:MI:SS')l_ntfd_dt = to_date(BEN_PPL_IV_NTFN_DT, 'YYYY/MM/DD HH24:MI:SS')l_lf_evt_dt_30 = add_days(l_lf_evt_dt, 30)

if (l_ntfd_dt >l_lf_evt_dt_30) then

life_event_happened = 'N'return life_event_happened

11. Formula functions used in the sample formula : None

12. Unit test script :

Formula Type : Waiting Period Value and UOM

13. Functional Usage : Participant have to wait for certain time before getting the eligibility to a

compensation object. Sometimes employees have to wait for 6 months to get eligibility to acompensation object. A waiting period value rule have to be used if the waiting period variesfrom employee to employee based on their organization or varies for rehired employees.

14. Navigation Path : Total Compensation > Programs and Plans > Plans : Plan Eligibility : Waiting Period Rule

1. Contexts: DATE_EARNED (p_effective_date), LER_ID, ASSIGNMENT_ID,ORGANIZATION_ID, BUSINESS_GROUP_ID, PGM_ID, PL_ID, PL_TYP_ID.


3. Database items available : All columns except attribute columns from tables ben_ler_f,ben_pl_f, ben_pl_typ_f, ben_opt_f, and data base items based on persons assignment_id.

4. Input values available : No Input values are available.

5. Constraints using Database items : Data base items other than the ones mentioned above cant

be used.

6. Formula output or return values : Formula should use pre defined names for return variables.


Type

Value Remarks

WAIT_PERD_VAL(manadatory outvariable)

Number -

WAIT_PERD_UOM (manadatory out Char One of the If this return value is not one of the


27/184

variable) codes : DY,WK, MO,

QTR, YR

codes then WAIT_PERD_VALwill be used as it is.

1. Erroring : If wrong values are passed back from the formula does it gracefully exit or if errors

what kind of error is raised. : If any other output variables are used then processerrors(BEN_92310_FORMULA_RETURN).

2. Remarks : This rule will only get executed, if Waiting Period Value and Waiting Period UOMfields are null.

3. Sample Formula :

4. /**********************************************************************FORMULA NAME: BEN_TEST_WAIT_PERD_VAL_RLFORMULA TYPE : Oracle Advanced Benefits

DESCRIPTION : Formula returns the waiting period value.***********************************************************************Change History:Name Date Description

-----------------------------------------------------------------------Prasad Bodla 22-Feb-2002 Initial Version

***********************************************************************FORMULA TEXT : Logic in simple terms.





/*================== INPUTS SECTION ENDS ========================*//*================ FORMULA SECTION BEGIN =======================*/

WAIT_PERD_VAL = 1WAIT_PERD_UOM = YRreturn WAIT_PERD_VAL, WAIT_PERD_UOM



Formula Type : Life Event Reason Timeliness (Timeliness Period Rule)

1. Functional Usage: Life events sometimes reported after certain number of days. In suchscenarios if customer wants to use complex logic to decide whether the potential life event

happened or not, this formula can be used.

2. Navigation Path: Total Compensation > General Definitions > Additional Setup > Life Event Reasons >

Timeliness Period Rule

3. Contexts: DATE_EARNED (p_effective_date), LER_ID, ASSIGNMENT_ID,ORGANIZATION_ID, BUSINESS_GROUP_ID.


28/184


5. Database items available: All columns except attribute columns from tables ben_ler_f and

database items based on persons assignment_id.

6. Input values available: No Input values are available.

7. Constraints using Database items: Database items other than the ones mentioned above cantbe used.

1. Formula output or return values: No need to use predefined names for return variables.


Type

Value Remarks

L_LIFEEVENT_VOIDED

(manadatory out variable, any namecan be used)

Varchar2 Y/N Y means the potential life event

will be voided. N means potentiallife event will be evaluated further.

1. Erroring: If wrong values are passed back from the formula does it gracefully exit or if errorswhat kind of error is raised. : If any other output value is passed back then processerrors(BEN_91329_FORMULA_RETURN).

2. Remarks : None3. Sample Formula :/**********************************************************************

FORMULA NAME: BEN_TIMELINESS_PERD_RLFORMULA TYPE : Oracle Advanced BenefitsDESCRIPTION :***********************************************************************

Change History:Name Date Description-----------------------------------------------------------------------Prasad Bodla 22-Feb-2002 Initial Version

***********************************************************************

FORMULA TEXT : Logic in simple terms.

**********************************************************************/

/*=========== DATABASE ITEM DEFAULTS BEGIN =====================*//*=========== DATABASE ITEM DEFAULTS ENDS======================*/





/*================ FORMULA SECTION BEGIN =======================*/l_life_event_voided = 'Y'return l_life_event_voided




29/184

Formula Type: Family Member Determination

8 Functional Usage: A company may want to customize how the family memberdetermination rule works. For example a company might want that any employee havingmore than two children, should not be eligible for a certain plan. Customer may want thislogic to be executed when eligibility profile is determined and based on the logic eligibility

to the plan can be determined.9 Navigation Path: Total Compensation -> Programs and Plans-> Plans: Family MemberRule: Option-> Family Member Rule

10 Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED,ORGANIZATION_ID, JURISDICTION_CODE, PGM_ID, PL_ID, PL_TYP_ID, OPT_ID,LER_ID

11 Overloaded Contexts: No

12 Data base items available: All columns except attribute columns from ben_ler_f,

ben_pgm_f, ben_pl_f, ben_pl_typ_f, ben_opt_f and data base items based on personsassignment_id.

13 Input values available: No Input values are available.

14 Constraints using Data Base items: Database items other than the ones mentioned cant beused.

15 Formula output or return values: Formula should use pre-defined names for returnvariables.

Out Variable Name Data

Type

Value Remarks

L_FMLY_MMBR(Not a mandatory out variable)

Char Y/N If N is passed exceptionBEN_92321_FMM_INELIG is raised

8 Erroring: None

9 Sample Formula

/**********************************************************************

FORMULA NAME: BEN_TEST_FMLY_MMBRFORMULA TYPE: Family member DeterminationDESCRIPTION:

***********************************************************************

Change History:Name Date Description-----------------------------------------------------------------------Vikas Sethi 22-apr-2002 Initial Version







30/184

/*================ FORMULA SECTION BEGIN =======================*/l_fmly_mmbr = FN_FMLY_MMBR_DETERMINATION()return l_fmly_mmbr

/*================ FORMULA SECTION ENDS =======================*/

10 Formula Functions used in the sample formula: Yes

/**********************************************************************FUNCTION NAME: BEN_TEST_FMLY_MMBRDATA TYPE : TextCLASS : External Function

ALIAS NAME :DESCRIPTION :DEFINITION : fn_fmly_mmbr_determinationCONTEXT : ASSIGNMENT_ID

BUSINESS_GROUP_ID

DATE_EARNEDPARAMTERS :

**********************************************************************/CREATE OR REPLACE FUNCTION FN_FMLY_MMBR_DETERMINATION

(p_asg_id IN number,p_business_group_id IN number,p_date_earned IN varchar2) RETURN varchar2 as

--l_rel_count number(2):= 0;

--cursor c_relationship isselect count(*)from per_all_assignments_f asg,

per_contact_relationships con,hr_lookups hlk

where asg.assignment_id = p_asg_idand asg.business_group_id = p_business_group_idand p_date_earned between asg.effective_start_date and

asg.effective_end_dateand con.person_id = asg.person_idand hlk.lookup_code = con.contact_typeand hlk.lookup_type = 'CONTACT'

and hlk.enabled_flag = 'Y'and p_date_earned between nvl(start_date_active, p_date_earned) and

nvl(end_date_active, p_date_earned)and hlk.lookup_code in (

A, -- Adopted Child

C, -- ChildOC ); -- Own Child

beginopen c_relationship;

fetch c_relationship into l_rel_count;close c_relationship;

If l_rel_count < 2 thenRETURN Y ;

ElseRETURN N;


31/184

End if;End ;

11 Unit test script:

============================================================================

Formula Type: Maximum Waiting Period Date to Use

Functional Usage: The Maximum Waiting Period Date to Use formula can be used to determine

the date from which maximum waiting period for a person is to be calculated.

12 Navigation Path: Total Compensation -> Programs and Plans-> Option-> Family MemberRule


14 OverloadedContexts: No

15 Database items available: All columns except attribute columns from tables ben_ler_f,

ben_pgm_f, ben_pl_f, ben_pl_typ_f, ben_opt_f and database items based on personsassignment_id.

16 Input values available: No Input values are available.17 Constraints using Database items: Database items other than the ones mentioned abovecant be used.

18 Formula output or return values: No need to use predefined names for return variables.


Type

Value Remarks

L_RET_DT(Not a mandatory out variable, anyname can be used)

Date

8 Erroring: If any other output value is passed back then process errors out by raising

BEN_91329_FORMULA_RETURN.9 Remarks: None

10 Sample Formula:/**********************************************************************

FORMULA NAME: BEN_MX_PER_TO_USEFORMULA TYPE : Maximum Waiting Period Date to UseDESCRIPTION : Formula evaluates the date from which the waiting period begins.

For managers the waiting period start date is the date of joining forOthers it is one year after the date of joining.

***********************************************************************Change History:Name Date Description-----------------------------------------------------------------------

Vikash Sethi 22-Feb-2002 Initial Version


**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/Default for ACP_SERVICE_START_DATE is '01-JAN-0001' (date)Default for ASG_JOB is ' '


32/184






/*================ FORMULA SECTION BEGIN =======================*/l_ser_start_dt = ACP_SERVICE_START_DATEl_job = ASG_JOBif l_job = 'Manager' then

( l_ret_dt = l_ser_start_dt )else( l_ret_dt = add_months(l_ser_start_dt,12) )return l_ret_dt


11 Formula functions used in the sample formula: None


============================================================================

Formula Type: Participation Eligibility Start Date

13 Functional Usage: The Participation Eligibility Start Date formula can be used to determine thedate from which a person becomes eligible to participate in particular option in a plan.

14 Navigation Path: Total Compensation -> Programs and Plans-> Plan: Plan Eligibility:Participation Start Date -> Option Eligibility, Life Eligibility: Participation Start Date

15 Total Compensation -> Programs and Plan->Program-> Plan and Plan Types: Plans: Participation

16 Eligibility: Participation Start Date: Rule17 Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED,ORGANIZATION_ID, JURISDICTION_CODE, PGM_ID, PL_ID, PL_TYP_ID, OPT_ID,LER_ID

18 OverloadedContexts: No

19 Database items available: All columns except attribute columns from tables ben_ler_f,ben_pgm_f, ben_pl_f, ben_pl_typ_f, ben_opt_f and database items based on persons

assignment_id.


21 Constraints using Database items: Database items other than the ones mentioned above cant beused.



Type

Value Remarks

L_PRTN_START_DT(Not a mandatory out variable,

any name can be used)

Date

8 Erroring: If any other output value is passed back then process errors out by raisingBEN_91329_FORMULA_RETURN.

9 Remarks: None


33/184

10 Sample Formula:/**********************************************************************

FORMULA NAME: BEN_PRTN_ELIG_START_DT

FORMULA TYPE : Participation Eligibility Start DateDESCRIPTION : Formula to determine persons eligibility start date in a particular plan option. It

returns the effective date as the eligibility start date

***********************************************************************Change History:

Name Date Description-----------------------------------------------------------------------Vikash Sethi 25-Apr-2002 Initial Version







l_prtn_start_dt = fn_effective_date()return l_prtn_start_dt


11 Formula functions used in the sample formula: FN_EFFECTIVE_DATE


Formula Type: Participation Eligibility End Date

13 Functional Usage: The Participation Eligibility End Date formula can be used to determine thedate on which a person loses eligibility to participate in particular option in a plan.

14 Navigation Path: Total Compensation -> Programs and Plans-> Plan: Plan Eligibility:Participation End Date -> Option Eligibility, Life Eligibility: Participation End DateTotal Compensation -> Programs and Plan->Program-> Plan and Plan Types: Plans: ParticipationEligibility: Participation End Date: Rule


16 OverloadedContexts: No17 Database items available: All columns except attribute columns from tables ben_ler_f,

ben_pgm_f, ben_pl_f, ben_pl_typ_f, ben_opt_f and database items based on personsassignment_id.





34/184

Out Variable NameData

Type

Value Remarks

L_PRTN_END_DT(Not a mandatory out variable,any name can be used)

Date


9 Remarks : None

10 Sample Formula:

/******************************************************************************FORMULA NAME: BEN_PRTN_ELIG_END_DTFORMULA TYPE : Participation Eligibility End DateDESCRIPTION : Formula to determine the participation eligibility end date for person in a

particular compensation object . It returns eligibility end date as two years from

the effective date.*******************************************************************************Change History:


***********************************************************************FORMULA TEXT:




/*================ FORMULA SECTION BEGIN =======================*/l_date_earned = fn_effective_date()l_prtn_end_dt = add_months(l_date_earned,24)

return l_prtn_end_dt/*================ FORMULA SECTION END =======================*/


12 Unit Test Script:

============================================================================


35/184

Formula Type: Waive Certification Required

Functional Usage: The Waive Certification Required formula can be used to define a custom logic to

evaluate if a certificate is to be produced if a person decides to waive a particular compensationobject.

Navigation Path: Total Compensation -> Programs and Plans-> Plan: Waiving: Waive Certificate:

Certification Required When Rule

Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED, ORGANIZATION_ID,JURISDICTION_CODE, PGM_ID, PL_ID, PL_TYP_ID, OPT_ID, LER_ID, SOURCE_TEXT

Overloaded Contexts: ORGANIZATION_ID is overloaded with ELIG_PER_ELCTBL_CHC_ID

Database items available: All columns except attribute columns from tables ben_ler_f, ben_pgm_f,ben_pl_f, ben_pl_typ_f, ben_opt_f and database items based on persons assignment_id.

Input values available:No Input values are available.

Constraints using Database items: Database items other than the ones mentioned above cant beused.

Formula output or return values: No need to use predefined names for return variables.


Type

Value Remarks

L_CERT_REQD(Not a mandatory variable, anyname can be used)

Char Y/N

8 Erroring: None

9 Remarks:None

10 Sample Formula:

/******************************************************************************

FORMULA NAME: BEN_WAIV_CERT_REQD

FORMULA TYPE : Waive Certification RequiredDESCRIPTION : Formula to evaluate if a certificate is to be produced when a person decides towaive a plan enrollment. In case the person is aged above 35 then certificate is not required

*******************************************************************************

Change History:Name Date Description-----------------------------------------------------------------------Vikash Sethi 25-Apr-2002 Initial Version




/*============== INPUT VALUES DEFAUT ENDS ======================*//*================= INPUTS SECTION BEGIN ========================*//*================== INPUTS SECTION ENDS ========================*/

/*================ FORMULA SECTION BEGIN =======================*/l_dob = fn_date_of_birth()l_effective_date = fn_effective_Date()l_age = trunc(months_between(l_effective_date, l_dob)/12)if l_age > 35 then


36/184

(l_cert_reqd = 'N')else(l_cert_reqd = 'Y')return l_cert_reqd


11 Formula functions used in the sample formula: FN_EFFECTIVE_DATE,

FN_DATE_OF_BIRTH


============================================================================

Formula Type: Participation and Rate Eligibility

Functional Usage: The Participation And Rate Eligibility formula can be used to determine whether a

person is eligible for an associated compensation object.

Navigation Path: Total Compensation -> Programs and Plans-> Plan: Plan Eligibility: Eligibility:Rule: Eligibility Profile Rule

Total Compensation -> Programs and Plan->Program-> Plan and Plan Types: ParticipationEligibility: Eligibility: Rule: Eligibility Profile Rule

13 Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED, PGM_ID, PL_ID,

OPT_ID, LER_ID

14 Overloaded Contexts:No

15 Database items available: All columns except attribute columns from tables ben_ler_f,ben_pgm_f, ben_pl_f, ben_pl_typ_f, ben_opt_f and database items based on personsassignment_id.

16 Input values available:No Input values are available.


18 Formula output or return values: Formula should use pre defined names for return variables

Out Variable Name DataType Value Remarks

ELIGIBLE

(Not a mandatory out variable)

Char Y/N

8 Erroring: If any other output value is passed back then process errors out by raising

BEN_91329_FORMULA_RETURN.

9 Remarks : None

10 Sample Formula:/******************************************************************************FORMULA NAME: BEN_PRTN_AND_RT_ELIGFORMULA TYPE : Participation and Rate Eligibility

DESCRIPTION : The formula checks the gender to evaluate the eligibility of a person to a

particular compensation object. This rule makes only females eligible to a compensation object*******************************************************************************Change History:


***********************************************************************

FORMULA TEXT: Logic in simple terms.


37/184



/*============== INPUT VALUES DEFAUT ENDS ======================*//*================= INPUTS SECTION BEGIN ========================*//*================== INPUTS SECTION ENDS ========================*/

/*================ FORMULA SECTION BEGIN =======================*/l_gender = fn_gender()if l_gender = 'M' then( ELIGIBLE = 'N' )

else( ELIGIBLE = 'Y')return ELIGIBLE


11 Formula functions used in the sample formula: FN_GENDER


============================================================================

Formula Type: Maximum Period of Enrollment Determination Date

Functional Usage: The Maximum Period of Enrollment Determination Date formula can be used todetermine the low date to be used when determining whether the person has been enrolled in a

plan or option in plan for the maximum period of time.

Navigation Path: Total Compensation -> Programs and Plans-> Plan: Plan Eligibility: MaximumEnrollment: Date Determination Rule

Total Compensation -> Programs and Plan->Program-> Plan and Plan Types: Plans: ParticipationEligibility: Maximum Enrollment: Date Determination Rule

Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED, ORGANIZATION_ID,

JURISDICTION_CODE, PGM_ID, LER_ID, PER_PAY_METHOD_IDOverloaded Contexts: PER_PAY_METHOD_ID is overloaded with REGN_ID

Database items available: All columns except attribute columns from tables ben_ler_f, ben_pgm_fand database items based on persons assignment_id.

Input values available:No Input values are available.

Constraints using Database items: Database items other than the ones mentioned above cant beused.

Formulas output or return values: No need to use predefined names for return variables.


Type

Value Remarks

L_POE_DET_DT

(Not a mandatory out variable,

any name can be used)

Date

8 Erroring:None

9 Remarks:None

10 Sample Formula:

/**********************************************************************

FORMULA NAME: BEN_MX_POE_DET_DTFORMULA TYPE : Maximum Period of Enrollment Determination DateDESCRIPTION : The formula returns the effective date***********************************************************************


38/184

Change History:Name Date Description-----------------------------------------------------------------------Vikash Sethi 25-Apr-2002 Initial Version

***********************************************************************


**********************************************************************/





l_poe_det_dt = fn_effective_date()return l_poe_det_dt/*================ FORMULA SECTION END =======================*/



===========================================================================

=

Formula Type: Maximum Period of Enrollment

13 Functional Usage: The Maximum Period of Enrollment formula is used to determine the

maximum length of time for a person to be enrolled in a plan or in a option in plan.14 Navigation Path: Total Compensation -> Programs and Plans-> Plan: Plan Eligibility: MaximumEnrollment: Rule

Total Compensation -> Programs and Plan->Program-> Plan and Plan Types:Plans: Participation Eligibility: Maximum Enrollment: Rule

15 Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED,ORGANIZATION_ID, JURISDICTION_CODE, PGM_ID, PL_TYP_ID, LER_ID,

PER_PAY_METHOD_ID

16 OverloadedContexts: PER_PAY_METHOD_ID is overloaded with REGN_ID

17 Database items available: All columns except attribute columns from table ben_ler_f, ben_pl_f,ben_pgm_f and database items based on persons assignment_id.


19 Constraints using Database items: Database items other than the ones mentioned above cant be

used.20 Formula output or return values: No need to use predefined names for return variables.


Type

Value Remarks

L_MX_POE

(Not a mandatory out variable,any name can be used)

Date


39/184


9 Remarks:None

10 Sample Formula:

/******************************************************************************

FORMULA NAME: BEN_MX_POE

FORMULA TYPE : Maximum Period of EnrollmentDESCRIPTION : Formula to determine the maximum period of enrollment for person.It returns eligibility end date as two years from the effective date.

*******************************************************************************Change History:Name Date Description

-----------------------------------------------------------------------Vikash Sethi 25-Apr-2002 Initial Version

***********************************************************************


**********************************************************************/


/*============ INPUT VALUES DEFAULT BEGIN ======================*//*============== INPUT VALUES DEFAUT ENDS ======================*//*================= INPUTS SECTION BEGIN ========================*/


/*================ FORMULA SECTION BEGIN =======================*/l_date_earned = fn_effective_date()l_mx_poe = add_months(l_date_earned,24)

return l_mx_poe/*================ FORMULA SECTION END =======================*/

11 Formula functions used in the sample formula: FN_EFFECTIVE_DATE12 Unit test script:

Formula Type: Enrollment Coverage Start Date

13 Functional Usage: The Enrollment Coverage Start Date formula can be used to determine the

date from which the enrollment coverage starts for a compensation object.

14 Navigation Path: Total Compensation -> Programs and Plans-> Plan Enrollment Requirements:General: Plan: Coverage: Enrollment Coverage Start Date Rule

15 Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED,ORGANIZATION_ID, JURISDICTION_CODE, PGM_ID, PL_ID, PL_TYP_ID, OPT_ID,LER_ID, PAYROLL_ACTION_ID

16 OverloadedContexts: PAYROLL_ACTION_ID is overloaded with ACTIVITY_BASE_RT_ID,ORGANIZATION_ID is overloaded with ELIG_PER_ELCTBL_CHC_ID






40/184


Type

Value Remarks

L _START_DT(Not a mandatory out variable,any name can be used)

Date

8 Erroring: None

9 Remarks: None

10 Sample Formula:/**********************************************************************

FORMULA NAME: BEN_ENRT_COV_START_DTFORMULA TYPE : Enrollment Coverage Start DateDESCRIPTION : Formula to determine persons enrollment coverage start date. The coverage

start date is calculated as the day on which the person turns 21.

***********************************************************************Change History:Name Date Description-----------------------------------------------------------------------

Vikash Sethi 25-Apr-2002 Initial Version

***********************************************************************FORMULA TEXT:

**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/Default for PER_DATE_OF_BIRTH is '01-jan-0001' (date)/*=========== DATABASE ITEM DEFAULTS ENDS======================*/



/*================ FORMULA SECTION BEGIN =======================*/l_dob = PER_DATE_OF_BIRTH (date)if PER_DATE_OF_BIRTH was defaulted then

(l_min_age = 12*21l_start_dt = add_months(l_dob,l_min_age)

)

else(

l_start_dt = '01-jan-3000' (date)

)return l_start_dt





41/184

Formula Type: Enrollment End

13 Functional Usage: The Enrollment End formula can be used to determine the date on which a

person loses coverage for a particular compensation object.

14 Navigation Path: Total Compensation -> Programs and Plans-> Plan Enrollment Requirements:

General: Plan: Coverage: Enrollment Coverage Start Date Rule15 Contexts: BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED,ORGANIZATION_ID, JURISDICTION_CODE, PGM_ID, PL_ID, PL_TYP_ID, OPT_ID,LER_ID, PAYROLL_ACTION_ID

16 OverloadedContexts: PAYROLL_ACTION_ID is overloaded with ACTIVITY_BASE_RT_ID,ORGANIZATION_ID is overloaded with ELIG_PER_ELCTBL_CHC_ID





Out Variable Name

Data

Type

Value Remarks

L_END_DT(Not a mandatory out variable,any name can be used)

Date

8 Erroring: None.

9 Remarks: None

10 Sample Formula:/******************************************************************************

FORMULA NAME: BEN_ENRT_ENDFORMULA TYPE : Enrollment EndDESCRIPTION : Formula to determine the enrollment coverage end date for person in a

Compensation object. It returns end date as the date on which the person

turns 25.*******************************************************************************

Change History:Name Date Description


***********************************************************************


**********************************************************************//*=========== DATABASE ITEM DEFAULTS BEGIN =====================*/Default for PER_DATE_OF_BIRTH is '01-jan-0001' (date)




42/184


/*================ FORMULA SECTION BEGIN =======================*/l_dob = PER_DATE_OF_BIRTH (date)

if not PER_DATE_OF_BIRTH was defaulted then

( l_min_age = 12*25l_end_dt = add_months(l_dob,l_min_age)

l_end_dt = add_days(l_end_dt, -1))else(

l_end_dt = l_dob

)return l_end_dt



12 Unit test script: None

Formula Type: Rate Start Date

13 Functional Usage: The Rate Start Date formula can be used to determine the date on which therate for enrollment result starts for a participant.

14 Navigation Path: Total Compensation -> Programs and Plans-> Plan Enrollment Requirements:General: Plan: Rate: Rate Start Date Rule

15 Contexts: BUSINESS_GROUP_ID, PAYROLL_ACTION_ID, ASSIGNMENT_ID,DATE_EARNED, ORGANIZATION_ID, JURISDICTION_CODE, PGM_ID, PL_ID,PL_TYP_ID, OPT_ID, LER_ID

16 OverloadedContexts: ORGANIZATION_ID is overloaded with

ELIG_PER_ELCTBL_CHC_ID, PAYROLL_ACTION_ID is overloaded withACTIVITY_BASE_RT_ID






Type

Value Remarks

L _START_DT(Not a mandatory out variable,any name can be used)

Date

8 Erroring:None

9 Remarks: None

10 Sample Formula:/**********************************************************************

FORMULA NAME: BEN_RT_START_DT


43/184

FORMULA TYPE : Rate Start DateDESCRIPTION : Formula to determine participants rate start date for an enrollment result. Therate start date is calculated as the day on which the person turns 21.***********************************************************************Change History:

Name Date Description




Default for PER_DATE_OF_BIRTH is '01-jan-0001' (date)/*=========== DATABASE ITEM DEFAULTS ENDS======================*/




/*================ FORMULA SECTION BEGIN =======================*/l_dob = PER_DATE_OF_BIRTH (date)if not PER_DATE_OF_BIRTH was defaulted then(

l_min_age = 12*21 /* age in months */l_start_dt = add_months(l_dob,l_min_age)

)else(

l_start_dt = '31-Dec-4712' (date) /* end of time */)return l_start_dt/*================ FORMULA SECTION END =======================*/



Formula Type: Rate End Date

13 Functional Usage: The Rate End Date formula can be used to determine the date on which therate for enrollment result ends for a participant.

14 Navigation Path: Total Compensation -> Programs and Plans-> Plan Enrollment Requirements:

General: Plan: Rate: Rate Start Date Rule15 Contexts: BUSINESS_GROUP_ID, PAYROLL_ACTION_ID, ASSIGNMENT_ID,

DATE_EARNED, ORGA

oab fast formula user guide

Documents