Search Results ar_open_trx_v




Overview
Explore in AI Chat

Overview

AR_OPEN_TRX_V is a reporting view owned by the APPS schema in Oracle E-Business Suite Receivables. It is documented in the ETRM as a Release 115 object, meaning its definition originates from the earliest supported 11i code line and is retained in subsequent releases, including 12.1.1 and 12.2.2. The view is declared VALID and, per its embedded comment, is "mainly used in applications LOVs to show open transactions." It therefore serves as a denormalized, ready-to-display projection of open (unpaid or partially paid) receivable transactions, joining transaction header, payment schedule, customer, site, currency, and terms data into a single flat row per payment schedule.

Rather than requiring developers to construct joins across RA_CUSTOMER_TRX, AR_PAYMENT_SCHEDULES, and the customer model, AR_OPEN_TRX_V presents formatted and unformatted balances, descriptive lookups, and cross-reference identifiers in one place. This makes it suitable both for list-of-values (LOV) definitions and for ad hoc reporting against Receivables open items.

Underlying Base Objects

The documented base objects referenced by the view are: ARPT_SQL_FUNC_UTIL (package), ARP_VIEW_CONSTANTS (package), FND_CURRENCY (package), FND_DATE (package), AR_CONS_INV (synonym), AR_PAYMENT_SCHEDULES (synonym), RA_CUSTOMER_TRX (synonym), RA_CUST_TRX_TYPES (synonym), RA_TERMS (synonym), RA_BATCH_SOURCES (synonym), HZ_CUST_ACCOUNTS (synonym), HZ_CUST_SITE_USES (synonym), and HZ_PARTIES (synonym).

The query is driven by the payment schedule (aliased PS) joined to the transaction (CT) and transaction type (CTT), with outer lookups to the customer account, party, site use, terms, and batch source. The ORDERED USE_NL hint in the view text indicates the optimizer is directed to drive from the payment schedule and nested-loop into the remaining tables, consistent with LOV execution where a small number of rows are expected. Utility packages supply formatting: FND_CURRENCY.GET_FORMAT_MASK formats the balance for display, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING translates the transaction class into a readable meaning, and FND_DATE supports date handling.

Key Columns

Common Use Cases and Queries

The view is typically consumed where open transactions must be listed or selected, such as LOVs on application and receipt entry forms, and in lightweight reporting of outstanding receivables.

SELECT trx_number, customer_number, customer_name,
       trx_due_date, balance_due_curr, class_meaning
FROM   apps.ar_open_trx_v
WHERE  customer_id = :customer_id
ORDER BY trx_due_date;

A second common pattern filters by status and due date to isolate overdue items:

SELECT trx_number, trx_date, balance_due_curr, status
FROM   apps.ar_open_trx_v
WHERE  status = 'OP'
AND    trx_due_date < SYSDATE
AND    balance_due_curr > 0;

Because the view embeds formatting logic and lookup decoding, joining it to further tables should be limited to cases where the additional attributes justify the cost. For high-volume extractions, the underlying base tables are preferable.