Writing a Formula in Oracle HRMS

Writing a Formula

As we are aware, based on the place where it is attached to, the input and the return variables of a formula vary. That is why, different formula types are defined based on the purpose and provision. Few Examples are:

Accrual Formula: Determines the leave accruals of a person.
Age Calculation Formula: Calculates the age of a Person in derived factors.
Rounding Formula: To calculate the Rounded value.

Each and every formula type has its own set of Input Values and Return values. There are around 150 formula types in HR, and their details are huge, so it is not possible for us to discuss all sorts of formulae here, however we will discuss a few types that are important. In case we need to know the specifics of a Particular formula, there are documents available on Oracle support that we can download. As we had decided earlier, we will focus primarily on the language.

Defining a Formula

We will start with defining a formula. There is just one screen to deal with. See Figure 5.1 – Write Formula.

Responsibility: Super HRMS Manager
Navigation: Total Compensation -> Basic ->Write Formulas

(Figure 5.1 – Write Formula)

Name	Name of the Formula
Type	Type of the Formula
Verified	This flag denotes that the formula is compiled and no errors were found
From Date	The start date of the record
To Date	The end date of the record
Edit	This opens the editor where we actually write the program
Show Items	This opens the database Item Box. We will learn more about these later
Input Values	This opens the Input Values box with all the Elements and their available input values

Defining Variables

Variables are the place holders in which we save values for our calculation. Although all the place holders that are used to store values are known as the Variables; there are different types of them.
There are three types of variables available in Fast Formulae.

Local variables: These are the most frequently used variables. These variables are defined inside a formula, and they stay valid only within the scope of the formula. We use local variables to store temporary information, which is used later in the program. In the example of swapping values from A to B, X was the temporary variable. Usually the frequently changing information is stored in Local Variable.

Global variables: These variables, as the name suggests, has a global scope in comparison to the Local Variables. The Global variables can be accessed by all Formulae within the application, and the value of a global variable cannot be updated with in the Formula. We usually store the slowly changing information in these variables. We use the Global Value screens to create and update Global Variables.

Database Items: The third category of the variable type will be the DBIs. As we know, DBIs are the values retrieved by stored queries in the application, which is hidden from the front end.

Using Data Types

Data types define the type of data being stored in the variable. There can be three types of data in Oracle Fast Formulae:

Text
Number
Date

While defining a variable, we must define its data type. If the data type is not added along with the variable, the compiler takes it as a Number.

Using Operators

Operators add meaning to an expression. Let’s take an example of an Expression.

IF [ ( ( A + B ) = C ) OR ( ( P – Q ) <> R ) ] THEN
……………

In the example given above, the portion between IF and THEN can be called as an Expression, which looks like this:

[ ( ( A + B ) = C ) OR ( ( P – Q ) <> R ) ]

Now, the symbols +,-, =, <>, ‘OR’ are called operators. The Operators instruct the compiler about the arithmetic / logical action that needs to be taken on the variables to arrive at a result of the expression.

There are two types of Operators that are used in Formulas

Arithmetic Operators: used in arithmetic calculations. Example: +, -, *, / etc.

Logical Operators: used in logical calculations. Example: =, <>, <, >, OR, AND etc.

Let’s take some time to look at them individually.

Operator	Meaning
+	Adds two values. In case of text, it adds the two strings and makes it one.
-	Subtracts two values.
*	Multiplies two values
/	Divides the first value with the second.
=	There are two usages. 1. Determines if the expressions on both sides match. If they do, it’s a Pass. 2. Assigns the Right side value to the variable on the left side.
<>, !=, ><	Determines if the expressions on both sides do not match. If they don’t, it’s a Pass.
>	Determines if the value on the left side is bigger than that the one on the right. If it is, It’s a pass.
<	Determines if the value on the left side is smaller than that the one on the right. If it is, It’s a pass.
> =	It is a pass, if the value / variable on the left side is bigger than or equal to the one on its right.
< =	It is a pass, if the value / variable on the left side is smaller than or equal to the one on its right.
Like	It is a pass, if the value on the left side is matching with the right. The “Like” Operator is similar to that of ‘=’, with only two differences. Like works only with Text variables/ constants Like takes the wild card character ‘%’ (any number of characters) and ‘_’ (One character). NOTE: Oracle fast Formulae are case Sensitive to the Text Values. ‘Joe’ and ‘JOE’ are not same.
Not Like	It works similar to the ‘Like’ Operator; however it works like the inversion of the later.

There are few things that we must keep in mind while using Arithmetic operators:

The second operand on a division should never be ‘ZERO’.

We must not use two big numbers for multiplication, if the resultant is too big, it errors.

So is the case with subtraction, if the resultant is a negative number. It errors if the resultant is a big negative number.

