Search Results pqh_role_templates_uk1

Overview

The PQH_ROLE_TEMPLATES table is a core data object within the Oracle E-Business Suite Public Sector Human Resources (PQH) module. It functions as a junction table that establishes a critical link between security roles, transaction categories, and templates. Its primary role is to define which approval templates are applicable for specific transaction categories when processed by a user assigned a particular role. This configuration is essential for enforcing role-based workflow and approval routing rules within the complex personnel transactions typical of public sector HR operations, such as position management, funding changes, and employee lifecycle events.

Key Information Stored

The table stores the association between three key entities. Its structure, as defined by its primary and unique keys, emphasizes the uniqueness of the relationship between a role and a transaction category. The most important columns are:

• ROLE_TEMPLATE_ID: The primary key surrogate identifier for each record.
• ROLE_ID: A foreign key referencing the PQH_ROLES table, identifying the security role.
• TRANSACTION_CATEGORY_ID: A foreign key referencing the PQH_TRANSACTION_CATEGORIES table, identifying the type of HR transaction (e.g., hire, promotion, transfer).
• TEMPLATE_ID: A foreign key referencing the PQH_TEMPLATES table, identifying the specific approval template to be used for the given role and transaction category combination.

The unique constraint (PQH_ROLE_TEMPLATES_UK1) on ROLE_ID and TRANSACTION_CATEGORY_ID ensures that a role cannot have more than one default template assigned per transaction type.

Common Use Cases and Queries

This table is central to configuring and auditing the approval matrix. A common administrative task is to review all template assignments for a specific role to understand its approval authorities. The following query retrieves this information:

SELECT r.ROLE_NAME, tc.NAME AS TRANSACTION_CATEGORY, t.TEMPLATE_NAME
FROM pqh_role_templates rt,
    pqh_roles r,
    pqh_transaction_categories tc,
    pqh_templates t
WHERE rt.role_id = r.role_id
AND rt.transaction_category_id = tc.transaction_category_id
AND rt.template_id = t.template_id
AND r.ROLE_NAME = 'HR_MANAGER';

Conversely, support personnel may query this table to diagnose workflow issues, such as identifying which template is being invoked when a specific user submits a particular transaction type, by joining through user role assignments.

Related Objects

PQH_ROLE_TEMPLATES is a dependent object with strong referential integrity ties to three key master tables, as defined by its foreign keys:

• PQH_ROLES: The source table for ROLE_ID, containing the definition of security roles within PQH.
• PQH_TRANSACTION_CATEGORIES: The source table for TRANSACTION_CATEGORY_ID, defining the types of personnel actions available.
• PQH_TEMPLATES: The source table for TEMPLATE_ID, storing the definition of approval templates that specify workflow rules and approver hierarchies.

This table sits at the intersection of security (Roles), business process (Transaction Categories), and workflow configuration (Templates), making it a pivotal point for the Public Sector HR approval engine.