Friday, 3 November 2017

FUSION HCM : FAST FORMULAS FAQ'S

What are Fast Formulas?

Fast formulas are generic expressions of calculations or comparisons that you want to repeat with different input variables. Fast Formula is a way to customize the existing functionality in Oracle Fusion Payroll.

Fast formulas are used to:

Calculate payrolls
Define the rules for paid time off accrual plans
Define custom calculations for benefits administration
Validate element inputs or user-defined tables
Edit the rules for object group population for elements or people
Calculate absence duration
Define custom configuration for compensation

What is the scope of the support of custom fast formulas?

Fast formulas are considered a customization to the seeded application. Oracle support services will assist with troubleshooting formula issues, but Oracle Support Services is not responsible for writing any custom fast formula code.

Oracle Fusion Payroll allows for the use of fast formulas on the forms and processes, and if the application does not recognize the fast formulas, then further investigation from Oracle Support Services is necessary.

However, if the issue is with the specific custom formula or custom function, Oracle Support Services will provide you with some steps for you to troubleshoot your custom fast formula or function issue, or you will need to contact your technical expert onsite or Oracle consulting services for further assistance, as this is outside the scope of support.

This document will not assist with the creation of either a formula or a function, but will give you steps to follow to troubleshoot fast formulas and/or functions that you have created.

Which HCM products use fast formulas?

Oracle Fusion Payroll
Fusion HCM Extract
Oracle Fusion Advanced Benefits
Oracle Fusion Workforce Compensation

What are the seeded fast formulas and how to determine seeded ones?

Oracle Fusion Payroll delivers seeded fast formulas for legislation taxation calculation. Formulas which were created for the user-defined elements will have legislative data group populated. Seeded fast formulas have effective start date 01-JAN-0001 and their Edit field is set to not editable.

Fast formulas that were created for the user created element will have Legislation Data Group populated, Effective Start Date set to the implementation date, Edit action is enabled.

Why are the seeded formulas failing after an install or applying a patch?

You must compile all seeded formulas after an install or patch by selecting the “Submit a Process or Report” task from the Payroll Checklist work area and then running the “Compile Formula” process.


Database Items related issues :

 No DBIs are created for elements with input values of "Char"

a) run the above sql to check if the database item was created

b) If the element is multiple entry allowed then the DBI will not be generated to get the entry values.

c) There is a formula called GET_ELEMENT_ENTRY_VALUES which should provide with the functionality required to access the input values of an element from another element.  It is documented in the formula header that they can review in the Formula Editor screen - the mode to use would be mode: 2 - ENTRY_VALUE.
Note: The formula can access other element entries that are also being processed in the payroll run.


No DBIs are create for Cost Allocation Flexfield

a) Please regenerate the flexfield again and run SQL below which show the generated list of DBIs:

select * from FF_ROUTE_TO_DESCR_FLEXS
where DESCRIPTIVE_FLEXFIELD_CODE='COST';

select * from FF_USER_ENTITIES_VL
where creator_id in
  (select ROUTE_TO_DESCR_FLEXS_ID
   from FF_ROUTE_TO_DESCR_FLEXS
   where DESCRIPTIVE_FLEXFIELD_CODE='COST');

select * from FF_DATABASE_ITEMS_VL
 where USER_ENTITY_ID in
  (select USER_ENTITY_ID from FF_USER_ENTITIES_VL
    where creator_id in
     (select ROUTE_TO_DESCR_FLEXS_ID
        from FF_ROUTE_TO_DESCR_FLEXS
           where DESCRIPTIVE_FLEXFIELD_CODE='COST'));


Generate Database Item process errors: 
'A record with this combination of values already exists'

When Log into Fusion Application and Navigate to the Scheduled Processes and run the Generate Payroll Data Base Items, the following error occurs: 'A record with this combination of values already exists'.
You need to run it for the context you needed it. The issue may be it is trying to create a DBI another context, which might have been seeded and DBI already exists.


Tips for resolving Fast Formulas performance issues :

When experiencing slow performance issues in fast formulas there are a number of techniques to follow to ensure your formulas are easy to read, use, understand, and process efficiently.

Variable Names and Aliases

To improve readability, use names that are brief yet meaningful. Use aliases if the names of database items are long. Name length has no effect on performance or memory usage.

 Inputs Statements :

