Beispiel zur Erstellung von Beziehungen in Exact Online

Fragen (nl)
1

Het volgende script laadt massaal relaties in Exact Online, in dit geval vanuit een tekst met meerdere relaties vanuit een tekst in CSV-formaat. Het gebruikt synchronize (zie Exact Online synchroniseren met Visma.net Financialss):

set use-http-disk-cache@eol false

set use-http-memory-cache@eol false

create or replace table accounts_soll@inmemorystorage
as
select *
from   csvtable
       ( passing '123|Legato Inc#124|Philips#125|Oracle#126|Data Kings'
         row     delimiter  '#' 
         column  delimiter  '|'
         columns code varchar2 position next
         ,       name varchar2 position next
       )

create or replace table ExactOnlineAccountsIst@inMemoryStorage
as
select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       act.Id
,      act.Code
,      act.Name
,      act.VATNumber VATNumberOriginal
,      replace(act.VATNumber, ' ', '') VATNumberNormalized
from   AccountsIncremental@eol act

--
-- Synchroniseer de relaties van CSV naar Exact Online.
--
synchronize accounts_soll@inmemorystorage
to ExactOnlineAccountsIst@inMemoryStorage
with        insert
            exit after 1000 rows /* Avoid too many loads when things go wrong. */
            or
            update all
            except
            AddressLine1
            , AddressLine2
            , City
            , Country
            , Postcode
            , VATNumberOriginal
            , CreatedMin
            , rowid$
identified by code
batchsize 1
continue on first 100 errors
triggers
instead of insert
declare
  l_account_name    varchar2;
  l_account_created datetime;
  l_label           varchar2;
begin
  l_label := 'code ' || :new.code || ' - ' || :new.name;
  dbms_output.put_line('instead of insert: ' || l_label);
  insert into ExactOnlineREST..Accounts@eol
  ( Code
  , Name
  )
  values
  ( :new.Code
  , :new.Name
  );
end
--
instead of update
declare
  l_label varchar2;
begin
  l_label := 'Code ' || :new.code || ' - ' || :new.name;
  dbms_output.put_line('instead of update Accounts ' || l_label || '.');
  update ExactOnlineREST..Accounts@eol
  set    Name                     = :new.Name
  where  Id       = :old.Id
  ;
exception
  when others
  then
    dbms_output.put_line('Error on update Accounts on ' || l_label || ' due to ' || sqlerrm);
    raise;
end
2

Dank voor delen!