SP/GP - This GP fails when downloading Statement of Registration PDF from filing history - FM0538164. Ops #1621

[Mihai-QuickSilverDev]

FM0538164

Statement of reg is visible on the ledger, not on paper only but download of PDF fails with HTTP 500 no error body.

[Mihai-QuickSilverDev]

@argush3

[argush3]

In SOFI the statement of registration for this firm is paper only and in COLIN it is marked as such. On the LEAR side the registration filing is marked as available for download. This probably happened due to the attempt to implement the data migration logic outlined in #13168 for the SP/GP pipeline.

[Mihai-QuickSilverDev]

So Argus, where do we go from here? Are you going to mark in Lear the outputs for this firm as paper only, also? Thank you!

[argush3]

I will mark this as paper only in LEAR and try to figure out the SOFI logic for displaying this.

[argush3]

@Mihai-QuickSilverDev I've updated the ops ticket to resolve this issue for the specific firm. Will dig a bit more into this general scenario though.

[Mihai-QuickSilverDev]

Thanks very much, Argus!

[argush3]

I've looked into this a bit more and I believe this has to do with the SP/GP data migration logic not taking into consideration classic one stop session logic for specific filings between March 15, 2004 and April 8, 2011.

Whether a firm has a classic one stop session is determined by the OS_SESSION field value in the COLIN "CORPORATIONS" table. A OS_SESSION value of null or of length 6 represents a classic one stop session.

Currently getting the firms that have a class one stop session to determine how many filings in LEAR are affected.

[argush3]

@Mihai-QuickSilverDev I've analyzed this issue a bit more and determined that there are 16357 filings that were filed between March 15, 2004 and April 8, 2011 where the SP/GP is using a classic one stop session. I need to double check my query a bit more and talk to @rarmitag and @droberts27 but I believe we will need to flip the paper only flag to true for these filings in LEAR.

[argush3]

@Mihai-QuickSilverDev can you get approval from the business to set 16357 filings in the LEAR Prod db to paper only? Once you get approval, I'll work with @rarmitag in applying this data fix in Prod.

[argush3]

Data fix has been applied to Prod. Following sql run:

update filings
set paper_only = true
where id in (select f.id
             from businesses b
                      join colin_corporation cc on b.identifier = cc.corp_num
                      join filings f on b.id = f.business_id
             where 1 = 1
               and b.identifier like 'FM0%'
               and f.effective_date at time zone 'america/los_angeles' >= '2004-03-15'
               and f.effective_date at time zone 'america/los_angeles' <= '2011-04-08'
               and f.paper_only = false
               and f.meta_data -> 'colinFilingInfo' ->> 'filingType' in ('FRREG', 'FRARG', 'FRCRG')
               and not (f.meta_data -> 'colinFilingInfo' ->> 'filingType' = 'FRARG'
                 and f.meta_data -> 'colinFilingInfo' ->> 'filingType' ~ '^CONV.*$'));

Spot checked FM0392743, FM0375832, FM0404534, FM0494014, FM0517511, FM0485332, FM0495145, FM0458406, FM0543289 and FM0540953 in Prod to verify registration filings on ledger are now marked as paper only and that business summary is downloadable.

@riyazuddinsyed you can verify this datafix by spot checking the same business identifiers I used or you can ping me for the complete list of filings updated.