Fast Formula – Without DBI

Fast Formula & Value Set

In EBS days, Fast Formula allows us to write SQL through their Formula Functions. This feature allows accessing any table and columns through the Fast Formula. In Fusion, Fast Formula functions are limited to customers who have onsite implementation. 

In Fusion, the only way to access the data is using DBI or Input Values. In a real life scenario, many times, it is hard to find the DBI we want. Sometimes the DBIs are not available, sometimes the Context are not available, even sometime, the DBI are available in an array format that may lead to performance issues. 

For a long time, we were looking for a way where we can access the data without using DBI.  Finally we found the Holy Grail. 

Holy Grail of Fast Formula: 

We know that we cannot access the database and therefore writing our own SQL or a Function is out of the question. So, we are looking for an option where SQL can be defined through a setup and that can be executed from Formula Function.

After some search , we found out exactly what we were looking for. 

Value Set:  Value Set is a setup where customer can define a table, id and value columns with Where, Condition and Order By clause. This setup allows us to define a simple Select statement from a table with condition. 

This option, Value Set can be navigated as follows. 

Select Setup and Maintenance 

From the Setup and Maintenance Overview, search for Manage Payroll Value Sets as shown in the following screen. 

From the search Result, click ‘Go to Task’ to navigate to the page where Value Set can be created as follows.

In the above example, the Value Set is defined as 

select the lookup code and meaning from HR_LOOKUP for lookup type ‘CMP_TCS_COLS_BNFTS’

The Value Set needs to have at a minimum, Table, ID and Value columns for using Fast Formula Function (ID is not a required column in the Value Set UI).

Formula Function: GET_VALUE_SET: 

This Formula Function, Get_Value_Set is delivered by Oracle Payroll. This function is defined with 4 parameters and returns a character value. 

The parameters are: 

1. P_VALUESET: This is a required parameter and is used for passing the Value Set name.
2. P_BINDS: this is an optional parameter. If you have defined your Value Set with bind variable in where clause, you can pass a value to the bind variable with this parameter. The Formula Function uses a predefined format for passing the value.

Predefined Format : The first character defines the delimiter and the second character defines the  assignment character. From third character onwards, you can pass the bind variable name and value to the bind variable. We will go through the format in detail in our examples.

3. P_FILTER: This is an optional parameter. With this parameter, we can pass additional filter condition. The Filter value is validated against the Value column Name of the Value Set. The function uses like condition to validate the value.
4. P_ENABLE_CACHE: I don’t think this parameter is used anywhere in Fast Formula, so we can ignore this parameter. 

Now, we will go through a few examples to understand how we can use this Function and Value Set in our Fast Formulas. For our example, we use the same set of data so that we can understand the different options with ease.

Data: We use the following lookup data for the type, CMP_TCS_COLS_BNFTS for our example.

Example 1

Value Set:  The first Value Set is a very simple value set.
The Value Set needs to have at a minimum - Table, ID and Value columns. The Formula Function, Get_Value_Set returns ID column value to the Fast Formula. The following Value Set is defined for returning Lookup Code for the lookup type, CMP_TCS_COLS_BNFTS. 

Fast Formula:  The following Formula is defined for TCS item. The Formula calls the function, Get_Value_Set with our Value Set. And we log the return value in the ESS log.

The Fast Formula attached to the TCS Item as follows.

We are not going into the details of the TCS setup here. Assume the item is added to a Category and the Category is added to a Statement and the Statement is processed in ESS. 

The following is the result of the ESS process and the result of the Get_Value_Set. Since there is no “order by” clause defined in the Value Set, we expect any one value from the Lookup type.

The result shows the first value of the column ‘Code’, DESC from our select statement.  

Example 2  

We changed the first example to bind the lookup type instead of hard coding to ‘CMP_TCS_COLS_BNFTS’.
Value Set:  Bind variable are defined with the following format 

:{PARAMETER.<BIND_NAME>}

Fast formula:   As we explained earlier, the bind variable is passed to the function in a specific format.
In our example, the bind value is passed as ‘|=TYPE_CODE=’CMP_TCS_COLS_BNFTS’ ‘

1. The first character, | defines the delimiter. 
2. The second character, = defines the assignment character. 
3. TYPE_CODE is the bind variable name from the Set Value and = is the assignment character. 
4. Finally we pass the value of the bind variables, ‘CMP_TCS_COLS_BNFTS’. 

In theory, you can use any delimiter and assignment character but with our experience, we would recommend using the same delimiter (|) and assignment character (=).

We expect the same result as in the previous example.

 

Example 3

In this example, we try to bind more variables to explain how more than one value is passed.
Value Set:  With two bind variables, one is equi joint and other one is a like condition. Please note that the like condition is already defined within quotes.

