An analysis was made of the raw API-data received (as described in Collect Native Platform Call Data).
The third query receives data spread as answer of a few API-calls. Each answer has a specific number of rows, as indicated by the Visma.net specifications.
The error occurs since for the batch 1100012 since that batch occurs in two answers:
select *
from jsontable
( '[*]'
passing file 'apidata.json'
columns batchNumber varchar2 path 'batchNumber'
, ledger varchar2 path 'ledger'
, postPeriod varchar2 path 'postPeriod'
) j
as shown in this picture:
There are 10 transaction lines for batch 1100012, but the first three lines are returned in the first answer, and the other seven lines in the next answer.
select *
from jsontable
( '[*].journalTransactionLines[*]'
passing file 'c:\temp\formatted.json'
columns lineNumber varchar2 path 'lineNumber'
, accountNumber varchar2 path 'accountNumber'
--
, batchNumber varchar2 path '::ancestor.batchNumber'
, ledger varchar2 path '::ancestor.ledger'
, postPeriod varchar2 path '::ancestor.postPeriod'
) j
where batchNumber = 1100012
as shown in this picture:
The strange thing is that this problem is not occurring for any other user of the Visma.net-connector in the last six months.
The Invantive-code logic expects when specifying a number of rows to be retrieved on an API-call, that the number of rows is the number of rows on the top-level (so batches here). It is known that the Visma.net-API has had several issues with row counts over the last 4 years when paging was added.
When looking at the documentation for this table:
there is a text included from the OpenAPI-specification by Visma which signals this problem for specifically this table:
Get a range of Journal Transactions - ScreenId=GL301000. On this particular endpoint, pagesize and totalcount denotes number of journaltransaction lines. When using pagination, the transactions for one specific batch can be split into several responses. Please use a page size lower or equal to the allowed max page size which is returned under metadata. If pagesize is greater than the max page size, it will be limited to max page size.
This text is automatically included by Invantive SQL into the documentation from Visma’s documentation and the API works as documented by Visma. It is not really practical for retrieving transactions, but that is something Invantive SQL must handle.
For the moment, please try to resolve the problem by switching to the transaction lines table:
As a structural solution, we will change the logic specifically for this table. There was already deviating logic for JournalTransactionLinesByPeriodOrDateV2 and JournalTransactionsByPeriodOrDateV2 in terms of determination whether all API-answers had been fetched. This will now be extended as follows:
- eat duplicate rows as well as possible for
JournalTransactionsByPeriodOrDateV2 instead of raising an error for tables which are documented to return duplicate rows.
- for all other tables, raise an error when two duplicate rows are detected (original behaivour)
Please note that this problem as far as known only involves the two tables listed and is a (documented) peculiarity of the Visma.net APIs which we did not oversee the possible ramifications which caused specifically for your environment this error.