Managing projects with web services

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;
/