...
Below, is the draft script for CAMIS (this script that adds all of the records for a fully functioning eMER account to the CAMIS database
The script sets the DELETED_DATE_DTE to the current date for all the rows it inserts exceptCAMIS.AA008_APPLICATION_USER
Currently, it is necessary to also insert data into the TWOFA database . That way, the account never gets listed in the list of available accounts. I was still able to activate the account and connect to eMER by creating the related 2FA records. I cannot submit an eMER (further testing to be done to ensure that).
At this time the TWOFA_USERID_LBL CAMIS.AA008_APPLICATION_USER has to correspond with GLOBAL_USERID_LBL in TWOFA.AC040_STAKEHOLDER.
TODO: test an account created by the scripts further; update this document / script further to improve the usability.
| 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_DELETED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
(558724, 'AAACTestAcct200', 'Noreen', 'noreen.dertinger@tc.gc.ca', 'B',
'B', 1, TO_DATE('2023/07/26 9:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/26 9:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 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, DATE_DELETED_DTE, USER_LAST_UPDATE_ID)
Values
(558724, 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/26 9:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'),
TO_DATE('2023/07/26 9:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 557838);
Insert into CAMIS.YA006_PERSON_TELEPHONE
(TELEPHONE_ID, PERSON_ID, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, DATE_DELETED_DTE, USER_LAST_UPDATE_ID)
Values
(166100, 558724, TO_DATE('2023/07/26 9:46:15 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/26 9:46:15 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 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, DATE_DELETED_DTE,
USER_LAST_UPDATE_ID)
Values
(558724, 346591, '3642', TO_DATE('2023/07/26 9:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/26 9:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'),
SYSDATE, 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, DATE_DELETED_DTE, USER_LAST_UPDATE_ID)
Values
(558724, 346591, 166099, 'N', TO_DATE('2023/07/26 9:45:02 AM', 'YYYY/MM/DD HH:MI:SS AM'),
TO_DATE('2023/07/26 9:45:02 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE, 557838);
Insert into CAMIS.UA045_STAMP
(STAMP_NUMBER_TXT, MEDICAL_EXAMINER_PERSON_ID, MEDICAL_OFFICE_ADDRESS_ID, STAMP_ASSIGNED_DTE, STAMP_RETURN_DTE, DATE_CREATED_DTE, DATE_DELETED_DTE,
DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
('A2165', 558724, 346591, TO_DATE('2023/07/01', 'YYYY/MM/DD'), SYSDATE, TO_DATE('2023/07/26 9:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), SYSDATE,
TO_DATE('2023/07/26 9: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
(558724, '431', '1', 'UKTTCV8HZVF6', TO_DATE('2023/07/26 9:44:00 AM', 'YYYY/MM/DD HH:MI:SS AM'),
TO_DATE('2023/07/26 9:46:24 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);
COMMIT; |
...