NAMES /Expired NR showing as active in NAMEX /Nov 14 /NAMEX /NR 9775128 related to OPS 3297

[ozamani9gh]

NAMES /Expired NR showing as active in NAMEX /Nov 14 /NAMEX /NR 9775128 - related to ops ticket

https://app.zenhub.com/workspaces/ops-60f8556e05d25b0011468870/issues/gh/bcgov-registries/ops-support/3297

Contact information

Staff Name: Josh Thiessen
Staff Email: joshua.thiessen@gov.bc.ca
Team Lead: Jeff Zukowski
NameX issue

NR 9775128 had a set expiry date of Nov. 13, however it is still showing as "conditionally approved" in NAMEX.

Private Zenhub Image

[eve-git]

The expiration_date stored in the database reflects either 7:59 am or 6:59 am UTC, equivalent to 23:59 Pacific Time on the previous day. However, when querying to identify expiring NRs on that day, the expiration_date is compared to CURRENT_DATE, which corresponds to 0:00 UTC. Consequently, NRs that expire on the day may not have their status updated on time, as the expiration_date's time (7:59) exceeds 0:00. To resolve this, we can address the issue by simply cast expiration_date to a DATE and with 0:00 time in the query.

The existing query is as
https://github.com/bcgov/namex/blob/main/jobs/nr-day-job/nr_day_job.py#L114

select * from requests
where expiration_date <= CURRENT_DATE and 
state_cd in ('APPROVED', 'CONDITIONAL') and 
notified_before_expiry == 'true' and
notified_expiry == 'false'

Do we also consider the CURRENT_DATE in the query for notify_nr_before_expiry?
https://github.com/bcgov/namex/blob/main/jobs/nr-day-job/nr_day_job.py#L96

[eve-git]

Test to make sure the NR expiration_date on the day, and the state of the NR will be updated to 'EXPIRED' on next day instead of the the day after next day.

Also, please test if the notification before NR expiry (expiry within 14 days, and the notification hasn't been sent before).

In order to create testing data, you can modify the expiration_date after an NR approved.

[eve-git]

deployed to TEST environment. Ready to test.

[oanyahuru]

Comments related to this ticket have been updated on #18301

[eve-git]

deployed to PROD.