CAMIS - SIMAC

PreAcc: http://catcpreaccpx.tc.gc.ca/Saf-Sec-Sur/2/camis-simac
Acceptance: https://etpacc.tc.gc.ca/camis_simac/
Production: https://etp.tc.gc.ca/camis-simac/

User Authentication in CAMIS is done through Entrust TruePass. Once the credentials for TruePass are entered, the application extracts some data from the request header passed from Entrust TruePass to CAMIS. Currently, CAMIS gets the users full name (not the user name) from the request header and through a select query, finds the user. If the user is found in the CAMIS database, the authentication process is complete.

More details:
LoginCheck method, which is a member of class clsCAMISUI, is called:

The following 2 lines get the user full name (last name , first name) from Entrust TruePass, passing to CAMIS through the request header:

entrust_client_id = Request.Headers.Get("Entrust-Client")
entrust_client_id = encoding.GetString(System.Convert.FromBase64String(entrust_client_id))

And then through the following query, CAMIS finds the user (please note entrust_client_id is used in the where clause of the query):

SELECT TC_USER_ID, PERSON_ID, FIRST_NAME_NM, LAST_NAME_NM, REGION_ETXT, REGION_FTXT EMAIL_TXT, ENTRUST_ID_TXT, REGION_CD, DESIGNATOR_TXT, USER_STATUS_CD, ROLE_CODES_E, ROLES_ETXT, ROLE_CODES_F, ROLES_FTXT 
from V_USER 
where USER_STATUS_CD = 1 
  and DATE_DELETED_DTE is null 
  and upper(ENTRUST_ID_TXT)='" & UCase(entrust_client_id) & "'"
;

If the above query returns a record, the user is found.

Future Improvement for CAMIS authentication process:
It is a more reliable approach to use username instead of actual user name. We have both username and actual user name (last name, first name) in CAMIS database, so instead of getting actual user name from Truepass request header, we can get username (header request is a key/value collection contaning information passed from Truepass to the application) and then modify above query (where clause) to get the user record in CAMIS database based on his/her username not the actual name, this way we can avoid the situations in which we enter the user’s full name with typo issue when creating a user in CAMIS, so even the user’s names has some typo, the application can still authenticate the user since it is based on the username not the user’s full name.

On eMER, the page is showing that all draft eMERs will be expired after 30 days

However, this info is not correct because the draft eMERs will not be expired until 400 days

This value is set inside the table WA007_SETTING

The following script is used to fix an ongoing issue with the document naming scheme. The issue is that eMER adds the date of submission in the file name, but CAMIS doesn’t expect it.

eMER does: {APPLICANT_NAME}-{SUBMISSION_DATE}-mer.[html|pdf]

CAMIS expects: {APPLICANT_NAME}--mer.html

Script to run:

UPDATE UA018_DOCUMENT SET FILENAME_TXT=REGEXP_REPLACE(FILENAME_TXT,'-mer.html$', '--mer.html') WHERE REGEXP_LIKE(FILENAME_TXT,'[^-]-mer.html$')

User must be added into 2FA database before they are able to give eMER access to CAMEs
Create 2FA admin account for CAMIS

To log in to eMER, the user must have a valid cell-phone number in their CAMIS profile in order to receive the 2FA SMS code.

1. Log in to CAMIS and go to ADMIN → CAME Admin.

2. In the Select Existing CAME dropdown, find the user (sorted by last name).

3. Scroll down to the eMER section of the page and verify that profile is active & the number in the eMER Cell Phone # field is composed of only digits (no spaces even at the end, no special characters).

4. If there are no issues with the cell #, check the 2FA database logs for any errors.

   • TWOFA_USER@TTSXP18

5. Check the Twilio logs.
   Note: the following can provide access to Twilio: Michelle Le, Michelle Coelho, Alain Asgill, Peter Khouri.

   • Log in to Twilio.
     Note: After receiving access to Twilio, when you login for the first time you will need to activate
     your access to the TC Account from within Twilio. Look at the drop down above the Monitor tab
     
     Example:
     
     

     

     
     
     From the TC Account in Twilio:

   • Click on Monitor in the left-hand menu.

   • Go to Logs → Messaging.

   • Filter the logs by the cell # from step 3

     

6. If we can not find any error in above steps, and even if the eMer status is changed from Inactive to active and CAM still does not get the 2FA SMS code:

Email to a CAMIS administrator to keep a copy of original number, modify that phone number in CAMIS to the different one (fake ones), with the “active” eMER status, click update, then put back original phone number, click update. CAMIS administrator then will need to generate a new activation code. CAM will sign in eMER using the new activation code and he/she would receive 2FA SMS codes after that.

Note: The assignor is able to delete any tasks they created.

Tasks are saved in UA047_TASK table. You can filter by TASK_TYPE_CD (refer to TA082_TASK_TYPE for the code) and TASK_DESCRIPTION_TXT (LIKE '%file_no%'). Once you find the correct task, set the DATE_DELETED_DTE to SYSDATE.

