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.

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.
  

R