Update the batch job to use colin & auth and select only those that have a unique email associated with the business

[trishreimer]

Problem statement: we have about 175K accounts that need to be setup on the new platform. The new AR filing needs to target this group, excluding law firms to help people get logged in and affiliated. We do not want to send the new AR Reminder to law firms because most of these firms will need premium accounts and other premium account functionality, like folio, statements, etc.

Solution: Update the job to use colin & auth to pull those businesses that are ready for their AR, and have a unique email associated with them.

By targeting those with a unique email address, we remove most law firms and still target 140K people. This will make the screen text cleaner as we won't need to consider these different audiences and support easier. It also reduces potential issues like: if we sent the reminder to all bc corps and we turned off old reminder, we'd have thousands of password resets by law firms.

The remaining 35K we are not targeting will need to setup their premium and/or basic accounts going through the standard create account flow and affiliate their businesses manually (or through mass affiliations if they fit into this group)

Note: It's possible that this will still go to some law firms that manage one corporation but those numbers are quite small and they could setup their premium account first...but will not have folio number in this flow. Basic may also be sufficient for them to use.

I chatted with Adriy and we are going to work on data refresh schedule for data in the auth-db

• Warehouse update schedule for CPRD is in place
• Query for AR returns same data in CPRD and the Warehouse
• auth- db further constrains the previous query to the BAR target audience
  ( doesn't already have an account, has a matching email, has a unique email, etc)

[trishreimer]

need to confirm if this logic is correct but here is the draft:
• Active
• BC corps only
• And where the BC Corp currently receives the AR Reminder by email
• And where user on legacy is not in “bcol” or “staff” – we want to include public users only
• And where we have unique emails associated to a BC Corporation.
• So, if one email is associated to more than one corporation, we do not include this email in our target group.
• Next, we want to exclude email addresses that are associated to an account on SBC Connect (as we targeting those without an account),
• so once we do the first selection of data above, we check and compare the emails we have with those on SBC Connect and if the email has an account, we remove that email from the list.

[Maribeth-Wilson]

Thanks Trish, this will be extremely helpful

[vikas-gov]

@Maribeth-Wilson

Reviewed with @droberts27 , need further discussion on the below 2 items

• And where user on legacy is not in “bcol” or “staff” – we want to include public users only - **Clarify
  @magszymanski Role Type code != 'bcol'
• we want to exclude email addresses that are associated to an account on SBC Connect - Clarify (initial thoughts, Its likely in the postgres DB, support from someone who has access to the DB)
• Affiliations live in AUTH DB
• Review between David/Omid/Sid C to exclude SBC connect overlapping data

[trishreimer]

I believe by Thor is going to do the query in auth DB.

right now, we just need the team to review the logic, not create or update the batch job.

The batch job that needs to be updated is the new batch job, not legacy.

[trishreimer]

This ticket is to support the work for the September 3rd release and going forward. The logic in the new batch job needs to be updated to reflect how we select the BC corporations

On September 3rd and going forward everything will be automatic.

[Maribeth-Wilson]

@thorwolpert here is the updated email text -note subject line has changed as well

[Maribeth-Wilson]

@nsmenon8890 - fyi

[droberts27]

The modern AR Prompt batch job needs to implement the following:

insert into set_ar_to_no (CORP_NUM,PREVIOUS_VALUE,UPDATE_DATE) values (:corp_num,(select send_ar_ind from corporation where corp_num=:corp_num),sysdate)
update corporation set send_ar_ind='N' where corp_num=:corp_num

[droberts27]

Here is my code for selecting BC corporations for AR Prompts for September 3, 2024.  It is missing the affiliation part and the February 29 part.

SELECT co.corp_num
     , co.recognition_dts
     , co.last_ar_filed_dt
     , co.admin_email
     , co.send_ar_ind
FROM corporation   co
   , corp_type     ct
   , corp_state    cs
WHERE co.corp_typ_cd    = ct.corp_typ_cd
  AND co.corp_num       = cs.corp_num
  AND cs.end_event_id   IS NULL
  AND cs.state_typ_cd   = 'ACT'                                                                             -- active
  AND ct.corp_class     = 'BC'                                                                              -- BC Corporations
  AND co.corp_typ_cd   <> 'BEN'                                                                             -- no Benefit Companies
  AND co.admin_email is NOT null                                                                            -- they have an email
  AND not exists (select 'x' from filing f,event e,filing_user u 
                  where f.event_id=e.event_id and f.event_id=u.event_id                                     -- no previous BCOL filings
                    and e.corp_num=co.corp_num and u.role_typ_cd='bcol')
  AND not exists (select 'x' from corporation where admin_email=co.admin_email and corp_num <> co.corp_num) -- no other business using the same email
  AND NVL(co.last_ar_filed_dt,co.recognition_dts ) < to_date('20240903','yyyymmdd')                         -- they have not already filed this AR
--AND they are not affiliated                                                                               -- not affiliated (needs to read Postgres database)  
  AND to_char(recognition_dts,'mm-dd')='09-03'                                                              -- AR due month-day

[trishreimer]

Why is this code written for the selecting corps in cprd? @droberts27 Are other changes needed?

We are first getting data from the DB warehouse for the AR reminder.

The job will update legacy by turning off the COLIN reminder.

https://www.figma.com/board/RdHFy0pmIgYDrJC5keS8fz/Business-AR-Radiator?node-id=0-1&t=jMK4FhdcedzvZl9m-0

[droberts27]

Think of it as a specification. Also handy for getting counts. It looks to me like corporation.send_ar_ind is not being set to N in CDEV and CTST. I manually set it to N in CPRD.

[trishreimer]

As noted in the figma diagram, the batch job should automatically update COLIN.

If it is not doing this, could you please ensure this requirement is noted and logged so it can be resolved @Maribeth-Wilson

[Maribeth-Wilson]

Thank you @droberts27 and @trishreimer - I have asked Thor this question but have not received a response. I will open a ticket specifically for this

[droberts27]

Reviewing the "External AR Filing MVP" Teams channel I see:

Trish's 2 examples, 0314148 and 0453984 were NOT selected in CPRD because they have a BCOL filing.

@thorwolpert Why were 0314148 and 0453984 selected from the Warehouse?

[thorwolpert]

I don't know @droberts27 it's the same query, so either there's some view or data differences.

[Maribeth-Wilson]

Update from @schaturv18:

There is an event table in COLIN which holds all the activities happening with a company. In COLIN it has about 21 million rows, but in the data warehouse it has about 499217 rows. This means there is a huge difference between the data in COLIN and the data warehouse.
We might need to refresh the event table in the data warehouse with the COLIN data.
There is a clause in our query which checks if the company has any BCOL filing and when we exclude those companies in that clause we make use of filing table, event table etc. If we remove that clause the number of rows returned by that query in both system is same.
So, someone will need to refresh event table in data warehouse.
FYI @thorwolpert; @ozamani9gh, @droberts27

[ozamani9gh]

@Maribeth-Wilson @hanlunBCRegistries @bolyachevets (i cant tag siddarth, haha), lets meet next week about what the next steps are after the datawarehouse issue and cprd has been resolved.

[bolyachevets]

@Maribeth-Wilson @hanlunBCRegistries @bolyachevets (i cant tag siddarth, haha), lets meet next week about what the next steps are after the datawarehouse issue and cprd has been resolved.

the event table should be fixed now

[schaturv18]

Yes, the event table is fixed and query we use to get the list of companies to send AR prompt is returning same records in CPRD and Warehouse.