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

Overview

The support team occasionally receives requests to fix data that was been incorrectly submitted to CAMIS via an eMER.

There are two parts to fixing this issue:
1) correct the data in the database.
2) correct the html and pdf files that are automatically attached when an eMER is submitted. These files are not automatically regenerated. It is necessary to make the correction manually and replace the incorrect documents stored in the database with the corrected files.

Once the correction is completed, the updated information should be verified in CAMIS.

Pre-requisite:

The person who requests the task needs to provide the information (such as file number and applicant name/initials) in order to proceed. For some requests, additional information may be needed. This will have to be determined on a case by case basis based on each individual request.

The requestor will usually send a “file” number. That file number corresponds with LICENCE_NUM in UA006_MEDICAL_EXAMINATION.

Reproduce the issue

Use the information provided by the requestor to retrieve and view the data.

For the bug provided in the example, the requestor sent the file number and initials of the applicants provided in the example.

Queries to retrieve the record(s)

a) check which tables contain the column(s) with the value that needs to be changed).

select distinct OWNER, TABLE_NAME, COLUMN_NAME
from all_tab_columns
where column_name = {'COLUMN_NAME'} --replace 'COLUMN_NAME' with actual column names and remember to remove the { and }
order by TABLE_NAME asc;

Example: select distinct OWNER, TABLE_NAME, COLUMN_NAME from all_tab_columns where column_name = 'ESOPHORIA_NBR' order by TABLE_NAME asc; Returns the names of the tables containing the column ESOPHORIA_NBR The only table we need to be concerned with, in this result set, is UA006_MEDICAL_EXAMINATION

Obtain the MEDICAL_EXAMINATION_D from UA006_MEDICAL_EXAMINATION

select MEDICAL_EXAMINATION_ID, MEDICAL_EXAMINATION_DTE, LICENCE_NUM, FIRST_NAME_NM, FAMILY_NAME_NM, STAMP_NUMBER_TXT, ESOPHORIA_NBR from UA006_MEDICAL_EXAMINATION
where LICENCE_NUM in {replace with file numbers from requestor};

select MEDICAL_EXAMINATION_ID, MEDICAL_EXAMINATION_DTE, LICENCE_NUM, FIRST_NAME_NM, FAMILY_NAME_NM, STAMP_NUMBER_TXT, ESOPHORIA_NBR from UA006_MEDICAL_EXAMINATION
where LICENCE_NUM in ( 336002, 855969, 63424, 855523, 812436, 200546, 851754, 856404 )
and STAMP_NUMBER_TXT = {'STAMP_NUMBER'}
and ESOPHORIA_NBR=1  -- 1 represents Esophoria = Yes.

Query to obtain document ids:

select UA006_MEDICAL_EXAMINATION.MEDICAL_EXAMINATION_ID, UA035_MEDICAL_EXAMINATION_DOC.MEDICAL_EXAMINATION_ID, UA006_MEDICAL_EXAMINATION.LICENCE_NUM,
UA006_MEDICAL_EXAMINATION.FIRST_NAME_NM, UA006_MEDICAL_EXAMINATION. FAMILY_NAME_NM, UA006_MEDICAL_EXAMINATION.STAMP_NUMBER_TXT,   UA035_MEDICAL_EXAMINATION_DOC.DOCUMENT_NBR,
UA018_DOCUMENT.DOCUMENT_NBR, UA018_DOCUMENT.DOCUMENT_UPLOAD_DTE, UA018_DOCUMENT.FILENAME_TXT, UA018_DOCUMENT.FILE_CONTENT_TXT, UA018_DOCUMENT.RDIMS_NUM, UA018_DOCUMENT.DOCUMENT_TYPE_CD
from UA006_MEDICAL_EXAMINATION, UA035_MEDICAL_EXAMINATION_DOC, UA018_DOCUMENT
where UA006_MEDICAL_EXAMINATION.MEDICAL_EXAMINATION_ID in {(medical examination ids)}
and UA006_MEDICAL_EXAMINATION.MEDICAL_EXAMINATION_ID = UA035_MEDICAL_EXAMINATION_DOC.MEDICAL_EXAMINATION_ID
and UA035_MEDICAL_EXAMINATION_DOC.DOCUMENT_NBR = UA018_DOCUMENT.DOCUMENT_NBR
and FILENAME_TXT like '%mer%';

