Search Results per_competence_levels_v




Overview
Explore in AI Chat

Overview

The PER_COMPETENCE_LEVELS_V view is a public, VALID database object owned by the APPS schema within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments. It belongs to the PER – Human Resources product module and is classified in ETRM documentation with the description "Used to support user interface." This classification indicates that the view exists primarily to serve Oracle Forms and OAF-based HR self-service and professional forms rather than as a standalone reporting entity. It presents a denormalized, language-aware projection of competence rating levels, combining competency definitions, rating scale levels, and their translated display names into a single queryable structure. Because it is a view and not a table, it introduces no storage of its own; it derives its results dynamically from the underlying RATING_LEVELS and COMPETENCES base tables, applying session-language filtering through USERENV('LANG').

Underlying Base Objects

The documented referenced base objects are PER_COMPETENCES, PER_RATING_LEVELS, and PER_RATING_LEVELS_TL (each exposed through APPS synonyms). The view's SQL text is a UNION of two branches. The first branch joins PER_RATING_LEVELS (aliased RTL) to PER_RATING_LEVELS_TL (aliased RTX) on RATING_LEVEL_ID, restricting rows to those where COMPETENCE_ID is not null and filtering the translation table by the current session language. The second branch joins PER_RATING_LEVELS (LVL) to PER_RATING_LEVELS_TL (RTX2) and to PER_COMPETENCES (CMP) through the shared RATING_SCALE_ID, capturing levels that belong to a competency's rating scale even when the level row itself is not directly tied to a COMPETENCE_ID. This dual-branch construction ensures that both explicitly linked competence levels and scale-derived levels are surfaced for the UI.

Key Columns

  • RATING_LEVEL_ID – Primary identifier for the rating level; the join key across all three base objects.
  • COMPETENCE_ID – Identifies the competency to which the level applies; null-filtered in the first branch and populated from PER_COMPETENCES in the second.
  • RATING_SCALE_ID – The rating scale that governs the group of levels.
  • BUSINESS_GROUP_ID – Multi-tenant style partitioning column identifying the HR business group.
  • STEP_VALUE – Numeric ordering/weighting of the level within its scale.
  • NAME – Language-specific display name of the level, sourced from the _TL translation table.
  • BEHAVIOURAL_INDICATOR – Descriptive behavioral statement shown for the level in the UI.
  • OBJECT_VERSION_NUMBER – Optimistic locking column.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 – Descriptive flexfield (DFF) columns carried through from PER_RATING_LEVELS.
  • ROW_ID – The base table ROWID, enabling direct DML against the underlying PER_RATING_LEVELS or PER_COMPETENCES row.
  • Audit columns – CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The view is typically consumed when displaying competence level pick-lists, rating-scale maintenance screens, and competency profile pages. A representative query to list all levels for the current session language is:

SELECT competence_id, rating_level_id, name, behavioural_indicator, step_value
FROM apps.per_competence_levels_v
WHERE business_group_id = :p_bg_id
ORDER BY competence_id, step_value;

Because ROW_ID is exposed, developers occasionally use the view as a convenient row source for updating behavior indicators or DFF attributes. Queries that need only translated names should join on NAME rather than decoding the _TL table directly, since the view already applies the USERENV('LANG') filter. Report authors working in XML Publisher or BI Publisher should note that the view performs a UNION and two translation joins, so adding further joins to high-volume tables can affect performance; indexing on RATING_LEVEL_ID and RATING_SCALE_ID in the base tables supports efficient access.