Debugging Oracle Fast Formula.
Writing just a fast formula is not enough, first it should get compiled successfully without any error and then it should also work as expected and deliver desired result. Since you have stumbled upon this article, we assume we have already written a nice fast formula and now struggling to fix the issue as its giving crazy result. Don’t worry, you are in safe hands now. Fortunately there are several ways to debug a formula, we will go them one by one..
Note : If you want to learn functional side of oracle fusion absence management, we recommend you to go through this article » Fusion Absence Management : Explained with examples
First Method : Troubleshoot using ESS_LOG_WRITE() function
Using this function you can write/display anything in the log file, which will help you to debug the formula. Anything you want to print just put them inside braces. You can print anything, simply write Hello World, as shown below or any local variable, database item, input values etc.
l_log = ESS_LOG_WRITE (‘Hello World!!’)
Please note, you will have to use single quote not double quote. If you want to print multiple things in a single line, you can concatenate them using || double pipe symbol, as shown below.
l_log = ESS_LOG_WRITE (‘This is first statement. ‘ || ‘This is second statement.’)
Drawback of this function is that it can print only characters, so if you wish to print number you will have to convert it to character first using TO_CHAR() function, similarly, you need to convert date variable also.
l_log = ESS_LOG_WRITE(‘This will print number : ‘|| to_char(accrual))
l_log = ESS_LOG_WRITE (‘This will print date : ‘||to_char(l_start_date,’DD-MON-YYYY’))
Now lets assume we have below piece of code placed in our fast formula (Global Absence Accrual Matrix) to check log message.
You can write similar message in any absence fast formula you are working on like Global Absence Accrual Formula, Global Absence Carryover, Global Absence Partial Period etc. You just need to make sure that formula is attached with absence plan and/or absence type. You will be able to see log message only if that formula is linked with absence plan or absence type.
How to see the log message printed by fast formula?
Now the biggest question is how to see those messages printed by formula. Okay, to get that navigate to My Client Group » Absence » Task Panel » Schedule and Monitor Absence Processes. You will be on Submit Jobs page. On this screen click on run icon against process “Calculate Accruals and Balances“.
On this screen, enter Effective Date, make sure you have checked “Include trace statements in audit log“, you have also selected Person and also selected Absence Plan.
Once you enter all these details, click on Submit button. Note down the process name. Scroll below on the same screen, under process name you will find an entry for “Calculate Accruals and Balances”. Once the status is changed to “Succeeded”, click on the “View Log” icon, as shown below.
Once file is downloaded, open that file using Notepad++ and scroll below. You will find your message there as shown below.
Second Method : Using Global Absence Entry Validation Formula
In the first method we saw how to troubleshoot fast formula using ESS_LOG_WRITE() method and check the log file. What if I say, you don’t event need to check the log file and you can see message directly on UI. Yes this is also possible. In the second approach we will see how to display message directly on screen.
To achieve this, we have to leverage functionality of fast formula type “Global Absence Entry Validation”. This formula has two return variables VALID and ERROR_MESSAGE. Now to see the message directly on UI, we will set the return variable VALID to ‘N’, and we will assign all our message to return variable ERROR_MESSAGE.
You can see how this is used in below fast formula.
/****************************************************************************** FORMULA NAME: AVI_GBL_ABSENCE_VALIDATION_FF FORMULA TYPE: Global Absence Entry Validation DESCRIPTION: This is to demonstrate how to debug fast formula. Change History: Name Date Comments -------------------------------------------------------------------------------------------- iavinash.com 01-01-2001 Initial Version -------------------------------------------------------------------------------------------- ********************************************************************************/ DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date) DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date) DEFAULT_DATA_VALUE for ANC_PER_ABS_ENTRS_ABSENCE_ENTRY_ID_ARR is 0 DEFAULT FOR ANC_ABS_ENTRS_ABSENCE_TYPE_ID is 0 DEFAULT FOR ANC_ABS_TYP_NAME IS ' ' DEFAULT FOR PER_ASG_NORMAL_HOURS is 0 DEFAULT FOR PER_ASG_PERSON_NUMBER is ' ' INPUTS ARE IV_END_DATE (date), IV_START_DATE (date) l_debug = 'Person number is : ' ||PER_ASG_PERSON_NUMBER l_debug = l_debug || ' and his normal working hours is : ' ||to_char(PER_ASG_NORMAL_HOURS) VALID = 'N' ERROR_MESSAGE = l_debug RETURN VALID, ERROR_MESSAGE
You will need to attach this fast formula with absence type, for the same open your absence type and click on “Type Attributes” tab. On this screen you will see a filed called “Validation Formula”. Just attach your fast formula here and save the changes.
Now its time to see it work.
To see the message either you can login as an employee account and schedule the same absence type where this formula is attached, or as an HR specialist also navigate to Manage Absence Record and try to submit absence, you will see your message as follows.
How to troubleshoot absence fast formulas?
Now as you have seen both the approaches it is basically up to you, which one you prefer. Although second option is easy and convenient but it has its own limitation. It would be mostly useful to print DBI items and context values. On the other hand first approach is useful in all scenarios. You do not need to write separate formula, you can directly write all your log messages inside the same formula you are working and then just run the process “Calculate Accruals and Balances” to see all those message together from different fast formulas. I personally prefer first approach of using ESS_LOG_WRITE().
How to debug any fast formula like a pro
In this article, we have explored various techniques for effectively debugging fast formulas. One such technique is using the ESS_LOG_WRITE method, which allows you to print log messages after running a process, making it a valuable tool for specific types of fast formulas. However, it may not be applicable to all fast formula types. Not to worry, as we have also provided an alternative approach that can be employed to debug any type of fast formula with ease. Prepare to enhance your debugging skills with our comprehensive guide on » How to Debug Any Fast Formula Like a Pro
If you have any other approaches that you follow, we encourage you to share them with us. Should you have any questions, please visit our hcmfolks.com and post your query and join community of other technical folks.