My Top SQL Queries for SAP Business One

I started out as a relative novice to SQL a few months ago. Now I use SQL queries in Business One almost daily. I wanted to share with you some of my most frequently used queries and why I like them. Just copy and paste these in to your own Query Generator

If you like SQL want to learn more we’d love to see you at the next Business One User Group at SAP UK: UK and Ireland SAP User Group – Developing, Benefiting and Serving the SAP User Community

We are on SAP 9.0 PL07

Open Invoices Not Emailed:

This query shows all AP Invoices added to SAP that are Open but have not been sent. It’s easy to forget to send invoices and this report has helped us be more efficient. Please note we are using Boyum Print and Deliver module:

SELECT t0.DocEntry, t0.CardCode, t0.CardName, t0.DocDueDate as ‘Due Date’, t0.DocCur, t0.DocTotal

FROM OINV t0

WHERE t0.DocStatus = ‘O’ and (t0.Printed = ‘N’ and (SELECT count(*)

FROM [dbo].[@BOY_85_DEL_LOG]  s0

WHERE s0.[U_BOY_id]  = t0.DocEntry and  s0.[U_BOY_OBJECTID]  = ’13’ and  s0.[U_BOY_ACTION]  = 4) = 0)

Partial Stock Take

We needed a way to list items in stock AND items not in stock for stock taking so we can run an inventory report between locations. The Bin Locations Content report in SAP only shows items in stock:

select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as ‘In Stock’, t2.BinCode, t2.SL1Code, t2.SL2Code from OITW T0

inner join OITM t1 on t0.ItemCode = t1.ItemCode

inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs

left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode

where t2.BinCode between [%0] and [%1]

order by t2.SL1Code, t2.SL2Code, t0.ItemCode

Items with No Default Bin Location

We want to report on all items in SAP not assigned a Default Bin Location. This has helped our Warehouse Manager to ensure ALL stock has a Default Bin:

SELECT T0.[ItemCode], T0.[WhsCode], T0.[DftBinAbs] FROM OITW T0 WHERE T0.[WhsCode]  = ‘main’ and  T0.[DftBinAbs]  is null

Purchase Invoice Analysis

The SAP Purchase Analysis report is in System Currency, mine is GBP. No use to me when I buy in EUR. This report shows all AR Invoices for a set period for a particular Business Partner in the currency on the Invoice (FC):

SELECT T0.[DocEntry], T0.[CardCode], T0.[DocDate], T0.[DocTotalFC]

FROM OPCH T0

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] =[%2]

If you want to run the same report but on Purchase Orders, simply substitute OPCH for OPOR

Top 10 Customers (Thanks to Zal Parchem for this)

SELECT TOP 10 T0.CardCode, MAX(T0.Cardname) as Customer,  SUM(T0.doctotal) as “Amount(LC)”

FROM dbo.OINV T0

WHERE t0.docdate BETWEEN [%0] AND [%1]

GROUP BY T0.CardCode

Order by SUM(T0.doctotal) DESC

Sales History by Item Group

Yes I know this exists in Sales Analysis reports but I prefer my layout:

SELECT T0.[DocEntry], T0.[DocDate],T0.[CardName], T2.[ItmsGrpCod], T2.[ItemCode], T2.[ItemName], T1.[Quantity]

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T2.[ItmsGrpCod] = [%0] AND T0.[DocDate] BETWEEN [%1] AND [%2]

Sales Orders with no SLP Code

Some of our Sales People forget to enter their name when adding a Sales Order. This handy report helps me identify these quickly and easily

SELECT T1.[DocNum], T1.[CardName], T1.[DocDate], T0.[SlpCode]

FROM OSLP T0  INNER JOIN ORDR T1 ON T0.SlpCode = T1.SlpCode

WHERE T0.[SlpCode] = ‘-1’

ORDER BY T0.[SlpCode]

If anyone has any suggestions on how to improve these, or you would like to share some of your own SQL Queries please feel free. It’s good to share!