Results for “ben_comptncy_rt_d”

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

Overview

The BEN_COMPTNCY_RT_D view is a denormalized reporting object owned by the APPS schema in Oracle E-Business Suite Advanced Benefits (BEN). It presents competency rating configuration records — the rules that associate a competency, a rating level, and an exclusion indicator with a variable rate profile — in a format suited for inquiry screens, concurrent reporting, and integration extracts. In EBS 12.1.1 and 12.2.2, the view is delivered with a status of VALID and is documented in the ETRM repository as a read-only view over the base configuration table BEN_COMPTNCY_RT_F.

As the "_D" suffix implies, the object is intended for descriptive/reporting access rather than transactional maintenance. It resolves surrogate foreign keys into user-facing names, sparing report authors and integration developers from repeatedly joining to PER_COMPETENCES, PER_RATING_LEVELS, BEN_VRBL_RT_PRFL_F, and FND_USER_VIEW. Because it exposes the effective-dated rows directly, callers must apply their own date filtering when a point-in-time result is required.

Underlying Base Objects

The view is defined over a five-way join between configuration, reference, and audit objects:

• BEN_COMPTNCY_RT_F (SYNONYM) — the driving table; holds competency rating rows with surrogate IDs, effective dates, ordering, and audit columns.
• PER_COMPETENCES (SYNONYM) — supplies the competency name via COMPETENCE_ID.
• PER_RATING_LEVELS (SYNONYM) — supplies the rating level name via RATING_LEVEL_ID. This is the object most directly associated with the search term rating_level.
• BEN_VRBL_RT_PRFL_F (SYNONYM) — supplies the variable rate profile name via VRBL_RT_PRFL_ID.
• FND_USER_VIEW (VIEW) — supplies the application user name for the LAST_UPDATED_BY column via an outer join (FUSER.USER_ID(+) = CTY.LAST_UPDATED_BY), so rows with no resolvable user still return.

All joins except the user lookup are inner joins, meaning a competency rating row is only returned when its competence, rating level, and variable rate profile all resolve. The view inherits the multi-org-independent, effective-dated nature of BEN_COMPTNCY_RT_F but does not itself apply a DATE_TRACK or session-effective filter.

Key Columns

• COMPTNCY_RT_ID — surrogate primary key of the competency rating row; use this for joins and drill-down.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — datetrack range of the configuration row.
• COMPETENCE — competency name from PER_COMPETENCES.NAME.
• RATING_LEVEL — rating level name from PER_RATING_LEVELS.NAME; the primary target of the "rating_level" search.
• EXCLUDE_FLAG — indicates whether the competency rating combination is excluded from evaluation.
• VRBL_RT_PRFL_NAME — name of the associated variable rate profile.
• ORDR_NUM — display/evaluation ordering sequence.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE — audit columns; LAST_UPDATED_BY resolves to the user name through FND_USER_VIEW.

Common Use Cases and Queries

Typical scenarios include auditing competency rating configuration, validating that rating levels are correctly mapped to variable rate profiles, and feeding benefits eligibility extracts. A current-dated listing is produced with:

• SELECT competence, rating_level, vrbl_rt_prfl_name, exclude_flag, ordr_num FROM apps.ben_comptncy_rt_d WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date ORDER BY competence, ordr_num;
• SELECT rating_level, COUNT(*) FROM apps.ben_comptncy_rt_d WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date GROUP BY rating_level;
• SELECT c.competence, c.rating_level, c.vrbl_rt_prfl_name FROM apps.ben_comptncy_rt_d c WHERE c.exclude_flag = 'N' AND c.rating_level = :p_rating_level;

Because the synonym is APPS-owned, queries should be qualified with APPS or accessed through a reporting responsibility whose MOAC access covers the required business groups.