The following information uses a series of examples to help you understand how to use different components to build your Oracle formulas.
To start with a simple example, suppose you wanted to calculate the pay value for the element Wage by multiplying the number of hours an employee works each week by hourly rate. You could write this formula:
wage = hours_worked * hourly_rate
RETURN wage
Note: If you want to know the rules which govern the use of a specific component, refer to Formula Reference.
The first line is an Assignment statement that simply assigns a value to the element Wage. The second line is a Return statement that passes back the Wage value to the payroll run.
In this example, the Wage value is calculated, but it could be a constant value, such as: wage = 200. To calculate the Wage value, Oracle FastFormula needs to get values for the variables hours_worked and hourly_rate. They are called variables because they can contain different values depending, in this example, on which assignment the payroll run is processing.
Both variables and constants can be one of three data types:
numeric
text
date
The variables in the Wage example are numeric.
We have said that Oracle FastFormula needs to get values for the variables hours_worked and hourly_rate. There are three ways it can do this:
Receiving them as input when the formula is called.
Finding the values in the database from database items.
Using global values, which you enter in the Globals window.
To use a database item or global value in your formula, you simply refer to it by name. You can browse through lists of database items in the Formulas window. To use a value passed to the formula at run time, you must write an Inputs statement.
In our Wage example, suppose that hours_worked is an input value to the element Wage. To pass the element input values to the formula during a payroll run, you define an Inputs statement, as follows:
INPUTS ARE hours_worked
wage = hours_worked * hourly_rate
RETURN wage
The name you use in the Inputs statement must be the same as the name of the element input value, and multiple words must be joined by underscores. In this example, the input value hours_worked is numeric. If the input value is not numeric, you must tell Oracle FastFormula whether it is text or date. For example:
INPUTS ARE start_date (date)
Suppose that hourly_rate is a standard rate taken from the Grade Rates table. This is an example of a database item. A database item has a label, or special piece of code, telling Oracle FastFormula the path to take to access the data. Oracle HRMS produces most of the database items you need without you taking any special action. These items include both information unique to your enterprise, which you hold in flexfield segments, and standard information such as assignment numbers and grades.
In the Formulas window, you pick database items from a list. You will see that the name of the database item for a grade rate called hourly_rate is actually grade_hourly_rate_value. This is the name you must use in your formula.
By this naming convention, Oracle FastFormula knows that hourly_rate is a database item from the Grade Rate table. But how does it know which hourly_rate to use from this table? It works this out from the context the payroll run provides for each element entry. The context identifies:
the Business Group
the element and element link
the payroll and payroll run
the employee and employee assignment.
Attention: You should use an Inputs statement in preference to database items where possible because this is more efficient.
Use global values to store information that does not change often, but you refer to frequently, such as Company Name, or company-wide percentages used to calculate certain types of bonus. You define the global value and change its value using the Globals window.
Local variables exist in one formula only. You can change the value of a local variable by assigning it a value in an Assignment statement. In the Wage example, the variable wage itself is a local variable. It receives a value within the formula by the Assignment statement:
wage = hours_worked * hourly_rate
Note: You cannot change the value of input values, database items, or global values within a formula.
The Assignment statement in the wages example above uses a simple multiplication to calculate the value of the wages element. You can also use addition, subtraction, division, and a number of functions. For example:
bonus = GREATEST(days_at_work,163) + bonus_rate
Here the function GREATEST tells Oracle FastFormula to use the value of the variable days_at_work, if it is greater than 163, and otherwise to use the constant 163.
The data type of variables and constants determines how operators and functions act on the values. For example, the addition operator (+) can be used with numeric or text data, while division can be used with numeric data only.
There are special functions that convert variables from:
numbers to text (TO_TEXT)
dates to text (TO_TEXT)
text to date (TO_DATE)
text to number (TO_NUM)
See: Functions
The Assignment statement can use as many arithmetic operators and functions as you require. Each function or calculation is one expression, and you can nest expressions to create more complex calculations. You must use brackets to make clear to Oracle FastFormula the order in which the calculations are performed. For example:
ANNUAL_BONUS = trunc(((((salary_amount/100)*
bonus_percentage)/183)*(days_between(end_period_date,
start_date) + 1)), 2)
Oracle FastFormula begins calculating inside the brackets and from left to right, in the following steps:
salary_amount/100
1. * bonus_percentage
2. / 183
days_between (end_period_date, start_date)
4. + 1
3. * 5.
TRUNC(6.,2)
Notice that TRUNC is another function. It rounds a numeric value down to the number of decimal places specified after the comma (two in this case).
In our simple Wage element example, only one value is returned, and it is calculated in the same way for every assignment. However you may need to perform different calculations depending on the particular group of employee assignments, or the time of the year, or some other factors. You can do this by incorporating conditions in your formula.
For example:
IF age < 20 THEN
training_allowance = 30
ELSE
training_allowance = 0
The formula checks whether the condition (age < 20) is true or false. If it is true, the formula processes the statement that follows the word THEN. If the condition is not true, the formula ignores this statement and processes any statement that follows the word ELSE. The ELSE clause is optional.
In the example above, the condition compares a variable (age) to a constant (20). The condition can be more complex, comparing expressions that contain functions and arithmetic operators. For example:
IF (DAYS_BETWEEN(end_period_date, start_date)+1) >= threshold
DAYS_BETWEEN is another function.
We have seen two comparators: less than (<) and greater than or equal to (>=). A full list of the comparators you can use appears in the Reference section.
See: Formula Reference
There is a special type of condition called WAS DEFAULTED. Use this to test whether a default value has been placed in an input value or database item. Default values are placed using the Default statement. 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'
In this example, if the database item hourly_rate is empty (NULL), the formula uses the default value of 3.00 and issues a warning message.
You can combine conditions using the logical operators AND, OR, NOT.
Use AND if you want an action to occur when more than one condition is true. For example:
IF (days_between(end_period_date, start_date) + 1) >= 183
AND employee_status = 'FULL TIME'
THEN . . .
Use OR if you want an action to occur when any one of two or more conditions is true. For example:
IF stock_level < 10000
OR order_size >= 1500
THEN . . .
Use NOT if you want an action to occur when a condition is not true. For example:
IF NOT (months_between(purchase_date, system_date) => 60
THEN . . .
As with Assignment statements, you may need to use brackets to tell Oracle FastFormula in which order to test conditions. By default, NOT has the highest precedence, followed by AND then OR. So the following condition:
IF X = 6 AND NOT Y = 7 OR P >= 6
is interpreted as:
IF ((X = 6) AND (NOT (Y = 7))) OR (P >= 6)
How you use brackets can change dramatically the meaning of a formula.
Suggestion: Use brackets whenever you create multiple conditions, so that the meaning of the formula is clear to other readers.
We have seen how to make conditions more complex. You can also make the actions performed as complex as you like. In our simple example above, the result of the condition was the assignment of a value to the variable training_allowance. As well as assigning values, you can perform calculations and return values.
For example, suppose you must check whether there are sufficient funds in a bank account before processing a withdrawal:
INPUTS ARE acct_balance,
acct (text),
debit_amt
IF acct_balance >= debit_amt
THEN
(
new_balance = acct_balance - debit_amt
RETURN new_balance
)
ELSE
(
message = 'Account No. ' + acct + ' - Insufficient Funds'
message2 = 'Account Balance is ' + TO_TEXT(acct_balance)
RETURN message, message2
)
Notice that you can return two variables in the same Return statement.
Attention: The brackets following THEN and ELSE are essential when you have multiple actions based on a condition. Without them, Oracle FastFormula processes the first statement conditionally and the other statements unconditionally.