Use INPUTS statements rather than database items whenever possible. It speeds up the running of your payroll by eliminating the need to access the database for the input variables.

An example of inefficient formula without INPUTS statement is:

SALARY = SALARY_ANNUAL_SALARY / 12

RETURN SALARY

An example of efficient use of INPUTS statements is:

INPUTS ARE ANNUAL_SALARY

SALARY = ANNUAL_SALARY / 12

RETURN SALARY

Database Items :

Do not refer to database items until you need them. Users sometimes list at the top of a formula all the database items the formula might need, thinking this helps the formula process more quickly. However, this in fact slows processing by causing unnecessary database calls.

An example of an inefficient use of database items is:

S = SALARY

A = AGE

IF S < 20000 THEN

IF A < 20 THEN

TRAINING_ALLOWANCE = 30

ELSE

TRAINING_ALLOWANCE = 0

An example of an efficient use of database items is:

IF SALARY < 20000 THEN

IF AGE < 20 THEN

TRAINING_ALLOWANCE = 30

ELSE

TRAINING_ALLOWANCE = 0

The first example always causes a database fetch for AGE whereas the second only fetches AGE if salary is less than 20000.

Balance Dimensions :

Wherever possible, use balance dimensions for single assignments only in formulas. Multiple assignments require more calculation time, leading to slower processing time. The number of multiple assignments in a payroll is not normally high, and the presence of a small number does not lead to any significant increase in overall processing time. However, there could be a problem if you unnecessarily link balance dimensions for multiple assignments into general formulas.

Here are some of the things for enhancing the performance of Fast Formula (in no particular order):

The more elements entered for an assignment, the longer its processing time.
The longer the formula, the longer its processing time.
One element associated with a longer formula usually processes faster than two related elements each associated with short formulas.
The number of elements per assignment affects processing time more than the number of elements and formulas.
Use balance dimensions for single assignments whenever possible. (ASG_GRE vs. PER_)
Do not refer to database items until needed.
Do not default unnecessary database items.
Using an ALIAS instead of assigning a database item to a local variable is more efficient.
Input statements are up to 10x faster than using database items.
Assign date constants using DATE component instead of the TO_DATE function.
Review generated formulas and remove unnecessary or poor logic coding.
Create elements with the correct input values instead of having a separate element for each input value. As you can see from item #9 above, Input statements are up to 10x faster than referencing database items.
Assign smaller fast formulas to each of these elements that only reference necessary database items for that specific element. Allows for easier maintenance and debugging.
Formula code is converted to PLSQL. The 200+ ALIAS lines are not converted to executable code - the alias statement is there so that you can use an alternative name for a database item within Formula text. The defaulting lines are only executed if defaulting is necessary i.e. when the corresponding database item is executed, but the underlying SQL returns no rows or a null row. 

In the scenario stated above, the formula has at least 200+ variables referred to (database items etc) - this can cause a performance hit due to network traffic because all these variables are exchanged between the payroll process and the database server whether or not they get used. This is because a Formula execution is a PLSQL procedure call. The number of parameters to this process is related to the number of different variables in the formula (inputs, outputs, database items, local variables).

Formula Errors :

Types of Fast Formula compilation errors

Compilation errors display in the Manage Fast Formulas page when you compile the formula. The formula compiler returns line numbers starting at 1 from the beginning of a formula, and character positions starting at 1 from the beginning of a line in its error messages. The compiler aborts compilation when an error is encountered.

This list contains the types and descriptions of several common formula compilation errors.

Syntax Error - The formula text violates the grammatical rules for the formula language. An example is using IF1 instead of IF for an IF statement.

Incorrect Statement - Order ALIAS, DEFAULT, or INPUT statements come after other statements.

Misuse of ASSIGNMENT Statement Occurs when any of these conditions occurs:

• An ASSIGNMENT assigns a value to a database item.

• A context is assigned a value externally to a CHANGE-CONTEXTS statement.

• A non-context variable is assigned a value within a CHANGE-CONTEXTS statement.

