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!
