HRMS: Oracle Fast Formula

Oracle Fast Formula (FF) is a simple way to write formulas using English words & basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language. Oracle stores fast formulas as a database packages

 
Common tables
SELECT *
FROM all_objects
WHERE object_type = 'TABLE' AND object_name LIKE 'FF%'

FF_FUNCTIONS
FF_FUNCTION_PARAMETERS
FF_FORMULAS_F
FF_FORMULA_TYPES
FF_DATABASE_ITEMS
FF_GLOBALS_F


SELECT *
FROM all_objects
WHERE object_type like  'Pack%' 
AND (object_name LIKE 'FFP_%' or object_name LIKE 'FFW_%')


Uses of Oracle FastFormula
In HRMS, Oracle FastFormula is used for validation, to perform calculations, and to specify rules. Here are some examples.

In Payroll, you use formulas to:
• Validate element inputs
• Calculate element pay values and run results during payroll processing
• Specify the rules for skipping an element during payroll processing
• Perform legislative checks during a payroll run

In Compensation and Benefits Management, you use formulas to:
• Specify the rules for Paid Time Off accrual plans, such as how much time is accrued and over what period, when new starters are eligible to begin accruing time, and how much time can be carried over to the next accrual term.
• Define custom calculations for benefits administration.
• Calculate the duration of an absence given the start and end dates and times.
• Create rules for benefits administration such as eligibility determination.

In People Management, fast formulas are used to:
• Check that element entry values are valid for an assignment
• Specify the criteria for including an assignment in an assignment set and to edit assignment sets
• Configure the people management templates in a number of ways such as supplying additional information to be available from fields on the template and validating field entries.
• Define collective agreements
• Generate custom global person number sequences for employees, applicants, and contingent workers.

Components of Formulas
Formulas are made up of a number of different components. These can include assignment statements, different types of input including database items, functions, nested expressions, and conditions.

1) Assignment and Return Statements.
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
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.

2) Constants and Variables.
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.

3) Data Types.
Both variables and constants can be one of three data types:
• Numeric
• Text
• Date

4) Types of Input.
Values for the variables hours_worked and hourly_rate can be populated using three ways:
• 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.

Input Statements
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)

Database Items
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. These items include both information unique to your enterprise, which you hold in flexfield segments & standard information such as assignment numbers and grades etc. In the Formulas window, you pick database items from a list. There are two types of DB Items: Static and Dynamic.

Static database items are shipped with the system and you cannot modify them.
Dynamic database items are created by Oracle HRMS processes whenever you define new elements or other related entities.

Element Database Items:
When you define a new element, Oracle HRMS runs a process to create a number of related database items for it. To ensure easy recognition of these items, the process adds the element name to each one. It also creates further database items for each pay and input value you use .

Global Variables
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
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

Functions
Oracle FastFormula provides functions that manipulate data in different ways.
GREATEST,INITCAP,LEAST LENTH,INSTR,LOWER,RTRIM,LTRIM,ABS,REPLACE SUBSTRING,TRANSLATE, CALCULATE_HOURS_WORKED,FLOOR,ROUND,TRUNC, ETC( For full list refer the Fast Formula Guide).
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)

Nested Expressions
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:

1. salary_amount/100
2. 1. * bonus_percentage
3. 2. / 183
4. days_between (end_period_date, start_date)
5. 4. + 1
6. 3. * 5.
7. TRUNC(6.,2)

Incorporating Conditions
In 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.

Simple Conditions
For example:
IF age < training_allowance =" 30" training_allowance =" 0">

IF (DAYS_BETWEEN(end_period_date, start_date)+1) >= threshold_value WAS DEFAULTED
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.

Combined Conditions
You can combine conditions using the logical operators AND, OR, NOT.

Commenting Formula
You must include comments in your formulas to make them easier to read and understand what the formula does. For example, you can name the formula as:

/* Formula: Calculates Duration of Absence */
Caution: Do not put a comment within a comment. This causes Oracle FastFormula to return a syntax error.

Alias Statements
Sometimes DB Item names are too long to conveniently use in a formula. Set up an alternative shorter name to use within the formula. For example: ALIAS as_qualifying_length_of_service AS as_los In the rest of the formula, you can use the alias (in this example, as_los) as if it were
the actual variable (as_qualifying_length_of_service).

Default Statements
It is used 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.

Important: You must use the Default statement for database items that can be empty. The Database Items window includes a check box labeled 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.
How to Compile FastFormula?
After creating or editing a formula in the Formula window, choose the Verify button to compile it.
If you need to compile many formulas at the same time, you can run the concurrent program “Bulk Compile Formulas process” in the Submit Requests window.

Formula Errors
There are two types of error that can occur when using Oracle FastFormula:
• Verify-time errors occur in the Formulas window when you run the formula verification. An error message explains the nature of the error. Common verify-time errors are syntax errors resulting from typing mistakes.

• Run-time errors occur when a problem arises while a formula is running. The usual cause is a data problem, either in the formula or in the application database. The basic Oracle FastFormula errors that can occur at run-time are:

• Uninitialized Variables: An uninitialized local variable is one that has no value when the formula runs. The term ’uninitialized’ means you have not assigned any value to the variable before you try to use it. This causes an error in all statements except the Return statement. For example:
IF (tax_band < tax =" salary"> 2000)
THEN tax = salary / 10
IF tax > 1000
THEN...
This formula fails with an ’Uninitialized variable’ message (for the variable tax) if the tax band is set to 2000.

• Divide by Zero: Dividing a number by zero is an operation that provides no logical result. If this situation ever arises, Oracle FastFormula passes a code back to the application indicating an error (the application then takes the appropriate action).Always check for the possibility of a divide by zero error if there is any chance it could occur. For example, the formula:
x = salary/contribution_proportion
produces an error if the contribution proportion is set to zero. In this formula, check
for the divide by zero condition as follows:
IF contribution_proportion = 0
THEN

(

message = 'The contribution proportion is not valid.'

RETURN message

)
ELSE x = salary/contribution_proportion

• No Data Found: A database item supposed to be in the database was not found. This
represents an error in the application data.

• Too Many Rows: The database item definition within the application caused more than one value to be fetched from the database.

• Value Exceeded Allowable Range: This can occur for a variety of reasons such as:

• exceeding the maximum allowable length of a string (which is 240 characters)
• rounding up a number to an excessive number of places, for example, round (1,100)
• using an invalid date, for example, 39-DEC-1990.

• Invalid Number: This occurs only when a database item contains an item that does
not make sense as a number.

• Null Data Found: A database item was found to have a null value when it should have had a non-null value. Use the Default statement for database items marked as Default Required in the Database Items window.

Also you can call PL/SQL procedure from the Fast formula.