Results for “ben_elig_ee_stat_prte”

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

Overview

The BEN_ELIG_EE_STAT_PRTE view is a reporting-layer object in the Oracle E-Business Suite Advanced Benefits (BEN) module. It is owned by the APPS schema and is classified as a valid, retrofitted view in release 12.1.1 and 12.2.2. In Oracle Advanced Benefits, an eligibility profile determines which employees qualify for a given program, plan, or option. A single eligibility profile is composed of one or more participation criteria rules. The EE_STAT_PRTE component represents the "Employee Status" criterion type, capturing assignment status based eligibility rules. This view exposes those employee-status criteria records in a date-effective, session-aware form, making it suitable for concurrent programs, PL/SQL APIs, OAF/Forms data sources, and ad hoc SQL reporting against the eligibility engine.

The "Retrofitted" designation indicates that the object was reworked to conform to the standard EBS date-tracked, multi-tenant aware architecture, providing effective dating, audit columns, and an OBJECT_VERSION_NUMBER for optimistic locking.

Underlying Base Objects

The view is defined over two referenced base objects, both exposed through APPS synonyms:

• BEN_ELIG_EE_STAT_PRTE_F — the _F (frozen/final) date-tracked table that stores the actual employee-status eligibility participation criteria rows. The view aliases this table as EES and selects all of its business, attribute, and audit columns.
• FND_SESSIONS — the standard EBS session table, used to obtain the current effective date for the active user session.

Critically, the view is not a simple passthrough. It applies the canonical EBS date-effectiveness filter, restricting rows to those whose EFFECTIVE_START_DATE is on or before, and whose EFFECTIVE_END_DATE is on or after, the session's effective date obtained from FND_SESSIONS via USERENV('SESSIONID'). This means the view always returns the single currently-effective version of each employee-status criterion, depending on the effective date the user has chosen at sign-on. This date-tracking pattern is the standard mechanism used throughout Advanced Benefits to guarantee point-in-time accuracy.

Key Columns

• ELIG_EE_STAT_PRTE_ID — Primary key identifying the employee-status criterion record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective range governing when the criterion row is valid; enforced by the session filter.
• BUSINESS_GROUP_ID — Operating unit/business group that owns the record, supporting multi-tenant data partitioning.
• ELIGY_PROFILE_ID — The parent eligibility profile to which this criterion belongs.
• ASSIGNMENT_STATUS_TYPE_ID — Foreign key to the assignment status type that this criterion tests against, e.g., Active, Leave of Absence, Terminated. This is the defining attribute of an employee-status participation criterion.
• EXCLD_FLAG — Indicates whether the criterion includes or excludes eligible persons (Y = exclusion).
• ORDR_NUM — Ordering sequence controlling the evaluation order of criteria within the profile.
• EES_ATTRIBUTE_CATEGORY and EES_ATTRIBUTE1..30 — Descriptive flexfield (DFF) columns for customer-defined attributes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — Standard audit and concurrency-control columns.

Common Use Cases and Queries

The view is typically queried to audit or report on the eligibility rules attached to benefit programs and plans. Because it is session-filtered, it returns only the currently-effective version of each criterion, making it ideal for validation and troubleshooting.

Listing all employee-status criteria for a given eligibility profile:

• SELECT e.elig_ee_stat_prte_id, e.eligy_profile_id, e.assignment_status_type_id, e.excld_flag, e.ordr_num
• FROM apps.ben_elig_ee_stat_prte e
• WHERE e.eligy_profile_id = :p_profile_id
• ORDER BY e.ordr_num;

Joining to the assignment status type to render readable rule descriptions for an eligibility profile audit report:

• SELECT ep.name profile_name, ast.user_status_name, e.excld_flag
• FROM apps.ben_elig_ee_stat_prte e, apps.ben_eligy_prfl ep, apps.per_assignment_status_types ast
• WHERE e.eligy_profile_id = ep.eligy_profile_id
• AND e.assignment_status_type_id = ast.assignment_status_type_id
• AND ep.business_group_id = :p_bg_id;

Integrators extracting eligibility configuration for downstream systems should join to BEN_ELIGY_PRFL and the associated program/plan relations, and should be aware that, owing to the FND_SESSIONS filter, the effective date of the querying session determines the rows returned. Running such extracts against a controlled session date is recommended to obtain reproducible, point-in-time output.