The 7 Pillars in Action: How a Philippine Leave Formula Brings Every Concept to Life
In the previous post, we covered the 7 default components of Fast Formula — what each building block is and how they relate. In this post, we go hands-on using a real production formula: PH_VACATION_LEAVE_ACCRUAL_MATRIX — a Philippine vacation leave accrual formula I built that handles probation periods, monthly accruals, and one-time lump sum credits.
Instead of a toy "return Y" example, we'll use this formula to see how Formula Types, Contexts, Input Values, and the type-to-column linkage work in a real-world scenario.
For our Philippine leave formula, the type is Absence Accrual Matrix Formula. That single selection determined:
If I had accidentally chosen Compensation Person Selection instead, none of the absence-specific inputs (IV_ACCRUAL, IV_ACCRUALPERIODSTARTDATE, etc.) would be available, the formula couldn't attach to an absence plan, and the PER_ASG_ DBIs might behave differently due to different context availability.
Look at this line from our accrual formula:
l_hire_date = PER_ASG_REL_ORIGINAL_DATE_OF_HIRE
This DBI returns a hire date — but whose hire date? The answer is: whoever the HR_ASSIGNMENT_ID context points to.
When the absence accrual process runs for 500 employees, it calls this formula 500 times. Each time, it sets a different HR_ASSIGNMENT_ID context. The DBI automatically returns that specific employee's hire date. You never write SQL — the context-route-DBI chain handles it.
/* We also retrieved context values directly: */ l_person_id = GET_CONTEXT(PERSON_ID, 0) l_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID, 0) /* These DBIs work BECAUSE the context is set: */ l_hire_date = PER_ASG_REL_ORIGINAL_DATE_OF_HIRE l_term_date = PER_ASG_REL_ACTUAL_TERMINATION_DATE l_asg_status = PER_ASG_STATUS_USER_STATUS
Here's the INPUTS ARE block from our formula:
INPUTS ARE IV_ACCRUAL, IV_CARRYOVER, IV_CEILING, IV_ACCRUAL_CEILING, IV_ACCRUALPERIODSTARTDATE (DATE), IV_ACCRUALPERIODENDDATE (DATE), IV_CALENDARSTARTDATE (DATE), IV_CALENDARENDDATE (DATE), IV_PLANENROLLMENTSTARTDATE (DATE), IV_PLANENROLLMENTENDDATE (DATE), IV_EVENT_DATES (DATE_NUMBER), IV_ACCRUAL_VALUES (NUMBER_NUMBER)
These aren't random — they're defined in the Oracle FF Reference Guide (pages 16-17) specifically for the Absence Accrual Matrix formula type. The accrual engine populates them automatically at runtime.
| Input Value | What Our Formula Does With It |
|---|---|
IV_ACCRUAL | Matrix engine's pre-calculated value — we completely override it with our own logic |
IV_ACCRUALPERIODSTARTDATE | Used to determine which month we're processing and calculate months of service |
IV_ACCRUALPERIODENDDATE | End of current period — critical for the MONTHS_BETWEEN calculation |
IV_PLANENROLLMENTSTARTDATE | When the employee enrolled — used in eligibility checks |
IV_EVENT_DATES | Array type — declared but not used in our logic (still must be declared) |
Every DBI and input value in our formula has a DEFAULT. This isn't optional — it's the difference between a working formula and a runtime crash:
/* What if the employee has no termination date? */ DEFAULT FOR PER_ASG_REL_ACTUAL_TERMINATION_DATE IS '4712/12/31 00:00:00' (date) /* What if the matrix engine sends no accrual value? */ DEFAULT FOR IV_ACCRUAL IS 0 /* What if assignment status DBI returns nothing? */ DEFAULT FOR PER_ASG_STATUS_USER_STATUS IS 'NA'
In our formula, the 4712/12/31 default for termination date is clever — later in the logic, we check if l_term_date = TO_DATE('4712/12/31...') to determine "is this person active?" If they are active, the DBI returns empty, the DEFAULT kicks in with 4712, and our check correctly identifies them as active.
All Fast Formulas do one of two things:
/* Write — returns a number */ RETURN accrual /* 0, 1.25, or 15 */ /* Validate — returns Y or N */ RETURN l_value /* 'Y' or 'N' */
You wrote a formula. It compiled. Green checkmark. But when you go to attach it to a setup field — it's not in the dropdown. You search, refresh, recompile. Nothing.
The reason: every formula has a type, and every setup field expects a specific type. If they don't match — the formula simply won't appear. No error, no warning. It's just invisible.
A real mistake I've seen:
A consultant needed to write an absence accrual formula. They went to the formula editor and accidentally selected Absence Accrual instead of Absence Accrual Matrix. Two very similar-sounding types — but different.
Here's how I created the PH accrual formula.
Go to Setup and Maintenance, search for "Fast Formulas", and open the Fast Formulas task.
PH_VACATION_LEAVE_ACCRUAL_MATRIX01/01/2000 — far past for safetyWhy a far-past date? If the absence process runs for a past period (Jan 2024) but the formula was created with today's date, it won't be found. Using 01/01/2000 ensures it's always available.
Our PH formula required an LDG because Absence formulas are country-specific. Here's the general rule:
Every formula follows this path from idea to production:
Identify the correct Type
↓
Check if LDG is required
↓
Write the formula code — DEFAULTs, INPUTS ARE, logic, RETURN
↓
Save → Submit → Compile — wait for green status
↓
Verify attachment — confirm the formula appears in the correct setup field
What our PH Accrual Formula taught us:
Want to see the full formula code with line-by-line explanation? Check out my detailed breakdown: Breaking Down a PH Vacation Leave Accrual Matrix Formula — Section by Section.
Comments
Post a Comment