QP Price Lists – Oracle EBS SQL Report

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

Overview

Master data report showing the setup of price lists including item prices, calculation methods, terms and dates.

Report Parameters

Price List, Item, Category Set, Category, Valid on Date

Oracle EBS Tables Used

qp_list_headers_vl, qp_list_lines_v, mtl_system_items_vl, qp_currency_lists_vl, fnd_currencies_vl, ra_terms_tl, hz_cust_accounts, hz_parties, hr_all_organization_units_vl, qp_price_formulas_vl

Report Categories

Enginatics

Related Reports

QP Customer Pricing Engine Request, ONT Orders and Lines, PPF_WP3_OM_DETAILS, AR Customer Upload, CAC Intercompany SO Price List vs. Item Cost Comparison, ONT Order Upload

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	QP Price Lists 26-Jul-2018 134124.xlsx
Blitz Report™ XML Import	QP_Price_Lists.xml
Full SQL on Enginatics	www.enginatics.com/reports/qp-price-lists/

Case Study & Technical Analysis: QP Price Lists Report

Executive Summary

The QP Price Lists report is a fundamental master data and configuration audit tool for Oracle Advanced Pricing (QP). It provides a comprehensive listing of all defined price lists, including their associated items, base prices, calculation methods, terms, and date effectiveness. This report is indispensable for pricing analysts, sales managers, and system configurators to understand complex pricing structures, audit price list setups, troubleshoot pricing discrepancies, and ensure accurate and consistent application of item selling prices across the organization.

Business Challenge

Effective pricing is crucial for revenue generation and profitability. However, managing and auditing price lists in a complex Oracle EBS environment can present significant challenges:

• Opaque Pricing Structures: Organizations often have numerous price lists with thousands of items, different unit of measures, and various effective dates. Understanding which price applies to which item at a given time is challenging with standard Oracle forms.
• Ensuring Pricing Consistency: Inconsistent pricing across different price lists, or errors in item-price assignments, can lead to incorrect invoicing, customer dissatisfaction, and margin erosion.
• Date-Effective Pricing Management: Price changes are frequent, requiring meticulous management of effective start and end dates. Without a clear report, it’s difficult to ensure that only the correct price is active for a given period.
• Auditing and Compliance: Regularly auditing price list setups for accuracy, compliance with pricing policies, and preventing unauthorized price changes is a critical but often manual and error-prone process.
• Strategic Price Analysis: Analyzing base prices by item, category, or price list is essential for strategic pricing decisions, but often requires consolidated data that is not readily available.

The Solution

This report offers a powerful, configurable, and auditable solution for analyzing and managing pricing price lists, transforming how organizations oversee their sales pricing strategies.

• Comprehensive Price List Overview: It presents a detailed list of price lists, their associated items, unit prices, calculation methods, and date effectiveness. This provides a clear, at-a-glance understanding of the entire pricing setup.
• Date-Effective Pricing Visibility: The crucial Valid on Date parameter allows users to view price lists and item prices as they were defined and active on any specific date, which is vital for historical analysis, troubleshooting, and audit purposes.
• Simplified Configuration Audit: Pricing analysts and auditors can use this report to quickly review and verify price list setups, ensuring they are correctly configured and align with pricing policies and market strategies.
• Enhanced Troubleshooting: When a pricing discrepancy occurs on a sales order, this report provides immediate insight into the active price lists and item prices for a given date, helping to quickly pinpoint and resolve pricing issues.

Technical Architecture (High Level)

The report queries core Oracle Advanced Pricing and Inventory tables to extract and present detailed price list configurations.

• Primary Tables/Views Involved:
  • qp_list_headers_vl (the central view for pricing list headers).
  • qp_list_lines_v (for individual lines within a price list, defining the item and its base price).
  • mtl_system_items_vl (for item master details).
  • fnd_currencies_vl and qp_currency_lists_vl (for currency details and currency-specific price list setups).
  • ra_terms_tl (for payment terms associated with price lists).
  • qp_price_formulas_vl (for details of any pricing formulas used).
• Logical Relationships: The report starts by retrieving price list headers from qp_list_headers_vl. It then joins to qp_list_lines_v to get the item-specific prices. The Valid on Date parameter is applied to these date-effective tables to ensure that only prices active on the specified date are retrieved. Further joins to item master, currency, and other tables enrich the output with descriptive information.

Parameters & Filtering

The report offers flexible parameters for targeted analysis of price list configurations:

• Price List Identification: Price List allows users to focus on a specific price list for detailed review.
• Item & Category Filters: Item, Category Set, and Category enable granular targeting of price lines for specific products or product groupings.
• Date-Effective Filter: Valid on Date is the most critical parameter, allowing users to define the point in time for which they want to retrieve active pricing data. This ensures historical accuracy.

Performance & Optimization

As a master data report dealing with date-effective tables, it is optimized for efficient historical and current data retrieval.

• Date-Effective Table Querying: The core pricing tables (qp_list_lines_v) are designed and indexed by Oracle to efficiently retrieve the correct price version for a given Valid on Date, ensuring fast performance even when analyzing historical pricing.
• Indexed Joins: The queries leverage standard Oracle indexes on list_header_id, list_line_id, item_id, and category_id for efficient joins between the various pricing and item master tables.
• Parameter-Driven Scope: Filtering by Price List, Item, and Category significantly reduces the data volume processed, preventing the report from attempting to process excessively broad datasets when detailed analysis is not required.

FAQ

1. What is the difference between a ‘Price List’ and a ‘Modifier’? A ‘Price List’ typically defines the base selling price for an item or service. A ‘Modifier’ (such as a discount, surcharge, or promotion) then adjusts that base price based on specific conditions. This report focuses on the base prices from price lists, while the QP Modifier Details report provides insights into the adjustments.

2. How does the ‘Valid on Date’ parameter ensure accurate pricing information? Prices can change over time. The Valid on Date parameter ensures that the report retrieves the exact price that was active for an item on that specific date, correctly reflecting any date-effective price changes. This is critical for accurate historical analysis, invoicing, and revenue recognition.

3. Can this report show all prices for a specific item across all price lists? Yes, by leaving the Price List parameter blank and filtering by Item, the report can be configured to display all active prices for that specific item across every price list it appears on (as of the Valid on Date), providing a comprehensive view of an item’s pricing strategy.

---

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