Search Results ar_rate_adjustments_n1

Overview

AR.AR_RATE_ADJUSTMENTS_ALL is a transactional table in the Oracle Receivables (AR) schema that stores information about currency exchange rate adjustments applied to receipts. Oracle Receivables permits users to adjust a receipt's exchange rate, rate type, or rate date both before and after posting to General Ledger, and this table captures one row for each receipt subject to such an adjustment. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are maintained in APPS_TS_TX_IDX. Each rate adjustment generates two new sets of receipt applications: the first reverses the existing applications using the old rate, and the second re-creates the applications using the new rate. This mechanism ensures that the accounting impact of the rate change is fully reflected in the application records.

From a Data Vault modeling perspective, the metadata's heuristic classification places this table as satellite-leaning. The table is descriptive and transactional in nature, keyed by a single surrogate identifier and heavily dependent on its parent receipt. It should be treated as a candidate satellite attached to the receipt hub rather than as an independent hub or link.

Key Information Stored

The table carries 46 documented columns, of which the following are the most operationally significant:

• RATE_ADJUSTMENT_ID – The surrogate primary key (NUMBER(15)), declared via AR_RATE_ADJUSTMENTS_PK. It is also the sole business-key candidate exposed through the unique index AR_RATE_ADJUSTMENTS_U1.
• CASH_RECEIPT_ID – Identifies the payment (receipt) against which the adjustment was made; a foreign key to AR_CASH_RECEIPTS_ALL.
• OLD_EXCHANGE_RATE and NEW_EXCHANGE_RATE – The exchange rate before and after the adjustment.
• OLD_EXCHANGE_RATE_TYPE and NEW_EXCHANGE_RATE_TYPE – The rate type (for example, Corporate or Spot) before and after the change.
• OLD_EXCHANGE_DATE and NEW_EXCHANGE_DATE – The rate date values before and after adjustment.
• GAIN_LOSS – The realized gain or loss resulting from the rate change.
• GL_DATE and GL_POSTED_DATE – The accounting date and the date on which the adjustment was posted to General Ledger; both are indexed (via AR_RATE_ADJUSTMENTS_N1 and AR_RATE_ADJUSTMENTS_N2).
• POSTING_CONTROL_ID – Foreign key to AR_POSTING_CONTROL, linking the adjustment to its posting cycle.
• ORG_ID – The operating unit that owns the adjustment, supporting multi-org reporting.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – Standard WHO audit columns.

The columns prefixed with MRC_ (for example, MRC_OLD_EXCHANGE_RATE, MRC_NEW_EXCHANGE_DATE, MRC_GAIN_LOSS) hold the corresponding values for Multiple Reporting Currencies, enabling reporting in secondary ledgers.

Common Use Cases and Queries

Typical reporting scenarios include reconciling exchange rate adjustments against receipts, tracing gain/loss amounts posted to GL, and auditing adjustments made after GL posting. A representative query joining the adjustment to its parent receipt:

SELECT ra.rate_adjustment_id, ra.cash_receipt_id, cr.receipt_number,
       ra.old_exchange_rate, ra.new_exchange_rate,
       ra.gl_date, ra.gl_posted_date, ra.gain_loss
  FROM ar.ar_rate_adjustments_all ra,
       ar.ar_cash_receipts_all cr
WHERE ra.cash_receipt_id = cr.cash_receipt_id
   AND ra.org_id = :p_org_id;

For period-end reporting, developers frequently filter on GL_DATE or GL_POSTED_DATE to isolate adjustments in a given accounting period. Adjustments that have not yet reached GL can be identified by comparing GL_DATE against a null or future GL_POSTED_DATE. The GAIN_LOSS column is a common aggregation target for foreign-exchange exposure analysis.

Related Objects

The following objects have the strongest documented relationships to AR_RATE_ADJUSTMENTS_ALL:

• AR.AR_CASH_RECEIPTS_ALL – Parent table of the receipt; joined on CASH_RECEIPT_ID.
• AR.AR_POSTING_CONTROL – Referenced through POSTING_CONTROL_ID, describing the posting run.
• AR.AR_MC_RATE_ADJUSTMENTS – Child table that references this table through RATE_ADJUSTMENT_ID, holding multiple-reporting-currency detail.
• AR.AR_RECEIVABLE_APPLICATIONS_ALL – Stores the receipt application sets generated by each adjustment (reverse-then-reapply pattern).
• AR.AR_CASH_RECEIPT_HISTORY_ALL – Captures the activity and posting history of the affected receipt.
• GL.GL_JE_LINES / GL.GL_JE_HEADERS – Hold the journal entries created for the exchange gain/loss when the adjustment is posted.
• FND_USER / FND_LOGINS – Resolved by the standard WHO columns for audit reporting.

Together these related objects allow complete reconstruction of the receipt, its applications, and the accounting entries produced by any rate adjustment recorded in this table.