Results for “select_flag”

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

Overview

The APPS.PA_ALL_COMPETENCES_LOV_V view is a Projects (PA) module object that exposes the complete set of competences defined in Oracle E-Business Suite. As its name implies, it is designed primarily as a list-of-values (LOV) source, returning the competences that are relevant to competency-based project and resource management. The view is defined in the APPS schema and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2.

Functionally, the view filters the underlying PER_COMPETENCES data so that only competences with associated rating-level definitions are returned. Its role is to provide Forms-based LOVs and concurrent/reporting queries with a validated, ready-to-use list of competences and their business-group context, avoiding the need for callers to re-implement the rating-level join logic themselves.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects for this view:

• PER_COMPETENCES (accessed via a synonym) — the primary source of competence rows, supplying the competence identifier, alias, name, description, rating scale, and date-range attributes.
• PER_RATING_LEVELS (accessed via a synonym) — used in correlated EXISTS subqueries to restrict the result set to competences that have rating levels defined.
• PER_BUSINESS_GROUPS (accessed as a view) — outer-joined to supply the business group name associated with each competence.
• PA_COMP_PROFILE_PUB (a PL/SQL package) — its GET_SELECT_FLAG function is invoked in the select list to compute the SELECT_FLAG value for each competence.

The view text joins PER_COMPETENCES to PER_BUSINESS_GROUPS using an outer join on BUSINESS_GROUP_ID. The WHERE clause contains an OR-condition with two EXISTS subqueries against PER_RATING_LEVELS: one matching the competence by COMPETENCE_ID, and the other matching by RATING_SCALE_ID. This dual condition ensures a competence is returned when either a competence-specific rating level exists or a rating level exists for the competence's rating scale.

Key Columns

The view exposes the following documented columns:

• COMPETENCE_ID — unique identifier of the competence; the key join value for downstream tables.
• COMPETENCE_ALIAS — short alias or code used to reference the competence.
• COMPETENCE_NAME — the descriptive name of the competence.
• DESCRIPTION — free-text description of the competence.
• BUSINESS_GROUP_ID — identifier of the owning business group (HR security context).
• BUSINESS_GROUP_NAME — name of the business group, sourced from PER_BUSINESS_GROUPS.
• SELECT_FLAG — value returned by PA_COMP_PROFILE_PUB.GET_SELECT_FLAG for the competence, used to control LOV selection behaviour.
• COMPETENCE_DATE_FROM / COMPETENCE_DATE_TO — the effective date range during which the competence is valid.

Common Use Cases and Queries

The view is most commonly consumed as an LOV source in Projects and competency-related forms, and as a reporting source when a validated competence list is required. A caller searching for the related term "per_rating_levels" typically encounters this view because its definition depends on PER_RATING_LEVELS to qualify competences.

A basic query returning all competences is:

• SELECT competence_id, competence_alias, competence_name, business_group_name, competence_date_from, competence_date_to FROM apps.pa_all_competences_lov_v;

A filtered query for currently active competences within a specific business group might read:

• SELECT competence_id, competence_name FROM apps.pa_all_competences_lov_v WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN competence_date_from AND NVL(competence_date_to, SYSDATE);

Because SELECT_FLAG is derived from a package function, queries intended for LOV population can include it as a display or enabled indicator. Callers should note that the EXISTS predicates guarantee at least one rating level per returned competence, so the view is safe to use where rating scale consistency is required.