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
Input Values
DBI
Variables
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:
Type of Value - indicates the value type (Number, Date or Char) stored in the Array.
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.
/*************************************************
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
This comment has been removed by the author.
ReplyDeleteExtraordinary. I never know we can use arrays in ff. Great work
ReplyDeleteCan you write a formula to capture email ID is an array & change each character to its ascii & display it?
ReplyDeleteThanks,
Aloka
you can get emails from PER_PER_EMAIL_MAIL_ADDRESS.
DeleteI do not understand the second part.
Why do you need to convert it to ASCII char ? and what you mean by display it ?
Very useful..Thank you
ReplyDeleteUnable to get the DFF created in biographical info, the attribute is attribute 1 , can you help with that?
ReplyDeletetable name ?
Deleteyou can use this DBI PER_PER_BASIC_ATTRIBUTE_TEXT1
DeleteHi ,
ReplyDeleteI am using error while compilation the Compensation Default and override FF code as " compilation failed while creating the formula PL/SQL package FFP300004510533057_01011951."
Could you please suggest me where I did the mistake.
Code:========
INPUTS ARE CMP_IV_ASSIGNMENT_ID (number), CMP_IV_PLAN_ID (number)
/*=========== INPUT VALUES DEFAULTS ENDS======================*/
/*================ FORMULA SECTION BEGIN =======================*/
DEFAULT_DATA_VALUE FOR PER_EXT_CMP_FEED_STATUS_POST_PROMOTION IS 'AA'
L_DAT = ESS_LOG_WRITE ('PROCESS HAS STARTED')
L_HR_ASSIGNMENT_ID = GET_CONTEXT(HR_ASSIGNMENT_ID,-1)
L_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)
L_DAT = ESS_LOG_WRITE('ASSIGNMENT_ID IS'|| to_char(L_HR_ASSIGNMENT_ID) )
L_DAT = ESS_LOG_WRITE('PERSON_ID IS'|| to_char(L_PERSON_ID ))
L_INDEX = 1
L_SELECT ='N'
WHILE PER_EXT_CMP_FEED_STATUS_POST_PROMOTION.EXISTS(L_INDEX)
LOOP
( L_DAT = ESS_LOG_WRITE ('INDEX VALUE IS' || TO_CHAR( L_INDEX))
L_VAL = PER_EXT_CMP_FEED_STATUS_POST_PROMOTION[L_INDEX]
L_DAT = ESS_LOG_WRITE ( 'VALUE AT POST PROMOTION IS '|| L_VAL )
IF (L_VAL = 'ORA') THEN
(L_SELECT ='Y'
EXIT
)
L_INDEX =L_INDEX + 1
)
RETURN L_SELECT
I got the same error, i think it has something to do with the DBI, the errors raised when you access the DB. You can talk to the oracle support.
DeleteIn my opinion, they do not support your logic
1) You are trying to access the table that is populated by the process, that is not supported.
2) You are using the DBI that does not have any context, you possibly fetching millions of records. even if the ff compiles, you are going to kill the system.
Used the info above to navigate arrays for a FF that we have. Thanks!
ReplyDeleteVery apt! Thank you for such clear and informative blog.
ReplyDeleteCan you please suggest me what material/video's to follow to learn writing FF. I would like to start with the basics
ReplyDeleteis it not what this blog doing? :). Well I have not seen much doc or video. Well, you can look at oracle documents or white papers.
DeleteFacing same issue 'Formula XX_EXT_CONFTE_NEW compilation failed while creating the formula PL/SQL package FFP300000048316049_01011951.'
ReplyDeleteHow can I find a list of arrays that are already defined in Fusion payroll for use in fast formula
ReplyDeleteplease see the documentation.
Deleteexiting a while loop, can u please give an example sir.. Navya here.
ReplyDeletethis is from manual, hope this helps:
DeleteFOUND = -1 /* -1 is not a valid index for A. */
I = A.FIRST(-1)
WHILE (A.EXISTS(I))
LOOP
(
/* EXIT-clause for early exit. */
IF A[I] = KEY THEN
(
FOUND = I
/* Exit the loop. */
EXIT;
)
I = A.NEXT(I,-1)
)