Integration of Invantive Estate with other systems
Invantive Estate is often integrated in complex IT environments. The primary registration of data often takes place in different systems. For a good project management it is in my opinion necessary to have a total overview on a project. For that reason we have made sure that Invantive Estate can exchange data easily with other systems.
Next to the internal IT systems there are often also external systems involved here. For example for the completion of relation data, calculation prices of real estate, a credit check or the requesting of location data from the car fleet. A part of these services are supplied by the Dutch company webservices.nl.
Webservices for the maintaining of projects
The use of web services is very accepted nowadays. Even with Invantive Estate you can use web services per batch or per transaction to manage your real estate projects. This can both be done through the running of ETL processes as well as from PL/SQL. In this example I will show you how you can clean up the already existing relation data with data from the Commercial Register like it is offered by webservices.nl.
Adding data from the commercial register to your relations
In this case the following data is edited or added:
- Name
- trade names;
- telephone number;
- mobile number;
- contact;
- number of employees;
- website;
- creation date;
- legal form;
- activities in the form of SBI codes.
The formal data such as legal status are useful in the closing of a deal, while the SBI codes are useful to inform your target groups with information relevant for them
In the next contribution I will show you how you can complete the file of a new relation with the same link by using an additional company line.
Adding commercial register information using web services from Oracle PL/SQL
In the code below all above mentioned data is edited or added for all (legal) relations. To use this example you need to have Invantive Estate 2012R1 version 3 or newer. The source code of relevant parts is available on request.
--
-- Sample implementation to access webservices.nl for additional Chamber of Commerce information
-- (Dutch: KvK / Kamer van Koophandel).
--
-- Prerequisites:
--
-- - KvK number has been registered for organizations (excluding location number/last four digits).
-- - Organization code equals KvK number.
-- - Sufficient credits at webservices.nl.
--
-- More documentation on the possibilities of webservices.nl can be found at:
--
-- - https://ws1.webservices.nl/soap?function=kvkGetDossier
-- - https://ws1.webservices.nl/documentation/files/service_kvk-class-php.html
--
begin
--
-- Log on to Invantive Estate.
--
bubs_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')
);
--
-- Log on to Invantive Producer.
--
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')
);
--
-- Process all organizations with a possible valid number for the Chamber of Commerce
-- (KvK handelsregister).
--
-- Restricts the list to:
--
-- - Suppliers.
-- - Customers.
-- - Projectentities.
-- - Organizations without an SBI classification.
--
for r_lvr
in
( select lvr.lvr_kvk_nummer
, listagg(lvr.lvr_naam, ', ') within group(order by lvr_kvk_nummer) lvr_namen
from bubs_leveranciers_v lvr
where 1=1
--
-- Meaningful KvK numbers.
--
and lvr.lvr_kvk_nummer is not null
and lvr.lvr_kvk_nummer not like '% %'
and lvr.lvr_kvk_nummer not like 'UNKNOWN'
and length(lvr.lvr_kvk_nummer) = 8
--
-- Limit to specific parts.
--
and ( lvr.lvr_opdrachtnemer_vlag = 'Y'
or
lvr.lvr_klant_vlag = 'Y'
or
lvr.lvr_project_entiteit_vlag = 'Y'
or
not exists
( select 1
from bubs_lvr_kle_v oke
where oke.lvr_id = lvr.lvr_id
and oke.kle_code like 'Branche.SBI 2008.%'
)
)
group
by lvr.lvr_kvk_nummer
order
by lvr.lvr_kvk_nummer
)
loop
dbms_output.put_line
( 'Process organization with KvK number '
|| r_lvr.lvr_kvk_nummer
|| ' which includes: '
|| r_lvr.lvr_namen
|| '.'
);
begin
xxive_lookup_ws_nl_kvk(r_lvr.lvr_kvk_nummer);
exception
when others
then
--
-- Ignore an error, just continue after printing it.
--
dbms_output.put_line(dbms_utility.format_error_stack || ' ' || dbms_utility.format_error_backtrace);
end;
end loop;
commit;
end;
/
This code makes use of the procedure xxive_lookup_ws_nl_kvk. The code of this is listed below.
Webservices.nl PL/SQL invocation
The following Oracle PL/SQL code ensures that for the organization with the supplied Chamber of Commerce number (p_lvr_kv_nummer) all data that is available so far is updated.
The updating can be done in multiple updates if multiple data has been changed compared to what is listed in Invantive Estate. This can still be optimized by combining all updates into one update.
create or replace procedure xxive_lookup_ws_nl_kvk
( p_lvr_kvk_nummer bubs_leveranciers_v.lvr_kvk_nummer%type
)
is
--
-- Update an organization with data fetched from webservices.nl.
--
l_result xmltype;
l_request varchar2(32767);
l_session_id varchar2(240);
l_loop boolean;
l_cnt pls_integer;
l_lvr_extra_handelsnaam varchar2(240);
l_rvm_code_raw varchar2(240);
l_rvm_code bubs_leveranciers_v.rvm_code%type;
l_lvr_naam bubs_leveranciers_v.lvr_naam%type;
l_lvr_handelsnamen varchar2(4000);
l_lvr_website_url bubs_leveranciers_v.lvr_website_url%type;
l_lvr_aantal_medewerkers bubs_leveranciers_v.lvr_aantal_medewerkers%type;
l_lvr_datum_opgericht bubs_leveranciers_v.lvr_datum_opgericht%type;
l_lvr_datum_opgericht_c varchar2(240);
l_lvr_werk_tel bubs_leveranciers_v.lvr_werk_tel%type;
l_lvr_mobiel_tel bubs_leveranciers_v.lvr_mobiel_tel%type;
l_lvr_kle_code_sbi_1 bubs_lvr_kle_v.kle_code%type;
l_lvr_kle_omschrijving_sbi_1 bubs_lvr_kle_v.kle_omschrijving%type;
l_lvr_kle_code_sbi_2 bubs_lvr_kle_v.kle_code%type;
l_lvr_kle_omschrijving_sbi_2 bubs_lvr_kle_v.kle_omschrijving%type;
l_lvr_kle_code_sbi_3 bubs_lvr_kle_v.kle_code%type;
l_lvr_kle_omschrijving_sbi_3 bubs_lvr_kle_v.kle_omschrijving%type;
l_gbr_voorletters bubs_gebruikers_v.gbr_voorletters%type;
l_gbr_tussenvoegsel bubs_gebruikers_v.gbr_tussenvoegsel%type;
l_gbr_achternaam bubs_gebruikers_v.gbr_achternaam%type;
l_gbr_geslacht_ind bubs_gebruikers_v.gbr_geslacht_ind%type;
--
g_webservice_url varchar2(240)
:= bubs#profiel_opties.get_value_vc
( 'bubs-interface-webservices-nl-url-soapnoheaders' );
g_webservice_url_auth varchar2(240)
:= bubs#profiel_opties.get_value_vc
( 'bubs-interface-webservices-nl-url-soap' );
g_username constant varchar2(60)
:= bubs#profiel_opties.get_value_vc
( 'bubs-interface-webservices-nl-gebruiker' );
g_password constant varchar2(60)
:= bubs#profiel_opties.get_value_vc
( 'bubs-interface-webservices-nl-wachtwoord' );
g_wallet_path constant varchar2(60)
:= bubs#profiel_opties.get_value_vc
( 'bubs-auth-ldap-beurs-locatie' );
g_wallet_password constant varchar2(30)
:= bubs#profiel_opties.get_value_vc
( 'bubs-auth-ldap-beurs-wachtwoord' );
--
begin
--
-- Login to webservices.nl.
--
l_request := ''
|| itgen_constants.g_crlf || ' ' || g_username || ''
|| itgen_constants.g_crlf || ' ' || g_password || ''
|| itgen_constants.g_crlf || ''
;
l_result := itgen_ws.soap_call_webservices_nl
( null
, l_request
, g_webservice_url_auth
, 'N'
, p_wallet_path => g_wallet_path
, p_wallet_password => g_wallet_password
);
l_session_id := itgen_ws.extractstring
( l_result
, '/ns1:loginResponse/reactid/text()'
, itgen_ws.g_ws_nl_payload_ns
);
--
-- Query the KvK handelsregister.
--
l_request := ''
|| itgen_constants.g_crlf || ' :parameter1'
|| itgen_constants.g_crlf || ' ' || p_lvr_kvk_nummer || ''
|| itgen_constants.g_crlf || ''
;
l_result := itgen_ws.soap_call_webservices_nl
( l_session_id
, l_request
, g_webservice_url
, 'Y'
, p_wallet_path => g_wallet_path
, p_wallet_password => g_wallet_password
);
--
-- Legal name.
--
-- With itgen_ws.extractstring you can extract a value from XML using
-- an xpath expression.
--
l_lvr_naam := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/legal_name/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
if l_lvr_naam is not null
then
update bubs_leveranciers_v
set lvr_naam = l_lvr_naam
where lvr_kvk_nummer = p_lvr_kvk_nummer
and ( lvr_naam is null or lvr_naam != l_lvr_naam )
;
if sql%rowcount != 0
then
dbms_output.put_line(p_lvr_kvk_nummer || ': name changed to ' || l_lvr_naam || '.');
end if;
end if;
--
-- Tradename.
--
l_lvr_handelsnamen := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/trade_name_full/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
--
-- Append array of tradenames.
--
l_cnt := 1;
l_loop := true;
while l_loop
loop
l_lvr_extra_handelsnaam := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/trade_names/item['
|| trim(to_char(l_cnt))
|| ']/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
if l_lvr_extra_handelsnaam is not null
then
l_cnt := l_cnt + 1;
if length(l_lvr_handelsnamen || ', ' || l_lvr_extra_handelsnaam) <= 240
then
l_lvr_handelsnamen := l_lvr_handelsnamen || ', ' || l_lvr_extra_handelsnaam;
end if;
else
l_loop := false;
end if;
end loop;
--
-- Some companies have an extremely long list of tradenames. So let's truncate it to 240 positions.
--
l_lvr_handelsnamen := substr(l_lvr_handelsnamen, 1, 240);
--
if l_lvr_handelsnamen is not null and l_lvr_handelsnamen != l_lvr_naam
then
update bubs_leveranciers_v
set lvr_handelsnamen = l_lvr_handelsnamen
where lvr_kvk_nummer = p_lvr_kvk_nummer
and ( lvr_handelsnamen is null or lvr_handelsnamen != l_lvr_handelsnamen )
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': tradenames changed to ' || l_lvr_handelsnamen || '.' );
end if;
end if;
--
-- Telephone number.
--
l_lvr_werk_tel := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/telephone_number/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
if l_lvr_werk_tel is not null
then
-- No update, phone number gets rewritten always in a different format.
update bubs_leveranciers_v
set lvr_werk_tel = l_lvr_werk_tel
where lvr_kvk_nummer = p_lvr_kvk_nummer
and lvr_werk_tel is null
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': telephone number changed to ' || l_lvr_werk_tel || '.' );
end if;
end if;
--
-- Mobile number.
--
l_lvr_mobiel_tel := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/mobile_number/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
if l_lvr_mobiel_tel is not null
then
-- No update, phone number gets rewritten always in a different format.
update bubs_leveranciers_v
set lvr_mobiel_tel = l_lvr_mobiel_tel
where lvr_kvk_nummer = p_lvr_kvk_nummer
and lvr_mobiel_tel is null
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': mobile number changed to ' || l_lvr_mobiel_tel || '.' );
end if;
end if;
--
-- Contactperson.
--
l_gbr_voorletters := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/contact_initials/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_gbr_tussenvoegsel := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/contact_prefix/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_gbr_achternaam := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/contact_surname/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_gbr_geslacht_ind := upper
( utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/contact_gender/text()'
, itgen_ws.g_ws_nl_payload_ns
)
)
);
if l_gbr_achternaam is not null
then
if l_gbr_geslacht_ind = 'F'
then
l_gbr_geslacht_ind := 'V';
elsif l_gbr_geslacht_ind is null
then
l_gbr_geslacht_ind := 'O'; -- Unknown.
end if;
--
-- Only insert a person when there is no contactperson known yet.
-- This avoids duplicates. When trigggered by an insert, this will
-- always create the contactperson.
--
insert into bubs_gebruikers_v
( lvr_code
, tal_code
, gbr_voorletters
, gbr_tussenvoegsel
, gbr_achternaam
, gbr_geslacht_ind
)
select lvr.lvr_code
, lvr.tal_code
, l_gbr_voorletters
, l_gbr_tussenvoegsel
, l_gbr_achternaam
, l_gbr_geslacht_ind
from bubs_leveranciers_v lvr
where lvr_kvk_nummer = p_lvr_kvk_nummer
and not exists
( select 1
from bubs_gebruikers gbr
where gbr.lvr_id = lvr.lvr_id
)
;
if sql%rowcount != 0
then
--
-- Register as primary contactperson.
--
update bubs_leveranciers_v
set gbr_cp_naam
=
( select gbr.naam
from bubs_gebruikers gbr
where gbr.id = bubs$gebruikers.get_last_identity
)
where lvr_kvk_nummer = p_lvr_kvk_nummer
;
dbms_output.put_line
( p_lvr_kvk_nummer || ': added primary contactperson ' || l_gbr_achternaam || '.' );
end if;
end if;
--
-- Number of employees.
--
l_lvr_aantal_medewerkers := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/personnel/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
if l_lvr_aantal_medewerkers is not null
then
update bubs_leveranciers_v
set lvr_aantal_medewerkers = l_lvr_aantal_medewerkers
where lvr_kvk_nummer = p_lvr_kvk_nummer
and ( lvr_aantal_medewerkers is null or lvr_aantal_medewerkers != l_lvr_aantal_medewerkers )
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': number of employees changed to ' || l_lvr_aantal_medewerkers || '.' );
end if;
end if;
--
-- Website.
--
l_lvr_website_url := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/domain_name/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
if l_lvr_website_url is not null
then
if l_lvr_website_url not like 'http://%'
then
l_lvr_website_url := 'http://' || l_lvr_website_url;
end if;
update bubs_leveranciers_v
set lvr_website_url = l_lvr_website_url
where lvr_kvk_nummer = p_lvr_kvk_nummer
and ( lvr_website_url is null or lvr_website_url != l_lvr_website_url )
;
if sql%rowcount != 0
then
dbms_output.put_line(p_lvr_kvk_nummer || ': website changed to ' || l_lvr_website_url || '.');
end if;
end if;
--
-- SBI codes
--
l_lvr_kle_code_sbi_1 := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/primary_sbi_code/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_lvr_kle_omschrijving_sbi_1 := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/primary_sbi_code_text/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_lvr_kle_code_sbi_2 := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/secondary_sbi_code1/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_lvr_kle_omschrijving_sbi_2 := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/secondary_sbi_code1_text/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_lvr_kle_code_sbi_3 := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/secondary_sbi_code2/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
l_lvr_kle_omschrijving_sbi_3 := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/secondary_sbi_code2_text/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
--
if l_lvr_kle_code_sbi_1 is not null
then
l_lvr_kle_code_sbi_1 := 'Branche.SBI 2008.' || l_lvr_kle_code_sbi_1;
--
-- Insert kle if not yet existing.
--
insert into bubs_classificaties_v
( kle_code
, kle_omschrijving
)
select l_lvr_kle_code_sbi_1
, l_lvr_kle_omschrijving_sbi_1
from dual
where not exists
( select 1
from bubs_classificaties kle
where kle.code = l_lvr_kle_code_sbi_1
)
;
insert into bubs_lvr_kle_v
( lvr_code
, kle_code
)
select lvr_code
, l_lvr_kle_code_sbi_1
from bubs_leveranciers_v lvr
where lvr.lvr_kvk_nummer = p_lvr_kvk_nummer
and not exists
( select 1
from bubs_lvr_kle_v lke
where lke.lvr_code = lvr.lvr_code
and lke.kle_code = l_lvr_kle_code_sbi_1
)
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': SBI 1 added: ' || l_lvr_kle_code_sbi_1 );
end if;
end if;
--
if l_lvr_kle_code_sbi_2 is not null
then
l_lvr_kle_code_sbi_2 := 'Branche.SBI 2008.' || l_lvr_kle_code_sbi_2;
--
-- Insert kle if not yet existing.
--
insert into bubs_classificaties_v
( kle_code
, kle_omschrijving
)
select l_lvr_kle_code_sbi_2
, l_lvr_kle_omschrijving_sbi_2
from dual
where not exists
( select 1
from bubs_classificaties kle
where kle.code = l_lvr_kle_code_sbi_2
)
;
insert into bubs_lvr_kle_v
( lvr_code
, kle_code
)
select lvr_code
, l_lvr_kle_code_sbi_2
from bubs_leveranciers_v lvr
where lvr.lvr_kvk_nummer = p_lvr_kvk_nummer
and not exists
( select 1
from bubs_lvr_kle_v lke
where lke.lvr_code = lvr.lvr_code
and lke.kle_code = l_lvr_kle_code_sbi_2
)
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': SBI 2 added: ' || l_lvr_kle_code_sbi_2 );
end if;
end if;
--
if l_lvr_kle_code_sbi_3 is not null
then
l_lvr_kle_code_sbi_3 := 'Branche.SBI 2008.' || l_lvr_kle_code_sbi_3;
--
-- Insert kle if not yet existing.
--
insert into bubs_classificaties_v
( kle_code
, kle_omschrijving
)
select l_lvr_kle_code_sbi_3
, l_lvr_kle_omschrijving_sbi_3
from dual
where not exists
( select 1
from bubs_classificaties kle
where kle.code = l_lvr_kle_code_sbi_3
)
;
insert into bubs_lvr_kle_v
( lvr_code
, kle_code
)
select lvr_code
, l_lvr_kle_code_sbi_3
from bubs_leveranciers_v lvr
where lvr.lvr_kvk_nummer = p_lvr_kvk_nummer
and not exists
( select 1
from bubs_lvr_kle_v lke
where lke.lvr_code = lvr.lvr_code
and lke.kle_code = l_lvr_kle_code_sbi_3
)
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': SBI 3 added: ' || l_lvr_kle_code_sbi_3 );
end if;
end if;
--
-- Establishment date.
--
l_lvr_datum_opgericht_c := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/establishment_date/Year/text()'
, itgen_ws.g_ws_nl_payload_ns
)
)
|| '-'
|| utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/establishment_date/Month/text()'
, itgen_ws.g_ws_nl_payload_ns
)
)
|| '-'
|| utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/establishment_date/Day/text()'
, itgen_ws.g_ws_nl_payload_ns
)
)
;
if l_lvr_datum_opgericht_c like '____-_%-_%'
then
l_lvr_datum_opgericht := to_date(l_lvr_datum_opgericht_c, 'YYYY-MM-DD');
update bubs_leveranciers_v
set lvr_datum_opgericht = l_lvr_datum_opgericht
where lvr_kvk_nummer = p_lvr_kvk_nummer
and ( lvr_datum_opgericht is null or lvr_datum_opgericht != l_lvr_datum_opgericht )
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': establishment date updated to ' || l_lvr_datum_opgericht || '.' );
end if;
end if;
--
-- Legal Form.
--
l_rvm_code_raw := utl_i18n.unescape_reference
( itgen_ws.extractstring
( l_result
, '/ns1:reactKvkGetDossierResponse/out/legal_form_code/text()'
, itgen_ws.g_ws_nl_payload_ns
)
);
if l_rvm_code_raw in ( 1, 2)
then
--
-- Eenmanszaak or eenmanszaak with more than one owner.
--
l_rvm_code := 'EZ';
elsif l_rvm_code_raw in (41, 42)
then
l_rvm_code := 'BV';
elsif l_rvm_code_raw in (74)
then
l_rvm_code := 'ST';
elsif l_rvm_code_raw in (7)
then
l_rvm_code := 'MAAT';
elsif l_rvm_code_raw in (88)
then
l_rvm_code := 'OVR';
elsif l_rvm_code_raw in (94, 96)
then
l_rvm_code := 'BOVNL';
elsif l_rvm_code_raw in (11)
then
l_rvm_code := 'VOF';
elsif l_rvm_code_raw in (71)
then
l_rvm_code := 'VER';
elsif l_rvm_code_raw in (51, 52, 53, 54)
then
l_rvm_code := 'NV';
else
raise_application_error(-20163, 'Unsupported legal form with raw code ' || l_rvm_code_raw || '.');
l_rvm_code := null;
end if;
--
if l_rvm_code is not null
then
update bubs_leveranciers_v
set rvm_code = l_rvm_code
where lvr_kvk_nummer = p_lvr_kvk_nummer
and ( rvm_code is null or rvm_code != l_rvm_code )
;
if sql%rowcount != 0
then
dbms_output.put_line
( p_lvr_kvk_nummer || ': legal form updated to ' || l_rvm_code || '.' );
end if;
end if;
--
-- Logout.
--
l_request := ''
|| itgen_constants.g_crlf || ' :parameter1'
|| itgen_constants.g_crlf || ''
;
l_result := itgen_ws.soap_call_webservices_nl
( l_session_id
, l_request
, g_webservice_url
, 'N'
, p_wallet_path => g_wallet_path
, p_wallet_password => g_wallet_password
);
end;
/