Search Results ar_trx_summary_hist_u1

Overview

AR.AR_TRX_SUMMARY_HIST is a transactional history table in the Oracle E-Business Suite Receivables (AR) module. As documented in the ETRM metadata for releases 12.1.1 and 12.2.2, the table stores the history of all modifications performed on transactions that would affect the data held in the Receivables summary tables. Whenever an AR business flow raises a business event, the history rows written to this table are consumed by the subscriptions of those business events in order to refresh and reconcile the corresponding summary table. The object therefore functions as an intermediate change-capture and audit structure rather than as a primary transactional store.

From a Data Vault modeling perspective, the mined foreign key structure around HISTORY_ID, CUSTOMER_TRX_ID, PAYMENT_SCHEDULE_ID, and PREVIOUS_HISTORY_ID suggests a link classification, since the table primarily records relationships and associations between transactions, payment schedules, payments, and their successor or predecessor history records. It also carries descriptive measures such as amounts and due dates, which in Data Vault terms behave like satellite attributes attached to that link. This classification is a heuristic suggestion derived from the FK topology, not a documented Oracle design statement.

Key Information Stored

The table is owned by the AR schema, resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and exposes 22 documented columns. The most operationally significant are listed below.

• HISTORY_ID — the unique history record identifier; it is the surrogate primary key and the column behind the unique index AR_TRX_SUMMARY_HIST_U1, the business-key candidate for this object.
• CUSTOMER_TRX_ID — identifies the transaction whose modification generated the history row; indexed by the nonunique index AR_TRX_SUMMARY_HIST_N1.
• PAYMENT_SCHEDULE_ID — identifies the payment schedule (installment) of the transaction; forms the leading column of the nonunique composite index AR_TRX_SUMMARY_HIST_N2.
• PREVIOUS_HISTORY_ID — the unique identifier of the prior history record for the same transaction, enabling chained traversal of changes; indexed together with PAYMENT_SCHEDULE_ID.
• CASH_RECEIPT_ID — identifies the payment associated with the change.
• DUE_DATE — the due date of the installment affected by the modification.
• AMOUNT_DUE_ORIGINAL, AMOUNT_ADJUSTED, AMOUNT_IN_DISPUTE, and AMOUNT_DUE_REMAINING — the monetary measures captured at the time of the change, covering original, adjusted, disputed, and remaining balances.
• CUSTOMER_ID and SITE_USE_ID — the customer account and site use associated with the transaction.
• CURRENCY_CODE, TRX_DATE, EVENT_NAME, and INSTALLMENTS — the currency, transaction date, originating business event name, and installment context for the record.
• Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — provide the audit trail.

Common Use Cases and Queries

Typical scenarios include diagnosing why a summary balance diverges from transaction detail, auditing the sequence of changes to a specific installment, and feeding downstream reporting on disputed or adjusted amounts.

Retrieving the change chain for a transaction:

• SELECT history_id, previous_history_id, customer_trx_id, payment_schedule_id, due_date, amount_due_original, amount_due_remaining FROM ar.ar_trx_summary_hist WHERE customer_trx_id = :p_trx_id ORDER BY creation_date;

Isolating disputed balances for a customer:

• SELECT customer_id, customer_trx_id, amount_in_dispute, currency_code FROM ar.ar_trx_summary_hist WHERE customer_id = :p_customer_id AND amount_in_dispute > 0;

Reconstructing the latest state of an installment via PREVIOUS_HISTORY_ID chaining is also common when reconciling the summary tables after a failed business-event subscription. Because EVENT_NAME captures the originating business event, queries filtered on EVENT_NAME are useful for tracing which AR flow produced a given change.

Related Objects

The documented foreign keys and dependent objects include:

• HZ_CUST_ACCOUNTS — joined via CUSTOMER_ID to resolve customer account details.
• HZ_CUST_SITE_USES_ALL — joined via SITE_USE_ID to resolve the customer site use.
• AR.AR_PAYMENT_SCHEDULES_ALL — joined via PAYMENT_SCHEDULE_ID, the installment record affected by the change.
• AR.RA_CUSTOMER_TRX_ALL — joined via CUSTOMER_TRX_ID as the parent transaction header.
• AR.AR_CASH_RECEIPTS_ALL — joined via CASH_RECEIPT_ID to resolve the associated receipt.
• CS_KB_HISTORIES_B — referenced through HISTORY_ID per the documented foreign key relationship.
• AR summary tables and the AR business event subscriptions — the primary consumers of this history data, which update summary balances from these rows.