Search Results comp_ele




Overview
Explore in AI Chat

Overview

The PA_PROJ_ROLE_COMPETENCES_V view is an Oracle E-Business Suite database object owned by the APPS schema and registered under the Projects (PA) product family. Its documented purpose is to return the competences associated with a project role. In other words, it exposes the set of skills, attributes, or qualifications that have been attached to a given project role, together with the required proficiency rating and whether the competence is mandatory for that role.

The view acts as a reporting and integration layer over the Oracle Human Resources (PER) competence model. Because project roles in Oracle Projects reference the same PER competence infrastructure used across HR and Talent Management, this view provides a Projects-specific projection of that data, filtered to OBJECT_NAME = 'PROJECT_ROLE'. Teams building project staffing, resource matching, or competency gap reports can query this view rather than reconstructing the joins between competence elements, competence definitions, and rating levels manually. It is read-only and carries no business logic beyond the underlying joins, so it behaves consistently across EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The documented ETRM metadata identifies three referenced base objects, all accessed through APPS synonyms:

  • PER_COMPETENCE_ELEMENTS — the primary driving table. It stores individual competence elements attached to objects such as project roles, keying each row by OBJECT_ID, OBJECT_NAME, and COMPETENCE_ID.
  • PER_COMPETENCES — the competence definition table, supplying the competence name, alias, and description.
  • PER_RATING_LEVELS — the proficiency/rating level table, providing the rating level value (exposed as STEP_VALUE) for each competence element.

The join is defined as follows: PER_COMPETENCE_ELEMENTS is filtered where OBJECT_NAME = 'PROJECT_ROLE'; it is joined to PER_COMPETENCES on COMPETENCE_ID; and it is outer-joined ((+)) to PER_RATING_LEVELS on PROFICIENCY_LEVEL_ID = RATING_LEVEL_ID. The outer join means a competence element with no proficiency level will still be returned, with rating values null. All joins are on the APPS synonyms rather than the underlying HR schema names, which is standard for EBS-seeded views.

Key Columns

  • ROW_ID — the rowid of the underlying PER_COMPETENCE_ELEMENTS record, useful as a unique identifier.
  • PROJECT_ROLE_ID — the identifier of the project role (aliased from OBJECT_ID) to which the competence is attached.
  • OBJECT_VERSION_NUMBER — concurrency/versioning column inherited from the base element record.
  • COMPETENCE_ID — foreign key to the competence definition in PER_COMPETENCES.
  • COMPETENCE_ELEMENT_ID — the unique element identifier linking the role to the competence.
  • COMPETENCE_ALIAS, COMPETENCE_NAME, DESCRIPTION — descriptive attributes of the competence itself.
  • RATING_LEVEL_ID / RATING_LEVEL_VALUE — the required proficiency level (documented as PROFICIENCY_LEVEL_ID and STEP_VALUE).
  • MANDATORY — flag indicating whether the competence is required for the role.
  • LAST_UPDATE_DATE — audit timestamp of the last change to the competence element.

Common Use Cases and Queries

Typical uses include resource-matching reports, competency requirement listings for project roles, and data extracts feeding staffing or skills-management integrations. A straightforward query is:

  • SELECT project_role_id, competence_name, rating_level_value, mandatory FROM apps.pa_proj_role_competences_v WHERE project_role_id = :role_id;
  • To list all mandatory competences across all roles: SELECT project_role_id, competence_name FROM apps.pa_proj_role_competences_v WHERE mandatory = 'Y' ORDER BY project_role_id;
  • To find roles requiring a specific skill: SELECT project_role_id, competence_alias, rating_level_value FROM apps.pa_proj_role_competences_v WHERE competence_id = :competence_id;

Because the view is a simple projection with no aggregation, it can be joined freely to PA_PROJECT_ROLES or HR person competence data for gap analysis. Query performance is driven by the indexed joins to PER_COMPETENCE_ELEMENTS; filtering by PROJECT_ROLE_ID is recommended to limit the scan.