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

Columns returned:

• Item Code
• Batch Number
• Batch Attribute 2
• Notes
• Warehouse Code
• Quantity
• Committed Quantity
• Location
• Status
• 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.