Search Results pa_transaction_xface_control




Overview
Explore in AI Chat

Overview

PA_TRANSACTION_XFACE_CONTROL is a reporting view within the Oracle Projects (PA) module of Oracle E-Business Suite. Its purpose is to expose control and status information for batches of transactions that are staged in the Projects transaction interface. It surfaces the same columns as the multi-organization table PA_TRANSACTION_XFACE_CTRL_ALL, but through an organization-security predicate that filters rows to the single operating unit (organization) currently in scope for the session.

The view is documented in ETRM 12.2.2 as a "Single-Org - Retrofitted" object, indicating that it is the single-organization variant of a control entity that was retrofitted to support the multi-org architecture introduced in Release 11i and carried forward into 12.1.1 and 12.2.2. The documented metadata further states that the object is "Not implemented in this database," so its presence and behavior depend on the specific environment and on whether the Projects transaction interface has been configured and populated. In practical reporting terms, the view gives administrators and developers a direct window into interface batches, their processing status, transaction counts, and the system linkage function associated with each batch, without requiring them to query the underlying _ALL table directly.

Underlying Base Objects

The view is defined over a single documented base object: PA_TRANSACTION_XFACE_CTRL_ALL. The ETRM metadata lists no other referenced base objects, and the view text confirms a straightforward SELECT of nine columns from that table with no joins. Organization filtering is achieved entirely through the WHERE clause rather than through a join to an organization hierarchy table.

The predicate uses USERENV('CLIENT_INFO') to read the client session context established by the multi-org initialization routines. The first character of CLIENT_INFO is inspected; if it is a space, the expression resolves to NULL, otherwise the first ten characters are cast to a number. The resulting value is compared against ORG_ID, with NVL and a sentinel value of -99 applied on both sides so that rows with a null ORG_ID are matched when no organization context is set. This is the classic Oracle Applications single-org retrofit pattern and explains the object's designation. Because the base table is the _ALL variant, the view inherits all organization partitions but restricts visibility to the active operating unit.

Key Columns

  • TRANSACTION_SOURCE — Identifies the source of the interface transactions in the batch, allowing control records to be grouped by originating subsystem.
  • BATCH_NAME — The name assigned to the interface batch; used as the primary human-readable identifier during troubleshooting.
  • STATUS — The current processing status of the batch, indicating whether the batch is pending, in process, completed, or in error.
  • INTERFACE_ID — The unique identifier of the interface run or batch, used for joins and audit tracing.
  • TRANSACTION_COUNT — The number of transactions associated with the batch as expected or declared for the interface.
  • ORG_ID — The operating unit to which the control record belongs; this is the column against which the single-org predicate is applied.
  • SYSTEM_LINKAGE_FUNCTION — Identifies the linkage function used to associate the interface data with its source system or calling process.
  • INTERMEDIATE_FLAG — A flag indicating whether the batch passes through an intermediate processing stage before final interface.
  • PROCESSED_COUNT — The number of transactions that have been processed, which can be compared with TRANSACTION_COUNT to determine progress or identify incomplete batches.

Common Use Cases and Queries

Typical uses include monitoring outstanding interface batches, reconciling processed versus expected transaction counts, and diagnosing stalled or failed interfaces. A simple status listing can be produced as follows:

SELECT TRANSACTION_SOURCE, BATCH_NAME, STATUS, TRANSACTION_COUNT, PROCESSED_COUNT FROM PA_TRANSACTION_XFACE_CONTROL ORDER BY BATCH_NAME;

To find batches that are incomplete, compare the two count columns:

SELECT BATCH_NAME, TRANSACTION_COUNT, PROCESSED_COUNT FROM PA_TRANSACTION_XFACE_CONTROL WHERE NVL(PROCESSED_COUNT,0) < NVL(TRANSACTION_COUNT,0);

Because the view applies single-org filtering automatically, queries run within a properly initialized Projects session return only the batches for the current operating unit. If the client context is not set, the predicate falls back to the -99 sentinel and may return rows with a null ORG_ID. When cross-organization analysis is required, the underlying PA_TRANSACTION_XFACE_CTRL_ALL table should be queried instead. As always, the object's availability depends on the documented note that it is not implemented in every database.