Ok, after a couple of years experimenting with a different website (that I also left to rot), I’ve decided to return to the blog format with most of the old content still intact. I’ve tidied up some posts that were no longer relevant and updated where necessary.
The graffiti art photo posts have moved to their own subdomain, which can be found here:
https://found-art.roguelj.co.uk
The music content posts will also be moved to their own sub-domain in due course.
SQL Query for SAP Business One to return a list of open sales order lines at row detail.
SELECT
T0.[DocNum],
T0.[DocDate],
T0.[NumAtCard],
T0.[CardCode],
T0.[CardName],
T1.[ItemCode],
T1.[WhsCode],
T1.[Quantity],
T1.[OpenQty],
T1.[ShipDate],
T2.[SlpName]
FROM
[dbo].[ORDR] T0
INNER JOIN [dbo].[RDR1] T1 ON T0.[DocEntry] = T1.[DocEntry]
LEFT JOIN [dbo].[OSLP] T2 ON T1.[SlpCode] = T2.[SlpCode]
WHERE
T1.[LineStatus] !='C'
The handy thing about the Orders, Delivery, and Invoices tables (and many other tables besides), is that the schema’s are the same. So you can re-purpose the query by swapping out the table names – for instance, the following will give you Open Sales Deliveries:
SELECT T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[WhsCode], T1.[Quantity], T1.[OpenQty], T1.[ShipDate], T2.[SlpName] FROM [dbo].[ODLN] T0 INNER JOIN [dbo].[DLN1] T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT JOIN [dbo].[OSLP] T2 ON T1.[SlpCode] = T2.[SlpCode] WHERE T1.[LineStatus] !='C'
Columns returned:
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. For those of you that are integrating with SAP Business One more deeply, you can find related articles about the DI API here.
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:
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.