Search Results pqh_routing_history_pk




Overview
Explore in AI Chat

Overview

The PQH_ROUTING_HISTORY table is a core transactional entity within the Oracle E-Business Suite Public Sector HR (PQH) module, specifically for versions 12.1.1 and 12.2.2. It functions as a detailed audit trail for the routing and approval workflow of HR transactions. The table's primary role is to chronologically record each step or "hop" a transaction undergoes as it moves through a predefined approval hierarchy. This includes capturing the actors involved, their roles, and the specific point in the organizational structure where the action occurred. By maintaining this history, the table provides essential data for compliance reporting, process analysis, and troubleshooting approval chain issues.

Key Information Stored

The table's structure is designed to capture the complete context of a routing event. Its primary key, ROUTING_HISTORY_ID, uniquely identifies each historical record. Critical foreign key columns define the transaction's context and path: ROUTING_CATEGORY_ID and TRANSACTION_CATEGORY_ID classify the type of routing and transaction. The POS_STRUCTURE_VERSION_ID links the action to a specific version of the position hierarchy, ensuring historical accuracy. To track the flow between participants, the table stores FORWARDED_BY_MEMBER_ID and FORWARDED_TO_MEMBER_ID (linking to routing list members), as well as FORWARDED_BY_ROLE_ID and FORWARDED_TO_ROLE_ID (linking to defined roles). Additional columns, as indicated by the description storing "pre-identified attributes," would typically include timestamps, action types (e.g., FORWARD, APPROVE, REJECT), and comments.

Common Use Cases and Queries

A primary use case is generating audit reports to demonstrate compliance with internal controls and regulatory requirements by showing the complete approval path for a personnel action. Support personnel query this table to diagnose why a transaction is stalled, identifying the last person it was forwarded to and the current status. Common SQL patterns involve joining to related master tables to resolve IDs into meaningful names. For example, to trace the approval history for a specific transaction category:

Related Objects

The PQH_ROUTING_HISTORY table is centrally connected within the PQH workflow schema. It references several master and configuration tables via foreign keys: PQH_ROUTING_CATEGORIES and PQH_TRANSACTION_CATEGORIES for classification; PQH_ROUTING_LIST_MEMBERS (twice) to identify the forwarding and receiving individuals; PER_POS_STRUCTURE_VERSIONS for the organizational context; and PQH_ROLES (twice) for the functional roles of the participants. Crucially, it is the parent table for PQH_ROUTING_HIST_ATTRIBS, which holds detailed attribute-level changes associated with each routing history record, forming a comprehensive audit trail. The primary key constraint PQH_ROUTING_HISTORY_PK on ROUTING_HISTORY_ID ensures referential integrity for these relationships.