Oracle HCM Benefits Tables & Configuration Guide — BEN_ Schema Deep Dive

Oracle Fusion HCM Benefits tables are among the most complex in the entire system. Unlike payroll tables (which follow a linear month-by-month flow) or absence tables (which are largely self-contained), benefits involve multiple overlapping dimensions: plan types, eligibility rules, life events, coverage options, enrollment periods, and enrollment overrides. This guide covers the BEN_ schema with working SQL examples for the most common reporting scenarios.

Why Benefits Tables Are Complex

Benefits in Oracle HCM are complex because they model real-world insurance administration:

• Multiple plan types: Medical, dental, vision, FSA, HSA, 401(k), supplemental life — each with different rules, rates, and eligibility.
• Eligibility rules: Not all employees can enroll in all plans. Eligibility depends on employment type (FTE vs. contractor), assignment status, union membership, age, tenure, and job classification.
• Coverage options within plans: A medical plan might offer Employee-Only, Employee+Spouse, Employee+Child, and Family coverage. Each option has a different premium.
• Life events: Employees can change elections outside open enrollment if a life event occurs (birth, marriage, divorce, loss of coverage).
• Enrollment overrides: Administrators can override or cancel enrollments for corrections, terminations, or compliance.
• Date-effective history: Plans, options, and enrollment decisions all have effective dates. Benefit administrators need to query "which plans were active on January 15, 2026?" — not just "which plans are active today?"

Because of these dimensions, the benefits schema uses at least 12 core tables, plus supporting tables for eligibility profiles, life event definitions, and enrollment rules. Queries often require 4-6 table joins and careful WHERE clause filtering.

Core Benefits Tables Overview

Here are the 12 most commonly queried BEN_ tables:

BEN_PL_F: Plans Deep Dive

BEN_PL_F is the master definition of all benefit plans. It is a date-effective table, meaning each plan can have multiple rows representing different versions across time.

Key Columns

• pl_id — Unique plan ID (auto-generated by Oracle)
• business_group_id — Organizational hierarchy; typically 1 for a single-group company
• plan_type — MEDICAL, DENTAL, VISION, FSA, HSA, 401K, LIFE, SUPPLEMENTAL, etc.
• plan_name — User-friendly name, e.g., "Blue Cross PPO" or "Delta Dental Premier"
• effective_start_date — When this plan version becomes active
• effective_end_date — When this plan version expires (12/31/4712 = no end date)
• pl_status — ACTIVE or INACTIVE
• uses_elig_flag — Y/N: does this plan use eligibility profiles?

BEN_OPT_F: Options & Rates

BEN_OPT_F defines the coverage choices within a plan. A single plan can have 2-5 options. For example, a medical plan might offer:

• Employee Only
• Employee + Spouse
• Employee + 1 Child
• Employee + 2+ Children
• Family (Employee + All)

Each option has its own premium (stored in BEN_OPT_RT_F) and eligibility rules. You query BEN_OPT_F to see what choices are available for a plan; you query BEN_OPT_RT_F to see the monthly or annual cost of each choice.

Key Columns

• opt_id — Unique option ID
• pl_id — Foreign key to BEN_PL_F
• opt_name — e.g., "Family Coverage"
• opt_typ_cd — Option type (e.g., EMPLOYEE_ONLY, FAMILY)
• effective_start_date, effective_end_date — Date-effective validity
• cvg_ty_cd — Coverage type (e.g., MONTHLY, ANNUAL)

BEN_PRTT_ENRT_RSLT_F: Enrollment Transactions

This is the workhorse table for benefits reporting. Every row represents one employee's enrollment in one plan-option combination. This table grows over time — every enrollment, change, or override creates a new row.

Key Columns

• prtt_enrt_rslt_id — Unique enrollment transaction ID
• person_id — Employee ID (matches PER_ALL_PEOPLE_F.person_id)
• pl_id — Plan ID (matches BEN_PL_F.pl_id)
• opt_id — Option ID (matches BEN_OPT_F.opt_id)
• elig_flag — Y/N: was the employee eligible for this plan at the time of enrollment?
• enrt_ovrid_reason — If not NULL, this enrollment was overridden (e.g., "TERMINATION", "ADMIN_CHANGE", "BENEFIT_SUSPENSION"). Active enrollments have NULL.
• enrt_cvg_strt_dt — Coverage effective date (when the benefit kicks in)
• enrt_cvg_end_dt — Coverage end date (typically 12/31/YYYY of the plan year)
• creation_date — When this enrollment record was created