Misuse of ALIAS Statement - An ALIAS statement may only be used for a database item.
Missing DEFAULT Statement - A database item with defaulting specified must have a DEFAULT statement.
Misuse of DEFAULT Statement - A DEFAULT statement is specified for a variable other than an input or database item.
Uninitialized Variable - The compiler detects that a variable is uninitialized when used. The compiler cannot do this in all cases. This error often occurs when you want to use a database item, but a database item is not available in the formula.
Missing Function Call - A function call is not recognized. The combination of return type, function name, and parameter types does not match any available function.
Incorrect Operator Usage - An instance of a formula operator use does not match the permitted uses of that operator. For example, the + operator has two permitted uses. The operands are both of data type NUMBER, or both of data type TEXT.
Inconsistent Data Type Usage - A formula variable is being used as if it is of more than one data type. Or a database item or context is being used with the wrong data type. For example, Variable A is assigned a NUMBER value at the start of the formula, but a TEXT value later in the formula.
EXIT Statement Not Within WHILE Loop - A condition that eventually becomes false, or an EXIT call for exiting the loop does not exist.
Misuse of Context - A variable is used as a context, or a context is used as a variable. For example, AREA1 is assigned a value as an ordinary variable, but later in the formula AREA1 used as a context in a GET_CONTEXT call.

Error: “Local value used before initialized”

a) Error can mean that a database item is not available and is being treated as a local variable.

b) Remove the quotes around the Database Items (DBIs) that you are wishing to utilize (HRT_PERSON_PREV_WORKEXP....)
c) As these DBIs are of type 'array', you must use the proper syntax to default and to use them 
    i) Use 'DEFAULT_DATA_VALUE' for the HRT_PERSON_PREV_WORKEXP.....DBIs rather than 'DEFAULT'
    ii) Using the Fast formula User Guide as an aid, select a method of looping to loop through the DBI values.

          A simple example is below:

       DEFAULT_DATA_VALUE FOR HRT_PERSON_PREV_WORKEXP_EMPLOYER_NAME IS ' '
       DEFAULT_DATA_VALUE FOR HRT_PERSON_PREV_WORKEXP_START_DATE IS '01-JAN-0001' (date)
       DEFAULT_DATA_VALUE FOR HRT_PERSON_PREV_WORKEXP_END_DATE IS '01-JAN-0001' (date)
       DEFAULT_DATA_VALUE FOR HRT_PERSON_PREV_WORKEXP_PERSON_ID IS ' '
       DEFAULT FOR S_DT1 IS '01-JAN-0001' (date)
              for
           /* 1 is the starting index for an array database item. */
               I = 1

             WHILE HRT_PERSON_PREV_WORKEXP_START_DATE.EXISTS(I) LOOP
             (
               S_DT1 = HRT_PERSON_PREV_WORKEXP_START_DATE[I] /* Do some processing with element at index I. */
               I = I + 1 /* Array database items indexes go up in steps of 1. */
              )
        ACCRUAL = 3
        RETURN ACCRUAL


Types of Fast Formula Execution Errors

Fast formula execution errors occur when a problem arises while a formula is running. The usual cause is a data problem, either in the formula or in the application database. These errors contain the formula line number where the error occurs.

This list contains the types and descriptions of several common formula execution errors.

Uninitialized Variable - Where the formula compiler cannot fully determine if a variable or context is initialized when it is used, it generates code to test if the variable is initialized. When the formula executes and the variable or context is not initialized an error is raised.

Divide by Zero - Raised when a numeric value is divided by zero.

No Data Found - Raised when a non-array type database item unexpectedly fails to return any data. If the database item can return no data then it should allow defaulting. This error is also raised from within a formula function. The cause is an error in the formula function code.

Too Many Rows - Raised when a non-array type database item unexpectedly returns more than a single row of data. The cause is an incorrect assumption made about the data being accessed. This error can also be raised from within a formula function. The cause is an error in the formula function code.

NULL Data Found - Raised when a database item unexpectedly returns a NULL data value. If the database item can return a NULL value then defaulting is allowed.

Value Exceeded Allowable Range - Raised for a variety of reasons, such as exceeding the maximum allowable length of a string.

Invalid Number - Raised when an attempt is made to convert a non numeric string to a number.

User Defined Function Error - Raised from within a formula function. The error message text is output as part of the formula error message.

External Function Call Error - A formula function returned an error, but did not provide any additional information to the formula code. The function might have output error information to the logging destination for the executing code.

Function Returned NULL Value - A formula function returned a NULL value.

