SPIKE Obtain Archived Collateral from the Home Team (Avocette)

[arlentees]

• [ ] Get all PPR database extract files from Avocette
• [ ] Load data in PostGres

There are some lingering problematic general collateral records migrated from legacy where the sequence is out of order. If available, obtain legacy PPR DB2 extract files from the January, 2022 data migration.

For historical point in time searches and requests for discharge verification statements on base registrations that are not in the modern database, obtain the legacy PPR archive files for loading into s PostgreSQL database.

Store the extract/archive files at least temporarily on the Assets Google drive in the folder: https://drive.google.com/drive/folders/17BcyOPb3KNb54lBD3Zk7tnWWFjbumydt

For individual general collateral data fixes, we have been using a PostgreSQL table loaded by Bob Bowles named general_collateral_legacy as the source.

An outstanding related task is to correct active PPR legacy registrations that have been identified as having duplicate general collateral text. A spreadsheet that identifies the 133 remaining registrations with the updated text is here: https://docs.google.com/spreadsheets/d/1P2-Khj_ZbPRt60FMsMXCAZ_5fPw6h1C9anZ6HfYfkM0/edit?gid=6303236#gid=6303236

A database update script has been created and run in DEV to correct the duplicate general collateral text. The corrections can be verified by comparing searches from DEV and TEST using some random registration numbers from the spreadsheet. Upate notes:

• Only active registration (not discharged or expired) are updated.
• The extract is from PROD which has data in a different state than DEV and TEST. Not all registrations to be updated in PROD will be updated in DEV and TEST.
• Green update cells indicate that the update of a random sample of registrations was verified in DEV

2024-09-04 files for conversion date document and general collateral received. File format is fixed length. Next steps:

• [x] Convert file to utf-8 csv.
• [x] Load into DEV as is (legacy format) in new table named "ppr_legacy_extract_gc".
• [x] Analyze table definitions and initial compare of general collateral by registration id
• [x] Create a database function to coalesce and clean up the legacy general collateral text split into block numbers.

[arlentees]

Review policy for using Google Drive as a temp. storage solution

[doug-lovett]

Email sent to the home team (Michael Kagis) asking for the legacy PPR database extract files.