Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Registered entities in the "Other" Category should be added to OrgBook #322

Closed
swcurran opened this issue Jun 15, 2023 · 22 comments
Closed

Registered entities in the "Other" Category should be added to OrgBook #322

swcurran opened this issue Jun 15, 2023 · 22 comments
Assignees
@amanji

Comments

@swcurran
Copy link
Contributor

About 545 legal entities are in the "Other" category at BC Registries and are not loaded into OrgBook BC. This makes it impossible for those searching for these entities to find them in OrgBook BC. Propose that these entities be added to BC Gov, perhaps with a tag indicating how the information about them may differ from other entities -- notably that there is not a real-time feed of updates.

We have had 2 queries from people trying to find these entities in OrgBook or anywhere else. They can't even determine if they are registered entities. Their issue is that when they interact with other organizations that require proof of existence (e.g. banks, etc.) as is done for other registered entities -- they can't find the information in all "usual sources" such as OrgBook.

This is not a trivial update as (from what I understand) the records on these entities are not in the "normal" place. I'm not sure how many historical entities would properly be included in this feed and published on OrgBook.

Here is a list from BC Registries of the types of entities in "Other" and the number of entities impacted:

TYPE                      COUNT
====                      =====
Cemetery                     28
Financial Institutions      143
Parishes                     74
Pension Funded Society        2
Public Library Association  109
Railways                     10
Society Branch                2
Tramways                     11
Trust                       166
@swcurran
Copy link
Contributor Author

BC Registries has requested these categories be added to OrgBook BC:

CODE DESCRIPTION
==== ===========
FI Financial Institutions
LIB Public Library Association
PAR Parishes

@amanji
Copy link
Contributor

amanji commented Jul 27, 2023

Do we recommend manually adding these to OrgBook or queuing them up as usual @WadeBarnes?

Need a bit more clarification on were being asked to do here.

@swcurran
Copy link
Contributor Author

@ianco — any chance you can give us your thoughts on this one? Have you looked into this at all?

@ianco
Copy link
Contributor

ianco commented Jul 27, 2023

There are 2 steps - (1) update the jobs to include the new company types (this will start including any new changes going forward), and (2) run a job to bring over any existing companies with those types.

For (1) the change is here:

https://github.com/bcgov/von-bc-registries-agent/blob/master/data-pipeline/bcreg/bcregistries.py#L40

or:

https://github.com/bcgov/von-bc-registries-agent/blob/master/data-pipeline/bcreg/bcreg_lear.py#L34

... depending if the corps are in COLIN or LEAR (I think probably COLIN)

For (2) I don't think we have an existing job to do this, I'd need to check. We did something similar when we added the Business Number credential, with this job:

https://github.com/bcgov/von-bc-registries-agent/blob/master/data-pipeline/bcreg/process-corps-generate-bn-creds.py

... so something similar to bring over all credentials for existing companies for the new "in scope" types

@swcurran
Copy link
Contributor Author

@amanji — does that help?

@ianco — these are the “Others” that are (AFAIK) not in COLIN or LEAR, but handled in some other way. However, maybe they come through COLIN and we skip them. @amanji can you see in the code if we are bypassing the three types above?

@amanji
Copy link
Contributor

amanji commented Jul 27, 2023

Will take a look. Thanks @ianco and @swcurran

@ianco
Copy link
Contributor

ianco commented Jul 27, 2023

these are the “Others” that are (AFAIK) not in COLIN or LEAR, but handled in some other way.

If they're not in COLIN or LEAR then we need a whole new job to pull the data, we need to confirm exactly where the data is coming from

@swcurran
Copy link
Contributor Author

Thanks @ianco — or should I say merci @ianco! @amanji — either they are coming through COLIN and are being explicitly skipped or we need to find out where we can get them from. Fun...

@amanji
Copy link
Contributor

amanji commented Jul 28, 2023 •
edited
Loading

From a cursory glance it looks like they are coming through COLIN. I can query the BC Registries corp types and see all "OTHER" types:

Here is what I was able to gather:
FI N OT FINANCIAL Financial Institutions
CEM N OT CEMETARY Cemetary
CP N OT COOP Cooperative
LIB N OT LIBRARY Public Library Association
PA N OT PRIVATE ACT Private Act
PAR N OT PARISHES Parishes
PFS N OT PENS FUND SOC Pension Funded Society
RLY N OT RAILWAYS Railways
SB N OT SOCIETY BRANCH Society Branch
T N OT TRUST Trust
TMY N OT TRAMWAYS Tramways
XCP N OT XPRO COOP Extraprovincial Cooperative