Too Many Iterations - A single WHILE loop, or a combination of WHILE loops, has exceeded the maximum number of permitted iterations. The error is raised to terminate loops that could never end. This indicates a programming error within the formula.

Array Data Value Not Set - The formula attempted to access an array index that has no data value. This is an error in the formula code.

Invalid Type Parameter for WSA_EXISTS - An invalid data type was specified in the WSA_EXISTS call.

Incorrect Data Type For Stored Item - When retrieving an item using WSA_GET, the items actual data type does not match that of the stored item. This is an error within the calling formula.

Called Formula Not Found - The called formula could not be resolved when attempting to call a formula from a formula. This could be due to an error in the calling formula, or because of installation issues.

Recursive Formula Call - An attempt was made to call a formula from itself. The call could be directly or indirectly via another called formula. Recursive formula calling is not permitted.

Input Has Different Types In Called and Calling Formulas - When calling a formula from a formula, the actual formula input data type within the called formula does not match the data type specified from the calling formula.

Output Has Different Types In Called and Calling Formulas - When calling a formula from a formula, the actual formula output data type within the called formula does not match the data type specified from the calling formula.

Too Many Formula Calls - There are two many formula from formula calls. This is due to a problem with the formulas.

Error: Formula XYZ_HR_TO_PAY, line 45, database item or local variable HR_RELATIONSHIP_ID used as a context

Issue:

Need to to fetch HR DBIs for checking the "With Match 401k" Eligibility: PER_REL_DATE_START, PER_REL_ADJUSTED_SVC_DATE.

Used "Payroll Access to HR" Formula Type and wrote the following formula. But it gives the following error message : Formula XYZ_HR_TO_PAY, line 45, database item or local variable HR_RELATIONSHIP_ID used as a context.

Formula Name : XYZ_HR_To_Pay
Formula Text :
default for ForPay_REL_ADJUSTED_SVC_DATE IS '1951/01/01 12:00:00' (date)
default for ForPay_REL_DATE_START is '1951/01/01 12:00:00' (date)

default for PER_REL_ADJUSTED_SVC_DATE IS '1900/01/01 12:00:00' (date)
default for PER_REL_DATE_START is '1900/01/01 12:00:00' (date)

default for TERM_HR_RELATIONSHIP_ID is 0
default for HR_RELATIONSHIP_ID is 0

INPUTS ARE hr_assg_id (number)

l_HR_RELATIONSHIP_ID = 0
l_TERM_HR_RELATIONSHIP_ID = 0

CHANGE_CONTEXTS (HR_ASSIGNMENT_ID=hr_assg_id)
l_TERM_HR_RELATIONSHIP_ID = TERM_HR_RELATIONSHIP_ID
l_HR_RELATIONSHIP_ID = HR_RELATIONSHIP_ID

CHANGE_CONTEXTS (HR_ASSIGNMENT_ID=hr_assg_id, HR_RELATIONSHIP_ID = l_HR_RELATIONSHIP_ID)
ForPay_PER_REL_DATE_START = PER_REL_DATE_START

RETURN ForPay_REL_DATE_START
  

Solution:

The formula running at assignment level and the PAYROLL_TERM_ID context is set.


1. Change the calculator formula as follows:

At the start add:

DEFAULT FOR TERM_HR_RELATIONSHIP_ID IS -1

/*
* Only if you need HR_TERM_ID database items in Payroll Access To HR
formula.
*/
DEFAULT FOR TERM_HR_TERM_ID IS -1
  

Change the call to the Payroll Access To HR formula as follows:


CALL_FORMULA
('XYZ_HR_To_Pay'
,TERM_HR_RELATIONSHIP_ID > 'HR_RELATIONSHIP_ID'
/* Only if you need HR_TERM_ID database items in Boz_HR_To_Pay. */
,TERM_HR_TERM_ID         > 'HR_TERM_ID'
/* Only if you need HR_ASSIGNMENT_ID database items in Boz_HR_To_Pay. */
,ASG_HR_ASG_ID           > 'HR_ASSIGNMENT_ID'
,ForPay_REL_DATE_START < 'ForPay_REL_DATE_START'
                         default '1901/01/01 12:00:00' (date)
,ForPay_REL_ADJUSTED_SVC_DATE  < 'ForPay_PER_REL_ADJUSTED_SVC_DATE'
                                 default '1901/01/01 12:00:00' (date)
)
  

