Payroll Fast Formula

Oracle Fast Formula is a simple way to write formulas using English words and basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language.

Oracle has provided places to attach formulae, whenever there could be a need of a complex logic that a code cannot suffice. Each and every Fast Formula has a type with a set of allowed Input and Return Values. Let us now look at the building blocks of a fast formula -

Variables

While writing a program, we will need different place holders that can hold values. Let’s take an example of a swapping program. We want to swap the value between A to B. To do this, what should we do? Get a new place holder called X, which can hold the values for us, and run the following statements.

X = A

A = B

B = X

Here X is a place holder. This place holder is called a variable, which can store a value at one point of time. And the value can be changed as per the requirement of the program during the execution of the program in other words, at the run-time.

Constants

A Constant is a Variable that does not change its value throughout the program. Like the mathematical value of Pi is approximately at 3.141. If we wish to change its value to 2, we cannot do it; because it is a constant.

There are few rules we must follow while using Constants:

1. If the constant is of type Numeric, then we should not use comma in the numbers.
2. We should not use Exponential values as Constants.
3. The Text Constants should always be in single quotes.
4.  If we are using a  date constant, we can follow two different types of date formats:
• ‘DD-MON-YYYY’ like: ’01-AUG-1984’
•  ‘YYYY-MON-DD HH24:MI:SS’ like ‘1985-JAN-18 14:05:11’

Data Type

Oracle Fast Formula supports three types of data.

• Numeric: For number
• Text: For Text / Characters and strings
• Date

So, any Variable or Constant will have to be one of these three data types.

Expressions

An Expression is a combination of Variables and Constants with either an Arithmetic operator or a Function. We will park functions as of now, because we are going to learn it late. Let’s take the following example with arithmetic operators to learn about the expressions.

OVERTIME_RATE = FIXED_ALLOWANCE / OVERTIME_HOURS

Here OVERTIME_RATE, OVERTIME_HOURS are Variables of type Number whereas FIXED_ALLOWANCE is a Constant of numbers. Now the resultant of the arithmetic division of fixed_allowance and  overtime_hours will be stored in the variable “Overtime_Rate”. This entire sentence can be called as an Expression.

Database Items

In a formula, we might need the details of various stored information to calculate something. For Example, Age of an employee, Employees basic Salary YTD (Year to date: Basic Salary earned by an employee till date in this year) etc. In a case we need these types of values; we can get the data from the database by running a query. However fetching these values are difficult inside a Fast Formula and it drastically impacts the performance of the formula.

To solve this issue, Oracle has come up with a concept of Database Items. These are hidden queries created and stored by oracle, with a name. We will just have to refer the name in the formula and oracle runs the related query in the backend and gets us the results. 

Global Values

There are few variables that do not change very frequently. For an example, Company wide Bonus Percentage, Company’s short name etc. these values do not change very frequently. To store these kinds of values, we can use Lookups, but to use the value in the Fast Formula, we must have a query to get the data from the lookup. So Oracle has provided something called a Global Variable. The Global value is stored in a date tracked table, and can be easily used, with the Global Variable name. With this, we do not have to write a query to fetch the value stored in the Global Variable; we can just mention the name and the formula will fetch the value at the run-time.  The Global Variable can be accessed from any fast formula.

Functions

There are a set of functionality that we need very frequently in fast formulae. Like calculating the Greatest of three numbers, Average of two numbers etc. Rather than adding the code every time in the fast formula, oracle gives us the liberty of storing that code somewhere and just using the code whenever necessary. This design is used keeping the code re-usability in mind. Those codes are called Functions. These are few advantages:

• Once written the Function can be used in any Fast formula (If contexts match, we will discuss about the contexts later).
• Oracle gives us a wide set of seeded functions that can be used in fast formulae.
• Oracle enables us to define our own Functions, and use them across formulae.
• The User Defined Functions are capable of calling PL/SQL functions, which makes it easy for the user to define complex business processes with ease.

PREVIOUS POST || NEXT POST