Here’s a SQL query that returns batch information for item codes that have a stock level greater than zero. This is the first in a series of posts highlighting some useful SQL queries for SAP Business One. I usually find myself writing out the same queries time & time again, so I thought I’d share them with the world. Some will be simple, some will be complex, hopefully all will be useful!
To keep things simple, wherever possible, I will not be using any User Defined Fields that I have created as part of my day-to-day work. However, sometimes they can be handy so if I include one, I’ll explain the reasoning why.
SELECT T0.[ItemCode], T0.[DistNumber], T0.[LotNumber], T0.[Notes], T1.[WhsCode], T1.[Quantity], T1.[CommitQty], T2.[Location], T0.[Status], T3.[SWW] FROM [dbo].[OBTN] T0 LEFT OUTER JOIN [dbo].[OBTQ] T1 ON T0.[ItemCode] = T1.[ItemCode] AND T0.[SysNumber] = T1.[SysNumber] INNER JOIN [dbo].[OBTW] T2 ON T0.[ItemCode] = T2.[ItemCode] AND T0.[SysNumber] = T2.[SysNumber] AND T1.[WhsCode] = T2.[WhsCode] INNER JOIN [dbo].[OITM] T3 ON T0.[ItemCode] = T3.[ItemCode] WHERE T1.[Quantity] > 0
- Item Code
- Batch Number
- Batch Attribute 2
- Warehouse Code
- Committed Quantity
- Item Additional Identifier
By a strange quirk of fate I had cause to use this query moment after I had written the first draft of this post!
You can find this query, and more over at my GitHub page: https://github.com/roguelj/SAP-Business-One-SQL-Queries
There are more SAP Business One queries on my blog, and you can find them here directly.