Oracle FastFormula provides an easy to use tool for professional users. Using simple commands and syntax, users can write their own validation rules or payroll calculations.
Until R11 the execution engine for calling formulas and dealing with the outputs has been hidden within the Oracle HR and Payroll products. The original engine for calling PL/SQL was written in Pro*C. It is complex and can be called only from user exits or directly from another 'C' interface.
Now, an execution engine or interface that lets you call formulas directly from Forms, Reports or other PL/SQL packages. This interface means that you can call existing validation or payroll formulas and include them in online or batch processes. It also means that you can define and call your own formulas for other types of validation and calculation. With FastFormula you automatically have access to the database items (DBIs) and functions of Oracle HRMS and you automatically have calculations and business rules that are datetracked.
The basic concepts of FastFormula remain the same as before:
Inputs -> Process -> Outputs
As you now have complete freedom to decide the inputs you provide and what happens to the outputs produced by a formula you must write the calling code to handle both inputs and outputs.
For optimal performance when calling FastFormula from PLSQL, generate the Formula Wrapper after compiling the formula. You can execute a formula even if you did not compile it before you generated the Formula Wrapper. The Bulk Compile Formulas process automatically generates the Formula Wrapper.
Generate the Formula Wrapper only when necessary. The Formula Wrapper generates a PLSQL package body, and the generation process may cause runtime errors in FastFormula calls that occur at the same time. You do not need to generate the Formula Wrapper when you test formulas.
This essay provides an overview and technical details to show you how to call FastFormula from PL/SQL. You should be familiar with PL/SQL coding techniques and with Oracle FastFormula but you will not need to understand the internal working of the execution engine.
There are two interfaces to the execution engine for FastFormula.
Server-side
Use this interface for any formulas to be executed by batch processes or on the server. See: Server Side Interface
Client-side
Use this interface only when a direct call is required from forms and reports to execute a formula immediately. You could also write a custom 'wrapper' package to call the server engine from the client. See: Client Side Call Interface
Note: Some Oracle tools currently use PL/SQL V1.x only. This version does not support the table of records data structure needed by the server interface. The client-side version was written to get around this current limitation.
The execution engine files are stored in $FF_TOP/admin/sql
ffexec.pkh and ffexec.pkb
Server side execution engine package header and body files.
ffcxeng.pkh and ffcxeng.pkb
Client side versions of execution engine package header and body files.
Note: There is a special interface in the ff_client_engine module that is designed specifically for the forms client. This interface avoids the overhead of a large number of network calls using a fixed number of parameters. See: Special Forms Call Interface
All formulas in Oracle HRMS products are datetracked, enabling you to use DateTrack to maintain a history of changes to your validation rules or calculations.
In the predefined interfaces to the execution engine the system automatically manages the setting or changing of the effective date. When you execute your own formulas you must also manage the setting of the effective date for the session. This means that before calling any of the execution engine interfaces you may need to insert a row into the FND_SESSIONS table. This is required if there is no row in FND_SESSIONS for the current SQL*PLUS session_id or the formula or formulas to be executed access database items that reference datetracked tables.
Important: Always check the effective date for the formula to be executed. This date affects the values of the database items and any functions that you include in the formula.
This section describes the interface to the server execution engine and how to call the module from other PL/SQL.
This version of the interface is preferred. It combines maximum flexibility with relatively low network demands. However, it can only be used with PL/SQL V2.3 and above as it requires support for the table of records data structure.
There are two important user data structures when you use the server side interface. These are the inputs table and the outputs table:
| Name | Description |
|---|---|
| NAME | The input name, such as RATE, or ASSIGNMENT_ID |
| DATATYPE | Can be DATE, NUMBER, or TEXT |
| CLASS | The type of input : CONTEXT or INPUT This field is not required, as it is not necessary to know if an input is a context or a normal input value to call the formula correctly. |
| VALUE | The actual value to pass to the formula as a Context or an Input. This field is a type of varchar2(240). This means that for NUMBER and DATE datatypes the value passed in has to be in the appropriate format. See the example code for how this works. |
| Name | Description |
|---|---|
| NAME | The output name, such as RESULT1, or MESSAGE |
| DATATYPE | Can be DATE, NUMBER, or TEXT |
| VALUE | The actual value returned from the formula |
Note: The names of all inputs and outputs must be in upper case and the same name can appear in both the inputs and the outputs tables, for example where an input value is also a return value from the formula. However, a CONTEXT can only appear in the inputs table.
Both inputs and outputs tables are initialized by a call to the ff_exec.init_formula procedure and then contain details of all the inputs, including contexts that are needed to execute the formula and all the outputs that will be returned.
You are responsible for holding these tables between the initialization and execution calls.
Important: Although the index values for these tables are positive in value, the caller should not assume that they start at 1. Always use the "first" and "last" table attributes when accessing and looping through these tables. See also: Examples.
The following procedure calls are available. They are described below with some detail on the parameters that can be passed to them.
Note: Refer to the appropriate package header for information on the class of parameter (in, out or in/out).
This call initializes the execution engine for a specific formula. That is, it declares to the engine that a formula is about to be run. It must be called before a formula is executed, but that formula can then be executed as many times as desired without having to call the initialization procedure again. This will be understood from the examples further on.
| Parameter Name | Data Type | Comments |
|---|---|---|
| p_formula_id | number | Formula_id to execute |
| p_effective_date | date | Effective date to execute |
| p_inputs | ff_exec.inputs_t | Input variable information |
| p_outputs | ff_exec.outputs_t | Output variable information |
This call actually executes the formula, taking inputs as specified and returning any results from the formula. The init_formula procedure must have been called before this is used (see examples).
| Parameter Name | Data Type | Comments |
|---|---|---|
| p_inputs | ff_exec.inputs_t | Inputs to the formula |
| p_outputs | ff_exec.outputs_t | Outputs from the formula |
| p_use_dbi_cache | boolean | If TRUE, the database item cache will be active during execution, else will not. Defaults to TRUE |
The p_inputs and p_outputs parameters could be NULL if the formula does not have any inputs and/or outputs (although the latter is rather unlikely).
The p_use_dbi_cache would only be set to FALSE under unusual circumstances requiring the disabling of the cacheing of database item values. This might be required if the engine is called from code that would invalidate the values for fetched database items.
For instance, if the database item ASG_STATUS was accessed from within a formula used in business rule validation used in turn to alter the Assignment's status, we might want to disable the Database Item cache in case we attempted to read that database item in a subsequent formula.
The following examples assume we are going to execute the following formula. Note that the DATABASE_ITEM requires an ASSIGNMENT_ID context.
The formula itself does not represent anything meaningful, it is for illustration only.
inputs are input1, input2 (date), input3 (text)
dbi = DATABASE_ITEM
ret1 = input1 * 2
return ret1, input2, input3
The following anonymous block of PL/SQL could be used to execute the formula. In this case, it is called a number of times, to show how we can execute many times having initialized the formula once.
declare
l_input1 number;
l_input2 date;
l_input3 varchar2(80);
l_assignment_id number;
l_formula_id number;
l_effective_date date;
l_inputs ff_exec.inputs_t;
l_outputs ff_exec.outputs_t;
l_loop_cnt number;
l_in_cnt number;
l_out_cnt number;
begin
-- Set up some the values we will need to exec formula.
l_formula_id := 100;
l_effective_date := to_date('06-05-1997', 'DD-MM-YYYY');
l_input1 := 1000.1;
l_input2 := to_date('01-01-1990', 'dd-mm-yyyy');
l_input3 := 'INPUT TEXT';
l_assignment_id := 400;
-- Insert FND_SESSIONS row.
insert into fnd_sessions (
session_id,
effective_date)
values (userenv(`sessionid'),
l_effective_date);
-- Initialise the formula.
ff_exec.init_formula(l_formula_id, l_effective_date, l_inputs,
l_outputs);
-- We are now in a position to execute the formula.
-- Notice that we are illustrating here that the formula can
-- be executed a number of times, in this case setting a new
-- input value for input1 each time.
for l_loop_cnt in 1..10 loop
-- The input and output table have been initialized. We now have
-- to set up the values for the inputs required. This includes
-- those for the 'inputs are' statement and any contexts.
for l_in_cnt in l_inputs.first..l_inputs.last loop
if(l_inputs(l_in_cnt).name = 'INPUT1') then
-- Deal with input1 value.
l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(l_input1);
elsif(l_inputs(l_in_cnt).name = 'INPUT2') then
-- Deal with input2 value.
l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(l_input2);
elsif(l_inputs(l_in_cnt).name = 'INPUT3') then
-- Deal with input3 value.
l_inputs(l_in_cnt).value := l_input3;
-- no conversion required.
elsif(l_inputs(l_in_cnt).name = 'ASSIGNMENT_ID') then
-- Deal with the ASSIGNMENT_ID context value.
l_inputs(l_in_cnt).value := l_assignment_id;
end if;
end loop;
ff_exec.run_formula(l_inputs, l_outputs);
-- Now we have executed the formula. We are able
-- to display the results.
for l_out_cnt in l_outputs.first..l_outputs.last loop
hr_utility.trace('output name : ' || l_outputs(l_out_cnt).name);
hr_utility.trace('output datatype : ' || l_outputs(l_out_cnt).datatype);
hr_utility.trace('output value : ' || l_outputs(l_out_cnt).value);
end loop;
end loop;
-- We can now continue to call as many formulas as we like,
-- always remembering to begin with a ff_exec.init_formula call.
-- Note: There is no procedure to be called to
-- shut down the execution engine.
end;
/
As noted earlier, if you are attempting to call the execution engine from a client that is not running the appropriate version of PL/SQL, it will be necessary to create a package that 'covers' calls to the engine or consider calling the client engine, specified below.
This section attempts to describe in detail the interface to the client execution engine from a user perspective, and how to call the module from other PL/SQL.
Note: These client side calls are designed to avoid any use of overloading, which causes problems when procedures are called from forms.
This interface can be used when the version of PL/SQL on the client is prior to V2.3 (does not support tables of records). It is probably the easiest interface to use. However, it is not recommended where high performance is required, due to the greater number of network round-trips. In these cases, consider using the special forms interface.
There are no user visible data structures in the client side call.
The following procedure calls are available. They are described below with some detail on the parameters that can be passed to them.
Note: Refer to the appropriate package header for information on the class of parameter (in, out, or in/out).
This call initializes the execution engine for a specific formula. That is, it declares to the engine that a formula is about to be run. It must be called before a formula is executed, but that formula can then be executed as many times as desired without having to call the initialization procedure again. This will be understood from the examples further on.
| Parameter Name | Data Type | Comments |
|---|---|---|
| p_formula_id | number | Formula_id to execute |
| p_effective_date | date | Effective execution date |
This call sets the value of an input to a formula. To cope with the different datatypes that FastFormula can handle, the values have to be converted to the appropriate character strings.
| Parameter Name | Data Type | Comments |
|---|---|---|
| p_input_name | varchar2 | Name of input to set |
| p_value | varchar2 | Input value to set |
This call actually executes the formula, taking inputs as specified and returning any results from the formula. The init_formula procedure must have been called before this is used (see examples).
There are no parameters to run_formula.
This call gets the output values returned from a formula. To cope with the different datatypes that FastFormula can handle, the output has to be converted as appropriate.
| Parameter Name | Data Type | Comments |
|---|---|---|
| p_input_name | varchar2 | Name of input to set |
| p_return_value | varchar2 | Value of varchar2 output |
The following examples rely on the same formula used above.
inputs are input1, input2 (date), input3 (text)
dbi = DATABASE_ITEM
ret1 = input1 * 2
return ret1, input2, input3
The following anonymous block of PL/SQL can be used to run the formula.
declare
l_input1 number;
l_input2 date;
l_input3 varchar2(80);
l_output1 number;
l_output2 varchar2(12);
l_output3 varchar2(80);
l_assignment_id number;
l_formula_id number;
l_effective_date date;
l_loop_cnt number;
begin
-- Set up the values we need to execute the formula.
l_formula_id := 100;
l_effective_date := to_date('06-05-1997', 'DD-MM-YYYY');
l_input1 := 1000.1;
l_input2 := to_date('01-01-1990', 'dd-mm-yyyy');
l_input3 := 'INPUT TEXT';
l_assignment_id := 400;
-- Insert FND_SESSIONS row.
insert into fnd_sessions (
session_id,
effective_date)
values (userenv(`sessionid'),
l_effective_date);
-- Initialize the formula. ff_client_engine.init_formula(l_formula_id,l_effective_date);
-- We are not in a position to execute the formula.
-- Notice that we are illustrating here that the formula can
-- be executed a number of times, in this case setting a new
-- input value for input1 each time.
for l_loop_cnt in 1..10 loop
-- The input and output tables have been initialized.
-- We now have to set up the values for the inputs required.
-- This includes those for the 'inputs are' statement
-- and any contexts.
-- Note how the user has to know the number of inputs the
-- formula has.
ff_client_engine.set_input('INPUT1', fnd_number.number_to_canonical(l_input1));
ff_client_engine.set_input('INPUT2', fnd_date.date_to_canonical(l_input2));
ff_client_engine.set_input('INPUT3', l_input3);
ff_client_engine.set_input('INPUT3', l_input3);
ff_client_engine.set_input('ASSIGNMENT_ID', l_assignment_id);
ff_client_engine.run_formula;
-- Now we have executed the formula. Get the results.
ff_client_engine.get_output('RET1', l_output1);
ff_client_engine.get_output('INPUT2', l_output2);
ff_client_engine.get_output('INPUT3', l_output3);
-- OK. Finally, display the results.
hr_utility.trace('RET1 value : ' || output1);
hr_utility.trace('INPUT2 value : ' || l_output2);
hr_utility.trace('INPUT3 value : ' || output3)
end loop;
-- We can now continue to call as many formulas as we like,
-- always remembering to begin with a
-- ff_client.init_formula call.
-- Note: There is no procedure to be called to
-- shut down the execution engine.
end;
/
This section attempts to describe in detail the interface to the special forms client execution engine interface from a user perspective, and how to call the module from forms.
This interface is recommended for use when you want to execute a formula directly from a form or report client that does not support PL/SQL V2.3 or above (that is, does not allow PL/SQL tables of records).
There are no user visible data structures in the client side call.
The following procedure calls are available. They are described below with some detail on the parameters that can be passed to them.
Note: Refer to the appropriate package header for information on the class of parameter (in, out, or in/out).
This call initializes the execution engine for a specific formula, then runs the formula taking the input and context arguments specified. Finally it returns the appropriate results to the user via a further set of arguments. This form of call therefore requires only one network round-trip. The disadvantage is that it is limited to the number of inputs and returns that it can cope with (this is based round the PL/SQL V1.0 limitations).
Note: Use this procedure call when the formula_id for the formula to execute is known. Another procedure call (run_name_formula - see below) is used where only the name is known.
| Parameter Name | Data Type | Comments |
|---|---|---|
| p_formula_id | number | Formula_id to execute |
| p_effective_date | date | Effective execution date |
| p_input_name01 . . . 10 | varchar2 | input name 01 . . . 10 |
| p_input_value01 . . . 10 | varchar2 | input value 01 . . . 10 |
| p_context_name01 . . . 14 | varchar2 | context name 01 . . . 14 |
| p_context_value01 . . . 14 | varchar2 | context value 01 . . . 14 |
| p_return_name01 . . . 10 | varchar2 | return name 01 . . . 10 |
| p_return_value01 . . . 10 | varchar2 | return value 01 . . . 10 |
This call initializes the execution engine for a specific formula, then runs the formula taking the input and context arguments specified. Finally it returns the appropriate results to the user via a further set of arguments. This form of call therefore requires only one network round-trip. The disadvantage is that it is limited to the number of inputs and returns that it can cope with (this is based round the PL/SQL V1.0 limitations).
Note: Use this procedure call when you know the name and type for the formula to execute. Use the run_id_formula call (see above) when only the id is known.
| Parameter Name | Data Type | Comments |
|---|---|---|
| p_formula_type_name | number | Formula type |
| p_formula_name | varchar2 | Name of formula to execute |
| p_effective_date | date | Effective execution date |
| p_input_name01 . . . 10 | varchar2 | input name 01 . . . 10 |
| p_input_value01 . . . 10 | varchar2 | input value 01 . . . 10 |
| p_context_name01 . . . 14 | varchar2 | context name 01 . . . 14 |
| p_context_value01 . . . 14 | varchar2 | context value 01 . . . 14 |
| p_return_name01 . . . 10 | varchar2 | return name 01 . . . 10 |
| p_return_value01 . . . 10 | varchar2 | return value 01 . . . 10 |
Sometimes things may go wrong when attempting to execute formulas via the PL/SQL engine. In many cases, the error messages raised will make it obvious where the problem is. However, there are cases where some more information is needed.
You can set the execution engine to output logging information. This section explains how to activate and use the logging options
Note: The logging output makes use of the standard Oracle HR trace feature.
You set logging options for the execution engine by calling the ff_utils.set_debug procedure. This procedure has the definition:
procedure set_debug ( p_debug_level in binary_integer );
Since the numeric values for the options are power of two values, each represented by a constant, the appropriate values are added together.
For instance, to set the routing and dbi cache debug options (see below) use the following call (from SQLPLUS).
SQL> execute ff_utils.set_debug(9)
The value 9 is (1 + 8).
If preferred, you can use the constants that have been defined. For example:
SQL> execute ff_utils.set_debug(ff_utils.ROUTING +
ff_exec.DBI_CACHE_DBG)
If the execution engine is being called from a form, you can enable logging options using the FF_DEBUG profile option.
You use a series of characters to indicate which logging options you want to set. You must specify X, as this enables user exit logging. For example, if you set the profile option to XDR, you initiate the database item cache and routing information.
The full list of characters you can specify is as follows (see Summary of Available Information for a description of each logging option).
| Character | Equivalent to . . . |
|---|---|
| R | ff_utils.ROUTING |
| F | ff_exec.FF_DBG |
| C | ff_exec.FF_CACHE_DBG |
| D | ff_exec.DBI_CACHE_DBG |
| M | ff_exec.MRU_DBG |
| I | ff_exec.IO_TABLE_DBG |
What follows is a brief discussion of each logging option, with its symbolic and equivalent binary value used to set it.
Note: To interpret the output of many of these options, you require some familiarity with the workings of the execution engine code.
Routing. Outputs information about the functions and procedures that are accessed during an execution engine run. An example of the visible output would be:
In : run_formula
Out : run_formula
This debug level, although defined in the header, is not currently used.
Formula Cache Debug. Displays information about the currently executing formula, including its data item usage rows.
Database Item Cache Debug. Displays information about those items held in the database item cache. These items are not constrained to a particular formula.
Most Recently Used Formula chain. Displays information about those formulas currently held in the MRU chain. The information displayed includes the table index, formula_id, sticky flag and formula name.
Input and Output Table Debug. Shows information about items currently held in the input and output tables. This includes both information set by the user and the formula engine.
Only general advice can be given, since there is no way of predicting what the problem may be. Some hints are:
ROUTING is useful only for those who understand the code. Tracing the procedures may illuminate a problem - perhaps an error is being raised and it is not obvious where from.
FF_CACHE_DBG will confirm what basic formula information is held by the execution engine. This is useful to see if it looks as you expect.
IO_TABLE_DBG will confirm what is really being passed to and from a formula.