search

bcgov / entity

ServiceBC Registry Team working on Legal Entities
Apache License 2.0
23 stars 58 forks source link

Mass Affiliation - Source lists of managed businesses for each lawfirm/service provider from COLIN #23079

Open fareenr opened 2 months ago

fareenr commented 2 months ago
  1. Create a query to get the current data from COLIN based on list of company email addresses that are the same for more than 50 business registration records. 1.1 Managed business list must contain all active BC Limited Companies, CCC, ULC (must not include sole proprietorships/general partnerships, limited partnerships, extraprovincial companies, LLP)

Report output: a. List output must include business legal identifier/ Incorp number and Company Name b. List must be exportable/ downloadable for external users (prefer csv format)

fareenr commented 1 month ago

List of admin email addresses with over 50 corps linked to them: https://docs.google.com/spreadsheets/d/13SDzgL_MtrJt7_mFfUMeRaDiu784-yig/edit?usp=drive_link&ouid=108001163913555862413&rtpof=true&sd=true

This is the list that the google form solution must be sent to for input of their BCROS account number.

fareenr commented 1 month ago

Sample of a file of managed businesses for 1 law firm with legal identifier/ incorporation number and business name with SQL: https://docs.google.com/spreadsheets/d/18pMFpl02jh8dq-VLKRtrfFG0z_6PXpQx/edit?usp=drive_link&ouid=108001163913555862413&rtpof=true&sd=true

Note that business type field may need to be added (tbd, waiting on response from Argus or Dietrich).

fareenr commented 1 month ago

List of lawfirms/service provider email addresses from COLIN that are affiliated to 50 or more corps (incl ULC and CCC) as of Sep 6 2024: Report shows list of incorp number https://docs.google.com/spreadsheets/d/13SDzgL_MtrJt7_mFfUMeRaDiu784-yig/edit?usp=drive_link&ouid=108001163913555862413&rtpof=true&sd=true

@sameer0422 - SQL query is on second tab, as this will need to be re-queried from COLIN prod db closer to the date the emails will be sent out to lawfirms.

JohnamLane commented 1 month ago

@sandragornall @fareenr Hoping that this can be owned by the SBC Connect team. I will book one more session to coordinate

ochiu commented 2 weeks ago

Adding 1 point to this, looks like we just need to coordinate and use the query provided closer to emailing date