Search Results ar_trx_summary

Overview

AR.AR_TRX_SUMMARY is a Receivables (AR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores pre-aggregated transactional metrics for a customer account, site, and currency across a defined accounting period. Rather than recalculating totals from the base transaction tables (AR.AR_CASH_RECEIPTS_ALL, AR.RA_CUSTOMER_TRX_ALL, AR.AR_ADJUSTMENTS_ALL, and so on) each time a report or dashboard is rendered, Oracle maintains periodic snapshots of invoice values and counts, cash receipt values and counts, credit memo and debit memo activity, discounts earned and unearned, adjustments, deposits, chargebacks, and payment-timing statistics. The lowest grain of data is the combination of customer account, site use, currency, and as-of date.

Under a heuristic Data Vault classification mined from the foreign-key structure, AR_TRX_SUMMARY is assessed as satellite-leaning: it holds descriptive, periodically refreshed measures keyed to business entities (account, site) rather than acting as an integration hub or a many-to-many link. This classification is a modeling suggestion only; in EBS terms it functions as a summary/aggregate table that supports credit management, collections, and receivables reporting.

Key Information Stored

The table contains 50 documented columns. The candidate business key is defined by the unique index AR_TRX_SUMMARY_U1 over CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, AS_OF_DATE, ORG_ID. There is no exposed surrogate sequence key in the documented metadata; the composite unique key effectively serves as the identifying key, with CUST_ACCOUNT_ID and SITE_USE_ID carrying the foreign-key relationships.

CUST_ACCOUNT_ID — the customer account being summarized; foreign key to HZ_CUST_ACCOUNTS.
SITE_USE_ID — the specific customer site use; foreign key to HZ_CUST_SITE_USES_ALL.
CURRENCY — the currency in which the summarized monetary values are denominated.
AS_OF_DATE — the point in time the aggregated figures reflect.
ORG_ID — the operating unit, providing multi-org partitioning.
TOTAL_INVOICES_VALUE / TOTAL_INVOICES_COUNT — aggregate invoice amount and number of invoices for the period.
TOTAL_CASH_RECEIPTS_VALUE / TOTAL_CASH_RECEIPTS_COUNT — aggregate receipt amount and receipt count.
TOTAL_CREDIT_MEMOS_VALUE / TOTAL_CREDIT_MEMOS_COUNT — credit memo totals.
TOTAL_ADJUSTMENTS_VALUE / TOTAL_ADJUSTMENTS_COUNT — adjustment activity totals.
INV_PAID_AMOUNT and INV_INST_PMT_DAYS_SUM — amount paid against invoices and the summed days-to-payment across installments.
SUM_APP_AMT / SUM_APP_AMT_DAYS_LATE — applied amount and the summed lateness of applications.
OP_BAL_HIGH_WATERMARK / OP_BAL_HIGH_WATERMARK_DATE — the peak open balance and the date it was reached.
LARGEST_INV_AMOUNT / LARGEST_INV_DATE / LARGEST_INV_CUST_TRX_ID — reference to the largest invoice in the period.
NSF_STOP_PAYMENT_COUNT / NSF_STOP_PAYMENT_AMOUNT — non-sufficient-funds and stop-payment statistics.

Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and five generic REFERENCE_1 through REFERENCE_5 columns complete the documented set.

Common Use Cases and Queries

The primary use case is credit and collections analysis, where the summary rows feed credit limit reviews, aging-equivalent trend reporting, and customer risk scoring without a full scan of transaction tables. Finance reporting also leverages the table for period-over-period receivables movement.

A typical query retrieving the most recent snapshot for a customer and site:

SELECT cust_account_id, site_use_id, currency, as_of_date, total_invoices_value, total_invoices_count, total_cash_receipts_value, sum_app_amt FROM ar.ar_trx_summary WHERE cust_account_id = :p_cust_account_id AND org_id = :p_org_id ORDER BY as_of_date DESC;

Trend analysis across dates uses the AS_OF_DATE key to build time series, for example comparing TOTAL_CASH_RECEIPTS_VALUE spanning successive periods to observe collection velocity. A high-watermark review compares OP_BAL_HIGH_WATERMARK against current exposure for credit-limit monitoring. Multi-org reporting filters on ORG_ID to isolate a specific operating unit.

Related Objects

The foreign-key relationships documented for AR_TRX_SUMMARY anchor it to the Trading Community Architecture:

HZ_CUST_ACCOUNTS — joined via AR_TRX_SUMMARY.CUST_ACCOUNT_ID; the customer account master.
HZ_CUST_SITE_USES_ALL — joined via AR_TRX_SUMMARY.SITE_USE_ID; the customer site use master.

Because the summary is derived, its values reconcile against the base Receivables tables, principally RA_CUSTOMER_TRX_ALL, AR_CASH_RECEIPTS_ALL, AR_ADJUSTMENTS_ALL, and the application and payment-history tables referenced by SUMP_APP_AMT and INV_INST_PMT_DAYS_SUM. Customer-facing reporting typically blends the summary with AR_PAYMENT_SCHEDULES_ALL to reconcile summarized totals to open balances, while Customer Master and Collection APIs supply the account context that keys each summary row.

Table: AR_TRX_SUMMARY 12.2.2
owner:AR, object_type:TABLE, fnd_design_data:AR.AR_TRX_SUMMARY, object_name:AR_TRX_SUMMARY, status:VALID, product: AR - Receivables , description: This table stores the fields, such as 'Total Invoices Value', 'Total Invoices Count', 'Total Cash Receipts Value', etc., that require summarization for a specified period of time. The lowest level of granularity at which the data can be st , implementation_dba_data: AR.AR_TRX_SUMMARY ,
Table: AR_TRX_SUMMARY 12.1.1
owner:AR, object_type:TABLE, fnd_design_data:AR.AR_TRX_SUMMARY, object_name:AR_TRX_SUMMARY, status:VALID, product: AR - Receivables , description: This table stores the fields, such as 'Total Invoices Value', 'Total Invoices Count', 'Total Cash Receipts Value', etc., that require summarization for a specified period of time. The lowest level of granularity at which the data can be st , implementation_dba_data: AR.AR_TRX_SUMMARY ,
Concurrent Program: ARSUMREF 12.1.1
execution_filename: ar_trx_summary_pkg.refresh_all , product: AR - Receivables , user_name: ARSUMREF , description: AR summary tables complete refresh program , argument_method: Standard , enabled: Yes , execution_method: PL/SQL Stored Procedure ,
Concurrent Program: ARSUMREF 12.2.2
execution_filename: ar_trx_summary_pkg.refresh_all , product: AR - Receivables , user_name: ARSUMREF , description: AR summary tables complete refresh program , argument_method: Standard , enabled: Yes , execution_method: PL/SQL Stored Procedure ,
Table: HZ_CUST_SITE_USES_ALL 12.1.1
owner:AR, object_type:TABLE, fnd_design_data:AR.HZ_CUST_SITE_USES_ALL, object_name:HZ_CUST_SITE_USES_ALL, status:VALID, product: AR - Receivables , description: Stores business purposes assigned to customer account sites. , implementation_dba_data: AR.HZ_CUST_SITE_USES_ALL ,
Table: HZ_CUST_SITE_USES_ALL 12.2.2
owner:AR, object_type:TABLE, fnd_design_data:AR.HZ_CUST_SITE_USES_ALL, object_name:HZ_CUST_SITE_USES_ALL, status:VALID, product: AR - Receivables , description: Stores business purposes assigned to customer account sites. , implementation_dba_data: AR.HZ_CUST_SITE_USES_ALL ,