2. The Payroll Access To HR formula should be as follows because the contexts will get set from the CALL_FORMULA call in the parent formula so no need for CHANGE_CONTEXTS:

default for PER_REL_ADJUSTED_SVC_DATE IS '1900/01/01 12:00:00' (date)
default for PER_REL_DATE_START is '1900/01/01 12:00:00' (date)

ForPay_REL_DATE_START = PER_REL_DATE_START
ForPay_REL_ADJUSTED_SVC_DATE = PER_REL_ADJUSTED_SVC_DATE

RETURN ForPay_REL_DATE_START, ForPay_REL_ADJUSTED_SVC_DATE
  

a) Legal Employer Level Seniority Date PER_ASG_REL_ADJUSTED_SVC_DATE returns a seniority date in a Legal Entity (LE) relationship seniority date.
b) Enterprise Seniority Date PAYROLL_INTERFACE_ORIGINAL_DATE_OF_HIRE  returns you first original date of hire irrespective of LE. Ensure necessary contexts are set before use.

PAYROLL_INTERFACE_ORIGINAL_DATE_OF_HIRE also uses PERSON_ID context and this is set in payroll formulas so should be able to use without the Payroll Access to HR formula.

For formulas running at term level, extra work would need to be done to be able to set the HR_ASSIGNMENT_ID contexts in the Payroll Access To HR formula.

For formulas running at relationship level, extra work would need to be done to be able to set the HR_ASSIGNMENT_ID and HR_TERM_ID contexts in the Payroll Access To HR formula.


Error: Formula TD US SECOND SHIFT HOLIDAY_EARN, line 432, no data returned.(3=GET_TABLE_VALUE) 

Issue:

The formula is being called by this call in formula TD_US_SECOND_SHIFT_HOLIDAY_EARN_ff:

l_table_rate = get_table_value(             'SECOND_SHIFT_RATES',
                                           'SHIFT_RATE',
                                           l_location_name,
                                           PAY_EARN_PERIOD_START)


GET_TABLE_VALUE function call appears to be returning multiple values, hence the the error.

Solution:

Ensure that:

    (1) Table Name must be unique,
    (2) Row Title must be Unique,
    (3) Column Name must be Unique, and
    (4) Sequence Numbers of Rows must be Unique


1. There are 2 formats for the function call:

GET_TABLE_VALUE(table_name, column_name, row_value [,default_value])
GET_TABLE_VALUE(table_name, column_name, row_value, effective date)

2. You cannot provide a null column_name or row_value to GET_TABLE_VALUE.

3. When defining UDTs, the table_name must be unique

4. When defining UDTs, the column_name must be unique within a specific table

5. When defining UDTs, the row_name must be unique within a specific table

6. The row name/value and the column name must be unique for the given table - they do not need to be unique across all UDTs.

7. The User Table must be visible in the legislative data group of the payroll process. This means that it must exist at enterprise level or in the same legislative data group.

8. GET_TABLE_VALUE matching matches against internal (base table) values. These are non-modifiable values from when the user table is constructed. The UI values are translation table translatable values. These are initially the same as the internal values but can be updated and in different languages - this is why internal values must be used.

So it is possible that the translated value on the UI and the internal base value don't match. BI queries could be run by the customer against FF_USER_TABLES,  FF_USER_COLUMNS and FF_USER_ROWS_F to get the correct values.

GET_TABLE_VALUE does upper case matching with UPPER function i.e. 'abc', 'Abc', and 'ABC' are the same thing.

9. The internal user tables names are unique i.e. it should be impossible to select 2 user tables called 'ABC' within the same legislative data group. Also, for each user table the internal user column and user row names are unique.

It is possible that the UI does not do translated name uniqueness checking so that the UI could show duplicate names.

10. The effective date ranges of the user rows and user column instances must cover the effective date of formula execution.


Error: ‘Context Payroll_Assignment_ID was not set’

If you use the dimension _ASG_RUN in your formula (which uses the context PAYROLL_ASSIGNMENT_ID) and your formula gets executed at the payroll relationship level then your formula will error out because this context is not automatically set at this level and there is no way the balance call will successfully complete.

