Results for “ben_elig_age_cvg_f”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BEN_ELIG_AGE_CVG_F is a transactional configuration table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, released under the BEN schema. It stores the age-related eligibility requirements that govern when a dependent may be covered under a specific compensation object, such as a benefit plan, program, or option. In practical terms, the table defines the age thresholds and coverage start and end rules that drive dependent eligibility processing during enrollment and life-event evaluation.

The table is date-tracked: every logical row is qualified by an EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, so historical configurations are preserved and eligibility can be re-derived as of any point in time. This is essential in benefits administration, where plan rules change annually and enrollments must be evaluated against the rules in force on the event date.

From a Data Vault modeling perspective, the mined FK structure suggests a satellite-leaning classification. The table carries a foreign key to BEN_AGE_FCTR and is bounded by a composite effective-dated primary key, which is characteristic of a satellite that records descriptive, time-variant attributes around a parent business key rather than a hub or a link. This is offered as a modeling suggestion only; the physical implementation is a standard EBS dated table, not a Data Vault construct.

Key Information Stored

The documented schema comprises 48 columns. The most operationally significant are listed below.

• ELIG_AGE_CVG_ID — surrogate identifier for the age-coverage rule; the first component of the composite primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-track the validity window of each configuration row and complete the primary key.
• BUSINESS_GROUP_ID — the HR business group that owns the rule, providing multi-tenant separation.
• AGE_FCTR_ID — foreign key to BEN_AGE_FCTR, the age factor that supplies the actual age calculation underlying the requirement.
• DPNT_CVG_ELIGY_PRFL_ID — references the dependent coverage eligibility profile to which this age rule belongs.
• CVG_STRT_RL / CVG_STRT_CD — the rule and code that determine when dependent coverage begins relative to the qualifying event.
• CVG_THRU_RL / CVG_THRU_CD — the rule and code that determine when dependent coverage ends, typically age-driven.
• EXCLD_FLAG — indicates whether the rule excludes, rather than includes, the dependent from coverage.
• EAC_ATTRIBUTE_CATEGORY and EAC_ATTRIBUTE1–EAC_ATTRIBUTE30 — a descriptive flexfield (DFF) block allowing customers to capture installation-specific attributes without schema changes.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the BEN APIs and concurrent programs.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS audit and WHO columns.

The unique index BEN_ELIG_AGE_CVG_F_PK (ELIG_AGE_CVG_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the documented business-key candidate. ELIG_AGE_CVG_ID behaves as the surrogate key; the effective dates provide the temporal qualifier.

Common Use Cases and Queries

Typical use cases include auditing which age rules are attached to a dependent eligibility profile, validating coverage-through rules before an open enrollment window, and replicating plan configuration across business groups. A common query pattern restricts to the currently effective row:

• SELECT e.elig_age_cvg_id, e.age_fctr_id, e.cvg_thru_cd, e.cvg_thru_rl FROM ben.ben_elig_age_cvg_f e WHERE TRUNC(SYSDATE) BETWEEN e.effective_start_date AND e.effective_end_date AND e.dpnt_cvg_eligy_prfl_id = :p_profile_id;

Reporting scenarios frequently join the rule to the age factor and to the eligibility profile, and extract the DFF columns for country-specific compliance reporting. Because the table is date-tracked, "as-of" reporting requires filtering on the effective window or using BETWEEN clauses rather than assuming a single current row.

Related Objects

• BEN_AGE_FCTR — referenced through BEN_ELIG_AGE_CVG_F.AGE_FCTR_ID; supplies the age calculation that the rule compares against.
• BEN_DPNT_CVG_ELIGY_PRFL — the dependent coverage eligibility profile owning this rule (via DPNT_CVG_ELIGY_PRFL_ID).
• BEN_ELIG_AGE_CVG_F_PK — the primary key index that enforces uniqueness of the surrogate key plus effective dates.
• Advanced Benefits setup and enrollment concurrent programs and the enrollment APIs that read BEN_ELIG_AGE_CVG_F when evaluating dependent eligibility.
• The standard WHO audit columns link the row to FND_USER and related audit views maintained by EBS.

Because BEN_ELIG_AGE_CVG_F is a configuration satellite beneath the age factor and eligibility profile parents, changes should be made through the Advanced Benefits setup forms or supported APIs so that effective dating and object-version integrity are preserved.