SAP Business One Product Price Query
SQL queries for price lists, special customer prices, volume discounts, and period-based pricing.
These are sample queries you can run on Manager Boost. Use them as-is, or edit and customize on our platform. Go custom or use our pre-built queries — you're in control.
How SAP B1 Pricing Works
Price Lists (ITM1)
Every item can have different prices for each price list. ITM1 stores one row per item/price list combo. Price list 1 is typically the base price. Customers are assigned a default price list in OCRD.
Special Prices (OSPP/SPP1)
Override price list prices for specific customers. OSPP links the customer to the special price agreement. SPP1 contains the actual item prices. These take priority over the customer's default price list.
Volume Discounts (SPP2)
Quantity-based tier pricing. Buy 1-10 units at $100, 11-50 at $90, 50+ at $80. Stored in SPP2 linked to the special price in SPP1.
Period Pricing
Both ITM1 and SPP1 support date ranges via FromDate and ToDate. Use these for promotional pricing or seasonal adjustments.
Available Fields Reference
ITM1 (Price List Prices)
ItemCode PriceList Price Currency FromDate ToDate
SPP1 (Special Prices)
ItemCode CardCode Price Currency FromDate ToDate Discount
SPP2 (Volume Tiers)
ItemCode CardCode Amount SPP2Disc SPP2Price
Item Price List Prices
Get all prices for an item across all price lists. We join OPLN to get the price list name. Filter Price > 0 to skip empty entries.
SELECT
T0.ItemCode,
T1.ItemName,
T0.PriceList,
T2.ListName,
T0.Price,
T0.Currency
FROM ITM1 T0
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OPLN T2 ON T0.PriceList = T2.ListNum
WHERE T0.ItemCode = 'A00001'
AND T0.Price > 0
ORDER BY T0.PriceList| ItemCode | ItemName | PriceList | ListName | Price | Currency |
|---|---|---|---|---|---|
| A00001 | Widget Pro | 1 | Base Price | 89.00 | USD |
Period-Based Pricing
ITM1 supports date ranges for promotional or seasonal pricing. Check FromDate and ToDate against today to find active prices. NULL dates mean no restriction.
SELECT
T0.ItemCode,
T0.PriceList,
T1.ListName,
T0.Price,
T0.FromDate,
T0.ToDate,
CASE
WHEN (T0.FromDate IS NULL OR T0.FromDate <= GETDATE())
AND (T0.ToDate IS NULL OR T0.ToDate >= GETDATE())
THEN 'Active'
ELSE 'Inactive'
END AS Status
FROM ITM1 T0
INNER JOIN OPLN T1 ON T0.PriceList = T1.ListNum
WHERE T0.ItemCode = 'A00001'
AND T0.Price > 0
ORDER BY T0.PriceList, T0.FromDate| ItemCode | PriceList | ListName | Price | FromDate | ToDate | Status |
|---|---|---|---|---|---|---|
| A00001 | 1 | Base Price | 89.00 | 2024-01-01 | 2024-12-31 | Active |
Tip: To get only the currently active price, add AND (T0.FromDate IS NULL OR T0.FromDate <= GETDATE()) AND (T0.ToDate IS NULL OR T0.ToDate >= GETDATE()) to the WHERE clause.
Special Prices for a Customer
Special prices override the customer's default price list. These are negotiated prices for specific business partners. Join OSPP to SPP1 using CardCode.
SELECT
T1.ItemCode,
T2.ItemName,
T1.Price AS SpecialPrice,
T1.Discount AS DiscountPct,
T1.Currency,
T1.FromDate,
T1.ToDate
FROM OSPP T0
INNER JOIN SPP1 T1 ON T0.CardCode = T1.CardCode
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.CardCode = 'C10000'
AND (T1.FromDate IS NULL OR T1.FromDate <= GETDATE())
AND (T1.ToDate IS NULL OR T1.ToDate >= GETDATE())
ORDER BY T1.ItemCode| ItemCode | ItemName | SpecialPrice | DiscountPct | Currency | FromDate | ToDate |
|---|---|---|---|---|---|---|
| A00001 | Widget Pro | 75.00 | 15.7 | USD | 2024-01-01 | 2024-06-30 |
Volume Discount Tiers
SPP2 contains quantity breaks for tiered pricing. Amount is the minimum quantity for that tier. SPP2Price is the tier price, SPP2Disc is the discount percentage (use one or the other).
SELECT
T2.ItemCode,
T3.ItemName,
T2.Amount AS MinQty,
T2.SPP2Price AS TierPrice,
T2.SPP2Disc AS TierDiscountPct
FROM OSPP T0
INNER JOIN SPP1 T1 ON T0.CardCode = T1.CardCode
INNER JOIN SPP2 T2 ON T1.CardCode = T2.CardCode
AND T1.ItemCode = T2.ItemCode
INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode
WHERE T0.CardCode = 'C10000'
AND T2.ItemCode = 'A00001'
ORDER BY T2.Amount| ItemCode | ItemName | MinQty | TierPrice | TierDiscountPct |
|---|---|---|---|---|
| A00001 | Widget Pro | 1 | 89.00 | 0 |
| A00001 | Widget Pro | 10 | 80.00 | 10 |
Example output: MinQty 1 = $100, MinQty 10 = $90, MinQty 50 = $80. Customer buying 25 units pays $90 each.
Get Effective Price for Customer
Combine logic: check special price first, fall back to customer's price list. This query shows both prices so you can see which applies.
SELECT
T0.CardCode,
T0.CardName,
T0.ListNum AS CustomerPriceList,
@ItemCode AS ItemCode,
-- Price List Price
PL.Price AS PriceListPrice,
-- Special Price (if exists)
SP.Price AS SpecialPrice,
-- Effective Price
COALESCE(SP.Price, PL.Price) AS EffectivePrice,
CASE WHEN SP.Price IS NOT NULL THEN 'Special' ELSE 'PriceList' END AS PriceSource
FROM OCRD T0
LEFT JOIN ITM1 PL ON PL.ItemCode = @ItemCode
AND PL.PriceList = T0.ListNum
AND PL.Price > 0
LEFT JOIN SPP1 SP ON SP.CardCode = T0.CardCode
AND SP.ItemCode = @ItemCode
AND (SP.FromDate IS NULL OR SP.FromDate <= GETDATE())
AND (SP.ToDate IS NULL OR SP.ToDate >= GETDATE())
WHERE T0.CardCode = 'C10000'| CardCode | CardName | CustomerPriceList | ItemCode | PriceListPrice | SpecialPrice | EffectivePrice | PriceSource |
|---|---|---|---|---|---|---|---|
| C10045 | Acme Corp | 2 | A00001 | 85.00 | 75.00 | 75.00 | Special |
Note: Replace @ItemCode with the actual item code or use as a parameter in stored procedures.