Flow of an Expression

In an Expression, the compiler executes the statements from left to right taking the brackets as precedence.

Let’s take an example to understand it better:

X = [ ( ( A + B ) / C ) * ( ( P – Q ) * R ) ]

In this Expression, there are four different arithmetic calculations to be done. The way Oracle Fast Formula decides the flow is:

It goes from Left to Right

Calculates the expressions inside the Brackets first.

So, if we implement the rules, we will see, the Fast formula will execute the statement in this flow:

A + B
( Result of 1 ) / C
P – Q
( Result of 3 ) * R
(Result of 2) * (Result of 4)
Result of 5 is assigned to X

Usually the Expression uses operands of same data type to arrive at a resultant. However, there are a few exceptions like:

X = DAYS_BETWEEN (Date1, Date2) + 9

In this expression, X will have a numeric value, as DAYS_BETWEEN is a function that returns a number, which is the difference of days between the two dates. The resultant number of the function is then added to the number 9 and then the final result is stored in the Variable X. So what does that show? Even though the operands are of a certain data type, the resultant data type may vary based on the functions.

Statements

A statement is a sentence that completes a task in a Program. There can be four types of statements.

Input Statement
Assignment statement
Conditional Statement
Return Statement

Let’s take the most famous Formula example in payroll

=============================================================

1.  Inputs are HOURS_WORKED, HOURLY_RATE

2.  If HOURS_WORKED > 0 then

3.  WAGE = HOURS_WORKED * HOURLY_RATE

4.  Else

5.  WAGE = 1 * HOURLY_RATE

6.  RETURN WAGE

=============================================================

Now, let’s analyze the Formula.

The statement 1 is an Input statement.

Statement 2 and 4 are conditional statements.

Statement 3 and 5 are assignment statements.

And statement 6 is a return Statement.

Input Statement

The input statements are used, to pass the Input values from element into our formula. Each and every element has some input values attached to it, which hold the values. In a formula, if we are using Input Values, we need to write a statement that tells the formula about the different Input values in use.

Like we have used in the example:

Inputs are HOURS_WORKED, HOURLY_RATE

Here, the HOURS_WORKED and the HOURLY_RATE are the two Input values from the Elements.

The syntax is:

INPUTS ARE Input_value_1 (data type) [, Input_value_2 (data type)]

The FF Compiler takes Number as the default data type. If the INPUT_VALUE is mentioned without any data type, the compiler takes them as a Number. For Text ad Date data types, we must add the variable name along with the Input value. In our example, both the Input Values are of Numeric type.

Assignment Statement

The assignment statement represents assignment of a variable. With this statement, a variable gets updated with a new value.

The syntax is:

Variable = expression

Like:

WAGE = HOURS_WORKED * HOURLY_RATE

The Resultant of the expression on the left hand side is calculated and placed on the variable on the left hand side, which must be a Local Variable, as we know no other types of variable can be updated with in a formula.

Conditional Statement

A Conditional statement has a condition and a resultant attached to it. The resultant is executed if the condition matches.

The syntax is:

IF condition THEN

statement1

ELSE

statement2

Here, if the condition matches, then the statement1 is executed; and if the condition does not match, then the statement2 is executed.

Notes

Oracle Fast Formula does not understand “End If”; unlike other programming languages

The Conditions in an IF statement can be Multiple with logical operators like AND / OR. If the entire condition passes, it’s a pass. For an example, we can have an IF Statement like this:

IF [((A=B) AND (C=D)) OR (E=F)] THEN

……..

To simplify the above example, we can write it like this.

1. IF [

2. (

3. (A=B) AND (C=D)

4. ) OR

5. (E=F)

6. ] THEN

Here, based on the logical operator settings, we can be sure that the condition passes, if either 3 or 5 is a pass.

If more than one statement is to be executed inside an IF, we can take help of brackets. In case of absence of brackets, the compiler takes the first statement as part of the conditional statements and executes the rest unconditionally.

Take this example:

IF A = B THEN
(

X = R

Y= S

)

Here, the brackets ensure both statements get executed when the condition is a pass.
Now, let’s have a look at this:

IF A = B THEN

X = R

Y = S

In this case only the statement ‘X = R’ is executed only if the condition matches, and statement ‘Y = S’ is executed irrespective of the condition given above. So what did we conclude? The application takes just one statement after the IF statement as part of the condition, if there are no brackets in it. It makes sense, right?

Return Statement

Each and every formula type expects a set of resultants from the formula to be fed to the application. So if we have attached a formula at a place in the application, the application expects the resultants in order to decide on the next action. Those resultants are known as the Return Values.

Every formula must have one, and may have more than one return Values. Those return values are given back to the application with the use of Return Statements. Oracle Fast Formula stops executing the formula, when it finds the Return Statement.

