Assigning an eMER to the correct applicant
- Noreen Dertinger
From time to time, the maintenance team receives requests to transfer an eMER from the file of an applicant, that it was incorrectly assigned to, by mistake, to the file corresponding to the correct applicant.
In order to perform such a file transfer request, you need to have a clear understanding of
the incorrect_file_no = the file (licence) number of the incorrect applicant that the eMER is assigned to (provided by the requester in the ticket)
correct_file_no the file number of the applicant the eMER has to be assigned to. (provided by the requester in the ticket)
IMPORTANT NOTE: The table that you will be updating is UA006_MEDICAL_EXAMINATION. However, some of the queries used in obtaining the information to update the data are also referred to in the queries, so I have provided the names of all the tables (views etc. are not listed) in these listings. (UA006_MEDICAL_EXAM39QE1437003 is what appears to be a backup table that pre-dates my experience with CAMIS and should be disregarded for the purpose of these transfers).
and FILE_NUMBER in these tables.
For example, LICENCE_NUM = 1234567 in table YA003_APPLICANT refers to the same value in the other tables having the COLUMN_NAME of LICENCE_NUM. LICENCE_NUMBER=1234567 also refers to the equivalent of FILE_NUMBER = 1234567 in table MV002_DAPLS_APPLICANT (and the other tables having the COLUMN_NAME of FILE_NUMBER). |
|---|
Some examples of previously completed file transfer tasks:
183798 Request to transfer eMER to correct file number - see also the subtask
194685 Update the incorrect assignment of eMer file number.
Example from February 2024 that also involved transferring data under other tabs, in addition to the eMER: 326481 Data File transfer due to duplicate file created in error. The related subtask, 327203, contains the scripts used to transfer data in the “Tests & Docs” and “Related Activities” tabs. See subtask
Transfer eMER and other related items to correct file/licence in error.
Performing the file transfer:
Retrieving the applicant information.
Find the applicant information for the incorrect and correct file numbers by running the attached script (embedded below) in an TOAD editor tab (or other SQL editor
Confirm that the file numbers, applicant name(s) and, if provided, date of birth, correspond with what the requester provided in the task. the same as the info given from the client.
Take note of the MEDICAL_EXAMINATION_ID as you will need it when updating the data.
IMPORTANT: Reach out to the author of the task if you find unexplained discrepancies in the data at any point.
Script to retrieve the applicant data for the correct and incorrect files.
--This script is for retrieving information that is needed when transferring an eMER from an incorrect file (licence) --to a correct licence. select UA006_MEDICAL_EXAMINATION.MEDICAL_EXAMINATION_ID, UA006_MEDICAL_EXAMINATION.APPLICANT_PERSON_ID, UA006_MEDICAL_EXAMINATION.LICENCE_NUM, UA006_MEDICAL_EXAMINATION.FIRST_NAME_NM, UA006_MEDICAL_EXAMINATION.FAMILY_NAME_NM, UA006_MEDICAL_EXAMINATION.DATE_BIRTH_DTE, UA006_MEDICAL_EXAMINATION.STAMP_NUMBER_TXT, UA006_MEDICAL_EXAMINATION.DATE_EMER_SUBMITTED_DTE from UA006_MEDICAL_EXAMINATION inner join YA003_APPLICANT on UA006_MEDICAL_EXAMINATION.LICENCE_NUM = YA003_APPLICANT.LICENCE_NUM where UA006_MEDICAL_EXAMINATION.LICENCE_NUM = {incorrect_file_no} -- replace {incorrect_file_no} with the file (licence) # of incorrect applicant. Remember to remove the { and }. OR UA006_MEDICAL_EXAMINATION.LICENCE_NUM = {correct_file_no} -- replace {correct_file_no} with the file (licence) # of correct applicant Remember to remove the { and }
Performing the file transfer
You will require the following information, for the “correct” file from the earlier section ”Retrieving the Applicant information”
the
correct LICENCE_NUM (aka “correct” file number provided by the requester).
the
MEDICAL_EXAMINATION_ID corresponding with the correct LICENCE_NUM.
You will also require the APPLICANT_PERSON_ID corresponding with the correct LICENCE_NUM.
The following script:
retrieves the APPLICANT_PERSON_ID
allows you to verify that it corresponds with the applicant the data is being transferred to.
You will need to replace {licence_num}, including { and } with the correct LICENCE_NUM
Script to retrieve the APPLICANT_PERSON_ID
select YA003_APPLICANT.APPLICANT_PERSON_ID, YA003_APPLICANT.LICENCE_NUM, MV002_DAPLS_APPLICANT.FILE_NUMBER, MV002_DAPLS_APPLICANT.GIVEN_NAMES, MV002_DAPLS_APPLICANT.SURNAME, MV002_DAPLS_APPLICANT.DATE_OF_BIRTH from YA003_APPLICANT left join MV002_DAPLS_APPLICANT on YA003_APPLICANT.LICENCE_NUM = MV002_DAPLS_APPLICANT.FILE_NUMBER where LICENCE_NUM = {licence_num} --replace licence_num with the correct licence_number of the applicant. Remember to remove the { and }.
In a new/separate editor tab, run the following script to transfer the file from the incorrect applicant to the correct one:
--script to transfer data to the correct file number
--don't forget to replace the placeholders, including { and } with the correct values.
update UA006_MEDICAL_EXAMINATION
set LICENCE_NUM = {license_no}, APPLICANT_PERSON_ID = {person_id}
where MEDICAL_EXAMINATION_ID = {medical_examination_id}
Prior to committing the change:
Go back to the editor tab where the script to obtain the applicant information was run
rerun the script.
verify that the LICENCE_NUM and APPLICANT_PERSON_ID are correctly updated for the applicant the eMER information is being transferred to.
COMMIT the change that was made (go back to the editor tab having the update script and click commit on the toolbar or type “commit” (and run) in an empty editor tab.
Transferring Data under other tabs such as Tests & Docs, Related Activities, etc.
It may be necessary to also transfer data from other tabs, such as Tests & Docs and Related Activities. The author of the ticket should indicate that.
In a case where the MER was already transferred to the correct licence use the following query to obtain the APPLICANT_PERSON_IDs:
See also
371493 Transfer of Related activity from 526621 to 506050 → refer to
subtask #376244 Transfer activities from 526621 to 506050
Checking for Related Activities
Transferring Related Activities
Checking for Tests & Documents
Transferring Tests & Docs
Document what you did - just in case we need to come back to it at a later date for any reason.
Create a related task and include the actual script you ran.
Verifying the change in CAMIS PROD
IMPORTANT: 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.
Note: if the files listed under the Tests and Docs and / or MERS tabs are incorrect double check that you have used the correct APPLICANT_PERSON_ID in the update script.
In CAMIS (prod):
Select “Applicant search” from “Applicant” in the left hand menu:
Enter the file number in the resulting form:
Look in the Tests & Docs and the MER tabs in the resulting profile.
Set the task to “Committed” and ask the requester to review that the new file has the correct information
