1. Arrays in Oracle Fusion Fast Formulas
In this article we will learn how to use and handle arrays in oracle fusion fast formulas. In oracle fusion fast formulas array are used extensively thus it has become necessity of the time to be familiar with the concept. We assume that you have some basic knowledge about fast formulas. To make this article short, sweet and to the point we will only talk about concepts of array in fast formula. So let’s begin.
2. Array Type Data Base Items (DBI)
Yes you got it right there are array types of database items (DBI) also, by look they are very much similar to the normal data base items. To understand it better let’s open an empty fast formula and search for data base item “PER_HIST_ASG_EFFECTIVE_START_DATE”
Did you notice the data type of this DBI that reads “Date value and number index array”. For normal DBIs you generally see data type value as Number, Text or Date. I am sure you have got it by now that this is an array type database item.
Normal DBI holds single value at a time but array type DBI holds multiple values. By the data type description of this DBI you can get that it holds date type values, where index is of type number.
PER_HIST_ASG_EFFECTIVE_START_DATE DBI is capable of storing all effective start date changes happening on an assignment record. For example let’s assume for an assignment there are five changes and the effective start dates for those changes are 1st Jan 2010, 30th May 2017, 10th March 2019, 31st December 2019 and 10th Feb 2020 in the same order. Since the index type of this DBI is number, oracle will store information similar to what is depicted below.
2.1. How to declare and default array type DBI
To know more about usages of the array type DBI, let’s create a simple fast formula with name “Test” and formula type Participation and Rate Eligibility. Just add below three lines in fast formula and compile it. If you need to debug your fast formula, you can read this article » How to Debug Fast Formulas
DEFAULT FOR PER_HIST_ASG_EFFECTIVE_START_DATE IS '1951/12/31 00:00:00' (date) ELIGIBLE = 'Y' RETURN ELIGIBLE
What happened, it didn’t work right!! You must have got below error. “conflicting type usage”
But why is it so? This is how you declare and default any DBI right? Actually array type DBI items do not follow conventional default syntax. They need to be declared and defaulted as follows
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_START_DATE IS ‘1951/12/31 00:00:00’ (date)
Edit your fast formula and compile again. This time you will not see the error, as shown in the image below.
2.2. How to use array type DBI
We have so far seen what array type DBIs are and how to declare and default them. Now let’s understand how to use (or read their values) inside fast formula. Generally when we want to get value of a DBI, we directly use them as follows (here PER_ASG_FTE is a DBI item)
worker_fte = PER_ASG_FTE
However, this will not work in array type DBI, which is obvious because array is not a single value. When we want to retrieve value we also need to tell the index position. So if we want to know when the first assignment change happened for a worker, we will use syntax like this
first_change = PER_HIST_ASG_EFFECTIVE_START_DATE[1]
Make a note of the index position we are passing using brackets []. But this is not the recommended way to get first index position. There could be scenarios where first position is starting from index 5 or something else. So to get the first position we use syntax like FIRST, as described below.
first_position = PER_HIST_ASG_EFFECTIVE_START_DATE.FIRST(-1)
2.3. Different Functions Available With Respect to Array
| Function | Syntax | Example |
| FIRST | <Array>.FIRST( <default value> ) | PER_HIST_ASG_EFFECTIVE_START_DATE.FIRST(-1)
This will retrieve the first position index but if there are no values it will return default value that is -1 |
| LAST | <Array>.LAST( <default value> ) | PER_HIST_ASG_EFFECTIVE_START_DATE.LAST(-1)
This will retrieve the last position index but if there are no values it will return default value that is -1 |
| NEXT | < Array>.NEXT (<index>, <default value>) | PER_HIST_ASG_EFFECTIVE_START_DATE.NEXT(1,-99)
This will retrieve value in index position 1, if value doesn’t exist there, will return default value that is -99 |
| PRIOR | < Array>.PRIOR (<index>, <default value>) | PER_HIST_ASG_EFFECTIVE_START_DATE.NEXT(5,-99)
This will retrieve value prior to index position 5, if value doesn’t exist there, will return default value that is -99 |
| COUNT | <Array> .COUNT | PER_HIST_ASG_EFFECTIVE_START_DATE.COUNT
This will give the total count of an array. |
| DELETE | <Array>. DELETE( <index value> ) | CUSTOM_ARRAY_DBI_NAME.DELETE(2)
This will delete value in index position 2. If index position is not passed, it will delete all values from array.
|
| EXISTS | <Array>. EXISTS ( <index value> ) | PER_HIST_ASG_EFFECTIVE_START_DATE.EXISTS(1)
If value exists in index position 1, it will return true otherwise false. |
3. Let’s try to understand Arrays with a real life scenario
To cement the concept we have learnt so far, let’s assume we have a requirement to check eligibility of an employee for a performance document based on employees adjusted service date. Adjusted service date code that we need to consider is “’ADJ_SERVICE_DATE’”. If employees adjusted service date anniversary is falling in the current month, he should be eligible provided his assignment status is in active status.
To get this create a Participation and Rate Eligibility fast formula using following code
3.2. Fast formula based on the adjusted service date
/*****************************************************************
FORMULA NAME: AVINASH_PERFORMANCE_DOC_ELIGIBILITY_FF
FORMULA TYPE: Participation and Rate Eligibility
DESCRIPTION : Fast Formula to check the eligibility
Name Date Comments
--------------------------------------------------------------------------------------------
iavinash.com 29-02-2020 Initial Version
--------------------------------------------------------------------------------------------
********************************************************************************/
DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_SENIORITY_DATE IS '1951/01/01' (date)
DEFAULT_DATA_VALUE FOR PER_SENDT_F_V3_SENIORITY_DATE_CODE IS 'XX'
DEFAULT FOR PER_ASG_STATUS_CODE IS 'X'
ELIGIBLE = 'N'
l_eff = GET_CONTEXT(EFFECTIVE_DATE, '1951/01/01' (date))
l_code_index = PER_SENDT_F_V3_SENIORITY_DATE_CODE.FIRST(-1)
l_srvc_dt_crr_mn = 'xx'
l_status = PER_ASG_STATUS_CODE
l_crr_mn = to_char(l_eff,'MM')
l_log = ESS_LOG_WRITE('Effective date is :'|| to_char(l_eff, 'DD-MON-YYYY') )
l_log = ESS_LOG_WRITE('Assignment status is : '|| l_status )
WHILE (PER_SENDT_F_V3_SENIORITY_DATE_CODE.EXISTS(l_code_index))
LOOP
(
IF ( PER_SENDT_F_V3_SENIORITY_DATE_CODE[l_code_index] = 'ADJ_SERVICE_DATE' ) THEN
(
l_ads_sdt = PER_SENDT_F_V3_SENIORITY_DATE[l_code_index]
l_srvc_dt_crr_mn = to_char(l_ads_sdt, 'MM')
l_log = ESS_LOG_WRITE('Adjusted service date exists for employee')
l_log = ESS_LOG_WRITE('Adjusted service date is : '|| to_char(l_ads_sdt, 'DD-MON-YYYY') )
EXIT
)
l_code_index = PER_SENDT_F_V3_SENIORITY_DATE_CODE.NEXT(l_code_index,-99)
)
IF ( l_srvc_dt_crr_mn = l_crr_mn AND l_status like 'ACTIVE_%' ) THEN
(
ELIGIBLE = 'Y'
l_log = ESS_LOG_WRITE('Person is eligible')
)
RETURN ELIGIBLE
After successful compilation your fast formula will look like this
3.3. Let’s understand the formula and array concepts more
If you give a close look to this fast formula you will find that
- At line number 11 and 12 we are using two array type data base items (PER_SENDT_F_V3_SENIORITY_DATE and PER_SENDT_F_V3_SENIORITY_DATE_CODE) check how are they declared.
- At line number 17 we are getting the first index position of DBI PER_SENDT_F_V3_SENIORITY_DATE_CODE
- At line number 25 before going inside WHILE loop we are checking if that index position exists or not, if it doesn’t, we won’t enter loop.
- At line number 28 we are getting value from DBI PER_SENDT_F_V3_SENIORITY_DATE_CODE to check if it matches to adjusted service date “ADJ_SERVICE_DATE”. If it does we need to read the adjusted service date or move on.
- At line number 30 we are reading the value from DBI PER_SENDT_F_V3_SENIORITY_DATE for further calculation.
- At line number 38 we are incrementing the index position by using function NEXT.
3.4. More About Array Indexes
Hope things are getting better….. Please also understand that index type of an array is not always number, text could also be index of an array. Please also note some other points related to array index.
- The index types are either text or number.
- Text indexes are upper case unique.
- Gaps in index value sequences are permitted.
- Number indexes are truncated to remove any fractional part.
- You may iterate an array in an index either forwards or backward.
3.4.1. Array Data Types
In the example above we have seen DBI of data type DATE with index NUMBER. Likewise we have few other data types for array as mentioned below.
| Array Type | What does it mean |
| NUMBER_NUMBER | Number value with number index |
| NUMBER_TEXT | Number value with text index |
| DATE_NUMBER | Date value with number index |
| DATE_TEXT | Date value with text index |
| TEXT_NUMBER | Text value with number index |
| TEXT_TEXT | Text value with text index |
Since we have got the concept of Array type DBI, let’s also understand where else we can use arrays in oracle fusion fast formulas. If you need to know contexts supported by any fast formula you can run the query available in this article » Oracle Cloud Reusable Queries.
4. Different Types of Arrays in Oracle Fusion Fast Formulas
Since we have got the concept of Array type DBI, let’s also understand where else we can use arrays in oracle fusion fast formulas.
Arrays can also be used as
- Data Base Item (DBI) – have already explained above
- Input Values
- Variable
- Return Values
4.1. Use of Arrays in Input Values
Like database items arrays can be used as input vales also but input values supported by any fast formulas purely depends on the type of fast formula you are working on. Global Absence Accrual Matrix Formula is one example where array is used as input value. Below are the two array type input values used by Global Absence Accrual Matrix Formula.
| Variable Name | Data Type | Description |
| IV_EVENT_DATES | Date_Number | Array of dates returned by the Accrual Event formula. |
| IV_ACCRUAL_VALUES | Number_Number | Array of accrual values fetched from the accrual matrix as of the dates contained in the accrual event dates array. |
4.1.1 Method to declare array type input values
Like we have the different syntax for declaring data base item we also have different syntax for input value as arrays. Below is the example to declare input value IV_EVENT_DATES inside Global Absence Accrual Matrix Formula.
DEFAULT FOR IV_EVENT_DATES IS EMPTY_DATE_NUMBER
4.2. Use of Arrays in Return Values
Similar to input values some fast formulas support array type return values. Global Absence Accrual Event fast formula is one example, which return array type return values.
| Variable Name | Data Type | Description |
| IV_EVENT_DATES | Date_Number | Array of dates that feeds into the accrual matrix which can be used for band change pro-ration. Corresponding accrual values derived from the matrix also feeds into the matrix formula |
4.3. Use of Arrays as variables
You can also have array as your local variable, it is just that you need to mentioned the index when you want to capture the record. For example you can syntax like below to store
L_LOCAL_ARRAY[1] = ‘Hello World’
5. Arrays in Oracle Fusion fast Formulas
Hope you enjoyed reading this article and was able to gain some knowledge about arrays in oracle fusion fast formula. Please give me your feedback and always post your followup question here » oracle fusion forum.