In Our Example, the wage was calculated with the arithmetic formulas, and at the end, the calculated wage is returned back to the application by the return statement which looks like this: “RETURN WAGE”. Did we see the variable Wage is not initialized? That is because the formula knows its return type, and hence automatically defines the local variable in that data type.

Using Input values

Input values are place holders to store values for a particular element entry. For an example, if we have an element called “Weekly Wage” and it has an input value as “HOURS_WORKED” then, then we can use the input value to store the number of hours worked by the employee in employee’s element entries.

Later, we can use the Input value in my formula to calculate the wage multiplying the HOURLY_RATE with the HOURS_WORKED. Simple, isn’t it? Input values come handy in FFs, because we can directly use the input value name in the fast formula, and based on the ASSIGNMENT_ID and the Payroll run being evaluated, the Hours Worked will be pulled from the element entries and can be used directly there, without any extra line of code. In the Write fast Formula screen, we have a button for Input values, which lists all the valid Element types and the corresponding Input Values from which we can choose the one we are going to use.

Input values can be used in the FF, only after we define the Inputs with an Input statement. An Input statement looks like this:

Inputs are HOURS_WORKED, HOURLY_RATE

Here, the HOURS_WORKED and HOURLY_RATE are the two input values that can be used in the fast formula. Let’s look at few points related to Input Values and their usages.

As both these input values in the example, are numbers we did not have to add the data type in the statement, however for any other data type, we must have the data type added in brackets just after the Input value name.

We must make sure the Input values are not null for any assignment. In such cases our calculation might go wrong. To avoid such issues, we can use the Default keyword. The statement might look like:

Default for HOURLY_RATE is 35.00

Was Defaulted

We just discussed about the default statement. So when will the default value of 35 be applied over the HOURLY_RATE? It will be defaulted, only when the Input value for the assignment being evaluated for the Payroll run is Null.

We can always check, if the Input value was defaulted or not, by using the “was defaulted” key word in a conditional statement.

The syntax looks like this:

IF HOURLY_RATE WAS DEFAULTED THEN
………

For an example, in a fast formula, we need the assignment_id of the employee being evaluated. In this case, we might default the assignment_id as ‘0’. So whenever the syatem will not find a valid (Not Null) assignment_id it will assign it a value ‘0’. However before using the assignment_id, we might want to check if the data was fetched properly. In this case, we would use Was Defaulted to check if the value was retrieved or was just defaulted. Based on that fact, we can write the next set of statements.

Global Variables

As we know, we store the Global information in Global variables. We store the information that does not change frequently. Let’s see how to configure the Global Values. See Figure 5.2 – Global Values.

Responsibility: Super HRMS Manager
Navigation: Total Compensation -> Basic ->Write Formulas

(Figure 5.2 – Global Values)

Name	Name of the Global Value.
Description	Description, a free text.
Type	Type of the variable. Could be Date, Number or Text.
Value	The Value of the Global Value.
Start Date	Start Date of the Global Value.
End Date	End date.

Using Database Items

Database Items are the hidden queries used by the application to provide fast information in the Fast Formula. The query actually uses a set of values as Contexts, which is passed by the Fast Formula to the DBI. Using the context the query pulls the data from the application tables, using the defined query.

Question, what are the data that are available in DBIs? There are many, mainly the assignment details, Contact details, Input Values, Balances etc.

· For the Input Values we must check the create Database Item flag in Input Value definition screen in elements, in order to create a DBI for the same.

· For DFFs and KFFs, we must run the “Create Descriptive Flex field DB Items” process and “Create Key Flex field DB Items process” respectively, to have the DBIs created.

· For all others the DBI gets created without any added tasks.

There is a button called “Show Items” in the Write Formula Screen. This button opens up a form that lists all the Database Items that are available to the formula type. Now, how does the system identify if a DBI should be available to the formula type or not? It is the context.

Every formula runs with a set of contexts. The contexts are:

·         BUSINESS_GROUP_ID
·         ASSIGNMENT_ID or PERSON_ID
·         ELEMENT_TYPE_ID AND ELEMENT_LINK_ID
·         PAYROLL_ID, RUN_RESULT_ID

Based on the type of formula, the contexts are used. And Most of the DBIs need contexts to provide required results. Because the query in the DBI, needs some inputs and the inputs are given by the Contexts. So if the context is missing, the DBIs will not be able to pull the data. For an example, we will not need payroll related contexts, if we are writing a formula for Benefits. So a Benefits Formula will not have Payroll contexts and eventually will not have the payroll related DBIs. So it is always advised to check the “Show Items” button while writing the formula, to be sure if the DBI is available in there.

There are two types of DBIs.