Backing up the tables:

see also Production - Backup the database data, HTML and PDF files related to the incorrect value entries

1) Query to back up affected record(s) in UA006_MEDICAL_EXAMINATION:

select *
from UA006_MEDICAL_EXAMINATION
where MEDICAL_EXAMINATION_ID in {(medical examination id(s)};

2) Query to backup affected records in UA035_MEDICAL_EXAMINATION_DOC

select *
from UA035_MEDICAL_EXAMINATION_DOC
where MEDICAL_EXAMINATION_ID in {(medical examination id(s)}

3) Query to backup affected records in UA018_DOCUMENT

select * from UA018_DOCUMENT
where DOCUMENT_NBR in (document number)

Backup the original HTML and PDFs from the database:

see also Child task 208272: Production - Backup the database data, HTML and PDF files related to the incorrect value entries

• create a subdirectory, with a name like “original” in your backup location.
  Example:
  
  

  

• In UA018_DOCUMENT, double-click on "HUGEBLOB" in the FILENAME_CONTENT_TXT column.
  for the records returned by the “Query to backup affected records in UA018_DOCUMENT”
  

  

• A pop-up will appear.
  Select “Save to file”.
  

  

• Save the file with the original name from the FILENAME_TXT column (do this for all the relevant HTML Files.
  

Update the database data:

Update the appropriate column(s) in the database. Remember to commit the change (commit statement is not included in the queries)

update UA006_MEDICAL_EXAMINATION
set ESOPHORIA_NBR = NULL
where MEDICAL_EXAMINATION_ID in {(1234567, 2234567, 3234567)}

Update the HTML and PDF files

• To update the HTML and PDF files in the database:

• make a copy of the directory where you downloaded the HTML files and PDF files as described in the section “Backup the original HTML and PDFs from the database”. For example, call the directory “revised”.
  
  Example:
  

  

• open the HTML file in a plain text editor such as notepad or notepad++

• update the value.

• save the HTML file to the same filename that is already in the database (UA018_DOCUMENT in the FILENAME_TXT column)

• open the HTML file in a browser.

• print to PDF.

• save the PDF file to the same filename that is already in the database (UA018_DOCUMENT in the FILENAME_TXT column)

Replace the Files in the database

• Once the HTML and PDF files are updated they will have to be uploaded to the database. The replacement of these files has to be done from the schema browser. It cannot be done in a result set that was retrieved by a query.

• Navigate to “UA018_DOCUMENT

• To ensure I was replacing the correct files, I found it easiest to filter on the DOCUMENT_NBR of the files I was updating. As a reminder, the DOCUMENT_NBR is available in the query used to backup UA018_DOCUMENT (see Query to backup affected records in UA018_DOCUMENT earlier in this article).

  

• Double click the n UA018_DOCUMENT, double-click on "HUGEBLOB" in the FILENAME_CONTENT_TXT column.
  

  

• This time click “Load a file”

• 

• Load both the HTML file and the PDF files. (you will need to click the “X” in the upper right corner of the pop-up, to close it, after you load each file.

• Commit the change.

• Repeat as necessary, for affected file numbers in the request.
  

Testing that the file was correctly loaded to the database:

• In UA018_DOCUMENT, double-click on "HUGEBLOB" in the FILE_CONTENT_TXT column for the file you want to check.
  

  

• Select “Save to file” in the pop-up.
  

  

• Save the file to a temporary name. For example, I saved the html files I was testing to h.html, and pdf files to p.pdf in the folders for each “file” I had to update

• Verify that the files open and contain the updates that were made.
  
  

Checking the record(s) in CAMIS

• The final step, before asking the requestor to review the change is to login to CAMIS (prod).

• Important: clear the browser cache before performing the checks.

• Retrieve the file.
  

  

• Under the Tests & Docs tab, view the HTML and PDF files that were replaced.
  
  

  

  
  
  . Verify that they contain the correct updates.
  

Required info: First and last name of applicant and their new region.

Within CAMISP.WORLD, in the table CAMIS.UA006_MEDICAL_EXAMINATION, change the CAMIS_REGION_CD to the requested region.

The region value list is available in CAMIS.TA057_CAMIS_REGION. As of 2021-07-28, the region list is:

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.