Search Results orig_transaction_reference




Overview
Explore in AI Chat

Overview

PA_TRX_INTF_ORIG_TRANSREF_V is a read-only reporting view owned by the APPS schema within the Oracle E-Business Suite Projects (PA) module. It exposes a deduplicated, grouped projection of the PA_TRANSACTION_INTERFACE table, the primary staging table through which external and subledger-sourced transactions are loaded into Oracle Projects. The view answers a narrow but important question: which combinations of transaction source, batch name, original transaction reference, and operating unit are currently present in the interface staging area.

Because the defining query applies a GROUP BY across all four projected columns, the view returns distinct combinations rather than individual interface rows. This makes it suitable for reconciliation, exception monitoring, and reporting queries where the analyst needs to enumerate the distinct source or batch values pending in the interface rather than inspect row-level detail. It is catalogued as VALID in the ETRM metadata for 12.2.2 and behaves identically under 12.1.1, as its definition is unaffected by the Online Patching (adop) editioning changes introduced in 12.2.

Underlying Base Objects

The view is defined exclusively over a single documented base object, PA_TRANSACTION_INTERFACE, referenced in the metadata as a SYNONYM owned by APPS. The defining SQL is:

No joins, filters, or aggregations beyond the grouping are present. Consequently, the view carries no data of its own; it is a pure derived projection. Row visibility is governed entirely by the contents and any Multi-Org security policies applied to the underlying interface table, and the ORG_ID column is preserved in the output so that consumers can respect operating unit partitioning.

Key Columns

  • TRANSACTION_SOURCE — Identifies the originating source system or process that populated the interface row (for example, a feeder subledger or an external system). Grouping by source allows analysts to isolate workloads by origin.
  • BATCH_NAME — The user-assigned or system-assigned batch identifier used to group related transactions during import. Distinct batch names reveal which logical loads are staged or outstanding.
  • ORIG_TRANSACTION_REFERENCE — The original transaction reference carried from the source document. This is the column most commonly targeted by the search term "orig_transaction_reference" and is the primary reconciliation key between the source system and the Projects interface.
  • ORG_ID — The operating unit identifier, enabling partitioning of results by business entity where Multi-Org Access Control is in use.

Common Use Cases and Queries

Typical scenarios include pre-import reconciliation, duplicate detection, and troubleshooting of stalled or repeated interface loads. The following examples reflect the grouped nature of the view.

Listing all distinct staged sources and batches for an operating unit:

  • SELECT transaction_source, batch_name, orig_transaction_reference FROM apps.pa_trx_intf_orig_transref_v WHERE org_id = :p_org_id;

Confirming whether a specific original reference has already reached the interface:

  • SELECT transaction_source, batch_name, org_id FROM apps.pa_trx_intf_orig_transref_v WHERE orig_transaction_reference = :p_ref;

Enumerating distinct batches per source to size a pending import:

  • SELECT transaction_source, COUNT(*) FROM apps.pa_trx_intf_orig_transref_v GROUP BY transaction_source;

Because the view provides no row counts or amounts, it should be paired with direct queries against PA_TRANSACTION_INTERFACE when transactional detail is required. Its value lies in supplying a lightweight, pre-grouped inventory of interface content keyed by the original transaction reference.