The XML import on Exact Online can be used to load accounts (customers, suppliers) with their contacts, addresses and bank accounts. The Exact Online XML import module automatically merges accounts based upon an identical value for the Code attribute. However, it fails to detect identical addresses and contacts with no or slight changes.
The contacts and addresses in Exact Online can grow considerably when the same XML import is run multiple times. It is not uncommon for example to have dozens of identical addresses for one account. Addresses can be removed by hand, but this a tedious task, especially when it concerns thousands of customers.
A contact and/or address can be used for instance on an invoice. When not used, a delete is possible. The following code automates the process to delete contacts and addresses not used elsewhere.
Remember to create an Exact Online backup of the company before you use the code and delete the unused contacts and addresses.
--
-- Select one or more Exact Online companies ("divisions").
--
use all@eol
begin
--
-- Delete unused contacts.
--
for r
in
( select *
from contactsincremental@eol
--
-- For high volumes, you may want to spread the deletes
-- across a number of days to remain within rate limits.
-- Use a filter like the following to limit the deletes to a partition
-- of your contact data.
--
-- Each delete effort counts as one API call. Typically you have
-- a daily allowance of 5.000 or 50.000 per division.
--
where to_char(id) >= '0'
and to_char(id) <= '1'
--
-- Sort on GUID, reduces chance of hot spot on
-- one division in terms of rate limits.
--
order
by id
)
loop
begin
delete exactonlinerest..contacts@eol
where id = r.id
and division = r.division
;
exception
when others
then
--
-- When the contact is still in use for an invoice
-- or alike, an error will be raised. The contact
-- should not be deleted. Ignore and continue.
--
null;
end;
end loop;
--
-- Remove superfluous addresses.
--
for r
in
( select *
from addressesincremental@eol
where to_char(id) >= '0'
and to_char(id) <= '1'
order
by id
)
loop
begin
delete exactonlinerest..contacts@eol
where id = r.id
and division = r.division
;
exception
when others
then
null;
end;
end loop;
end;