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.
