Oacle HR/Payroll Fast Formula Introdouction

Posted on by

Introduction

Oracle E-Biz is a huge set of programs, combined together in a way that the data across modules are closely connected to enforce business logic. It is designed with a lot of sophistication, in order to help businesses achieve their goals. Again the product was designed to cater to all kind of requirements to all sorts of businesses. Hence it is equally scalable, so that businesses can change the settings in a way their business rules want them to.

Configurability was at the centre of the focus when they built the product. With that in mind, Oracle has tried to give as many interfaces/ codes as possible, to the functional consultants, where they can key in codes and tweak the flow of the system and processes as per their demand. However usage of codes is not possible in each and every process of the application. There was a need of sub-programs that can be written by the Functional guys, so that it will be referred by the application to decide on the process. Those are like decision making sub-programs that ultimately return values, with which the application can decide the flow. Those sub-programs are called Fast Formulae.

Fast formulas / Rules are used whenever the codes are not adequate to meet the business requirement. And the codes are not adequate, because there is a complex logic involved to reach at a decision. But what are codes?  If you remember the flags that we check on the forms, like, we had in Organization screen; we added classifications, and checked the enabled flag. Let’s concentrate on the enabled flag now. What does it do? It tells the system, that the selected classification is valid and available. That’s what we call a decisive code, the code that tells the system to do something.

Picture a case where the decision is not so easy like a yes/ no. Think of this example, that the Classification should be valid only if (A=B and B=C and C<> D). This is where the code cannot be used, and we need a set of statements / subprograms to arrive at the decision. Those sub programs are called Fast Formulae.

Fast Formula is a Sub-Program that can be written by the functional Consultants. It is written in a language that resembles SQL, however a lot simplified. Although the syntax is like SQL, it looks a lot like English. It’s a whole language in itself and is very easy to learn. Even though a lot of functionalities are absent in comparison to SQL, there is provision to achieve any requirement using Fast Formulae. The trade off for simplicity of the language is the absence of complex functionalities.

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’s learn it as a Programming language first. Once we know the different programming constructs and the flow of the language, we will then focus on the input and return values.

Chapter Overview

This chapter talks about:

  • Fast formula and the need of it
  • Programming constructs of a Fast Formula
  • How to write a Fast Formula
  • Defining and using User Functions
  • Using Database Items, Global Variables and User Functions in Formulae
  • Formula Contexts and Parameters
  • Using PLSQL in Fast Formulae
  • Compiling a Formula
  • Tuning a Formula
  • Examples of Fast Formula

Learning Outcomes

After going through this Chapter, you should be able to:

  • Understand the need and usage of fast formulae
  • Write user functions and use them in formulae
  • Use Global Variables, Database items and PLSQL in fast formulae
  • Compile and fine-tune a fast formula
 

The Dictionary

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 runtime.

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’
  5. If we have multiple language support in our applications, then we must use the second type of dates.

Data Type

Oracle FF 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 later in this chapter. Let’s take the following example with arithmetic operators to learn about the expressions.

RATE = PREMIUM_VALUE – EMPLOYER_CONTRIBUTION

Here RATE is a Variable of type Number. PREMIUM_VALUE is a Constant of numbers and so is EMPLOYER_CONTRIBUTION. Now the resultant of the arithmetic subtraction between these two numbers will be stored in the variable “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 runtime.  The Global Variable can be accessed from any fast formula.

Functions

There are a set of functionalities 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 reusability 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.