Search Results ben_eligy_prfl_f

Overview

BEN_ELIGY_PRFL_F is the core Advanced Benefits (BEN) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores eligibility profiles. An eligibility profile is the rule definition that determines whether a person, dependent, or other compensation object qualifies for a given benefit offering, plan, program, or compensation object. Each row in this table represents a logical eligibility profile as of a specific effective date range, expressed through the effective-dated key ELIGY_PRFL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The table is defined in the BEN schema and contains 111 documented columns in ETRM 12.2.2.

Because eligibility profiles are reused across plans, programs, and plan types, this table functions as a central reference for the benefits eligibility engine. The many ELIG_*_FLAG columns indicate which eligibility criteria types are enabled for a given profile, while the paired detail tables hold the actual criterion values. From a Data Vault modeling perspective, the heuristic classification is standalone, meaning it does not participate in a documented foreign-key hub or link relationship. In practice it behaves as an effective-dated reference/satellite-style structure keyed by the profile identifier.

Key Information Stored

The table is defined by the primary key BEN_ELIGY_PRFL_F_PK, which uniquely identifies each effective-dated version of a profile:

• ELIGY_PRFL_ID — surrogate identifier for the eligibility profile; also the leading column of the primary key and the business-key candidate.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the DateTrack effective range that completes the primary key and enables historical tracking.
• NAME — user-visible profile name presented in the forms and LOVs.
• BUSINESS_GROUP_ID — the HR business group that owns the profile.
• STAT_CD — status code controlling whether the profile is active.
• DESCRIPTION — free-text explanation of the profile's purpose.
• ASG_TYP_CD / ASMT_TO_USE_CD — assignment type and the assessment method used to evaluate eligibility.
• ELIG_ENRLD_PLIP_FLAG, ELIG_ENRLD_PTIP_FLAG, ELIG_DPNT_CVRD_PTIP_FLAG — enrollment and dependent-coverage trigger flags that scope how the profile is applied.
• ELIG_AGE_FLAG, ELIG_LOS_FLAG, ELIG_HRS_WKD_FLAG, ELIG_COMP_LVL_FLAG — flags indicating which criteria categories (age, length of service, hours worked, compensation level) are active.
• ELIG_PER_TYP_FLAG, ELIG_JOB_FLAG, ELIG_GRD_FLAG, ELIG_ORG_UNIT_FLAG — flags for person type, job, grade, and organization-based criteria.

Surrogate identity is carried by ELIGY_PRFL_ID; the unique index BEN_ELIGY_PRFL_F_PK (ELIGY_PRFL_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the effective-dated business key candidate. Standard WHO/audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) and ELP_ATTRIBUTE1 through ELP_ATTRIBUTE30 are also present for auditing and extensibility.

Common Use Cases and Queries

Typical scenarios include confirming which profiles exist for a business group, determining the criteria enabled for a profile as of a date, and checking effective-dating history.

• List active profiles in a business group:

  SELECT eligy_prfl_id, name, stat_cd, effective_start_date, effective_end_date
  FROM   ben.ben_eligy_prfl_f
  WHERE  business_group_id = :p_bg_id
  AND    SYSDATE BETWEEN effective_start_date AND effective_end_date
  ORDER  BY name;

• Identify profiles that consider age or length of service:

  SELECT eligy_prfl_id, name
  FROM   ben.ben_eligy_prfl_f
  WHERE  elig_age_flag = 'Y'
  AND    elig_los_flag = 'Y';

• Retrieve effective-dated history for a single profile:

  SELECT eligy_prfl_id, effective_start_date, effective_end_date, name, stat_cd
  FROM   ben.ben_eligy_prfl_f
  WHERE  eligy_prfl_id = :p_profile_id
  ORDER  BY effective_start_date;

Reporting commonly joins this table to the criteria detail tables to render a human-readable eligibility rule, and to benefit plan and program tables to show where a profile is applied.

Related Objects

The metadata classifies this object as standalone, so no FK relationships are documented; the following are the principal related objects based on the Advanced Benefits data model.

• BEN_ELIGY_PRFL — non-dated view over BEN_ELIGY_PRFL_F, keyed by ELIGY_PRFL_ID; the most common access path.
• BEN_ELIG_DPNT_CVRD_OTHR_F and other BEN_ELIG_*_F criterion tables — joined on ELIGY_PRFL_ID and matching effective dates to supply the actual eligibility values.
• BEN_PL_F / BEN_PGM_F / BEN_PLIP_F / BEN_PTIP_F — plan, program, plan-in-program, and plan-type-in-program definitions that reference eligibility profiles by ELIGY_PRFL_ID.
• BEN_ELIGY_PRFL_RL — stores related profile rules (for example, profile inclusion or exclusion) using ELIGY_PRFL_ID.
• BEN_PRTT_ENRT_RSLT_F — participant enrollment results, where eligibility outcomes are recorded against the profile evaluated.
• FND_LOOKUPS / BEN_*_API — lookups and public APIs used to create and validate eligibility profiles programmatically.