Results for “ar_trx_summary_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AR.AR_TRX_SUMMARY is a transaction summary table in the Oracle Receivables (AR) module of Oracle E-Business Suite. It stores pre-aggregated Receivables metrics — invoice values and counts, cash receipts values and counts, credit and debit memo totals, adjustments, deposits, earned and unearned discounts, and derived credit metrics — for a defined window of activity. Rather than recalculating these figures from AR_PAYMENT_SCHEDULES and AR_CASH_RECEIPTS on demand, Oracle Receivables persists them here so that high-volume dashboards, customer profile screens, and collection workbenches can retrieve totals with minimal query cost.

The lowest level of granularity at which data is stored and retrieved is a specified AS_OF_DATE for a given CURRENCY, at a specified bill-to SITE_USE_ID, for a specific CUST_ACCOUNT_ID of a party, within an ORG_ID. This five-column grain is enforced physically by the unique index AR_TRX_SUMMARY_U1 (CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, AS_OF_DATE, ORG_ID). Because every row is a point-in-time measurement keyed by a composite business key rather than a single generated surrogate, the table leans toward a Data Vault satellite classification: the combination of account, site use, currency, organization, and date forms the effective hub/link key, while the metric columns are the descriptive payload. This is a modeling suggestion only; the physical implementation is a conventional Oracle table with standard Who columns.

Key Information Stored

The identifying columns are CUST_ACCOUNT_ID (Customer Account Identifier), SITE_USE_ID (Site Use Identifier), ORG_ID (Organization identifier), CURRENCY (currency code), and AS_OF_DATE (the date for which the summary record was created). In the physical schema these five columns together constitute the unique business key — AR_TRX_SUMMARY_U1 — rather than a single surrogate primary key. No separate surrogate primary key column is documented; the unique index above is the documented business-key candidate.

The most significant metric columns are:

• TOTAL_INVOICES_VALUE and TOTAL_INVOICES_COUNT — aggregate invoiced amount and invoice count for the period.
• TOTAL_CASH_RECEIPTS_VALUE and TOTAL_CASH_RECEIPTS_COUNT — aggregate cash receipts amount and count.
• OP_BAL_HIGH_WATERMARK and OP_BAL_HIGH_WATERMARK_DATE — the highest Open Receivables Balance observed for the account, site, currency, and date, where the balance is the sum of amount_due_remaining across payment schedules.
• TOTAL_CREDIT_MEMOS_VALUE / TOTAL_CREDIT_MEMOS_COUNT and TOTAL_DEBIT_MEMOS_VALUE / TOTAL_DEBIT_MEMOS_COUNT — memo transaction totals.
• TOTAL_ADJUSTMENTS_VALUE and TOTAL_ADJUSTMENTS_COUNT — adjustment activity totals.
• TOTAL_DEPOSITS_VALUE and TOTAL_DEPOSITS_COUNT — deposit totals.
• INV_PAID_AMOUNT, INV_INST_PMT_DAYS_SUM, COUNT_OF_INV_INST_PAID, and COUNT_OF_INV_INST_PAID_LATE — payment behavior and days-to-pay metrics.
• DAYS_CREDIT_GRANTED_SUM — cumulative days of credit granted.
• NSF_STOP_PAYMENT_COUNT and NSF_STOP_PAYMENT_AMOUNT — non-sufficient-funds and stop-payment activity.
• LARGEST_INV_AMOUNT, LARGEST_INV_DATE, LARGEST_INV_CUST_TRX_ID — the single largest invoice in the window.
• REFERENCE_1 through REFERENCE_5 — descriptive flexfield-style reference attributes.

Standard Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) provide audit tracking. The table is stored in tablespace APPS_TS_TX_DATA with PCTFREE 10; both indexes reside in APPS_TS_TX_IDX.

Common Use Cases and Queries

Typical uses include customer profile and credit-review screens, collections dashboards, aging and exposure reporting, and trend analysis of open balance versus credit limits. A straightforward lookup by account and site follows the unique index:

• SELECT as_of_date, currency, total_invoices_value, total_cash_receipts_value, op_bal_high_watermark FROM ar.ar_trx_summary WHERE cust_account_id = :p_account AND site_use_id = :p_site AND org_id = :p_org ORDER BY as_of_date DESC;
• Trending open balance over time: select as_of_date, op_bal_high_watermark from ar.ar_trx_summary where cust_account_id = :p_account and currency = :p_currency order by as_of_date;
• Payment-behavior analysis using inv_inst_pmt_days_sum, count_of_inv_inst_paid, and count_of_inv_inst_paid_late to derive average days-to-pay and late-payment ratios per account.
• Exposure ranking: join to hz_cust_accounts and order by op_bal_high_watermark to identify the largest open receivables balances across a portfolio.

Queries should always supply CUST_ACCOUNT_ID and SITE_USE_ID where possible, since AR_TRX_SUMMARY_N1 (SITE_USE_ID, CURRENCY, AS_OF_DATE) is the only secondary access path and it does not include the account column.

Related Objects

The documented foreign keys anchor this table to the Trading Community Architecture (TCA) customer model, and the metric population depends on the core Receivables transaction tables:

• HZ_CUST_ACCOUNTS — joined on AR_TRX_SUMMARY.CUST_ACCOUNT_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID (documented FK); supplies party and account attributes.
• HZ_CUST_SITE_USES_ALL — joined on AR_TRX_SUMMARY.SITE_USE_ID = HZ_CUST_SITE_USES_ALL.SITE_USE_ID (documented FK); supplies bill-to site context.
• AR_PAYMENT_SCHEDULES — source of amount_due_remaining used to compute OP_BAL_HIGH_WATERMARK and the aggregate metric values.
• AR_CASH_RECEIPTS — source of the total cash receipts metrics.
• RA_CUSTOMER_TRX_ALL — source of invoice, credit memo, and debit memo values and counts; also the parent of LARGEST_INV_CUST_TRX_ID.
• AR_ADJUSTMENTS — source of adjustment totals.
• FND_USER — referenced by LAST_UPDATED_BY and CREATED_BY Who columns.
• FND_LOGINS — referenced by LAST_UPDATE_LOGIN.

Because the table is a derived summary store, it is typically refreshed by concurrent programs and reporting extracts rather than written directly by application users.