Update LC Permit expiration batch email templates and 48 hour batch timer limit (SS) (Ready for UAT on TEST)

[TracyLinder]

Acceptance Criteria

• The 24 hour limit logic for checking expired LC licenses set up in #10849 should be removed from the batch process.
• The email template needs to be updated to the following:

  Expiration email template

Subject: ROW License and Permit Expiration Notice

Dear ROW License Holder,

This email is to advise you that your Right of Way Contractor License [2022-12345678 LC] expired on [LC folder Expiry date].

All Driveway/Sidewalk and Excavation permits associated with your Right of Way Contractor License have expired on [LC folder Expiry date] until the license is renewed. To renew your Right of Way Contractor License, look for the "How is a Right of Way Contractor License renewed?" Section at this URL - https://austintexas.gov/page/right-way-contractors

The following list includes the DS/EX Permits that have expired. To view a full list of expired permits, please visit https://abc.austintexas.gov/web/permit/folder-select?reset=true

[Folder Number of DS or EX folder, for example, 2022-012345 DS] - [Folder Name]
    2023-12345678-DS - New Driveway & Sidewalk

To reactivate your license, please view the documentation requirements on the Right of Way Contractor webpage {https://www.austintexas.gov/page/right-way-contractors}, and then submit your updated documentation to our SmartSheet {https://app.smartsheet.com/b/form/cad1e53c88224399979f40df80ef6270}.

Re-activation email template

Subject: ROW License and Permit Renewal Notice

Dear ROW License Holder,

This email is to advise you that your Right of Way Contractor License [2022-12345678 LC] has been reactivated.

All Driveway/Sidewalk and Excavation permits associated with your Right of Way Contractor License have been reactivated. Please note that any permits that are past their expiry date will not be reactivated.

The following list includes the DS/EX Permits that have been Reactivated. To view a full list of reactivated permits, please visit https://abc.austintexas.gov/web/permit/folder-select?reset=true

[Folder Number of DS or EX folder, for example, 2022-012345 DS] - [Folder Name]
    Example: 2023-12345678 DS - New Driveway & Sidewalk

• The number of permits listed should be TBD (Need Raj and Subha to confirm)
• If there are no DS/EX permits to list, system must display 'N/A' where the list of permits would have displayed. (from #15763)

Notes from original ticket description: Determine why folder 13045443 wasn't expired-license when the related license expired (12753417)

The license for Captex Construction, LLC expired on 2/22/24 however it's child permit (13045443 ) did not update to expired permit, it is still active.

[TracyLinder]

There also aren't any EX permits with the status expired-license, indicating an issue/bug.

[SubhaShroff]

@TracyLinder @Nadin-Nader @vschandramouli Batch - Expire EX DS with LC(Job RSN-203): if we don't put expired date/ or the date it was expired check in the Cursor to get the expired LC and ROW contractor, everyday the batch will hit all the expired LCs irrespective of the expirydate/stampdate. So, my question would be - are there any scenarios that DS/EX can get reactivated (other than renewing the LC with Renew Process), before even LC is renewed/reactivated? 1- If any such scenarios exist, while LC is still expired DS/EX are activated, but the batch will keep updating the DS/EX 'Active' status to 'Expired-License' status. 2- As you suggested to check the update date(stampdate) instead of expirydate, I am not sure if with the status update the stampdate is also updated. To know that, I would need to know what are the processes/batches or any other triggerring points that expire LC. So that I make sure those are all updating the stamp date also with status update.

[TracyLinder]

DTS-AMANDA Conversation 3/5/24

• We know that the where clause with expiration the last 24 hours is causing this issue.
• We know that we added that clause, but we can't remember why
• We are going to remove this and test it again, and go from there.

[SubhaShroff]

@TracyLinder @Nadin-Nader Expirydate WHERE clause commented and the commit is sent to DEV - SCTASK0401844. Thanks.

[Nadin-Nader]

@SubhaShroff this has been sent to the DevOps CM Team to be deployed to TEST under SCTASK0402207

[SubhaShroff]

@Nadin-Nader The above DEV deployment I tested but batch job is throwing overflow error - "PL/SQL: numeric or value error: character string buffer too small ORA-06512:" As we discussed today (3/7/2024) about this issue, we can only test the expiry date condition commented after fixing the above issue addressed.

I checked with the code if it will do with permit number format(2024-021365 EX) only, it still overflows.. I tried only for RSNs listing, that too fails for these Right of Contractors permits with the given number of active permits(EX, DS) they have. So, we only can make it work by limiting the display of these permits in the email, let be RSNs alone. Below data are on PROD only a few for example - contractors with active EX, DS

NOTE: This is working fine as of now since its not hitting all the LCs, especially with those having large number of permits.

Thanks.

[TracyLinder]

Solution is to have 2 notification templates, one that includes a list of permits and one that contains a link to the portal instead.

does the character limit error message happen? Y - Send email with link to portal we need email content for this N - Send email with the permits listed

Emails that need to be created:

1. Expiration Notification with portal link
2. Renewal Notification with portal link (renew process on LC)

Next Steps:

1. Turn the batch job(s) off.
2. Creating email content
3. Subha create the new emails
4. Subha update the batch job with email logic based on the character limit

[TracyLinder]

@Vaishnav1397 can you please work with Kyle to create email templates that point the user to the portal to find their permits. Please also create AC for the fix.

[vschandramouli]

Moving this back to Needs Scoping to work with Kyle.

[TracyLinder]

@vschandramouli will add the AC from #15763 to this card.

[Nadin-Nader]

Incident to Pause Batch Jobs on PROD INC0374203

[TracyLinder]

We will need to document the use cases that require UAT.

1. Folder expired because it expired
2. Folder expired b/c insurance expired
3. Folder expired b/c bond expired

[TracyLinder]

Note

The causes of this bug were identified as

1. character limit issues with the automated emails.
2. A 24 hour limit in the query where clause

Resolutions:

1. Create new email templates for the scenario where the list gets too long to prevent the character limit issue
2. Remove the 24 hour limit in the query

Next Steps:

1. Kyle & Vish to work on email templates
2. Vish update AC
3. Development

[vschandramouli]

Notes from scoping meeting with Kyle and Raj: 4/2/24

• Raj recommends showing a set number of expiring permits (say 5 to 10), and still adding a link to the portal so they can view the full list.
• Subha and Raj can figure out what the threshold is before we start seeing character limit errors, and we can confirm with ROW teams to set it.
• Vish will add AC from #15763 as part of this card.

Expiration email template

Subject: ROW License and Permit Expiration Notice

Dear ROW License Holder,

This email is to advise you that your Right of Way Contractor License [2022-12345678 LC] expired on [LC folder Expiry date].

All Driveway/Sidewalk and Excavation permits associated with your Right of Way Contractor License have expired on [LC folder Expiry date] until the license is renewed. To renew your Right of Way Contractor License, look for the "How is a Right of Way Contractor License renewed?" Section at this URL - https://austintexas.gov/page/right-way-contractors

The following list includes the DS/EX Permits that have expired. To view a full list of expired permits, please visit add link to the AB+C Portal here.

[Folder Number of DS or EX folder, for example, 2022-012345 DS] - [Folder Name]
    2023-12345678-DS - New Driveway & Sidewalk

To reactivate your license, please view the documentation requirements on the Right of Way Contractor webpage {https://www.austintexas.gov/page/right-way-contractors}, and then submit your updated documentation to our SmartSheet {https://app.smartsheet.com/b/form/cad1e53c88224399979f40df80ef6270}.

Re-activation email template

Subject: ROW License and Permit Renewal Notice

Dear ROW License Holder,

This email is to advise you that your Right of Way Contractor License [2022-12345678 LC] has been reactivated.

All Driveway/Sidewalk and Excavation permits associated with your Right of Way Contractor License have been reactivated. Please note that any permits that are past their expiry date will not be reactivated.

The following list includes the DS/EX Permits that have been Reactivated. To view a full list of reactivated permits, please visit add link to the AB+C Portal here.

[Folder Number of DS or EX folder, for example, 2022-012345 DS] - [Folder Name]
    Example: 2023-12345678 DS - New Driveway & Sidewalk

[vschandramouli]

Updated URL @rajeshlaunch @Nadin-Nader @SubhaShroff

Card should be ready for development

[Nadin-Nader]

This has been sent to the DEVOps CM Team to be deployed to DEV under SCTASK0412009

[Nadin-Nader]

@SubhaShroff this has been sent to the DevOps CM Team to be deployed in TEST under SCTASK0413275

[SubhaShroff]

@Nadin-Nader SCTASK0413275 is closed but looks like one of the commits is not deployed in TEST - fd0f598f26274f006dd96d7886b2807f1dc89b25. Messaged Monty about it. Will retest it once its deployed properly. Thank you.

[SubhaShroff]

@Nadin-Nader I tested this after Monty fixed it.
Right-of-Way Contractor - 5747087 LC RSN - 12623304 The expiration batch email is attached. This particular Right of Contractor had 32 EX/DS active permits. The email listed those permits only as much as it could accommodate. All those active permits' status got updated to Expired-License.

If the LC doesn't have any associated DS/EX active. For that particular LC, email is sent as follows displaying N/A in place of the permits list.

Tested Renew Process.

Received Renew email. Its email content is not formatted but its displaying as one paragraph. I am checking whats wrong with it.

[SubhaShroff]

@Nadin-Nader Please create a DEV deployment request. Thank you. Fixed the renewal email content and the commit -8bd726c7875679d3bc8069036545729356bfe717 is sent for DEV deployment. 16106 - 8bd726c7875679d3bc8069036545729356bfe717 - DEV Deployment Plan.docx

[Nadin-Nader]

This has been sent to the DevOps CM Team to be deployed in DEV SCTASK0415921

[Nadin-Nader]

This has been sent to the DevOps CM Team to be deployed to TEST under SCTASK0416157

[SubhaShroff]

@Nadin-Nader This is ready for UAT on TEST. Thank you.

[SubhaShroff]

@Nadin-Nader I move it to In Progress as I noticed this morning, the batch job looks sending multiple emails for the same LC. I will check this and update the ticket.

[SubhaShroff]

@Nadin-Nader @vschandramouli @KyleErich @TracyLinder After testing this ticket on TEST there are few points I noted that seek your view:

1. Turning the Batch job on is triggering it for all the previous days that it was not on.

Suggested Solution: To avoid it, on test environment we can run Batch job manually, which will fire only once. On PROD, I think we will delete the existing Batch and add it again as fresh.

2. The Batch job (Update DS/EX with expired LC), would send email to the Right of Way Contractor daily until the LC is Activated or atleast the status is updated to something that is not EXPIRED.

This is the reason Tracy had suggested to add an expiry date check and do the update/send emails to only those LC licenses that expired previous day. That should restrict to send the emails everyday. But This ticket has some examples that would be an exception for the date check.

• One, LC which doesn't have an expiry date on it but expired- If the expiry date check is added, these LCs are never addressed by the batch job.
• Two, the old expired LCs with past exipry date (other than previous day).
• If we still consider only the previous day expired LCs, to address the old LCs we can run a script, for once. That way this date check solution can be made workable and not send repeated emails to ROW Contractors for the expired LC until it is renewed.

But, there will be a question about the LCs that are expiring without expiry date on it, after the script is executed. I think, adding expiry date to the LC along with the expired status update would solve that too. (Need to know when it expires but not update the expiry date).

Or Run the batch weekly once to reduce the email frequency. This will also, update the DS/EX permits weekly once, to Expired-License.

Please let me know your thoughts/solutions that works. Thank you.

[KyleErich]

1. I agree with the suggested solution.
2. I'd like to avoid the emails going out daily or even weekly. To me that will overburden our customers and result in consternation/complaints. In regards to the scenario where the folder is Expired but there's no Expiry Date, I don't think that is a common scenario. Any LC that has been issued will have an expiry date -- presumably most of the ones without an expiry date were never issued in the first place and whose last status before Expired was Pending. It's okay to me if these cases don't receive an email.

We can discuss in greater detail during the Friday afternoon meeting.

[TracyLinder]

DTS-AMANDA Refinement Conversation 5/7/24

Outcome: We suggest to fix the insurance expiration date issue and add a 'expired yesterday' clause to the batch job.

Problem 1: The batch job is emailing LC Right-of-Way Contractor every day

• Solution would be insert a folder comment to record the notification email has been sent. The batch would be updated so as not to send a notification email for those that have already gotten an email.
• This introduces a new problem of the batch job only working one time; the 1st time. So the folder comment would need to be deleted after the LC is reactivated/renewed.

Problem 2: When insurance expires the LC, the expiration date remains in the future so the batch does not pick it up.

• Fixing this would resolve the 1st problem.
• Solution would be to update the expiration procedure to input today's date as the expiration date.
• We'd need to be sure the expiration date gets fixed when the folder is reactivated.
• Be sure the Expired-License batch job only picks up licenses with yesterday's expiration date.
• Explore feature where the system inserts a folder comment when the insurance expiration procedure updates the folder expiration date.

[vschandramouli]

Notes from meeting with Subha and Raj

• Raj mentions there is no fall back if this batch fails one day. Since batch checks only 24 hours, if the batch fails one day then it wont get caught in the future
• Subha mentioned it may be helpful to split solution for problem 1 and problem 2 into 2 separate tickets since they are two different batch jobs.
• Raj mentioned potentially using a Read-only folder info with radio buttons (like Expired email sent? Yes/No). Read only means users will not be able to edit this. When system sends email, update folder info to Yes. When folder is activated (which status is considered activated? Just active? Something else?), update folder info to No.
• This info may also serve as a record of emails sent to the applicant.
• We will need to add the info first, re-default all folders and then add/run the batch.
• Vish will add this to Friday meeting agenda.

[vschandramouli]

Meeting with Kim, Paloma, Kyle and team -

• It would be okay to extend the yesterday clause to 48 hours, it would be okay to send 2 emails to ROW Contractor.
• This will reduce complexity of adding info field.
• We will proceed with this approach. Vish will update AC.

• 16003 will need to be handled separately.

[vschandramouli]

Vish will create a separate card for insurance work (problem 2) and scope it out with ROW team. This may be related to #16314

[TracyLinder]

Update - On Hold

• We have decided to update the insurance expiration scenario prior to updating the batch job with the 48 hour clause.

[SubhaShroff]

@Nadin-Nader Please create a DEV deployment request. Thank you. 16106 - 56985c629df0348434b63f75c8bdee7b3ec069a7 - DEV Deployment Plan.docx

[Nadin-Nader]

This has been sent to the DevOps CM Team to be deployed to DEV under SCTASK0439363

[Nadin-Nader]

Status: Waiting on TEST UAT for #16415. If #16415 is approved on TEST, this one can progress

[Nadin-Nader]

16415 has passed UAT on TEST and is going to PROD. This has been sent to the CM DevOps Team for deployment to TEST under SCTASK0441813

[SubhaShroff]

Set the data to TEST: PeopleRSN-5597614 LC RSN-11766283

13150693 DS 2024-10-26 50010 13193141 EX 2024-07-26 50010 12819803 DS 2025-01-22 50010

The following batches will trigger tonight after midnight. 196 - Expire EX DS with LC 179 - Expire EX Folders 200 - Expire DS with Expire License Status

Will verify the data on Monday.

[Nadin-Nader]

The new commit has been sent to the DevOps CM Team to be deployed to DEV under SCTASK0443101

[Nadin-Nader]

@SubhaShroff This has been deployed to DEV please UAT

[SubhaShroff]

DEV Batches turned on : 198 - Expire EX DS with LC 204 - Expire DS with Expire License Status

[SubhaShroff]

@Nadin-Nader @TracyLinder Expire EX folders batch job(already existing not part of 10849 ticket). This one expires EX permits with expirydate of the previous day. So, old EX permits if there are any with past expiry date other than previous day. Should I remove the previous day expiry date condition for this Batch Job?

NOTE: And also this batch check not only for 'Expired-License' status of EX, but also other status which are NOT any of the following status - XXXX Expired, Final, Canceled, Closed, Void, Aborted XXXX

[Nadin-Nader]

Another DEV commit has been sent to the DevOps CM TEAM to be deployed to DEV under SCTASK0444016

[Nadin-Nader]

@SubhaShroff this has still not been deployed as of 10:07am 8/2/24

[Nadin-Nader]

@SubhaShroff This has been deployed to DEV please UAT

[SubhaShroff]

@Nadin-Nader Thank you. Setting the data to test the following batch jobs: 198 - Expire EX DS with LC 204 - Expire DS with Expire License Status 154 - Expire EX folders

Ppl LCrsn status expirydate LC number email sent count 5723093 12482138 50030 2024-07-31-- 20 082501 LC 2nd Aug(2), 5418121 11113820 50030 2024-08-03 --14 031668 LC 4th Aug(2), 5th Aug(2) 5810048 13077576 50030 2024-08-01 -- 23 003966 LC 2nd Aug(2), 3rd Aug(2) 5815138 13106720 50030 2024-08-02 -- 23 029356 LC 3rd Aug(2), 4th Aug(2)

There are many EX/DS. Few are listed to verify on Tuesday(8/6/2024). 11109909 DS 12616443 EX 12596371 EX 13181667 EX 13166805 EX 13193483 EX 13187863 EX 11109909 DS 13181843 DS 13112204 DS 12886853 DS 11109909 DS 13181843 DS 13112204 DS 12886853 DS 11109909 DS

DEV test results:

1. Email is sent twice to the same ROW contractor in a day with batch run.
2. DS expiring with batch doesn't look like working. Still the DS with 'Expired-License are not updating to 'Expired' status.
3. Since, we are checking the LC expired in last two days(48 hours), first day batch run will send email with the list of DS/EX that will be updated to expire. Next run will not list the DS/EX in the email as those were updated to 'Expired-License' with the previous day batch run.

Working on these points.

[Nadin-Nader]

This has been sent to the DevOps CM Team to be deployed on DEV under SCTASK0445323

[Nadin-Nader]

This has been deployed to DEV. Subha is checking on it but also has to discuss the email portion of it @ the 2pm meeting with ROW

[Nadin-Nader]

This has been sent to the DevOps CM Team to be deployed to DEV underSCTASK0447019

[Nadin-Nader]

This has been sent to the DevOps CM Team to be deployed to TEST under SCTASK0448365

[SubhaShroff]

@Nadin-Nader I am testing this on TEST. Tested day1, today. Will need to test it on day 2 as well, checking the emails tomorrow.

[SubhaShroff]

@Nadin-Nader Since DS expire batch was not working I couldn't test it completely on DEV. Its tested on TEST. 2nd day email is not good enough. And did another commit for the fix. - SCTASK0450580