Search Results pqh_transaction_status




Overview
Explore in AI Chat

Overview

APPS.PQH_TRANSACTIONS_V is a reporting and integration view in Oracle E-Business Suite Advanced Benefits and Enterprise Talent Management (ETRM), available in releases 12.1.1 and 12.2.2. It presents a consolidated, flattened list of transactions flowing through the Position Management and Workforce Compensation (Worksheets) transaction frameworks, resolved across both the position transaction and worksheet detail data models.

The view is particularly significant because it is the canonical source for the PQH_TRANSACTION_STATUS lookup. Each row exposes the raw status code and its decoded meaning via HR_GENERAL.DECODE_LOOKUP, and reports the workflow's current owner via PQH_WF.GET_CURRENT_OWNER. This makes it the primary object for reporting on transaction state, approval routing, and pending versus completed activity across both frameworks. It is commonly used for status dashboards, workflow queue analysis, and integrations that need to poll for transaction completion.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over the following objects:

  • PQH_POSITION_TRANSACTIONS (synonym) — source of position transaction rows, supplying position_transaction_id, name, transaction_status, and wf_transaction_category_id.
  • PQH_TRANSACTION_CATEGORIES (synonym) — joined on wf_transaction_category_id = transaction_category_id to categorize each transaction.
  • PQH_WORKSHEETS (synonym) and PQH_WORKSHEET_DETAILS (synonym) — source of worksheet detail rows, joined on worksheet_id and filtered to statuses in ('APPROVED','DELEGATED','TERMINATE','SUBMITTED','REJECT','APPLIED','PENDING') and NVL(action_cd,'D')='D'.
  • HR_GENERAL (package) — provides DECODE_LOOKUP to resolve lookup meanings.
  • PQH_WF (package) — provides GET_CURRENT_OWNER to identify the current approver/owner.
  • PQH_WKS_BUDGET (package) — supplies get_transaction_name for worksheet-derived rows.

The view is a UNION ALL of two branches: one for position transactions and one for worksheet details, unified into a common column list so that both transaction streams can be queried identically.

Key Columns

  • TRANSACTION_CATEGORY_ID — category identifier from PQH_TRANSACTION_CATEGORIES.
  • PARENT_TRANSACTION_ID — for position transactions, equals the transaction id; for worksheet rows, derives from parent_worksheet_detail_id or the detail id.
  • TRANSACTION_ID — the position transaction id or worksheet detail id.
  • TRANSACTION_NAME — the position transaction name or the worksheet transaction name.
  • TRANSACTION_STATUS — raw status code; worksheet rows default to 'PENDING' when null.
  • TRANSACTION_STATUS_MEANING — decoded meaning from the PQH_TRANSACTION_STATUS lookup.
  • CURRENT_OWNER — the current workflow owner/approver derived by PQH_WF.GET_CURRENT_OWNER.

Common Use Cases and Queries

Typical uses include transaction status dashboards and pending-work queries.

  • Status summary by category and decoded meaning.
  • Filtering for currently pending items awaiting approval.
  • Identifying the current owner for workflow follow-up.
  • Integration polling to detect transactions in APPLIED or APPROVED states.

Sample SQL:

SELECT transaction_id, transaction_name,
       transaction_status, transaction_status_meaning, current_owner
FROM   apps.pqh_transactions_v
WHERE  transaction_status = 'PENDING';

Because the view invokes package functions per row, queries should be filtered and tuned, as CURRENT_OWNER and name resolution may impose performance overhead on large result sets.