Search Results routing_hist_attrib_id




Overview
Explore in AI Chat

Overview

The HR.PQH_ROUTING_HIST_ATTRIBS table is a core data repository within the Oracle E-Business Suite (EBS) Human Resources (HR) module, specifically supporting the Position Hierarchy (PQH) functionality. Its primary role is to provide a granular, historical audit trail of attribute values associated with each step of a routing or approval workflow. When a transaction, such as a position management or budget change, is routed through an approval hierarchy, this table captures the specific attribute conditions and values that were evaluated at each stage of that routing process. This historical record is critical for compliance, auditing, and troubleshooting the behavior of complex approval rules over time in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table stores a combination of routing context, attribute definition, and value data. The primary key, ROUTING_HIST_ATTRIB_ID, uniquely identifies each historical attribute record. The ROUTING_HISTORY_ID column links the record to a specific instance in the PQH_ROUTING_HISTORY table, establishing the workflow context. The ATTRIBUTE_ID column references the PQH_ATTRIBUTES table, defining which specific attribute (e.g., salary grade, job code) was being evaluated. Crucially, the table stores both range-based and discrete attribute values to support different rule conditions. The RANGE_TYPE_CD indicates the range context (e.g., Next, List, Current), while the FROM_*/TO_* columns (CHAR, DATE, NUMBER) define a value range, and the VALUE_* columns (CHAR, DATE, NUMBER) store a discrete value. Standard WHO columns and an OBJECT_VERSION_NUMBER track creation, modification, and object-level concurrency control.

Common Use Cases and Queries

A primary use case is auditing the approval path of a specific transaction to understand why it was routed to particular approvers. Analysts or support personnel query this table to reconstruct the attribute-based rules that were in effect. Another scenario involves diagnosing routing failures by examining the historical attribute snapshots captured when a workflow engine evaluated conditions.

Sample SQL to retrieve the full attribute history for a specific routing instance:

  • SELECT rh.routing_history_id, a.attribute_name, rha.range_type_cd, rha.value_char, rha.from_number, rha.to_number FROM pqh_routing_history rh, pqh_routing_hist_attribs rha, pqh_attributes a WHERE rh.routing_history_id = rha.routing_history_id AND rha.attribute_id = a.attribute_id AND rh.transaction_id = :p_txn_id ORDER BY rh.routing_history_id;

To find all historical records where a specific attribute (e.g., salary) fell within a given range:

  • SELECT * FROM pqh_routing_hist_attribs rha WHERE rha.attribute_id = (SELECT attribute_id FROM pqh_attributes WHERE attribute_name = 'SALARY') AND :p_salary_value BETWEEN NVL(rha.from_number, :p_salary_value) AND NVL(rha.to_number, :p_salary_value);

Related Objects

This table is centrally linked to two key parent tables via foreign key constraints. The PQH_ROUTING_HIST_ATTRIBS_FK1 index and foreign key relationship tie it to PQH_ROUTING_HISTORY (ROUTING_HISTORY_ID), which is the master record for each routing step. The PQH_ROUTING_HIST_ATTRIBS_FK2 index and foreign key relationship tie it to PQH_ATTRIBUTES (ATTRIBUTE_ID), the definition table for all available attributes. The table's primary key is enforced by the unique index PQH_ROUTING_HIST_ATTRIBS_PK on the ROUTING_HIST_ATTRIB_ID column. As a transactional table, it is stored in the APPS_TS_TX_DATA tablespace with its indexes residing in APPS_TS_TX_IDX.