Formula Engine

This appendix covers the following topics:

• About the Formula Engine
• Terminology
• Examples of Formulas
• Commission Formula Example
• Net Investment Value Formula Example
• General Setup Steps for a Formula with Operand Functions
• Write the Source PL/SQL Function To Use in a Formula

About the Formula Engine

The Formula Engine is a tool that lets you define custom formulas and execute them.

Many different parts of Oracle Lease Management use formulas. Formulas are always relevant to either a particular contract or an individual line on a contract.

Examples of formulas that Oracle Lease Management uses include asset residual, contract original equipment cost, and contract capitalized fees.

Note: All formulas return values in the ledger currency associated with the ledger.

Simplified Formula Engine Architecture

Terminology

The main parts of the Formula Engine are:

• PL/SQL: PL/SQL is Oracle's Procedural Language extension to SQL. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, and information hiding. PL/SQL is commonly used to write data centric programs to manipulate data in an Oracle database.

• PARAMETER: A seeded variable whose value is calculated and passed to a function. Used by functions and context groups.

• FORMULA: A combination of arithmetic symbols and operands that evaluates to a numeric value.

  The symbols you can use in a formula are:  +  -  *  /  (  )

• OPERAND: An object that can take part in arithmetic operations. An operand derives its value either from another formula, an expression or a function.

• FUNCTION: The registration of a source PL/SQL function, which returns a numeric value. The source PL/SQL function must have been previously created in the database. A function can accept parameters.

• FUNCTION PARAMETER: An object that passes a value to the function.

• CONTEXT: A grouping mechanism that allows for the efficient evaluation and passing of parameters to functions in formulas. Every formula executes within a context.

• CONTEXT PARAMETER: A variable of a context whose value is calculated and passed to a function when a formula is executed that contains the function.

Examples of Formulas

The following sections show examples of formulas:

• Commission Formula Example

• Net Investment Value Formula Example

• Write the Source PL/SQL Function To Use in a Formula

Commission Formula Example

The main topics in this section are:

• Functions and Parameters for the Commission Formula

• Context and Context Parameters

• Context Parameters for the Commission Formula

• How a Formula Executes

You can configure Context Groups and Function parameters as described in the next section when seeded parameters are available. Currently no seeded parameters are available.

To understand how the Formula Engine works, consider a sample formula to calculate sales commissions.

Note: This example is not oriented to specific lease or loan transactions, but illustrates some of the features of the Formula Engine, especially the concepts of context and context parameters.

The Commission formula is:

Commission := (Delta Amount) * 0.05 + Group Bonus

This formula calculates a sales agent's commission as 5% of the difference between bookings and cancellations plus some group bonus.

The Commission formula consists of three operands:

• Delta Amount, which is another formula:

  Delta Amount: = Ordered Amount - Canceled Amount
  
  

  This formula consists of two operands: Ordered Amount and Canceled Amount. Both of these operands are functions.

• The constant value 0.05.

• Group Bonus, which is a function.

The essential concepts that this illustrates are:

• Formulas consist of operands + symbols.

• Operands derive their values from formulas, constants or functions.

Functions and Parameters for the Commission Formula

Functions accept input parameters, which can either be constants or variables.

There can be any number of function parameters.

Assume that the variable parameters for the Commission example functions are:

• EMPLOYEE_ID

• GROUP_ID

• START_DATE

• END_DATE

The Commission example functions and the parameters that they use appear in the following table.

Commission Example Functions and Parameters

Functions	Parameters
Ordered Amount	EMPLOYEE_ID
Ordered Amount	START_DATE
Ordered Amount	END_DATE
Canceled Amount	EMPLOYEE_ID
Canceled Amount	START_DATE
Canceled Amount	END_DATE
Group Bonus	START_DATE
Group Bonus	END_DATE
Group Bonus	GROUP_ID

The essential concepts that this illustrates are:

• Functions can accept parameters.

• The same parameters can be used in many functions.

Context and Context Parameters

A context is a basically a grouping mechanism, whose main task is to provide an efficient way of passing parameters to functions.

