Search Results pqh_routing_categories

Overview

The PQH_ROUTING_CATEGORIES table is a core data object within the Oracle E-Business Suite Public Sector Human Resources (PQH) module. It serves as the central repository for defining and managing transaction routing rules. In the context of public sector HR processes, which often involve complex approval workflows for actions like hiring, promotions, and salary changes, this table establishes the linkage between a specific type of transaction (category) and the corresponding approval hierarchy (routing list). Its primary role is to determine the correct sequence of approvers for a given personnel action based on its category and, optionally, the position structure involved, thereby enforcing organizational policy and control within automated workflows.

Key Information Stored

The table stores configuration data that dictates workflow behavior. Its key columns, as indicated by primary and unique keys, include ROUTING_CATEGORY_ID (the primary surrogate key), TRANSACTION_CATEGORY_ID (identifying the type of HR transaction), and ROUTING_LIST_ID (pointing to the defined list of approvers). The DEFAULT_FLAG column is critical for signifying a default routing rule for its associated transaction category. The POSITION_STRUCTURE_ID allows routing rules to be further refined based on a specific organizational hierarchy. Additionally, the OVERRIDE_ROLE_ID column provides a mechanism to designate a specific role that can supersede the standard routing, offering flexibility for exceptional approvals.

Common Use Cases and Queries

This table is central to configuring and auditing approval workflows. A common operational use case is identifying the default routing rule for a specific transaction category to troubleshoot approval path issues. For reporting and analysis, administrators may query to list all routing rules associated with a particular position structure or to audit which transaction categories have multiple routing rules defined. Sample SQL to find the default routing list for a transaction category would be:

• SELECT prc.routing_category_id, prc.transaction_category_id, prl.name FROM pqh_routing_categories prc JOIN pqh_routing_lists prl ON prc.routing_list_id = prl.routing_list_id WHERE prc.transaction_category_id = <CATEGORY_ID> AND prc.default_flag = 'Y';

Another typical query involves joining with PQH_TRANSACTION_CATEGORIES to get a human-readable report of all category-to-routing-list mappings.

Related Objects

PQH_ROUTING_CATEGORIES is a pivotal table with several documented foreign key relationships, acting as both a child and parent table in the data model. It references the following key configuration tables:

• PQH_ROUTING_LISTS via ROUTING_LIST_ID: The master list of approvers.
• PQH_TRANSACTION_CATEGORIES via TRANSACTION_CATEGORY_ID: The definition of the HR transaction type.
• PER_POSITION_STRUCTURES via POSITION_STRUCTURE_ID: The organizational hierarchy.
• PQH_ROLES via OVERRIDE_ROLE_ID: A specific role for override approvals.

Conversely, it is referenced as a parent table by:

• PQH_ATTRIBUTE_RANGES via ROUTING_CATEGORY_ID: Allows for creating conditional routing rules based on attribute values (e.g., salary amount).
• PQH_ROUTING_HISTORY via ROUTING_CATEGORY_ID: Stores historical records of approval instances for audit trails.