Setting Up Functions and Formulas for Timecard Automation

This chapter provides an overview of functions and formulas and discusses how to:

• Register a function.

• Create and test formulas.

Understanding Functions and Formulas

To pay employees correctly for exception pay such as overtime, retroactive pay, holiday pay, or special allowance pay, many organizations require complex calculations. Though many of these complex overtime calculations are included in the software, you might need to create functions and formulas for more complex earnings calculations.

Registering a Function

This section includes an overview of functions and discusses how to register functions.

Understanding Functions

The Fields and Functions program (P186101) enables programmers and consultants to access field and function definitions that are used throughout the Timecard Automation module. A field is an area in which the system displays a specific piece of information. A field can be either an input-capable field or a display field, for which the system retrieves the information from another part of the system. For example, the TIN (taxpayer identification) field displays taxpayer identification numbers. A function is a calculation with variables that the system uses to perform specific actions. For example, you can use the timecard rate function on its own or in a formula to find employees' timecard rates.

To use different fields and functions in the Timecard Automation module, you must first register the field or the function in the Fields and Functions program. The fields in the F060116, F06116, and F0618 tables are preregistered in the system. Several additional functions have also been registered in the Fields and Functions program.

The system includes several functions for calculating common methods of determining hourly rates and hours worked. When you create a timecard template, overtime rule set, or retroactive pay rule, you can use one or more of these functions. Because each function is specific to a field in a specific program, the system limits the available functions to those that are applicable to the calling program. For example, the function that is available for calculating the hourly rate for a timecard template is different from the function that is available for calculating the hourly rate for a retroactive pay rule.

You can use a function alone, or you can create a formula that includes one or more functions. Use the Fields and Functions program (P186101) to create custom formulas. You can also use this program to validate and test a formula before you begin using it.

Occasionally, you might need a function that is not included with the system. In this case, you can customize the system by adding a new function. Typically, you create custom functions using named event rules through the Business Function Design Aid tool, which you access from the Object Management Workbench (OMW). You can also create custom business functions using C programming. Programmers or consultants typically create custom business functions. Oracle recommends that you contact the system administrator for assistance with creating custom functions. After you create a custom function, you must register it for use in the applicable Timecard Automation program. Registering the function enables you to access it from the applicable program.

To simplify the process of creating formulas for calculating hourly rates and hours worked, the Timecard Automation module includes several predefined functions. You can use these functions alone or include them in formulas. These functions include:

• Hours worked function for timecard templates.

• Hours worked function for retroactive pay rules.

• Hourly rate function for timecard templates.

• Hourly rate functions for retroactive pay rules.

• Hourly rate functions for overtime accumulator rules.

• Custom overtime rules functions.

To meet the specific needs of the organization, you can also create custom functions. Each function uses a data structure, which lists the data item name, the input or output status, and a description of the data item.

Hours Worked Function for Timecard Templates

The hours worked function for timecard templates is included in the Template Hours Worked Functions module (N1861312). This function uses data structure D186132. You also use this data structure when you create a custom hours worked function for timecard templates. This table illustrates input and output information about the data items in this data structure:

The predefined hours worked function for timecard templates is THWF_8HoursIf8OnPrevWorkDate.

The 8 Hours if Employee Worked 8 or More Hours on a Previous Day function returns eight hours worked if the employee worked eight or more hours, not including sick pay, on the previous work date. Saturdays and Sundays are not considered work dates. If the employee did not work at least eight hours, the returned value is zero. The work date is identified on the timecard template in the Work Date Schedule. For example, you might use this function if you want the timecard template to use eight hours for the amount of work an employee is paid each day, based on the criteria specified.

Hours Worked Function for Retroactive Pay Rules

The hours worked function for retroactive pay rules is included in the Retropay Hours Worked Functions module (N1861712). This function uses data structure D186172. You also use this data structure when you create a custom hours worked function for retroactive pay rules. This table lists examples of input and output information about the data items in this data structure:

The predefined hours worked function for retroactive pay rules is RHRF_TimecardHours.

The hours worked on timecard function returns the hours worked from the Rate Hourly field on a timecard currently being processed by a retroactive pay rule for one of the pay types that you identify. For example, you might use this function when you pay employees retroactive pay for the hours worked, based on their original timecards.

