Search Results pqh_process_log

Overview

The PQH_PROCESS_LOG table is a core logging entity within the Oracle E-Business Suite Public Sector HR (PQH) module. It functions as a system audit trail, capturing detailed records of batch and transactional processes executed within the PQH application. Its primary role is to provide a persistent, queryable history of process execution, which is critical for debugging, compliance reporting, and operational monitoring in complex public sector HR implementations. The table's self-referencing foreign key structure supports hierarchical logging, allowing a master process log entry to be linked to multiple subordinate or child log entries for granular traceability.

Key Information Stored

The table's structure is designed to encapsulate the lifecycle and outcome of a process execution. The most critical column is PROCESS_LOG_ID, the system-generated primary key that uniquely identifies each log entry. The MASTER_PROCESS_LOG_ID column establishes a parent-child relationship within the same table, enabling the nesting of log entries for complex, multi-step processes. The TXN_TABLE_ROUTE_ID column is a foreign key to PQH_TABLE_ROUTE, linking the log entry to the specific transactional routing or business rule that was being processed. Additional columns not explicitly listed in the metadata but standard in such logging tables would typically include timestamps (creation date), process name or type, status (e.g., 'SUCCESS', 'ERROR', 'WARNING'), initiating user or concurrent program identifier, and descriptive message or error text.

Common Use Cases and Queries

This table is primarily accessed for troubleshooting and generating process history reports. A common scenario involves identifying failed processes by querying for entries with a specific error status over a given timeframe. Developers and system administrators use this data to diagnose the root cause of batch job failures. Sample SQL to find recent error logs and their potential parent processes is:

• SELECT child.PROCESS_LOG_ID, child.MASTER_PROCESS_LOG_ID, child.CREATION_DATE FROM PQH_PROCESS_LOG child WHERE child.STATUS = 'ERROR' AND child.CREATION_DATE > SYSDATE - 1;

Another frequent use case is tracing the complete execution path of a specific transaction by following the hierarchy from a master log entry down through all related child entries using hierarchical or recursive SQL queries.

Related Objects

The PQH_PROCESS_LOG table has defined relationships with two key objects, as per the provided metadata:

• Self-Reference (Hierarchical Logging): The table references itself via the column PQH_PROCESS_LOG.MASTER_PROCESS_LOG_ID to PQH_PROCESS_LOG.PROCESS_LOG_ID. This creates a recursive relationship where one log entry can be the parent of another.
• PQH_TABLE_ROUTE: The table references PQH_TABLE_ROUTE via the foreign key column PQH_PROCESS_LOG.TXN_TABLE_ROUTE_ID. This links a process log entry to a specific transactional routing rule defined within the PQH module.
• The primary key constraint PQH_PROCESS_LOG_PK on PROCESS_LOG_ID is the anchor for these relationships.