Summary
By Wwft 2018 it is required to determine the UBO in several cases. This note provides running Invantive SQL to determine the UBO. Accountants and tax advisors are helped by a ready-to-run solution based upon Invantive Producer Runtime. Of course, these outcomes can also be achieved in Exact Online Accountancy Premium subscriptions.
Ultimate Beneficiary Owner
An Ultimate Beneficiary Owner (“UBO”) is a physical person that ultimately benefits from ownership or control of a company, such as through stock, privileges, ownership or other means. Due to Dutch Wwft 2018 (“Wet ter voorkoming van witwassen en financieren van terrorisme”) it can be required by law for some activities to determine the UBO. This law is relevant for many companies in the economic reality, such as accountants and tax advisors.
Company.info provides a number of services that automate the UBO determination. Using Invantive SQL, you can easily address these services with ease.
Run UBO App
For this sample, I’ve chosen to use Invantive Producer Runtime as a runtime environment to execute Invantive SQL, Invantive PSQL and Invantive Script statements delivered as pre-packaged solutions.
After logging in to Company.info, you can select one of the pre-packaged solutions available to you:
Run UBO App
For this sample, I’ve chosen to use Invantive Producer Runtime as a runtime environment to execute Invantive SQL, Invantive PSQL and Invantive Script statements delivered as pre-packaged solutions.
After logging in to Company.info, you can select one of the pre-packaged solutions available to you:
The pre-defined parameters are displayed and enable you to enter the Chamber of Commerce number of the legal entity being researched:
After some time, the UBO research is finished and the pre-packaged solution saves the report in an Excel sheet, such as:
Invantive SQL/PSQL
The following annotated SQL code with Invantive PSQL performs the actual logic, and can also be used from within all other Invantive products such as the Excel add-in Invantive Control or the ETL-tool Invantive Data Hub:
declare
--
-- Determine Ultimate Beneficiary Owner.
--
-- These parameters are typically provided as procedure parameters,
-- therefore the 'p_' notation style.
--
-- The ${} variables are Invantive Script variables. You can also
-- use bind variables using the notation ':NAME'.
--
p_coc_number varchar2 := '${p_coc_number}';
p_max_loops pls_integer := 10;
--
l_token varchar2;
l_loop boolean;
l_loop_cnt pls_integer;
l_check_status varchar2;
begin
--
-- Do not use the result cache, since the UBO determination
-- is asynchroneous and in this sample requires polling till the
-- lookup is completed.
--
set use-result-cache false;
--
-- Retrieve a token to later check the status of the UBO determination.
--
select token
into l_token
from dutchBusinessUBOStartInvestigation(p_coc_number, null, null)
;
--
-- Wait till the data is available.
--
-- When you have many lookups to be made, please request them all
-- in the previous step and store the associated tokens in an
-- in-memory table.
--
l_loop := true;
l_loop_cnt := 0;
while l_loop
loop
select /*+ http_disk_cache(false) http_memory_cache(false) ods(false) */
status
into l_check_status
from dutchBusinessUBOCheckInvestigation(l_token)
;
if l_check_Status = 'finished'
then
l_loop := false;
else
dbms_lock.sleep(5);
l_loop_cnt := l_loop_cnt + 1;
if l_loop_cnt > p_max_loops
then
l_loop := false;
end if;
end if;
end loop;
--
-- UBO determination is finished.
-- Store results in a table.
--
create or replace table ubo@inmemorystorage
as
select *
from dutchBusinessUBOPickupInvestigation(l_token)
;
--
-- Create some output format.
--
create or replace table output@inmemorystorage
as
select 'Ultimate Beneficiary of ' || p_coc_number || ' on ' || to_char(sysdate)
subject
, 'Ultimate Benificiary structure of ' || p_coc_number || ':'
|| chr(13)
|| chr(13)
|| name
|| ' with file number '
|| dossier_number
|| ' (RSIN '
|| organisations_item_extract_data_legal_entity_rsin_number
|| '): '
|| persons_item_first_name
|| ' '
|| persons_item_middle_names
|| ' '
|| persons_item_last_name
|| ', born '
|| to_char(persons_item_date_of_birth, 'DD-MM-YYYY')
|| ' in '
|| persons_item_place_of_birth
|| '. Retrieved from document retrieved on '
|| to_char(organisations_item_extract_data_document_date, 'DD-MM-YYYY')
|| ' stating involvement as '
|| tree_parents_item_role_type
|| ' in '
|| tree_parents_item_name
|| '.'
body
from UBO@InMemoryStorage
;
end;
--
-- Export the report in this case as an Excel sheet.
--
select *
from output@inmemorystorage
local export results as "${p_target_file_path}" format xlsx
--
-- Show user a message that work has been finished using Invantive Script.
--
local show message "Ultimate Beneficary Owner investigation completed of CoC number {p_coc_number}. Please find report in ${p_target_file_path}."