Search Results ra_customer_trx_u1

Overview

The APPS.AR_RECEIVABLE_APPLICATIONS_V view is a reporting and integration construct within the Oracle E-Business Suite Receivables (AR) module, available in releases 12.1.1 and 12.2.2. It presents a unified, denormalized projection of receivables application activity, combining cash receipt applications and credit memo applications into a single readable result set. The view is designed to expose the essential attributes of an application — the receipt or transaction applied, the invoice or debit memo (transaction) against which the application was made, the amount applied, discounts taken, and remaining balances — without requiring consumers to perform multiple joins against the underlying normalized AR tables.

Because Receivables stores application data across AR_RECEIVABLE_APPLICATIONS and associated payment schedule and receipt tables, this view serves as the canonical read-only interface for reporting, extract, and downstream integration purposes. It is defined with an ORDERED hint and explicit index directives, indicating that performance tuning was applied at the view level to steer the optimizer toward specific access paths.

Underlying Base Objects

The documented metadata for ETRM 12.2.2 lists the following referenced base objects: ARPT_SQL_FUNC_UTIL (package), ARP_DEDUCTION (package), AR_CASH_RECEIPTS (synonym), AR_CONS_INV (synonym), AR_LOOKUPS (view), AR_PAYMENT_SCHEDULES (synonym), AR_RECEIVABLES_TRX (synonym), AR_RECEIVABLE_APPLICATIONS (synonym), HZ_CUST_ACCOUNTS (synonym), HZ_CUST_SITE_USES (synonym), HZ_PARTIES (synonym), RA_BATCH_SOURCES (synonym), RA_CUSTOMER_TRX (synonym), RA_CUSTOMER_TRX_LINES (synonym), and RA_CUST_TRX_TYPES (synonym).

The primary driving table is AR_RECEIVABLE_APPLICATIONS, aliased in the view text as APP. The payment schedule (AR_PAYMENT_SCHEDULES, alias PS_INV) is joined to supply transaction numbers, due dates, terms, and remaining balances. AR_CASH_RECEIPTS (CR) provides the receipt number, while RA_CUSTOMER_TRX (CT) and RA_CUST_TRX_TYPES (CTT) anchor transaction-level descriptors. Customer identity is resolved through HZ_PARTIES, HZ_CUST_ACCOUNTS, and HZ_CUST_SITE_USES. Supporting lookups include RA_BATCH_SOURCES, RA_TERMS_U1, and AR_RECEIVABLES_TRX.

Key Columns

• ROW_ID — the ROWID of the underlying AR_RECEIVABLE_APPLICATIONS row.
• CASH_RECEIPT_ID — identifier of the applied cash receipt.
• CUSTOMER_TRX_ID — identifier of the invoice or debit memo being applied to.
• CM_CUSTOMER_TRX_ID — populated via TO_NUMBER(''), yielding a null credit memo transaction identifier in this projection.
• RECEIPT_NUMBER — the human-readable receipt number from AR_CASH_RECEIPTS.
• APPLIED_FLAG — hard-coded to 'Y', indicating the row represents an applied transaction.
• CUSTOMER_ID / CUSTOMER_NAME / CUSTOMER_NUMBER — customer identity attributes resolved from HZ tables and the customer account.
• TRX_NUMBER — the transaction number of the invoice being applied against.
• INSTALLMENT — the terms sequence number, derived conditionally from the applied payment schedule.
• AMOUNT_APPLIED and AMOUNT_APPLIED_FROM — the amount applied, with the latter defaulting to AMOUNT_APPLIED when no explicit source amount exists.
• TRANS_TO_RECEIPT_RATE — the exchange rate used to translate the transaction amount to the receipt currency.
• DISCOUNT / DISCOUNTS_EARNED / DISCOUNTS_UNEARNED — discount amounts taken, with the aggregate DISCOUNT summing earned and unearned components.
• AMOUNT_DUE_REMAINING, DUE_DATE, STATUS, and TERM_ID — payment schedule attributes describing the outstanding balance and terms.
• Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_ID, REQUEST_ID) are carried through from the base application row.

Common Use Cases and Queries

This view is commonly used to report cash applications against invoices, reconcile receipts to transactions, and support AR aging and collection analysis. A typical query retrieves applied receipts for a given customer:

• SELECT receipt_number, trx_number, amount_applied, discount, due_date, status FROM apps.ar_receivable_applications_v WHERE customer_id = :p_customer_id;
• SELECT trx_number, SUM(amount_applied) applied_total FROM apps.ar_receivable_applications_v WHERE customer_trx_id = :p_trx_id GROUP BY trx_number;
• SELECT receipt_number, SUM(amount_applied) FROM apps.ar_receivable_applications_v WHERE cash_receipt_id = :p_receipt_id GROUP BY receipt_number;

Note that the search term ra_customer_trx_u1 corresponds to the unique index on RA_CUSTOMER_TRX, one of the underlying base objects of this view; querying the view does not require that index to be named explicitly, but the optimizer’s access path to RA_CUSTOMER_TRX relies on such indexes for efficient joins.