Page Comparison

Applies to development and acceptance. Do not use this procedure to insert test CAME accounts, for developers, on production.

When should an eMER account(s) be backed up?

CAMIS developer (Avro Arrows and Medevac) as well business support accounts being used on acceptance should be backed up before the refresh.
If you have an account on development and wish to recreate it on acceptance (or vice versa). You will have to modify some values in the scripts that are exported, due to sequences that exist on some tables, as explained in this document.
other occasions, as needed.

Identifying the accounts to backup:

This script returns the developer accounts, for Team Avro Arrows and Team Medevac, as well as the business that are currently active on the target environment.

Code Block

select * from YA096_PERSON
where upper(LAST_NAME_NM) in ('ASGILL', 'AMIN', 'COLLINS', 'DERTINGER', 'KHAN', 'MAGHUR', 'NGUYEN', 'WANG', 'SHIRZAD', 'LE', 'BEDARD', 'DAVID-OMBIGA',  'KENNEDY', 'PEREIRA', 'ROY', 'SAMANT', 'ZOBARICH', 'BAPTISTE', 'DAWI' )
and upper(FIRST_NAME_NM) in ('ALAIN', 'HIREN', 'CHRIS', 'NOREEN', 'SAM', 'SUFYAN', 'PHUONG', 'HAOYUAN', 'SIAMAK', 'MICHELLE', 'ALEXANDRE', 'BOJE', 'RYAN', 'FABIO', 'VERONIQUE', 'MOHIT', 'RICHARD', 'DANIEL', 'SAMIA')
and DATE_DELETED_DTE is NULL
--and upper(EMAIL_TXT) like ('%TC.GC.CA')

order by LAST_NAME_NM asc;

Export the records to “sql” script files for the following tables:

These tables contain the main data for a fully functional eMER account. The tables are presented in the order that the insert scripts need to be in, in the final script that is constructed to restore the record.

CAMIS.YA096_PERSON
CAMIS.YA001_MEDICAL_EXAMINER
CAMIS.YA006_PERSON_TELEPHONE
CAMIS.UA031_MEDICAL_EXAMINER_OFFICE
CAMIS.UA041_MEDICAL_OFFICE_TEL
CAMIS.UA045_STAMP
CAMIS.AA008_APPLICATION_USER

Also backup the reference table TC019_ADDRESS as you may wish to restore addresses if a refresh is being done from production to acceptance or development. Production may not have all the same addresses.Note: TC019_ADDRESS contain reference data for UA031_MEDICAL_EXAMINER_OFFICE.

The rows to backup can be identified by running queries similar to the following

1) A query to select the relevant rows from UA045_STAMP based on MEDICAL_OFFICE_ADDRESS IDS

Code Block

select distinct MEDICAL_OFFICE_ADDRESS_ID from UA045_STAMP
where MEDICAL_EXAMINER_PERSON_ID in (762968,
762940,
762942,	
762818,
762900,
762960,
762967,
721858,
762965,
762964,
762966,
762921,
763069,
762968,
762940,
762942,	
762818,
762900,
762960,
762967,
721858,
762965,
762964,
762966,
762921,
763069)

2) Query to retrieve the Medical Office Addresses from UA031_MEDICAL_EXAMINER_OFFICE

Code Block
select * from UA031_MEDICAL_EXAMINER_OFFICE where MEDICAL_OFFICE_ADDRESS_ID in(100607, 325820, 352444, 352403, 249515, 352383, 352423, 352424)

Exporting data to “sql” script files:

Take note of the PERSON_ID of the account in YA096_PERSON from the script run earlier in this document.
Retrieve the account to be backed up;
For example:

...

The expected result is a sql file containing an insert statement similar to the one below, with the actual data content corresponding to the record being exported:

Code Block

