Results for “cty_attribute_category”

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

Overview

BEN_COMPTNCY_RT_F is a date-tracked (effective-dated) table in the BEN schema of Oracle E-Business Suite Advanced Benefits. The "_F" suffix denotes a "flex" or date-effective structure, and the table stores the rate attributable to a competency. In practical terms, it defines how a specific competency (for example, a certification, skill, or proficiency requirement) maps to a rate, typically through an associated rating level and a variable rate profile. This table is central to competency-based rate configuration, where compensation, benefit contributions, or eligibility-driven rates vary according to an employee's assessed competency level.

From a heuristic Data Vault modeling perspective, the metadata classifies this object as standalone. This suggests it functions as a satellite-like or descriptive structure rather than a hub or link. It carries its own surrogate identity and effective-dating, and it references external competency and rating-level masters. In an EBS 12.1.1 / 12.2.2 environment, the table is registered as VALID under the BEN product, confirming its active role in the Advanced Benefits data model.

Key Information Stored

The table is physically documented with 46 columns. The most operationally significant are summarized below. The surrogate primary key, COMPTNCY_RT_ID, uniquely identifies each competency-rate record. The business key or unique index, BEN_COMPTNCY_RT_F_PK, is composed of COMPTNCY_RT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, giving each row a date-effective validity window.

• COMPETENCE_ID — foreign key to PER_COMPETENCES; identifies the competency to which the rate applies.
• RATING_LEVEL_ID — foreign key to PER_RATING_LEVELS; identifies the proficiency or rating level associated with the rate.
• VRBL_RT_PRFL_ID — references the variable rate profile that defines how the rate is calculated or retrieved.
• ORDR_NUM — ordering sequence for evaluating or displaying the rate records.
• EXCLD_FLAG — indicates whether the record is excluded from processing.
• BUSINESS_GROUP_ID — identifies the business group (operating unit) context for the configuration.
• OBJECT_VERSION_NUMBER — supports optimistic locking and concurrency control.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — define the date-effective range for the row.
• CTY_ATTRIBUTE_CATEGORY and CTY_ATTRIBUTE1–30 — descriptive flexfield columns for client-specific extensions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.

Common Use Cases and Queries

This table is queried most often during benefit rate derivation, competency-based eligibility processing, and configuration reporting. A typical pattern joins the competency and rating-level masters to resolve descriptive names for a given effective date:

• Retrieve all competency rates active as of a date, filtered by business group and excluding flagged rows.
• Join to PER_COMPETENCES and PER_RATING_LEVELS to report which competency/rating combinations drive specific variable rate profiles.
• Trace the variable rate profile (VRBL_RT_PRFL_ID) to determine actual monetary or percentage outcomes.
• Audit configuration changes using the WHO columns and effective dates.

Example: SELECT cr.COMPTNCY_RT_ID, cr.COMPETENCE_ID, cr.RATING_LEVEL_ID, cr.VRBL_RT_PRFL_ID FROM BEN_COMPTNCY_RT_F cr WHERE TRUNC(SYSDATE) BETWEEN cr.EFFECTIVE_START_DATE AND cr.EFFECTIVE_END_DATE AND cr.EXCLD_FLAG = 'N';

Related Objects

The most significant related objects, based on the documented foreign-key relationships, include:

• PER_COMPETENCES — referenced via COMPETENCE_ID.
• PER_RATING_LEVELS — referenced via RATING_LEVEL_ID.
• BEN_COMPTNCY_RT_F_PK — the unique index enforcing the effective-dated primary key.
• Variable Rate Profile tables in the BEN schema referenced through VRBL_RT_PRFL_ID.
• Other BEN date-effective tables sharing the business group and effective-dating conventions.