Fast Formula is Oracle Fusion Cloud HCM's built-in programming language for business rules. It drives payroll calculations, absence accruals, benefits eligibility, compensation rules, extract criteria, and validation logic. If you've ever stared at the formula editor wondering "what does a working formula actually look like," this guide is for you.
Every formula below compiles and runs. Copy them, adapt the variable names to your environment, and deploy. We also cover the syntax reference, database items (DBIs), formula types, and the debugging techniques that will save you hours.
Where formulas live: Navigate to My Client Groups > Payroll > Fast Formulas (or search "Fast Formulas" in the task pane). Formulas reference data from the underlying HCM tables — use HCM Tables to look up columns and table structures.
In This Guide
- Syntax Reference
- Database Items (DBI) Reference
- Formula Types by Module
- Example 1: PTO Accrual Formula
- Example 2: Sick Leave Accrual
- Example 3: Overtime Earnings Calculation
- Example 4: Tax Deduction Formula
- Example 5: Benefits Eligibility
- Example 6: Compensation Eligibility
- Example 7: Absence Entitlement
- Example 8: Extract Criteria Rule
- Example 9: Validation Formula
- Example 10: Payroll Skip Rule
- Debugging Tips
- Best Practices
Syntax Reference
Fast Formula uses a procedural, English-like syntax. Here are the core constructs:
Variable Declarations and Defaults
/* Declare default values for database items (DBIs) */
DEFAULT FOR PER_ASG_HOURS_WORKED IS 0
DEFAULT FOR PER_PER_DATE_OF_BIRTH IS '1900/01/01 00:00:00' (DATE)
/* Local variables */
l_hours_per_year = 0
l_accrual_rate = 0
l_employee_name = ' 'INPUTS (for Payroll Formulas)
/* INPUTS receive values from element entry input values */
INPUTS ARE hours_worked (number),
rate (number),
date_earned (date)IF / THEN / ELSE
IF (l_years_of_service >= 10) THEN
l_accrual_rate = 200
ELSE IF (l_years_of_service >= 5) THEN
l_accrual_rate = 160
ELSE
l_accrual_rate = 120
/* Compound conditions */
IF (l_status = 'ACTIVE' AND l_fte >= 0.5) THEN
l_eligible = 'Y'RETURN
/* Return values back to the calling process */
RETURN l_accrual_amount, l_accrual_date
/* For payroll: return to specific output names */
RETURN pay_value, hoursCHANGE_CONTEXTS
/* Switch context to access DBIs from a different legislation or assignment */
CHANGE_CONTEXTS (LEGISLATIVE_DATA_GROUP_ID = l_ldg_id)
(
l_balance_value = PAY_BALANCE_GET_VALUE('Regular Earnings' , 'Assignment Year to Date')
)Built-in Functions
/* Date functions */
l_today = GET_DATE()
l_days = DAYS_BETWEEN(l_hire_date, l_today)
l_date = ADD_DAYS(l_start_date, 90)
l_date2 = ADD_MONTHS(l_start_date, 6)
l_year = TO_NUMBER(TO_CHAR(l_today, 'YYYY'))
/* String functions */
l_upper = UPPER(l_name)
l_sub = SUBSTR(l_code, 1, 3)
l_len = LENGTH(l_value)
l_pos = INSTR(l_string, '-')
/* Number functions */
l_rounded = ROUND(l_amount, 2)
l_truncated = TRUNC(l_value)
/* Conversion */
l_num = TO_NUMBER(l_string_val)
l_str = TO_CHAR(l_number_val)
l_date = TO_DATE(l_date_string, 'YYYY/MM/DD HH24:MI:SS')Database Items (DBI) Reference
Database Items are the bridge between Fast Formula and HCM tables. Each DBI maps to a column in an underlying table like PER_ALL_PEOPLE_F or PER_ALL_ASSIGNMENTS_F. Here are the 20+ most commonly used DBIs:
| Database Item (DBI) | Returns | Source Table |
|---|---|---|
| PER_PER_DATE_OF_BIRTH | Date | PER_ALL_PEOPLE_F |
| PER_ASG_EFFECTIVE_START_DATE | Date | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_EFFECTIVE_END_DATE | Date | PER_ALL_ASSIGNMENTS_F |
| PER_PER_ORIGINAL_DATE_OF_HIRE | Date | PER_PERIODS_OF_SERVICE |
| PER_ASG_HOURS_WORKED | Number | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_NORMAL_HOURS | Number | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_EMPLOYMENT_CATEGORY | Text | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_ASSIGNMENT_STATUS_TYPE | Text | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_WORKER_TYPE | Text | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_FULL_TIME_OR_PART_TIME | Text | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_ORGANIZATION_ID | Number | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_JOB_ID | Number | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_LOCATION_ID | Number | PER_ALL_ASSIGNMENTS_F |
| PER_ASG_GRADE_ID | Number | PER_ALL_ASSIGNMENTS_F |
| PER_POS_DATE_START | Date | PER_PERIODS_OF_SERVICE |
| PER_POS_ACTUAL_TERMINATION_DATE | Date | PER_PERIODS_OF_SERVICE |
| PAY_PAYROLL_ID | Number | PAY_PAYROLLS_F |
| PAY_EARN_PERIOD_START | Date | PAY_TIME_PERIODS |
| PAY_EARN_PERIOD_END | Date | PAY_TIME_PERIODS |
| CMP_SALARY_AMOUNT | Number | CMP_SALARY_F |
| CMP_SALARY_ANNUAL_AMOUNT | Number | CMP_SALARY_F |
| BEN_ELIG_FLAG | Text | BEN_ELIG_PER_F |
Tip: To find all available DBIs, go to Setup and Maintenance > Manage Fast Formula Database Items or query the FF_DATABASE_ITEMS table. You can also type a partial name in the formula editor and use auto-complete.
Formula Types by Module
| Formula Type | Module | Purpose |
|---|---|---|
| Payroll Calculation | Payroll | Calculate earnings, deductions, and net pay. Attached to element types. |
| Payroll Skip Rule | Payroll | Determine whether to process an element for a given assignment in a run. |
| Payroll Proration | Payroll | Calculate prorated amounts when mid-period changes occur. |
| Absence Accrual | Absence | Calculate leave accruals (PTO, sick, vacation). Determine carryover and caps. |
| Absence Entitlement | Absence | Determine one-time or annual entitlement to an absence type. |
| Benefits Eligibility | Benefits | Determine if a person qualifies for a benefit plan or program. |
| Benefits Rate | Benefits | Calculate contribution or coverage rates for a benefit plan. |
| Compensation Eligibility | Compensation | Determine eligibility for compensation plans, budgets, or individual components. |
| Extract Rule | Extract | Filter or transform data in HCM Extracts. Criteria rules, data elements, or record-level rules. |
| Validation | Validation | Validate data entry (person, assignment, element entry). Return error messages on failure. |
Example 1: PTO Accrual Formula
This formula calculates PTO hours based on years of service, prorates for part-time workers, and enforces a balance cap.
Absence Accrual/* PTO Accrual Formula
Type: Absence Accrual
Calculates annual PTO hours based on years of service.
Prorates for part-time. Caps balance at 1.5x annual rate. */
DEFAULT FOR PER_POS_DATE_START IS '1900/01/01 00:00:00' (DATE)
DEFAULT FOR PER_ASG_NORMAL_HOURS IS 40
DEFAULT FOR PER_ASG_FULL_TIME_OR_PART_TIME IS 'FULL_TIME'
l_hire_date = PER_POS_DATE_START
l_today = GET_DATE()
l_years_of_service = TRUNC(DAYS_BETWEEN(l_hire_date, l_today) / 365.25)
l_normal_hours = PER_ASG_NORMAL_HOURS
l_ft_pt = PER_ASG_FULL_TIME_OR_PART_TIME
/* Annual accrual tiers */
IF (l_years_of_service >= 15) THEN
l_annual_hours = 200
ELSE IF (l_years_of_service >= 10) THEN
l_annual_hours = 180
ELSE IF (l_years_of_service >= 5) THEN
l_annual_hours = 160
ELSE IF (l_years_of_service >= 1) THEN
l_annual_hours = 120
ELSE
l_annual_hours = 80
/* Prorate for part-time (based on scheduled hours vs 40) */
IF (l_ft_pt = 'PART_TIME' AND l_normal_hours < 40) THEN
(
l_fte_ratio = l_normal_hours / 40
l_annual_hours = ROUND(l_annual_hours * l_fte_ratio, 2)
)
/* Per-period accrual (biweekly = 26 periods) */
l_accrual_amount = ROUND(l_annual_hours / 26, 4)
/* Balance cap at 1.5x annual */
l_max_balance = l_annual_hours * 1.5
RETURN l_accrual_amount, l_max_balanceExample 2: Sick Leave Accrual
Flat-rate sick leave accrual with a separate cap and no service tiers.
Absence Accrual/* Sick Leave Accrual
Type: Absence Accrual
Flat 96 hours/year for all employees. Cap at 480 hours. */
DEFAULT FOR PER_ASG_ASSIGNMENT_STATUS_TYPE IS 'INACTIVE'
DEFAULT FOR PER_ASG_NORMAL_HOURS IS 40
l_status = PER_ASG_ASSIGNMENT_STATUS_TYPE
l_normal_hours = PER_ASG_NORMAL_HOURS
/* Only accrue for active employees */
IF (l_status = 'ACTIVE_ASSIGN' OR l_status = 'ACTIVE') THEN
(
l_annual_hours = 96
/* Prorate for part-time */
IF (l_normal_hours < 40 AND l_normal_hours > 0) THEN
l_annual_hours = ROUND(96 * (l_normal_hours / 40), 2)
/* Biweekly accrual */
l_accrual_amount = ROUND(l_annual_hours / 26, 4)
l_max_balance = 480
)
ELSE
(
l_accrual_amount = 0
l_max_balance = 480
)
RETURN l_accrual_amount, l_max_balanceExample 3: Overtime Earnings Calculation
Calculates overtime pay at 1.5x the regular hourly rate. Sources from PAY_ELEMENT_TYPES_F and PAY_ELEMENT_ENTRIES_F.
Payroll Calculation/* Overtime Earnings Calculation
Type: Payroll Calculation
Input: overtime_hours from element entry
Calculates pay at 1.5x regular hourly rate */
DEFAULT FOR CMP_SALARY_ANNUAL_AMOUNT IS 0
DEFAULT FOR PER_ASG_NORMAL_HOURS IS 40
INPUTS ARE overtime_hours (number)
l_annual_salary = CMP_SALARY_ANNUAL_AMOUNT
l_weekly_hours = PER_ASG_NORMAL_HOURS
/* Calculate hourly rate from annual salary */
IF (l_weekly_hours > 0 AND l_annual_salary > 0) THEN
(
l_annual_work_hours = l_weekly_hours * 52
l_hourly_rate = ROUND(l_annual_salary / l_annual_work_hours, 4)
l_ot_rate = l_hourly_rate * 1.5
l_pay_value = ROUND(overtime_hours * l_ot_rate, 2)
)
ELSE
(
l_hourly_rate = 0
l_ot_rate = 0
l_pay_value = 0
)
RETURN l_pay_valueExample 4: Tax Deduction Formula
A simplified tax deduction formula demonstrating bracket-based progressive calculation.
Payroll Calculation/* Supplemental Tax Deduction
Type: Payroll Calculation
Progressive bracket calculation on bonus/supplemental pay */
INPUTS ARE taxable_amount (number)
l_tax = 0
/* Progressive brackets */
IF (taxable_amount > 50000) THEN
(
l_tax = l_tax + (taxable_amount - 50000) * 0.35
l_remaining = 50000
)
ELSE
l_remaining = taxable_amount
IF (l_remaining > 20000) THEN
(
l_tax = l_tax + (l_remaining - 20000) * 0.25
l_remaining = 20000
)
IF (l_remaining > 5000) THEN
(
l_tax = l_tax + (l_remaining - 5000) * 0.15
l_remaining = 5000
)
l_tax = l_tax + l_remaining * 0.10
l_pay_value = ROUND(l_tax, 2)
RETURN l_pay_valueExample 5: Benefits Eligibility
Determines if an employee qualifies for medical benefits based on hours worked and employment category. References PER_ALL_ASSIGNMENTS_F columns.
Benefits Eligibility/* Medical Benefits Eligibility
Type: Benefits Eligibility
Eligible if: active employee, 30+ hours/week, past 90-day waiting period */
DEFAULT FOR PER_ASG_ASSIGNMENT_STATUS_TYPE IS 'INACTIVE'
DEFAULT FOR PER_ASG_NORMAL_HOURS IS 0
DEFAULT FOR PER_ASG_WORKER_TYPE IS ' '
DEFAULT FOR PER_POS_DATE_START IS '1900/01/01 00:00:00' (DATE)
l_status = PER_ASG_ASSIGNMENT_STATUS_TYPE
l_hours = PER_ASG_NORMAL_HOURS
l_worker_type = PER_ASG_WORKER_TYPE
l_hire_date = PER_POS_DATE_START
l_today = GET_DATE()
l_days_employed = DAYS_BETWEEN(l_hire_date, l_today)
l_eligible = 'N'
/* Must be active employee (not CWK) */
IF (l_status = 'ACTIVE_ASSIGN' OR l_status = 'ACTIVE') THEN
(
IF (l_worker_type = 'E' OR l_worker_type = 'EMPLOYEE') THEN
(
/* Must work 30+ hours per week */
IF (l_hours >= 30) THEN
(
/* Must pass 90-day waiting period */
IF (l_days_employed >= 90) THEN
l_eligible = 'Y'
)
)
)
RETURN l_eligibleExample 6: Compensation Eligibility
Determines eligibility for an annual merit compensation plan based on performance rating and hire date.
Compensation Eligibility/* Annual Merit Compensation Eligibility
Type: Compensation Eligibility
Eligible if: active, hired before Oct 1, not on PIP */
DEFAULT FOR PER_ASG_ASSIGNMENT_STATUS_TYPE IS 'INACTIVE'
DEFAULT FOR PER_POS_DATE_START IS '1900/01/01 00:00:00' (DATE)
DEFAULT FOR PER_ASG_EMPLOYMENT_CATEGORY IS ' '
l_status = PER_ASG_ASSIGNMENT_STATUS_TYPE
l_hire_date = PER_POS_DATE_START
l_emp_category = PER_ASG_EMPLOYMENT_CATEGORY
l_today = GET_DATE()
l_current_year = TO_NUMBER(TO_CHAR(l_today, 'YYYY'))
/* Cutoff: must be hired before Oct 1 of current year */
l_cutoff_date = TO_DATE(TO_CHAR(l_current_year) || '/10/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
l_eligible = 'N'
IF (l_status = 'ACTIVE_ASSIGN' OR l_status = 'ACTIVE') THEN
(
/* Must be regular employee (not temporary/seasonal) */
IF (l_emp_category = 'FR' OR l_emp_category = 'PR') THEN
(
/* Must be hired before cutoff */
IF (l_hire_date < l_cutoff_date) THEN
l_eligible = 'Y'
)
)
RETURN l_eligibleExample 7: Absence Entitlement
Grants a one-time bereavement leave entitlement based on relationship type.
Absence Entitlement/* Bereavement Leave Entitlement
Type: Absence Entitlement
Grants leave days based on relationship to the deceased */
INPUTS ARE relationship_type (text)
DEFAULT FOR PER_ASG_ASSIGNMENT_STATUS_TYPE IS 'INACTIVE'
l_status = PER_ASG_ASSIGNMENT_STATUS_TYPE
l_entitlement_days = 0
IF (l_status = 'ACTIVE_ASSIGN' OR l_status = 'ACTIVE') THEN
(
IF (relationship_type = 'SPOUSE' OR
relationship_type = 'CHILD' OR
relationship_type = 'PARENT') THEN
l_entitlement_days = 5
ELSE IF (relationship_type = 'SIBLING' OR
relationship_type = 'GRANDPARENT' OR
relationship_type = 'GRANDCHILD') THEN
l_entitlement_days = 3
ELSE IF (relationship_type = 'IN_LAW' OR
relationship_type = 'AUNT_UNCLE') THEN
l_entitlement_days = 1
ELSE
l_entitlement_days = 1
)
/* Convert days to hours (8-hour day) */
l_entitlement_hours = l_entitlement_days * 8
RETURN l_entitlement_hoursExample 8: Extract Criteria Rule
Filters an HCM Extract to include only active US employees in specific departments.
Extract Rule/* Extract Criteria Rule — Active US Employees
Type: Extract Rule (Criteria)
Include only active employees in US legislation, in specific departments */
DEFAULT FOR PER_ASG_ASSIGNMENT_STATUS_TYPE IS 'INACTIVE'
DEFAULT FOR PER_ASG_WORKER_TYPE IS ' '
DEFAULT FOR PER_ASG_ORGANIZATION_ID IS 0
l_status = PER_ASG_ASSIGNMENT_STATUS_TYPE
l_worker_type = PER_ASG_WORKER_TYPE
l_org_id = PER_ASG_ORGANIZATION_ID
l_include = 'N'
/* Active employees only */
IF (l_status = 'ACTIVE_ASSIGN' OR l_status = 'ACTIVE') THEN
(
IF (l_worker_type = 'E' OR l_worker_type = 'EMPLOYEE') THEN
(
/* Include all orgs, or filter to specific ones:
Uncomment below to restrict to specific org IDs */
/* IF (l_org_id = 300000001234567 OR
l_org_id = 300000001234568) THEN */
l_include = 'Y'
)
)
RETURN l_includeExample 9: Validation Formula
Validates that a required field is populated when a specific condition is true. Used as person or assignment validation.
Validation/* Assignment Validation — Require Position for Regular Employees
Type: Validation Formula
Error if regular full-time employee has no position assigned */
DEFAULT FOR PER_ASG_EMPLOYMENT_CATEGORY IS ' '
DEFAULT FOR PER_ASG_FULL_TIME_OR_PART_TIME IS ' '
INPUTS ARE position_id (number)
l_emp_category = PER_ASG_EMPLOYMENT_CATEGORY
l_ft_pt = PER_ASG_FULL_TIME_OR_PART_TIME
l_formula_status = 'S' /* S = Success */
l_formula_message = ' '
/* Regular full-time employees must have a position */
IF (l_emp_category = 'FR' AND l_ft_pt = 'FULL_TIME') THEN
(
IF (position_id = 0 OR WAS_DEFAULTED('position_id') = 'Y') THEN
(
l_formula_status = 'E' /* E = Error */
l_formula_message = 'A position is required for regular full-time employees. Please select a position before saving.'
)
)
RETURN l_formula_status, l_formula_messageExample 10: Payroll Skip Rule
Determines whether to skip processing an element for a given assignment. Returns 'Y' to skip, 'N' to process.
/* Skip Rule — Skip Bonus for Employees on Leave
Type: Payroll Skip Rule
Skip the bonus element if employee is on unpaid leave */
DEFAULT FOR PER_ASG_ASSIGNMENT_STATUS_TYPE IS 'INACTIVE'
l_status = PER_ASG_ASSIGNMENT_STATUS_TYPE
l_skip = 'Y' /* Default: skip (safe default) */
/* Only process if actively working */
IF (l_status = 'ACTIVE_ASSIGN' OR l_status = 'ACTIVE') THEN
l_skip = 'N'
ELSE IF (l_status = 'SUSPEND_ASSIGN' OR l_status = 'SUSPENDED') THEN
l_skip = 'Y'
ELSE
l_skip = 'Y'
RETURN l_skipDebugging Tips
Compilation Errors
Fast Formula compilation errors are notoriously cryptic. Here are the most common:
| Error | Cause | Fix |
|---|---|---|
HR_51085_FF_MISSING_OPERAND |
Missing value after operator, or stray keyword | Check for missing right-hand side of = or incomplete IF block |
HR_51086_FF_INCORRECT_ITEM |
Referencing a DBI that doesn't exist in current context | Verify DBI name spelling and check formula type context. Use Manage Fast Formula Database Items to search |
HR_51074_FF_TYPE_MISMATCH |
Comparing text to number, or wrong DEFAULT type | Ensure DEFAULT types match DBI return types. Use TO_NUMBER() or TO_CHAR() to convert |
HR_51069_FF_DUPLICATE_NAME |
Variable declared twice or name conflicts with DBI | Rename your local variable. Prefix with l_ to avoid conflicts |
ORA-01403: no data found |
DBI returns null and no DEFAULT is set | Add DEFAULT FOR [dbi_name] IS [value] for every DBI you reference |
| Formula compiles but returns wrong result | Logic error, wrong DBI, or missing parentheses | Use the Run Formula test feature with sample inputs. Add intermediate RETURNs temporarily to trace values |
Testing Your Formula
- Compile first: Click "Submit" to compile. Fix all compilation errors before testing
- Use Run Formula: Navigate to the formula, click "Run Formula", provide test inputs and effective date
- Check with a real employee: Run a QuickPay or test absence entry against a known employee to validate real DBI values
- Trace values: Temporarily add extra RETURN variables to expose intermediate calculation steps
Warning: Always test formulas in a non-production environment first. A payroll formula with an error can produce incorrect paychecks for your entire population. Use sandboxes or test environments.
Best Practices
- Always declare DEFAULTs. Every DBI you reference should have a DEFAULT. This prevents
ORA-01403runtime errors when a DBI returns null - Use
l_prefix for local variables. This prevents name collisions with DBIs and makes your formula readable - Comment liberally. Use
/* comment */to explain business rules. Future maintainers (including yourself) will thank you - Keep formulas short. If a formula exceeds 200 lines, consider splitting the logic into multiple formulas or using lookup tables
- Use ROUND() on currency. Always round monetary calculations to 2 decimal places:
ROUND(l_amount, 2) - Handle edge cases. What happens for zero hours? Null hire date? Missing salary? Test these scenarios
- Version your formulas. Include a version comment at the top:
/* v2.1 - 2026-02-06 - Added part-time proration */ - Test with boundary values. If your formula has a 5-year service tier, test with exactly 4.99 years, 5.0 years, and 5.01 years
- Don't hardcode IDs. Use descriptive lookups or input parameters instead of hardcoding
ORGANIZATION_ID = 300000001234567 - Document RETURN values. Clearly state what each return variable means and what units it uses (hours, days, dollars, 'Y'/'N')
Look Up the Underlying Tables
Fast Formula DBIs map to HCM table columns. Search them all here.
Search 35,000+ Tables