Breaking Down a PH Vacation Leave Accrual Matrix Formula — Section by Section
Before jumping into the code, quick context. In Oracle Absence Management there are different formula types. This one is Absence Accrual Matrix Formula. What that means is — the absence plan already has a matrix table configured (like a grid with bands based on years of service or grade etc), and the matrix engine calculates an accrual value and passes it to this formula as IV_ACCRUAL.
The formula's job is to either accept that value or override it. In our case we're completely ignoring IV_ACCRUAL and doing our own calculation. That's the whole point of a matrix formula — you get a hook to intercept and customize.
This specific formula implements Philippine vacation leave rules:
- 0 to 6 months — nothing (probation)
- 6 to 12 months — 1.25 days per month
- After 12 months — one-time 15 day credit in January, then nothing after
OK let's get into it.
This is just a comment block but don't skip it. It tells you the formula type (which determines available inputs and returns), the business rules, the Oracle doc reference, and the processing frequency requirement. The note about monthly frequency is important — the formula has a guard for this later.
In Fast Formula there is no null. If a database item (DBI) or input value returns nothing and you haven't declared a DEFAULT, the formula errors out at runtime. Not a warning — a hard error. The whole ESS process will show that employee as failed.
So DEFAULT is mandatory for every DBI and every input value you reference.
The 4712/12/31 Date
This is Oracle's "end of time" constant. It's used across all Oracle products to represent "no value" for dates. For example, DEFAULT FOR PER_ASG_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31' means if the employee has no termination date (i.e. they're active), the formula uses 4712/12/31 instead of crashing.
Two categories of defaults here:
PER_ASG_ prefixed ones are Database Items. These pull from HR tables at runtime using the context (HR_ASSIGNMENT_ID). The formula doesn't query the DB directly — Oracle resolves the DBI behind the scenes.
IV_ prefixed ones are Input Values. These come from the absence accrual engine. Oracle populates them automatically when the formula runs as part of the accrual process.
EMPTY_DATE_NUMBER and EMPTY_NUMBER_NUMBER are special FF constants for array-type inputs. Their empty defaults use these built-in constants.
(date) at the end. Strings are inferred from the quotes.
This block declares the input values the accrual engine will pass into the formula. You have to list every input even if you don't use it. Oracle reference guide pages 16-17 lists all the available inputs for each formula type.
Data type declarations: Notice (DATE) after the date inputs and (DATE_NUMBER), (NUMBER_NUMBER) for the array types. Numbers don't need a type declaration — FF assumes numeric by default. This is a common source of compilation errors for beginners — forget the (DATE) and FF treats it as a number and compilation fails.
| Input | What It Is |
|---|---|
IV_ACCRUAL | The value the matrix engine pre-calculated. We override this. |
IV_ACCRUALPERIODSTARTDATE / ENDDATE | The current processing period (e.g. Jan 1 to Jan 31) |
IV_CALENDARSTARTDATE / ENDDATE | The plan calendar year (usually Jan 1 to Dec 31) |
IV_PLANENROLLMENTSTARTDATE | When the employee enrolled in the absence plan |
IV_EVENT_DATES | Array of event dates (band change dates etc) |
IV_ACCRUAL_VALUES | Array of accrual values per band from the matrix |
For this formula the important ones are the period dates and IV_ACCRUAL (which we override). The arrays aren't used in the logic but still must be declared.
GET_CONTEXT() retrieves context values that Oracle sets before running the formula. For absence formulas, PERSON_ID and HR_ASSIGNMENT_ID are always available. The second parameter (0) is the default if the context isn't set.
PER_ASG_ DBIs are "Person Assignment" level database items. They work because HR_ASSIGNMENT_ID is a context. Oracle uses the context to know WHICH assignment to pull data for. In absence formulas Oracle sets this context automatically.
Date Extraction Pattern
FF has no direct "get month from date" function. The workaround is TO_CHAR with a format mask to get the string, then TO_NUMBER to convert to integer. 'MM' gives two-digit month (01-12), 'YYYY' gives four-digit year. You'll use this pattern constantly.
The l_process flag — initialized to 'Y'. This is an important FF pattern. Since FF doesn't support early returns mid-formula (you can only RETURN at the very end), you use a flag variable to control flow. Each validation check can set l_process = 'N', and all subsequent logic checks it before executing. It's the FF equivalent of guard clauses.
FF has no debugger, no breakpoints, no console. ESS_LOG_WRITE is it. It writes a line to the Enterprise Scheduler (ESS) job output log.
Important FF Syntax Rule
You MUST assign the return to a variable. You cannot just call ESS_LOG_WRITE('...') as a standalone statement. FF requires all function calls to be assigned. l_log is a throwaway variable — its value doesn't matter.
String concatenation with || — the || operator joins strings. TO_CHAR converts numbers and dates to strings for concatenation. For dates you can pass a format mask like 'DD-MON-YYYY'.
The formula wraps all logging in IF (l_debug_flag = 'Y') so you can turn it off in production by changing one variable. In testing environments keep it on.
DAYS_BETWEEN(date1, date2) returns the number of days between two dates. The +1 is because it's exclusive — Jan 1 to Jan 31 gives 30, but the actual period is 31 days.
Why this guard exists: this formula assumes monthly processing. The accrual logic returns 1.25 days per period. If the plan is misconfigured to process weekly, the employee would get 1.25 days per WEEK instead of per month. This check catches that by rejecting any period shorter than 28 days (February being the shortest month).
Four checks, all using the same pattern. Each one checks l_process = 'Y' first — once any check sets it to 'N', all remaining checks are skipped automatically.
- Check 1: Hire date is 4712 = hire date DBI returned empty = no hire date on record. Skip.
- Check 2: Period end date is before hire date = this accrual period is before the employee was hired. Skip.
- Check 3: Termination date is before period start AND termination date is not 4712. The second condition is critical — without it, you'd compare the 4712 default against the period start for every active employee.
- Check 4: Assignment user status must be exactly 'ACTIVE'. FF string comparison is case-sensitive. If your Oracle instance uses 'Active' or 'Active - Payroll Eligible', this check will fail for everyone. Always verify the exact string value in your setup.
FF Concept — TO_DATE()
The format mask 'YYYY/MM/DD HH24:MI:SS' must match the string format exactly. HH24 is 24-hour time. FF is strict — wrong format mask = runtime error.
This is where IV_ACCRUAL gets overridden. MONTHS_BETWEEN(date1, date2) returns a decimal. If someone was hired on Mar 15 and the period ends on Sep 15, it returns exactly 6. If the period ends on Sep 10, it returns something like 5.83.
Straightforward. Less than 6 months of service? Accrual stays at 0.
1.25 days per monthly period. Since the formula runs once per month and returns 1.25, the engine accumulates 1.25 each month. 6 months × 1.25 = 7.5 days by regularization.
This is the most complex part. It has sub-phases. First, calculate the one-time credit date:
The business rule: the 15-day credit happens in January. If regularization falls IN January, use that same January. Otherwise use the NEXT January:
| Hire Date | Regularization Date | First Eligible January |
|---|---|---|
| Jan 15, 2024 | Jan 15, 2025 | January 2025 |
| Mar 10, 2024 | Mar 10, 2025 | January 2026 |
| Dec 1, 2023 | Dec 1, 2024 | January 2025 |
3A Bridge Period — Between regularization and first eligible January, keep accruing 1.25/month:
3B One-Time Lump Sum — ONLY the first eligible January. Both period start and end must be in the same January:
3C After the Credit — Any period after the first eligible January returns zero:
For accrual matrix formulas, you return a single numeric value called accrual. The engine takes this value and adds it to the employee's leave balance for the period.
FF Concept
You can only have ONE return statement and it must be the last executable statement. You cannot return early mid-formula. That's why the entire flow uses the l_process flag and nested IFs to control which value accrual gets set to.
Since this post is for people learning FF, here are the syntax rules that this formula demonstrates:
= is used for both assignment and comparison. Context determines which. Inside IF conditions it's comparison, outside it's assignment. There is no ==.
IF (condition) THEN ( statements ). Remove the parens around the body and compilation fails.
Use nested IF inside ELSE, or independent IF blocks with a flag. There is no ELSIF keyword.
'ACTIVE' <> 'Active'. Always verify the exact string value in your Oracle setup.
Every function call must be assigned to a variable, even void-like functions like ESS_LOG_WRITE.
Not logical OR. Logical OR is the word OR. Use || only for string joining.
No early returns allowed. Use flag variables to control flow and return only at the end.
FF statements are not terminated with ;. Line breaks and parser context determine boundaries.
That's the whole formula broken down. The key FF concepts it covers: DEFAULT handling, DBI vs input values, GET_CONTEXT, date manipulation with TO_CHAR / TO_NUMBER / TO_DATE / ADD_MONTHS / MONTHS_BETWEEN / DAYS_BETWEEN, ESS_LOG_WRITE debugging, the process flag pattern for flow control, and RETURN behavior.
If you're new to FF, I'd suggest actually typing this formula out yourself in the formula editor rather than copy-pasting. You'll catch the syntax patterns faster that way.
Hope this helps someone. First blog post done.
Great blog, indeed very informative. Please do continue spreading knowledge!
ReplyDelete