BEN_PER_IN_LER: Life Events

BEN_PER_IN_LER records when an employee experiences a life event (birth, marriage, divorce, loss of coverage). Each life event opens a defined enrollment period during which the employee can change benefits.

Key Columns

• per_in_ler_id — Unique life event record ID
• person_id — Employee ID
• ler_id — Life event reason ID (matches BEN_LER_F.ler_id)
• lf_evt_ocrd_dt — Date the life event occurred (e.g., birth date, marriage date)
• dtls_clctd_flag — Y/N: has the employee provided details about the life event?
• creation_date — When the life event was recorded

Join BEN_PER_IN_LER to BEN_LER_F to get the name of the life event (e.g., "Birth", "Marriage", "Loss of Coverage").

BEN_ELIG_PER_F: Eligibility Profiles

BEN_ELIG_PER_F defines eligibility rules — which employees can enroll in which plans. Eligibility can be based on employment type, assignment status, union membership, or job classification.

Key Columns

• elig_per_id — Unique eligibility profile ID
• person_id — Employee ID
• elig_flag — Y/N: is this employee eligible?
• effective_start_date, effective_end_date — Date-effective eligibility window

To find which PLANS are eligible for an employee, join BEN_ELIG_PER_F to BEN_ELIG_PER_OPT_F (option level) or to BEN_ELIG_PL_F (plan level). The structure is: Employee → Eligibility Profile → Eligible Plans/Options.

Benefits Enrollment Data Flow

Here's the typical path of data through the benefits tables when an employee enrolls in a plan during open enrollment:

8 Production SQL Queries

Query 1: Active Enrollments by Plan & Department

SELECT
  pp.person_number,
  pp.first_name,
  pp.last_name,
  pl.plan_name,
  pl.plan_type,
  opt.opt_name,
  dorg.name AS department,
  per.enrt_cvg_strt_dt,
  per.enrt_cvg_end_dt,
  COUNT(*) OVER (PARTITION BY pl.pl_id) AS plan_enrollees
FROM ben_prtt_enrt_rslt_f per
JOIN ben_pl_f pl
  ON per.pl_id = pl.pl_id
  AND TRUNC(SYSDATE) BETWEEN pl.effective_start_date AND pl.effective_end_date
JOIN ben_opt_f opt
  ON per.opt_id = opt.opt_id
  AND TRUNC(SYSDATE) BETWEEN opt.effective_start_date AND opt.effective_end_date
JOIN per_all_people_f pp
  ON per.person_id = pp.person_id
  AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date AND pp.effective_end_date
JOIN per_all_assignments_f paa
  ON pp.person_id = paa.person_id
  AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
JOIN org_organization_definitions_f dorg
  ON paa.department_id = dorg.organization_id
  AND TRUNC(SYSDATE) BETWEEN dorg.effective_start_date AND dorg.effective_end_date
WHERE per.enrt_ovrid_reason IS NULL
  AND TRUNC(SYSDATE) BETWEEN per.enrt_cvg_strt_dt AND per.enrt_cvg_end_dt
ORDER BY dorg.name, pl.plan_type, pp.last_name

Query 2: Enrollment History for Single Employee

SELECT
  per.prtt_enrt_rslt_id,
  pl.plan_name,
  pl.plan_type,
  opt.opt_name,
  per.enrt_cvg_strt_dt,
  per.enrt_cvg_end_dt,
  per.enrt_ovrid_reason,
  per.creation_date,
  per.elig_flag
FROM ben_prtt_enrt_rslt_f per
JOIN ben_pl_f pl
  ON per.pl_id = pl.pl_id
JOIN ben_opt_f opt
  ON per.opt_id = opt.opt_id
