Search Results ar_archive_detail

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.

• Document identity: ARCHIVE_ID, TRANSACTION_CLASS, TRANSACTION_TYPE, TRANSACTION_ID, TRANSACTION_LINE_ID, LINE_NUMBER.
• Line and item attributes: ITEM_NAME, LINE_DESCRIPTION, QUANTITY, UNIT_SELLING_PRICE, UOM_CODE, LINE_TYPE.
• Amounts: AMOUNT, ACCTD_AMOUNT, plus the applied amounts LINE_APPLIED, TAX_APPLIED, FREIGHT_APPLIED, RECEIVABLES_CHARGES_APPLIED and the corresponding ACCTD_AMOUNT_APPLIED_FROM / ACCTD_AMOUNT_APPLIED_TO.
• Accounting context: ACCOUNT_COMBINATION1 through ACCOUNT_COMBINATION4, GL_DATE, GL_POSTED_DATE, ACCOUNTING_RULE_NAME, RULE_DURATION, RULE_START_DATE, LAST_PERIOD_TO_CREDIT.
• Adjustment and discount flags: LINE_ADJUSTED, TAX_ADJUSTED, FREIGHT_ADJUSTED, RECEIVABLES_CHARGES_ADJUSTED, EARNED_DISCOUNT_TAKEN, UNEARNED_DISCOUNT_TAKEN, FACTOR_DISCOUNT_AMOUNT.
• Taxation: TAX_CODE, TAX_RATE, TAX_PRECEDENCE, TAX_EXEMPTION_FLAG, TAX_EXEMPTION_ID, TAX_EXEMPTION_TYPE, TAX_LINE_ID.
• Currency: EXCHANGE_RATE_TYPE, EXCHANGE_RATE_DATE, EXCHANGE_RATE.
• Dates and collectibility: DUE_DATE, APPLY_DATE, ORIGINAL_COLLECTIBILITY_FLAG, LINE_COLLECTIBLE_FLAG, CONTINGENCY_CODE, EXPIRATION_DATE.
• Ship-to address snapshot: SHIP_TO_CUSTOMER_NUMBER, SHIP_TO_CUSTOMER_NAME, SHIP_TO_CUSTOMER_LOCATION, and the related ADDRESS1–4, CITY, STATE, COUNTRY, POSTAL_CODE columns.
• Extensibility: ATTRIBUTE_CATEGORY and ATTRIBUTE1–15, plus the INTERFACE_LINE_CONTEXT and INTERFACE_LINE_ATTRIBUTE1–15 group.

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.