Results for “comp_v2”

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

Overview

APPS.PER_COMPETENCE_ELEMENTS_V3 is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 Oracle Human Resources (PER) product family. It exposes competence element data structured specifically around assessment relationships, presenting a denormalized, self-joined picture of how assessment groups relate to the individual assessed competences. This view is commonly referenced by Oracle Learning Management and iLearning assessment flows, and is intended for reporting, integration, and lookup purposes rather than for direct transactional maintenance. Because the view resolves language-specific descriptions through a USERENV('LANG') predicate, it is session-aware and returns only the competence name appropriate for the connected user's language, making it safe to consume from concurrent multi-language EBS sessions. Applications searching on the term assessment_competence will find this view central to the assessment competence model in EBS.

Underlying Base Objects

The documented base objects underlying PER_COMPETENCE_ELEMENTS_V3 are the synonyms PER_COMPETENCES, PER_COMPETENCES_TL, and PER_COMPETENCE_ELEMENTS. The view text performs a UNION of two branches and joins PER_COMPETENCE_ELEMENTS to itself using two aliases: CEL1 representing the parent assessment group (filtered by CEL1.TYPE = 'ASSESSMENT_GROUP') and CEL2 representing the child assessed competence (filtered by CEL2.TYPE = 'ASSESSMENT_COMPETENCE'). The child row is linked to PER_COMPETENCES via COMPETENCE_ID, which in turn is joined to PER_COMPETENCES_TL for the translatable competence name. The second branch of the UNION begins with a literal 'N' flag, projecting competence data where no assessment-group parent applies, which explains the TO_NUMBER(NULL) placeholders for group-related identifiers.

Key Columns

• ASSESSMENT_TYPE_ID — inherited from the assessment group row CEL1, tying the record to a configured assessment type.
• GROUP_COMPETENCE_TYPE and COMPETENCE_ID — identify the parent group's type and the assessed competence identifier respectively.
• NAME — the language-specific competence name sourced from PER_COMPETENCES_TL.
• COMPETENCE_ELEMENT_ID — surrogate key of the child assessment competence row in PER_COMPETENCE_ELEMENTS.
• SEQUENCE_NUMBER — ordering of the competence within its assessment group.
• A computed literal flag (returning 'Y' in the first branch and 'N' in the second) — distinguishes grouped versus ungrouped assessment competence rows.
• Standard WHO/audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER), 20 flexfield ATTRIBUTE columns, and 20 INFORMATION columns for descriptive flexfield/extra data.
• Personalisation columns including PARTY_ID, QUALIFICATION_TYPE_ID, UNIT_STANDARD_TYPE, STATUS, and ACHIEVED_DATE, which support assessing whether an individual has achieved a competence.
• BUSINESS_GROUP_ID — the HR business group operating context; the view allows a null business group match on the competence.

Common Use Cases and Queries

Typical uses include extracting the composition of assessment groups, resolving competence names for integration payloads, and building assessment reports for learners. A straightforward listing of grouped assessment competences can be written as:

SELECT v.assessment_type_id,
       v.competence_id,
       v.name,
       v.sequence_number,
       v.status,
       v.achieved_date
  FROM apps.per_competence_elements_v3 v
 WHERE v.competence_element_id IS NOT NULL
 ORDER BY v.assessment_type_id, v.sequence_number;

To isolate only grouped (flagged 'Y') rows for a specific business group:

SELECT v.name, v.sequence_number
  FROM apps.per_competence_elements_v3 v
 WHERE v.business_group_id = :p_bg_id;

Because the view relies on PER_COMPETENCES_TL with USERENV('LANG'), names are returned in the session language only; consumers requiring all translations should query the underlying _TL table directly.