Hourly Rate Function for Timecard Templates

The hourly rate functions for timecard templates are included in the Template Hourly Rate Functions module (N1861311). These functions use data structure D186131. You also use this data structure when you create a custom hourly rate function for timecard templates. This table lists examples of input and output information about the data items in this data structure:

The predefined hourly rate function for timecard templates is THRF_EmployeeMasterRate.

The Employee Master Rate function returns the rate from the Rate-Hourly field in the F060116 table for the employee records that are processed using the timecard template. For example, you might want to pay an employee based on the established master pay rate.

Hourly Rate Functions for Retroactive Pay Rules

The hourly rate functions for retroactive pay rules are included in the Retropay Hourly Rate Functions module (N1861711). These functions use data structure D186171. You also use this data structure when you create a custom hourly rate function for retroactive pay rules. This table lists examples of input and output information about the data items in this data structure:

The predefined hourly rate functions for retroactive pay rules are:

• RHRF_EmployeeMasterRate

  The Employee Master Rate function returns the rate from the Rate-Hourly field in the F060116 table for the employee on the current timecard that the system is processing using the retroactive pay rule. For example, you might want to pay employees retroactive pay based on the master hourly pay rate.

• RHRF_MaxRate

  The Maximum Rate from Timecard vs. Employee Master function returns the greater rate of the timecard hourly rate from the Hourly Rate field or the employee master hourly rate from the Rate-Hourly field. For example, you might want to pay employees retroactive pay based on either the master hourly pay rate or the timecard hourly rate. This function provides the larger of these two hourly rates.

• RHRF_PayRateMultiplier

  The Pay Rate Multiplier function returns the pay rate multiplier from the Multiplier-Pay Type Multiplier field on the current timecard that the system is processing using the retroactive pay rule.

• RHRF_TimecardRate

  The Timecard Rate function returns the hourly rate from the Hourly Rate field on the current timecard that the system is processing using the retroactive pay rule. For example, you might want to pay employees retroactive pay based on the rate on the timecard instead of another rate.

• RHRF_UnionRateX where X is A, B, C, D, or R

  The Union Rate X function returns the rate for a chosen employee from a particular column of the F069126 table. You identify the appropriate column. This function returns the union rate by calling the Retrieve Union Rates function (N0500015) and using fields on the current timecard that the system is processing in the retroactive pay rule. For example, you might want to pay employees retroactive pay based on a particular union rate. This function provides you with the appropriate union rate.

• RHRF_WeightedAverageHourlyRate

  The Weighted Average of Employees' Timecards in Retroactive Pay Period function returns an hourly rate weighted by hours worked. The function uses all timecards in the overtime period for one of the pay types that you identify. For example, you might want to pay employees retroactive pay based on an average of the hourly pay rates received in the past.

Hourly Rate Functions for Overtime Accumulator Rules

The hourly rate functions for overtime accumulator rules are included in the Overtime Hourly Rate Functions module (N1861411). These functions use data structure D186141. You also use this data structure when you create a custom hourly rate function for overtime accumulator rules. This table lists examples of input and output information about the data items in this data structure:

The predefined hourly rate functions for overtime accumulator rules are:

• OHRF_EmployeeMasterRate

  The Employee Master Rate function returns the hourly rate from the Rate-Hourly field (PHRT) in the F060116 table for the employee that the system is processing using the overtime rule set. For example, you might want to pay employees overtime pay based on the master hourly pay rate.

• OHRF_HoursWorked

  The Hours Worked on Timecard function returns the hours worked from the PHRW field on the current timecard that the system is processing using the overtime rule set. For example, you might want to pay employees overtime pay based on the hours worked on the current timecards.

• OHRF_MaxRate

  The Maximum Rate from Timecard vs. Employee Master function returns the greater rate of the timecard hourly rate from the Hourly Rate field (SHRT) or the hourly rate in the Rate-Hourly field (PHRT) in the Employee Master table. For example, you might want to pay employees overtime pay based on either the master hourly pay rate or the timecard hourly rate. This function provides the higher of these two hourly rates.

