This article explains how to use Invantive Cloud applications to synchronize customers across multiple Exact Online companies.
Instructions
The article is based upon another article on customer synchronization using Invantive SQL in an on-premise product like Invantive Query Tool or Invantive Data Hub.
Invantive Cloud however does not support Invantive Script statements like ‘local define NAME VALUE’. And Invantive Cloud adds application modules to the game.
First register an Exact Online database with one or multiple Exact Online subscriptions using https://cloud.invantive.com/setup/databases/new/exactonline.
Then create a new application named “My App“ on https://cloud.invantive.com/development/applications/new:
Add an executable module using Invantive PSQL to the application with the following code:
declare
l_division pls_integer := 123;
l_output varchar2;
begin
create or replace table targetcompanies@inmemorystorage
as
--
-- Select some companies.
--
select code
from systemdivisions@eol
where
code != l_division
;
use select l_division, 'eol';
--
-- Create cartesian product of target companies and
-- Exact Online accounts to load.
--
create or replace table accounts_soll@inmemorystorage
as
select tgt.code
division
, act.code
, act.name
, act.chamberofcommerce
from exactonlinerest..accountsbulk@eol act
join targetcompanies@inmemorystorage tgt
where act.division = l_division
limit 50 /* Remove when tested. */
;
--
-- Focus on all selected target companies.
--
use select code, 'eol' from TARGETCOMPANIES@InMemoryStorage
;
begin
synchronize exactonlinerest..accounts@eol
from accounts_soll@inmemorystorage
with insert
or
update
identified
by division
, code
continue on first 500 errors
;
l_output := '<html><body>Synchronized customers ' || sqlrowcount || '.</body></html>';
exception
when others
then
l_output := '<html><body>Synchronizing customers failed with ' || sqlerrm || '.</body></html>';
end;
--
cloud_http.set_body(l_output);
end;
Go to the application again and choose “Execute“.
Choose the database you have just created.
Click on the synchronization button:
After some time, the customers have been synchronized.