Writing a User Function
Contexts and Parameters
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_DATEOutput:
· Number of Days
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 Code
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.
Defining User Functions
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:
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.
Using PLSQL in a Formula
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
No comments:
Post a Comment