Developers should not be added as a CAME via CAMIS Admin on Production because developers should not be listed in the CAME drop-down. |
|---|
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. 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.
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;
This is the draft 2FA script.
Determine if MBUN needs to be the same as for the account brought from acc when testing “bringing back” and account on dev.
SELECT AC040_STAKEHOLDER_ID_SEQ.nextval FROM dual;
INSERT INTO TWOFA.AC040_STAKEHOLDER (STAKEHOLDER_ID, STAKEHOLDER_TYPE_CD, NAME_FIRST_NM, NAME_LAST_NM, STAKEHOLDER_CREATED_ID, GLOBAL_USERID_LBL, USER_LAST_UPDATE_ID)
VALUES (1447, 2, 'Noreen', 'AAACTestAcct200', 1, 'UKTTCV8HZVF6', 1);
Insert into TWOFA.AC044_EXTERNAL_STAKEHOLDER
(STAKEHOLDER_ID, STAKEHOLDER_TYPE_CD, IDENTITY_ISSUER_TXT, IDENTITY_MBUN_NUM, ACTIVATION_CODE_TXT,
DATE_ACTIVATED_DTE, TELEPHONE_NUMBER_NUM, STAKEHOLDER_CREATED_ID, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
(1447, '2', '', '', 'y52cA9lR76J6',
SYSDATE, '6132958253', 1, SYSDATE, SYSDATE, 1);
INSERT INTO TWOFA.AC002_XREF_APPLICATION_USER (STAKEHOLDER_ID, APPLICATION_CD, ROLE_CD, STAKEHOLDER_CREATED_ID, USER_LAST_UPDATE_ID)
VALUES (1447, 1, 1, 1, 1);
COMMIT;
0 Comments