AR European Sales Listing
Description:
Summary report listing sales by country and currency code, with transaction amount / currency and accounted amount/ currency
Parameters
Detail/Summary, Ledger, From Date, To Date, Branch (to which remitted), Site Reported, EU Countries Only
Used tables
hz_cust_acct_sites, hz_party_sites, hz_locations, hz_cust_acct_sites_all, fnd_territories_vl, xle_etb_profiles, zx_party_tax_profile, zx_registrations, gl_ledgers, hr_operating_units, ra_customer_trx_all, ra_cust_trx_types_all, ra_customer_trx_lines_all, ra_cust_trx_line_gl_dist_all, hz_cust_site_uses_all, mo_glob_org_access_tmp, dual
Categories
Related reports
Dependencies
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
Example Report
AR European Sales Listing 25-Jul-2017 120805.xlsx
Report SQL
www.enginatics.com/reports/ar-european-sales-listing/
Blitz Report™ import options
AR European Sales Listing - Case Study & Technical Analysis
Executive Summary
The AR European Sales Listing (often referred to as the EC Sales List or ESL) is a critical compliance report for organizations trading within the European Union. It summarizes sales of goods and services to VAT-registered customers in other EU member states. This report facilitates the preparation of statutory declarations required by tax authorities to monitor cross-border trade and combat VAT fraud.
Business Challenge
EU VAT laws require businesses to submit periodic statements listing the total value of supplies made to each customer in other EU countries.
- Compliance Risk: Failure to report or incorrect reporting can lead to significant fines and audits.
- Data Complexity: The data requires linking financial transactions with customer master data (VAT numbers) and geographical data (Country codes), which are often stored in different modules.
- Currency Conversion: Transactions may be in various currencies, but reporting must often be in the functional currency of the reporting entity.
Solution
The AR European Sales Listing report automates the data gathering process:
- VAT Number Validation: It retrieves the Tax Registration Number (TRN) for each customer, a mandatory field for the ESL.
- Geographical Filtering: The “EU Countries Only” parameter ensures that only relevant intra-community supplies are included, excluding domestic sales or exports to non-EU nations.
- Aggregation: It groups transactions by Customer and Country, summing the values to provide the line items needed for the tax return.
Technical Architecture
The report integrates financial transaction data with the Tax and Trading Community Architecture (TCA) models.
Key Tables & Joins
- Transactions:
RA_CUSTOMER_TRX_ALLandRA_CUSTOMER_TRX_LINES_ALLprovide the invoice details and amounts. - Distributions:
RA_CUST_TRX_LINE_GL_DIST_ALLis used to get the accounted amounts (functional currency). - Customer Location:
HZ_LOCATIONS(viaHZ_CUST_ACCT_SITES_ALL) determines the “Ship-To” country, which defines the destination of the goods. - Tax Profiles:
ZX_PARTY_TAX_PROFILEandZX_REGISTRATIONSare queried to fetch the customer’s VAT registration number. - Legal Entity:
XLE_ETB_PROFILESidentifies the legal entity responsible for the reporting.
Logic
- Scope: Selects AR transactions (Invoices, Credit Memos, Debit Memos) within the date range.
- Location Check: Checks the country code of the Ship-To address. If “EU Countries Only” is selected, it filters against a list of EU member state codes.
- Tax Calculation: Aggregates the net transaction amounts (excluding VAT) as ESL reporting typically requires the net value of supplies.
- Currency: Reports both the entered currency amount and the accounted (ledger) currency amount.
Parameters
- Ledger: Specifies the accounting book.
- From/To Date: Defines the reporting period (usually monthly or quarterly).
- Detail/Summary:
- Summary: One line per Customer/Country (for the tax form).
- Detail: Lists individual invoices (for internal audit and reconciliation).
- EU Countries Only: A flag to restrict the output to intra-community trade.
- Site Reported: Filters by specific business sites if the entity has multiple locations.
FAQ
Q: Does this report include VAT amounts? A: Typically, the EC Sales List requires the value of supplies (Net Amount), not the VAT amount itself, as the customer accounts for the VAT (Reverse Charge mechanism).
Q: How are Credit Memos handled? A: Credit Memos are subtracted from the total sales value for the period. If the net total is negative, it is reported as such (depending on specific country rules).
Q: Why is a customer missing from the report? A: Ensure the customer has a valid “Ship-To” address in an EU country and that the transaction date falls within the selected range. Also, verify that the customer has a VAT number defined in their Tax Profile.
Oracle E-Business Suite Reporting Library
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.
The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.
You can download and use Blitz Report free of charge for your first 30 reports.
The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
Useful Links
Blitz Report™ – World’s fastest data upload and reporting for Oracle EBS
Oracle Discoverer replacement – importing worksheets into Blitz Report™
Blitz Report™ Questions & Answers
Supply Chain Hub by Blitz Report™
© 2025 Enginatics