Fast Formula - Functions

Fast Formula and Functions

Fast Formula allows executing a PL/SQL Function through Fast Formula function definitions.

Fast Formula function is an interface between Fast Formula engine and the PL/SQL functions.

Each product that uses Fast Formula has seeded various Functions that can be used in the Fast Formula.

Fast Formula Functions is one of the most used features in EBS, especially in Oracle Benefit and Oracle Compensation. In EBS, the developer can write any function in PL/SQL and define that as Fast Formula Function and this function can be executed in the application via the Fast Formula.  EBS has even provided an UI to define the Fast Formula Functions.

But in Fusion, unless it is an onsite implementation, the user does not have access to the database. Therefore, it is not possible for the user to create PL/SQL Function.

Since there is no option to define the Fast Formula Function, we are not going into the details of defining the Fast Formula Function. If your implementation is onsite, you can follow the EBS documents. Only difference from the EBS days is that the Fast Formula is executed in a different schema. So, you need to create synonyms and provide proper grants to execute the functions.

In this section, we will explain the different components of the Fast Formula Functions and how it is used in Fast Formula from a user point of view.

Components of Fast Formula:

1)       PL/SQL Function

2)       Return Type

3)       Context

4)       Parameters

PL/SQL Function: Fast Formula does not support any PL/SQL function that uses an array or any other collection as a parameter or return value. The name of the PL/SQL Function does not have to necessarily match the Fast Formula Function names. Function overloads are allowed.

Return Type: Return Type should be one of the simple data types – Text, Number or Date. The return type must match with what is returned by the PL/SQL Function.

Context:  This is a hidden part of the Fast Formula Function. Some parameter values in the PL/SQL Function are passed by the Fast Formula engine from the Context values. This way the user does not have to worry about these parameters. For example, to get the currency conversion rate, we use the Function GET_RATE and we pass parameters: From Currency, To Currency, Conversion Rate Type and Default value. We do not pass the date when the currency conversion is extracted and it is passed from the EFFECTIVE_DATE Context. You might have noticed that we never pass the person id or the assignment id or the effective date to the Fast Formula Functions because these values are passed from the Context behind the scenes. If the Context is not available in your Fast Formula Type then you will notable to use the Function in your Fast Formula.

Parameter: Parameter needs to be one of the basic types (Text, Date, or Number). It can be optional or required and it can be IN or OUT or Both. The Fast Formula developer needs to pass the values to these parameters. It is always passed in sequential order. I have never tried the Named Parameter. When I try this option, I will update the section. Usually the optional parameters are defined at the end and therefore, you do not need to worry about the Named Parameter. Sometimes they define the same function with different parameters for overloading. For example, TO_CHAR is an overloaded function with different parameter type. This is for date and number.

Example:

L_DATA = ESS_LOG_WRITE( 'BEGIN MY_FIRST_CWB_DEFAULT_FF' ) 

Ess_Log_write is a Fast Formula Function with 1 parameter and returns a Text Value.

L_date = ADD_MONTHS(L_PL_START_DATE, 1)

This is another Fast Formula Function with 2 parameters, 1 with Date, another with Number and returns a Date value.

We have tried to list all the available Fusion Functions below. You can always approach the Oracle team for further explanation. Some functions may be obsolete or not available in your release. Please check with the documentation or with the Oracle support.

 Function List

Fast Formulas and Arrays

Fast Formulas and Arrays

In this session, we will cover the usages of Arrays in Fast Formula.  

Fast Formula uses Arrays since its inception. Even in EBS days, the return values are passed as name and value pair of Arrays.

In Fusion, the Fast Formula started using Arrays extensively. The knowledge of the Array in Fast Formula is so important that the developer would need to understand Arrays to write a meaningful Fast Formula.  For example, Arrays can be used in

1. Input Values
2. DBI
3. Variables
4. Return values.

In this session, we will explain the above usages in detail with an example. Before we explain the usages, we will explain about the different types of Arrays.

In Fusion Fast Formula, Arrays are defined with two attributes:

1. Type of Value - indicates the value type (Number, Date or Char) stored in the Array.
2. Type of Index - indicates how the Array is indexed.

The following options are available for defining an Array in Fusion Fast Formula.

