AP Suppliers – 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) including supplier sites, contact and bank account information on vendor and site level

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No.

Duplicate Match restricts the output to suppliers sharing a match key with at least one other supplier and lists the shared keys. Names are compared in upper case without punctuation, spaces and legal form suffixes (Inc, Ltd, GmbH, SA and similar), the other keys without punctuation and spaces. Bank accounts are shown masked.

Spend Last 12 Months is the total of the supplier’s invoices dated in the last twelve months, excluding cancelled invoices, in each operating unit’s functional currency.

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, Supplier, Supplier Number, Supplier starts with, Creation Date From, Creation Date To, Supplier Site, Supplier Type, Country, Show Contacts, Show Bank Accounts, Active Suppliers Only, Active Sites Only, Active Contacts Only, Show Contact Responsibilities, Active Bank Accounts Only, DFF Display, Duplicate Match, Name Data Quality Issues Only

Oracle EBS Tables Used

per_all_people_f, fnd_territories_vl, ap_awt_groups, ap_invoices_all, zx_input_classifications_v, iby_ext_party_pmt_mthds, gl_code_combinations_kfv, hr_all_organization_units_vl, ap_suppliers, ap_supplier_sites_all, po_vendor_contacts, hz_relationships, hz_parties, hz_party_usg_assignments, ap_supplier_contacts, fnd_user, fnd_user_resp_groups_direct, fnd_responsibility_vl, fnd_application_vl, ap_terms_vl, iby_external_payees_all, iby_pmt_instr_uses_all, iby_ext_bank_accounts, ce_bank_branches_v, hz_party_sites, zx_party_tax_profile

Report Categories

Enginatics

Related Reports

AP Supplier Upload, AP Invoice Upload, AP Invoices and Lines, GL Account Distribution Analysis, INV Items, AP Accounted Invoice Aging, AP Invoice Payments 11g, AP Invoice Payments

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 10-Jul-2024 104442.xlsx
Blitz Report™ XML Import	AP_Suppliers.xml
SQL Source	AP Suppliers.sql
Full Report on Enginatics	www.enginatics.com/reports/ap-suppliers/

Case Study & Technical Analysis: AP Suppliers

1. Executive Summary

Business Problem

Supplier master data is the foundation of the Procure-to-Pay process. Inaccurate or duplicate supplier records can lead to duplicate payments, fraud risks, tax compliance issues (e.g., missing 1099 flags), and procurement inefficiencies. Organizations often struggle to maintain a clean vendor master due to:

• Decentralized Entry: Multiple departments creating vendors with inconsistent standards.
• Lack of Visibility: Difficulty in seeing the full picture of a supplier across multiple operating units and sites.
• Compliance Gaps: Missing Taxpayer IDs, VAT registration numbers, or Withholding Tax configurations.
• Risk Management: Inability to quickly identify active vendors with missing bank details or outdated payment terms.

Solution Overview

The AP Suppliers report provides a 360-degree view of the vendor master database. It consolidates data from multiple tables to present a unified view of Suppliers, Supplier Sites, Contacts, and Bank Accounts. This report is essential for Master Data Management (MDM) teams, Auditors, and AP Managers to validate, clean, and monitor the health of their supplier base.

Key Benefits

• Comprehensive Visibility: Drills down from the Supplier Header to Sites, Contacts, and Bank Accounts in a single view.
• Risk Mitigation: Highlights critical fields like Taxpayer IDs, Payment Terms, and Bank Account details for audit review.
• Compliance: Identifies suppliers missing 1099 flags, VAT codes, or Withholding Tax groups.
• Operational Efficiency: Helps identify and deactivate unused or duplicate supplier sites.
• Multi-Org Support: Shows which Operating Units have access to specific supplier sites.

2. Technical Analysis

Core Tables and Views

The report queries the Trading Community Architecture (TCA) and AP tables:

• AP_SUPPLIERS: Stores the header-level vendor information (Name, Tax ID, Parent Supplier).
• AP_SUPPLIER_SITES_ALL: Stores address and site-specific attributes (Payment Terms, GL Accounts, Purchasing Flags).
• HZ_PARTIES / HZ_PARTY_SITES: Underlying TCA tables for party information.
• IBY_EXT_BANK_ACCOUNTS: Stores external bank account details (via IBY payment/funds capture tables).
• PO_VENDOR_CONTACTS: Stores contact names, phones, and emails.
• AP_AWT_GROUPS: Links to Withholding Tax configurations.

SQL Logic and Data Flow

The SQL is structured to handle the complex relationships between Suppliers, Sites, and Banks.

• Outer Joins: Extensively used to ensure that a supplier is listed even if they don’t have a site, contact, or bank account defined (depending on the “Show” parameters).
• Dynamic Columns: The SQL uses lexical parameters (e.g., &supplier_bank_account, &contacts_columns) to conditionally include or exclude sensitive or voluminous data based on user selection.
• TCA Integration: Joins to HZ_PARTIES to fetch the “Trading Partner” name and other TCA-level attributes.
• Payment Method Logic: Uses listagg to concatenate multiple payment methods into a single comma-separated string for easier reading.
• Security: Bank account information is typically retrieved from the secure IBY tables, and access is often controlled by Oracle’s function security.

Integration Points

• TCA (Trading Community Architecture): The central repository for parties and locations.
• Payments (IBY): Centralized payment setup for banks and payment methods.
• General Ledger: Default account coding (Liability, Prepayment).
• Tax (E-Business Tax): Links to ZX_PARTY_TAX_PROFILE for tax registration details.

3. Functional Capabilities

Reporting Dimensions

• Supplier Profile: Analyze by Supplier Type (Standard, Employee, Tax Authority), Country, or Category.
• Site Analysis: Review active vs. inactive sites, Pay Sites vs. Purchasing Sites.
• Financial Controls: Audit Payment Terms, Credit Limits, and Hold Flags.
• Contact Management: Export contact lists for mass communication or cleanup.

Key Parameters

• Scope: Operating Unit, Supplier Name (starts with).
• Status Filters: Active Suppliers Only, Active Sites Only.
• Detail Level: Show Contacts (Yes/No), Show Bank Accounts (Yes/No).
• Date Ranges: Creation Date (to audit new vendors).

4. Implementation Considerations

Data Privacy

• Bank Accounts: This report can expose sensitive bank account numbers. Ensure that access to the report is restricted to authorized personnel (e.g., AP Managers, MDM Team).
• PII: Supplier contacts and Employee-type suppliers may contain Personally Identifiable Information.

Best Practices

• Regular Audits: Schedule this report monthly to review new suppliers created in the period.
• Inactive Cleanup: Use the “Last Update Date” and “Inactive Date” fields to identify and deactivate dormant suppliers.
• Duplicate Check: Export to Excel and use fuzzy matching on “Supplier Name” and “Address” to find potential duplicates.

---

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