Search Results pqh_transactions_v




Overview
Explore in AI Chat

Overview

PQH_TRANSACTIONS_V is an APPS-owned database view within the Oracle E-Business Suite product family PQH (Public Sector HR), also known as the Position Control or Position Management module. The view presents a consolidated, workflow-aware projection of HR transactions that flow through the Position Control engine, unifying records from position transactions, worksheet detail rows, and copy entity transactions into a single reporting surface. Its primary role is to expose the current state, category, and current workflow owner of every in-flight or completed transaction so that reports, workflow routing logic, and integration extracts can query transactions without needing to join the underlying transactional and setup tables independently.

The view is marked VALID in the ETRM 12.2.2 metadata and is owned by the APPS schema, which is consistent with other end-user-facing PQH views. Because it is a view and not a table, it is read-only and reflects the live state of the underlying base objects at query time. This makes it suitable for real-time dashboards and approval worklists rather than for historical snapshots.

Underlying Base Objects

The view definition is documented as a three-branch UNION over the following base objects:

The view also invokes the packages HR_GENERAL (for DECODE_LOOKUP), PQH_WF (for GET_CURRENT_OWNER), and PQH_WKS_BUDGET (for GET_TRANSACTION_NAME). These package calls resolve lookup meanings and the current workflow approver at runtime, meaning query performance depends partly on the cost of executing those PL/SQL functions per row.

Key Columns

Common Use Cases and Queries

Typical uses include building position control approval worklists, auditing in-flight transactions by owner, and integrating transaction status into custom OAF or BI Publisher reports. Because the view hides the UNION complexity, a simple SELECT returns a normalized result set:

  • Retrieve all pending transactions for a category: SELECT transaction_id, transaction_name, transaction_status_meaning, current_owner FROM pqh_transactions_v WHERE transaction_category_id = :cat AND transaction_status_meaning = 'PENDING';
  • Group workload by current owner: SELECT current_owner, COUNT(*) FROM pqh_transactions_v GROUP BY current_owner;
  • Filter by status meaning for dashboards, joining transaction_id back to PQH_POSITION_TRANSACTIONS or PQH_WORKSHEET_DETAILS for additional detail.

Queries should account for the per-row PL/SQL calls in the view definition; filtering early on category or status columns materially improves response time in high-volume environments.