All posts by rogue lj

The Return

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.

Open Sales Order Lines SQL query

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:

  • Document Number (DocNum)
  • Document Date (DocDate)
  • Customer Reference (NumAtCard)
  • Customer Code (CardCode)
  • Customer Name (CardName)
  • Item (ItemCode)
  • Warehouse (WhsCode)
  • Quantity
  • Open Quantity (OpenQty)
  • Delivery Date (ShipDate)
  • Sales Person (SlpName)

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.

Batch information SQL query

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.