Draft of possible proposed updated process for Developer access to eMER on PROD

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 doesn't create a record for the UA45 Stamp right now.
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 also tried keeping the insert stmt for the STAMP in the script. Then it shows in the dropdown, and
setting the return and / or deleted date did not suppress the account from showing there.

=Test if the account is displayed in the dropdown if the stamp is set to returned / deleted at the time the script provided in the Confluence document for restoring an account is run.

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;