• OHRF_PayFrequency

  The Employee Master Pay Frequency function returns an employee's numeric pay frequency (which is derived from the Description 2 field in UDC 07/PF) based on the value in the PFRQ field (Pay Frequency) on an employee record. For example, this function would convert a value of W (Weekly) in the PFRQ field to the numeric value of 52, which can then be used in payroll calculations.

• OHRF_PayRateMultiplier

  The Pay Rate Multiplier function returns the pay rate multiplier from the Multiplier-Pay Type Multiplier field (PAYM) on the current timecard that the system is processing using the overtime rule set.

• OHRF_Salary

  The Employee Master Salary function returns the employee's salary from Rate-Salary, Annual field (SAL) on an employee record for the employee being processed by the overtime rule. For example, you might want to pay employees overtime pay based on the master salary.

• OHRF_TimecardRate

  The Timecard Rate function returns the hourly rate from the Hourly Rate field on the current timecard that the system is processing using the overtime rule set. For example, you might want to pay employees overtime pay based on the rate on the timecard instead of another rate.

• OHRF_TotHoursWorked

  The Total Hours Worked function returns the total hours worked by a given employee within the overtime period for a pay type of one of the pay types that you identify. For example, you might want to pay employees overtime pay based on the total hours that each employee worked.

• OHRF_UnionRateX where X is A, B, C, D, or R

  The Union Rate X function returns the rate for a chosen employee from a particular column of the Union Rates File table (F069126), where you identify the appropriate column. This function returns the union rate by calling the Retrieve Union Rates function (N0500015) using fields on the current timecard that the system is processing using the overtime rule set. For example, you might want to pay employees overtime pay based on a particular union rate. This function provides you with the appropriate union rate.

• OHRF_WeightedAverageHourlyRate

  The Weighted Average of Employees' Regular Timecards in Overtime Period function returns an hourly rate weighted by hours worked. The function uses all timecards in the overtime period for a pay type of one of the pay types that you identify. For example, you might want to pay employees overtime pay based on an average of the hourly pay rates received in the past.

Custom Overtime Rules Functions

The predefined functions for custom overtime rules are included in the Custom Overtime Rules module (N186405). These functions use data structure D186405A. You also use this data structure when you create a custom hourly rate function for overtime accumulator rules. This table lists examples of input and output information about the data items in this data structure:

The predefined hourly rate functions for custom overtime rules are:

• OCR_MealAllowance

  The Meal Allowance custom rule pays a meal allowance of 8.00 USD for days on which employees work 9.5 or more hours of regular time (pay type of 1). The system creates a gross pay timecard with a pay type of 999 for 8.00 USD for that day. For example, you might want to pay consultants a meal allowance when they have worked a certain number of hours within a specific period.

• OCR_SpreadOvertime

  The Spread Employee's Salary custom rule spreads a salaried employee's salary over all hours worked and does not generate overtime. For example, you might want to pay salaried employees the same amount regardless of overtime worked. With this function, you can spread employee overtime across all hours worked, which in essence reduces the hourly rate, but pays employees the same amount.

• OCR_SevenDaysWorked

  The Seven Days Worked custom overtime rule applies to overtime worked in California. An organization must first determine whether an employee worked more than 30 hours in a week or over six hours in any day of the week. This calculation determines whether the employee is entitled to overtime on the seventh consecutive day. If an employee works seven consecutive days and has worked at least 30 hours in the week, or six hours in a day, the employee is entitled to overtime. The first eight hours of the seventh day are paid at 1.5 of the regular rate of pay. Any hours over eight worked on the seventh day are paid at 2.0 of the regular rate.

  New timecards are created with pay type 999.

• OCR_MinWageAdjustment_1

  The Minimum Wage Adjustment custom overtime rule checks to determine whether an employee is being paid at least minimum wage based on the total pay and hours worked on the timecards being processed. If not, this rule adds a timecard for the employee to bring his or her hourly rate up to minimum wage.

Registering Functions

You must register functions to access them when you build a formula for the hourly rate or hours worked fields in the Timecard Automation programs. Functions are registered specifically for use with timecard templates, retroactive pay rules, or overtime rule sets. The functions that are included with the Timecard Automation module are already registered. Occasionally, you might need to use a function that is not registered, such as any custom functions that you create. You can use functions alone or in formulas.

