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.