Global Absence Accrual Matrix
In this article we will talk about Global Absence Accrual Matrix fast formula and see how this formula can be used to calculate absence accrual and also prorate the same if there are any changes in the assignment record. Before we process further lets understand what is the business requirement.
Learn With the Example
Before we write our formula lets understand what is the business requirement
- If workers job contains ‘Sales’ or ‘Director’, they should receive 3 days of accrual every month.
- If workers job doesn’t contains ‘Sales’ or ‘Director’ and workers Normal Working hours is less than 37.5 hours per week, they should receive 2 days of accrual every month.
- If workers job doesn’t contains ‘Sales’ or ‘Director’ and workers Normal Working hours is equal to or more than 37.5 hours per week, they should receive 2.5 days of accrual every month.
- If worker has applied for any unpaid leave, they wont be receiving accrual for that period. Accrual should be deducted and adjusted in the plan.
- If there is any change in the workers job, normal working hours or FTE, they should receive prorated accruals.
Write Global Absence Accrual Matrix 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 | AP_ANNUAL_LEAVE_ACCRUAL_MATRIX_FF |
| Type | Global Absence Accrual Matrix |
| Description | Accrual Matrix formula for Annual Vacation plan |
| Legislative Data Group | |
| Effective Start Date | 1/1/1951 |
You are on Create Fast Formula : AP_ANNUAL_LEAVE_ACCRUAL_MATRIX_FF page. Enter below code in the Formula Text section.
/******************************************************************************
FORMULA NAME: AP_ANNUAL_LEAVE_ACCRUAL_MATRIX_FF
FORMULA TYPE: Global Absence Accrual Matrix Formula
DESCRIPTION: This formula returns the pro-rated accrual value if there is any change
in the band and deducts accrual if employee has applied for unpaid leave.
Change History:
Name Date Comments
--------------------------------------------------------------------------------
Avinash Patel 22-03-2021 Initial Version
--------------------------------------------------------------------------------
********************************************************************************/
DEFAULT FOR IV_CALEDARSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_CALEDARENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_PLANENROLLMENTSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ACCRUALPERIODSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_EVENT_DATES IS EMPTY_DATE_NUMBER
DEFAULT FOR PER_ASG_FTE_VALUE IS 0.00
DEFAULT FOR PER_ASG_NORMAL_HOURS IS 0.00
DEFAULT FOR PER_ASG_JOB_NAME IS 'NA'
INPUTS ARE
IV_EVENT_DATES,
IV_CALEDARSTARTDATE (date),
IV_CALEDARENDDATE (date),
IV_PLANENROLLMENTSTARTDATE,
IV_ACCRUALPERIODSTARTDATE,
IV_ACCRUALPERIODENDDATE
i = 1
j = 2
l_accrual_total = 0
l_unpaid_leave = 0
l_leave_adjust = 0
l_asg_id = GET_CONTEXT(HR_ASSIGNMENT_ID, 0)
l_person_id = GET_CONTEXT(PERSON_ID, 0)
l_array_count = IV_EVENT_DATES.COUNT
l_total_days = DAYS_BETWEEN(IV_ACCRUALPERIODENDDATE, IV_ACCRUALPERIODSTARTDATE) + 1
WHILE IV_EVENT_DATES.EXISTS(j)
LOOP
(
IF (j = l_array_count) THEN
l_add_days = 1
Else
l_add_days = 0
change_contexts (EFFECTIVE_DATE = IV_EVENT_DATES[i], HR_ASSIGNMENT_ID = l_asg_id )
(
l_log = ESS_LOG_WRITE('Employee job is : '||PER_ASG_JOB_NAME)
IF (PER_ASG_JOB_NAME LIKE '%Sales%' OR PER_ASG_JOB_NAME LIKE '%Director%') THEN
accrual = 3
ELSE
(
IF (PER_ASG_NORMAL_HOURS < 37.5) THEN
accrual = 2.0
ELSE
accrual = 2.5
)
l_accrual_total = l_accrual_total + (DAYS_BETWEEN(IV_EVENT_DATES[j], IV_EVENT_DATES[i]) + l_add_days) * PER_ASG_FTE_VALUE * accrual
i = i+1
j = j+1
)
)
accrual = l_accrual_total / l_total_days
/* to check unpaid leaves - this is just prototype */
CHANGE_CONTEXTS(PERSON_ID = l_person_id)
(
l_unpaid_leave = GET_ABSENCE_DAYS_PER_TYPE('Unpaid Leave',IV_ACCRUALPERIODSTARTDATE,IV_ACCRUALPERIODENDDATE) /* to get unpaid leaves */
l_leave_adjust = ROUND(l_unpaid_leave*accrual/l_total_days, 2) * -1
adjustmentvalues[1] = l_leave_adjust
adjustmentdates[1] = IV_ACCRUALPERIODENDDATE
adjustmenttypes[1] = 'Unpaid Leave Adjustment'
)
accrual = ROUND (accrual, 2)
l_log = ESS_LOG_WRITE('Avinash - Accrual : '||to_char(accrual))
l_log = ESS_LOG_WRITE('Avinash - Total Days : '||to_char(l_total_days))
l_log = ESS_LOG_WRITE('Avinash - Unpaid Leave : '||to_char(l_unpaid_leave))
l_log = ESS_LOG_WRITE('Avinash - Adjusted Leave : '||to_char(l_leave_adjust))
l_log = ESS_LOG_WRITE('Avinash - Adjustment Date is : '||to_char(IV_ACCRUALPERIODENDDATE,'DD-MON-YYYY'))
l_log = ESS_LOG_WRITE('Avinash - Period Start Date is : '||to_char(IV_ACCRUALPERIODSTARTDATE,'DD-MON-YYYY'))
Return accrual, adjustmentvalues, adjustmentdates, adjustmenttypes
Lets understand the formula
- In line number 52, we are checking employees Job. If it is either ‘%Sales%’ OR ‘%Director%’ then we are awarding 3 days of accrual.
- In line number 56, we are checking employees normal hours and comparing it with 37.5 to allocate accrual accordingly.
- In line number 62, we are getting prorated accrual if there is any change in Job, FTE or working hours. This is possible due to Accrual Event Formula we configured here » Learn Global Absence Accrual Event Fast Formula
- In line number 73, we are getting any Unpaid Leave applied by employee between the accrual period.
- Line number 74 is helping to get amount that we need to adjust in accrual due to Unpaid Leave.
- This formula is extensively using array concept, if you are not familiar with that, please real this article » Learn Arrays in Oracle Fusion Fast Formulas With Example
How to use Accrual Matrix Formula
Once your formula is ready, you will have to link it with an accrual plan. For the same, you can navigate to My Client Groups » Absences » Absence Plans » here search for your plan (or create a new plan) » Click on the Accruals tab and scroll below to Accrual Matrix section to attach your formula.
You can also follow this article to learn to configure accrual plan » Fusion Absence Management : Explained with examples
Please Note : To test out this formula you will also need to be ready with following setups
- An Accrual Plan and Absence Type – You may follow this article for more information. » Fusion Absence Management : Explained with examples
- Accrual event formula same as your accrual plan name as described in this article » Learn Global Absence Accrual Event Fast Formula
- Optionally, you can have another Absence Type with name “Unpaid Leave”, to test unpaid leave adjustment.
Learn More About Accrual Matrix Formula
Context Supported by Accrual Matrix 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 Supported by Accrual Matrix Formula
| Variable Name | Data Type | Description |
| IV_ACCRUAL | Number | Accrual value received from the accrual matrix |
| IV_CARRYOVER | Number | Carryover limit received from accrual matrix |
| IV_CEILING | Number | 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_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. |
| IV_ACCRUAL_CEILING | Number | Annual accrual limit from the accrual matrix |
Return Variables Supported by Accrual Matrix Formula
| Variable Name | Data Type | Description |
| accrual | Number | Amount of accrual that the worker accrues in the specific period. |
| ceiling | Number | Maximum time that a worker can accrue |
| carryover | Number | Maximum unused time that a worker can transfer to the next accrual term |
| adjustmentvalues | Number_Number | Array of balance adjustments that when returned by the formula is applied against the accrual plan enrollment. |
| adjustmentdates | Date_Number | Array of dates associated with the array of balance adjustment values to specify the dates on which each of the balance adjustments need to be applied |
| adjustmenttypes | Text_Number | Array of adjustment reasons used for the balance adjustments |
| absvalues | Number_Number | Array of absence accrual usages that can be applied against an accrual plan enrollment. Useful for cases where the absence entry itself is not captured in Global Absence Management, but rather via other modules (such as payroll element entries) or even third party systems and the absence deduction against the accrual plan needs to happen in Fusion. When using this, it should be noted that absence entries should not be made for the same dates. |
| absdates | Date_Number | Array of dates associated with the absence accrual usages on which the deduction is applied. |
| accrualCeiling | Number | Maximum time that a worker can accrue |
Accrual Matrix Formula Questions and Clarifications
In this article, we have learned about the global absence accrual matrix 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.