When you register a function, you can define it as active or inactive. An active field or function is one that other users can access from a Timecard Automation program. You can use inactive mode to verify whether the function works properly before you make it active for other users.

Before registering a function, you should identify the usage code that the system uses to determine the program in which the function is used. Usage codes include:

• THRF - Identifies a timecard template hourly rate function.

• THWF - Identifies a timecard template hours worked function.

• RHRF - Identifies a retroactive pay rule hourly rate function.

• RHWF - Identifies a retroactive pay rule hours worked function.

• OHRF - Identifies an overtime rule hourly rate function.

• OCRI - Identifies an overtime change rules IF statement.

• OCRT - Identifies an overtime change rules THEN statement.

• OCR - Identifies a custom overtime rule that is created using a custom business function.

In addition to specifying the usage codes for a function, you should also identify this information for each function:

• The source module, which is the object name to which the function belongs.

  Object names that are associated with named event rule business functions begin with N. For example, N1861411 is the object name (or source module) that contains the various overtime hourly rate functions.

• The function name within the source module.

  Event rule logic and data structures are attached to individual functions within a source module.

• The status of the field availability (either active or inactive).

  Note. You can use the Field/Function Definition form to revise information about a function, such as its active status. The changes that you make to functions affect all batches of timecards that use changed fields and functions and that are processed after the change. Changes do not affect timecards that have already been processed.
  
  You can delete a function from the Fields and Functions program, thereby making it unregistered in the Timecard Automation module. Deleting a function removes it from the search list for the appropriate Hourly Rate or Hours Worked field, but does not remove it from the system.

Forms Used to Register a Function

Registering Functions

Access the Field/Function Definition form.

Creating and Testing Formulas

This section provides an overview of formulas, lists a prerequisite, and discusses how to:

• Create a formula

• Test a formula for a timecard template.

• Test a formula for a retroactive pay rule.

• Test a formula for an overtime rule set.

Understanding Formulas

When you need to perform simple, complex, or unusual calculations to determine the appropriate hourly rate or number of hours worked for a particular situation, you can create a formula. The formula can use the standard functions that are included with the system, or you can use custom functions that you have created. You can also use a combination of both types of functions.

When you create a formula, you create it within a specific timecard template, retroactive pay rule, or overtime rule set. Based on the program that you select, the system provides you with a list of appropriate functions that are available for use in the formula. You then create mathematical statements that include one or more of these functions.

For example, assume that you need to average two different union rates to pay warehouse employees retroactive pay. To calculate the correct rate, you enclose within parentheses the multiple of the two union rates and divide the result by two. The formula for the new rate is:

New Rate = (RHRF_UnionRateA × RHRF_UnionRateB) ÷ 2

You cannot use a formula outside of the initial timecard template, overtime rule set, or retroactive pay rule for which it was created. You cannot save and reuse formulas; however, if you need to use a formula often, you might consider asking the system administrator to create a custom function. You can save and reuse the timecard template, retroactive pay rule, or overtime rule set that contains the formula that you created.

You use the Build Formula form to create custom formulas. You cannot access this form from a menu; instead, you access it using the search button in the hours worked and hourly rate fields in the Timecard Templates, Retroactive Pay Rules, and Overtime Rule Sets programs.

This table shows the forms and fields from which you can access the Build Formula form:

After you create the formula, but before you begin using it, test it to validate its mathematical logic and to ensure that it calculates the appropriate results.

Depending on government regulations, industry standards, union contracts, and company processes, different organizations calculate overtime rates using different methods. Review the examples of the formulas for these overtime hourly rate calculations. The functions that are used in these formulas are predefined in the system and are available for use in the overtime rule type specified in the formula description.

Time-and-a-Half Overtime Based on Average Hourly Rate

This formula averages the hourly rates of all of the regular timecards within the specified accumulation period and multiplies the average rate by 1.5 to determine the new hourly rate for overtime. You can use this formula in an overtime accumulator rule within an overtime rule set.

New Hourly Rate = 1.5 x OHRF_WeightedAverageHourlyRate

Chinese Overtime

This formula calculates the new hourly rate for Chinese Overtime. You can use this formula in an overtime accumulator rule within an overtime rule set.

New Hourly Rate = OHRF_TimcardRate + ¹/₂ OHRF_Salary/OHRF_FrequencyOHRF_TotHoursWorked

