| layout | default |
|---|---|
| title | AR Open Items Revaluation | Oracle EBS SQL Report |
| description | Imported from BI Publisher Description: Open Items Revaluation Report Application: Receivables Source: Open Items Revaluation Report (XML) Short Name… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, BI Publisher, Enginatics, R12 only, Open, Items, Revaluation, gl_translation_rates, ar_receivable_applications, ar_cash_receipt_history |
| permalink | /AR%20Open%20Items%20Revaluation/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Imported from BI Publisher Description: Open Items Revaluation Report Application: Receivables Source: Open Items Revaluation Report (XML) Short Name: ARXINREV_XML DB package: AR_ARXINREV_XMLP_PKG
Ledger, Operating Unit, Report Format, Revaluation Period, Include Up To Due Date, Rate Type, Daily Rate Type, Daily Rate Date, Balancing Segment Low, Balancing Segment High, Transferred To GL only, Cleared only
gl_translation_rates, ar_receivable_applications, ar_cash_receipt_history, ar_adjustments, ra_cust_trx_types, hz_cust_accounts, hz_parties, hz_locations, hz_cust_acct_sites, hz_party_sites, hz_cust_site_uses, gl_code_combinations, gl_sets_of_books, fnd_currencies, hr_all_organization_units_vl, ra_customer_trx, ar_xla_ctlgd_lines_v, ar_payment_schedules, gl_period_statuses
BI Publisher, Enginatics, R12 only
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 Items Revaluation - Default 11-Oct-2023 030916.xlsx |
| Blitz Report™ XML Import | AR_Open_Items_Revaluation.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/ar-open-items-revaluation/ |
The AR Open Items Revaluation report is the XML Publisher-based standard report for calculating foreign exchange (FX) gains and losses on open receivables. It is an essential month-end compliance tool that ensures assets held in foreign currencies are reported at their current fair value, as required by accounting standards like IAS 21 and FAS 52.
Global organizations hold receivables in multiple currencies. As exchange rates fluctuate between the invoice date and the reporting date, the functional currency value of these assets changes.
- Financial Accuracy: Reporting receivables at historical rates can significantly overstate or understate the company's financial position.
- Manual Effort: Calculating the FX impact for thousands of individual invoices and credit memos manually is impractical and error-prone.
- Audit Trail: Auditors require a clear, itemized list showing the original rate, the closing rate, and the resulting adjustment for every revalued transaction.
The AR Open Items Revaluation report streamlines the revaluation process:
- Automated Calculation: It automatically retrieves the closing exchange rate for the selected period and applies it to all open foreign currency balances.
- Detailed Analysis: It lists each invoice, showing the original amount, the open amount, the original exchange rate, the revaluation rate, and the calculated gain or loss.
- Flexibility: Users can run the report in "Detail" mode for auditing or "Summary" mode for booking journal entries.
This report (ARXINREV_XML) is built on Oracle's XML Publisher technology, offering a more modern output format compared to older text-based reports.
- Open Items:
AR_PAYMENT_SCHEDULESis the source of truth for what is currently owed. - Transaction Data:
RA_CUSTOMER_TRXprovides the invoice number, date, and original currency details. - Exchange Rates:
GL_DAILY_RATES(orGL_TRANSLATION_RATES) supplies the period-end rates used for revaluation. - Accounting:
AR_XLA_CTLGD_LINES_VandGL_CODE_COMBINATIONShelp identify the specific GL accounts (e.g., Trade Receivables) associated with the transactions.
- Scope: Identifies all transactions that are "Open" (have a remaining balance) as of the end of the "Revaluation Period."
- Rate Selection: Uses the "Rate Type" (e.g., Corporate, Spot) and "Daily Rate Date" (usually period-end) to fetch the new exchange rate.
- Gain/Loss Formula: $$ \text{Unrealized Gain/Loss} = (\text{Open Amount} \times \text{New Rate}) - (\text{Open Amount} \times \text{Original Rate}) $$
- Revaluation Period: The accounting period for which the revaluation is being performed (e.g., 'Oct-23').
- Report Format:
- Detail: Lists every invoice.
- Summary: Aggregates by Currency and GL Account.
- Rate Type: The source of the revaluation rate (e.g., 'Corporate').
- Transferred to GL only: If set to 'Yes', restricts the report to transactions that have already been posted to the General Ledger, ensuring alignment between subledger and GL.
- Cleared only: For receipts, filters for those that have cleared the bank.
Q: How does this differ from the "AR Open Balances Revaluation" report?
A: Both reports perform similar calculations. This version (ARXINREV_XML) is the newer XML Publisher version, which typically offers better formatting and potentially different parameter options compared to the older text-based or RDF versions.
Q: Why is the "Unrealized Gain/Loss" zero for some items? A: This happens if the revaluation rate is identical to the original rate, or if the item is in the functional currency (and "Include Domestic Invoices" is not applicable or set to No).
Q: Does this report automatically create the journal entry? A: No, this report calculates and displays the figures. The actual journal creation is done by the "Revaluation" process in the General Ledger or the "Receivables Revaluation" program, depending on your setup.
- 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