ULC/CCC/BC pipeline - data mapping analysis for Change of Directors

davemck513 commented 1 month ago

This ticket is used to analyze whether we have the required tables and data in the COLIN db extract(postgres) and LEAR db to start updating the corps data pipeline to bring over Change of Directors for corps.

[x] Determine if COLIN db extracts all relevant data for filing that need to make it into LEAR db. Reference data mapping(COLIN LEAR) doc as starting point. May need to talk with David Roberts if there are questions about data mapping doc or COLIN specific questions.
[x] Determine if LEAR db supports all the data from COLIN db extract
[ ] Cut tickets as required based off of analysis done in this ticket. Potential tickets may include:
- Update COLIN db extract script to pull missing data
- Update LEAR db and/or code to support data being pulled over from COLIN db extract

eason-pan-bc commented 1 week ago

The tables in COLIN db (DEV) related to Filing Type `NOCDR` (change of directors)

EVENT
CORP_PARTY
FILING
LEDGER_TEXT
NOTIFICATION (as per discussion with Dylan: 99% sure that we can ignore this table entirely)
~~PAYMENT~~ (Do not care at this moment)
FILING_USER
FURNISH_REPORT (TBD)
~~MRAS_MESSAGE_OUTBOUND~~ (as per discussion with Dylan, we can ignore ALL tables that have "MRAS" in the name as well as ALL tables that end in "_STG")
~~PAYMENT_STATE~~ (in data_mapping doc, there is a comment: as per conversation with David M, assuming we are not moving these tables over unless told otherwise count = 2,136,596)

There are other tables also been modified which are not directly connected by `EVENT_ID`, but by other keys like `MAILING_ADDR_ID`

ADDRESS
~~BATCH_REPORT_LOG~~ (as per discussion with Dylan, We are replacing some COLIN batch jobs, e.g the Involuntary Dissolution runs, but there is nothing in that table we are interested in. Same for all the other tables where the name starts "BATCH_....")

Tool Sharing

Created a helper tool to find out non-null columns in tables related to a particular filing type in COLIN. https://github.com/eason-pan-bc/data_mapping_analysis_tool

eason-pan-bc commented 4 days ago

Analysis Summary

1- May need work for data mapping

They are already in COLIN extract, but haven't been mapped to LEAR db	COLIN Table	COLIN Columns
EVENT	EVENT_TYP_CD
CORP_PARTY	PREV_PARTY_ID
LEDGER_TEXT	LEDGER_TEXT_DTS
LEDGER_TEXT	USER_ID

2 - Need further discussion

NOTIFICATION table: 99% sure that we can ignore, double check with Dylan again FURNISH_REPORT table: Dylan is having discussions on this

3 - May need to figure out for LEAR CoD filing

There are several columns in some tables DO NOT have been mapped out, but related to CoD filing in LEAR.	Table	Column
parties	email	EMAIL_ADDRESS in COPR_PARTY are all null
addresses	delivery_instructions	not converting
addresses	address_type	MAILING_ADDR_ID, DELIVERY_ADDR_ID in CORP_PARTY
party_roles	party_id
transaction	issued_at	e.g. 30.07.2019 19:43:20 947
business	last_cod_date
filings	id
filings	filing_json	not exist in COLIN, since they were not using JSON
filings	payment_id
filings	submitter_id
filings	payment_completion_date
filings	completion_date
filings	source	if mapped from COLIN then it's "COLIN"
filings	parent_filing_id
filings	payment_status_code
filings	meta_data	not exist in COLIN, since they were not using JSON

Detailed Mapping Information

In this Google Sheet file, you will find 2 spreadsheets,

COLIN to LEAR map: contains detailed column to column mapping information, including notes from the data_mapping doc and from this analysis. Also, have a column called isExtracted to indicate whether that column is in the COLIN extract or not.
LEAR Takes: contains details about the tables and columns related to CoD filing in LEAR. Also, includes a column called hasMappedOut to indicate whether it's already been mapped out or not with some notes if possible. Doc: CoD_filing_data_mapping

vikas-gov commented 4 days ago

@argush3 @chenhongjing to review

eason-pan-bc commented 3 days ago

Dummy company created in COLIN Tst

Company Number: BC1144712 Name of Company: 1144712 B.C. LTD. Company Email Address: eason.pan@gov.bc.ca You have the following company password for the above company. Company Password: test123456 BN_9: 985439009 BN_15: 985439009BC0001

CoD filing (NOCDR)

Event_id (CoD/NOCDR)

9321796,9321798,9321802,9321816,9321817,9321818

eason-pan-bc commented 3 days ago

Tables referencing to EVENT table

REFERENCING_TABLE	REFERENCING_COLUMN (-> EVENT.EVENT_ID)
CORP_PARTY	START_EVENT_ID
CORP_PARTY	END_EVENT_ID
FILING	EVENT_ID
LEDGER_TEXT	EVENT_ID
NOTIFICATION	EVENT_ID
PAYMENT	EVENT_ID
FILING_USER	EVENT_ID
FURNISH_REPORT	EVENT_ID
MRAS_MESSAGE_OUTBOUND	STG_ID
OFFICE	END_EVENT_ID
OFFICE	START_EVENT_ID
PAYMENT_STATE	EVENT_ID

Tables referencing to the related tables listed above

Main Table	Column in main table	Connected Table	column in connected table
CORP_PARTY	MAILING_ADDR_ID	ADDRESS	ADDR_ID
CORP_PARTY	DELIVERY_ADDR_ID	ADDRESS	ADDR_ID
FURNISH_REPORT	BATCH_ID	BATCH_REPORT_LOG	BATCH_ID
NOTIFICATION	MAILING_ADDR_ID	ADDRESS	ADDR_ID

argush3 commented 1 day ago

great work @eason-pan-bc ! the CoD filing data mapping analysis spreadsheet, tool you created and notes were all very helpful. I think there are definitely some things I'll need to follow up with Dylan on before we get to the implementation of this filing.

I'm going to move this to done in the meantime.

bcgov / entity