| layout | default |
|---|---|
| title | AR Open Balances Revaluation | Oracle EBS SQL Report |
| description | Report: AR Open Balances Revaluation Report Application: Receivables Source: AR Open Balances Revaluation Report Short Name: AROBRR |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, Open, Balances, Revaluation, ar_receivable_applications, ar_adjustments, hr_operating_units |
| permalink | /AR%20Open%20Balances%20Revaluation/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Report: AR Open Balances Revaluation Report Application: Receivables Source: AR Open Balances Revaluation Report Short Name: AROBRR
Ledger, Operating Unit, As of Date, Exchange Rate Type, Currency, User Rate Type Exchange Rate, Include Domestic Invoices, Customer
ar_receivable_applications, ar_adjustments, hr_operating_units, gl_ledgers, ra_customer_trx, ar_payment_schedules, ra_cust_trx_types, ra_cust_trx_line_gl_dist, gl_code_combinations, xla_distribution_links, xla_ae_lines, hz_cust_accounts, hz_parties, ar_system_parameters, gl_import_references, gl_je_headers, ar_lookups, ar_cash_receipts, ar_cash_receipt_history, ar_distributions
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
| Resource | Link |
|---|---|
| Excel Example Output | AR Open Balances Revaluation - Default 11-Oct-2023 065223.xlsx |
| Blitz Report™ XML Import | AR_Open_Balances_Revaluation.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/ar-open-balances-revaluation/ |
The AR Open Balances Revaluation report is a key financial closing tool for multinational organizations. It calculates the unrealized foreign exchange (FX) gains or losses on outstanding customer invoices. Accounting standards (such as US GAAP and IFRS) mandate that monetary assets denominated in foreign currencies be revalued at the end of each reporting period using the closing exchange rate. This report provides the supporting detail for these adjustments.
When an invoice is issued in a foreign currency (e.g., EUR) but the company's books are kept in another (e.g., USD), the value of that receivable fluctuates with the exchange rate.
- Volatility: Significant currency swings can materially impact the balance sheet.
- Compliance: Failure to revalue open balances results in incorrect asset valuation and violates accounting principles.
- Complexity: Manually tracking the original rate vs. the current rate for thousands of open invoices and partial payments is prone to error.
The AR Open Balances Revaluation report automates the valuation process by:
- Snapshotting: Identifying all open receivables as of a specific "As of Date."
- Rate Comparison: Retrieving the original exchange rate used at the time of the transaction and comparing it to the closing rate for the period.
- Calculation: Computing the difference (Unrealized Gain/Loss) for each transaction and summarizing it by currency and account.
The report logic involves simulating the revaluation process without necessarily posting it.
- Open Balances:
AR_PAYMENT_SCHEDULEScontains the remaining amount due for each invoice. - Transaction Details:
RA_CUSTOMER_TRXprovides the original exchange rate and currency code. - Market Rates:
GL_DAILY_RATESis queried to find the exchange rate for the "As of Date" based on the selected Rate Type (e.g., Corporate, Spot). - Accounting:
GL_CODE_COMBINATIONSandXLA_AE_LINESmay be referenced to determine the Receivables account associated with the transaction.
- Selection: Finds all transactions with a non-zero balance as of the parameter date.
- Rate Retrieval: Looks up the revaluation rate. If a rate is missing for the specific date, it may look back to the last available rate depending on configuration.
- Computation: $$ \text{Revalued Amount} = \text{Open Foreign Amount} \times \text{Revaluation Rate} $$ $$ \text{Unrealized Gain/Loss} = \text{Revalued Amount} - \text{Open Functional Amount} $$
- As of Date: The valuation date (usually the last day of the month).
- Exchange Rate Type: The rate type to use for revaluation (e.g., 'Month End', 'Corporate').
- Currency: Option to run for a specific currency or all currencies.
- Include Domestic Invoices: Typically set to 'No', as domestic transactions do not generate FX gains/losses unless the functional currency differs from the reporting currency.
Q: Does this report post entries to the GL? A: No, this is a reporting tool. The actual "Revaluation" process in the General Ledger or the "Revaluation" program in AR is responsible for creating the journal entries. This report is used to validate those figures.
Q: Why is the revaluation rate missing? A: If the report shows a missing rate, ensure that the Daily Rates are defined in the General Ledger for the "As of Date" and the selected "Exchange Rate Type."
Q: How are partial payments handled? A: The report revalues only the remaining open balance of the invoice, not the original full amount.
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics