Search Results ar_trx_bal_summary




Overview
Explore in AI Chat

Overview

AR_TRX_BAL_SUMMARY is a Receivables (AR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores pre-aggregated transactional balance and metrics for a customer account, customer site, currency, and operating unit combination. Its documented purpose is to hold summary fields such as Total Invoices Value, Total Invoices Count, and Total Cash Receipts Value for a specified period, at the lowest level of granularity at which the data can be stored. Rather than requiring on-the-fly aggregation of high-volume transaction tables, the collector populates this table so that dashboards, Dunning Letters, and Collections workbenches can retrieve customer-level totals quickly.

From a heuristic Data Vault perspective (mined from the foreign key structure), AR_TRX_BAL_SUMMARY is classified as satellite-leaning. This suggests that the table is best modeled as a descriptive satellite attached to the customer account and site-use hubs, holding measured attributes (values, counts, dates) rather than serving as an independent hub or a pure link between entities. Its grain is defined by CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, and ORG_ID, each of which acts as a dimension key.

Key Information Stored

The table contains 41 documented columns. The most operationally significant fields fall into several categories:

Audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) track record history. No single surrogate primary key column is documented; the unique index AR_TRX_BAL_SUMMARY_U1 on (CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, ORG_ID) serves as the business-key candidate, enforcing one summarized row per account, site, currency, and operating unit.

Common Use Cases and Queries

AR_TRX_BAL_SUMMARY supports Collections dashboards, Dunning Letter generation, and customer aging reporting where near-real-time aggregation is impractical. A typical query retrieves total and open balances for a customer:

  • SELECT cust_account_id, site_use_id, currency, BEST_CURRENT_RECEIVABLES, TOTAL_DSO_DAYS_CREDIT, PAST_DUE_INV_VALUE FROM ar.ar_trx_bal_summary WHERE cust_account_id = :p_cust_account_id AND org_id = :p_org_id;
  • Aggregating a collector's portfolio: SELECT cust_account_id, SUM(PAST_DUE_INV_VALUE) overdue, SUM(UNRESOLVED_CASH_VALUE) unresolved FROM ar.ar_trx_bal_summary GROUP BY cust_account_id;
  • Dunning eligibility: filtering on DUNNING_COUNT and LAST_DUNNING_DATE to identify accounts due for another letter.

Because the table is a summary, it must be refreshed by the appropriate Collection/Receivables programs before reporting is run; stale data can understate or overstate balances.

Related Objects

Together these relationships confirm that AR_TRX_BAL_SUMMARY acts as a customer-centric satellite aggregating Receivables transaction activity.