AP Open Items 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
Imported from BI Publisher Description: 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, 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_ledgers, hr_all_organization_units, 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
Related Reports
AP Open Balances Revaluation, GL Account Distribution Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Analysis, PO Procure to Pay Tracker, GL Account Analysis (Drilldown), INV Material Account Distribution Detail
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 Items Revaluation 30-Oct-2020 191455.xlsx |
| Blitz Report™ XML Import | AP_Open_Items_Revaluation.xml |
| SQL Source | AP Open Items Revaluation.sql |
| Full Report on Enginatics | www.enginatics.com/reports/ap-open-items-revaluation/ |
Case Study & Technical Analysis: AP Open Items Revaluation
1. Executive Summary
Business Problem
In a multi-currency environment, the value of unpaid invoices fluctuates with exchange rates. Accounting standards require these “monetary liabilities” to be revalued at the closing rate of each financial period. The difference between the booked rate and the closing rate represents an “Unrealized Gain or Loss.” Finance teams need a detailed schedule to substantiate this GL entry and to analyze currency exposure.
Solution Overview
The AP Open Items Revaluation report is the standard tool for this analysis. It lists every open foreign currency invoice, its original exchange rate, the new period-end rate, and the resulting variance. Unlike the legacy “Balances” report, this version is optimized for the Subledger Accounting (SLA) architecture, providing a direct link between the operational invoice and the financial impact.
Key Benefits
- Audit Trail: Provides the line-level detail supporting the high-level Revaluation journal in the General Ledger.
- Exposure Analysis: Allows Treasurers to see total open liability by currency (e.g., “We owe 5M EUR, and the rate moved 2%”).
- SLA Integration: Fully aligned with XLA tables to ensure the “Accounted Amount” matches the Trial Balance.
2. Technical Analysis
Core Tables and Views
AP_OPEN_ITEMS_REVAL_GT: The primary driver. This Global Temporary table is populated by theAP_OPEN_ITEMS_REVAL_PKGpackage before the report runs. It contains the calculated revaluation data.AP_INVOICES_ALL: Joins to provide invoice header details (Invoice Date, Number).XLA_AE_LINES/XLA_DISTRIBUTION_LINKS: Used to trace the original accounting of the invoice to ensure the “Booked” balance is correct.GL_DAILY_RATES: Source of the revaluation rate.
SQL Logic and Data Flow
- Initialization: The user runs the report, which triggers a PL/SQL package to calculate open balances and populate
AP_OPEN_ITEMS_REVAL_GT. - Extraction: The Blitz Report SQL queries this GT table.
- Enrichment: It joins to
HR_ALL_ORGANIZATION_UNITSfor Operating Unit names andGL_LEDGERSfor Ledger context. - Formatting: The query formats the output to show
Original_Rate,Revaluation_Rate, andUnrealized_Gain_Loss.
Integration Points
- General Ledger: The report is designed to tie out to the GL Revaluation process.
- Payables: Reflects the payment status as of the “Revaluation Period” end date.
3. Functional Capabilities
Parameters & Filtering
- Revaluation Period: Defines the “As Of” date for the analysis.
- Rate Type: The conversion rate type (e.g., Corporate, Spot) used to fetch the closing rate.
- Include Up to Due Date: Allows filtering invoices based on their maturity.
- Balancing Segment: Useful for running revaluation for a specific legal entity or cost center.
Performance & Optimization
- Pre-Calculation: Because the heavy lifting (balance calculation) is done by the PL/SQL package into a temp table, the extraction SQL is extremely fast and efficient.
4. FAQ
Q: Does this report create accounting entries? A: No, this is a reporting tool. The actual GL Revaluation journal is created by the “Revalue Balances” program in the General Ledger module. This report explains why that journal has that value.
Q: What happens if an invoice is partially paid? A: The report calculates revaluation only on the remaining unpaid portion of the invoice.
Q: Why is the “Unrealized Gain/Loss” zero for some lines? A: If the exchange rate hasn’t changed between the invoice date and the revaluation date, or if the invoice is in the functional currency, the variance is zero.
Useful Links
- 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
© Enginatics GmbH