Retrieve SEPA files Loket

Questions
,
#1

Trying another endpoint (TotalSepaFileByPayrollRunId) I get a similiar response. Is this also not implemented yet?

Response:

itgensgr045
The table ‘LoketNlRest.PayrollRunDownloads.TotalSepaFileByPayrollRunId’ must produce data in at least one of the supported MIME-types: application/json, application/pdf, text/plain, text/csv.
Use another table or contact Invantive regarding extension on supported MIME-types.

What is the status on: LoketNlRest.PayrollRunDownloadsSepaFileBySepaFileId. Would that also be needing development first?

Errors when fetching CSV from Loket table PayrollRunBalanceSheetByPayrollRunIdAsCsv
#2

The message code itgensgr045 signals that the Loket API specification provides the data on TotalSepaFileByPayrollRunId in a format not yet supported. Currently the following formats are supported:

  • application/json
  • application/pdf
  • text/plain
  • text/csv

However, the message does not yet clearly display what the possible formats are. A change has been released that displays the formats that can be produced with itgensgr045. It is probably text/xml with the content wrapping another API-specification, but better to be sure.

Please reproduce the error on February 15, 2022 or later. Include the new extended error message as an answer.

Please also include the SQL-statement used to generate the message.

Can you illustrate what the exact error is with the other table PayrollRunDownloadsSepaFileBySepaFileId? Please include the SQL-statement too.

#3

Tried again today:

SQL:

select *
from   LoketNlRest.PayrollRunDownloads.TotalSepaFileByPayrollRunId('{validPayrollRunId}'
, 'Salary', 'BtchBookgTrue', '2019-08-24')
limit  1000

Response:

The table ‘LoketNlRest.PayrollRunDownloads.TotalSepaFileByPayrollRunId’ must produce data in at least one of the supported MIME-types: application/json, application/pdf, text/plain, text/csv.
Use another table or contact Invantive regarding extension on supported MIME-types to include at least one of application/xml.

#4

Thank you for the additional information.

We recommend to give delegation for one week to our support team to analyze the issue.

Is there also a SQL-statement that relates to PayrollRunDownloadsSepaFileBySepaFileId?

#5

Sure, how can I delegate to Invantive Support Team?

Lets put the PayrollRunDownloadsSepaFileBySepaFileId on hold for now. We have no SepaFileId to try with.

#6

To delegate to a person please use the email address of that Invantive Support Team-member as described in Access via delegation on Invantive Cloud for consultants. You can delegate to me as first line.

#7

Okay, that should be done now.

#8

A test was done using:

select *
from   PayrollAdministrationPayrollRuns prn
join   LoketNlRest.PayrollRunDownloads.TotalSepaFileByPayrollRunId(prn.prn_id, 'Salary', 'BtchBookgTrue', '2019-08-24')
limit  5

But an error was returned due to missing scopes assigned to the Invantive client ID:

itgenlnr005
Insufficient rights for this request, reason: “Client does not have access to activity.”.
The remote server returned an error: (403) Forbidden.

The following query indicates the actual Loket API call made:

select url
,      error_message
from   sessionios@datadictionary
where  successful = false

itgenlnr005
itgenlnr0051232×252 22.9 KB

A request #214337 has been registered with Loket requesting that the Invantive client IDs get access to this API.

#9

Is there any update on this issue?

#10

The current status is unknown. Please contact your contact person within the Organisation using Loket regarding their meeting with Loket.

#11

I got the news this might be solved.

I tried the same query as above and this time I got a new error:

itgenjdn003:
Can’t find the column ‘XML’ in the current list of 1 columns.
Possible alternative column names: DOCUMENT, DOCUMENT.

#12

The issue can not be reproduced yet due to missing scopes on the Invantive client ID. A ticket was created with Loket (#216341) to acquire additional scopes. Once completed, this ticket will be updated.

#13

The error itgenjdn003 was reproduced using:

select *
from   PayrollAdministrationPayrollRuns prn
join   LoketNlRest.PayrollRunDownloads.TotalSepaFileByPayrollRunId(prn.prn_id, 'Salary', 'BtchBookgTrue', to_char(prn.prn_payrollPeriod_periodStartDate, 'YYYY-MM-DD'))
where  prn.prn_payrollperiod_year=2014
limit  5

The expected column is DOCUMENT as checked by:

select name
from   systemtablecolumns@datadictionary
where  table_name='TotalSepaFileByPayrollRunId'

The actual XML-document is returned as XML column name with contents such as:

<?xml version="1.0" encoding="utf-8"?>
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
  <CstmrCdtTrfInitn>
    <GrpHdr>
      <MsgId>46-99999-99</MsgId>
      <CreDtTm>2022-03-23T12:49:24</CreDtTm>
      <NbOfTxs>2</NbOfTxs>
      <CtrlSum>1.1</CtrlSum>
      <InitgPty>
        <Nm>ACME BV</Nm>
      </InitgPty>
    </GrpHdr>
    <PmtInf>
...
    </PmtInf>
  </CstmrCdtTrfInitn>
</Document>

But the metadata of Loket specifies that the payload should be named DOCUMENT in this specific case. An alternation has been implemented for this.

Currently, the metadata also specifies a BLOB and not a CLOB. This is subject to further research. However, research is currently awaiting additional scopes being assigned to the client IDs.

#14

The maintenance of the Loket scopes is quite a burden due to the work needed and the number of individual scopes to be requested one-by-one. Therefore a policy change was introduced; changes to the standard Invantive client scopes can only be handled against a surcharge. Please contact sales@invantive.com for pricing. As an alternative, you might want to reach out to Loket and acquire a client ID.

#15

There is a metadata issue on the Loket specification for XML-documents. Currently an error will occur on binary XML (itgenrst004). Once the metadata is corrected, the query will work. There are no plans to implement a workaround. A manual workaround would be to use NativeScalarRequests to construct the request in code and workaround the format issue.