This simple script fetch all invoices (header & lines) from Fortnox and store them in an in-memory table (*@inmemory) for use. It also features an adaptive column structure in case the API evolves.
-----------------------------------------------
--- Script to fetch all invoices from fortnox
--- Datacontainer : @fnx
---------------------------------------------------
--
-- 1) Create the inmemory tables for
-- invoices_headers filled with data from fortnox
-- empty invoices_lines just to have the columns + datatypes
DECLARE
docnb varchar2;
BEGIN
create or replace table invoicesHeaders@inmemorystorage as
select * from Invoices.Invoices@fnx;
select DocumentNumber into docnb from invoicesHeaders@inmemorystorage LIMIT 1 ;
create or replace table invoicesLines@inmemorystorage as
select * from Invoices.InvoiceLinesByDocumentNumber@fnx (docnb) LIMIT 0;
END
--
-- 2) Build explicit column list for invoices_lines from DataDictionary
--
create or replace table invlines_cols@inmemorystorage as
select
name,
database_data_type,
provider_name
from DataDictionary.Invantive.SYSTEMTABLECOLUMNS@DataDictionary
where Provider_Data_Container_Alias = 'fnx' AND table_name = 'InvoiceLinesByDocumentNumber'
select * from invlines_cols@inmemorystorage
-- 3) Build ONE explicit column list (unquoted), and the source list with il.
DECLARE
v_cols CLOB := ''; -- => Col1, Col2, ...
v_cols_il CLOB := ''; -- => il.Col1, il.Col2, ...
v_first NUMBER := 1;
BEGIN
FOR r IN (select name from invlines_cols@inmemorystorage)
LOOP
IF v_first = 1 THEN
v_cols := r.name;
v_cols_il := 'il.'||r.name;
v_first := 0;
ELSE
v_cols := v_cols || ', '|| r.name;
v_cols_il := v_cols_il || ', il.'|| r.name;
END IF;
END LOOP;
create or replace table invlines_lists@inmemorystorage as
select v_cols as cols_csv, v_cols_il as cols_il_csv
from dual@datadictionary;
END;
-- 4) Insert invoice lines using the same explicit column list on both sides
-- Print progress every 50 invoices
DECLARE
v_cols CLOB;
v_cols_il CLOB;
v_sql CLOB;
v_cnt integer := 0;
v_total integer;
t0 datetime := sysdate;
tb datetime := sysdate;
secs decimal;
BEGIN
SELECT cols_csv, cols_il_csv
INTO v_cols, v_cols_il
FROM invlines_lists@inmemorystorage;
SELECT COUNT(*) INTO v_total FROM invoicesHeaders@inmemorystorage;
dbms_output.put_line('Invoice lines fetch started at '||
to_char(t0,'YYYY-MM-DD HH24:MI:SS')||
' | invoices='||to_char(v_total));
FOR r IN (SELECT DocumentNumber FROM invoicesHeaders@inmemorystorage ORDER BY DocumentNumber) LOOP
-- Looks like: insert into invoicesLines (col1, col2, ...) select il.col1, il.col2, ...
v_sql :=
'insert into invoicesLines@inmemorystorage ('|| v_cols ||')
select '|| v_cols_il ||'
from Invoices.InvoiceLinesByDocumentNumber@fnx(' || r.DocumentNumber || ') il';
execute immediate v_sql;
--dbms_output.put_line(v_sql);
--dbms_output.put_line(v_cnt);
v_cnt := v_cnt + 1;
IF MOD(v_cnt, 50) = 0 OR v_cnt = v_total THEN
secs := round((sysdate - tb)*60*60*24,3);
dbms_output.put_line('Processed '|| v_cnt || '/' || v_total ||
' invoices; last 50 in '|| round(secs,2) ||'s');
tb := sysdate;
END IF;
END LOOP;
secs := (sysdate - t0) * 86400;
dbms_output.put_line('Completed '||to_char(v_cnt)||'/'||to_char(v_total)||
' invoices; total time '||to_char(round(secs,2))||'s');
exception
when others
then
dbms_output.put_line('error: ' || sqlerrm);
dbms_output.put_line(chr(13) || 'counter stopped at :' || v_cnt);
dbms_output.put_line(chr(13) || v_sql);
end;
-- see the restults
select * except rowid$ from invoicesHeaders@inmemorystorage
--
select * except rowid$ from invoicesLines@inmemorystorage