Search Results balance_due_functional




Overview
Explore in AI Chat

Overview

APPS.AR_OPEN_TRX_V is a supplementary Oracle E-Business Suite view owned by the APPS schema and registered under FND Design Data as AR.AR_OPEN_TRX_V. Its documented status is VALID. As a supplementary view, it exists primarily to simplify Forms coding within the Receivables module rather than to serve as a supported public interface. Oracle's own documentation carries an explicit caution that querying or altering data through this view is not recommended, because its definition may change dramatically between minor or major releases. In practice, however, the view is widely referenced by technical consultants, report developers, and integration specialists who need a denormalized, presentation-ready projection of open (unpaid or partially paid) Receivables transactions without writing the full join logic against RA_CUSTOMER_TRX and AR_PAYMENT_SCHEDULES themselves.

The view resolves customer, site, transaction type, currency, and payment schedule information into a single flat row per transaction installment, exposing both formatted and unformatted balance amounts. This makes it a convenient source for dashboards, aging extracts, collection workbooks, and ad hoc reconciliation queries, provided the consumer accepts the caveat that it is not a certified integration object.

Underlying Base Objects

The documented dependency list for this view includes both synonyms to core Receivables tables and supporting PL/SQL packages. The synonyms resolve to the following base objects:

  • RA_CUSTOMER_TRX — the transaction header, supplying transaction number, type, dates, currency, and purchase order.
  • AR_PAYMENT_SCHEDULES — the installment-level record that carries balance due, original amount, discount amounts, and status.
  • RA_CUST_TRX_TYPES — transaction type definition and its class.
  • HZ_CUST_ACCOUNTS, HZ_CUST_SITE_USES, and HZ_PARTIES — the Trading Community Architecture tables that supply customer number, customer name, and location.
  • RA_TERMS — payment terms and their sequence.
  • RA_BATCH_SOURCES — the batch source used at transaction creation.
  • AR_CONS_INV — the consolidated invoice synonym used in the underlying query logic.

Programmatic behavior is provided by the packages ARPT_SQL_FUNC_UTIL, ARP_VIEW_CONSTANTS, FND_CURRENCY, and FND_DATE. These packages supply currency formatting and date handling, which is why the view can return a display-ready value in BALANCE_DUE_CURR alongside the raw numeric value in BALANCE_DUE_CURR_UNFORMATTED.

Key Columns

Common Use Cases and Queries

The most frequent requirement is a list of open transactions with their raw numeric balance, since the formatted column is unsuitable for aggregation. A typical query is:

SELECT trx_number, customer_number, customer_name,
       trx_due_date, invoice_currency_code,
       balance_due_curr_unformatted
FROM   apps.ar_open_trx_v
WHERE  balance_due_curr_unformatted > 0
AND    trx_due_date < SYSDATE
ORDER BY trx_due_date;

Because BALANCE_DUE_CURR_UNFORMATTED is a NUMBER, it supports SUM, AVG, and CASE logic directly, enabling aging buckets or customer-level exposure summaries. Analysts building collection or credit-limit reports typically group by CUSTOMER_ID and sum this column, while drilldown reports correlate PAYMENT_SCHEDULE_ID back to AR_PAYMENT_SCHEDULES. Integration developers sometimes use the view to stage open-transaction data into external systems, although for production integrations the documented base tables remain the recommended source given the view's non-certified status.