Array Type	Meaning
DATE_NUMBER	Date value and number index Array
DATE_TEXT	Date value and text index Array
NUMBER_NUMBER	Number value and number index Array
NUMBER_TEXT	Number value and text index Array
TEXT_NUMBER	Text value and number index Array
TEXT_TEXT	Text value and text Index Array

Usage of Arrays:

Input Values: There are many products in Fusion that pass the Input Values as Array. For example, Total Compensation Statement (TCS) process the data at a person level. Therefore, when the Fast Formula is executed for a person, it sets the primary Assignment Id as Context, HR_ASSIGNMENT_ID. In case if the person has more than one assignment, it passes all the assignments as Array to the Fast Formula so that the formula can use the Assignments to process the data.

The syntax for declaring the Array Input Value is:

INPUTS ARE CMP_IVR_ASSIGNMENT_ID (NUMBER_NUMBER)

You can also default the Array with an empty Array as follows.

DEFAULT for CMP_IVR_PAY_ASSIGNMENT_ID is EMPTY_NUMBER_NUMBER

To find the Array input values, you need to check the documentation of each of the products.

DBI: The Array DBI is also called range DBI. Every product in HCM has seeded these Array DBIs. It looks like the Array DBIs are more popular than the conventional ones in Fusion.

The Array DBIs are declared and defaulted as follows

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_START_DATE IS '1900/01/01 00:00:00' (date)

If you use the wrong syntax, you will get a compilation error.

If the Array DBI is not handled in a proper manner, it may lead to a performance issue. Since the Array DBI holds a range of data, you will need to implement the proper validation to get the desired results.

Variables: The variables are like any other Array, the only difference is how you declare them. You can declare the variable Array as follows.

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER

Return values: Fast Formula engine allows returning Array variables provided the Formula Type supports it. The Product that executes the Formula determines whether the Array variables that are returned from Fast Formula are supported. As far as I know, in Compensation product, TCS supports Arrays for their Formula Type, “Total Compensation Item” in their latest release. (please check the documentation for the availability).

Fast Formula has provided the following functions to handle the Arrays.

Function	Syntax	Meaning
COUNT	<Array> .COUNT	Return the count of an Array elements Example: l_count = l_array.count
DELETE	<Array>. DELETE( <index value> )	Delete a element from the Array Example: l_array.delete(2) or  l_array.delete(‘TWO’) depending on the index type of the Array.
DELETE	<Array>. DELETE()	Delete all the elements.   Example: l_array.delete()
EXISTS	<Array>. EXISTS ( <index value> )	If a value exists at an index of an Array. It returns a Boolean. Example:  if l_array.exists(1) then
FIRST	<Array>.FIRST( <default value> )	Returns first index of an Array. If there are no elements, then return the default value. Example: l_index = l_array.first(-1)
LAST	< Array>.LAST (<default value>)	Returns last index of an Array. If there is no elements then returns the default value. Example: l_index = l_array.last(-1)
NEXT	< Array>.NEXT (<index>,  <default value>)	Returns the next index of the index parameter. If there is no next element, returns default value. Example: : l_index =l_array.next(l_index, -1)
PRIOR	< Array>.PRIOR (<index>, <default value>)	Returns the previous index of the index parameter. If there is no previous element, returns default value. Example: l_index =l_array.prior(l_index,  -1)
	Array[<index>]	To get/set a value from/to an Array element. Example:  L_number  = l_array[100] l_array[100] = L_number

Sample Fast Formula to handle Arrays:

/*

 Author    : Tilak-Lakshmi

 Name     : TCS_ITEM_ARRAY_TEST

 Type      : Total Compensation Item

 Purpose  : Example of using Array DBI, input values and return variables. This formula returns changed salary of every assignment.

*/

/*Defaulting DBI */

DEFAULT_DATA_VALUE FOR CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT IS 0

/* Defaulting Input Values and variables*/

DEFAULT for CMP_IVR_ASSIGNMENT_ID is EMPTY_NUMBER_NUMBER

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER

DEFAULT FOR VALUES is EMPTY_TEXT_NUMBER

DEFAULT FOR ASSIGNMENTS is EMPTY_TEXT_NUMBER

/* Declaring Input Values */

