Search Results competence_name




Overview
Explore in AI Chat

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

Overview

OTFV_DELIVERED_COMPETENCES is an APPS-owned business view template in the Oracle E-Business Suite OTA (Learning Management) module. It belongs to the family of flexfield-generated business views (the "OTFV" prefix denotes an Oracle Training/Talent flexfield view), and its documented description states that it is a "business view template from which the flexfield view is generated." Status is VALID, and the view is defined with a WITH READ ONLY clause, so it exposes data purely for query and reporting purposes rather than for direct DML.

The view presents the intersection of competencies and the learning activities through which those competencies are delivered. In OTA/OTM data model terms, a PER_COMPETENCE_ELEMENTS record of type 'DELIVERY' links a competence to a specific activity version, meaning the activity delivers or develops that competence. OTFV_DELIVERED_COMPETENCES flattens this many-to-many relationship, joining competences, competence elements, activity definitions, activity versions, rating levels, business groups, and language information into a single denormalised row per delivered competence. This makes it a convenient reporting and integration surface for users who need the "which learning delivers which competence" picture without hand-joining the PER and OTA schemas. Because the user searched on competence_name, the COMPETENCE_NAME column is the primary descriptive attribute returned here.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over the following documented objects: FND_LANGUAGES_VL (view), HR_ALL_ORGANIZATION_UNITS and HR_ALL_ORGANIZATION_UNITS_TL (synonyms), the HR_BIS package, OTA_ACTIVITY_DEFINITIONS and OTA_ACTIVITY_DEFINITIONS_TL (synonyms), OTA_ACTIVITY_VERSIONS and OTA_ACTIVITY_VERSIONS_TL (synonyms), the OTA_GENERAL package, PER_COMPETENCES, PER_COMPETENCE_ELEMENTS, PER_RATING_LEVELS (synonyms), and PO_VENDORS (view).

The joins reflect the following relationships:

  • PER_COMPETENCES joins PER_COMPETENCE_ELEMENTS on COMPETENCE_ID, with the element filtered to TYPE = 'DELIVERY' and matched on business group.
  • PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID outer-joins PER_RATING_LEVELS to supply the proficiency step and rating name.
  • OTA_ACTIVITY_VERSIONS joins the element on ACTIVITY_VERSION_ID, and further joins OTA_ACTIVITY_DEFINITIONS on ACTIVITY_ID; activity name and description come from the versions side.
  • FND_LANGUAGES_VL is outer-joined via the activity version's LANGUAGE_ID.
  • The business group is resolved through HR_ALL_ORGANIZATION_UNITS / _TL, with the translated name filtered by USERENV('LANG').
  • PO_VENDORS is outer-joined on the activity version's VENDOR_ID, and the row set is constrained by OTA_GENERAL.GET_BUSINESS_GROUP_ID.

Two functions are referenced: HR_BIS.BIS_DECODE_LOOKUP (decodes competence type via the COMPETENCE_TYPE lookup) and OTA_GENERAL.GET_BUSINESS_GROUP_ID (restricts results to the session business group).

Key Columns

  • BUSINESS_GROUP_NAME — translated name of the business group owning the delivered competence.
  • COMPETENCE_NAME — the name of the competence; the column most commonly searched and reported on.
  • COMPETENCE_TYPE — decoded (lookup-resolved) competence type; NULL when the element type is NULL.
  • ACTIVITY_TYPE and ACTIVITY_NAME — the learning activity type and version name delivering the competence.
  • DESCRIPTION — description of the activity version.
  • PROFICIENCY — composed as STEP_VALUE, plus ' - ' and rating NAME when a name exists.
  • "_DF" — the descriptive flexfield context token shown in the view text as '_DF:PER:PER_COMPETENCE_ELEMENTS:COMP', carrying the competence element flexfield context.
  • Identifier columns — ACTIVITY_ID, ACTIVITY_VERSION_ID, BUSINESS_GROUP_ID, COMPETENCE_ID, COMPETENCE_ELEMENT_ID, CONTROLLING_PERSON_ID, PROFICIENCY_LEVEL_ID, and RCO_ID — support drill-down and joins back to base tables.

Common Use Cases and Queries

Typical uses include competence-to-curriculum mapping, training catalogue reporting, integration feeds into learning portals, and gap analysis where delivered competencies are compared against required ones. A straightforward lookup by competence name:

  • SELECT competence_name, activity_name, activity_type, proficiency, business_group_name FROM apps.otfv_delivered_competences WHERE competence_name = :p_name;
  • SELECT competence_name, COUNT(*) FROM apps.otfv_delivered_competences GROUP BY competence_name ORDER BY 2 DESC; — identify the most frequently delivered competencies.
  • SELECT activity_name, competence_name, proficiency FROM apps.otfv_delivered_competences WHERE activity_type = :p_type; — list competencies delivered by a given activity type.
  • Join to PER_COMPETENCES or PER_COMPETENCE_ELEMENTS on the exposed ID columns for extended attribute detail, including flexfield segments surfaced by the "_DF" context.

Because results are business-group restricted and the view is read only, it is safe for concurrent reporting and extract jobs, though performance benefits from filtering on COMPETENCE_NAME or the ID columns.