Search Results ar_rate_adjustments_pk

Overview

AR_RATE_ADJUSTMENTS_ALL is a Receivables (AR) transaction table that stores information about adjustments made to the exchange rates of foreign currency receipts. When a receipt is entered in a foreign currency and the exchange rate associated with that receipt is subsequently revised — either manually or through Oracle's revaluation and rate-adjustment processes — Oracle Receivables records the before-and-after rate details, the resulting gain or loss, and the accounting distribution information in this table. It therefore serves as the audit and accounting record for foreign exchange rate adjustments on customer receipts.

The table resides in the AR schema and is documented with 46 columns in the ETRM 12.2.2 physical schema. Its primary key, AR_RATE_ADJUSTMENTS_PK, is defined on RATE_ADJUSTMENT_ID, and a unique index, AR_RATE_ADJUSTMENTS_U1, also exists on the same column. From a Data Vault modeling perspective, the metadata's foreign key structure suggests a satellite-leaning classification: the table hangs off AR_CASH_RECEIPTS_ALL and AR_POSTING_CONTROL through foreign keys and stores descriptive, change-oriented attributes (old and new rates, gain/loss amounts, GL dates) around a parent transaction. The _ALL suffix reflects the multi-organization architecture introduced in Release 11i, where ORG_ID scopes rows to a specific operating unit.

Key Information Stored

The most significant columns fall into several functional groups:

• RATE_ADJUSTMENT_ID — surrogate primary key, populated from the AR_RATE_ADJUSTMENTS_PK sequence; also the business-key candidate defined by the unique index AR_RATE_ADJUSTMENTS_U1.
• CASH_RECEIPT_ID — foreign key to AR_CASH_RECEIPTS_ALL, identifying the receipt whose exchange rate was adjusted.
• OLD_EXCHANGE_RATE / NEW_EXCHANGE_RATE — the exchange rate before and after the adjustment.
• OLD_EXCHANGE_RATE_TYPE / NEW_EXCHANGE_RATE_TYPE — the rate type (for example, Corporate or Spot) in effect before and after the adjustment.
• OLD_EXCHANGE_DATE / NEW_EXCHANGE_DATE — the effective dates of the prior and revised rates.
• GAIN_LOSS — the realized foreign exchange gain or loss amount generated by the rate change.
• GL_DATE / GL_POSTED_DATE — the accounting date and the date the adjustment was transferred to the General Ledger.
• POSTING_CONTROL_ID — foreign key to AR_POSTING_CONTROL, governing the posting cycle for the adjustment.
• ORG_ID — the operating unit that owns the adjustment in the multi-org model.
• MRC_OLD_EXCHANGE_RATE, MRC_NEW_EXCHANGE_RATE, MRC_GAIN_LOSS — Multiple Reporting Currency (MRC) counterparts of the core rate and gain/loss columns.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — concurrent program and request tracking for the process that created the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle flexfield descriptive columns.

Common Use Cases and Queries

Typical uses include reconciling foreign exchange gains and losses on receipts, auditing rate changes for compliance, and reporting adjustments by operating unit or accounting period.

To list all adjustments for a given receipt:

SELECT rate_adjustment_id, old_exchange_rate, new_exchange_rate,
       old_exchange_rate_type, new_exchange_rate_type,
       gain_loss, gl_date, gl_posted_date
FROM   ar_rate_adjustments_all
WHERE  cash_receipt_id = :receipt_id;

To summarize unrealized versus posted gains and losses by period:

SELECT gl_date, SUM(gain_loss)
FROM   ar_rate_adjustments_all
WHERE  org_id = :org_id
GROUP  BY gl_date
ORDER  BY gl_date;

To find adjustments not yet transferred to GL, filter on GL_POSTED_DATE IS NULL and join to AR_POSTING_CONTROL for the posting status.

Related Objects

• AR_CASH_RECEIPTS_ALL — parent receipt table; joined on AR_RATE_ADJUSTMENTS_ALL.CASH_RECEIPT_ID = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID.
• AR_POSTING_CONTROL — joined on POSTING_CONTROL_ID; determines the GL posting cycle.
• AR_MC_RATE_ADJUSTMENTS — Multiple Reporting Currency detail table; joins on AR_MC_RATE_ADJUSTMENTS.RATE_ADJUSTMENT_ID = AR_RATE_ADJUSTMENTS_ALL.RATE_ADJUSTMENT_ID.
• AR_RATE_ADJUSTMENTS — base (non-_ALL) counterpart view/table used in single-org contexts.
• AR_RECEIVABLES_TRX_ALL and AR_CASH_RECEIPT_HISTORY_ALL — receipt transaction and history tables commonly queried alongside for full receipt lifecycle reporting.