The following table lists the formula types you can use in administering benefits with Oracle HRMS.
| Formula Type | Description | Contexts | Input Values | Return Value | Sample Rule |
|---|---|---|---|---|---|
| Hours Worked Calculation | Used to determine an Hours Worked amount to be used for Eligibility, Coverage or Benefit, Premium, and Rate calculations | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Hrs_Wkd_Fctr_Id; Value of the Hrs_Wkd_Fctr.Val, Min Val, Max Val | Amount | Determine total number of hours worked during the person's most recent pay period. |
| Age Calculation | Used to determine an Age value to be used for Eligibility, Coverage or Benefit, Premium, and Rate calculations | " | Value of the Age_Fctr.Val, Min Val, Max Val | Amount | |
| Length of Service Calculation | Used to determine a Service value to be used for Eligibility, Coverage or Benefit, Premium, and Rate calculations | " | LOS_Fctr_Id; Value of the LOS_Fctr. Val, Min Val, Max Val | Amount | |
| Compensation Calculation | Used to determine a Total Compensation amount to be used for Eligibility, Coverage or Benefit, Premium and Rate calculations | " | Comp_Lvl_Fctr_Id; Value of the Comp_Lvl_Fctr.Val, Min Val, Max Val | Amount | Sum the amounts for person's current Regular Salary defined balance as of the beginning of the year, Prior Year Total Commission benefits balance type, and Prior Year Total Bonus benefits balance type, and return the total amount. |
| Rate Value Calculation | Calculates a rate amount for a person. May be used to calculate a base or variable rate. | " | Acty_Base_Rt_Id or Vrbl_Rt_Id; Value of the Acty_Base_Rt.Val, Min Val, Max Val; Value of the Vrbl_Rt_Prfl.Val, Min Val, Max Val, | Amount | If a person is currently enrolled in this plan, or has been enrolled in this plan within the past 2 years, then the rate is equal to the rate person is currently paying. Otherwise, rate is equal to X. |
| Premium Value Calculation | Calculates a premium amount for a person. | " | Acty_Base_Rt_Id or Vrbl_Rt_Id or Actl_Prem_Id or Cvg_Amt_Calc_Mthd_Id; Value of the Acty_Base_Rt.Val, Min Val, Max Val | Amount | Premium amount is equal to .05 times Coverage amount less 50,000 |
| Matching Amount Calculation | Calculates the matching amount. | " | " | Amount | People that work at Division A get matching amount of 5%. All others get 4% matching amount. |
| Minimum Coverage Amount Calculation | Calculates a minimum coverage or benefit amount for a person. | " | " | Amount | If in Benefits Group A, minimum amount is $100. If in Benefits Group B, minimum amount is $200. |
| Maximum Coverage Amount Calculation | Calculates a maximum coverage or benefit amount for a person. | " | " | Amount | Coverage maximum amount is equal to coverage amount for current enrollment; if no current enrollment, then maximum is $100,000. |
| Period to Date Amount Calculation | Determines the maximum period to date amount a person may have for a particular activity rate. | " | Amount | ||
| Coverage Amount Calculation | Calculates a coverage or benefit amount for a person. | " | Amount | ||
| Age Determination Date | Determines the date from which age will be calculated (e.g., the first day of the next plan year). | " | Date | If person is in an Annual Enrollment event then return 10/1/99. If person is becoming eligible as a result of a new hire, rehire, return from leave or a change from part-time to full-time then return the event occurred on date. | |
| Hours Worked Determination Date | Determines the date from which hours worked will be calculated (e.g., the first day of the next plan year). | " | Date | If person is in an Annual Enrollment event then return 10/1/99. If person is becoming eligible as a result of a new hire, rehire, return from leave or a change from part-time to full-time then return the event occurred on date. | |
| Length of Service Date to Use | Determines the low date from which LOS will be calculated (e.g., original hire date or rehire date). | " | Date | Return Start Date from the person's Absence Attendance row in order to determine the elapsed time that a person has been on a Leave of Absence. | |
| Length of Service Determination Date | Determines the high date for which LOS will be calculated (e.g., the first day of the following month). | " | Date | Return Start Date from the person's Absence Attendance row. | |
| Compensation Determination Date | Determines the high date from which hours compensation will be calculated (e.g., the first day of the next plan year). | " | Date | ||
| Action Type Due Date | Determines the date on which the Action Type must be completed (e.g. 90 days from the Life Event Creation Date). | " | Date | If person is currently enrolled, 60 days from life event creation date. If person is not currently enrolled, 30 days from life event creation date. | |
| Participation Eligibility Start Date | Determines when eligibility for a person should start. | " | Date | Add 6 months to the event date and return. | |
| Participation Eligibility End Date | Determines when eligibility for a person should end. | " | Date | If Organization on current assignment = A, return date equal to Event Date; otherwise, return date equal to Start of Following Month after event date. | |
| Enrollment Coverage Start Date | Determines when enrollment coverage for a participant should start. | " | Date | Coverage start date based on how long you have been absent from a plan. | |
| Enrollment End | Determines when enrollment coverage for a participant should end. | " | Date | If Calculated LOS is less than 5 years, then end of current month. If Calculated LOS is greater than or equal to 5 years, then end of 6 months after event date. | |
| Dependent Coverage Start Date | Determines when coverage for a dependent should start. | " | Date | If notified of birth within 31 days of event, Coverage Start Date is Date of Birth. Otherwise, coverage start date is date of notification. | |
| Dependent Coverage End Date | Determines when coverage for a dependent should end. | " | Date | If Contact Relationship Type = Spouse, coverage ends on date of event; if Contact Relationship Type = Child, coverage ends on end of the month following event. | |
| Rate Start Date | Determines when a rate for an enrollment result for a participant should start. | " | Date | Rate starts on the date after the rate end date of the current enrollment. | |
| Rate End Date | Determines when a rate for an enrollment result for a participant should end. | " | Date | Rate ends on the day before the person's next pay period. | |
| Participation Eligibility | Determines whether a person is eligible for the associated compensation object. (This is the rule used in the eligibility profile rule entities.) | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id, Balance_Date | Y/N | ||
| Dependent Eligibility | Determines whether a person is eligible to be covered by a participant in a compensation object. | " | Y/N | Dependent is eligible if Employee Work Location is 001 and Dependent is under age 21 or if Employee Work Location is not 001 and Dependent is under age 25. | |
| Enrollment Opportunity | Determines whether the compensation object should be an electable choice for the person. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | One of the BEN_ENRT_MTHD values: A or E; or N | As a result of a transfer event, the participant can only enroll in an HMO if they were previously enrolled in an HMO and that HMO is no longer available to them in their new location. | |
| To Be Sent Date | Determines the date on which the communication should be sent to the person. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id, Cm_Typ_Id | Date | If participant's organization is equal to {org1} or {org2} then return start of enrollment period - 14 days. If participant's organization is not equal to {org1} or {org2} then return start of enrollment period - 7 days. | |
| Rounding | Rounds a number to the specified place or decimal. | None | Amount | Amount to be rounded - $250 rounded to the next $500 | |
| Percent Rounding | Rounds a percent to the specified place or decimal. | Amount | |||
| Automatic Enrollment Method | Determines the conditions under which a person should be automatically enrolled in a compensation object. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | One of the BEN_ENRT_MTHD values: A or E; or N | Reinstate active benefits if rehired in the same plan year. | |
| Deduction Schedule | Determines the deduction schedule to be used for this person. | " | One of the BEN_DED_SCHED values | If bargaining unit code is not null return "Second Period In Month" else return "Every Pay Period". | |
| Payment Schedule | Determines the payment schedule to be used for this person. | " | One of the BEN_PYMT_SCHED values | If bargaining unit code is not null return "Second Period In Month" else return "Every Pay Period". | |
| Default to Assign Pending Action | Determines the applicable default option or benefit to assign to the person when his or her choice has been suspended. | " | One of the BEN_DFLT_TO_ASN_PNDG_CTFN values | Reinstate the active benefits that a person had at the time that the person was previously active. (NOTE: cannot reinstate most recent benefits, as person could have some other elections, such as COBRA). | |
| Enrollment Certification Required | Determines the conditions under which a person must provide certification in order to enroll or elect a particular plan or option in plan, or benefit. | " | Y/N | Participant does not need to provide certification if they were enrolled in the compensation object and had provided certification within the past 12 months. | |
| Dependent Certification Required | Determines the conditions under which a person must provide certification for his or her designated dependents. | " | Y/N | ||
| Beneficiary Certification Required | Determines the conditions under which a person must provide certification for his or her designated beneficiaries. | " | Y/N | ||
| Waive Certification Required | Determines the conditions under which a person must provide certification when he or she waives participation. | " | Y/N | ||
| Inspection Required | Determines whether inspection of the communication is required. If so, the "Inspection Flag" is set to 'yes' for this person's communication. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id, Cm_Typ_Id | Y/N | If participant's division = "Corporate" or if participant's HCE indicator = "Y" return "YES". | |
| Communication Appropriate | Determines for this communication and trigger, whether the communication should be sent; restricts to whom to send. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Ler_Id, Cm_Typ_Id | Y/N | If person is in an Annual Enrollment event and today's date is within seven days of the enrollment period end date return "No". | |
| Communication Type | Determines whether the communication should be sent. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Ler_Id, Cm_Typ_Id | Y/N | ||
| Mandatory Determination | Determines whether this option in plan should be assigned to a person (and not be optional) as part of the enrollment process. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Y/N | ||
| Postelection Edit | Performs edits on an enrollment result, e.g. is the spouse of the participant also enrolled; does the spouse of the participant work at the same company. | " | Y/N and Text | If the participant elects a non-waive plan under the spouse life plan type, the participant must elect an equal or greater amount of participant life insurance. | |
| Partial Month Proration Method | Determines which value to use: Date Earned, Pay Period End Date, or Payment Date. This tells the proration process which date to use when determining how many pay periods remain, and when the element entry should start. | " | One of the Values of : BEN_PRTL_MO_DET_MTHD | If the participant is paid monthly and enrolls in a medical plan between the 8th and 15th of the month, then return 75% of the normal monthly price tag. | |
| Partial Year Coverage Restriction | Determines the maximum coverage amount for partial years of coverage. Allows determination of values other than delivered values of: 1) Standard maximum as defined in the plan. 2) Prorate maximum based on the number of months remaining in the plan year. | " | Amount | ||
| Partial Month Effective Date Determination | Determines which value to use: Date Earned, Pay Period End Date, or Payment Date. This tells the proration process which date to use when determining how many pay periods remain, and when the element entry should start. | " | One of the Values of : BEN_PRTL_MO_EFF_DT_DET | ||
| Lack Certification Allow Reimbursement | Identifies cases where certification is waived. | " | Y/N | ||
| Compensation Object Selection | Determines which compensation objects are to be included for processing in a concurrent manager process. | " | Y/N | Run the default process for the flex and nonflex programs only. Participation process selection mode: determine eligibility for all compensation objects that use derivable factors. | |
| Person Selection | Determines which people are to be included for processing in a concurrent manager process. | " | Y/N | Select all ex-participants who were working at a specific organization. | |
| Verify Family Member | Determines whether the person has one or more contacts of a specific type or types, e.g. spouse, spouse and one child, more than one child. | " | Y/N | ||
| Five Percent Owner | Determines for this plan and regulation whether the person is a five percent owner as defined in the regulation. | " | Y/N | ||
| Highly Compensated | Determines for this plan and regulation whether the person is considered to be "highly compensated" as defined in the regulation. | " | Y/N | ||
| Key Employee | Determines for this plan and regulation whether the person is considered to be a "key employee" as defined in the regulation. | " | Y/N | ||
| Break in Service Value | Break in Service Value | " | Amount | ||
| Break in Service Determination | Determines whether a break in service has occurred and if this break should not be ignored. | " | Y/N | ||
| Contribution Nondiscriminatory | Compares individual employee pretax contribution amounts to total pretax contributions for highly and non-highly compensated persons. | " | Y/N | ||
| Coverage Nondiscriminatory | Compares the total number of persons eligible to participate in a plan minus the persons who are not eligible due to legislated allowable factors to the number actually participating. | " | Y/N | ||
| Extract Person Data Element | Specifies person or assignment information to be included as a data element item. This rule type can also return the results of a calculation performed on person or assignment information. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment_Id | Text | ||
| Extract Person Inclusion | Specifies person or assignment level inclusion or exclusion for system extract. | " | Y/N | ||
| Communication Usage | Determines whether a Communcation Usage requirement has been satisfied. If so, then a communication should be triggered for this usage. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Ler_Id, Cm_Typ_Id | Y/N | ||
| Default Enrollment | Determines whether this option in plan should be assigned to a person as part of the default enrollment process. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | |||
| Enrollment Period Start Date | Determines the date on which the enrollment period starts. | Date | |||
| Enrollment Period End Date | Determines the date on which the enrollment period ends. | " | Date | ||
| Pop-Up Message | Determines whether a pop-up message displays and it what form. | " | Y/N | For a participant who selects life insurance at 10x salary, display a warning message on the enrollment form if the participant's salary is less than 50k. | |
| Collapse Life Event Resulting Occurred On Date | When life events are collapsed this rule determines the date to use for the resulting life event. | " | Date | ||
| Collapse Life Event Evaluation | Determines whether the life event should be collapsed and deleted, collapsed and voided, or neither. | " | One of the values of: BEN_EVAL_DT: Collapse or Void; or leave as is | ||
| Vested Value | Determine the vested percent for a person. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id, Balance_Date | Percent | ||
| Change Dependent Coverage | Used to determine if a dependent can be 'Added Only', 'Removed Only', Added and Removed', or 'Neither'. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Y/N | ||
| Evaluate Life Event | Determines if this life event is valid, or if the life event information needs to be changed, e.g. status, voided date, unprocessed date. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Ler Id | Y/N, status code, unprocessed date, processed date. | ||
| Maximum Waiting Period Date to Use | Determines the low date from which the maximum waiting period will be calculated (e.g., original hire date or rehire date). | " | Date | ||
| Waiting Period Value and UOM | Determines the waiting period and unit of measure to be applied to a person. | " | Amount and UOM | ||
| Maximum Period of Enrollment Value and UOM | Determines the maximum enrollment period and unit of measure for a compensation object. | " | Amount and UOM | ||
| Person Change Causes Life Event | Determines whether this life event is valid for a person based on the data that changed. | " | Y/N | ||
| Related Person Change Causes Life Event | Determines whether this life event is valid for a related person based on the data that changed. | " | Y/N | ||
| Participant Eligible to Rollover | Determines whether this person may roll over flex credits into a particular compensation object. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id, Balance_Date | Y/N | ||
| Payment Must Be Received | Determines whether a payment is missing or late. | " | Y/N | ||
| Life Event Reason Timeliness | Determines whether a life event has been reported in a timely manner. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Y/N | ||
| Required Period of Enrollment | Determines the earliest deenrollment date for a person's electable choice for a compensation object. | " | Date | ||
| Rate Lower Limit | Calculates an amount used to determine the lower limit value to which an activity rate or variable rate value is compared. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id, Balance_Date | Amount | ||
| Rate Upper Limit | Calculates an amount used to determine the upper limit value to which an activity rate or variable rate value is compared. | " | Amount | ||
| Coverage Lower Limit | Calculates an amount used to determine the lower limit for an activity or variable coverage or benefit. | " | Amount | ||
| Coverage Upper Limit | Calculates an amount used to determine the upper limit for an activity or variable coverage or benefit. | " | Amount | ||
| Premium Lower Limit | Calculates an amount used to determine the lower limit for an activity or variable premium. | " | Amount | ||
| Premium Upper Limit | Calculates an amount used to determine the upper limit for an activity or variable premium. | " | Amount | ||
| Maximum Period of Enrollment | Period of Enrollment Rule determines whether a person has been enrolled for the maximum length of time allowed for a plan or option in a plan. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Y/N | ||
| Maximum Period of Enrollment Determination Date | Determines 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. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Date | ||
| Partial Month Proration Value Calculation | Calculates a value for a partial month enrollment. | " | Percent | ||
| Variable Rate Add On Calculation | Calculates a new value when a variable rate result is used. | " | Result of the Variable Rate Calculation | Amount | Multiply the result by 102%. |
| Segment Costing Method | Determines how an amount is to be costed based on the segment in the COST KEY ALLOCATION KEY FLEXFIELD. | " | Key Cost Allocation Flexfield Segment | ||
| Extract Enrollment Data Element | Specifies enrollment information to be included as a data element item. This formula type can also return the results of a calculation performed on enrollment information. | Business_Group_Id, Effective_Date, Assignment Id, Pl_Id, Opt_Id, Ler_Id | Text | ||
| Maximum Credit Rollover Value | Determines the maximum amount a person may rollover to another plan or option in plan. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Amount | ||
| Default Excess Credit Treatment | Determines how any excess credits are to be allocated. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | One of the values in BEN_DFLT_EXCS_TRTMT_CD | ||
| Prorate Annual Election Value | Determines how a minimum or maximum annual election value is prorated. | Business_Group_Id, Effective_Date, Jurisdiction_Code, Organization_Id, Assignment Id, Pgm_Id, Pl_Typ_Id, Pl_Id, Opt_Id, Ler_Id | Amount | ||
| Extract Post Process | This formula type provides additional system extract processing and is called after all extraced records are written. | Business_Group_Id, Effective_Date | EXT_RSLT_ID (this is found on ben_ext_rslt and ben_ext_rslt_dtl tables). | Nothing. All processing should be via formula function. Commit will occur in calling program. | Allows you to insert intermittent totals, delete records, change sorting, format fields, update values, etc. |
| Eligibility Access Calculation | Returns a person's value for a user-defined criteria so it can be evaluated in the eligibility determination process. | Business_Group_Id, Assignment Id, Date_Earned (life event occurred date or effective date), Organization_Id, pgm_id, pl_id, opt_id, ler_id, pl_typ_id | None | Criteria (and subcriteria) values | For a user-defined criteria of "Works more than 30 hours per week", rule returns Y or N according to the information stored on the person's work schedule. |
| Preferential Rate Calculation | For a given criteria rate definition, if an employee is found eligible for multiple rates by the Rate by Criteria API, the Preferential Rate Calculation formula can resolve which of these rates/ combination of rates must be paid to the employee. | BUSINESS_GROUP_ID, ASSIGNMENT_ID, DATE_EARNED, ORGANIZATION_ID, JURISDICTION_CODE | PQH_RBC_PERSON_ID, PQH_RBC_CRIT_RATE_DEFN_ID, MIN_1, MID_1, MAX_1, DFLT_1, MIN_2, MID_2, MAX_2, DFLT_2, MIN_3, MID_3, MAX_3, DFLT_3, MIN_4, MID_4, MAX_4, DFLT_4, MIN_5, MID_5, MAX_5, DFLT_5, | MIN, MID, MAX, DFLT | Preferred rate for people in the Sales organization is the maximum eligible rate; for people in the Marketing organization, preferred rate is the average of the eligible rates. |