CAMIS - SIMAC

A workflow management and medical assessment tool used to support the entire life-cycle of a medical report for licensed aviation personnel.

Key functionality includes:
• A secure Internet-based application;
• Online access to current and previous medical history of applicants;
• Electronic submission of medical reports of licensed aviation personnel; and
• Online assessments of the reports and secure storage of medical documentation and
assessment results.

System Profile

System Full Name

Civil Aviation Medical Information System

System Full Name (French)

Systeme d'information medicine de l'Aviation civile

Subject Matter Expert

Michelle Coelho

Web Team Expert

Michaud, Marcel

Division

Medicine

NTARS Code

CA09

Source Code Location (Archived)

http://tfsprod:8080/tfs/CivAv/CAMIS-SIMAC/

Source Code Location

https://dev.azure.com/transport-canada/DSD-CIVAV%20Support/_git/CAMIS-SIMAC

Technology Assessment

Platform Type

Web (Internal/Secure)

Database Platform and Version

Oracle 18C

Development Language and Framework

ASP.NET + VB.NET

Operating System and Version

Windows Server 2016

Additional Dependencies

 

Authentication

Entrust True Pass

Environment Access Information

ENV

UNC

WWWFILES

URL

DB

ENV

UNC

WWWFILES

URL

DB

DEV

 

\\tcwebscripts\tpwwwroot\eigdev\CAMIS-SIMAC

https://eigdev.tc.gc.ca/camis-simac/

CAMISD

PREACC

 

 

 

PREACC_CA

ACC

 

\\tcwebscripts\tpwwwroot\eigacc\CAMIS-SIMAC

https://eigacc.tc.gc.ca/camis-simac/

CAMISA

TRAINING

 

 

 

CAMIST

PROD

 

 

https://eig.tc.gc.ca/camis-simac/

CAMISP

 

CAMIS Report Generator (CAMIS Weekly Auto-Accept report steps):

 

This program needs to be run weekly on Monday until the changes to CAMIS/eMER have been finalized.

CAMIS Report Generator: performing the weekly auto-accept - BSD - Civil Aviation - Confluence (atlassian.net)

System Overview

To provide a secure method for the submission of electronic Medical Examination Reports (MERs) for licensed aviation personnel and supporting medical documentation to Civil Aviation Medicine (CAM). Its purpose was to keep track of these medical reports, provide automated approval or specialized scrutiny by CAM medical resources, and store tombstone information on Civil Aviation Medical Examiners (CAMEs).

CAMIS Version 3.0 has been developed as a secure, thin-client, web-based, multi-tiered
application. Version 3.0 integrates CAMIS with the Distributed Air Personnel Licensing System
(DAPLS) ensuring synchronization of tombstone, address, and medical assessment information;
including medical license category and any medical licensing restrictions pertaining to the
issuance of the license (e.g. glasses must be worn).

 

 

The Civil Aviation Medical Information System (CAMIS) is a web based application used by the Civil Aviation Medicine (CAM) Group to track and assess medical reports of licensed aviation personnel.

CAMIS is tightly linked with https://tcmarin.atlassian.net/wiki/spaces/CA/pages/1879802573 (share a database).

Entrust True Pass is used to authenticate users' MyKey before they are able to access the website.

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.

 

The pilot records are maintained in DAPLS. A certified pilot needs to be certified medically. DAPLS updates license numbers to CAMIS but I think CAMIS will update licenses' medical information to DAPLS (we will review it later).

 

Good To Know

The first version of CAMIS was put in production in 2004 in Java and updated in 2010 as a .net wersion.

CAM: Civil Aviation Medicine - the CAMIS Client, CAM will process the applications from eMERS in the internal CAMIS application

CAME: Doctors working at Apple Tree doing medicals on behalf of TC. Doctors make applications on behalf of the pilots. They will do these through eMER

Medical Examination Date has to be before or equal submitted eMER date, otherwise CAM can not process the record. Both of the dates are inside the table UA006_MEDICAL_EXAMINATION. Errors are sometime happening because eMER allows to submit to CAMIS the future examination date!!!

 

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


Files for testing attachment types:

see Files for testing attachment types

 

Deploying CAMIS to development, acceptance and production:

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2850750477

Deploying CAMIS to development, acceptance and production:

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2850750477

How-To and Fixes

Suggested credential for any fix:
Development: CAMIS_DATA_ADMIN@CAMISD
Acceptance: CAMIS_DATA_ADMIN@CAMISA
Production: CAMIS_DATA_ADMIN@CAMISP

 

 

Backing up Production Data:


Production data should be backed up before it is changed. This document provides the steps for backing up data using TOAD.

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2812149905


Oracle errors: localhost and deployments (dev, acc, prod)

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2360573966

Applies to:

  • localhost

  • publishing packages for development, acceptance and production

Overview of CAMIS User (internal) and eMER accounts

External (eMER) and Internal (CAMIS User) accounts are both added to AC040_STAKEHOLER because AC040 is the master account table.
-> External accounts are identified by STAKEHOLDER_TYPE_CD = 2
-> Internal accounts are identified by STAKEHOLDER_TYPE_CD = 1

Table AC043_INTERNAL_STAKEHOLDER is for internal (CAMIS User) accounts ONLY.   

Table AC044_INTERNAL_STAKEHOLDER is for external (eMER - aka CAME Admin and CAME Associate Admin) only ONLY.

For a more detailed explanation of the accounts that are managed in CAMIS, please see,
https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2955935776


Account access issues:


CAME Accounts:

Checklist for the business users to check first:

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2813919334


Developer troubleshooting (queries)

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2812215297




Account Creation for access to CAMIS:

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2901770241

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2206695495 (for CAME and CAME associate account administration purposes)

Unable to generate new activation code for CAMES - BSD - Civil Aviation - Confluence (atlassian.net)

 

Account Creation for access to eMER:



Process for giving CAMES and CAME Associate Admins access to eMER on production.


The difference between CAMES and CAME Associates

CAME Associates are generally nurses/admin staff within a CAME's office, who do some data entry on behalf of the CAME(doctor).  

CAMES and CAME Associates access eMER the same way: with a GCKey and 2FA authentication.

 

The processes for creating a CAME or CAME Associate profile can be used by developers for creating test accounts on development and acceptance but not Production as the names of developers are not supposed to appear in the list of CAMES in the existing CAME dropdown.

This is the process that developers should use to create a test CAME troubleshooting account on production: https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2764800001

We should not submit data to production without collaborating with the business first.

The processes for creating a CAME or CAME Associate profile can be used by developers for creating test accounts on development and acceptance but not Production as the names of developers are not supposed to appear in the list of CAMES in the existing CAME dropdown.

This is the process that developers should use to create a test CAME troubleshooting account on production: https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2764800001

We should not submit data to production without collaborating with the business first.

 

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2814574599

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2814509069


File transfer: reassigning a file (an eMER) to the correct applicant

This is the main process that we have been following:

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2814509106

 

Applicant merge

This is used for merging duplicate files. It is different from File Transfer

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2814181593

 

Incoming eMER lost after being accepted
https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2850160644

 

ORACLE error when updating CAME profile

https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2851045383

 

CAMIS / eMER Region Change:

CAMIS / eMER Region Change - BSD - Civil Aviation - Confluence (atlassian.net)

 

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
https://tcmarin.atlassian.net/wiki/spaces/CA/pages/2206695495

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 ADMINCAME 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 LogsMessaging.

    • 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.

 

 

 

 

 

TFS Testing Plans, Suites and Test Cases imported: