Items with no price in a price list
Sales items that have a zero price in the chosen price list.
SELECT T0.ItemCode, T0.ItemName, T2.ListName, T1.Price FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OPLN T2 ON T1.PriceList = T2.ListNum WHERE T0.SellItem = 'Y' AND T0.frozenFor = 'N' AND T1.PriceList = [%0] AND ISNULL(T1.Price, 0) = 0 ORDER BY T0.ItemCode
SELECT T0."ItemCode", T0."ItemName", T2."ListName", T1."Price" FROM OITM T0 INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OPLN T2 ON T1."PriceList" = T2."ListNum" WHERE T0."SellItem" = 'Y' AND T0."frozenFor" = 'N' AND T1."PriceList" = [%0] AND IFNULL(T1."Price", 0) = 0 ORDER BY T0."ItemCode"
More inventory queries
Three steps, no signup
Copy the right version
Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.
Open Query Generator
In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.
Save and share
Click Save, pick a category and give the query a name. Your team can run it from Query Manager.
Questions about this tool
How do I run the items with no price in a price list query in SAP Business One?
Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.
Which tables does this query use?
It reads OITM, ITM1, OPLN from the standard SAP Business One schema. No user-defined fields are needed.
What should I enter when the selection window opens?
This query uses parameters: [%0] = price list number. SAP B1 shows a selection window for each one when you run it.
Can this be turned into a dashboard or alert?
Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.
Built for SAP Business One teams
Need this inside SAP Business One?
We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.