Some of the items like COOPs are not listed in the description above. Should all types be added or just the ones listed?

@swcurran
Copy link
Contributor Author

For now — just the listed ones.

@amanji
Copy link
Contributor

amanji commented Jul 31, 2023

@ianco is there any chance you'd be free to jump on a call this week to review the different scripts? There are a few different von-bc-registries repos with scripts for various operations. I recall some information but it seems RC has pruned a lot of old messages that I can no longer reference.

@ianco
Copy link
Contributor

ianco commented Aug 8, 2023

@ianco is there any chance you'd be free to jump on a call this week

Hi @amanji sorry for the late reply, I'm available this week (except Friday) to chat

@WadeBarnes
Copy link
Member

WadeBarnes commented Aug 30, 2023 •
edited
Loading

All of the corp types have been processed and exist in the event processor database and the ones that are/were not in scope (commented out of the list below) were skipped when it came to posting their credentials.

von-bc-registries-agent/data-pipeline/bcreg/bcregistries.py

Lines 40 to 75 in 3329164

CORP_TYPES_IN_SCOPE = {
"A": "EXTRA PRO",
"B": "EXTRA PRO",
"BC": "BC COMPANY",
"BEN": "BENEFIT COMPANY",
"C": "CONTINUE IN",
"CC": "BC CCC",
"CP": "COOP",
"CS": "CONT IN SOCIETY",
"CUL": "ULC CONTINUE IN",
"EPR": "EXTRA PRO REG",
"FOR": "FOREIGN",
"FI": "FINANCIAL",
#"GP": "PARTNERSHIP",
"LIB": "LIBRARY",
"LIC": "LICENSED",
"LL": "LL PARTNERSHIP",
"LLC": "LIMITED CO",
"LP": "LIM PARTNERSHIP",
"MF": "MISC FIRM",
"PA": "PRIVATE ACT",
"PAR": "PARISHES",
"QA": "CO 1860",
"QB": "CO 1862",
"QC": "CO 1878",
"QD": "CO 1890",
"QE": "CO 1897",
"REG": "REGISTRATION",
"S": "SOCIETY",
#"SP": "SOLE PROP",
"ULC": "BC ULC COMPANY",
"XCP": "XPRO COOP",
"XL": "XPRO LL PARTNR",
"XP": "XPRO LIM PARTNR",
"XS": "XPRO SOCIETY",
}

Updates have been made to the event processor to include several other organization types to the in scope list; #324

Scripts have been added to the von-bc-registries-agent-configurations ./manage script to requeue skipped organization records; bcgov/von-bc-registries-agent-configurations#71

The related updates have been deployed to dev and the scripts have been run to requeue the new in scope records. Theses have been processed and are appearing on the OrgBook side now.

@WadeBarnes
Copy link
Member

The code has been promoted to the OrbBook Test environment, and the von-bc-registries-agent-configurations ./manage scripts have been run.

Steps:

$ ./manage -e test requeueSkippedCorpTypes FI LIB PAR

Executing command on 7cba16-test/event-db-primary-7-csgqq:
        psql -d ${POSTGRESQL_DATABASE} -ac "update event_by_corp_filing set process_success = null, process_date = null, process_msg = null where process_msg like 'FI: Skipped%';"

update event_by_corp_filing set process_success = null, process_date = null, process_msg = null where process_msg like 'FI: Skipped%';
UPDATE 142


Executing command on 7cba16-test/event-db-primary-7-csgqq:
        psql -d ${POSTGRESQL_DATABASE} -ac "update event_by_corp_filing set process_success = null, process_date = null, process_msg = null where process_msg like 'LIB: Skipped%';"

update event_by_corp_filing set process_success = null, process_date = null, process_msg = null where process_msg like 'LIB: Skipped%';
UPDATE 106


Executing command on 7cba16-test/event-db-primary-7-csgqq:
        psql -d ${POSTGRESQL_DATABASE} -ac "update event_by_corp_filing set process_success = null, process_date = null, process_msg = null where process_msg like 'PAR: Skipped%';"    

update event_by_corp_filing set process_success = null, process_date = null, process_msg = null where process_msg like 'PAR: Skipped%';
UPDATE 72

$ ./manage -e test getPipelineStatus

