Get list of blocked Exact Online divisions

Question

We have a few thousand companies in Exact Online from which a certain percentage runs their own accounting and has their own license. However, there is a daily changing group of companies that are behind with their payments to Exact and therefore their companies are blocked.

For all companies we run Invantive Data Replicator to replicate all Exact Online companies into a SQL Server datawarehouse for analytical reporting and continuous monitoring.

In the SystemDivisions table, the state of such a blocked company remains at 1 (Active). It does not change to 2 (Archive) or 0 (upcoming). Nor is there any enddate set in the past.

However, when the XML or REST APIs are used through a query from Invantive SQL or directly from Python on such a blocked company there are lot of fuzzy error messages.

Currently we have to open each company which had an error during replication individually each day and check whether a block by Exact is causing the error and for what reason.

It seems that there is no way to retrieve the list of blocked companies.

Is there an alternative?

score 1 · Accepted Answer · answered Apr 30 '18 at 08:39

Although it is not supported and disadviced, you can access a limited number of screens in Exact Online using native requests. It is rumoured that this is not possible for all screens.

However, you are lucky. The blocking status of a company can be requested using the following queries:

insert into NativePlatformScalarRequests(url, orig_system_group)
select /*+ ods(false) */ 'https://start.exactonline.nl/docs/SysAccessBlocked.aspx?_Division_=' || code
,      'BLOCK-DIV-CHECK-' || code
from   systemdivisions

create or replace table currentlyblockeddivisions@inmemorystorage
as
select blockingstatus
,      divisioncode
from   ( select regexp_replace(result, '.*<table class="WizardSectionHeader" style="width:100%;"><tr><th colspan="2">([^<]*)</th>.*', '$1', 1, 0, 'n') blockingstatus
         ,      replace(orig_system_group, 'BLOCK-DIV-CHECK-', '') divisioncode
         from   NativePlatformScalarRequests
         where  orig_system_group like 'BLOCK-DIV-CHECK-%'
       )
where  blockingstatus not like '%: Onbekend%'

Please note that the hyperlink with '.nl' needs to be replaced when you run on a different country. The same holds for searching on the Dutch term 'Onbekend' ('Unknown' in english).

This query runs several thousand of HTTP request, each requesting the screen with the blocking status of a company. However, when the company is not blocked, the screen reports back a reason of 'Unknown'.

These companies with 'Unknown' reason are probably not blocked. The rest is.

Get list of blocked Exact Online divisions

1 Answers1