Results for “pa_competence_profiles”

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

Overview

PA_COMPETENCE_PROFILES is a table owned by the PA (Projects) schema in Oracle E-Business Suite, documented as VALID across release levels 12.1.1 and 12.2.2. Its purpose is narrow and operational: it functions as a temporary staging table that holds newly entered or modified competency information for a person until those changes receive managerial approval. Rather than writing directly to the permanent competency records, the application persists proposed changes here first, allowing a review-and-approve workflow before the data becomes effective.

Because the table captures the relationship between a person and a competency — including proposed rating values and effective dates — it behaves as an event or transaction staging area rather than a master reference table. From a heuristic Data Vault modeling perspective, this object is classified as standalone, meaning it does not participate in a parent-child hub/link structure with other tables via its own primary key. That classification is a modeling suggestion only; the table still carries conventional foreign key references outward to the PER competency definition tables.

Key Information Stored

The table comprises 20 documented columns. The most significant are:

• PROFILE_ID — the surrogate primary key uniquely identifying each staged profile record.
• PERSON_ID — the person (employee or applicant) to whom the proposed competency change applies. This is the principal business-key candidate for grouping pending changes.
• COMPETENCE_ID — foreign key to PER_COMPETENCES, identifying the competency being added or modified.
• COMPETENCE_ELEMENT_ID — foreign key to PER_COMPETENCE_ELEMENTS, identifying the specific element within the competency.
• RATING_LEVEL_ID — foreign key to PER_RATING_LEVELS, the proposed rating for the competency.
• RATING_LEVEL_VALUE — the denormalized numeric or descriptive value associated with the proposed rating.
• EFFECTIVE_DATE_FROM — the proposed start date from which the competency change should apply.
• OLD_EFFECTIVE_DATE_FROM, OLD_RATING_LEVEL_ID, OLD_RATING_LEVEL_VALUE — the prior values held before the modification, enabling before-and-after comparison during approval.
• COMPETENCE_NAME, COMPETENCE_ALIAS, DESCRIPTION — descriptive attributes carried for display and review.
• OPERATION — indicates the type of staged change (for example, insert versus update), which drives how the approval process applies the record.
• BUSINESS_GROUP_ID — the HR business group context for the person's records.
• OBJECT_VERSION_NUMBER — the optimistic locking column used to detect concurrent modification.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard WHO audit columns.

PROFILE_ID serves as the surrogate primary key; PERSON_ID combined with the competency identifiers and effective dates form the practical business-key candidates for locating a staged change.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include identifying all pending competency changes awaiting approval, and comparing proposed values against the prior stored values.

To list pending changes for a person:

• SELECT profile_id, person_id, competence_id, rating_level_id, effective_date_from, operation FROM pa_competence_profiles WHERE person_id = :person_id;

To detect modifications (rather than new insertions) by comparing proposed and prior ratings:

• SELECT person_id, competence_id, old_rating_level_id, rating_level_id FROM pa_competence_profiles WHERE old_rating_level_id IS NOT NULL AND rating_level_id <> old_rating_level_id;

A common join pattern resolves rating and competency descriptions for review screens:

• SELECT p.person_id, c.competence_name, r.rating_level_value FROM pa_competence_profiles p JOIN per_competences c ON p.competence_id = c.competence_id JOIN per_rating_levels r ON p.rating_level_id = r.rating_level_id;

Because the table is transient, audit queries frequently trace OPERATION values and audit timestamps to understand what was staged and when. Reporting on this table is generally restricted to workflow monitoring rather than long-term competency history, which resides in the permanent PER tables after approval.

Related Objects

The table's documented foreign keys connect it to the following principal objects:

• PER_COMPETENCES — joined via COMPETENCE_ID; defines the competency master data.
• PER_COMPETENCE_ELEMENTS — joined via COMPETENCE_ELEMENT_ID; defines the element within the competency.
• PER_RATING_LEVELS — joined via RATING_LEVEL_ID; defines the proposed rating level.

The most significant additional objects in this domain include the permanent person-competency storage tables (such as PER_COMPETENCE_PROFILES and related PER person-competency tables) that receive data once approval completes, the PER_COMPETENCES and PER_RATING_LEVELS reference tables used for validation, and the HR person tables keyed by PERSON_ID. The approval workflow that promotes records out of this staging table is the primary consumer, making the competency definition tables the natural companion set for any query or extension built against PA_COMPETENCE_PROFILES.