FSADT1-357 Explore BC Registry and Orgbook BC API

[MCatherine1994]

FSADT1-357 Explore BC Registry and Orgbook BC API

Key problem to solve:

• Can we get status, company name, address, business number through these two apis? Using OrgBook API we can only get the company name, registration number and the status. BC Registry provides business address, we are waiting for response from them to learn how to get access

• What api call to use in order to get those information? Using OrgBook API:

  • search by name: https://orgbook.gov.bc.ca/api/v4/search/credential?ordering=-score&q=PACIFIC%20GEOTECH%20SYSTEMS%20LTD.&inactive=false&latest=true&revoked=false, this uses the wildcard search, so the return result could be a long list
  • search by registry number: https://orgbook.gov.bc.ca/api/v4/search/topic?ordering=-score&q=BC0605515&inactive=false&latest=true&revoked=false, if not specify the registry type, could return multiple result

• Some data analysis query General Count:

  // number of active client in type corporation and association 23437
  select COUNT(*) from (select * from FOREST_CLIENT where CLIENT_TYPE_CODE in ('C', 'A') and CLIENT_STATUS_CODE='ACT');
  
  // number of active client in type corporation and association has a registry number 23410
  select COUNT(*) from (select * from FOREST_CLIENT where CLIENT_TYPE_CODE in ('C', 'A') and CLIENT_STATUS_CODE='ACT' and CORP_REGN_NMBR is not null);
  
  // number of active client in type corporation and association has a registry number and type code 13236
  select COUNT(*) from (select * from FOREST_CLIENT where CLIENT_TYPE_CODE in ('C', 'A') and CLIENT_STATUS_CODE='ACT' and CORP_REGN_NMBR is not null and REGISTRY_COMPANY_TYPE_CODE is not null);

  Check Duplicate:

  // number of active clients that has the same ID
  SELECT count(client_id)
  FROM
  (
  SELECT client_id, COUNT(*)
  FROM (
      SELECT registry_company_type_code || corp_regn_nmbr as client_id
      FROM forest_client
      WHERE = 'ACT'
      AND client_type_code in ('C', 'A')
  )
  GROUP BY client_id
  HAVING COUNT(*) > 1
  )
  
  // number of active client in type corporation and association has duplicate registry number 180
  SELECT count(corp_regn_nmbr)
  FROM
  (
    SELECT corp_regn_nmbr, COUNT(client_number)
    FROM forest_client
    WHERE client_status_code = 'ACT'
    AND client_type_code in ('C', 'A')
    GROUP BY corp_regn_nmbr
    HAVING COUNT(client_number) > 1
  )
  
  // example: same registry number, two clients have different names, one has type "A", one type is null
  SELECT * FROM forest_client WHERE corp_regn_nmbr = '0066876';
  
  // number of active client in type corporation and association has duplicate client name 65
  SELECT count(client_name)
  FROM
  (
    SELECT client_name, COUNT(client_number)
    FROM forest_client
    WHERE client_status_code = 'ACT'
    AND client_type_code in ('C', 'A')
    GROUP BY client_name
    HAVING COUNT(client_number) > 1
  );
  
  // example: same client name, two clients have different registry numbers, one has type "BC", one type is null
  SELECT * FROM forest_client WHERE client_name = 'ISLAND GARDEN ESTATES LTD.';

  Check accuracy:

  // number of registration number has '-', for example 's-0004149', '77-22479'  103
  select count(*) from (select * from FOREST_CLIENT where CLIENT_TYPE_CODE in ('C', 'A') and CLIENT_STATUS_CODE='ACT' and CORP_REGN_NMBR LIKE '%-%');
  
  // number of registration number has '?', for example '????????1' 6
  select count(*) from (select * from FOREST_CLIENT where CLIENT_TYPE_CODE in ('C', 'A') and CLIENT_STATUS_CODE='ACT' and CORP_REGN_NMBR LIKE '%?%');

  Check data matching: The REGISTRY_COMPANY_TYPE_CODE got from forest client table is way more less than the entity type in the orgbook https://github.com/bcgov/orgbook-bc-client/blob/main/src/i18n/en.json, some descriptions here. The orgbook records also only have 2 status (active and historical), our client table has 5 status (Active, Deactivated, Deceased, Receivership,Suspended)

• Are the data the same from bc registry and orgbook?

When to use the api:

• When create a new client, will auto populate the registry number and store the correct, matched format in the forest client table, so new data should be all good
• For old client data, if want to make the connection and correct the data based on orgbook information, call the api to search by registry number
  • if has the registration type code, the api will return the exact match
  • if doesn't have the registration type code, the api could return multiple results, then go through the return list and check the company name to find out the matching record

Simple use case: When we create a new client for an organization, we first check if it exists in forest client database or if has duplicate:

• if not exists, we call the orgbook or bc registry api to get data and auto populate the information, for example: company name, address, registry name, registry type, status (todo: what information to check)
• if there is one or has duplicate, we call the orgbook or bc registry api to get data and see if there is any update in orgbook or bc registry, and show a warning message (todo: what information to check)

Reference link: Bc registry API info https://developer.bcregistry.daxiom.ca/registry-search-api and https://developer.bcregistry.daxiom.ca/apis-summary/ and https://www2.gov.bc.ca/assets/gov/employment-business-and-economic-development/business-management/permits-licences-and-registration/registries-other-assets/api_info_session_april_22.pdf Orgbook API https://www.orgbook.gov.bc.ca/search, Orgbook BC https://www.orgbook.gov.bc.ca/entity/CP0002127

[MCatherine1994]

Todo:

• Check the relationship for three external tables MOF_USER_ORG_DEFAULT, NURSERY_LOCATION, ORG_UNIT
• Compare the client data vs orgbook data
• Clean up data (verify this on Monday's meeting)

[MCatherine1994]

Data comparison: compare the client data with the record in orgbook, just focus on the 23446 active client with client type corporation and association

Partial found (name or incorporation number match): 81% Found (name and incorporation number match): 54% Not found (neither name nor incorporation number match): 19% Match(name and incorporation number and status match): 32%

Definition for incorporation number match:

• client has both registryCompnayTypeCode and corpRegnNmbr, compare “registryCompnayTypeCode + corpRegnNmbr” with the incorporation number in orgbook
• client registryCompnayTypeCode is null, then just compare corpRegnNmbr with the number part of incorporation number

[MCatherine1994]

The business api from bc registry to get the address: https://bcregistry-sandbox.apigee.net/business/api/v2/businesses/{identifier}/addresses https://bcregistry-demo.apigee.io/docs/businessproxy/1/routes/businesses/%7Bidentifier%7D/addresses/get