Results for “ben_prtn_elig_prfl_f”

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

Overview

BEN_PRTN_ELIG_PRFL_F is a date-tracked (effective-dated) table in the BEN schema of Oracle E-Business Suite, belonging to the Advanced Benefits (BEN) product. As documented in the ETRM 12.2.2 physical schema, the table stores "profiles which build up eligibility criteria" — that is, the reusable participation eligibility profile definitions that are attached to benefit programs, plans, and plan types in order to determine who qualifies for a given offering. The "_F" suffix denotes the primary, non-translated entity table that carries the effective start and end dates used throughout Oracle Advanced Benefits for date-effective (datetrack) processing.

Because the metadata exposes no foreign keys into or out of this object, the heuristic Data Vault classification mined from the FK structure is standalone. As a modeling suggestion, this is best treated either as its own hub-and-satellite pair keyed on PRTN_ELIG_PRFL_ID (with the effective dates forming a historized satellite) or as a standalone reference/dimension entity, rather than as a link between two parent hubs. The absence of documented foreign keys reflects the fact that Advanced Benefits frequently enforces relationships through application logic and public APIs rather than hard referential constraints.

Key Information Stored

The primary key is BEN_PRTN_ELIG_PRFL_F_PK, composed of PRTN_ELIG_PRFL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. PRTN_ELIG_PRFL_ID is the surrogate identifier for the eligibility profile — it is the value most often searched for as "prtn_elig_prfl_id" and the value referenced by child and dependent objects. The composite of the surrogate key with the effective dates is the only documented unique index, so it is the sole business-key candidate recorded; no separate natural-key uniqueness is documented in the metadata.

The most significant remaining columns fall into several groups:

• Relationship/context columns: ELIGY_PRFL_ID (the underlying eligibility profile definition), PRTN_ELIG_ID (the participation eligibility record to which the profile contributes), and BUSINESS_GROUP_ID, which scopes the row to a specific business group for multi-tenant style separation.
• Behavioral control columns: MNDTRY_FLAG (whether the profile is mandatory for the associated eligibility rule), COMPUTE_SCORE_FLAG (whether a score is computed for the profile), and ELIG_PRFL_TYPE_CD (the profile type code that governs how it is evaluated).
• Descriptive flexfield columns: CEP_ATTRIBUTE_CATEGORY plus CEP_ATTRIBUTE1 through CEP_ATTRIBUTE30, providing the context-sensitive extensibility used for customer-specific eligibility profile attributes.
• Standard WHO/audit and concurrency columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER, the last supporting optimistic locking for the Applications framework.

Common Use Cases and Queries

Typical scenarios include auditing which eligibility profiles are active for a business group on a given date, resolving the surrogate id before joining to dependent eligibility criteria, and extracting profile definitions for reporting or migration. Because the table is effective-dated, nearly every query must restrict on date ranges to return the single correct version. A representative pattern resolving the current version of a profile is:

• SELECT prtn_elig_prfl_id, eligy_prfl_id, elig_prfl_type_cd, mndtry_flag, compute_score_flag FROM ben.ben_prtn_elig_prfl_f WHERE prtn_elig_prfl_id = :p_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Filtering by business group and profile type: ... WHERE business_group_id = :bg_id AND elig_prfl_type_cd = :type_cd AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Auditing full history for a profile by removing the date predicate and ordering by effective_start_date to inspect changes over time.

Reporting use cases include eligibility profile inventories, mandatory-profile validation, and score-computation audits where COMPUTE_SCORE_FLAG and the CEP_ATTRIBUTE flexfield values drive downstream scoring rules.

Related Objects

The metadata documents this object as standalone with no explicit foreign keys, so the relationships below reflect the logical BEN Advanced Benefits model keyed on the shared identifiers present in the column list:

• BEN_ELIGY_PRFL_F — the eligibility profile definition referenced by ELIGY_PRFL_ID, the principal parent in the eligibility model.
• BEN_PRTN_ELIG_F — the participation eligibility entity linked through PRTN_ELIG_ID.
• BEN_PRTN_ELIG_PRFL — the view layer commonly used for user-facing queries over the profile rows.
• BEN_ELIG_PRFL_TL — translated profile names and descriptions joined via ELIGY_PRFL_ID.
• BEN_PRTN_ELIG_PRFL_CRIT — the criteria lines that build up the actual eligibility conditions for each profile.
• BEN_ELIG_PRFL_RLSHP_F — relationship-based eligibility rules associated with the profile.
• BEN_PGM_F / BEN_PL_F — programs and plans that consume eligibility profiles when participation is evaluated.
• Public APIs such as ben_eligy_prfl_api and ben_prtn_elig_prfl_api, which create and maintain profile rows without direct DML.