Example: Open CAMIS, enter the file no, get to the specific MER record and “View eMER record”, the “Submit MER Confirmation” will show the Applicant Details' name is different from Applicant (on top of the page). On the same page, go to the “Documents” section, you must see the wronglastname-wrongfirstname--mer.html and wronglastname-wrongfirstname--mer.pdf.

How this issue can happen:

CAME (doctor) creates a new applicant on EMER application with the right name of applicant

He enters medical data, however at Applicant Details section, he enters the wrong given name and wrong last name. He has to view, click the check box before click “Submit MER”. However, he might forget to review the names here

After summitting MER, the file has been submitted to CAM (CAMIS client) and there is a wronglastname-wrongfirstname--mer.html which contains all medical data is saved in the UA018_document table with a document_nbr

On CAMIS application, CAM is now opening the file, checking the “incoming eMER' and assigning the file to a specific CAM . At this point, I do not know yet that specific CAM has done something on the file (he might confirm it), then there is wronglastname-wrongfirstname--mer.pdf saved in the UA018_document table with the next document_nbr

Database Notes

We can find licence_nbr and medical_examination_id in UA006_medical_examination

WE can find html and pdf file under UA018_document

Here is the SQL we can use to check if the person has the right names in the medical files. These info will be shown on the file names

This below SQL is from PK_EMER_MEDICAL_EXAMINATION.SP_get_mer_step6

SELECT ua018_document.document_nbr,
  filename_txt,
  document_description_txt,
  document_date_dte,
  document_upload_dte,
  document_status_cd
FROM ua018_document, ua035_medical_examination_doc
WHERE ua018_document.document_nbr = ua035_medical_examination_doc.document_nbr
  AND medical_examination_id = p_medical_examination_id 
  AND (document_status_cd IN ('1', '3', '4', '5', '6'))
  AND ua018_document.date_deleted_dte IS NULL;

Fix: Using database within CAMISP.WORLD,

1. Update the first name and last name in database for this MER record

   • UA006_MEDICAL_EXAMINATION: Filter the record using medical_examination_id

2. Update the record with the new HTML file

   • UA018_DOCUMENT: Filter the record using the specific document_nbr, at the File_Content_Txt column, right click on the field "HugeBlob", choose "Pop Up Editor", save the file with LastName-Firstname--mer.html at your local, open that HTML by Notepad, changing the names at the Applicant Details, save it. Now at “Pop Up Editor” of the same record, we load the changed html file, change the path_name_nm, FileName_txt, Document_Description_txt as LastName-FirstName--mer.html. In order to have the Load option enable on “Pop Up Editor”, we should use the Schema->UA018_document and filter the document_nbr to Load a file rather than using the ‘Select’ statement.

3. Update the record with the new PDF file

   • Double click on the HTML file at your local (at step 3) to open it in Google Chrome or Microsoft Edge, print to a pdf file with LastName-Firstname--mer.pdf, remember remove the option to print “Headers and Footers”

   • On UA018_document, filter the record by using the specific document_nbr, at the File_Content_Txt column, right click on the field "HugeBlob", choose "Pop Up Editor", we load the pdf file, change the path_name_nm, FileName_txt, Document_Description_txt as LastName-FirstName--mer.pdf

4. Test by opening CAMIS, go to the same file number, click on View MER record, check whether applicant detail’s name is correct and the documents are now shown with the rightlastname-rightfirstname--mer.html and rightlastname-rightfirstname--mer.pdf

The clients cannot modify eMer on Camis because submitted date of the eMER is before the medical examination date. This happens when CAM chooses the examination date in the future

Fix: Find the medical_examination_id from the client’s information, on UA006_MEDICAL_EXAMINATION, change the medical_examination_dte is before or equal date_emer_submitted_dte

Required info: The file no. that was incorrectly assigned and the correct file no., applicant id that the eMER should be assigned to.

There were two problems:
1) the tombstone data (name, dob was incorrect in UA006_MEDICAL_EXAMINATION.

2) two documents for ROBERT GARTSHORE were accidentally attached to the eMER for GEOFFREY LAWRENCE and had to be transferred to the correct eMER for ROBERT GARTSHORE.

3. Ran the scripts in https://dev.azure.com/transport-canada/DSD-CIVAV%20Support/_boards/board/t/Team%20Skyblazers/Backlog%20items/?workitem=183846 to resolve this issue.

This is slightly different from other scenarios and have logged bug 184054 Team Skyblazers Backlog items Board - Boards (azure.com) to address this

Verify in the application if the changes were successful, you can search the correct file no. and look in the Tests & Docs. and MERs tabs to verify if the files are there.

1. In CAMIS (prod):
   Select “Applicant search” from “Applicant” in the left hand menu:

   

   Enter the file number in the resulting form:

   

   
   To verify that the Documents are transferred correctly it is necessary to view the actual eMER and view the files listed in the documents section of the eMER.
   Set the task to “Committed” and ask the requester to review that the new file has the correct information

   Example of a file transfer task:
   181510 Request to transfer an eMER dated 2021-06-02 to correct file #.

