Missing Fortnox-table for VoucherRows of a voucher

We are starting to look into implementation for Fortnox with Invantive Cloud.

We found an incomplete endpoint, where we are missing the VoucherRows,
Array of objects (fortnox_Voucher_VoucherRow)

select *
from   Fortnox.Vouchers.VoucherByVoucherSeriesAndVoucherNumber
       ( :VoucherSeries -- identifies the voucher series
       , :VoucherNumber -- identifies the voucher number
       , :financialyear -- filter on financial year
       )

Source: Fortnox API

Search for “Voucher” > “Retrieve a specific voucher” > “Responses” > “200 a single voucher” > “Voucher” > “VoucherRows”.

Thank you for reporting this issue.

It seems that there is only one table discovered for this Swagger endpoint:

select *
from systemtables@datadictionary
where swagger_pre_mapping_table_name = 'get_57'

(1 row)

However, for the following entries no table is included VoucherRows.

This seems to be a generic issue, as for instance the rows are also missing below CustomInboundDocument:

select *
from   CustomInboundDocuments

for which no rows table with details on it exists:

select *
from systemtables@datadictionary
where swagger_pre_mapping_table_name = 'get_12'

(1 row)

An analyst will study the missing table and look at an approach to add these.

The issue has been identified and a fix will be included in the next 24.0 release.

This will add tables such as:

select *
from   Fortnox.Vouchers.VoucherRowsByVoucherSeriesAndVoucherNumber
       ( 'A'
       , 1164
       , 26
       )

The added tables are:

  • VoucherRowsByVoucherSeriesAndVoucherNumber
  • AccountOpeningQuantitiesByNumber

Possible use is:

select vse.code
,      fyr.FromDate
,      vle.VoucherNumber
,      vcr.Voucher_ReferenceNumber
,      vcr.Voucher_ReferenceType
,      vcr.Voucher_TransactionDate
,      vle.Account
,      vle.Credit
,      vle.Debit
,      vle.Description
from   VoucherSeries vse
join   Range@DataDictionary(1, 1164) rge
join   FinancialYearsByDate fyr
on     fyr.FromDate = add_months(trunc(sysdateutc, -2), -12)
join   VoucherByVoucherSeriesAndVoucherNumber(vse.code, rge.value, fyr.id) vcr
join   VoucherLinesByVoucherSeriesAndVoucherNumber(vse.code, rge.value, fyr.id) vle
where  vse.code like 'X%'

An alternative approach is to use Vouchers as in:

select vcr.VoucherSeries
,      vcr.VoucherNumber
,      vcr.Year
,      vle.Account
,      vle.Credit
,      vle.Debit
,      vle.Description
from   Vouchers vcr
join   VoucherLinesByVoucherSeriesAndVoucherNumber
       ( vcr.VoucherSeries
       , vcr.VoucherNumber
       , vcr.Year
       ) vle
where  vcr.year = 26
limit 100

or using:

select vcr.Series
,      vcr.Number
,      vcr.Year
,      vle.Account
,      vle.Credit
,      vle.Debit
,      vle.Description
from   SupplierInvoiceVouchers vcr
join   VoucherLinesByVoucherSeriesAndVoucherNumber(vcr.Series, vcr.Number, vcr.Year) vle
limit 100

Please note that the Fortnox APIs offer currently no extensive filtering capabilities as other problems. This hinders server-side filtering, making performance not especially good.

A view VoucherLines has been added to ease access.

Once more actual query needs become available, an inventarisation will be made what is filters can be added manually which might help in some scenarios.