The Commercial Register of the Chamber of Commerce
In the Commercial Register there is much interesting information on enterprises. This information is also interesting because the enterprises are required to keep this data correct.
In a previous contribution I have described how you can quickly add and/or update massive missing relation data from the Commercial Register with webservices.nl from Oracle PL/SQL. In this contribution I will show you how you can use an additional company rule to enter in an new relation just by using the Chamber of Commerce number. Of course including data from the Commercial Register.
Additional company rules
An additional company rule is a concept with Invantive Estate. “Out of the factory” the software has a number of possibilities and controls. Sometimes you might want to add possibilities and/or controls specifically for your own organization. The automatic numbering of new projects is an often occurring example of an extra possibility, specific to your own organization. But you can also add your own controls. An example is that as an emergency procedure only the head of the financial administration can provide purchase orders on projects.
An additional company rule is determined with a company object. Company objects take on the shape of a special type of database view. See the image below of an additional company rule that was entered using the web interface.
An additional company rule consists of a piece of code in PL/SQL. This PL/SQL code can use a number of extra variables that only have a function with additional company rules:
- :action: the SQL statement that the additional company rule will force to be executed; ‘INSERT’, ‘UPDATE’ or ‘DELETE’.
-
:action_moment:the moment on which the additional company rule is executed, before (‘B’) or after('A')the updating of the company object. -
:last:the last entered value of the company object (in the case of deletion the situation before deletion, otherwise always equal to :new). -
:old:the previous value of the company object. -
:new:the new value of the company object.
Oracle PL/SQL code for the retrieval of data
This additional company rule is executed every time an “Organization” changes a company object. The settings for this are listed in the image above.
An additional company rule is called upon twice: once before and once after the changing of the company object. In this example the address data is retrieved first before the entering of an organization (:action_moment=‘B’) at the OpenKvK. This is a free service.
The organization is subsequently added.
Then (:action_moment=‘A’) the remaining data like personnel size and contact are entered on the basis of Webservices.nl. This requires a credit balance.
--
-- Address information is fetched from www.openkvk.nl.
--
if :action_moment = 'B'
and :last.lvr_kvk_nummer is not null
and ( :last.lvr_code is null or :last.lvr_code = :last.lvr_kvk_nummer )
and :last.lvr_plaats is null
then
xxive_lvr.fetch_kvk
( :last.lvr_kvk_nummer
, :last.lvr_code
, :last.lvr_naam
, :last.lvr_adres_regel_1
, :last.lvr_postcode
, :last.lvr_plaats
, :last.lvr_website_url
);
:last.lvr_code := :last.lvr_kvk_nummer; -- Avoid removal of leading 0s.
:last.tal_code := 'nl'; -- Dutch companies all speak Dutch.
end if;
--
-- Webservices.nl provides additional information
-- such as contactpersons and SBI activity codes.
--
if :action_moment = 'A'
and :action = 'INSERT'
and :last.lvr_kvk_nummer is not null
and :last.lvr_code = :last.lvr_kvk_nummer
then
itgen_session.set_session_info
( 'toad.sql'
, 'install'
, 'system'
, 'maintain organizations from chamber of commerce'
, coalesce(sys_context('userenv', 'ip_address'), '?')
, sys_context('userenv', 'host')
, 'n/a'
, 'webservices.nl sample ' || to_char(sysdate, 'YYYYMMDDHH24MISS')
);
--
-- Lookup additional information from webservices.nl
--
xxive_lookup_ws_nl_kvk(:last.lvr_kvk_nummer);
end if;
This code makes use of the Oracle PL/SQL procedure xxive_lookup_ws_nl_kvk, of which the source code is available.