Required info: The name of the applicant and stamp number of the examiner (or exam date).

Within CAMISP.WORLD, in the table CAMIS.UA006_MEDICAL_EXAMINATION, locate the medical examination that needs to be deleted, usually by a combination of the applicant name and the stamp number of the examiner or the medical examination date. When located, set the DATE_DELETED_DTE to today’s date.

1. Find the MEDICAL_EXAMINATION_ID of the files to be removed.
   * Can also use license_num or first_name_nm instead of family_name_nm or any other identifying information to find the record (e.g. dob) *
   SELECT * FROM UA006_MEDICAL_EXAMINATION WHERE STAMP_NUMBER_TXT = '{stamp_no}' AND UPPER(FAMILY_NAME_NM) = UPPER('{applicant_last_name}');

2. Export the INSERT statements for the rows to be deleted and put into the the comments of the bug

3. Set the DATE_DELETED_DTE to today’s date
   UPDATE UA006_MEDICAL_EXAMINATION SET DATE_DELETED_DTE = SYSDATE WHERE MEDICAL_EXAMINATION_ID IN ({list of IDs to be removed});

Required info: The license number of the first applicant and of the second.

Make sure to first check that both license numbers exist in CAMIS. If not, you will need to wait for it to get populated in DAPLS.

The following script merges an applicant into another one (MERs, related activities, assessments, docs, etc.).

Create a related task and include the script you ran.

Verify in the application if the changes were successful, you can search the correct file no. and look in the Tests & Docs. and MERs tabs to verify if the files are there.

In CAMIS (prod):
Select “Applicant search” from “Applicant” in the left hand menu:

Enter the file number in the resulting form:

On CAMIS, entering a file number, at Tombstone tab, click on “Add/Update Indicators”, if there are missing indicators:

Check YA003_APPLICANT of that applicant, at date_deleted_dte to see whether the field is empty or not. If that field can be empty, then the missing indicators will appear

When we have a task to delete address associated with stamp number

(see bug # 244024 Delete address associated with stamp number)

Within CAMISP.WORLD, in the table CAMIS.UA045_STAMP, locate the stamp number that needs to be deleted, usually locate by the stamp number. When located, set the DATE_DELETED_DTE to today’s date.

In the table CAMIS.UA031_MEDICAL_EXAMINER_OFFICE, locate the medical examination that needs to be deleted, usually by a combination of the medical examiner person id and the medical office address id of the examiner. When located, set the DATE_DELETED_DTE to today’s date.

-- Get the stamp number from requested ticket:
Select * from UA045_STAMP where STAMP_NUMBER_TXT = <stamp_number_txt>; -- replace <stamp_number_txt> with requested stamp number from ticket

--Script to update the DATE_DELETED_DTE with the today's date and time in the following table.
Update UA045_STAMP
Set DATE_DELETED_DTE = SYSDATE
where STAMP_NUMBER_TXT = <stamp_number_txt>; -- replace <stamp_number_txt> with requested stamp number from ticket

--Script to locate the MEDICAL_EXAMINER_PERSON_ID and MEDICAL_OFFICE_ADDRESS_ID:
Select * from UA031_MEDICAL_EXAMINER_OFFICE where MEDICAL_EXAMINER_PERSON_ID = <medical_examiner_person_id> and MEDICAL_OFFICE_ADDRESS_ID =  <medical_office_address_id>;

--Script to update the DATE_DELETED_DTE with the today's date and time in the following table.
Update UA031_MEDICAL_EXAMINER_OFFICE
Set DATE_DELETED_DTE = SYSDATE
Where MEDICAL_EXAMINER_PERSON_ID = <medical_examiner_person_id> -- replace <medical_examiner_person_id> with your medical_examiner_person_id from UA045_STAMP table
And MEDICAL_OFFICE_ADDRESS_ID = <medical_office_address_id>;    -- replace <medical_office_address_id> with your medical_office_address_id from UA045_STAMP table

related DevOps Task: 201977 eMER found in applicant's Test & Docs tab not in MERs Tab

If an eMER is not listed under “Incoming eMERs” in CAMIS:
1. check if there the DATE_DELETED_DTE has a date set for the corresponding medical examination in the table UA006_MEDICAL_Examination.

• If the DATE_DELETED_DTE also check the values of DATE_LAST_UPDATE_DTE and USER_LAST_UPDATE_ID to find out when the medical examination was deleted and who deleted it.

• To find out who deleted the record, check YA096_PERSON and UA045_STAMP

2. If the eMER was deleted by the CAME (or business client) follow-up with the requestor to confirm if the record should be deleted.

If the requestor confirms the record should be undeleted, update the DATE_DELETED_DTE to NULL.

• Make a backup for the record.

• In CAMIS prod, verify that the record is listed under Incoming Records.

• Ask the requestor to check the eMER.