Search Results ar_archive_detail




Overview
Explore in AI Chat

Overview

AR_ARCHIVE_DETAIL is a Receivables (AR) module table in the Oracle E-Business Suite 12.1.1 / 12.2.2 schema, owned by the AR schema and catalogued as VALID. Its documented purpose is to store line and distribution-level archive information. The table preserves a denormalized, reportable snapshot of transaction lines—including their distribution/accounting attributes, tax characteristics, application (payment-matching) amounts, adjustment flags, exchange-rate detail, and ship-to customer addressing—so that historical receivables activity remains available for audit, reconciliation, and regulatory reporting even after the live transaction data has been purged or moved through the standard AR archiving and purge programs.

In the ETRM 12.2.2 physical schema the table is documented with 118 columns, making it one of the wider archive structures in the AR data model. The metadata's heuristic Data Vault classification is standalone; from a dimensional-modeling perspective this suggests the table behaves less like a strict hub, link, or satellite and more like an independent, self-contained detail store whose lineage to operational tables is maintained through foreign-key references rather than through a tightly modeled business-key dependency.

Key Information Stored

The table is keyed at archive level by ARCHIVE_ID, which functions as the surrogate primary key tying detail rows back to the archive header. Business-key candidates are formed by the combination of TRANSACTION_ID with TRANSACTION_LINE_ID and, where distributions are stored, DISTRIBUTION_TYPE; the parallel RELATED_TRANSACTION_CLASS / RELATED_TRANSACTION_TYPE / RELATED_TRANSACTION_ID / RELATED_TRANSACTION_LINE_ID columns support cross-referencing between a line and its originating or applied-against document.

Common Use Cases and Queries

Typical reporting queries reconcile archived line totals against the corresponding transaction or distribution, or reconstruct the applied and adjustment history of a specific invoice line. A representative pattern joins the detail back to the header through ARCHIVE_ID:

  • SELECT d.transaction_id, d.transaction_line_id, d.item_name, d.amount, d.acctd_amount, d.tax_code, d.gl_date FROM ar.ar_archive_detail d WHERE d.archive_id = :archive_id ORDER BY d.line_number;
  • Tax reconciliation: filter on TAX_LINE_ID or TAX_CODE and aggregate ACCTD_AMOUNT for a fiscal period.
  • Collections and aging analysis: use the collectibility, contingency, and EXPIRATION_DATE columns to reproduce historical aging.
  • Discount analysis: compare EARNED_DISCOUNT_TAKEN and UNEARNED_DISCOUNT_TAKEN against ACCTD_EARNED_DISC_TAKEN and ACCTD_UNEARNED_DISC_TAKEN.

Related Objects

The documented foreign keys show AR_ARCHIVE_DETAIL.MOVEMENT_ID referencing MTL_MOVEMENT_STATISTICS and AR_ARCHIVE_DETAIL.TAX_LINE_ID referencing ZX_LINES, confirming downstream linkage to Inventory movement statistics and the E-Business Tax engine. The header table that owns ARCHIVE_ID (the AR archive header) is the principal parent. Transaction-line detail is related to RA_CUSTOMER_TRX_LINES and RA_CUSTOMER_TRX_ALL in the operational schema, while distributions map to RA_CUST_TRX_LINE_GL_DIST_ALL. Applied and adjustment activity relates to AR_RECEIVABLE_APPLICATIONS_ALL and AR_ADJUSTMENTS_ALL, and currency/tax context is shared with ZX_LINES and the GL account combinations referenced by ACCOUNT_COMBINATION1–4.