Script to fetch all invoices from Fortnox

Questions
,
1

This simple script fetch all invoices (header & lines) from Fortnox and store them @inmemory for use. It also feature 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

--select * from invoicesHeaders@inmemorystorage

-- 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;

--select * from invlines_lists@inmemorystorage

-- 4) Insert invoice lines using the same explicit column list on both sides
--    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