Formula Writing Techniques (Oracle HRMS)

Formula Writing Techniques

When writing formulas there are a number of techniques you should follow to ensure your formulas are easy to read, use and understand.

Commenting Formula

It is good practice to include comments in your formulas to explain to other people what the formula does.

So, for example, you can name your formula:

	/*  Formula:  Attendance Bonus  */

and write an explanation of your formula:

	/*  Use this formula to calculate the annual bonus for

	    clerical staff.  Employees receive either a percentage of

	    their salary (if they have been present for 183 or more

	    days in the last six months), or a pro rata bonus (if they

	    have been in attendance for less than 183 days in the last 

	    six months).  */

Oracle FastFormula ignores everything between the comment delimiters: /* and */. You can place comments anywhere in a formula without affecting the formula's performance.

Caution: Do not put a comment within a comment. This causes Oracle FastFormula to return a syntax error.

You can use a comment to explain what part of your formula does. So, for example, you might want a comment explaining who decides the bonus percentage:

	INPUTS ARE  salary_amount,

	            start_date (date),

	            end_period_date (date),

	            bonus_percentage /*  decided at board level */

You can also use comments to `comment out' parts of the formula you do not currently want to use. So, for example, you can include a fifth input of employee_status, ensuring that employees with a status of full time are awarded a bonus. However, as you do not yet have a range of statuses, you do not currently need the fifth input.

	INPUTS ARE  salary_amount,

	            start_date (date),

	            end_period_date (date),

	            bonus_percentage /*  decided at board level  */

	        /*  employee_status (text)  */

Use comments and white space freely when entering formulas. This makes the formulas easier to read and understand, and has no effect on performance or memory usage. Use indentation for the same reason, especially when you are using brackets to control the order of processing.

It is good practice to include the following information in a comment at the beginning of a formula:

Formula title and short statement of its purpose
Description of formula inputs
List of variables and constants that may require updating
Description of the input values of the element that receives the formula's direct result
Explanation of the formula's calculations
Administrative information such as the name, address and telephone number of an office administering the earnings, deduction, or charge the formula affects
The dates of formula modifications, the names of those entering the edits, and possibly the reasons for change

Alias Statements

Database items are named by the system when it creates them, and sometimes these names are too long to conveniently use in a formula. You cannot shorten the name of a database item (or a global value) itself, but you can set up an alternative shorter name to use within the formula. For example:

	ALIAS  as_overtime_qualifying_length_of_service AS ot_qls

In the rest of the formula, you can use the alias (in this example, ot_qls) as if it were the actual variable.

Attention: Using an Alias is more efficient than assigning the database item to a local variable with a short name.

Default Statements

Use the Default statement to set a default value for an input value or a database item. The formula uses the default value if the database item is empty or no input value is provided when you run the formula. For example:

	DEFAULT FOR hourly_rate IS 3.00

	X = hours_worked * hourly_rate

	IF hourly_rate WAS DEFAULTED

	  THEN

	    MESG = 'Warning: hourly rate defaulted'

This example sets a default of 3.00 for the database item hourly_rate. If hourly_rate is empty (NULL) in the database, the formula uses the default value of 3.00. The formula uses the 'WAS DEFAULTED' test to detect when a default value is used, in which case it issues a warning message.

Attention: You must use the Default statement for database items that can be empty. The Database Items window includes a check box labelled Default Required. This check box is checked for database items that can be empty. The Database Items window appears when you choose the Show Items button on the Formulas window.

Writing Efficient Payroll Calculation Formulas

The following guidelines are generally true for typical payroll runs:

The longer an element's formula, the longer its processing time.
The more elements entered for an assignment, the longer its processing time.
One element associated with a lengthy formula usually processes faster than two related elements each associated with a short formula.
The overall number of elements and formulas in the system has little effect on processing efficiency. It is the number of elements per assignment that affects processing time.

Variable Names and Aliases

To improve readability use names that are brief yet meaningful. Name length has no effect on performance or memory usage. Use Aliases if the names of database items or global values are long.

Input Statements

Use Input statements rather than database items whenever possible. This improves formula processing by as much as a factor of ten. It speeds up the running of your payroll by eliminating the need to access the database for the input values.

Inefficient:

	Salary = Salary_annual_salary / 12

	         RETURN Salary

Efficient:

	INPUTS ARE Annual_salary

	Salary = Annual_salary / 12

	         RETURN Salary

Date Literals

Use the TO_DATE function only when the operand is a variable.

Inefficient:

	Start_date = TO_DATE ( '1992-01-12 00:00:00' )

Efficient:

	Start_date = '1992-01-12 00:00:00' (date)

Single Expressions

Use a single expression in straightforward formulas where this does not lead to confusion.

Inefficient:

	Temp = Salary / Annualizing_factor

	Tax = Temp * 3

Efficient:

	Tax = (Salary / Annualizing_factor) * 3

Database Items

Do not refer to database items until you need them. People sometimes list at the top of a formula all the database items the formula might need, thinking this helps Oracle FastFormula process more quickly. However, this in fact slows processing by causing unnecessary database calls.

Inefficient:

	S = Salary

	A = Age

	IF S < 20000 THEN

		IF A < 20 THEN

		Training_allowance = 30

	ELSE

		Training_allowance = 0

Efficient:

	IF Salary < 20000 THEN

		IF Age < 20 THEN

			Training_allowance = 30

		ELSE

			Training_allowance = 0

The first example always causes a database fetch for Age whereas the second only fetches Age if Salary is less than 20000.

Balance Dimensions for UK HRMS

Wherever possible, use balance dimensions for single assignments only in formulas. Multiple assignments require more calculation, leading to slower processing time. The number of genuine multiple assignments in a payroll is not normally high, and the presence of a small number does not lead to any significant increase in overall processing time. There could be a problem, however, if you unnecessarily link balance dimensions for multiple assignments into general formulas.

Proration Formulas for UK HRMS

You set up proration formulas to enable element values to be calculated accurately if they change during a payroll period, for example, if an employee leaves the company or if their pay rate changes.

For more detailed information on proration, see the Technical Essay entitled Proration available on My Oracle Support.