PO Approved Supplier List – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Approved supplier list and attribute details such as source documents, supplier scheduling, planning constraints and inventory attributes.

Report Parameters

Operating Unit, Owning Organization Code, Using Organization Code, Item, Category, Supplier, Show Documents, Show Authorizations, Show Supplier Capacity, Show Supplier Tolerance

Oracle EBS Tables Used

po_approved_supplier_list, mtl_parameters, hr_all_organization_units_vl, mtl_system_items_vl, mtl_categories_kfv, ap_suppliers, ap_supplier_sites_all, mo_glob_org_access_tmp, dual, mtl_manufacturers, po_asl_statuses, po_asl_documents_v, po_headers_all, po_asl_attributes, per_people_x, chv_bucket_patterns, chv_authorizations, po_supplier_item_capacity, po_supplier_item_tolerance, org_access_view

Report Categories

Enginatics

Related Reports

PO Approved Supplier List Upload, CAC Calculate Average Item Costs, INV Intercompany Invoice Reconciliation, PO Requisition Template Upload, CAC Open Purchase Orders, CAC AP Accrual IR ISO Match Analysis, PO Cancelled Purchase Orders, PO Invoice Price Variance, INV Items

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	PO Approved Supplier List 18-Jan-2018 223638.xlsx
Blitz Report™ XML Import	PO_Approved_Supplier_List.xml
Full SQL on Enginatics	www.enginatics.com/reports/po-approved-supplier-list/

Case Study & Technical Analysis: PO Approved Supplier List Report

Executive Summary

The PO Approved Supplier List (ASL) report is a crucial procurement master data tool within Oracle Purchasing. It provides a comprehensive listing of items and services that can be sourced from specific suppliers, along with critical details such as sourcing rules, supplier capacity, planning constraints, and quality attributes. This report is indispensable for procurement managers, buyers, and supply chain planners to enforce sourcing policies, optimize supplier selection, streamline purchasing processes, and ensure supply chain continuity and quality.

Business Challenge

Managing an Approved Supplier List is fundamental for controlling procurement costs, ensuring quality, and mitigating supply chain risks. However, maintaining and auditing this complex data in Oracle EBS can present several challenges:

• Lack of Centralized ASL View: While Oracle EBS provides ASL functionality, getting a consolidated report that details all approved suppliers for specific items or categories, along with their associated attributes, can be difficult to extract and analyze.
• Enforcing Sourcing Policies: Without clear reporting, it’s challenging to ensure that buyers are consistently purchasing from approved suppliers and adhering to established sourcing rules.
• Supplier Performance Management: Tracking supplier-specific details like capacity, lead times, and quality tolerances is crucial for effective supplier performance management, but often requires disparate data sources.
• Audit and Compliance: Demonstrating that sourcing decisions are made based on approved suppliers and established policies is a key requirement for internal and external audits, necessitating clear documentation of the ASL setup.

The Solution

This report offers a consolidated, detailed, and flexible solution for managing the Approved Supplier List, transforming how procurement teams enforce sourcing strategies.

• Comprehensive ASL Overview: It presents a detailed list of approved suppliers for specified items or categories, including associated source documents (e.g., Blanket Purchase Agreements), authorizations, and planning constraints.
• Configurable Detail Levels: Parameters such as Show Documents, Show Authorizations, Show Supplier Capacity, and Show Supplier Tolerance allow users to dynamically include deeper levels of detail, tailoring the report to specific analytical needs.
• Streamlined Supplier Selection: Buyers can use this report to quickly identify approved suppliers for specific items, along with key performance attributes, facilitating efficient and compliant sourcing decisions.
• Enhanced Supply Chain Planning: Supply chain planners can leverage supplier capacity and tolerance information to make more accurate planning decisions, ensuring adequate supply and mitigating risks.

Technical Architecture (High Level)

The report queries core Oracle Purchasing and Inventory tables related to the Approved Supplier List and its various attributes.

• Primary Tables Involved:
  • po_approved_supplier_list (the central table for ASL entries, linking items/categories to suppliers).
  • ap_suppliers and ap_supplier_sites_all (for supplier and supplier site details).
  • mtl_system_items_vl and mtl_categories_kfv (for item and category details).
  • po_asl_documents_v (a view that links ASL entries to underlying source documents like Blanket Purchase Agreements).
  • po_asl_attributes, po_supplier_item_capacity, po_supplier_item_tolerance (tables storing additional supplier-item specific attributes).
• Logical Relationships: The report builds its foundation from po_approved_supplier_list to identify approved supplier relationships. It then performs various conditional joins to other tables to retrieve detailed information on source documents, supplier authorizations, and item-specific capacity and tolerance, depending on the user’s parameter selections.

Parameters & Filtering

The report offers an extensive set of parameters for precise filtering and detailed data inclusion:

• Organizational Context: Operating Unit, Owning Organization Code, and Using Organization Code allow for filtering by specific business units or inventory organizations.
• Sourcing Criteria: Item, Category, and Supplier enable precise targeting of ASL entries.
• Detail Inclusion Flags: Crucial parameters like Show Documents, Show Authorizations, Show Supplier Capacity, and Show Supplier Tolerance dynamically control the level of detailed information presented in the report, allowing users to focus on specific aspects of ASL data.

Performance & Optimization

As a master data report that can include numerous attributes, it is optimized through efficient filtering and conditional data loading.

• Parameter-Driven Efficiency: The extensive filtering parameters are critical for performance, allowing users to limit the data retrieved to only the relevant ASL entries, preventing the report from attempting to process an excessively broad dataset.
• Conditional Joins: The “Show” parameters enable conditional joining to related detail tables (ee.g., po_asl_documents_v). This means that complex joins are only executed when the user explicitly requests that level of detail, preventing unnecessary database load.
• Indexed Lookups: Queries leverage standard Oracle indexes on item_id, category_id, vendor_id, and org_id for efficient data retrieval.

FAQ

1. What is the difference between an ‘Owning Organization’ and a ‘Using Organization’ in the ASL? The ‘Owning Organization’ is the inventory organization that created and manages the ASL entry. The ‘Using Organization’ is an inventory organization that is permitted to use that ASL entry. This allows a central team to manage the ASL, which can then be utilized across multiple operating inventory organizations.

2. How does the ASL relate to Blanket Purchase Agreements (BPAs)? BPAs are often established with approved suppliers for frequently purchased items. An ASL entry can reference a BPA as a “source document,” meaning that when a buyer creates a standard PO for that item from that supplier, the system can automatically suggest or use the terms from the referenced BPA.

3. Can this report help identify potential single points of failure in the supply chain? Yes, by filtering for a critical Item and reviewing all Approved Suppliers for it, along with their Capacity (if available), procurement managers can quickly assess if there are enough alternative approved sources to mitigate risks associated with a single supplier.

---

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

© 2026 Enginatics