Fast Formula:  The value of both bindings is passed in a different way.  The first binding is not defined with quotes and so we need to pass the value with a quote. The second binding is defined within quotes and so the value is passed without any quote. 

As per our value set, we are expecting one of the contribution values, EEC_CNTRB or ERC_CNTRB.

Example 4 :  In this example, we use the Value Set from Example 3. We pass the Value to the third variable, P_FILTER.

This parameter value is validated against the Value Column Name of the Value Set in ‘like’ condition.
In our example, ‘Meaning’ of the lookup is the value column Name.  We pass the value ‘Company’ to filter the row.

As per our Value Set and bind values, we expect company contribution Lookup Code, ERC_CNTRB.

Hope the above examples helped you understand how a Formula and Value Set can be used to execute any SQL in Fast Formula.

YouTube Video 19 Formula:

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

Name: CMP_PERSON_SELECTION

Type: Compensation Person Selection

Programer: Tilak

purpose: Do not process the people who where already process by 

         checking the period processed flag and row in person statement

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

INPUTS ARE CMP_IV_STMT_ID, CMP_IV_PERIOD_ID 

ltem = ess_log_write('Entering  CMP_PERSON_SELECTION' ) 

l_value = 'Y' 

stmtprocessed = GET_VALUE_SET('TCS_STMT_PROCESSED')

ltem = ess_log_write('TCS_STMT_PROCESSED ' +  stmtprocessed) 

IF stmtprocessed = 'Y' then (

  l_value = 'N' 

)

ltem = ess_log_write('Leaving  CMP_PERSON_SELECTION '  + l_value ) 

RETURN l_value

YouTube Video 20 Formula:

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

Name: CMP_PERSON_SELECTION

Type: Compensation Person Selection

Programer: Tilak

purpose: Do not process the people who where already process by 

         checking the period processed flag and row in person statement

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

Name: CMP_PERSON_SELECTION

Type: Compensation Person Selection

Programer: Tilak

purpose: Do not process the people who where already process by 

         checking the period processed flag and row in person statement

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

INPUTS ARE CMP_IV_STMT_ID, CMP_IV_PERIOD_ID 

ltemp = ess_log_write(' Entering CMP_PERSON_SELECTION') 

l_value = 'Y'

/* Calling valueSet */

/* 

stmtprocessed = GET_VALUE_SET('TCS_PERSON_SELECTION')

*/ 

stmtperd = TO_CHAR(CMP_IV_PERIOD_ID) 

ltemp = ess_log_write(' CMP_IV_STMT_PERD_ID: ' + stmtperd ) 

param = '|=STMTPERDID='|| stmtperd

ltemp = ess_log_write(' param ' + param) 

stmtprocessed = GET_VALUE_SET('TCS_STMT_PROCESSED', param)

ltemp = ess_log_write(' TCS_PERSON_SELECTION: ' + stmtprocessed) 

/* Validating result of value set */

If stmtprocessed = 'Y' Then (

   l_value = 'N'  

)

ltemp = ess_log_write(' Leaving CMP_PERSON_SELECTION: ' + l_value ) 

RETURN l_value

Youtube Video 21 Formula

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

Name: CMP_PERSON_SELECTION

Type: Compensation Person Selection

Programer: Tilak

purpose: Do not process the people who where already process by 

         checking the period processed flag and row in person statement

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

INPUTS ARE CMP_IV_STMT_ID, CMP_IV_PERIOD_ID 

ltem = ess_log_write('Entering  CMP_PERSON_SELECTION' ) 

l_value = 'Y'

 

personid = get_context(PERSON_ID, -1)

perid = TO_CHAR(CMP_IV_PERIOD_ID)

ltem = ess_log_write('stmt id  ' +  to_char(CMP_IV_STMT_ID) ) 

ltem = ess_log_write('CMP_IV_PERIOD_ID ' +  perid) 

ltem = ess_log_write('person id  ' +  to_char(personid) ) 

param = '|=STMTPERDID='||perid 

ltem = ess_log_write('param ' +  param) 

/* calling value set */

stmtprocessed = GET_VALUE_SET('TCS_PERSON_SELECTION',param )

ltem = ess_log_write('TCS_PERSON_SELECTION ' +  stmtprocessed) 

IF stmtprocessed = 'Y' then (

   param = '|=STMTPERDID='||perid ||' |STMTID='|| TO_CHAR(CMP_IV_STMT_ID)||' |PERSON_ID='|| to_char(personid)

   ltem = ess_log_write('param ' +  param) 

   perperdid =  GET_VALUE_SET('TCS_STMT_PROCESSED',param )

   ltem = ess_log_write('perperdid ' +  perperdid ) 

   if perperdid != ' ' then (

      l_value = 'N'

   )  

)

ltem = ess_log_write('Leaving  CMP_PERSON_SELECTION '  + l_value ) 

RETURN l_value