Results for “ben_elig_suppl_role_prte_f”

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

Overview

BEN_ELIG_SUPPL_ROLE_PRTE_F is a date-tracked (effective-dated) table in the BEN schema of Oracle Advanced Benefits (product code BEN), delivered in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores supplemental eligibility participation rules that restrict or prioritize eligibility based on job-related criteria, principally job and job group membership. It functions as one of the eligibility profile criteria tables that feed the Benefits eligibility engine, where the engine evaluates each candidate person's assignment against the rules defined in this table to determine benefit plan, program, or option eligibility.

Rows in this table are candidates for benefit programs and plans through the ELIGY_PRFL_ID column, which anchors each rule to a row in the eligibility profiles table. A given eligibility profile may therefore carry many supplemental role participation criteria rows, ordered by ORDR_NUM.

The heuristic Data Vault classification mined from the foreign key structure is standalone. That is a modeling suggestion rather than a statement of Oracle's internal design: the table does not participate in a conventional hub-and-link chain through the documented foreign keys, but it does carry a natural business key (the eligibility profile plus the effective date range) and a substantial descriptive payload, so a satellite-style treatment around that business key would also be defensible.

Key Information Stored

The primary key is BEN_ELIG_SUPPL_ROLE_PRTE_PK, composed of ELIG_SUPPL_ROLE_PRTE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The surrogate identifier is ELIG_SUPPL_ROLE_PRTE_ID; the effective-date pair supplies the temporal component of the key and enables date-tracked corrections and history.

• ELIGY_PRFL_ID — the eligibility profile to which the rule belongs; the principal business-key component linking to the profile definition.
• BUSINESS_GROUP_ID — the enterprise or business group that owns the row, providing the multi-tenant partitioning standard across HR and Benefits tables.
• JOB_ID — the specific job to which the participation rule applies.
• JOB_GROUP_ID — the job group that the rule references; documented as a foreign key to PER_JOB_GROUPS.
• EXCLD_FLAG — indicates whether the matched job or job group is included in or excluded from eligibility.
• ORDR_NUM — the evaluation sequence for the rule within the profile.
• CRITERIA_SCORE and CRITERIA_WEIGHT — the score and weighting assigned to the criterion when the profile is used in a scored or ranked eligibility model.
• EST_ATTRIBUTE_CATEGORY and EST_ATTRIBUTE1 through EST_ATTRIBUTE30 — the standard descriptive flexfield columns that hold installation-specific supplemental rule attributes.
• OBJECT_VERSION_NUMBER — the optimistic-locking column used by the forms and concurrent processing to detect conflicting updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, and CREATED_BY — the standard WHO audit columns.

Common Use Cases and Queries

Typical usage is diagnostic and reporting-oriented: identifying which job-based criteria an eligibility profile carries, and confirming whether a job or job group is included or excluded. A frequent pattern is a point-in-time check that joins the rule to its profile, as in the following:

• SELECT r.ELIG_SUPPL_ROLE_PRTE_ID, r.ELIGY_PRFL_ID, r.JOB_ID, r.JOB_GROUP_ID, r.EXCLD_FLAG, r.ORDR_NUM FROM BEN.BEN_ELIG_SUPPL_ROLE_PRTE_F r WHERE r.ELIGY_PRFL_ID = :p_profile_id AND TRUNC(SYSDATE) BETWEEN r.EFFECTIVE_START_DATE AND r.EFFECTIVE_END_DATE ORDER BY r.ORDR_NUM;
• Auditing configuration changes by comparing rows across overlapping effective-date ranges for the same ELIG_SUPPL_ROLE_PRTE_ID.
• Reviewing scored eligibility setups through CRITERIA_SCORE and CRITERIA_WEIGHT when a profile uses scored rather than binary inclusion logic.

Because the table is effective-dated, queries should always constrain SYSDATE (or the desired evaluation date) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to return only the currently valid definition of each rule.

Related Objects

The documented foreign key relationship points JOB_GROUP_ID to PER_JOB_GROUPS, making that table the most direct external reference for the job group side of the rule. On the Benefits side, ELIGY_PRFL_ID associates each row with the eligibility profile definition maintained through the profile criteria tables and forms in the BEN product. The row is grouped by BUSINESS_GROUP_ID, which aligns with the HR business group model used by PER_BUSINESS_GROUPS and related HR setup. The JOB_ID column corresponds to the HR jobs definition, conventionally PER_JOBS, and its job groups counterpart is PER_JOB_GROUPS. Collectively, the eligibility engine reads this table together with the other BEN eligibility criteria tables to resolve final eligibility for a person's assignment, so reporting on BEN_ELIG_SUPPL_ROLE_PRTE_F is normally performed alongside the eligibility profile, job, and job group definitions rather than in isolation.