AR Open Items Revaluation – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

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

Report Parameters

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

Oracle EBS Tables Used

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

Report Categories

BI Publisher, Enginatics, R12 only

Running This SQL Without Blitz Report

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.

Download & Import Options

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/

AR Open Items Revaluation - Case Study & Technical Analysis

Executive Summary

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.

Business Challenge

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.

Solution

The AR Open Items Revaluation report streamlines the revaluation process:

Technical Architecture

This report (ARXINREV_XML) is built on Oracle’s XML Publisher technology, offering a more modern output format compared to older text-based reports.

Key Tables & Joins

Logic

  1. Scope: Identifies all transactions that are “Open” (have a remaining balance) as of the end of the “Revaluation Period.”
  2. Rate Selection: Uses the “Rate Type” (e.g., Corporate, Spot) and “Daily Rate Date” (usually period-end) to fetch the new exchange rate.
  3. Gain/Loss Formula: \(\text{Unrealized Gain/Loss} = (\text{Open Amount} \times \text{New Rate}) - (\text{Open Amount} \times \text{Original Rate})\)

Parameters

FAQ

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.

© 2026 Enginatics