Results for “pqh_role_users_v1”

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

Overview

PQH_ROLE_USERS_V1 is a valid APPS-owned view within the Public Sector HR (PQH) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a denormalized, reporting-friendly projection of role-to-user assignments stored as descriptive flexfield-style extra information against a person record. Rather than storing role membership in a dedicated transactional table, PQH stores assignments in PER_PEOPLE_EXTRA_INFO rows of INFORMATION_TYPE 'PQH_ROLE_USERS', and this view reassembles those rows into a compact, readable result set. It is therefore the primary supported interface for querying which named users hold which PQH roles and whether those assignments are enabled.

The view joins the assignment row to PER_ALL_PEOPLE_F for the person's full name, to FND_USER for the login name and user identifier, and to PQH_ROLES for the role name and role-level enable flag. This makes it equally suitable for operational reporting, for data extraction into downstream HR or security models, and for integration logic that must resolve a person to a role assignment.

Underlying Base Objects

ETRM metadata documents five referenced base objects, all resolved through APPS synonyms:

• PER_PEOPLE_EXTRA_INFO — the driving table, contributing the row identifier, ROLE_USER_ID (PERSON_EXTRA_INFO_ID), the person key, and the flexfield segments PEI_INFORMATION3 through PEI_INFORMATION30.
• PER_ALL_PEOPLE_F — joined on PERSON_ID to supply PERSON_NAME (FULL_NAME).
• FND_USER — supplies USER_NAME and USER_ID.
• PQH_ROLES — supplies ROLE_NAME and ENABLE_FLAG.
• FND_SESSIONS — used in the predicate that restricts PER_ALL_PEOPLE_F to the session effective date, ensuring the person record returned is the one in effect for the current session.

The view is filtered to INFORMATION_TYPE = 'PQH_ROLE_USERS', so only role-assignment extra information rows are exposed. Because PER_ALL_PEOPLE_F is date-effective, the FND_SESSIONS sub-query is a functional requirement rather than an optional refinement.

Key Columns

• ROW_ID — the ROWID of the underlying PER_PEOPLE_EXTRA_INFO row; useful for back-referencing the physical row.
• ROLE_USER_ID — alias of PERSON_EXTRA_INFO_ID, the surrogate key of the assignment.
• PERSON_ID, PERSON_NAME, USER_NAME, USER_ID — the identity of the assignee, in both HR and application-login terms.
• ROLE_ID, ROLE_NAME — ROLE_ID is derived with DECODE from PEI_INFORMATION3, cast to NUMBER only when INFORMATION_TYPE is 'PQH_ROLE_USERS' (otherwise zero); ROLE_NAME comes from PQH_ROLES.
• DEFAULT_ROLE — from PEI_INFORMATION4; flags a default assignment.
• ROLE_ENABLE_FLAG, USER_ENABLE_FLAG — role-level enablement from PQH_ROLES.ENABLE_FLAG and assignment-level enablement from PEI_INFORMATION5, permitting independent deactivation.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.
• PEI_INFORMATION6 through PEI_INFORMATION30 — pass-through flexfield segments, including the segment the user searched for, PEI_INFORMATION30.

Common Use Cases and Queries

The view supports point lookups of a user's roles, reconciliation of role enablement between the role definition and the individual assignment, and extraction of role membership for audit. A typical listing follows:

• SELECT PERSON_NAME, USER_NAME, ROLE_NAME, DEFAULT_ROLE, ROLE_ENABLE_FLAG, USER_ENABLE_FLAG FROM APPS.PQH_ROLE_USERS_V1 ORDER BY PERSON_NAME, ROLE_NAME;
• SELECT USER_NAME, ROLE_NAME FROM APPS.PQH_ROLE_USERS_V1 WHERE USER_ENABLE_FLAG = 'Y' AND ROLE_ENABLE_FLAG = 'Y';
• SELECT PERSON_ID, ROLE_ID, PEI_INFORMATION30 FROM APPS.PQH_ROLE_USERS_V1 WHERE INFORMATION_TYPE IS NULL; — not applicable, as INFORMATION_TYPE is fixed by the view definition; PEI_INFORMATION30 may instead be selected directly for assignments of interest.
• SELECT * FROM APPS.PQH_ROLE_USERS_V1 WHERE PERSON_ID = :person_id;

Because join and filter logic is encapsulated, report and interface code should query the view rather than PER_PEOPLE_EXTRA_INFO directly, avoiding duplication of the session-date and information-type predicates.