BC_REG : Table: event_by_corp_filing Processed: 1838261 Outstanding: 330
BC_REG :        event_by_corp_filing Process Errors: 0
BC_REG : Table: corp_history_log Processed: 1624766 Outstanding: 0
BC_REG :        corp_history_log Process Errors: 0
BC_REG : Table: credential_log Processed: 3621398 Outstanding: 0
BC_REG :        credential_log Process Errors: 0
BCREG_LEAR : Table: event_by_corp_filing Processed: 100321 Outstanding: 0
BCREG_LEAR :        event_by_corp_filing Process Errors: 0
BCREG_LEAR : Table: corp_history_log Processed: 61790 Outstanding: 0
BCREG_LEAR :        corp_history_log Process Errors: 0
BCREG_LEAR : Table: credential_log Processed: 63228 Outstanding: 0
BCREG_LEAR :        credential_log Process Errors: 0

$ ./manage -e test runPipeline

>>> NOT Posted webhook level 2 ( 0 ), message Starting bc_reg_event_processor ...
bc_reg_event_processor: ★ 17:37m
bc_reg_event_processor / load_and_process_bc_reg_data: ★ 16.4s
bc_reg_event_processor / load_and_process_bc_reg_data / register_un_processed_events: ★ 2:30m
bc_reg_event_processor / load_and_process_bc_reg_data / register_un_processed_events: /data-pipeline/.venv/bin/python3 -u "bcreg/find-unprocessed-events.py"
bc_reg_event_processor / load_and_process_bc_reg_data / register_un_processed_events: >>> Processing 1 of 50 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / register_un_processed_events: >>> Processing 50 of 50 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / register_un_processed_events: succeeded,  16 seconds
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: ★ 3.0s
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: /data-pipeline/.venv/bin/python3 -u "bcreg/process-corps-generate-creds.py"
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: >>> in scope corp types: {'A': 'EXTRA PRO', 'B': 'EXTRA PRO', 'BC': 'BC COMPANY', 'BEN': 'BENEFIT COMPANY', 'C': 'CONTINUE IN', 'CC': 'BC CCC', 'CP': 'COOP', 'CS': 'CONT IN SOCIETY', 'CUL': 'ULC CONTINUE IN', 'EPR': 'EXTRA PRO REG', 'FOR': 'FOREIGN', 'FI': 'FINANCIAL', 'LIB': 'LIBRARY', 'LIC': 'LICENSED', 'LL': 'LL PARTNERSHIP', 'LLC': 'LIMITED CO', 'LP': 'LIM PARTNERSHIP', 'MF': 'MISC FIRM', 'PA': 'PRIVATE ACT', 'PAR': 'PARISHES', 'QA': 'CO 1860', 'QB': 'CO 1862', 'QC': 'CO 1878', 'QD': 'CO 1890', 'QE': 'CO 1897', 'REG': 'REGISTRATION', 'S': 'SOCIETY', 'ULC': 'BC ULC COMPANY', 'XCP': 'XPRO COOP', 'XL': 'XPRO LL PARTNR', 'XP': 'XPRO LIM PARTNR', 'XS': 'XPRO SOCIETY'}
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 14.65658096037805
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: >>> Processing 1 of 380 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 16.42063520103693
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: >>> Processing 101 of 380 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 17.92880072724074
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: >>> Processing 201 of 380 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 19.321381527930498
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: >>> Processing 301 of 380 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 23.505468552932143
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: >>> Processing 380 of 380 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 23.510772311128676
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 24.804288156330585
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: >>> Processing 1 of 1 corporations.
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: Processing: 24.825671545229852
bc_reg_event_processor / load_and_process_bc_reg_data / load_bc_reg_data: succeeded,  25 seconds
bc_reg_event_processor / load_and_process_bc_reg_data / create_bc_reg_credentials: ★ 0.4s
bc_reg_event_processor / load_and_process_bc_reg_data / create_bc_reg_credentials: /data-pipeline/.venv/bin/python3 -u "bcreg/generate-creds.py"
bc_reg_event_processor / load_and_process_bc_reg_data / create_bc_reg_credentials: >>> in scope corp types: {'A': 'EXTRA PRO', 'B': 'EXTRA PRO', 'BC': 'BC COMPANY', 'BEN': 'BENEFIT COMPANY', 'C': 'CONTINUE IN', 'CC': 'BC CCC', 'CP': 'COOP', 'CS': 'CONT IN SOCIETY', 'CUL': 'ULC CONTINUE IN', 'EPR': 'EXTRA PRO REG', 'FOR': 'FOREIGN', 'FI': 'FINANCIAL', 'LIB': 'LIBRARY', 'LIC': 'LICENSED', 'LL': 'LL PARTNERSHIP', 'LLC': 'LIMITED CO', 'LP': 'LIM PARTNERSHIP', 'MF': 'MISC FIRM', 'PA': 'PRIVATE ACT', 'PAR': 'PARISHES', 'QA': 'CO 1860', 'QB': 'CO 1862', 'QC': 'CO 1878', 'QD': 'CO 1890', 'QE': 'CO 1897', 'REG': 'REGISTRATION', 'S': 'SOCIETY', 'ULC': 'BC ULC COMPANY', 'XCP': 'XPRO COOP', 'XL': 'XPRO LL PARTNR', 'XP': 'XPRO LIM PARTNR', 'XS': 'XPRO SOCIETY'}
bc_reg_event_processor / load_and_process_bc_reg_data / create_bc_reg_credentials: succeeded,  0 seconds
bc_reg_event_processor / load_and_process_bc_reg_data: succeeded, 41 seconds
bc_reg_event_processor / submit_bc_reg_credentials: ★ 17:21m
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: ★ 17:21m
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: /data-pipeline/.venv/bin/python3 -u "bcreg/submit-creds.py"
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: >>> Processing 100 of 382 credentials.
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: Processing: 10.844869678840041
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: >>> Processing 200 of 382 credentials.
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: Processing: 25.2991948639974
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: >>> Processing 300 of 382 credentials.
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: Processing: 40.49468071106821
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: >>> Processing 382 of 382 credentials.
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: *** Processing: 50.414698554202914
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: 407.0241534408483 credentials per minute
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: >>> Waiting for all outstanding tasks to complete ...
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: >>> Completed.
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: Processing: 61.324073198251426
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: 373.75208143632466 credentials per minute
bc_reg_event_processor / submit_bc_reg_credentials / submit_credentials: succeeded,  1 minutes, 1 seconds
bc_reg_event_processor / submit_bc_reg_credentials: succeeded, 1 minutes, 43 seconds
bc_reg_event_processor: succeeded, 1 minutes, 43 seconds
>>> NOT Posted webhook level 2 ( 0 ), message Ran bc_reg_event_processor - complete.