INPUTS ARE CMP_IV_PERSON_ID , CMP_IV_PERIOD_START_DATE(DATE), CMP_IV_PERIOD_END_DATE (DATE),

      CMP_IVR_ASSIGNMENT_ID(NUMBER_NUMBER) , CMP_IVR_TERM_ID(NUMBER_NUMBER),

      CMP_IVR_PAY_ASSIGNMENT_ID (NUMBER_NUMBER), CMP_IVR_PAY_TERM_ID(NUMBER_NUMBER),

      CMP_IVR_PAY_RELATION_ID(NUMBER_NUMBER)

l_data= ESS_LOG_WRITE( 'ENTERING TCS_ITEM_ARRAY_TEST ' )

/* Get the first index of assignment */

index = CMP_IVR_ASSIGNMENT_ID.FIRST(-1)

l_data= ESS_LOG_WRITE( ' first index ' + TO_CHAR(index ))

l_counter = 1

/* Loop through the assignments */

WHILE (CMP_IVR_ASSIGNMENT_ID.EXISTS(index))

LOOP (

   l_data= ESS_LOG_WRITE( ' asg ' + TO_CHAR( CMP_IVR_ASSIGNMENT_ID[index]) )

  

   /*Set the context to assignment id */

   CHANGE_CONTEXTS(HR_ASSIGNMENT_ID= CMP_IVR_ASSIGNMENT_ID[index] )

     (

       l_asg  =  CMP_IVR_ASSIGNMENT_ID[index]

       l_data= ESS_LOG_WRITE( ' ASG CONTTEXT IS SET ' + TO_CHAR(l_asg))

       /* get salary index and loop through the Array */

       l_sal_indx = CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT.FIRST(-1)

       WHILE (CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT.EXISTS(l_sal_indx ) )

       LOOP (          

           if (CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT[l_sal_indx] <> 0 ) THEN

           (

              COMPENSATION_DATES[l_counter]= TO_CHAR(CMP_IV_PERIOD_START_DATE,'YYYY/MM/DD')

              ASSIGNMENTS[l_counter] = TO_CHAR(l_asg)

              VALUES[l_counter] = TO_CHAR( CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT[l_sal_indx])

              l_counter = l_counter + 1

           )

           l_sal_indx  = CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT.NEXT(l_sal_indx , -1)

       )

   )

   index  = CMP_IVR_ASSIGNMENT_ID.NEXT(index ,-1)

)

l_data= ESS_LOG_WRITE( ' LEAVING TCS_ITEM_ARRAY_TEST ' )

RETURN COMPENSATION_DATES, VALUES, ASSIGNMENTS

Explanation of the Formula:

When declaring an Array DBI, please note the syntax. It is a little different than the normal DBI declaration.

DEFAULT_DATA_VALUE FOR  CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT IS 0

We use the same syntax to default the Input Values and for declaring/defaulting variables.

DEFAULT FOR CMP_IVR_ASSIGNMENT_ID is EMPTY_NUMBER_NUMBER

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER

DEFAULT FOR VALUES is EMPTY_TEXT_NUMBER

DEFAULT FOR ASSIGNMENTS is EMPTY_TEXT_NUMBER

The Input Values are declared as below.

INPUTS ARE CMP_IV_PERSON_ID, CMP_IV_PERIOD_START_DATE(DATE),
CMP_IV_PERIOD_END_DATE (DATE),

    CMP_IVR_ASSIGNMENT_ID(NUMBER_NUMBER) , CMP_IVR_TERM_ID(NUMBER_NUMBER),

       CMP_IVR_PAY_ASSIGNMENT_ID (NUMBER_NUMBER), CMP_IVR_PAY_TERM_ID(NUMBER_NUMBER),

       CMP_IVR_PAY_RELATION_ID(NUMBER_NUMBER)

To loop through the Array, whether it is DBI or Input Values; we need to identify the index type. In our example, all the Arrays are indexed by number. We can identify the index type from the document or the DBI list from the Fast Formula UI.

Once we know the index type, we need to identify the first index. Many developers use 1 as starting index but I prefer to get the first index from the Array itself. In the .FIRST method, we use -1 as default value. In case there is no element in the Array, the method will return the default value, -1.

index = CMP_IVR_ASSIGNMENT_ID.FIRST(-1)

l_data= ESS_LOG_WRITE( ' first index ' + TO_CHAR(index ) )

l_counter = 1

In the loop, we iterate as long as an element exists. We use the .EXISTS method to find the existence of the element for an index.  

/* Loop through the assignments */

WHILE (CMP_IVR_ASSIGNMENT_ID.EXISTS(index))

