Search Results stat_cd




Overview
Explore in AI Chat

Overview

BEN_ELIGY_PRFL_X is an APPS-owned view in the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite. It exposes the eligibility profile definition, including its effective-dated header attributes and the full set of eligibility criteria flags that determine which participants qualify for a given benefits offering. The "_X" suffix is a standard EBS convention denoting a view that has been retrofitted for multi-organization or localization purposes, typically layering row identification and additional derived columns over a base _F (fact) table. In the documented 12.2.2 metadata the view is reported as VALID and is defined over BEN_ELIGY_PRFL_F, exposed through an APPS synonym. Because the view surfaces the complete criteria flag matrix in a single, denormalized record, it functions as the natural query point for reporting, data extracts, and integrations that need to assess or reproduce eligibility profile logic without navigating the multiple child criteria tables (eligibility profile relations, benefit group, organization unit, and similar detail entities).

Underlying Base Objects

The documented referenced base object is BEN_ELIGY_PRFL_F, the effective-dated fact table storing eligibility profile definitions in Oracle Advanced Benefits. The view text confirms a direct, nearly one-to-one projection from this base table, aliased as ELP, returning the row identifier (ROWID) as ROW_ID alongside the primary key ELIGY_PRFL_ID and the effective date range columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. Because the view selects from the _F table rather than a _TL or _VL companion, it does not perform translation joins; only the base descriptive columns NAME and DESCRIPTION are exposed. The presence of the ROW_ID pseudocolumn is characteristic of a retrofit view and supports downstream processing that requires a stable physical row reference. All business group scoping is retained through BUSINESS_GROUP_ID, and the descriptive flexfield structure is preserved via ELP_ATTRIBUTE_CATEGORY and the thirty ELP_ATTRIBUTE1 through ELP_ATTRIBUTE30 columns.

Key Columns

The primary identifier is ELIGY_PRFL_ID, combined with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE for date-effective queries. NAME and DESCRIPTION carry the user-facing profile label. The criteria flags indicate which eligibility determination method is active for the profile. The user-searched column ELIG_BENFTS_GRP_FLAG corresponds to the benefits group criterion, exposed in the view as ELIG_BENFTS_GRP_FLAG; note that the term as typed ("elig_benfts_grp_flag") is a close variant of the actual column name and resolves to this benefits group indicator. Other representative flags include ELIG_ENRLD_PLIP_FLAG, ELIG_ENRLD_PTIP_FLAG, ELIG_DPNT_CVRD_PLIP_FLAG, ELIG_DPNT_CVRD_PTIP_FLAG, ELIG_DPNT_CVRD_PGM_FLAG, ELIG_JOB_FLAG, ELIG_HRLY_SLRD_FLAG, ELIG_PSTL_CD_FLAG, ELIG_LBR_MMBR_FLAG, ELIG_LGL_ENTY_FLAG, ELIG_WK_LOC_FLAG, ELIG_BRGNG_UNIT_FLAG, ELIG_AGE_FLAG, ELIG_LOS_FLAG, ELIG_PER_TYP_FLAG, ELIG_EE_STAT_FLAG, ELIG_GRD_FLAG, ELIG_HRS_WKD_FLAG, ELIG_COMP_LVL_FLAG, ELIG_ORG_UNIT_FLAG, ELIG_LOA_RSN_FLAG, ELIG_PYRL_FLAG, ELIG_SCHEDD_HRS_FLAG, ELIG_PY_BSS_FLAG, ELIG_CMBN_AGE_LOS_FLAG, ELIG_PPL_GRP_FLAG, and ELIG_PRTT_PL_FLAG. Audit columns include LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER. The flags are typically stored as Y/N indicators that gate whether a corresponding criterion is evaluated for enrollment or coverage.

Common Use Cases and Queries

Typical uses include auditing which criteria a profile activates, extracting profile definitions for interfaces or comparison across environments, and validating data migration. A representative query listing active profiles and their benefits group flag follows:

  • SELECT eligy_prfl_id, name, effective_start_date, effective_end_date, elig_benfts_grp_flag, elig_age_flag, elig_los_flag FROM apps.ben_eligy_prfl_x WHERE business_group_id = :p_bg_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • SELECT eligy_prfl_id, name FROM apps.ben_eligy_prfl_x WHERE elig_benfts_grp_flag = 'Y' AND stat_cd = 'A';
  • SELECT p.name, p.elig_org_unit_flag, p.elig_wk_loc_flag FROM apps.ben_eligy_prfl_x p WHERE p.eligy_prfl_id = :p_profile_id AND :p_effective_date BETWEEN p.effective_start_date AND p.effective_end_date;

Because the view is read-oriented and derived directly from the base fact table, it should not be used as a DML target; profile maintenance is performed through the Advanced Benefits setup forms or the underlying BEN_ELIGY_PRFL_F API.