Fast Formula
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:
If the constant is of type Numeric, then we should not use
comma in the numbers.
We should not use Exponential values as Constants.
The Text Constants should always be in single quotes.
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’
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.
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.
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
|
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.
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.
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.
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.
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.
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.
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.
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?
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.
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
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.
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.
|
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.
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
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 User Function
Functions
are the sub-programs that take some inputs and after performing a particular
Mathematical / logical calculation, return a value. The subprogram once
defined, can be called from and used in any Formula.
A function takes
Input Values, processes them and returns an output. The input values can
be fed by the Contexts used in the Formula it is being called from. But what
are contexts?
The
contexts are the mandatory values that are passed to the called formula
automatically by the system. For an example, if the formula being called is a
Payroll Formula, the payroll engine will pass some basic information to the
formula automatically, information like, the assignment_id being
evaluated,element_id being processed etc. As these values are seeded by
the system by default, we do not have to worry about fetching them at the run
time.
However,
when we plan to create a user function, that is going to be used in that
payroll formula, we can use the assignment_id and the element_id as
a context to the function, and the function will be able to retrieve that data
automatically, when called from the formula. If the contexts do not suffice, we
can also add Parameters to the function. The Parameters can be of one of these
types, “Input Only”, “Output Only” and “Both Input and Output”. The Parameters
have to be passed on to the Function, while being called, unlike contexts that
get passed automatically. So how do we plan, where to use parameter? We will
take another example to understand this.
Let’s plan
to write a function named “GET_LEAVE_DAYS”, which does the following:
Inputs:
·
ASSIGNMENT_ID
·
ABSENCE_ATTENDANCE_TYPE_ID
·
FROM_DATE
Output:
·
Number of Days
Action:
It takes
the FROM_DATE; it calculates the number of leaves taken in the given Absence
Type from that date till Today and returns the number of days.
In this example, we can take ASSIGNMENT_ID as the Context,
envisioning it to be one of the context values passed to the formula, based on
the formula type in which the function will be used. Then we can use the rest
two inputs as the parameters. So while calling the function from a Formula,
which has ASSIGNMENT_ID as one of the contexts, we will have to call it like
this:
LEAVE_DAYS = GET_LEAVE_DAYS (L_ABSENCE_ATTENDANCE_TYPE_ID,
L_DATE)
In this
case, LEAVE_DAYS is a local variable of type number, to receive the output
given by the function, where as L_ABSENCE_ATTENDANCE_TYPE_ID and L_DATE are the
two different variables that are initialized and passed on to the function.
So what did
we just discuss?
·
If it is a Context value, we do not have to
pass it onto the function, while it is being called, but all the other
parameters will have to be passed.
·
If there is only one input value, then we do not have
to pass a separate parameter for the Output type, because every function is
capable of taking one output by default. However if there are more than one
output value, we will have to make one for the default and all others have to
be marked as Output Parameters.
·
If we attach a Particular context to the
function, we can use the function only within a formula which has that
particular context is use. For an example, the above function cannot be used in
a Formula that does not take an assignment id. To use this, we might as well
move the ASSIGNMENT_ID to the parameters, and pass the value, than using it as
a context.
Function
codes are usually written in PL/SQL; where we write a PL/SQL Function to be
called by the Formula function. The PL/SQL Function takes all the Input and
Output parameters including the Contexts and return a value based on the Output
parameter.
Let’s now learn
how to define a user function. See Figure 5.3 – Functions.
- Responsibility: Super
HRMS Manager
- Navigation: Other
Definitions -> Formula Functions
(Figure 5.3 – Functions)
Name
|
Name of
the Function.
|
Data Type
|
The data
type of the default Output parameter. In simple words, Return type.
|
Class
|
This
defines the type of the Function. It can be of three types:
· User
Defined Function
· Formula
· External
function
If
we are creating new function then use: External Function. The rest two are
reserved for Oracle Seeded functions.
|
Alias
name
|
The alias
name of the function. This is option yet useful in case we have a long
function name
|
Description
|
Description,
optional, free text.
|
Definition
|
This is
where we define the PL/SQL function call. In the image we can see, it is
referring to the package name. Function name
|
Context
Usages
|
This
button allows us to add contexts in to the function.
|
Parameters
|
This
button allows us to add parameters on to the function.
|
Once the
Function is defined, the next task is to assign contexts if any. Click on the
Context Usages button, it should open a screen, where we can enter a sequence
number and choose a value from the context list. The Data type gets populated
automatically. See Figure 5.4 – Function Contexts.
(Figure 5.4
– Function Contexts)
Once the
Contexts are defined, the next task is to add parameters. Click on the
Parameters button, add a sequence number, and add a Parameter name and type.
See Figure 4.5 – Function Parameters. The class should be mentioned based on
the parameter type, whether it is an Input / Output or Both. We should then
check the Option flag, if the parameter is not a required one in the function.
We must mark it as continuing, if we wish to call the parameter more than once
in the function.
(Figure 5.5
– Function Parameters)
The next
task is to define a pl/SQL function that will have the business logic. Remember
the following things while defining a Pl/SQL function for a Formula function:
·
All the Contexts and the Parameters must be
used in the function definition, along with the proper data type and class.
·
The optional Parameters can be passed with a
null.
·
It is always better to include the function in
a package and call it from the formula Functions.
There are a
lot of seeded functions provided by Oracle E-Biz. Those functions are not
updatable. So before creating a new function, it is always advised to check if
there is one already there with the same functionality, provided by Oracle.
As the
language used in fast formulae is very simple, it is little tricky to implement
complex business logic in them. From complex we mean a large set of conditions
and a pile of code that would last about 10,000 lines. The difficulty is not
only limited to the implementation part of it, but also the maintenance of the
code. In cases like that, we should use a PLSQL stored program to manage the business
logic for us, and then we can call the PLSQL program from the FF to yield the
desired result.
Using a
PLSQL program also lets us use all the pluses from the Object Oriented
Programming in fast formulae. For example, we can exploit core reusability, Overloading
etc in PLSQL and finally use them in the formula.
The most
preferred way to embed a PLSQL code in a formula is through user functions. As
user functions support contexts and parameters, those are capable of connecting
the fast formula to the programming objects. Let’s see the steps to do so:
- Write
a PLSQL stored Procedure / function, that takes the required IN parameters
and returns the OUT parameter
- It is
advised to encapsulate the procedures / functions related to one
particular business functionality in a particular package. For an example,
all the procedures used for Skip rules can reside in one package, and all
that relate to Accrual calculation stays in another. We can also create a
common payroll package that holds all the code that relates to common
payroll functionality
- Next,
code the business logic inside the procedure / function and return a value
based on the logic
- Create
a user function and attach the PLSQL code to it. Remember to key in the
contexts and parameters whenever necessary
- Go to
the formula, initiate the parameters if any, and call the user function
from there, with the parameters
- Finally,
based on the return value from the PLSQL object, set the outputs and
return them
Compiling a Formula
Like all
other programming languages, fast formulae have their own compilers to
translate the complex language to a standard system language. When we write the
code in to the Write formula window and hit on “Save”, the compiler comes into
action. It scans the entire formula and looks for the compilation errors.
The Compilation errors are usually the syntax related issues, or context
related issues, where a function is being used without appropriate context
related to the formula. Once the Compilation errors are field and the code is
verified, it checks the Verified check box in the write formulas screen.
The
compiler takes care of the Compilation errors, but not the runtime errors.
There can be run time errors that appear on a formula during execution. To
solve those, we can debug the formula from the error text or simply add
messages to the formula text.
Here is a
list of Common run time formula errors and the possible reasons.
Error
|
Possible
Error Reasons
|
Invalid
Number
|
If a DBI
context expects a number and is passed with a non-numeric value.
|
Uninitialized
Variables
|
One or
more of the variables are not initialized properly.
|
Null Data
Found
|
This
error comes when a DBI has Null allowed flag as Y and is not defaulted.
|
Divide by
Zero
|
The
Famous mathematical error. There is a division with the second parameter
having a value of zero.
|
No Data
Found
|
Usually
when a DBI does not find a row in Database
|
Too Many
Rows
|
More than
one rows returned by the Database
|
We have
known how to write a Formula, right? Let’s talk about the Dos and Don’ts to
make sure the written formula runs efficiently. Here are few points:
·
Use the minimum possible Elements as possible,
as the Elements take a lot of time to be fetched and processed.
·
In case we have to use more than one element,
use the Input Values for just one element and use the DBI for all others. This
makes the Formula run faster.
·
However if we are using just one element,
Database Items run slower than the Input Values.
·
The number of statements (Excluding the
Comments) in a Formula increases the processing duration. So we should always
try to make the formula short.
So instead
of writing:
A = C * D
E = A /15
We should
write:
E = (C * D) / 15
· Usage
of unnecessary functions is a killer. We should avoid the format functions,
like TO_DATE, TO_CHAR etc. unless we really need them.
· Aliases
always help. Although it increases in readability, there is no impact on
Performance.
Let’s look
at one example of a Fast Formula. The first one is a simple Participation and
Rate Eligibility Formula, and the second one is a complex Payroll formula.
/*===============================================================================
FORMULA TYPE: Participation and Rate Eligibility
FORMULA DESCRIPTION: The Formula gets the Gender of the
Participant being evaluated by the function get_gender(),Then it compares
the Values, If It’s a Male then the Return Variable ‘ELIGIBLE’ is set to N else
Y. So if a Participant is a Male he is denies Eligibility, Else he is granted.
================================================================================
================================================================================*/
l_gender = get_gender()
if l_gender = 'M' then
( ELIGIBLE = 'N' )
else
( ELIGIBLE = 'Y')
return ELIGIBLE
/*==============================================================================*/
/*===============================================================================
FORMULA NAME: DOUBLE_DISMANTLE_HOUR_X_RATE
FORMULA TYPE: Payroll
DESCRIPTION: THIS FORMULA
CALCULATES EARNING AMOUNT, RATE, HOURS WORKED
HOURS
PAID ETC, AND STAGE ALL THE VALUES IN TEMP TABLE FOR
FURTHER CALCULATING ACCRUALS AND DEDUCTIONS
CREATED BY
DATE
COMMENT
**********************************************************************************/
/* ===== Alias Section Begin ====== */
ALIAS SCL_ASG_US_WORK_SCHEDULE AS Work_Schedule
ALIAS SCL_ASG_US_TIMECARD_REQUIRED AS Timecard_Required
/* ===== Alias Section End ====== */
/* ===== Defaults Section Begin =====
*/
DEFAULT FOR PAY_PROC_PERIOD_START_DATE IS '0001/01/01
00:00:00' (DATE)
DEFAULT FOR PAY_PROC_PERIOD_END_DATE IS '0001/01/02
00:00:00' (DATE)
DEFAULT FOR ASG_SALARY IS 0
DEFAULT FOR ASG_SALARY_BASIS IS 'NOT
ENTERED'
DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_REDUCE_REGULAR IS 'N'
DEFAULT FOR USER_ENTERED_TIME IS 'N'
DEFAULT FOR Timecard_Required IS 'N'
DEFAULT FOR LABOR_RECORDING_COUNT IS 0
DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_ASG_GRE_RUN IS 0
DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_ASG_GRE_YTD IS 0
DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_HOURS_ASG_GRE_RUN IS 0
DEFAULT FOR Work_Schedule IS 'NOT
ENTERED'
DEFAULT FOR ASG_HOURS IS 0
DEFAULT FOR Hours IS 0
DEFAULT FOR Factor
IS 1
DEFAULT FOR Rate IS 0
DEFAULT FOR Rate_Code IS 'NOT
ENTERED'
DEFAULT FOR ASG_FREQ IS 'NOT
ENTERED'
DEFAULT FOR CURRENT_ELEMENT_TYPE_ID IS 0
DEFAULT FOR DOUBLE_TIME_DISMANTLE_ACCRUED_ASG_ITD IS 0
DEFAULT FOR
MAXIMUM_AMOUNT IS 0
DEFAULT FOR
DOUBLE_TIME_DISMANTLE_ASG_GRE_ITD IS 0
DEFAULT FOR
AUTHORIZATION_END_DATE IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR
PRORATE_START IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR
PRORATE_END IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR ENTRY_DATE_EARNED IS '0001/01/01
00:00:00' (DATE)
DEFAULT FOR
REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN IS 0
DEFAULT FOR
REDUCE_REGULAR_HOURS_ASG_GRE_RUN IS 0
DEFAULT FOR
REDUCE_REGULAR_EARNINGS_ASG_GRE_TD_BD_RUN IS 0
DEFAULT FOR
REDUCE_REGULAR_HOURS_ASG_GRE_TD_BD_RUN IS 0
DEFAULT FOR NMA_Percentage IS ' '
DEFAULT FOR Project_number is ' '
DEFAULT FOR task_number is ' '
DEFAULT FOR union is ' '
DEFAULT FOR Job is ' '
DEFAULT FOR
Shift IS ' '
DEFAULT FOR
Rate IS 0
/* ===== Inputs Section Begin ===== */
INPUTS ARE project_number,
task_number,
union,
Job,
Shift,
NMA_percentage,
hours,
rate,
Factor
lv_amt = 0
lv_rate = 0
lv_hrs_wrkd = 0
lv_hrs_paid = 0
lv_nma_flag = ' '
mesg = ' '
lv_shift = 0
lv_nma_pct = 1
lv_flag = 0
lv_job_nm = ' '
lv_union_nm = ' '
IF job was defaulted then
lv_job_nm = ' '
ELSE
lv_job_nm = FUNC_PAY_ID_CONV('JOB',to_number(job))
IF union was defaulted then
lv_union_nm = ' '
ELSE
lv_union_nm = FUNC_PAY_ID_CONV('UNION',to_number(union))
IF shift was DEFAULTED THEN
(lv_shift = 1)
ELSE
IF (FUNC_PAY_ID_CONV('SHIFT',to_number(shift)) = '1st
Shift') THEN
(lv_shift = 1)
ELSE
IF (FUNC_PAY_ID_CONV('SHIFT',to_number(shift)) = '2nd
Shift') THEN
(lv_shift = 2)
ELSE
IF (FUNC_PAY_ID_CONV('SHIFT',to_number(shift)) = '3rd
Shift') THEN
(lv_shift = 3)
ELEMENT_TYPE_ID_PASSED = CURRENT_ELEMENT_TYPE_ID
/* Calculation starts */
/* Determine if the employee
is working under NMA or under International Agreement */
IF NMA_percentage WAS
DEFAULTED THEN
(lv_nma_flag = 'N'
lv_nma_pct = 1
)
ELSE
(lv_nma_flag = 'Y'
lv_nma_pct = to_number(FUNC_PAY_ID_CONV('NMA',to_number(NMA_percentage)))/100
)
/* Rate Calculation */
IF (lv_nma_pct = 1 AND
lv_shift = 1)
THEN
(lv_rate = rate)
ELSE
(
IF (lv_shift = 1)
THEN
(lv_rate = rate*lv_nma_pct)
ELSE
(
IF (lv_shift = 2)
THEN
(lv_rate = ((rate*lv_nma_pct) + FUNC_PAY_NMA_SHIFT2))
ELSE
(
IF (lv_shift = 3)
THEN
(lv_rate = ((rate*lv_nma_pct) + FUNC_PAY_NMA_SHIFT3))
)
)
)
/* Amount, hours worked and hours paid calculation */
IF factor WAS NOT DEFAULTED
THEN
(lv_amt = lv_rate *
hours * factor
lv_hrs_paid = hours * factor
)
ELSE
(lv_amt = lv_rate *
hours
lv_hrs_paid = hours
)
/* Calculate hours worked and hours paid */
lv_hrs_wrkd = hours
/* Storing the earning calculated info for
further accrual and deduction calculations */
lv_flag =FUNC_EARN_JRNL(lv_amt,lv_rate,lv_hrs_wrkd,lv_hrs_paid,lv_nma_flag,project_number,task_number,job,union,lv_nma_pct,lv_job_nm,lv_union_nm)
IF lv_flag = 0
THEN
(mesg = 'Successfully calculated and
inserted into stage table')
ELSE
(mesg = 'Error in inserting into stage
table')
/*===================== RETURN SECTION
===========================*/
RETURN
lv_amt,
lv_rate,
ELEMENT_TYPE_ID_PASSED,
lv_hrs_wrkd,
lv_hrs_paid,
lv_nma_flag,
hours,
project_number,
task_number,
union,
Job,
NMA_percentage,
mesg,
rate,
factor
/* ===== Returns Section End ===== */
Technical Aspect
Here is a
list of all tables that store the Fast Formulas data.
Note:
- In the table below, if the
Date tracked column is marked as Yes, assume the Primary key to be
Composite. The given Primary will bind with the two date tracked columns
to make the Composite Primary keys
- Some
of the values in the column Table could be a view / synonym.
Table
Name
|
Date
Tracked?
|
Primary
Key
|
Description
|
FF_FORMULAS_F
|
Yes
|
FORMULA_ID
|
Stores
the details of the Formulae, along with the Business group, formula type, and
the text.
|
FF_FUNCTIONS
|
No
|
FUNCTION_ID
|
Stores
the details of the Formula Functions.
|
FF_DATABASE_ITEMS
|
No
|
USER_NAME
|
Stores
the details of the DBIs along with the Definition Text and user
Entity.
|
FF_USER_ENTITIES
|
No
|
USER_ENTITY_ID
|
Stores
the Entity Related Information with ROUTE_ID
|
FF_ROUTES
|
No
|
ROUTE_ID
|
This one
stores the actual query of the DBI along with other details.
|
FF_CONTEXTS
|
No
|
CONTEXT_ID
|
Stores
the different contexts used in the DBI
|
FF_ROUTE_CONTEXT_USAGES
|
No
|
Composite
Primary Key
|
This
links the FF_ROUTES and the FF_CONTEXTS tables.
|
Summary
In this chapter we discussed about the need and usage of
fats formulas. We started with the basics, like variables, data types and
expressions. Then we moved on to the database items and global variables. We
discussed about the flow of a formula, the conditions, comments, Input Values
and the different keywords. Then we discussed about the user functions, their
definition and how they are helpful in adopting PLSQL programming into
formulae. We discussed the compilations, tips on the efficiency of the formula,
the underlying table structures and finally we saw two different examples of
fast formulas.
Comments
Post a Comment