bcgov / bcgov/entity

Migrate businesses altered from (BC, ULC,CC) to BEN

Open
#27,342 2 comments 0 reactions 0 assignees View on GitHub
Entities - Data Migration
Dominant language
JavaScript
Stars
23
Forks
62
Avg merge
24m
Merged PRs (30d)
1

Description

These businesses are active in Lear and users are filing new filings in Lear (missing oldest filings)

Find all businesses altered from (BC, ULC, CC, C, CUL, CCC) -> (BEN, CBEN) - (filed through notebook)
```
-- Query in lear db
select b.identifier, f.*
from public.filings f
join businesses b on b.id = f.business_id
where
filing_type='alteration'
and meta_data->'alteration'->>'fromLegalType' in ('BC', 'ULC', 'CC', 'C', 'CUL', 'CCC')
and meta_data->'alteration'->>'toLegalType' in ('BEN', 'CBEN')
and b.id in (
select b.id from public.filings f
join businesses b on b.id=f.business_id
where
filing_type='lear_epoch'
and b.legal_type in ('BEN','BC', 'ULC', 'CC', 'CBEN','C', 'CUL', 'CCC')
and filing_json->'filing'->'business'->>'legalType' <> 'BEN')
```

Approach to migrate BC -> BEN

Load previous filings with version data (no changes to primary table), while loading transaction_id for the previous filings should be smaller. Lear require previous filings should have smaller transaction_id, which is used to identify the order of filings
- Option 1: Find all missing transaction ids from the transaction table. (if it takes too long to execute this query then dump the whole missing transaction_id's into a temporary table and use this transaction id to load previous filings)
```
-- Query to find missing transaction_id
SELECT gs.i AS missing_transaction_id
FROM generate_series(1, ) AS gs(i)
LEFT JOIN public.transaction t ON t.id = gs.i
WHERE t.id IS NULL and NOT EXISTS (select * from (
SELECT 'filings' as table_name, count(1) FROM public.filings WHERE transaction_id=gs.i UNION
SELECT 'addresses' as table_name, count(1) FROM public.addresses_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'aliases' as table_name, count(1) FROM public.aliases_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'amalgamating_businesses' as table_name, count(1) FROM public.amalgamating_businesses_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'amalgamations' as table_name, count(1) FROM public.amalgamations_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'businesses' as table_name, count(1) FROM public.businesses_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'jurisdictions' as table_name, count(1) FROM public.jurisdictions_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'offices_held' as table_name, count(1) FROM public.offices_held_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'offices' as table_name, count(1) FROM public.offices_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'parties' as table_name, count(1) FROM public.parties_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'party_roles' as table_name, count(1) FROM public.party_roles_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'resolutions' as table_name, count(1) FROM public.resolutions_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'share_classes' as table_name, count(1) FROM public.share_classes_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'share_series' as table_name, count(1) FROM public.share_series_version WHERE transaction_id=gs.i or end_transaction_id=gs.i
) t2 WHERE count > 0)
ORDER BY gs.i DESC;

- Option 2: Update all the existing filing with new transaction id which is greater than the newly loaded filings
```

Note: Businesses involved in multiple conversion (BC1255957, BC0460007) from BC -> BEN and then BEN -> BC - (Before Directed Launch) - Need to include in both migration scenario

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.