We (cursor) wrote a small piece of code to retrieve all vouchers + vouchers rows. It works in Invantive’s cloud and has better performance than the view
select *
from Fortnox.Views.Vouchers
were timeout can often be reached.
create or replace table vouchers@inmemorystorage as
select *
from Fortnox.Vouchers.Vouchers
( 27 -- filter on financial year 2025
);
-- Let's see the actual structure of the data
select *
from Fortnox.Vouchers.VoucherLinesByVoucherSeriesAndVoucherNumber
( 'A' -- example voucher series
, '1' -- example voucher number
, 27 -- financial year 2025
);
-- Now we can create the voucherslines table with the same structure
create or replace table voucherslines@inmemorystorage
( rowid$ integer
, Account varchar(10)
, CostCenter varchar(10)
, Credit decimal(18,2)
, Debit decimal(18,2)
, Description varchar(200)
, Project varchar(10)
, Quantity decimal(18,2)
, Removed boolean
, VoucherNumber varchar(10)
, VoucherSeries varchar(10)
, Year integer
);
-- Now we can loop through the vouchers and insert their lines
begin
for r in (select VoucherSeries, VoucherNumber from vouchers@inmemorystorage)
loop
insert into voucherslines@inmemorystorage
select cast(null as integer) as rowid$,
cast(t.Account as varchar(10)) as Account,
cast(t.CostCenter as varchar(10)) as CostCenter,
cast(t.Credit as decimal(18,2)) as Credit,
cast(t.Debit as decimal(18,2)) as Debit,
cast(t.Description as varchar(200)) as Description,
cast(t.Project as varchar(10)) as Project,
cast(t.Quantity as decimal(18,2)) as Quantity,
cast(t.Removed as boolean) as Removed,
cast(t.VoucherNumber as varchar(10)) as VoucherNumber,
cast(t.VoucherSeries as varchar(10)) as VoucherSeries,
cast(t.Year as integer) as Year
from Fortnox.Vouchers.VoucherLinesByVoucherSeriesAndVoucherNumber
( r.VoucherSeries
, r.VoucherNumber
, 27 -- financial year 2025
) t;
end loop;
end;
-- Join vouchers and voucherslines
select v.*, -- all columns from vouchers
vl.* -- all columns from voucherslines
from vouchers@inmemorystorage v
join voucherslines@inmemorystorage vl
on v.VoucherSeries = vl.VoucherSeries
and v.VoucherNumber = vl.VoucherNumber
order by v.VoucherSeries, v.VoucherNumber;