WHERE per.person_id = :person_id
ORDER BY per.enrt_cvg_strt_dt DESC, per.creation_date DESC

Query 3: Life Events Pending Processing

SELECT
  pil.per_in_ler_id,
  pp.person_number,
  pp.first_name,
  pp.last_name,
  ler.ler_name,
  pil.lf_evt_ocrd_dt,
  pil.creation_date,
  pil.dtls_clctd_flag,
  CASE WHEN pil.dtls_clctd_flag = 'Y' THEN 'Ready for enrollment'
       WHEN SYSDATE - pil.creation_date > 30 THEN 'PENDING - OVERDUE'
       ELSE 'Pending' END AS status
FROM ben_per_in_ler pil
JOIN per_all_people_f pp
  ON pil.person_id = pp.person_id
  AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date AND pp.effective_end_date
JOIN ben_ler_f ler
  ON pil.ler_id = ler.ler_id
  AND TRUNC(SYSDATE) BETWEEN ler.effective_start_date AND ler.effective_end_date
WHERE pil.dtls_clctd_flag = 'N'
  AND pil.lf_evt_ocrd_dt >= TRUNC(SYSDATE) - 60
ORDER BY pil.creation_date ASC

Query 4: Benefit Costs by Department (Monthly Deduction)

SELECT
  dorg.name AS department,
  pl.plan_type,
  pl.plan_name,
  COUNT(DISTINCT per.person_id) AS employee_count,
  SUM(ort.input_value_id) AS total_monthly_cost,
  ROUND(AVG(ort.input_value_id), 2) AS avg_per_employee
FROM ben_prtt_enrt_rslt_f per
JOIN ben_pl_f pl
  ON per.pl_id = pl.pl_id
  AND TRUNC(SYSDATE) BETWEEN pl.effective_start_date AND pl.effective_end_date
JOIN ben_opt_f opt
  ON per.opt_id = opt.opt_id
  AND TRUNC(SYSDATE) BETWEEN opt.effective_start_date AND opt.effective_end_date
JOIN ben_opt_rt_f ort
  ON opt.opt_id = ort.opt_id
  AND TRUNC(SYSDATE) BETWEEN ort.effective_start_date AND ort.effective_end_date
  AND ort.rate_type = 'EMPLOYEE_CONTRIBUTION'
JOIN per_all_assignments_f paa
  ON per.person_id = paa.person_id
  AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
JOIN org_organization_definitions_f dorg
  ON paa.department_id = dorg.organization_id
  AND TRUNC(SYSDATE) BETWEEN dorg.effective_start_date AND dorg.effective_end_date
WHERE per.enrt_ovrid_reason IS NULL
  AND TRUNC(SYSDATE) BETWEEN per.enrt_cvg_strt_dt AND per.enrt_cvg_end_dt
GROUP BY dorg.name, pl.plan_type, pl.plan_name
ORDER BY department, pl.plan_type

Query 5: Coverage Levels & Dependents

SELECT
  per.prtt_enrt_rslt_id,
  pp.person_number,
  pp.first_name,
  pp.last_name,
  pl.plan_name,
  opt.opt_name,
  COUNT(DISTINCT dpnt.dpnt_person_id) AS dependent_count,
  LISTAGG(CONCAT(dp.first_name, ' ', dp.last_name), ', ') WITHIN GROUP (ORDER BY dp.last_name) AS dependents
FROM ben_prtt_enrt_rslt_f per
JOIN ben_pl_f pl
  ON per.pl_id = pl.pl_id
  AND TRUNC(SYSDATE) BETWEEN pl.effective_start_date AND pl.effective_end_date
JOIN ben_opt_f opt
  ON per.opt_id = opt.opt_id
  AND TRUNC(SYSDATE) BETWEEN opt.effective_start_date AND opt.effective_end_date
JOIN per_all_people_f pp
  ON per.person_id = pp.person_id
  AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date AND pp.effective_end_date
LEFT JOIN ben_dpnt_cvrd_dpnt_f dpnt
  ON per.prtt_enrt_rslt_id = dpnt.prtt_enrt_rslt_id
  AND TRUNC(SYSDATE) BETWEEN dpnt.effective_start_date AND dpnt.effective_end_date
