Solution for oracle error when updating CAME profile

Summary

Client received an oracle error when attempting to update the telephone number in the eMER section of an existing CAME profile.

On checking the CAME’s record, development noticed that the CAME’s STAMP was marked returned in 1995.

On further verification of the CAME’s account using the scripts in the developer trouble shooting document
Account Access: CAME cannot access/login to eMER (developer troubleshooting), I saw that the related records for the account no longer existed in the CAMIS and TWOFA databases. Therefore, the CAME did not have a valid account that they could log into.

The following tables (at a minimum) were missing related records.

CAMIS
AA008_APPLICATION_USER
AA011_EXTERNAL_ACCESS

TWOFA
AC040_STAKEHOLDER
AC044_EXTERNAL_STAKEHOLDER

The records that are missing from the db are normally created when a CAME profile is created.
The records could be recreated by creating a database script but it could be error prone, if anything was missed.
I advised the client to create a brand new profile in CAMIS, for the the CAME. Since, a new STAMP number was generated for that profile:
- performed database updates to re-assign the CAME’s existing stamp number to the new profile so that it would correspond with the medical examinations previously performed by the CAME.
- asked the client to have the CAME create a new GC Key and to activate it using the activation key in the new profile.
- The old profile currently is updated to show the CAME’s lastname - previous in the drop-down list.

Refer to the “Problem” and “Solution” sections for more details.

Problem:

The bug that was recently reported was

259139 CAME T391 Dr. Sclater eMER phone number set up server error message

Business client was receiving an oracle error when attempting to update the CAME’s number in the eMER section of the profile. A generic screenshot of the section and field in question is shown below:

On pressing the Add/Update button, the oracle error shown below occurred:

Solution:

If the developer trouble shooting document
Account Access: CAME cannot access/login to eMER (developer troubleshooting),
does not return results, but a record for the STAMP number exists in UA045_STAMP, most likely the related records for the CAME’s account were removed.

Check if the STAMP if the date (STAMP_RETURN_DTE) in UA045_STAMP is in the past.

For example, in addition to having run the script in the developer trouble shooting document noted at the beginning of the solution section

CAMIS - query the following tables on PERSON_ID (MEDICAL_EXAMINER_PERSON_ID from UA045 is the same as PERSON_ID)
AA008_APPLICATION_USER
AA011_EXTERNAL_ACCESS

TWOFA
AC040_STAKEHOLDER (look for the last name (NAME_LAST_NM) for STAKEHOLDER_TYPE_CD = 2). Take note of the STAKEHOLDER_ID

AC044_EXTERNAL_STAKEHOLDER - query this table based on the corresponding STAKEHOLDER_ID, if one exist, found in AC040_STAKEHOLDER

Since no corresponding records were found in CAMIS tables AA008, AA011 or TWOFA tables AC040 and AC044, for the bug I was working on I proceeded as follows:

In order to ensure that the profile was correctly recreated, I recommended that the business client create a new profile for Dr. Sclater and add a comment to the new account to indicate the reason for the new account.

Important Creating a new profile also generates a new STAMP number for the profile.

In order to retain the CAME's original stamp number I:

Made backups of the following tables in production related to Dr. Sclater's existing stamp T391:

UA045_STAMP
select * from UA045_STAMP where STAMP_NUMBER_TXT = 'T391';
UA006_MEDICAL_EXAMINATION

select * from UA006_MEDICAL_EXAMINATION where STAMP_NUMBER_TXT = 'T391';

Temporarily updated the stamp number in T391 in UA006_MEDICAL_EXAMINATION to "2251"
(without the T).

update UA006_MEDICAL_EXAMINATION
set STAMP_NUMBER_TXT = 2251
where STAMP_NUMBER_TXT = 'T391'

Updated the existing record in UA045_STAMP from T391 to T391r

Note, May 30, 2023:
the column STAMP_NUMBER_TXT is defined as varchar so I was able to update it to T391r.

This subsequently caused an error when attempting to add an address , second stamp, etc., to an existing CAME profile (see bug 265360 Level 2 access to CAMIS)

I renamed the stamp number for “Dr. Sclater - previous to “T3910”

I also checked the next stamp number that will be generated using the query

select NEXT_STAMP_NBR from V_NEXT_AVAILABLE_STAMP_NBR and there should not be a conflict.

update UA045_STAMP
set STAMP_NUMBER_TXT = 'T3910'
where STAMP_NUMBER_TXT = 'T3910';

Verified the changes in the tables; then COMMITED them.

Performed the following updates to assign the existing STAMP number to the new profile:

Updated the STAMP number for the new CAME PROFILE:

Updated the STAMP number for the Medical Examinations that Dr. Sclater previously performed from ‘2251' back to T391.

Note: the "DATE_LAST_UPDATE_DTE" for those existing records now shows as May 4, 2023 due to the temporary update of the stamp number.

Verified the CAME's account was correctly created using the developer process documented in Confluence:
Account Access: CAME cannot access/login to eMER (developer troubleshooting)

Verified the changes in the tables; then COMMITED them.

Have temporarily updated the last name for Dr. Sclater's previous profile to show Sclater-previous in the
CAME dropdown (CAMIS)

I will look into removing the previous CAME profile from the dropdown list.

The client reached out to the CAME.