Results for “comp_def”

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

Overview

APPS.PA_COMPETENCES_V is a reporting and integration view in Oracle E-Business Suite that consolidates competency (competence) records with their associated competency definition attributes. It is documented in the ETRM metadata for release 12.2.2 under owner APPS, and applies equally to 12.1.1 deployments that share the same PER schema objects. The view joins the transactional competency entity, PER_COMPETENCES, to the descriptive framework entity, PER_COMPETENCE_DEFINITIONS, so that a single query returns both the specific competence instance (identifier, alias, name, description, effective dates) and the full set of definition-level flexfield segments (SEGMENT1 through SEGMENT30).

Because it exposes the definition segments in a flat, query-friendly form, the view is commonly used as a data source for competency catalogs, HR and project competency reporting, and outbound integration feeds. The user search term "comp_def" corresponds to the COMP_DEF table alias used in the view text, which is derived from PER_COMPETENCE_DEFINITIONS via the COMPETENCE_DEFINITION_ID join key.

Underlying Base Objects

The view is defined over two documented base objects, both exposed in APPS as synonyms:

• PER_COMPETENCES (alias COMP) — the competency instance table holding COMPETENCE_ID, COMPETENCE_ALIAS, NAME, DESCRIPTION, DATE_FROM, DATE_TO, and the foreign key COMPETENCE_DEFINITION_ID.
• PER_COMPETENCE_DEFINITIONS (alias COMP_DEF) — the definition or "framework" table holding COMPETENCE_DEFINITION_ID and the SEGMENT1 through SEGMENT30 flexfield columns.

The relationship is a simple equi-join: COMP.COMPETENCE_DEFINITION_ID = COMP_DEF.COMPETENCE_DEFINITION_ID. Each competency row is therefore enriched with the complete set of definition-level segments associated with its definition. No outer join or filter predicate is present in the documented view text, so the result set includes only those competences whose definition exists, and all segments are returned regardless of whether they are populated.

Key Columns

• COMPETENCE_ID — unique identifier of the competency instance; primary selection and join key for downstream applications.
• COMPETENCE_ALIAS — short user-facing code or alias used in catalogs and lookup screens.
• NAME, DESCRIPTION — descriptive text for the competency, suitable for display and reporting.
• DATE_FROM, DATE_TO — effective date range governing the validity of the competency record.
• SEGMENT1 – SEGMENT30 — the competency definition flexfield segments, inherited from PER_COMPETENCE_DEFINITIONS. These carry the structured attributes (for example, type, category, or grouping values) configured for the competency definition. Because competences from different definitions share the same column names, segment semantics depend on the definition's flexfield setup.

Common Use Cases and Queries

Typical usages include populating competency listings, extracting competency data for conversion or interfaces, and validating that definition segments are consistently maintained.

Basic listing of all competences with their definition segments:

SELECT competence_id, competence_alias, name, date_from, date_to, segment1, segment2 FROM apps.pa_competences_v ORDER BY name;

Retrieving a single competence by identifier:

SELECT * FROM apps.pa_competences_v WHERE competence_id = :p_competence_id;

Filtering currently effective competences:

SELECT competence_id, competence_alias, name FROM apps.pa_competences_v WHERE SYSDATE BETWEEN NVL(date_from, SYSDATE) AND NVL(date_to, SYSDATE);

Extracting competences by definition segment value:

SELECT competence_id, competence_alias, name FROM apps.pa_competences_v WHERE segment1 = :p_segment1_value;

Because the view is a simple join over synonyms, query performance depends on the indexes maintained on the underlying PER tables; applications should filter on COMPETENCE_ID or definition segment values where possible to reduce full scans.