Results for “ar_adjustments_all”

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

Overview

AR_ADJUSTMENTS_ALL is the core Oracle Receivables transaction table that stores both pending and approved invoice adjustments. Every manual write-off, discount, credit adjustment, chargeback offset, and tax or freight correction applied against a customer transaction is persisted here before it is posted to the General Ledger. Because it holds the authoritative record of every adjustment event, it functions as the transactional backbone for receivables balance reconciliation and revenue leakage analysis.

The table resides in the AR schema and is classified as a hub under the heuristic Data Vault modeling derived from its foreign key structure. This classification reflects its role as the central anchor of an adjustment-related star: it carries the durable business key, survives across source systems, and is referenced by numerous satellite-style children such as distributions, approval history, and multiorganization (MRC) mirrors. In EBS 12.1.1 and 12.2.2, the table is identical in structure, though 12.2.2 exposes 95 documented columns.

Key Information Stored

The surrogate primary key is ADJUSTMENT_ID, enforced by the unique index AR_ADJUSTMENTS_U1. A second unique index, AR_ADJUSTMENTS_U2, covers ADJUSTMENT_NUMBER combined with ORG_ID, making that pair the principal business-key candidate; ADJUSTMENT_NUMBER is the human-readable identifier users recognize on the Adjustments window.

The most significant descriptive and financial columns include:

• AMOUNT — the adjustment value in transaction currency; ACCTD_AMOUNT — the accounted (functional currency) equivalent.
• TYPE and ADJUSTMENT_TYPE — classify the adjustment (for example invoice versus chargeback, and the specific reason class).
• STATUS — tracks approval state, distinguishing pending from approved adjustments.
• CUSTOMER_TRX_ID and CUSTOMER_TRX_LINE_ID — the invoice header and line being adjusted.
• PAYMENT_SCHEDULE_ID — links the adjustment to the installment it reduces.
• CODE_COMBINATION_ID — the GL account receiving the adjustment.
• APPLY_DATE, GL_DATE, and GL_POSTED_DATE — govern application timing and accounting period impact.
• APPROVED_BY — the approver, validated against approval limits.
• REASON_CODE, COMMENTS, and POSTABLE — support auditability and posting eligibility.
• ORG_ID and SET_OF_BOOKS_ID — provide multiorganization and ledger context.
• RECEIVABLES_CHARGES_ADJUSTED, LINE_ADJUSTED, FREIGHT_ADJUSTED, and TAX_ADJUSTED — flag which portion of the invoice was adjusted.

Common Use Cases and Queries

Typical scenarios include reconciling adjustment activity to the GL, auditing approvals, and analyzing write-off trends by reason code. A representative query lists pending adjustments for a given set of books:

• SELECT adjustment_number, customer_trx_id, amount, status FROM ar_adjustments_all WHERE set_of_books_id = :p_sob AND status = 'P' ORDER BY creation_date;
• ... WHERE customer_trx_id = :p_trx AND postable = 'Y'; — retrieves postable adjustments tied to a single invoice.
• ... WHERE reason_code = :p_reason AND gl_date BETWEEN :p_start AND :p_end; — summarizes adjustments by reason for a period.

Reporting on AR_ADJUSTMENTS_ALL commonly feeds aging reports (via AR_PAYMENT_SCHEDULES_ALL) and distribution/accounting extracts (via AR_DISTRIBUTIONS_ALL), where SOURCE_ID resolves back to ADJUSTMENT_ID.

Related Objects

The table participates in dense referential relationships:

• RA_CUSTOMER_TRX_ALL — joined on CUSTOMER_TRX_ID (the adjusted invoice); also via SUBSEQUENT_TRX_ID for chargebacks.
• RA_CUSTOMER_TRX_LINES_ALL — joined on CUSTOMER_TRX_LINE_ID, and inversely references this table through BR_ADJUSTMENT_ID.
• AR_PAYMENT_SCHEDULES_ALL — joined on PAYMENT_SCHEDULE_ID; also references ADJUSTMENT_ID_LAST.
• AR_DISTRIBUTIONS_ALL and AR_MC_DISTRIBUTIONS_ALL — accounting lines where SOURCE_ID = ADJUSTMENT_ID.
• AR_MC_ADJUSTMENTS — multiorganization mirror keyed by ADJUSTMENT_ID.
• AR_APPROVAL_ACTION_HISTORY — approval workflow audit trail.
• AR_CASH_RECEIPTS_ALL and AR_RECEIVABLE_APPLICATIONS_ALL — linked via ASSOCIATED_CASH_RECEIPT_ID and ASSOCIATED_APPLICATION_ID.
• GL_CODE_COMBINATIONS — resolves CODE_COMBINATION_ID to accounting flexfield values.
• AR_BATCHES_ALL — groups adjustments through BATCH_ID.