Results for “transaction_name”

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

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:

• PQH_POSITION_TRANSACTIONS (synonym) — joined to PQH_TRANSACTION_CATEGORIES via WF_TRANSACTION_CATEGORY_ID.
• PQH_WORKSHEETS (synonym) joined to PQH_WORKSHEET_DETAILS (synonym) and PQH_TRANSACTION_CATEGORIES, restricted to worksheet detail statuses such as APPROVED, DELEGATED, TERMINATE, SUBMITTED, REJECT, APPLIED, and PENDING, and to rows where NVL(ACTION_CD,'D') = 'D'.
• PQH_COPY_ENTITY_TXNS joined to PQH_TRANSACTION_CATEGORIES for copy entity transactions.

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

• TRANSACTION_CATEGORY_ID — Identifier of the transaction category (from PQH_TRANSACTION_CATEGORIES), used to classify the transaction type.
• PARENT_TRANSACTION_ID — For position transactions this equals the transaction ID; for worksheet details it is NVL(PARENT_WORKSHEET_DETAIL_ID, WORKSHEET_DETAIL_ID), providing a hierarchy link.
• TRANSACTION_ID — Unique identifier for the transaction row across all three UNION branches.
• TRANSACTION_NAME — Descriptive name, sourced directly for position transactions and via PQH_WKS_BUDGET.GET_TRANSACTION_NAME for worksheet details.
• TRANSACTION_STATUS — Raw status code; worksheet rows default to 'PENDING' when status is null.
• TRANSACTION_STATUS_MEANING — Decoded meaning via HR_GENERAL.DECODE_LOOKUP against PQH_TRANSACTION_STATUS (or PQH_GEN_STATUS for copy entity transactions).
• CURRENT_OWNER — The current workflow owner or approver, resolved by PQH_WF.GET_CURRENT_OWNER.

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.