Each formula is defined as belonging to a context.

If the formula calls any functions with parameters, how does the function receive values for those parameters? The formula execution passes values to the function parameters via context parameters.

Context parameters form a pool of all the parameters that are needed for all functions to be executed by all the formulas belonging to that context. The name of each context parameter must be the same as the name of the corresponding function parameter. You can use a validation procedure to verify that the formula parameters and context parameters match.

Note: All contexts and formulas for lease and loan transactions are associated with either a contract or a line in the contract. If the source PL/SQL functions contain the parameters P_CONTRACT_ID and P_LINE_ID, you do not need to have context parameters corresponding to these two parameters.

For each context parameter, there must be a special internal PL/SQL function, GET_<par>_VALUE (where <par> is the actual parameter name). At formula execution time, the internal PL/SQL function calculates a value for the context parameter and passes it to the formula function.

The advantage of this mechanism is that, when a formula is executed, each context parameter is evaluated once and is then passed to possibly many functions being executed by the formula.

Context Parameters for the Commission Formula

The context parameters for the Commission Formula are the parameters needed for all of the functions—Ordered Amount, Canceled Amount, and Group Bonus—namely:

• EMPLOYEE_ID

• GROUP_ID

• START_DATE

• END_DATE

As you can see from the table Commission Example Functions and Parameters, three of the parameters are used more than once during the execution of the Commission Formula. However, because the formula executes within a context, and each of the parameters is also a context parameter, the parameters are only evaluated once.

How a Formula Executes

The following example shows the sequence of how a formula executes.

1. If the formula contains a function (for clarity, this also refers to the formula function), the formula engine looks at each parameter of the function.

2. For each function parameter, the formula engine looks for the corresponding CONTEXT PARAMETER.

3. For each context parameter, the formula engine executes the internal PL/SQL GET_<par>_VALUE function and passes the value returned from this internal function to the formula function parameter.

4. This continues until the formula produces a result.

Net Investment Value Formula Example

The Net Investment Value formula is:

Net Investment Value := Receivables + Residuals - Unearned Income

Each of the three operands—Receivables, Residuals and Unearned Income—derives its value from a similarly-named function.

FUNCTION GET_LEASE_RECEIVABLE

The following example shows the source function for the Receivables operand.

( p_contract_id IN NUMBER, p_line_id IN NUMBER )

RETURN NUMBER

IS

  v_lease_rec NUMBER;

  CURSOR get_lease_rec IS

  SELECT NVL(SUM(c.amount),0)

  FROM   okl_streams_v  a,  okl_strm_type_v  b,  okl_strm_elements_v  c

  WHERE  c.stm_id = a.id  AND b.id = a.sty_id 

        AND  b.name = 'Rent' AND c.stream_element_date >= SYSDATE 

        AND a.khr_id = p_contract_id;

BEGIN

 OPEN  get_lease_rec;

    FETCH get_lease_rec INTO v_lease_rec;

    CLOSE get_lease_rec;

 RETURN v_lease_rec;

END GET_LEASE_RECEIVABLE;

General Setup Steps for a Formula with Operand Functions

The general steps for setting up a formula with at least one operand function, which contains parameters in addition to P_CONTRACT_ID and P_LINE_ID, are:

1. Write the Source PL/SQL Function To Use in a Formula.

2. Register the Source PL/SQL Function.

3. Register the Function Parameters (register them in Oracle Lease Management).

4. Create the Context (first-time only for a particular context).

5. Create the Context Parameters.

6. Write the Internal Functions for the Context Parameters.

7. Create the (Formula Function type) Operand.

8. Create the Formula.

9. Validate the Formula. (strongly recommended)

You can follow the sequence exactly as shown in the preceding list.

Alternatively, you can first perform steps 4 through 6, then steps 1 to 3, then step 7. Step 8 must be the last mandatory step that you perform. Step 9—formula validation— is optional, but it is strongly recommended to perform this step.

Important: If your source function contains only the standard contract and line id parameters, you do not need to perform all the steps.

