Page Comparison

...

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.

IMPORTANT: Backup PRODUCTION DATA before making any changes.
Please refer to
child task 207061 Production: Backup and update Incorrect esophoria values in table UA006_MEDICAL_EXAMINATION for identified applicants
and child task 208272 Production - Backup the database data for the eMER HTML and PDF filesQueries to retrieve the record(s)

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

Expand

title	Fix incorrect eMER data in database and associated eMER HTML/PDF attachments

Example: Bug 206143 CAME entered values that were incorrect by error

Code Block

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

Image Removed

Obtain the MEDICAL_EXAMINATION_D from UA006_MEDICAL_EXAMINATION

Code Block

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};

Example: the LICENCE_NUM values provided are samples only, not real values in a database, replace them with the “file numbers” from the 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 (1, 2, 3, 4);

Since there were multiple records associated with each file number, it was best to work with the MEDICAL_EXAMINATION_ID for the record(s).

Code Block

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.

Example:
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 ( 1, 2, 3, 4 )
and STAMP_NUMBER_TXT = {'A123'}
and ESOPHORIA_NBR=1 -- 1 represents Esophoria = Yes.Query to obtain document ids:

Code Block

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%';

Example (note: the numbers in the where clause are samples, not actual values in any db).
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 (1234567, 2234567, 3234567)

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:

Code Block
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

Example (note: the numbers in the where clause are samples, not actual values in any db).
select *
from UA006_MEDICAL_EXAMINATIONwhere MEDICAL_EXAMINATION_ID in (1234567, 2234567, 3234567);

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

3) Query to backup affected records in UA018_DOCUMENT

Code Block

language	sql

select * from UA018_DOCUMENT
where DOCUMENT_NBR in (document number)

Example:
select *
from UA018_DOCUMENTwhere DOCUMENT_NBR in (123456, 23456)

Backup the original HTML and PDFs from the database:

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

Image Removed

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”

Image Removed

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

Image Removed

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)

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

Check that the value you are changing a result for is consistent with the related section of the eMER. If you are unsure, check with the requestor.
For bug 206143 (CAME entered values that were incorrect by error), I updated the ESOPHORIA_NBR from “yes” to “No” depending on how the CAME set the related values of “HYPERPHORIA” and “EXOPHORIA. That was because I noticed that sometimes the CAME specifically entered "No" for Hyperphoria and Exophoria and in those cases I also specifically set Esophoria to No to keep the report consistent., For three of the records, Hyperphoria and Exophoria were NULL so I set Esophoria to NULL for those cases.
see also child task 207061: Backup and update Incorrect esophoria values in table UA006_MEDICAL_EXAMINATION for identified applicants

Update statements to set ESOPHORIA_NBR to NULL for the following three records.

update UA006_MEDICAL_EXAMINATION
set ESOPHORIA_NBR = NULL
where MEDICAL_EXAMINATION_ID in {(medical examination id(s))}

Update ESPOPHORIA_NBR to 0 (NO) for these five records)
update UA006_MEDICAL_EXAMINATION
set ESOPHORIA_NBR = 0
where MEDICAL_EXAMINATION_ID in {(medical examination id(s))}

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:

Image Removed

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).
Image Removed
Double click the n UA018_DOCUMENT, double-click on "HUGEBLOB" in the FILENAME_CONTENT_TXT column.
Image Removed
This time click “Load a file”
Image Removed
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.

Image Removed

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

Image Removed

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.
In one case, the browser could not open the html and pdf files I updated. This was because the file name contained multiple spaces in one spot. For example, instead of the FILENAME_TXT column’s filename having Text-Text Text--mer.html it had Text-Text Text--mer.html.
I removed the extra spaces in FILENAME_TXT and PATH_NAME_NM columns for the affected record.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.

Image Removed

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

Image Removed. Verify that they contain the correct updates.
Expand

title	CAMIS/eMER Region Change

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:

Code	Name (English)
431	Outside of Canada-Ottawa (HQ)
432	Quebec - Dorval (UL)
433	Ontario - Toronto (YZ)
434	Central - Winnipeg (WG)
435	Western - Edmonton (XD)
436	Pacific - Vancouver (VR)
437	Atlantic - Ottawa (QM)
3642	Prairie Northern - (WG/XD)

Expand

title	Document Fix (eMER submits as PDF, CAMIS expects HTML)

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:

Code Block

language	sql

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

Expand

title	2FA SMS code is not being sent to users cell phone

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.

Log in to CAMIS and go to ADMIN → CAME Admin.
In the Select Existing CAME dropdown, find the user (sorted by last name).
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).
If there are no issues with the cell #, check the 2FA database logs for any errors.
- TWOFA_USER@TTSXP18
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.

Expand

title	Delete items from "My Tasks"

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.

Expand

title	Incorrect name for the applicant is displayed in the “Applicant Details” section of the medical record (eMER)

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

Code Block

language	sql

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,

Update the first name and last name in database for this MER record
- UA006_MEDICAL_EXAMINATION: Filter the record using medical_examination_id
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.
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
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

Expand

title	Future date of eMer is accepted

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

Expand

title	Steps for transferring / correcting eMERs and / or documents between existing applicants (bug 181510)

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.

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.

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}');
Export the INSERT statements for the rows to be deleted and put into the the comments of the bug
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.).

View file

name	CAMIS_Person_Transfer.sql

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:

Expand

title	Missing Indicators on CAMIS.Tombstone tab

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

Expand

title	Delete the address associated with Stamp number

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.

Code Block

language	sql

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

Expand

title	eMER not listed under incoming eMERS in CAMIS

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.

...

Versions Compared

Old Version 173

New Version 174

Key

Overview

Pre-requisite:

Update the database data:

Update the HTML and PDF files

Replace the Files in the database

Testing that the file was correctly loaded to the database: