SAP Business One Product Lookup Query

Key Concepts

ItemCode vs ItemName

ItemCode is the unique identifier — what users type to add items. ItemName is the description. Some companies use meaningful codes (WIDGET-001), others use sequential numbers. Always search both fields.

Active Items Filter

validFor = 'Y' filters to active items. Inactive items (validFor = 'N') are discontinued or archived. Always include this filter unless you need historical items.

Inventory vs Non-Inventory

InvntItem = 'Y' means the item tracks stock. Services and non-stock items have InvntItem = 'N' — they won't have meaningful OnHand values.

Price Fields on OITM

OITM has shortcut fields like AvgPrice (moving average cost) and LastPurPrc (last purchase price). For selling prices, join ITM1 — it has prices per price list.

Available Fields Reference

Find Product by Name

Search items using partial name match. We use LIKE with wildcards for flexible searching. Results include stock summary and cost info.

SELECT 
    ItemCode,
    ItemName,
    OnHand AS Stock,
    OnHand - IsCommited AS Available,
    AvgPrice AS Cost,
    LastPurPrc AS LastPurchasePrice
FROM OITM
WHERE validFor = 'Y'
  AND (ItemName LIKE '%widget%' OR ItemCode LIKE '%widget%')
ORDER BY ItemName

Find by Item Code

Exact match lookup with full details. Use this when you know the code. Includes purchase info and supplier catalog number.

SELECT 
    ItemCode,
    ItemName,
    FrgnName AS ForeignName,
    OnHand AS TotalStock,
    IsCommited AS Committed,
    OnHand - IsCommited AS Available,
    OnOrder,
    AvgPrice,
    LastPurPrc AS LastPurchasePrice,
    LastPurDat AS LastPurchaseDate,
    SuppCatNum AS SupplierCatalog
FROM OITM
WHERE ItemCode = 'A00001'

Item with All Price List Prices

Join ITM1 to get selling prices from all price lists. We join OPLN for the price list name. Filter Price > 0 to skip empty entries — not every item has a price in every list.

SELECT 
    T0.ItemCode,
    T0.ItemName,
    T1.PriceList,
    T2.ListName,
    T1.Price,
    T1.Currency
FROM OITM T0
INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OPLN T2 ON T1.PriceList = T2.ListNum
WHERE T0.ItemCode = 'A00001'
  AND T1.Price > 0
ORDER BY T1.PriceList

See also: Product Price Query for special prices, volume discounts, and period-based pricing.

Items by Group

List items in a specific category. Join OITB for the group name. ItmsGrpCod links to the item group.

SELECT 
    T0.ItemCode,
    T0.ItemName,
    T1.ItmsGrpNam AS GroupName,
    T0.OnHand AS Stock,
    T0.AvgPrice AS Cost
FROM OITM T0
INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
WHERE T0.validFor = 'Y'
  AND T1.ItmsGrpNam = 'Electronics'
ORDER BY T0.ItemName