Specifically, in this simpler case, you can omit steps 3, 5, and 6, that is, the steps which relate to function parameters and context parameters.

The example formula in the following pages uses two parameters PMR1 and PARAMETER2, both of which are numeric, in addition to the standard contract and line id parameters.

Write the Source PL/SQL Function To Use in a Formula

The topics in this section include:

• Code Example of a Sample Source

• Register the Source PL/SQL Function

• Register the Function Parameters

• Create the Context

• Create the Context Parameters

• Write the Internal Functions for the Context Parameters

• Create the (Formula Function type) Operand

• Create the Formula

• Create or Edit Operand Labels

• Validate the Formula

Code Example of a Sample Source

Here is the coding for a sample source PL/SQL function SFNC1, which has both the standard lease-oriented parameters P_CONTRACT_ID and P_LINE_ID as input parameters, as well as the special parameters PMR1 and PARAMETER2.

FUNCTION SFNC1(

P_CONTRACT_ID IN NUMBER,

P_LINE_IDIN NUMBER,

PMR1 IN NUMBER,

PARAMETER2IN NUMBER

) return number

IS

v_pre_tax_yield  number;

BEGIN

select nvl(pre_tax_yield,0)

into v_pre_tax_yield

from  okl_k_headers_v

where id=p_contract_id ;

RETURN (20 *(PMR1 + PARAMETER2) + v_pre_tax_yield) ;

END ;

Register the Source PL/SQL Function

Navigation

Setup > Formulas > Functions

Steps

Perform the following steps:

1. Click Create.

2. Enter the Name for the function, and optionally, the Description.

3. Enter the Effective From date, and optionally the Effective To date.

4. Select the Source Function you are registering.

5. Click Create.

Register the Function Parameters

After the function is created, if it contains parameters other than the standard contract and line id parameters, then you must register the non-standard parameters with Oracle Lease Management.

To do this, you must:

1. Search for the function.

2. Select the function.

3. Choose to create or edit function parameters.

You must perform all of the following steps for each non-standard function parameter.

Prerequisites

You have created the function and it contains parameters other than the standard contract and line id parameters.

Navigation

Setup > Formulas > Functions

Steps

Perform the following steps:

1. Search for the function.

2. In the Results panel, click the function name hypertext link.

3. Click the Function Parameters button.

4. In the Function Parameters area, enter the Sequence of the function parameter.

5. Enter the parameter name.

6. If the function parameter has a static value in the source function, enter this value in the Static Value field.

7. Click Create.

Create the Context

Each formula executes within a context. A context is basically a container for formulas that allows for the efficient passing of parameter values to the functions of a formula.

If the formula that you want to create does not have a context to be associated with, you must create the context.

Navigation

Setup > Formulas > Contexts

Steps

Perform the following steps:

1. Click Create.

2. Enter the context Name, and optionally the Description.

3. Click Create.

Create the Context Parameters

For each non-standard function parameter that you register with Oracle Lease Management, you must create a context parameter with exactly the same name as the function parameter. For more information on registering non-standard function parameters, see Create the Context.

To do this, you must:

1. Search for the context,

2. Select the context,

3. Choose to create or edit context parameters.

You must perform all of the following steps for each context parameter.

Navigation

Setup > Formulas > Contexts

Steps

Perform the following steps:

1. Search for the context.

2. In the Results panel, click the context name hypertext link.

3. Click the Context Parameters button.

4. In the Context Parameters area, enter the Parameter name.

5. Click Create.

Write the Internal Functions for the Context Parameters

Now we need to define the internal PL/SQL function that returns the context parameter's run-time value.

Note: If function requires any parameters other than contract_id and line_id, then you must create a context with the required additional parameters from the seeded parameters.

The function must have the name GET_[PARAMETER]_VALUE, where [PARAMETER] is the parameter name.

The function must be in the package OKL_FORMULAFUNCTION_PVT.

The internal functions must have p_contract_id and p_line_id as input parameters.

The following two code samples show the internal functions related to the parameters PMR1 and PARAMETER2, which in both examples, returns the number 1:

