Results for “default_role”

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

Overview

The PQH_ROLE_USERS_V view is an APPS-owned database view within the PQH (Public Sector HR) product family of Oracle E-Business Suite. In ETRM 12.2.2 (and equivalently in 12.1.1), the object is defined as a VALID view residing in the APPS schema, and it exposes the relationship between system users, roles defined in the PQH role framework, and the underlying person records to which those role assignments belong.

Functionally, the view presents a flattened, human-readable representation of role user assignments. Each row associates a person (identified by PERSON_ID) with an application user (USER_ID and USER_NAME) and a named role (ROLE_NAME). Because it resolves the person identifier to a display name via PER_ALL_PEOPLE_F.FULL_NAME, the view is particularly useful for reporting and integration scenarios where the consumer needs the person name rather than an internal numeric identifier — this is directly relevant to the user search term "person_name".

The view is not a transactional table; it is a read-only projection intended for queries, dashboards, extracts, and integrations that need to reconcile EBS users with PQH role assignments.

Underlying Base Objects

The view is defined over five documented base objects, all referenced through APPS synonyms:

• PER_PEOPLE_EXTRA_INFO (PEI) — Holds the role-user assignment records stored as extra information. The restricted INFORMATION_TYPE = 'PQH_ROLE_USERS' predicate isolates these rows, and the role identifier is stored functionally in PEI_INFORMATION3.
• PER_ALL_PEOPLE_F (PPF) — The effective-dated person table, joined on PERSON_ID to supply the person's full name. The effective-date filter is enforced by comparing the current FND session date between the person record's start and end dates.
• FND_USER (USR) — The application user table, joined on EMPLOYEE_ID = PERSON_ID to link the person to their EBS username and user ID.
• PQH_ROLES (RLS) — The role definition table, joined on ROLE_ID = PEI_INFORMATION3, supplying the role name and enable flag.
• FND_SESSIONS (SES) — Used in a scalar subquery to retrieve the effective date associated with the current session ID, ensuring the person record returned is the one effective at the time of query.

Key Columns

• ROW_ID — The ROWID of the underlying PER_PEOPLE_EXTRA_INFO row, providing a unique row locator.
• ROLE_USER_ID — The PERSON_EXTRA_INFO_ID, the surrogate key of the role-user assignment.
• PERSON_ID / PERSON_NAME — The person identifier and the resolved full name from PER_ALL_PEOPLE_F.FULL_NAME; PERSON_NAME is the display attribute most commonly sought by report authors.
• USER_ID / USER_NAME — The EBS application user identifier and login name.
• ROLE_NAME / ROLE_ID — The PQH role label and its numeric identifier, derived by decoding PEI_INFORMATION3.
• DEFAULT_ROLE — Indicates the default role flag from PEI_INFORMATION4.
• ROLE_ENABLE_FLAG — The role's enable status from PQH_ROLES.ENABLE_FLAG.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER support auditing and optimistic locking.

Common Use Cases and Queries

Typical scenarios include confirming which users hold a given PQH role, producing audit extracts of role assignments with person names, and feeding downstream integrations. The following sample returns active assignments with person and role detail:

• SELECT person_name, user_name, role_name, default_role FROM apps.pqh_role_users_v WHERE role_enable_flag = 'Y' ORDER BY person_name;
• SELECT person_name, role_name FROM apps.pqh_role_users_v WHERE user_name = :p_user_name;
• SELECT r.role_name, COUNT(*) user_count FROM apps.pqh_role_users_v r GROUP BY r.role_name;

Because the view resolves person names and applies session-effective date filtering, it can be queried directly without reconstructing the joins across the five base objects.