LOOP (

We get the assignment id and set the Context, HR_ASSIGNMENT_ID with the assignment id,
so that the DBI for the Assignment Id can be used.

   /*Set the Context to the Assignment Id */

 CHANGE_CONTEXTS(HR_ASSIGNMENT_ID= CMP_IVR_ASSIGNMENT_ID[index] )

  (

       l_asg  =  CMP_IVR_ASSIGNMENT_ID[index]

       l_data= ESS_LOG_WRITE( ' ASG CONTTEXT IS SET ' + TO_CHAR(l_ASG) )

In the following code, we get the salary changes for the assignment and then assign the value in the Array variables.

       l_sal_indx = CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT.FIRST(-1)

       WHILE (CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT.EXISTS(l_sal_indx ) )

       LOOP (

           if ( CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT[l_sal_indx] <> 0 ) THEN

           (

              COMPENSATION_DATES[l_counter]= TO_CHAR(CMP_IV_PERIOD_START_DATE,'YYYY/MM/DD')

              ASSIGNMENTS[l_counter] = TO_CHAR(l_asg)

 VALUES[l_counter] = TO_CHAR( CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT[l_sal_indx] )

               l_counter = l_counter + 1

           )

   

Get the next index for the DBI and Input values to move to the next element.

           l_sal_indx  = CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT.NEXT(l_sal_indx, -1)

       )

  index  = CMP_IVR_ASSIGNMENT_ID.NEXT(index ,-1)

)

l_data= ESS_LOG_WRITE( ' LEAVING TCS_ITEM_ARRAY_TEST ' )

RETURN COMPENSATION_DATES , VALUES,ASSIGNMENTS

Hope this section has helped you to get an understanding of the usages of Arrays in Fast Formula. Please let us know if you have any questions.

YouTube Tutorial on Array DBI

/*************************************************

Name: TCS_PERSON_SELECTION

type: Compensation Person Selection

Date: today 

Dev:  Tilak

Requirement: Select the Person  Whose total salary of all the Job is > 50K  as of effective date

*************************************************/

/* Declare DBI */ 

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_START_DATE is   '1900/01/01' (date)

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_END_DATE is   '1900/01/01' (date)

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_ASSIGNMENT_ID is -1 

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_STATUS_TYPE is ' '

DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_LATEST_CHANGE is ' '

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT is 0

l_temp =  ESS_LOG_WRITE('Entering  TCS_PERSON_SELECTION ' )

RETAVL = 'Y' 

SALARY = 0

l_person_id = get_context(PERSON_ID, -1 )

l_asg_id    = get_context(HR_ASSIGNMENT_ID, -1)

l_date      = get_context(EFFECTIVE_DATE, '1900/01/01' (date) )

l_temp =  ESS_LOG_WRITE('person id  ' + TO_CHAR(l_person_id) )

l_temp =  ESS_LOG_WRITE('Asg id  ' + TO_CHAR(l_asg_id) )

l_temp =  ESS_LOG_WRITE('Eff Date  ' + TO_CHAR(l_date) )

/* Loop through the person assignment */ 

count = PER_HIST_ASG_ASSIGNMENT_ID.count 

l_temp =  ESS_LOG_WRITE('Count of asg  ' + TO_CHAR(count) )

index = PER_HIST_ASG_ASSIGNMENT_ID.FIRST(-1)

While (PER_HIST_ASG_ASSIGNMENT_ID.EXISTS(index))

Loop (

   l_asg =    PER_HIST_ASG_ASSIGNMENT_ID[index]

   l_s_date = PER_HIST_ASG_EFFECTIVE_START_DATE[index] 

   l_e_date = PER_HIST_ASG_EFFECTIVE_END_DATE[index] 

   l_type   = PER_HIST_ASG_STATUS_TYPE[index]

   l_change = PER_HIST_ASG_EFFECTIVE_LATEST_CHANGE[index] 

 

   l_temp =  ESS_LOG_WRITE('l_asg  ' + TO_CHAR(l_asg) )  

   l_temp =  ESS_LOG_WRITE('l_s_date  ' + TO_CHAR(l_s_date) )

   l_temp =  ESS_LOG_WRITE('l_e_date  ' + TO_CHAR(l_e_date) )

   l_temp =  ESS_LOG_WRITE('l_type  ' + l_type )

   l_temp =  ESS_LOG_WRITE('l_change  ' + l_change )

   /* Validation */ 

   IF l_date >= l_s_date AND  l_date <= l_e_date THEN 

   (

      IF l_type = 'ACTIVE' and  l_change = 'Y' then 

      (

         l_temp =  ESS_LOG_WRITE('setting context for  ' + TO_CHAR(l_asg) )  

         CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = l_asg)(

            SALARY = SALARY + CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

            l_temp =  ESS_LOG_WRITE('SALARY  ' + TO_CHAR(SALARY) )

            

         )

      

       )

   )

   

  index = PER_HIST_ASG_ASSIGNMENT_ID.NEXT(index, -1) 

)

