Results for “pqh_process_log”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PQH_PROCESS_LOG is a logging table owned by the HR schema and associated with the PQH (Public Sector HR) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. As its ETRM description states, it is a "Log table" used to record the processing history, status, and diagnostic messages generated by PQH batch and transaction-processing routines. Because Public Sector HR workflows — such as position budgeting, assignment processing, and table-route driven transactions — execute in batches and often span multiple dependent steps, PQH_PROCESS_LOG provides the persistent audit trail that ties each transaction to its processing outcome, timestamp, and contextual information.

The heuristic Data Vault classification for this object is satellite-leaning. This suggests modeling PQH_PROCESS_LOG as a satellite attached to a parent transaction or route hub, capturing descriptive context, status, and audit attributes over time. The presence of a self-referencing MASTER_PROCESS_LOG_ID foreign key also introduces a hierarchical (parent/child) dimension within the same table, which is typical of process-orchestration logs.

Key Information Stored

The table contains 48 documented columns. The most significant are:

• PROCESS_LOG_ID — the surrogate primary key, uniquely identifying each log entry (PQH_PROCESS_LOG_PK).
• MASTER_PROCESS_LOG_ID — self-referencing foreign key pointing to a parent PQH_PROCESS_LOG row, used to link child steps to a master process run.
• TXN_TABLE_ROUTE_ID — foreign key to PQH_TABLE_ROUTE, identifying the routing definition that governed the transaction.
• TXN_ID — identifier of the transaction being logged.
• MODULE_CD — code indicating the originating module or process area.
• MESSAGE_TEXT and MESSAGE_TYPE_CD — the diagnostic message and its severity/type classification.
• BATCH_STATUS, BATCH_START_DATE, BATCH_END_DATE — batch execution state and timing.
• LOG_CONTEXT and INFORMATION_CATEGORY — contextual metadata and categorization of the log row.
• INFORMATION1 through INFORMATION30 — thirty generic attribute columns used by PQH processes to store process-specific payload or captured values.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard EBS WHO/audit columns.

The only documented unique index is PQH_PROCESS_LOG_PK on PROCESS_LOG_ID, making it the sole business-key candidate on record; no alternate unique business key is defined.

Common Use Cases and Queries

Typical scenarios include troubleshooting failed PQH batch runs, tracing the sequence of steps for a single transaction, and reporting on batch throughput and status. Common query patterns:

• Retrieve all messages for a given transaction: SELECT * FROM PQH_PROCESS_LOG WHERE TXN_ID = :txn_id ORDER BY CREATION_DATE;
• Reconstruct a hierarchical process run: SELECT * FROM PQH_PROCESS_LOG START WITH MASTER_PROCESS_LOG_ID IS NULL CONNECT BY PRIOR PROCESS_LOG_ID = MASTER_PROCESS_LOG_ID;
• Filter by status and time window: SELECT PROCESS_LOG_ID, BATCH_STATUS, BATCH_START_DATE, BATCH_END_DATE FROM PQH_PROCESS_LOG WHERE BATCH_STATUS = :status AND BATCH_START_DATE >= :from_date;
• Join to routing definitions: SELECT l.PROCESS_LOG_ID, r.ROUTE_ID FROM PQH_PROCESS_LOG l, PQH_TABLE_ROUTE r WHERE l.TXN_TABLE_ROUTE_ID = r.TXN_TABLE_ROUTE_ID;

These patterns support error analysis, SLA reporting on batch durations, and audit evidence for regulatory review.

Related Objects

• PQH_PROCESS_LOG (self) — referenced via MASTER_PROCESS_LOG_ID, forming the parent/child hierarchy of log entries.
• PQH_TABLE_ROUTE — referenced via TXN_TABLE_ROUTE_ID; defines the route that produced the logged transaction.
• PQH_PROCESS_LOG_PK — the primary key constraint enforcing uniqueness on PROCESS_LOG_ID.
• Standard HR audit columns (CREATED_BY, LAST_UPDATED_BY) reference FND_USER and related EBS security objects at the application layer.
• PQH transaction and batch orchestration programs that insert into this table are the primary consumers; report on these via the module code MODULE_CD.