Global Absence Accrual Event Fast Formula
Global Absence Accrual Event fast formula is one of the special fast formulas that oracle delivers; this is special because it can rescue you from many difficult situations, mostly if the requirement is related to proration. Your accrual proration can be dependent on multiple assignment attributes such as Job change, Grade Change, FTE value etc. Global Absence Accrual Event fast formula helps to capture all those assignment changes that you can later use to prorate your accrual. Global Absence Accrual Event fast formula always works in conjunction with Global Absence Accrual Matrix formula. This formula extensively usages concept of array, so if you are not familiar with that please read this article » Learn Arrays in Oracle Fusion Fast Formulas With Example
Another specialty of this formula is that you don’t need to link it with your accrual plan configuration anywhere. Name of the fast formula should be exactly same as your accrual plan name.
Learn Global Absence Accrual Event Formula With Example
Let’s try to understand this formula with a business requirement, which is as follows
- Employees receive different accruals based on their jobs, so if the job of any employee is changing during the calendar year, employee should receive prorated accrual.
- Accrual is also dependent on the FTE value of the employee. If the FTE value is also changing in a plan year, employee’s accrual should be prorated.
As we have requirement in place and we understand that accrual needs to be prorated whenever there is a change in JOB or FTE. Now the biggest challenge is how to know if there is a change in assignment record of the employee. PER_HIST_ASG_EFFECTIVE_START_DATE is one DBI item that holds start date of all assignment changes of an employee. We will make use of this DBI item in our Global Absence Accrual Event formula, explained next in this article.
Write Global Absence Accrual Event Formula
To write fast formula navigate to Setup And Maintenance » Task Panel » Search » Fast Formulas
From the search result section, click on Create (+) icon, Create Fast Formula window will pop up. Enter details as follows and then click on Continue.
| Formula Name | Annual Vacation |
| Type | Global Absence Accrual Event |
| Description | Accrual Event formula for accrual plan Annual Vacation |
| Legislative Data Group | |
| Effective Start Date | 1/1/1951 |
You are on Create Fast Formula : Annual vacation page. Enter below code in the Formula Text section.
/******************************************************************************
FORMULA NAME: Annual Vacation
FORMULA TYPE: Global Absence Accrual Event
DESCRIPTION: To capture assignment change dates for proration
Change History:
Name Date Comments
-------------------------------------------------------------------------------
Avinash Patel 14-03-2021 Initial Version
-------------------------------------------------------------------------------
********************************************************************************/
DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ACCRUALPERIODSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_PLANENROLLMENTSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_PLANENROLLMENTENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_EFFECTIVE_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR PER_ASG_EFFECTIVE_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT_DATA_VALUE FOR PER_HIST_ASG_EFFECTIVE_START_DATE IS '4712/12/31 00:00:00' (date)
INPUTS ARE
IV_ACCRUALPERIODSTARTDATE (date),
IV_ACCRUALPERIODENDDATE (date),
IV_PLANENROLLMENTSTARTDATE (date),
IV_PLANENROLLMENTENDDATE (date)
i=1
m=2
mn = 1
l_start_date = GREATEST(IV_ACCRUALPERIODSTARTDATE, IV_PLANENROLLMENTSTARTDATE)
l_end_date = LEAST (IV_PLANENROLLMENTENDDATE, IV_ACCRUALPERIODENDDATE)
IV_EVENT_DATES[1] = l_start_date
l_log = ESS_LOG_WRITE ('Start date is : '||TO_TEXT(l_start_date))
l_log = ESS_LOG_WRITE ('End date is : '||TO_TEXT(l_end_date))
l_log = ESS_LOG_WRITE ('First entry is : '||TO_TEXT(IV_EVENT_DATES[1]))
WHILE PER_HIST_ASG_EFFECTIVE_START_DATE.EXISTS(i)
LOOP
(
IF (PER_HIST_ASG_EFFECTIVE_START_DATE[i] > l_start_date AND PER_HIST_ASG_EFFECTIVE_START_DATE[i] < l_end_date AND PER_HIST_ASG_EFFECTIVE_START_DATE[i] != IV_EVENT_DATES[mn])
THEN
(
IV_EVENT_DATES[m] = PER_HIST_ASG_EFFECTIVE_START_DATE[i]
l_log = ESS_LOG_WRITE ('Next entry is : '||TO_TEXT(IV_EVENT_DATES[m]))
m = m + 1
mn = mn + 1
)
i=i+1
)
IV_EVENT_DATES[m] = l_end_date
l_log = ESS_LOG_WRITE ('Last entry is : '||TO_TEXT(IV_EVENT_DATES[m]))
RETURN IV_EVENT_DATES
Once you enter formula code, click on Submit button and then click on Compile button. After a while click on Refresh button to check compilation status, it should be successful without any error.
Lets understand the formula
- IV_EVENT_DATES is a return variable (array of Date_Number), which will be used by Global Absence Accrual Matrix formula to handle proration. In this return variable we try to capture all assignment changes between calendar start date and calendar end date.
- We get all assignment changes of employee from DBI PER_HIST_ASG_EFFECTIVE_START_DATE but we are only concerned about changes happened between calendar start date and end date (but an employee could be enrolled after calendar start date and could be terminated prior to calendar end date), so we are checking this using below two lines.
- l_start_date = GREATEST(IV_ACCRUALPERIODSTARTDATE, IV_PLANENROLLMENTSTARTDATE)
- l_end_date = LEAST (IV_PLANENROLLMENTENDDATE, IV_ACCRUALPERIODENDDATE)
- Using the while loop (line 39 to 51) we are going through each entry of PER_HIST_ASG_EFFECTIVE_START_DATE and recording them to our return variable IV_EVENT_DATES only if they are between l_start_date and l_end_date.
- First entry for IV_EVENT_DATES will always be l_start_date (line number 32 : IV_EVENT_DATES[1] = l_start_date)
- Similarly last entry of IV_EVENT_DATES will always be l_end_date (line number 53 : IV_EVENT_DATES[m] = l_end_date)
Note : You can read this article to know how to use accrual event formula accrual event matrix formula » Learn Global Absence Accrual Matrix Formula.
Learn More About Accrual Event Formula
Although the code shared above would work in all kind of business requirements where ever there is a need to prorate accrual, but its good to know what all other context, input values and output values this formula supports.
Context Supported by Global Absence Accrual Event formula
| Context Name | Data Type |
| ABSENCE_ENTRY_ID | Number |
| ABSENCE_TYPE_ID | Number |
| ACCRUAL_PLAN_ID | Number |
| DATE_EARNED | Date |
| EFFECTIVE_DATE | Date |
| END_DATE | Date |
| ENTERPRISE_ID | Number |
| HR_ASSIGNMENT_ID | Number |
| HR_RELATIONSHIP_ID | Number |
| HR_TERM_ID | Number |
| JOB_ID | Number |
| LEGAL_EMPLOYER_ID | Number |
| LEGISLATIVE_DATA_GROUP_ID | Number |
| ORGANIZATION_ID | Number |
| PAYROLL_ASSIGNMENT_ID | Number |
| PAYROLL_ID | Number |
| PAYROLL_RELATIONSHIP_ID | Number |
| PAYROLL_TERM_ID | Number |
| PERSON_ID | Number |
| START_DATE | Date |
Input Values Support by Global Absence Accrual Event formula
| Variable Name | Data Type | Description |
| IV_CARRYOVER | Date | Carryover limit received from accrual matrix |
| IV_CEILING | Date | Ceiling limit received from the accrual matrix |
| IV_ACCRUALPERIODSTARTDATE | Date | Start date of accrual period |
| IV_ACCRUALPERIODENDDATE | Date | End date of accrual period |
| IV_CALEDARSTARTDATE | Date | Start date of accrual calendar year |
| IV_CALEDARENDDATE | Date | End date of accrual calendar year |
| IV_PLANENROLLMENTSTARTDATE | Date | Start date of enrollment |
| IV_PLANENROLLMENTENDDATE | Date | End date of enrollment |
| IV_BAND_CHG_DT1* | Date | If the accrual matrix band definition consists of grades or derived factors length of service or age, then this input value fetches the date on which worker has had a change in any of these factors within the period. |
| IV_BAND_CHG_BEFVAL1* | Number | Accrual value as per the matrix before IV_BAND_CHG_DT1 |
| IV_BAND_CHG_AFTVAL1* | Number | Accrual value as per the matrix after IV_BAND_CHG_DT1 |
| IV_ACCRUAL_CEILING | Number | Annual accrual limit from the accrual matrix |
Return Variable supported by Global Absence Accrual Event formula
| 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 |
Accrual Event Formula Questions and Clarifications
In this article, we have learned about the global absence accrual event formula and seen how it can be practically utilized to address complex business requirements. If you encounter any issues while compiling and using formulas in absence configuration, please refer to this article on How to Debug Oracle Absence Management Fast Formulas for guidance on troubleshooting.
We hope you have found this article helpful. If you have any further questions or require clarification, please feel free to post them on hcmfolks.com, an open community of Oracle HCM professionals.
