Search Results xtr_ar_open_trx_v




Overview
Explore in AI Chat

Overview

XTR_AR_OPEN_TRX_V is a PL/SQL view owned by the APPS schema and maintained within the XTR – Treasury product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The object carries a VALID status and is documented in ETRM as the data source for the Hedge Positions form. Its purpose is to expose a consolidated, report-ready set of open Accounts Receivable transactions and unreconciled cash receipts so that Treasury users can associate hedge instruments with underlying exposed positions.

The view presents a unified transaction stream by combining two logically distinct populations through a UNION ALL. The first branch surfaces open AR transactions sourced through XTR_AR_OPEN_APLD_TRX_V, including invoice and debit memo rows that remain unapplied or partially applied. The second branch surfaces open cash receipt positions derived directly from AR cash receipt, payment schedule, and receivable application tables. This design allows the Hedge Positions form to display both receivable-side and receipt-side exposure from a single query surface, avoiding duplicate extraction logic across Treasury components.

Underlying Base Objects

The view is defined over a documented set of base objects. The first UNION branch reads exclusively from XTR_AR_OPEN_APLD_TRX_V, a Treasury view that itself aggregates applied and open AR transactions. The second branch joins a wide set of AR and Trading Community Architecture objects: AR_CASH_RECEIPTS_ALL, AR_PAYMENT_SCHEDULES_ALL, AR_RECEIVABLE_APPLICATIONS_ALL, and AR_CASH_RECEIPT_HISTORY_ALL from the Receivables schema, together with HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTIES, HZ_PARTY_SITES, and HZ_LOCATIONS from the customer model. HR_OPERATING_UNITS supplies the operating unit name, GL_LEDGER_LE_V provides the primary ledger context, and XTR_PARTIES_V supplies legal entity and party code attributes. FND_MESSAGE is invoked to retrieve the seeded lookup string for cash receipt transaction types, and XTR_USER_ACCESS governs row-level access for the Treasury user.

Because the second branch filters on APS.CLASS = 'PMT', APS.STATUS = 'OP', and excludes reversed or reversal-pending receipts via AR_CASH_RECEIPT_HISTORY_ALL, only genuine open cash positions are returned. Amounts are aggregated with SUM(NVL((-1)*ARA.AMOUNT_APPLIED, 0)) and grouped by receipt, producing one row per open cash receipt position.

Key Columns

  • CUSTOMER_TRX_ID / PAYMENT_SCHEDULE_ID – Identifiers of the transaction and its payment schedule; for cash receipt rows the receipt identifier is returned in the CUSTOMER_TRX_ID column position.
  • CUST_TRX_TYPE_ID / TRX_TYPE_NAME – Transaction type classification; the cash branch substitutes -99999 and a message-driven label for cash receipts.
  • ORG_ID / ORG_NAME – Operating unit identifier and name, used for security and reporting grouping.
  • CUSTOMER_ID / CUSTOMER_NAME / COMPANY_CODE – Customer account, party name, and legal entity party code from XTR_PARTIES_V.
  • TRX_DATE / TRX_NUMBER – Transaction or receipt date and document number.
  • CURRENCY_CODE / SOB_CURRENCY_CODE – Transaction currency and set of books (ledger) currency, enabling currency exposure analysis.
  • AMOUNT – Outstanding or applied amount exposed to hedge consideration.
  • MIN_DUE_DATE – Earliest due date, supporting maturity-based hedging decisions.
  • APPLIED_TRX – Flag indicating whether the row originates from the applied-transaction branch ('Y') or the cash receipt branch ('N').

Common Use Cases and Queries

The primary consumer is the Hedge Positions form, which filters this view by operating unit and currency to present open exposures. Beyond the form, the view is useful for ad hoc Treasury reporting, exposure dashboards, and reconciliation extracts.

Typical query for open exposures by currency:

SELECT org_name, currency_code, customer_name, trx_number,
       trx_date, amount, min_due_date
  FROM apps.xtr_ar_open_trx_v
 WHERE org_id = :p_org_id
   AND currency_code = :p_currency
 ORDER BY min_due_date;

Separating receivable exposure from cash receipt exposure using the APPLIED_TRX flag:

SELECT applied_trx, COUNT(*) row_count, SUM(amount) total_amount
  FROM apps.xtr_ar_open_trx_v
 WHERE org_id = :p_org_id
 GROUP BY applied_trx;

Because access is mediated by XTR_USER_ACCESS, queries executed under a Treasury responsibility automatically observe the user's authorized operating units. The view should be treated as read-only; all maintenance occurs in the underlying AR and HZ tables.