Search Results ar_open_items_v




Overview
Explore in AI Chat

Overview

The AR_OPEN_ITEMS_V view is a Receivables (AR) reporting object owned by the APPS schema in Oracle E-Business Suite. It is documented under Release 11.5 (Release 115 Only) and remains available in 12.1.1 and 12.2.2 as a VALID view. Its stated purpose, embedded directly in the view text comment, is to expose open transactions for use primarily in application List of Values (LOVs).

Functionally, the view flattens the transactional core of Receivables — payment schedules, customer transaction headers, receivables applications, cash receipts, and customer master data — into a single denormalized rowset with pre-formatted and unformatted balance columns. This makes it attractive for LOV queries, quick reconciliation screens, and lightweight custom reporting where the developer needs a ready-to-display transaction identifier, customer identity, currency, and outstanding balance without writing the join logic themselves.

Because the view is defined with Release 11.5-era logic (it still references legacy HZ_* synonyms and helper packages such as ARPT_SQL_FUNC_UTIL and ARP_DEDUCTION), it is best treated as a compatibility object rather than a modern analytics source. It exposes formatted currency strings produced by FND_CURRENCY.GET_FORMAT_MASK and canonical date conversion via FND_DATE.CANONICAL_TO_DATE, meaning its output is presentation-oriented.

Underlying Base Objects

The documented base objects underlying the view span the Receivables transaction, application, and customer model:

  • AR_PAYMENT_SCHEDULES (alias PS) — the driving transaction/installment table supplying trx number, class, currency, dues, discounts, terms, and GL date.
  • AR_RECEIVABLE_APPLICATIONS (alias APP) — cash and credit applications used to derive remaining balances.
  • RA_CUSTOMER_TRX (alias CT) and RA_CUST_TRX_TYPES (alias CTT) — transaction header and type definitions, including over-application and natural-application flags.
  • AR_CASH_RECEIPTS (alias CR) — receipt information used for payment-class transaction dates.
  • HZ_CUST_ACCOUNTS, HZ_PARTIES, and HZ_CUST_SITE_USES — customer account number, party name, and ship-to site location.
  • RA_TERMS (alias T) — payment term flags such as discount-on-lines and partial discount.
  • Supporting objects: RA_BATCH_SOURCES, AR_CONS_INV, and the packages ARPT_SQL_FUNC_UTIL, ARP_DEDUCTION, FND_CURRENCY, and FND_DATE.

The joins are keyed on customer_id, cust_trx_type_id, payment_schedule_id, term_id, and site_use/location identifiers, producing one consolidated row per schedule/application context.

Key Columns

Common Use Cases and Queries

Typical usage includes LOV population for transaction selection, customer-level open item listings, and quick balance reconciliation. A representative query follows:

SELECT trx_number
     , customer_name
     , trx_type
     , trx_date
     , balance_due_curr
FROM   apps.ar_open_items_v
WHERE  customer_id = :p_customer_id
AND    balance_due_curr_unformatted <> 0
ORDER BY trx_due_date;

A second pattern filters by class to isolate invoices or debit memos for aging analysis:

SELECT trx_number, class_meaning, balance_due_functional
FROM   apps.ar_open_items_v
WHERE  class IN ('INV','DM')
AND    trx_gl_date BETWEEN :p_from AND :p_to;

Because the view resolves LOV semantics and formatting internally, it reduces development effort for customer-facing open item pickers. However, developers should validate performance on large data sets, since the underlying joins against application and party tables are not indexed for this specific predicate combination, and should prefer base-table queries for high-volume or analytical workloads.