Tag Archives: sap business one

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.

SAP Business One Queries

Batch information SQL query

Leave a comment

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.

Coding

SAP:Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘SAPbobsCOM.ICompany’

SAP Business One DI API

I kept getting the above error when attempting to generate documents on a thread other than the main thread. Here’s the error message in full:

Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘SAPbobsCOM.ICompany’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{3BA8DAED-5B33-4CE4-A4B8-B4308D86E524}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
 
[quads id=1]

Turns out that running the following command on the client machine did the trick:

C:\SAP\SAP Business One DI API\DI API 88>regsvr32 SAPbobsCOM88.dll

What was weird here was that previous calls to the COM object on the main thread work fine. Any subsequent calls on a different thread threw the exception.

Incidentally, I have also spotted this error when using some 3rd party (and SAP – Certified) add-ons. In all cases, the above DLL registration command resolved the issue. Your mileage may vary, however.

For more information on the SAP Business One DI API, check out the following link over at the SBO website:

http://scn.sap.com/docs/DOC-7722