Results for “pqh_position_roles_v”

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

Overview

The PQH_POSITION_ROLES_V view is an APPS-owned database view within the Public Sector HR (PQH) product family of Oracle E-Business Suite. It presents the association between positions, defined on HR_ALL_POSITIONS, and the security or functional roles assigned to those positions through the roles framework defined by PQH_ROLES. In EBS 12.1.1 and 12.2.2 this view serves primarily as a reporting and integration surface, allowing developers, report authors, and interface builders to resolve the human-readable role name associated with a position without writing the multi-table join themselves.

The view is commonly encountered when a user searches on hr_all_positions, because that base table — the central repository of position definitions — is joined inside the view to supply the POSITION_NAME column. Its main purpose is to flatten the descriptive flexfield style storage of role assignments (held in PER_POSITION_EXTRA_INFO as an INFORMATION_TYPE of PQH_POS_ROLE_ID) into discrete, queryable columns.

Underlying Base Objects

The view is defined over three base objects:

• PER_POSITION_EXTRA_INFO (PEI) — stores the position extra information records. Only rows where INFORMATION_TYPE = 'PQH_POS_ROLE_ID' are relevant; the role identifier is held in the POEI_INFORMATION3 attribute and converted with TO_NUMBER into the ROLE_ID column.
• HR_ALL_POSITIONS (POS) — the base position definition table, supplying POSITION_ID and POSITION_NAME. This is the object most users search for and the principal access path.
• PQH_ROLES (RLS) — the roles catalogue, supplying ROLE_NAME and ENABLE_FLAG.

The documented metadata also references HR_GENERAL (PACKAGE) and a synonym for PER_POSITION_EXTRA_INFO in the ETRM 12.2.2 inventory, reflecting the standard EBS pattern of resolving base objects through synonyms and packaged APIs. Row creation and maintenance of the underlying extra information is normally performed through the HR_GENERAL package rather than direct DML.

Key Columns

• POSITION_EXTRA_INFO_ID — primary key of the source extra information row; useful for drill-back and diagnostics.
• POSITION_ID / POSITION_NAME — the position identifier and its display name from HR_ALL_POSITIONS.
• ROLE_ID / ROLE_NAME — the numeric role identifier and its descriptive name. Note the DECODE default of -1 when the information type does not match.
• ROLE_ENABLE_FLAG — indicates whether the referenced role is currently enabled.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the underlying entities.

Common Use Cases and Queries

Typical scenarios include auditing which roles are attached to a position, identifying positions carrying a specific role, and building extracts for security reviews. A representative query joining to HR_ALL_POSITIONS for a known position name:

• SELECT r.position_id, r.position_name, r.role_id, r.role_name, r.role_enable_flag
• FROM apps.pqh_position_roles_v r
• WHERE r.position_name LIKE '&position_name%'
• AND r.role_enable_flag = 'Y';

Because the view already encapsulates the join to HR_ALL_POSITIONS and PQH_ROLES, direct queries against it are preferred over reimplementing the joins to PER_POSITION_EXTRA_INFO. Filtering on POSITION_ID is recommended where the identifier is known, since it uses the leading access path to the position table.