SAP connector

[BabuJayaraman]

We have been using the SAP connector for fetching the GL data and few other information like units and install base. The connectors were working fine until Oct 2023. We noticed duplicate and/or missing rows. The row counts would match. It would only have this issue when the data is in multiple packages. We had package size of 10000.

As a workaround we increased the package size 100000. But one of the integration has >500000 rows. When we tried to increase the package size to match the rows, the integration would fail with internal memory size error.

Looking into get data function, I would like to know if there is a way to add order. Below you will find the get data code. The where clause is - r3Table.AddCriteria("FISCYEAR = " & "'" & fiscalYear & "'"). Is there a way to add order by?

#Region "GetData"
'Read source data from the R3 table
Private Sub GetSourceDataSAP(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal r3Conn As R3Connection, ByVal r3TableName As String)
	Try
		r3Conn.LogDir = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.ApplicationOutgoing, Nothing)
		r3Conn.Logging = True
		r3Conn.Open()
		Dim r3Table As New ReadTable(r3Conn)
		Me.w_r3Table = r3Table
		Dim whereclause As New Text.StringBuilder
		'Set the data package size
		r3Table.PackageSize = Me.m_PackageSize
		r3Table.RaiseIncomingPackageEvent = True
		'Specify the table to open
		r3Table.TableName = r3TableName
		r3table.SetCustomFunctionName("Z_XTRACT_IS_TABLE")
		Dim fields As List(Of String) = GetFieldList(si, globals, api, r3conn, r3TableName)
		For Each fieldname As String In fields
			r3Table.Addfield(fieldname)
		Next

		'filters for year 
		' Dim fiscalYear As String = "2021"
		Dim fiscalYear As String = TimeDimHelper.GetYearFromId(api.WorkflowUnitPk.TimeKey).ToString
		'where clause
		r3Table.AddCriteria("FISCYEAR = " & "'" & fiscalYear & "'")
		'Query the table
		r3Table.Run()
	Catch ex As Exception
		Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	End Try
End Sub

[JackLacava]

This is really a question for Theobald Software, makers of the ERPConnect library that the connector leverages. The documentation I've found on their site doesn't indicate anything related to Order By; in fact, they seem to have removed .AddCriteria too in the newest releases (6.1+), indicating to use .WhereClause instead. I don't know which version of ERPConnect you have installed.

It also looks like your code is using a SAP function Z_XTRACT_IS_TABLE, of which several versions can be found online - some supporting ORDERBY as parameter and some that don't.

I don't know enough about SAP to tell you more than that; unless someone else comes in, you might have to open a case with Support.

Btw, next time you'd like to post some code here (which you're welcome to!), please have a look at this post, describing how to do it in the best way.

[NicolasArgente]

Hi All!
Adding an order to your data fetch from SAP in VB.NET using ERPConnect's ReadTable isn't straightforward. ReadTable is great for grabbing data but not for sorting it like you'd do in a SQL query. It is designed primarily for data extraction and not query manipulation!
- Your solution could be to use a custom SAP Function Module  - requires some development from your SAP team. 
- Or you could Handle less data in each call by adding a filter.
  Can you add a filter on the month and add a loop in your BR? 

1. You add a filter on month, like Jan to June.
   
2. You run this call and it imports Jan to June data (so your volume gets divided by 2 in theory)
3. You write those values in a data table
4. You do a second call for data from July to December
5. You add those value to the data table...
   
   Obviously you could reduce the volume if you loop with entities, or even doing month by month...

Hope it helps,

Nic

[franciscoamores]

Hi,

I think the readtable class has the property OrderBy that you can assign. The problem is that it works only if the Z_Xtract custom function has that option enabled. If I remember well there were new versions of the custom function with the option enabled.

so R3table.orderby = “your order by”

you can give a try.

Anyway, I’d try to find out why that issue is happening .

[BabuJayaraman]

I could not get orderby to work.

Anyone with SAP integration greater than 500K record with the connector integration that is working? 

[MattG]

Additional thoughts on breaking up the query as mentioned already by Nic: your GL likely has numeric logic to it like 1 series are assets, 2 series accounts are liabilities, and so on.  After your existing r3table.AddCriteria statement on line 26 you can add another AddCriteria to limit this query to only certain accounts like the following:

r3table.AddCriteria("AND RACCT LIKE '00001%'")

Then fill an additional r3table2 with additional accounts with a different account list:

r3table2.AddCriteria("AND RACCT LIKE '00002%'")

Then once you have all of your r3tables filled (however many you need), convert them to datatables and either merge them for further operations if needed or simply present them all to the parser:

api.Parser.ProcessDataTable(si, dt1, True, api.ProcessInfo)

api.Parser.ProcessDataTable(si, dt2, True, api.ProcessInfo)