...
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
Note: the following table TC019_ADDRESS contain reference data for UA031_MEDICAL_EXAMINER_OFFICE:
TC019_ADDRESS.
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:
...
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; |
...