$ ./manage -e test getPipelineStatus

BC_REG : Table: event_by_corp_filing Processed: 1838642 Outstanding: 1
BC_REG :        event_by_corp_filing Process Errors: 0
BC_REG : Table: corp_history_log Processed: 1625144 Outstanding: 0
BC_REG :        corp_history_log Process Errors: 0
BC_REG : Table: credential_log Processed: 3621780 Outstanding: 0
BC_REG :        credential_log Process Errors: 0
BCREG_LEAR : Table: event_by_corp_filing Processed: 100321 Outstanding: 0
BCREG_LEAR :        event_by_corp_filing Process Errors: 0
BCREG_LEAR : Table: corp_history_log Processed: 61790 Outstanding: 0
BCREG_LEAR :        corp_history_log Process Errors: 0
BCREG_LEAR : Table: credential_log Processed: 63228 Outstanding: 0
BCREG_LEAR :        credential_log Process Errors: 0

@WadeBarnes
Copy link
Member

Results can be found here:

@WadeBarnes
Copy link
Member

WadeBarnes commented Aug 30, 2023 •
edited
Loading

Note the entity type for the Financial records is not displaying correctly.

image

Is this the fix; bcgov/orgbook-bc-client#185

@WadeBarnes WadeBarnes self-assigned this Aug 31, 2023
@WadeBarnes
Copy link
Member

These and the corresponding UI fixes, bcgov/orgbook-bc-client#185, have been deployed to test.

@WadeBarnes
Copy link
Member

@amanji, ready for testing

@WadeBarnes WadeBarnes assigned amanji and unassigned WadeBarnes Aug 31, 2023
@amanji
Copy link
Contributor

amanji commented Aug 31, 2023

The labels are now displaying properly but I think I agree with @swcurran. It's weird that they are pluralized while the others are singular. I'd like to propose updating to make those three labels in line and singularize them.

@amanji
Copy link
Contributor

amanji commented Aug 31, 2023

bcgov/orgbook-bc-client#194

@WadeBarnes
Copy link
Member

bcgov/orgbook-bc-client#194

The updates have been deployed to OrgBook dev and test.

@WadeBarnes
Copy link
Member

All changes related to this ticket have been deployed to prod, and the affected organization types have been processed and loaded into the OrgBook.

  • BC Registries Agent v2.5.2
  • OrgBook Client interface v1.0.13
  • The backend remains at Aries-VCR 1.9.6

Loaded Organizations can be found here:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@amanji amanji

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@swcurran @ianco @amanji @WadeBarnes