Results for “ben_elig_ee_stat_prte_x”

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

Overview

BEN_ELIG_EE_STAT_PRTE_X is a public view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the BEN (Advanced Benefits) product family and is designated in ETRM as a retrofitted object, meaning it was introduced or reworked during the transition from the legacy Oracle HRMS Benefits architecture to the newer Advanced Benefits (OAB) model to preserve backward-compatible access paths for dependent forms, reports, and integrations.

The view exposes the eligibility participant record for the employee assignment status eligibility criterion type — that is, the rule that determines whether a person is eligible for a benefit program or plan based on the status of their assignment (for example, Active, Leave of Absence, or Terminated). It provides a current, effective-dated snapshot of these eligibility rules by filtering the underlying base table to rows valid as of the current system date, making it suitable for reporting, PL/SQL lookups, and inbound/outbound interface logic. The user search term "ees_attribute1" corresponds directly to the EES_ATTRIBUTE1 column, part of the descriptive flexfield (DFF) segment set attached to each eligibility status participation row.

Underlying Base Objects

ETRM documents the view as being defined over a single base object: the synonym BEN_ELIG_EE_STAT_PRTE_F, which resolves to the APPS base table holding the physical eligibility participation records for the employee assignment status criterion. The view is a thin projection, not a join: it selects every column from the base table and applies a single filter predicate:

• WHERE TRUNC(SYSDATE) BETWEEN EES.EFFECTIVE_START_DATE AND EES.EFFECTIVE_END_DATE — restricts output to rows whose effective date range brackets the current date.

Because the _F (base/full) table retains all dated versions of a row, the _X view effectively acts as the "current effective" access layer. Inserts, updates, and deletes must be directed at the base table; the view is read-only. DateTrack-aware logic that needs a date other than SYSDATE must query the base table directly, since the view cannot be date-tracked.

Key Columns

The view exposes the following significant columns:

• ELIG_EE_STAT_PRTE_ID — primary key of the eligibility participation row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — DateTrack effective range governing which rows are returned by the view.
• BUSINESS_GROUP_ID — the business group (typically the enterprise's HR organization) that owns the rule.
• ELIGY_PRFL_ID — foreign key to the eligibility profile that contains this criterion.
• EXCLD_FLAG — indicates whether the record is an inclusion (N) or exclusion (Y) rule within the profile.
• ORDR_NUM — sequence in which this criterion is evaluated relative to others in the profile.
• ASSIGNMENT_STATUS_TYPE_ID — the assignment status being tested by the rule (Active, Suspended, Terminated, and so on).
• EES_ATTRIBUTE_CATEGORY through EES_ATTRIBUTE30 — the descriptive flexfield context and 30 attribute segments used for customer-defined extensions on the eligibility status participation record. EES_ATTRIBUTE1 is the first of these segments.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard WHO audit columns and optimistic locking version number.

Common Use Cases and Queries

Typical applications include auditing which assignment statuses are used in eligibility profiles, extracting current eligibility rules for data conversion or interfaces, and diagnosing why a participant was included or excluded from a program.

Retrieve all current employee assignment status eligibility rules for a business group:

• SELECT elig_ee_stat_prte_id, eligy_prfl_id, assignment_status_type_id, excld_flag, ordr_num FROM ben_elig_ee_stat_prte_x WHERE business_group_id = :p_bg_id ORDER BY eligy_prfl_id, ordr_num;

Filter on the descriptive flexfield segment:

• SELECT elig_ee_stat_prte_id, eligy_prfl_id, ees_attribute1 FROM ben_elig_ee_stat_prte_x WHERE ees_attribute_category = 'MY_CTX' AND ees_attribute1 = :p_value;

Join to the eligibility profile to see profile names alongside the status criterion:

• SELECT x.elig_ee_stat_prte_id, p.eligy_prfl_nm, ast.user_status_name, x.excld_flag FROM ben_elig_ee_stat_prte_x x, ben_eligy_prfl p, per_assignment_status_types ast WHERE x.eligy_prfl_id = p.eligy_prfl_id AND x.assignment_status_type_id = ast.assignment_status_type_id;

Because the view is filtered on TRUNC(SYSDATE), any query requiring a historical or future date must instead select from BEN_ELIG_EE_STAT_PRTE_F with an explicit date predicate on EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.