Insert into CAMIS_DATA_ADMIN.YA096_PERSON
   (PERSON_ID, LAST_NAME_NM, FIRST_NAME_NM, EMAIL_TXT, LANGUAGE_CORRESPONDENCE_CD, 
    LANGUAGE_SERVICE_CD, EXTERNAL_PERSON_IND, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
 Values
   (762818, 'DERTINGER', 'Noreen', 'noreen.dertinger@tc.gc.ca', 'B', 
    'B', 1, TO_DATE('2023/06/05 7:58:11 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/06 2:48:16 PM', 'YYYY/MM/DD HH:MI:SS AM'), 733962);

Repeat these steps to generate SQL files for the remaining seven tables
- Combine the exported SQL scripts into a single file in the exact order the table names were listed in this document to avoid constraint errors.

Updating the script for compliance with the sequences

Before running the script with the combined insert statements, the following values have to be checked for compliance with the sequences. If that is not done constraint errors will occur.

VALUE	SQL statement to get next correct sequence
PERSON_ID	SELECT YA096_SQ1_BI_PERSON_ID.nextval FROM dual;
STAMP_TXT_NBR	SELECT NEXT_STAMP_NBR from V_NEXT_AVAILABLE_STAMP_NBR;

Sample Script:

...

after a script such as this one was run the account will show in the list of available CAMES

Code Block
Insert into CAMIS.YA096_PERSON

...


   (PERSON_ID, LAST_NAME_NM, FIRST_NAME_NM, EMAIL_TXT, LANGUAGE_CORRESPONDENCE_CD,

...


    LANGUAGE_SERVICE_CD, EXTERNAL_PERSON_IND, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)

...


 Values
   (558712, 'AAACTestAcct111', 'Noreen', 'noreen.dertinger@tc.gc.ca', 'B',

...


    'B', 1, TO_DATE('2023/07/21 7:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/21 7:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'),  557838);

...



Insert into CAMIS.YA001_MEDICAL_EXAMINER

...


   (MEDICAL_EXAMINER_PERSON_ID, APPOINTMENT_START_DTE, APPOINTMENT_END_DTE, COMMENT_TXT, HOME_ADDRESS_PRIVATE_IND,

...


    MAILING_ADDRESS_PRIVATE_IND, EXAMINER_STATUS_CD, EMAIL_PRIVATE_IND, SEARCH_IND, DATE_CREATED_DTE,

...


    DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)

...


 Values
   (558712, TO_DATE('2023/07/19', 'YYYY/MM/DD'), TO_DATE('2027/07/19', 'YYYY/MM/DD'), 'Test account - noreen', 'Y',

...


    'Y', '1', 'N', 'Y', TO_DATE('2023/07/21 7:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'),

...


    TO_DATE('2023/07/21 7:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

...

   

Insert into CAMIS.YA006_PERSON_TELEPHONE

...


   (TELEPHONE_ID, PERSON_ID, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)

...


 Values
   (166100,

...

 558712, TO_DATE('2023/07/21 7:46:15 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/21 7:46:15 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

...

  

Insert into CAMIS.UA031_MEDICAL_EXAMINER_OFFICE

...


   (MEDICAL_EXAMINER_PERSON_ID, MEDICAL_OFFICE_ADDRESS_ID, CAMIS_REGION_CD, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE,

...


    USER_LAST_UPDATE_ID)

...


 Values
   (558712, 346591, '3642', TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'),

...


    557838);

Insert into CAMIS.UA041_MEDICAL_OFFICE_TEL
   (MEDICAL_EXAMINER_PERSON_ID, MEDICAL_OFFICE_ADDRESS_ID, TELEPHONE_ID, PRIVATE_IND, DATE_CREATED_DTE,
    DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
 Values
   (558712, 346591, 166099, 'N', TO_DATE('2023/07/21 7:45:02 AM', 'YYYY/MM/DD HH:MI:SS AM'),
    TO_DATE('2023/07/21 7:45:02 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

Insert into CAMIS.UA045_STAMP

...


   (STAMP_NUMBER_TXT, MEDICAL_EXAMINER_PERSON_ID, MEDICAL_OFFICE_ADDRESS_ID, STAMP_ASSIGNED_DTE, DATE_CREATED_DTE,

...


    DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)

...


 Values
   ('

...

2161',

...

 558712, 346591, TO_DATE('2023/07/01', 'YYYY/MM/DD'), TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'),

...


    TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);    

-- Note: the value for TWOFA_USERID_LBL has to match the GLOBAL_USERID_LBL in the corresponding TWOFA database. 
-- refer to the Confluence document "Refreshing the 2FA database"-- Refreshing the 2FA database 

Insert into CAMIS.AA008_APPLICATION_USER
   (PERSON_ID, CAMIS_REGION_CD, USER_STATUS_CD, TWOFA_USERID_LBL, DATE_CREATED_DTE,
    DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
 Values
   (558712, '431', '1', 'UKTTCV8HZ1T2', TO_DATE('2023/07/21 7:44:00 AM', 'YYYY/MM/DD HH:MI:SS AM'),
    TO_DATE('2023/07/21 7:46:24 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

...



COMMIT;

...

Versions Compared

Old Version 3

New Version Current

Key

When should an eMER account(s) be backed up?

Identifying the accounts to backup:

Export the records to “sql” script files for the following tables:

Exporting data to “sql” script files:

Updating the script for compliance with the sequences