Results for “pqh_roles”

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

Overview

PQH_ROLES is a core configuration table in the Oracle E-Business Suite Public Sector HR (PQH) module. It defines the roles that participate in position control workflow and security, serving as the authoritative registry of workflow actors within public sector position management. In EBS 12.1.1 and 12.2.2 the table resides in the HR schema and is validated against the ETRM data model. The table carries 42 documented columns and is the anchor (parent) of a family of dependent routing and configuration tables.

From a Data Vault modeling perspective, the heuristic classification of PQH_ROLES is a hub. Its surrogate primary key, ROLE_ID, is a stable, non-intelligent identifier referenced by many child tables, while descriptive attributes such as ROLE_TYPE_CD and ENABLE_FLAG behave as satellite-style attributes attached to that hub. This classification is a modeling suggestion derived from the foreign-key topology, not a physical design imposed by Oracle.

Key Information Stored

The table's identifying and controlling columns are the most operationally significant:

• ROLE_ID — surrogate primary key (PQH_ROLES_PK); the numeric identifier propagated to all dependent routing tables.
• ROLE_NAME — the business key, enforced by the unique index PQH_ROLES_UK; the human-readable role identifier exposed to users and workflow definitions.
• ROLE_TYPE_CD — classifies the role (for example, approver, reviewer, or routing participant), which drives how position control workflow resolves the actor.
• ENABLE_FLAG — indicates whether the role is active and eligible for workflow routing and security evaluation.
• BUSINESS_GROUP_ID — the operating unit / business group scope, with a foreign key to HR_ALL_ORGANIZATION_UNITS; supports multi-organization partitioning of role definitions.
• INFORMATION1 through INFORMATION30 and INFORMATION_CATEGORY — a descriptor-flex style block of 31 generic attributes available for customer-specific or localization extensions without schema modification.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications framework (OAF) to detect concurrent updates.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture standard WHO-column audit history for every role record.

Two unique indexes are documented: PQH_ROLES_PK on ROLE_ID (the surrogate key) and PQH_ROLES_UK on ROLE_NAME (the business-key candidate).

Common Use Cases and Queries

Typical usage centers on resolving which role should receive or forward a position control action, and on validating that a role is enabled before routing. A common lookup retrieves active roles by name or type:

• SELECT role_id, role_name, role_type_cd FROM pqh_roles WHERE enable_flag = 'Y' AND business_group_id = :p_bg_id;
• SELECT r.role_id, r.role_name FROM pqh_roles r WHERE r.role_name = :p_role_name; — leverages PQH_ROLES_UK for a unique business-key lookup.
• Join to PQH_ROUTING_LIST_MEMBERS to enumerate the members associated with a routing role, and to PQH_ROUTING_HISTORY to trace workflow forwarding chains.

Reporting use cases include auditing which roles are disabled, examining role-to-business-group distribution, and tracing the complete set of roles that have participated in routing history for compliance purposes.

Related Objects

PQH_ROLES is referenced by several dependent objects through documented foreign keys:

• PQH_ROUTING_LIST_MEMBERS — references ROLE_ID; defines membership of routing roles.
• PQH_ROLE_TEMPLATES — references ROLE_ID; templates derived from a role.
• PQH_ROLE_EXTRA_INFO — references ROLE_ID; supplementary role attributes.
• PQH_ROUTING_HISTORY — references ROLE_ID twice, via FORWARDED_TO_ROLE_ID and FORWARDED_BY_ROLE_ID, capturing who forwarded an action to whom.
• PQH_ROUTING_CATEGORIES — references ROLE_ID via OVERRIDE_ROLE_ID for category-level role overrides.
• HR_ALL_ORGANIZATION_UNITS — parent of PQH_ROLES.BUSINESS_GROUP_ID, providing the business group / operating unit context.