Use the RUN_INCLUDED_PAYROLL_ASGS DBI to resolve the issue. That is in the Calculator formula so that a call to CHANGE_CONTEXTS sets the PAYROLL_ASSIGNMENT_ID context values to derive HR_ASSIGNMENT_ID and pass that into a called formula.


Error: 'Contexts HR_ASSIGNMENT_ID was not set' for Element Input Validation type Fast Formula

Issue: Custom Fast Formula of type Element Input Validation is attached to Elements at => Element Details -> Default Entry Values and Validation -> Calculation Formula.

Application does not set contexts.

Adding the Element to a test employee.
Put some value in the Amount field and Save.
The application throws an error and the entry is not saved.

The application is not setting the Contexts HR_ASSIGNMENT_ID, DATE_EARNED which it should ideally.
The Fast Formula user guide says these contexts are available to this type of Formula.

Solution: The element is setup at Term/Relationship level. HR_ASSIGNMENT_ID context will not get set because there can be multiple assignments per relationship / term. Create element at Assignment level and then create the calculation formula.  Attach element to employees.


Error: Context PAYROLL_TERM_ID was not set when used at line 65 of formula XYZ_CHG_DEDN_CALCULATOR

Element is defined at the Payroll relationship level.  Under a payroll relationship level there could be one or more terms.  You can not access a DBI that uses the context PAYROLL_TERM_ID at this level because naturally the context would not automatically be set.

In this situation the issue was resolved by using another DBI RUN_INCLUDED_PAYROLL_ASGS.


Error: Array data value missing. (3=RUN_INCLUDED_PAYROLL_ASGS) (4=1)

This line of the formula is in error:

CHANGE_CONTEXTS(PAYROLL_ASSIGNMENT_ID = RUN_INCLUDED_PAYROLL_ASGS[1])

The error is that the RUN_INCLUDED_PAYROLL_ASGS database item is returning nothing so there is no value at RUN_INCLUDED_PAYROLL_ASGS index 1 hence the
error when accessing RUN_INCLUDED_PAYROLL_ASGS[1].

Looking at the formula, the preceding code block:

CHANGE_CONTEXTS (DIR_CARD_COMP_ID = l_comp_id)
(
  l_index = ALL_ASGS_LINK_TO_DEDUCTION_COMPONENT.first(-1)
  if (ALL_ASGS_LINK_TO_DEDUCTION_COMPONENT.exists(l_index) ) then
  (
....

  )
  else
  (
    l_log = PAY_INTERNAL_LOG_WRITE('(VAC_ACCRUAL_LIAB_CAL) Error.. Assignment
id is missing')
    l_error = PAY_LOG_ERROR('PAY:PAY_ASG_ID_MISSING')
  )
)
  
The log file shows the formula has entered the first part of the if-statement because there are messages from there like the following in the log file:

(VAC_ACCRUAL_LIAB_CAL) : l_accrual_unit 50
(VAC_ACCRUAL_LIAB_CAL) : l_accrual_uom H_DECIMAL3
(VAC_ACCRUAL_LIAB_CAL) Initialize the call for GET_PAY_SALARY_BASIS
(VAC_ACCRUAL_LIAB_CAL) : PAYROLL_ASSIGNMENT_ID 300000002161842
  
Alter the formula and include

l_assignment_id = ASG_HR_ASG_ID
l_hire_date = ACP_HIRE_DATE
  
into the earlier code code block where PAYROLL_ASSIGNMENT_ID is set.


How does formula caching effect formula execution?

a) If a formula's (non-comment) code is changed and it is compiled the code that is executed for the formula is changed.

b) For certain processes (e.g. Payroll run) the formula implementation (C) does cache the formula executable code for performance. If the formula were
changed and compiled in the middle of such a process then the changes would not be seen by the executing process. However, this caching only lasts for
the life-time of the process (the cache is within the process memory, not in an external sub-system e.g. JVM, database, web server).

c) Other processes e.g. Extracts have formula execution implemented differently (PLSQL) do some caching but the changed executable part would be
used if the formula were changed and compiled in the middle of the process. In this case the process formula execution would raise errors (unless the
changes were done in a restricted way). This information is cached as PLSQL package global values, but correctly written consuming code should clear the
caches at the start of execution.

0 comments:

Post a Comment