iStock-678860564.jpg

 

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.

 

Enjoy!!

 

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

 

Formatted Seach

 

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.

 

 

 

 

Other terms

 

 

Orange arrow, query, missing, drill, browse, golden arrow, link buttons, hide, foreign keys, SQL, appears,