1. Static Database Items

2. Dynamic Database Items

The static database Items are seeded along with the application. They get created during the time of Implementation. Examples of Static DBIs are:

· PER_LAST_NAME: Stores the last name of the Person.

· PAY_PERIODS_PER_YEAR: Stores the number of Pay periods in a year for a payroll.

· ASG_SALARY_BASIS: Salary basis of an employee.

The Dynamic Database Items, as the name suggests are created dynamically while we create an Element / absence type / DFF etc. The Different types are:

· Flex Field DBIs

o Created only after the Create Descriptive/ Key Flex field DB Items process is run

o It adds the Segment name to the end of the DBI name for uniqueness.

o Example: PEOPLE_<Segment Name>

· Element DBIs

o Created when the Elements are created.

o It adds the Element Name and Input Value name as a prefix for uniqueness.

o Example: <Element Name>_<Input Value Name>_ENTRY_VALUE

· Grade Rates DBIs / Pay Scale DBIs

o Created when a pay scale / grade rate is defined.

o It adds Grade name and the pay scale name to the DBI’s name for uniqueness

· Absence DBIs

o Created when an absence types is created.

o It adds the Absence name as the prefix for uniqueness.

To fetch the exact query that is used in a DBI, we can use the following query:

SELECT user_name

, definition_text

, item.data_type

, null_allowed_flag

, text

,'B' || sequence_no context_sequence

, context_name

FROM apps.ff_database_items item,

apps.ff_user_entities ent,

apps.ff_routes route,

ff_contexts fc,

ff_route_context_usages frcu

WHERE item.user_entity_id = ent.user_entity_id

AND route.route_id = ent.route_id

AND user_name = '&DBI_NAME'

AND route.route_id = frcu.route_id

AND fc.context_id = frcu.context_id

ORDER BY 6

The query returns the following:

USER_NAME	Name of the Database Item.
DEFINITION_TEXT	The Select Clause of the query.
DATA_TYPE	The data type of the DBI return Value.
NULL_ALLOWED_FLAG	Tells if the DBI can return Null; If this is Y, we should use Defaults.
TEXT	The ‘FROM’ clause and the ‘WHERE’ clause.
CONTEXT_SEQUENCE	The sequence that is used in the where clause.
CONTEXT_NAME	The corresponding Data that is expected in place of the sequence.

So, if we take an example and run the query for “PTO_ACCRUAL_PLAN_ID”

Definition Text will be: DISTINCT PAP.ACCRUAL_PLAN_ID

TEXT will be:

/* route for dates used in accrual plan calculations */

pay_element_entries_f pee,

pay_accrual_plans pap,

pay_element_links_f pel

where pap.accrual_plan_element_type_id = pel.element_type_id

and pel.element_link_id = pee.element_link_id

and pee.element_entry_id = &B1

and &B2 between pee.effective_start_date

and pee.effective_end_date

And the Contexts will be:

B1: ELEMENT_ENTRY_ID
B2: DATE_EARNED

So, the query will be:

SELECT < Definition Text >
FROM < TEXT >

As per our example, the query will look like this:

SELECT DISTINCT pap.accrual_plan_id

FROM /* route for dates used in accrual plan calculations */

pay_element_entries_f pee,

pay_accrual_plans pap,

pay_element_links_f pel

WHERE pap.accrual_plan_element_type_id = pel.element_type_id

AND pel.element_link_id = pee.element_link_id

AND pee.element_entry_id = &b1

AND &b2 BETWEEN pee.effective_start_date AND pee.effective_end_date

We will learn more about the tables in Technical essay Section.

Using Aliases

The database item names are big and hence are difficult to remember. It is very difficult to remember each and every data base item, and write the names accordingly, while writing a big fast formula. Here the concept of Alias is very useful. The name itself is self explanatory.

Let’s take an example:

ALIAS ASG_BARGAINING_UNIT_CODE as BARG_UNIT

In this above statement the DBI is aliased as BARG_UNIT. Hence forth in the Formula we can easily refer BARG_UNIT and the compiler will understand that the BARG_UNIT means the value returned by the Database Item.

Comments in Fast Formula

Comments are always preferred in coding, and Oracle Fast Formula is no exception. We can add as many comments and white lines in a formula as you want to increase the readability.

A Comment starts with /* and ends with */. Compiler ignores everything in between these two symbols. Comments never impact the performance of a Formula; hence as a standard practice, we should always add the Formula Name, Type, Input Values, Return Types, DBIs if any, flow of the code and also the versions with comments in order to make it more standard and readable. We should also add comments in between the codes to explain the purpose of the statements, in case the statement is complex. We should not put Comments in between Comments as the compiler does not understand the nesting and throws an error.

Writing a Formula in Oracle HRMS