Results for “ben_elig_mrtl_sts_prte_f”

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

Overview

BEN_ELIG_MRTL_STS_PRTE_F is a datable (effective-dated) table in the BEN schema of Oracle E-Business Suite Advanced Benefits. It stores eligibility criteria records in which marital status is the determining factor for whether a person, or the dependents and beneficiaries tied to that person, qualifies for a particular benefit plan, program, or eligibility profile. The "PRTE" suffix denotes participation/eligibility criteria, and the "_F" suffix identifies the table as a date-tracked (effective-dated) entity, meaning every logical criteria row is versioned across EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.

Within the benefits eligibility engine, this table participates in the evaluation logic that Advanced Benefits applies when a life event, open enrollment, or periodic eligibility reevaluation is executed. Criteria rows are evaluated against a participant's marital status and, optionally, against flexfield-defined employee attributes. When the criteria are satisfied, the associated eligibility profile either qualifies or disqualifies the person for the linked plan or program.

From a Data Vault modeling perspective, the metadata classifies this object as standalone, with no mined foreign-key dependencies. In practice it is best treated as a satellite attached to a parent eligibility profile hub, since its grain is criteria rows identified by a surrogate key plus effective dates rather than a business transaction.

Key Information Stored

• ELIG_MRTL_STS_PRTE_ID — Surrogate primary key for the marital status criteria row. Part of BEN_ELIG_MRTL_STS_PRTE_PK together with the effective dates.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-tracked validity window forming the remainder of the composite primary key; the same logical criteria can have multiple dated versions.
• ELIGY_PRFL_ID — Identifier of the parent eligibility profile to which the criteria belong.
• BUSINESS_GROUP_ID — Owning business group, providing multi-tenant segregation of benefits configuration.
• MARITAL_STATUS — The core criterion value: the marital status against which a participant is evaluated.
• EXCLD_FLAG — Exclusion indicator that reverses the criteria into an exclude rule.
• EMP_ATTRIBUTE_CATEGORY and EMP_ATTRIBUTE1 through EMP_ATTRIBUTE30 — Descriptive flexfield segments capturing supplementary employee attributes used alongside marital status.
• CRITERIA_SCORE / CRITERIA_WEIGHT — Numeric scoring and weighting values applied during criteria evaluation.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/forms layer.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard audit columns.

The unique index BEN_ELIG_MRTL_STS_PRTE_PK (ELIG_MRTL_STS_PRTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the documented business-key candidate. There is no documented natural-key unique index beyond the surrogate plus dates.

Common Use Cases and Queries

Typical reporting asks which marital status values qualify or exclude participants for a given profile. A query joining to the eligibility profile header resolves the profile name against the stored criteria:

• Identify active criteria rows for a profile by filtering on SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE.
• Extract all married-status criteria with EXCLD_FLAG = 'N' to audit which profiles automatically admit married employees.
• Compare dated versions of the same ELIG_MRTL_STS_PRTE_ID to reconstruct configuration history for audit or change-tracking reports.
• Reconcile configuration with employee flexfield attributes across EMP_ATTRIBUTE1..30 to explain unexpected eligibility outcomes.
• Feed downstream data warehouse eligibility dimensions using ELIGY_PRFL_ID and MARITAL_STATUS as conformed keys.

Related Objects

• BEN_ELIG_PRFL_F — Parent eligibility profile header, joined on ELIGY_PRFL_ID = ELIG_PRFL_ID.
• BEN_ELIG_PRFL_F (profile versions) — Provides effective-dated context for the criteria rows.
• BEN_PGM_F / BEN_PL_F — Programs and plans that consume the eligibility profile.
• PER_PERSON_TYPES / PER_ALL_PEOPLE_F — Source of participant marital status and personal attributes during evaluation.
• BEN_ELIG_*_PRTE_F tables — Sibling criteria tables (age, compensation, employment, etc.) evaluated in the same eligibility pass.
• BEN_ELIG_F / eligibility engine PL/SQL APIs — Runtime routines that read these criteria rows during enrollment.

Because the metadata records no mined foreign keys, joins to the above objects should be validated against the actual parent-column definitions in the target environment before use in production SQL.