FUNCTION GET_PMR1_VALUE

( p_contract_id IN number,  p_line_id     IN number )

return number

 IS

 BEGIN

    RETURN 1;

 END GET_PMR1_VALUE;

FUNCTION GET_PARAMETER2_VALUE

( p_contract_id  IN number,  p_line_id  IN number )

return number

 IS

 BEGIN

    RETURN 1;

 END GET_PARAMETER2_VALUE; 

Create the (Formula Function type) Operand

Here you define an operand OKLOPRND of type Formula Function, which uses the function OKLFUNC registered in step 2 of Register the Function Parameters.

Navigation

Setup > Formulas > Operands

Steps

Perform the following steps:

1. Click Create.

2. Enter the Name for the operand, and optionally, the Description.

3. Enter the Effective From date, and optionally, the Effective To date.

4. In the Operand Type field, select Formula Function.

5. In the Function Name field, select the name of the function you registered.

6. Click Create.

Create the Formula

Each formula must be created within a context. As you create the formula, you must specify the context name for the formula.

You must enter the formula string, which is an arithmetic expression consisting of labels of operands and the symbols +  -  *  /  (  ).

Once you have created an operand it is available to be used in a formula string. However you do not enter the operand name itself into the formula string, instead you must create a label for the operand, and use the label in the formula string. Basically, an operand label is a shortcut or alias for an operand.

The procedure for creating the formula consists of a two-stage process:

• Create the formula, including the formula string containing operand labels and symbols. You might or might not have created the operand labels at this point.

• If the operand labels have not been created, create the operand labels.

The following description of creating a formula assumes that operand labels have not been created.

Navigation

Setup > Formulas > Formulas

Steps

Perform the following steps:

1. Click Create.

2. Enter the formula Name, and optionally, the Description.

3. Enter the Effective To date, and optionally, the Effective From date.

4. Select the Context for this formula.

5. Enter your formula in the String field, consisting of labels of operands and the symbols +  -  *  /  (  ).

   Note: You can enter operand label names that you have not yet created.

6. Click Create.

Create or Edit Operand Labels

After you have created the formula and formula string, you create the operand labels. The following steps show you how to create operand labels.

Steps

Perform the following steps:

1. Search for the formula.

2. Select the formula.

3. Choose to create or edit formula operands (this final step is where you actually create or edit the operand labels used in the formula string).

Prerequisites

You have created the formula and formula string.

Navigation

Setup > Formulas > Formulas

Steps

You must perform all of the following steps for each formula operand.

1. Search for the formula.

2. In the Results panel, click the formula name hypertext link.

3. Click the Formula Operands button.

4. In the Formula Operands area, enter the Label for the operand name.

5. Select the operand to associate with the label.

6. Click Create.

Validate the Formula

Validation of the formula is optional, but recommended to verify its correctness. The validation procedures checks:

• Recursion—whether any one of the functions calls itself.

• Whether there is a correct context parameter for each of the function parameters used by all the formulas of the context, that is, the name and type of the parameters must match.

The full validation procedure also lets you see the values of your context parameters and operands, as well as the evaluated formula result.

For completeness, the following set of steps shows the full validation procedure.

Navigation

Setup > Formula Engine > Validation...Formula Validation page

Steps

Perform the following steps:

1. Select the Context and the Formula name.

2. Select the Contract.

3. Optionally select the Line Number.

4. Click the Validate button.

   The example page following shows that there was no recursion nor context group parameter mismatch.

5. Click Next to go to the next page.

   The Formula Context Parameters Value page appears. This shows the run-time context parameter values.

6. Click Next to go to the next page.

   The Evaluate Formula page shows the formula result and operand value.

   In the example, the context parameters PMR1 and PARAMETER2 both return a value of 1.

   The coding for the function OKLOPRND appears in Write the Source PL/SQL Function To Use in a Formula........).

   Assuming that the pre_tax_yield for the contract is null, the function OKLOPRND returns (20*(1+1) + 0), that is, 40.