Results for “pqh_role_type”

12 results




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

Overview

APPS.PQH_ROLES_V is a reporting and integration view in the Oracle E-Business Suite HR/Payroll family of modules, owned by the APPS schema. It presents a decoded, presentation-ready projection of the underlying PQH_ROLES base table, which stores role definitions used by Oracle Public Sector/HR role-based functionality (the PQH product family). The view exposes the same columns as the base table but substitutes the raw lookup code ROLE_TYPE_CD with a human-readable description, generated through the HR_GENERAL package's DECODE_LOOKUP function, aliased as ROLE_TYPE. This makes the view especially suitable for concurrent programs, Oracle Reports, BI Publisher datasets, and custom SQL where the business meaning of the role type must be displayed rather than its internal code. The view also preserves the audit and concurrency columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER), so it can support both reporting and integration scenarios that need to detect record changes or perform optimistic locking checks. Because it is a simple, non-join view over a single synonym, it carries minimal performance overhead relative to querying PQH_ROLES directly.

Underlying Base Objects

According to the ETRM metadata, APPS.PQH_ROLES_V is defined over two referenced base objects:

  • PQH_ROLES (SYNONYM) — the primary source of data. The view selects from the synonym PQH_ROLES, aliased as A, which in turn resolves to the underlying physical table. All columns in the view correspond one-to-one with columns of PQH_ROLES, with the sole exception of the decoded ROLE_TYPE column.
  • HR_GENERAL (PACKAGE) — referenced only for the DECODE_LOOKUP function, which translates the lookup code stored in ROLE_TYPE_CD into its descriptive meaning using the lookup type PQH_ROLE_TYPE. This is the same mechanism Oracle uses throughout EBS to expose lookup values in a user-friendly form.

Because the view is a thin wrapper, the relationship to PQH_ROLES is essentially identity: one row in the view corresponds to one row in the base table, with no filtering, aggregation, or joins applied. The only transformation is the lookup decode on ROLE_TYPE_CD. This design means any predicate or index available on PQH_ROLES (for example, on BUSINESS_GROUP_ID or ENABLE_FLAG) remains usable when the view is queried, provided the optimizer can push the predicate through the view.

Key Columns

  • ROLE_ID — primary key of the role record; uniquely identifies each row.
  • BUSINESS_GROUP_ID — the business group to which the role belongs, the standard EBS multi-tenancy discriminator.
  • ROLE_NAME — the descriptive name of the role as maintained by the administrator.
  • ROLE_TYPE — the decoded, display value derived from HR_GENERAL.DECODE_LOOKUP('PQH_ROLE_TYPE', ROLE_TYPE_CD), matching the user's search term. It provides the human-readable meaning of the role type.
  • ROLE_TYPE_CD — the underlying lookup code retained for filtering, joining, or programmatic use.
  • ENABLE_FLAG — indicates whether the role is active (typically 'Y'/'N' or equivalent), controlling availability of the role in the application.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER support change tracking and optimistic concurrency.

Common Use Cases and Queries

The view is typically used to report on configured roles and their types, to drive validation or selection lists in concurrent programs, and to support integrations that need both the code and the description.

Example: list all enabled roles with their decoded type.

SELECT role_id, role_name, role_type, role_type_cd, enable_flag
FROM apps.pqh_roles_v
WHERE enable_flag = 'Y'
ORDER BY role_name;

Example: restrict to a business group and specific lookup code.

SELECT role_id, role_name, role_type
FROM apps.pqh_roles_v
WHERE business_group_id = :p_bg_id
AND role_type_cd = 'SOME_CODE';

Example: find recently modified roles for audit reporting.

SELECT role_id, role_name, role_type, last_update_date, last_updated_by
FROM apps.pqh_roles_v
WHERE last_update_date >= TRUNC(SYSDATE) - 30;
ORDER BY last_update_date DESC;