Results for “ben_elig_comptncy_prte_f”

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

Overview

BEN_ELIG_COMPTNCY_PRTE_F is a date-tracked (effective-dated) table in the BEN schema of Oracle Advanced Benefits (Oracle E-Business Suite 12.1.1 and 12.2.2). It stores the competency criteria that participate in a compensation object eligibility profile, determining whether an employee's competencies and rating levels cause that person to be included in or excluded from a compensation object such as a plan, program, or option. The "PRTE" suffix denotes a participation/eligibility rule detail table, and the trailing "_F" denotes the date-effective (non-translated) variant. The table is therefore a rule-detail table beneath the eligibility profile framework rather than a transactional table.

The heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning the table has no parent-key dependency on other BEN eligibility tables through declared constraints; in Data Vault modeling terms it is best treated as a satellite whose parent hub would be an eligibility-profile business key, with PER_COMPETENCES and PER_RATING_LEVELS acting as reference links. This classification is a modeling suggestion only and does not change the physical design.

Key Information Stored

The table contains 48 documented columns. The most significant are:

• ELIG_COMPTNCY_PRTE_ID — system-generated surrogate identifier for the rule row; part of the composite primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective window; both are part of the primary key, giving the business key a temporal dimension.
• ELIGY_PRFL_ID — the eligibility profile to which this competency rule belongs, linking the rule to its parent profile.
• COMPETENCE_ID — foreign key to PER_COMPETENCES, identifying the competency being tested.
• RATING_LEVEL_ID — foreign key to PER_RATING_LEVELS, identifying the required rating level.
• EXCLD_FLAG — determines whether a match causes exclusion (Y) or inclusion (N) against the profile.
• ORDR_NUM — sequencing of the rule within the profile.
• CRITERIA_SCORE / CRITERIA_WEIGHT — scoring and weighting values used by profile evaluation logic.
• BUSINESS_GROUP_ID — the enterprise/legislative business group owning the row.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J middle tier.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard WHO audit columns.
• ECY_ATTRIBUTE_CATEGORY and ECY_ATTRIBUTE1 through ECY_ATTRIBUTE30 — the 30 descriptive flexfield attribute columns supporting customer-defined extensions.

The surrogate key is ELIG_COMPTNCY_PRTE_ID; the unique business-key index BEN_ELIG_COMPTNCY_PRTE_F_PK is composite over (ELIG_COMPTNCY_PRTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), confirming the effective-dated grain.

Common Use Cases and Queries

Primary use cases include auditing why an employee qualified for a compensation object, reporting competency-based eligibility configurations, and cloning profiles across business groups or effective dates. A typical query joins the rule to its reference tables to render a readable rule set:

• SELECT p.ELIG_COMPTNCY_PRTE_ID, c.COMPETENCE, r.RATING_LEVEL, p.EXCLD_FLAG, p.CRITERIA_SCORE FROM BEN_ELIG_COMPTNCY_PRTE_F p, PER_COMPETENCES c, PER_RATING_LEVELS r WHERE p.COMPETENCE_ID = c.COMPETENCE_ID AND p.RATING_LEVEL_ID = r.RATING_LEVEL_ID(+) AND p.ELIGY_PRFL_ID = :profile_id AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
• Filtering by EXCLD_FLAG to separate inclusion from exclusion rules within a profile.
• Comparing effective-dated rows to identify rule changes between plan years.
• Extracting ECY_ATTRIBUTE* values for customer-specific reporting where a DFF context (ECY_ATTRIBUTE_CATEGORY) is populated.

Related Objects

• PER_COMPETENCES — referenced via COMPETENCE_ID; supplies competency definitions.
• PER_RATING_LEVELS — referenced via RATING_LEVEL_ID; supplies rating-level definitions.
• BEN_ELIGY_PRFL_F — the parent eligibility profile header linked through ELIGY_PRFL_ID.
• BEN_ELIG_COMPTNCY_PRTE — the translated (_TL) counterpart holding language-dependent text.
• BEN_ELIG_*_PRTE_F tables — sibling rule tables (for example other criteria types) that share the same eligibility-profile framework.
• BEN_ELIGY_PRFL — the logical profile entity consumed by the benefits eligibility engine.
• PER_COMPETENCE_ELEMENTS / competency APIs — used when importing or maintaining competency and rating data that feeds these rules.