Tag Archives: ODLN

SAP Business One Queries

Open Sales Order Lines SQL query

Leave a comment

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.