IF SALARY > 5000 then 

(

      RETVAL = 'Y' 

      l_temp =  ESS_LOG_WRITE('FINAL SALARY  ' + TO_CHAR(SALARY) )

)

l_temp = ESS_LOG_WRITE('Returning  TCS_PERSON_SELECTION ' || RETAVL)

Return RETAVL

YoutTube Tutorial on Array Variables and Input Values

/******************************

FF : TCS_ARRAY_ITEM_FF

Type : Total Compensation Item

Developer: Tilak

Purpose: Find the salary of each person for every assignment and returns with asg id and start date

********************************/

/* Declare DBI */ 

DEFAULT FOR CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT is 0

DEFAULT FOR CMP_ASSIGNMENT_SALARY_DATE_FROM is   '1900/01/01' (date)

/* Default IV */ 

DEFAULT for CMP_IVR_ASSIGNMENT_ID is EMPTY_NUMBER_NUMBER 

/* Default Variables */ 

DEFAULT FOR COMPENSATION_DATES is EMPTY_TEXT_NUMBER 

DEFAULT FOR VALUES is EMPTY_TEXT_NUMBER 

DEFAULT FOR ASSIGNMENTS is EMPTY_TEXT_NUMBER 

/* Declare IV */ 

INPUTS ARE  CMP_IV_PERIOD_START_DATE(DATE), 

            CMP_IV_PERIOD_END_DATE (DATE), 

            CMP_IVR_ASSIGNMENT_ID(NUMBER_NUMBER) , 

            CMP_IVR_TERM_ID(NUMBER_NUMBER), 

            CMP_IVR_PAY_ASSIGNMENT_ID (NUMBER_NUMBER), 

            CMP_IVR_PAY_TERM_ID(NUMBER_NUMBER), 

            CMP_IVR_PAY_RELATION_ID(NUMBER_NUMBER)

/* Main */ 

l_temp = ESS_LOG_WRITE( 'ENTERING TCS_ARRAY_ITEM_FF ' ) 

l_count = CMP_IVR_ASSIGNMENT_ID.count 

l_temp = ESS_LOG_WRITE( 'count asg  ' + TO_char(l_count) ) 

counter = 1

index = CMP_IVR_ASSIGNMENT_ID.FIRST(-1)

WHILE (CMP_IVR_ASSIGNMENT_ID.EXISTS(index) )

LOOP

(

    l_asg = CMP_IVR_ASSIGNMENT_ID[index] 

    l_temp = ESS_LOG_WRITE( 'asg  ' + TO_char(l_asg) ) 

    CHANGE_CONTEXTS(HR_ASSIGNMENT_ID = l_asg)

    (

        salary = CMP_ASSIGNMENT_SALARY_ANNUAL_AMOUNT

        fromDate = CMP_ASSIGNMENT_SALARY_DATE_FROM

        COMPENSATION_DATES[counter] = to_char(fromDate, 'YYYY/MM/DD')

        VALUES[counter] = to_char(salary)

        ASSIGNMENTS[counter] =  to_char(l_asg)

        counter = counter + 1

        l_temp = ESS_LOG_WRITE( 'salary  ' + TO_char(salary) )  

        l_temp = ESS_LOG_WRITE( 'fromDate  ' + TO_char(fromDate) ) 

        l_temp = ESS_LOG_WRITE( 'l_asg  ' + TO_char(l_asg) )   

     )

     index = CMP_IVR_ASSIGNMENT_ID.NEXT(index, -1)

)

l_temp = ESS_LOG_WRITE( 'Leaving TCS_ARRAY_ITEM_FF ' )

RETURN COMPENSATION_DATES,VALUES,ASSIGNMENTS