Lately I have been getting quite fixated on SQL queries with SAP Business One, and looking around to see what queries are out there and where I can find them. (You've probably seen me blogging about them on our main blog. These posts were inspired by our most popular community post Useful SAP B1 queries which dates back to 2009.)
The good news is that there are a lot of queries that have been written and posted on various sites on the Interwebs.
But why go looking when you have ONE.Source and Richard Duffy as my personal information butler I hear you ask?
Good point - so here's a few useful ones I found from long time social collaborator, SAP Mentor and SAP Business One community member, Tim Guest.
You'll also find some additional tips about using the Golden Arrows for drill downs.
and kudos to Tim Guest (and Zal Parchem) for sharing their knowledge!
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]
In addition, Barbara Peterson asked on the Q and A Forum about getting the Golden Arrow to show up on her queries - I gave here an aswewr and you can view the thread here
But I thought you might find these tips from SAP handy as well when you aren't getting the love you need from the Golden Arrows in your queries
Your Golden Arrows may not show when one of these conditions occurs:
1. The field does not represent keys of a table.
2. Queries that contain groupings.
3. Queries that are based on Archive tables ("A" tables).
4. Queries that contain UNION clause.
5. Queries that contains temporary tables.
6. Queries for Formatted Search.
7. Queries with parameters declared.
8. Queries with certain restricted names in comment section
Also add the "FOR BROWSE" statement at the end of your query.
FOR BROWSE means that the result displays the orange arrow to navigate into the master record or document behind it.
Orange arrow, query, missing, drill, browse, golden arrow, link buttons, hide, foreign keys, SQL, appears,