NAMES/NAMEX expiry date different from Societies Online /Aug 16 /NAMEX /NR 4643704 related to ops - #2991

[ozamani9gh]

Joshua would like us to investigate this EXPIRY DATE difference between two systems issue:

from joshua:
Is there any way to confirm that this isn't a broader issue between NAMEX and REGI? I understand it can be difficult to know without more examples, unfortunately I just have the one.

Patrick Heath's suggestion:
DEVs - could this be another UTC time storage issue?

More details reference to https://github.com/bcgov-registries/ops-support/issues/2991

[Rajandeep98]

Private Zenhub Image
Private Zenhub Image
Private Zenhub Image

[Uploading... image.png]

[Rajandeep98]

Private Zenhub Image

[Rajandeep98]

no code changes needed in this case as the conversion of timings/dates is valid, pending to validate if this request can be consumed till 11:59pm or if it expires earlier.

[Rajandeep98]

changed expiration date to 26 sept , oracle db saves as 27 6:59. tested at 12am 27 still could consume the request, tested at 7:30am 27 sept i still can consume the request

[iamjtodd]

We've encountered another instance where a Society NR is showing an expiry date of "Sept 26" in NAMEX yet Societies Online is stating "end of day Sept. 27".
Private Zenhub Image
Private Zenhub Image

(Citizen states they were attempting to use NR on the 27th as indicated by REGI)

[eve-git]

@droberts27

Clients have raised a concern about the NR expiration time – it has nearly 1 day differences between Name Request and Legacy system. More details please see #17475
Although the expiration date are the same in both systems, the time is different. It is the last minutes of the day (11:59pm) in Postgres but the first minute of the day (00:00) in Oracle.

NameRequest nro_update job passes an expiration date as yyyyMMdd to NRO_DATAPUMP_PKG.name_examination_func. I think there are two solutions: either we pass expiration date including time info or the set the time to 11:59pm in name_examination_func. Could you please take a look?

[Rajandeep98]

omid is working with home team to confirm the work to be completed on their side.

[eve-git]

@ozamani9gh Here is the email from David Roberts:

Should we wait for Omid’s response?

Should someone create a ServiceNow change request?

Eve – Thanks for providing your two options.  I will implement the second one.  When NRO_DATAPUMP_PKG.name_examination_func inserts to request_instance I will modify it as follows:

          INSERT INTO request_instance(request_instance_id,
                                     request_id,
                                     priority_cd,
                                     request_type_cd,
                                     expiration_date,

            VALUES(request_instance_seq.nextval,
                   l_request_id,
                   ri_rec.priority_cd,
                   ri_rec.request_type_cd,
                   to_date(to_char(l_expiry_date, 'yyyymmdd')||' 23:59','yyyymmdd hh24:mi'),

[ozamani9gh]

@Rajandeep98 @eve-git Need to confirm with home team when this work has been implemented, so we can verify its working

[PatrickAHeath]

@ozamani9gh @Rajandeep98 @droberts27 Is there an ETA for implementation of this change? Clients are still experiencing the issue.

[droberts27]

Do I have approval to also do a datafix so that the existing data is synchronized? It would be just for Society name requests and would update 466 rows in NAMESP.

update request_instance set expiration_date=to_date(to_char(expiration_date,'yyyymmdd')||' 23:59','yyyymmdd hh24:mi') where trunc(expiration_date) >= trunc(sysdate) and request_type_cd='SO';

[droberts27]

I have applied the following in NAMESD and NAMEST NRO_DATAPUMP_PKG.name_examination_func so that Society name request expiration dates will be the same day but with hours and minutes set to 23:59

if (ri_rec.request_type_cd = 'SO') then
l_expiry_date := to_date(to_char(l_expiry_date,'yyyymmdd')||' 23:59','yyyymmdd hh24:mi');
end if;

[droberts27]

This is also ServiceNow CHG0031285 which was assigned to me. I will assign it to the Home Team BAs.

[droberts27]

I will also apply the same code change to NRO_DATAPUMP_PKG.update_request_state

Instead of only updating request type SO (Society - Incorporation) I have decided to update all the society and extra pro societies request types:

ASO Society - Amalgamation
CSO Society - Change of Name
CSSO Society - Conversion of a Special Act
CTSO Society - Continuation In
RSO Society - Restore
SO Society - Incorporation
XASO Society - Extraprovincial Non-Share Corporation Assumed Name
XCASO Society - Extraprovincial Non-Share Corporation Change Assumed Name
XCSO Society - Extraprovincial Change of Name
XRSO Society - Extraprovincial Restoration
XSO Society - Extraprovincial

[droberts27]

Datafix and code changes have been done in CDEV and CTST. Ready for testing. I have assigned ServiceNow CHG0031285 to Neeraja for testing.

[droberts27]

When Neeraja examined an NR in TEST the expiry date was set correctly. My code change caused the hours:minutes to be set to 23:59.

When Neeraja changed an existing NR expiry date to a new expiry date it was not correct. The date in the postgres namex database was copied to the Oracle namesp database without any date conversion to local time. This results in the expiry date being one day ahead. See columns "NAMES Postgres no conversion" and "NAMES Oracle" in the attached spreadsheet.

A modernization developer will need to change the code that updates existing expiry dates as follows:
select expiration_date at time zone 'America/Vancouver' from requests

[droberts27]

summary_temp.xlsx

[eve-git]

#18524 is created for the issue when updating expiration date.

[ozamani9gh]

This issue can now be closed, and the fix will be worked on Ticket #18524