Results for “per_competence_definitions”

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

Overview

PER_COMPETENCE_DEFINITIONS is a Human Resources (PER) table that stores the key flexfield combinations for competencies in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It is the definitional anchor for the Competence key flexfield (often configured to capture competency categories, groupings, or classification segments), holding one row per distinct competence definition. Each row carries a surrogate identifier together with the flexfield segment values that make up the combination, plus standard concurrent-program and audit columns.

The ETRM metadata classifies this object heuristically as hub-leaning under a Data Vault model. In practice this means PER_COMPETENCE_DEFINITIONS behaves like a hub: it holds a stable, unique business key (the competence definition) that other tables reference, rather than acting as a mutable descriptive satellite. This classification is a modeling suggestion mined from the foreign-key structure, not a physical property of the table.

Key Information Stored

The table is documented with 45 columns. The most functionally significant are:

• COMPETENCE_DEFINITION_ID — the surrogate primary key, enforced by the unique index PER_COMPETENCE_DEFINITIONS_PK. This is the column referenced by dependent tables and is the sole documented business-key candidate.
• ID_FLEX_NUM — identifies the specific key flexfield structure the combination belongs to, allowing multiple flexfield definitions to coexist.
• SEGMENT1 through SEGMENT30 — the flexfield segment values that together form each competence combination. Only the segments enabled in the configured structure are populated.
• SUMMARY_FLAG and ENABLED_FLAG — control whether a definition is a summary (roll-up) node and whether it is active for use.
• START_DATE_ACTIVE and END_DATE_ACTIVE — date-range effective dating for the definition.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — concurrent program context identifying the process that created or last updated the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and LAST_UPDATE_DATE — standard WHO audit columns.

The distinction is important: COMPETENCE_DEFINITION_ID is the surrogate key used for joins, whereas the flexfield segment concatenation constitutes the natural business key.

Common Use Cases and Queries

Typical uses include reporting on configured competency structures, validating flexfield combinations, and resolving competence identifiers to their segment descriptions. A standard lookup joins the definition to its underlying competence records:

• Retrieving active definitions: SELECT competence_definition_id, segment1, segment2 FROM per_competence_definitions WHERE enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
• Resolving child competences to their parent definition: SELECT c.competence_id, d.segment1 FROM per_competences c JOIN per_competence_definitions d ON c.competence_definition_id = d.competence_definition_id;
• Auditing data loads by concurrent request: filtering on REQUEST_ID or PROGRAM_ID to trace which process populated definitions.

Because the segment columns are generic, reports should reference FND_ID_FLEX_STRUCTURES and FND_FLEX_VALUES to translate segment values into meaningful labels.

Related Objects

The documented foreign-key relationship links this table to the transaction-level competence records that depend on it:

• PER_COMPETENCES — references PER_COMPETENCE_DEFINITIONS via PER_COMPETENCES.COMPETENCE_DEFINITION_ID. This is the primary dependent object and the source of the hub-leaning classification.
• FND_ID_FLEX_STRUCTURES — defines the flexfield structure identified by ID_FLEX_NUM.
• FND_FLEX_VALUES and FND_FLEX_VALUES_TL — hold valid values and translated descriptions for each segment.
• FND_FLEX_VALUE_SETS — defines the value sets feeding the segments.
• PER_COMPETENCE_ELEMENTS and related PER competency tables — represent the element-level detail associated with competence definitions.

Together these objects provide the flexfield structure, validation, and transactional context that surround the competence definitions stored in this table.