AP Open Balances Revaluation – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Payables · all Payables reports

Overview

Description: AP Open Balances Revaluation Report

This report is in the same format as the AP Open Balances Revaluation Report which is no longer supported and should no loinger be used. The data source for this report is the same as the AP Open Items Revaluation report.

Application: Payables Source: Open Items Revaluation Report (XML) Short Name: APOPITRN DB package: AP_OPEN_ITEMS_REVAL_PKG

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CGST Rate, SGST Rate, IGST Rate, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Operating Unit, 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, Include Domestic Invoice, Transaction Currency, Supplier

Oracle EBS Tables Used

gl_ledgers, hr_all_organization_units, ap_invoices_all, ap_open_items_reval_gt, ap_invoice_payments_all, ap_checks_all, xla_transaction_entities, xla_ae_headers, xla_ae_lines, ap_invoice_distributions_all, xla_distribution_links

Report Categories

Enginatics, R12 only

AP Open Items Revaluation, GL Account Analysis, PO Procure to Pay Tracker, GL Account Distribution Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), AP Period Close Exceptions, AP Invoices and Lines, GL Account Analysis 11g

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output AP Open Balances Revaluation 30-Oct-2020 190225.xlsx
Blitz Report™ XML Import AP_Open_Balances_Revaluation.xml
SQL Source AP Open Balances Revaluation.sql
Full Report on Enginatics www.enginatics.com/reports/ap-open-balances-revaluation/

Case Study & Technical Analysis: AP Open Balances Revaluation

1. Executive Summary

Business Problem

Multinational organizations frequently transact in foreign currencies. As exchange rates fluctuate, the value of open liabilities (unpaid invoices) changes relative to the functional currency. For accurate financial reporting (ASC 830 / IAS 21), companies must “revalue” these open balances at month-end to reflect the spot rate, booking the difference as an unrealized gain or loss. Failure to do so results in misstated liabilities and inaccurate P&L.

Solution Overview

The AP Open Balances Revaluation report (and its modern counterpart, AP Open Items Revaluation) calculates the revalued amount of all open foreign currency invoices as of a specific period end. It compares the original accounted amount (at the transaction date rate) with the revalued amount (at the period-end rate) to derive the unrealized gain/loss. This report serves as the supporting schedule for the General Ledger revaluation journal.

Key Benefits

2. Technical Analysis

Core Tables and Views

This report relies on a mix of transactional tables and Subledger Accounting (SLA) data:

SQL Logic and Data Flow

The report is unique because it often relies on the data generated by the standard Oracle “Open Items Revaluation” concurrent program.

Integration Points

3. Functional Capabilities

Parameters & Filtering

Performance & Optimization

4. FAQ

Q: Why is this report marked as “Legacy”? A: Oracle has transitioned to the “Open Items Revaluation” architecture. While this report format is still used by some for its specific layout, the underlying data source is the same.

Q: Why don’t I see my invoice? A: Ensure the invoice was open (unpaid) as of the “Revaluation Period” end date and that it is in a foreign currency. Functional currency invoices do not require revaluation.

© Enginatics GmbH