Results for “per_competence_elements_v3”

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

Overview

PER_COMPETENCE_ELEMENTS_V3 is an APPS-owned database view in the Oracle E-Business Suite Human Resources (PER) product family. As documented in the ETRM metadata, its stated purpose is to support the user interface, and it is registered as VALID across both Oracle EBS 12.1.1 and 12.2.2. The view presents a flattened, self-joined representation of competency elements used by competency and assessment functionality, allowing the application to render assessment groups alongside the individual competences that belong to them without requiring the calling form or program to reconstruct the parent-child relationship itself.

In practical reporting and integration terms, the view serves as a read-only convenience layer over the competency element hierarchy. It exposes assessment groups, the competences linked to those groups, and their translated display names in a single result set, which makes it suitable for ad hoc SQL, BI Publisher data templates, and inbound/outbound interface extracts that need a denormalized competency listing.

Underlying Base Objects

Per the documented view metadata, PER_COMPETENCE_ELEMENTS_V3 is defined over three referenced base objects, all exposed through APPS synonyms:

• PER_COMPETENCE_ELEMENTS — referenced twice in the view text, as CEL1 and CEL2, and additionally as CEL_V2 in the UNION branch.
• PER_COMPETENCES — referenced as COMP (and COMP_V2), supplying competence-level attributes such as DATE_FROM and DATE_TO.
• PER_COMPETENCES_TL — referenced as CPL (and CPL_V2), supplying the translated competence NAME filtered by USERENV('LANG').

The view text is a UNION of two branches. The first selects assessment groups (CEL1.TYPE = 'ASSESSMENT_GROUP') joined to their child competences (CEL2.TYPE = 'ASSESSMENT_COMPETENCE'), explicitly using the predicate CEL2.PARENT_COMPETENCE_ELEMENT_ID = CEL1.COMPETENCE_ELEMENT_ID. The second branch selects rows of TYPE = 'COMPETENCE_USAGE', returning a large set of NULL placeholders and the literal 'N' to align its column list with the first branch. This UNION structure is the reason the view behaves as a combined catalogue of grouped and standalone competence usages.

Key Columns

• ASSESSMENT_TYPE_ID — from the assessment-group row (CEL1); NULL for the COMPETENCE_USAGE branch.
• COMPETENCE_ELEMENT_ID — the child element identifier, returned only in the assessment-group branch.
• PARENT_COMPETENCE_ELEMENT_ID — the join key linking the competence to its assessment group; this is the column most directly implicated in the search term and is the basis for the self-join.
• COMPETENCE_ID and NAME — the competence identifier and its language-translated name from PER_COMPETENCES_TL.
• DATE_FROM / DATE_TO — effective dating of the competence.
• GROUP_COMPETENCE_TYPE / COMPETENCE_TYPE — classification of the row across the two UNION branches.
• BUSINESS_GROUP_ID, SEQUENCE_NUMBER, OBJECT_VERSION_NUMBER, and the twenty ATTRIBUTEn columns — standard EBS multi-tenant, ordering, concurrency, and descriptive-flexfield columns, with LAST_UPDATE_DATE, CREATED_BY, and audit equivalents also exposed.
• PARTY_ID — returned from the child element; NULL in the usage branch.

Common Use Cases and Queries

A typical requirement is enumerating every competence beneath each assessment group. Because the view already resolves the parent linkage, the query is straightforward:

• SELECT assessment_type_id, competence_id, name FROM apps.per_competence_elements_v3 WHERE competence_element_id IS NOT NULL ORDER BY assessment_type_id, name;
• SELECT competence_id, name, date_from, date_to FROM apps.per_competence_elements_v3 WHERE competence_id = :p_competence_id;
• Translation-aware extracts simply run the view under the desired session language, since the NAME join is filtered on USERENV('LANG').

Integration points include competency profile reporting, assessment setup validation, and data migration verification where the parent-child element hierarchy must be confirmed. Because the view is UI-support oriented and defined over the transactional competency tables, it should be treated as read-only and joined to PER_COMPETENCES_TL translations already inlined. Users searching on parent_competence_element_id should note the column is surfaced only for assessment-group rows; it is not projected for the COMPETENCE_USAGE branch.