Results for “ben_elig_ee_stat_prte_f”

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

Overview

BEN.BEN_ELIG_EE_STAT_PRTE_F is a date-tracked (effective-dated) table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It identifies which employee statuses are either included in or excluded from a given compensation object eligibility profile. In practical terms, it allows an eligibility profile to discriminate participants based on their assignment status—active, terminated, leave of absence, and similar categories defined in Oracle HRMS. Rows in this table act as eligibility criteria components, joined to a parent eligibility profile through ELIGY_PRFL_ID, and are evaluated by the Benefits eligibility engine when determining whether a person qualifies for a program, plan, or option.

The table carries 47 columns in the documented 12.2.2 schema. Its primary key is BEN_ELIG_EE_STAT_PRTE_F_PK, composed of ELIG_EE_STAT_PRTE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE—the standard Oracle date-effective pattern (_F suffix). The metadata's heuristic Data Vault classification is standalone, meaning the mined FK structure does not clearly resolve it to a hub, link, or satellite. As a modeling suggestion, this table behaves most like a satellite attached to the eligibility-profile hub, capturing descriptive attributes over time; the assignment status reference behaves as a lookup link. Treat that classification as advisory rather than definitive.

Key Information Stored

The most significant columns fall into identity, eligibility logic, and audit groups:

• ELIG_EE_STAT_PRTE_ID — surrogate system-generated identifier and the leading component of the primary key. This is the column most commonly returned when users search on "elig_ee_stat_prte_id." It is a meaningless internal key; joins to the parent profile use ELIGY_PRFL_ID.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracking range, also part of the unique business key. Every query should filter on SYSDATE between these dates to retrieve the current rule version.
• ELIGY_PRFL_ID — foreign reference to the parent eligibility profile. This is the practical business key linking the row to BEN_ELIGY_PRFL_F.
• ASSIGNMENT_STATUS_TYPE_ID — the sole documented foreign key, referencing PER_ASSIGNMENT_STATUS_TYPES. It names the assignment status being tested (for example, Active, Terminated, Suspended).
• EXCLD_FLAG — determines whether matching participants are included or excluded, allowing the same status list to be reused in either polarity.
• BUSINESS_GROUP_ID — multi-tenant discriminator separating enterprise data sets; it must typically be included in queries for correct row scoping.
• ORDR_NUM, CRITERIA_SCORE, CRITERIA_WEIGHT — control the evaluation sequence and scoring of the criterion when multiple eligibility rules compete or are ranked.
• EES_ATTRIBUTE_CATEGORY and EES_ATTRIBUTE1–30 — a descriptive flexfield (DFF) block for customer-defined extensions, often used to hold additional status-related qualifiers.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard Oracle WHO/audit columns and the optimistic-locking version number.

Common Use Cases and Queries

The primary use case is diagnosis and reporting of eligibility: consultants trace why a participant was included or excluded from a plan, and HR analysts report which statuses gate each program. A representative query for current rules:

• Select all data rows for the current date and instance, filtered to the entity, instance, and the identifier. Join ASSIGNMENT_STATUS_TYPE_ID to PER_ASSIGNMENT_STATUS_TYPES to resolve the readable status name.
• Retrieve exclusion-only rules by adding WHERE EXCLD_FLAG = 'Y'.
• Correlate to the parent profile by joining ELIGY_PRFL_ID to BEN_ELIGY_PRFL_F.ELIGY_PRFL_ID, again applying the effective-date predicate on both sides.
• Because the table is date-effective, historical point-in-time reports require an explicit date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE rather than a simple equality filter on the ID.

Typical reporting outputs include an eligibility-criteria matrix showing profile, status, inclusion/exclusion flag, and order number, which supports audits and configuration migration comparisons between environments.

Related Objects

The following are the most significant related objects and join paths:

• BEN_ELIGY_PRFL_F — parent eligibility profile; joined via ELIGY_PRFL_ID.
• PER_ASSIGNMENT_STATUS_TYPES — the documented foreign key target; joined via ASSIGNMENT_STATUS_TYPE_ID to obtain status names and multilingual text.
• BEN_ELIG_EE_STAT_PRTE — the non-date-effective or corresponding identifier view/table commonly paired with the _F table in BEN's two-table date-effective pattern.
• BEN_ELIG_*_PRTE_F tables — sibling criterion tables (for example, those keyed to people groups, organizations, or other criteria) sharing the same ELIGY_PRFL_ID parent pattern.
• BEN_ELIGY_PRFL and related eligibility views — the API/denormalized layer used by the Benefits eligibility engine and by concurrent programs that evaluate participant eligibility.

These joins allow complete reconstruction of an eligibility profile's criteria set for troubleshooting, migration, and audit purposes.