AP Suppliers Revenue Summary – 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

AP suppliers (po vendors) revenue summary across different operating units

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

Ledger, Operating Unit, Supplier, Supplier Number, Country, As of Date, Summary Level

Oracle EBS Tables Used

gl_periods, gl_ledgers, iby_external_payees_all, iby_pmt_instr_uses_all, iby_ext_bank_accounts, ap_suppliers, ap_supplier_sites_all, fnd_territories_vl, ap_terms_tl, ap_invoices_all, hr_operating_units

Report Categories

Enginatics

Related Reports

AP Supplier Upload, AP Suppliers, AP Invoices and Lines, AP Accounted Invoice Aging, CE Bank Branches, AR Customer Upload, AP Invoice Payments, AP Invoice Payments 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 Suppliers Revenue Summary 16-Nov-2020 043149.xlsx
Blitz Report™ XML Import	AP_Suppliers_Revenue_Summary.xml
SQL Source	AP Suppliers Revenue Summary.sql
Full Report on Enginatics	www.enginatics.com/reports/ap-suppliers-revenue-summary/

Case Study & Technical Analysis: AP Suppliers Revenue Summary

1. Executive Summary

Business Problem

Procurement and Finance teams often struggle to get a consolidated view of supplier spend across the organization. Standard Oracle reports focus on transactional details (Invoice Register) or open balances (Aging), but lack the analytical view needed for strategic decision-making. Key challenges include:

• Fragmented Data: Spend data is scattered across multiple Operating Units and years.
• Negotiation Leverage: Difficulty in quickly identifying total spend with a vendor to negotiate better terms.
• Vendor Rationalization: Inability to identify low-volume or dormant suppliers for cleanup.
• Trend Analysis: Hard to see if spend with a specific supplier is increasing or decreasing year-over-year.

Solution Overview

The AP Suppliers Revenue Summary is a high-level analytical report designed for Strategic Sourcing and Spend Analysis. It aggregates AP invoice data to provide a multi-year view of supplier revenue. By summarizing spend into fiscal year and rolling year buckets (e.g., Current FY, Last 3 Years), it allows users to instantly identify their top suppliers and analyze spending trends.

Key Benefits

• Strategic Insight: Provides a clear picture of “Who are our top suppliers?” and “How much did we spend with them last year?”.
• Trend Analysis: Side-by-side comparison of spend across multiple fiscal years (FY, FY-1, FY-2).
• Consolidation: Aggregates data across Operating Units (depending on parameters) for a global view of supplier relationships.
• Master Data Audit: Includes key vendor details like Tax IDs, Payment Terms, and masked IBANs alongside spend data.
• Efficiency: Replaces manual Excel consolidation of multiple “Invoice Register” exports.

2. Technical Analysis

Core Tables and Views

The report combines master data with transactional aggregates:

• AP_INVOICES_ALL: The source of truth for spend data (Invoice Amounts).
• AP_SUPPLIERS / AP_SUPPLIER_SITES_ALL: Vendor master data.
• GL_PERIODS / GL_LEDGERS: Used to determine Fiscal Year boundaries relative to the “As of Date”.
• IBY_EXT_BANK_ACCOUNTS: Fetches bank account details (IBAN) for the supplier.
• HR_OPERATING_UNITS: For multi-org context.

SQL Logic and Data Flow

The query uses advanced analytical functions to pivot time-based data into columns without complex subqueries.

• Common Table Expression (CTE): The with x as (...) block calculates the start and end dates of the fiscal year containing the :as_of_date. This dynamic date logic drives the column buckets.
• Analytical Sums: Uses SUM(CASE WHEN ...) with OVER (PARTITION BY ...) to calculate spend for specific time windows (e.g., amount_fy_&fy, amount_3_years) within a single pass of the data.
• Dense Rank: Uses keep (dense_rank last order by ...) to fetch the most relevant site details (Address, City) for the aggregated row, ensuring that even if a supplier has multiple sites, the report displays a representative location (usually the most recently created or updated).
• IBAN Aggregation: Subqueries for iban_prio1, iban_prio2, etc., fetch the top priority bank accounts for the supplier, providing a snapshot of payment details.

Integration Points

• General Ledger: Relies on the GL Calendar to define Fiscal Years.
• Payments (IBY): Fetches banking details.
• Payables: Aggregates invoice history.

3. Functional Capabilities

Reporting Dimensions

• Time Horizons:
  • Fiscal Years: Current FY, Previous FY, 2 Years Ago.
  • Rolling Periods: Last 12 Months, Last 24 Months, Last 36 Months, Last 10 Years.
• Grouping:
  • Summary Level: Can likely group by Supplier (Global) or Supplier Site (Local).
  • Geography: Analyze spend by Country or City.

Key Parameters

• As of Date: The reference date for calculating “Current Year” and backward-looking buckets.
• Operating Unit: Filter for specific business units or leave blank for all (if access allows).
• Supplier Filters: Focus on specific vendors or categories.

4. Implementation Considerations

Performance

• Data Volume: Aggregating AP_INVOICES_ALL over 10 years can be resource-intensive. The query is optimized with analytical functions, but running it for “All Suppliers” during peak hours should be avoided.
• Currency: The report groups by invoice_currency_code. If a supplier is paid in multiple currencies, they will appear in multiple rows (one per currency). Users may need to convert to a functional currency in Excel for a total global spend view.

Best Practices

• Sourcing Reviews: Use this report prior to contract renewals to understand total business volume.
• Supplier Rationalization: Filter for suppliers with amount_total < X to identify candidates for deactivation.
• Fraud Detection: Look for suppliers with significant spend increases (amount_fy » amount_fy_1) without a known business reason.

---

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