LEFT JOIN per_all_people_f dp
  ON dpnt.dpnt_person_id = dp.person_id
  AND TRUNC(SYSDATE) BETWEEN dp.effective_start_date AND dp.effective_end_date
WHERE per.enrt_ovrid_reason IS NULL
  AND pl.plan_type = 'MEDICAL'
GROUP BY per.prtt_enrt_rslt_id, pp.person_number, pp.first_name, pp.last_name,
         pl.plan_name, opt.opt_name
ORDER BY pp.last_name

Query 6: Open Enrollment Status (Employees Not Yet Enrolled)

-- Find employees eligible for current OE but have NOT enrolled yet
SELECT DISTINCT
  pp.person_id,
  pp.person_number,
  pp.first_name,
  pp.last_name,
  paa.assignment_status_type_id,
  dorg.name AS department,
  'NOT ENROLLED' AS status
FROM per_all_people_f pp
JOIN per_all_assignments_f paa
  ON pp.person_id = paa.person_id
  AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
JOIN org_organization_definitions_f dorg
  ON paa.department_id = dorg.organization_id
  AND TRUNC(SYSDATE) BETWEEN dorg.effective_start_date AND dorg.effective_end_date
WHERE TRUNC(SYSDATE) BETWEEN pp.effective_start_date AND pp.effective_end_date
  AND pp.person_id NOT IN (
    SELECT DISTINCT person_id
    FROM ben_prtt_enrt_rslt_f
    WHERE enrt_ovrid_reason IS NULL
      AND TRUNC(SYSDATE) BETWEEN enrt_cvg_strt_dt AND enrt_cvg_end_dt
  )
ORDER BY dorg.name, pp.last_name

Query 7: Plan Comparison Report

SELECT
  pl.plan_name,
  pl.plan_type,
  opt.opt_name,
  CASE
    WHEN ort.rate_type = 'EMPLOYER_CONTRIBUTION' THEN 'Employer'
    WHEN ort.rate_type = 'EMPLOYEE_CONTRIBUTION' THEN 'Employee'
    ELSE ort.rate_type
  END AS contribution_type,
  ort.input_value_id AS monthly_amount,
  COUNT(DISTINCT per.person_id) AS current_enrollees
FROM ben_pl_f pl
JOIN ben_opt_f opt
  ON pl.pl_id = opt.pl_id
  AND TRUNC(SYSDATE) BETWEEN opt.effective_start_date AND opt.effective_end_date
LEFT JOIN ben_opt_rt_f ort
  ON opt.opt_id = ort.opt_id
  AND TRUNC(SYSDATE) BETWEEN ort.effective_start_date AND ort.effective_end_date
LEFT JOIN ben_prtt_enrt_rslt_f per
  ON opt.opt_id = per.opt_id
  AND per.enrt_ovrid_reason IS NULL
  AND TRUNC(SYSDATE) BETWEEN per.enrt_cvg_strt_dt AND per.enrt_cvg_end_dt
WHERE TRUNC(SYSDATE) BETWEEN pl.effective_start_date AND pl.effective_end_date
GROUP BY pl.plan_name, pl.plan_type, opt.opt_name, ort.rate_type, ort.input_value_id
ORDER BY pl.plan_type, pl.plan_name, opt.opt_name

Query 8: Benefits Eligibility Audit

-- Find employees enrolled in plans they are NOT eligible for (audit issue)
SELECT
  per.prtt_enrt_rslt_id,
  pp.person_number,
  pp.first_name,
  pp.last_name,
  pl.plan_name,
  pl.plan_type,
  per.elig_flag,
  'AUDIT ISSUE: Eligible flag = N' AS issue_type
FROM ben_prtt_enrt_rslt_f per
JOIN ben_pl_f pl
  ON per.pl_id = pl.pl_id
  AND TRUNC(SYSDATE) BETWEEN pl.effective_start_date AND pl.effective_end_date
JOIN per_all_people_f pp
  ON per.person_id = pp.person_id
  AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date AND pp.effective_end_date