Time-and-a-Half Overtime Based on Employee Master Rate

This formula multiplies the employee's rate, as specified in the F060116 table, by 1.5 to determine the new hourly rate for overtime. You can use this formula in an overtime accumulator rule within an overtime rule set.

New Hourly Rate = 1.5 x OHRF_EmployeeMasterRate

Time-and-a-Half Overtime Based on Union Rate

This formula multiplies the union rate of a specified column in the F069126 by 1.5 to determine the new hourly rate for overtime.

OHRF_UnionRateA, OHRF_UnionRateB, OHRF_Union RateC, OHRF_UnionRateD, or OHRF_UnionRateR should be substituted for OHRF_UnionRateX in the formula. A, B, C, D, and R each specify particular columns in the F069126 from which to retrieve the rate. You can use this formula in an overtime accumulator rule within an overtime rule set.

New Hourly Rate = 1.5 x OHRF_UnionRateX

Regular Portion of Overtime Based on Employee Master Rate, and Premium Portion of Overtime Based on Average Hourly Rate

This formula uses the employee's rate, as specified in the F060116 table, to derive the regular portion of overtime. The system then multiplies the average hourly rate from the timecards by .5 to determine the premium portion and adds the two rates together to determine the new hourly rate for overtime. You can use this formula in an overtime accumulator rule within an overtime rule set.

New Hourly Rate = OHRF_EmployeeMasterRate + (0.5 x OHRF_WeightedAverageHourlyRate)

Spread Employee's Salary

This function does not calculate an overtime rate; rather, it calculates a new hourly rate for salaried employees by spreading an employee's salary over all of the hours worked.

Spread Employee's Salary is a predefined function that is available for use in the custom rule set rule within an overtime rule set.

Overtime Rate Based on Timecard Rate and Pay Rate Multiplier

This function calculates the overtime rate by retrieving the hourly rate from the original timecard and multiplying it by the pay rate multiplier that is specified on the timecard. This method is typically used for employees in the United States who receive tips. You can use this formula in an overtime accumulator rule within an overtime rule set.

New Hourly Rate = OHRF_PayRateMultiplier x OHRF_TimecardRate

Testing Formulas

After you create a formula and before you process the timecard template, overtime rule set, or retroactive pay rule to which it is attached, test the formula to verify that it is mathematically valid and accurate. The system includes a feature that you can use to automatically validate the formula. However, you need to confirm whether the calculations produce the results that you expect. For example, for a formula that averages two union pay rates, you should verify that it calculates the correct rate.

Using the formula testing functionality, you can automatically calculate the formula for one record at a time. You can then review the results to determine whether they are correct.

You can also test the formula by submitting the timecard template, retroactive pay rule, or overtime rule set for processing, and then reviewing the new timecards to ensure that they are correct. If the timecards are incorrect, you can cancel the batch.

Prerequisite

Create a timecard template, retroactive pay rule, or overtime rule set.

See Setting Up Timecard Automation.

Forms Used to Create and Test Formulas

Creating a Formula

Access the Build Formula form.

To create a formula:

1. Select a record in the detail area, and then click the Insert Function button.

   The system displays the function in the Rate Formula field.

2. Enter the appropriate characters in the Rate Formula field.

3. Select Validate from the Form menu.

   If the formula is mathematically valid, the system displays a confirmation message. If the formula is not mathematically valid, the system highlights the Formula To Test area in red.

4. Revise the formula if it is not valid, and repeat step 3.

5. Click OK when the confirmation window appears.

6. Select Test from the Form menu to test the formula and ensure that the system creates accurate timecards.

7. Complete the steps for testing a formula.

Testing a Formula for a Timecard Template

Access the Formula Tester form..

Employee Number	Enter the employee number that you want to use to test the formula.
Timecard Template Name	Enter a user-defined name for a timecard template.
Validate Formula	Select to verify that the syntax of the formula is mathematically valid. If it is not mathematically valid, the system changes the Formula To Test area to a contrasting color.
Calculate Formula	Select to calculate the formula and display the results in the Output hourly rate box.

Testing a Formula for a Retroactive Pay Rule

Access the Formula Tester form.

Testing a Formula for an Overtime Rule Set

Access the Formula Tester form.