WHERE per.enrt_ovrid_reason IS NULL
  AND per.elig_flag = 'N'
  AND TRUNC(SYSDATE) BETWEEN per.enrt_cvg_strt_dt AND per.enrt_cvg_end_dt
ORDER BY pp.last_name

Join Patterns & Best Practices

Benefits queries almost always start with BEN_PRTT_ENRT_RSLT_F (the enrollment fact table), then join to:

• BEN_PL_F on pl_id to get plan name and type
• BEN_OPT_F on opt_id to get option name (coverage level)
• PER_ALL_PEOPLE_F on person_id to get employee details
• PER_ALL_ASSIGNMENTS_F on person_id to get department and job
• BEN_OPT_RT_F on opt_id to get premium rates

Date-effective join pattern: Always use the BETWEEN clause on effective dates for all tables:

-- Date-effective join pattern (required for all BEN_ tables)
JOIN ben_pl_f pl
  ON per.pl_id = pl.pl_id
  AND TRUNC(SYSDATE) BETWEEN pl.effective_start_date AND pl.effective_end_date

-- For historical queries, use a specific date instead of SYSDATE:
JOIN ben_pl_f pl
  ON per.pl_id = pl.pl_id
  AND TO_DATE('2025-12-31', 'YYYY-MM-DD') BETWEEN pl.effective_start_date
    AND pl.effective_end_date

Frequently Asked Questions

Q: What's the difference between BEN_PRTT_ENRT_F and BEN_PRTT_ENRT_RSLT_F?

BEN_PRTT_ENRT_F is the enrollment master record (one row per employee per plan per year). BEN_PRTT_ENRT_RSLT_F is the enrollment result (one row per plan-option choice per employee). In practice, you query BEN_PRTT_ENRT_RSLT_F 99% of the time because you need to know which specific option (coverage level) the employee selected, not just that they're enrolled in a plan.

Q: How do I find which employees are NOT enrolled in any benefit plan?

Use the Query 6 example above (Open Enrollment Status). Alternatively, use an anti-join:

SELECT pp.person_id, pp.person_number
FROM per_all_people_f pp
WHERE TRUNC(SYSDATE) BETWEEN pp.effective_start_date AND pp.effective_end_date
  AND pp.person_id NOT IN (
    SELECT DISTINCT person_id
    FROM ben_prtt_enrt_rslt_f
    WHERE enrt_ovrid_reason IS NULL
      AND TRUNC(SYSDATE) BETWEEN enrt_cvg_strt_dt AND enrt_cvg_end_dt
  )

Q: Can an employee have multiple enrollments in the same plan?

No, not in the same plan year. But they can have multiple enrollments across different plan years (2025 coverage vs. 2026 coverage). In BEN_PRTT_ENRT_RSLT_F, you'll see multiple rows for the same employee-plan combo with different enrt_cvg_strt_dt/enrt_cvg_end_dt ranges. Use the WHERE clause to filter to the current period: WHERE enrt_ovrid_reason IS NULL AND TRUNC(SYSDATE) BETWEEN enrt_cvg_strt_dt AND enrt_cvg_end_dt.

Q: How do I calculate total benefits cost per employee?

Join BEN_PRTT_ENRT_RSLT_F to BEN_OPT_RT_F on opt_id. Sum the employee contribution amounts from BEN_OPT_RT_F.input_value_id where rate_type = 'EMPLOYEE_CONTRIBUTION'. Multiply by 12 for annual cost, or keep monthly. Use Query 4 above as a template.

Q: What does "enrt_ovrid_reason" mean when it's NULL?

NULL means the enrollment is active and not overridden. When enrt_ovrid_reason has a value (e.g., 'TERMINATION', 'BENEFIT_SUSPENSION'), that enrollment has been cancelled or superseded. Always filter for enrt_ovrid_reason IS NULL when querying current enrollments.

Q: How do I handle employees with multiple dependents on a single enrollment?

Each dependent is a row in BEN_DPNT_CVRD_DPNT_F, linked to the enrollment (prtt_enrt_rslt_id). Group or count by prtt_enrt_rslt_id to get dependent counts per enrollment. Use Query 5 above for a complete example with LISTAGG().