NOTE: The CAWIS release of April 2022 gave ADMIN users the ability to Alter existing User-ids under the following conditions :
You cannot change a TC userid (user exists on TC-Directory -or- has an email domain tc.gc.ca)
You cannot change a SYSID ( examples : ccarcsupd, CLIENT, AAIRRUN, AARDG ) A list of SYSIDs can be seen at (Select * from Z980_term_conversion where Term_type_cd = ‘SYSID’)
The New USERID cannot ALREADY exist on A01_user
You cannot change a USERID “BACK” to what it originally was on the same day. An old/previous Userid remains on the A01 table until its deleted by the overnight sync job (synchronize_ccarcs_cawis)
No special characters. Data_compression is applied to New Userid. (A THRU Z, 0 THRU 9 are the only values permitted)
At present, User-id changes can only be performed via the CAWIS admin menu, not from the WSDRS User maintenance module
SITUATION #1 - WSDRS USERID CONFLICTS WITH TC USERID
The notes below are retained for backup purposes only. The function described in the introductory notes should be sufficient to perform any cleanups.
When an external WSDRS userid is found to conflict with the userid of a new TC employee (or anyone on TC directory) , the external Userid needs to be altered to have a numeric on the end (SMITHJ should become SMITHJ1 ) For now, this is a manual process - However, it should be available to the SDR manager on the side menu - as this comes up often - see Enhancements section. for example )
This procedure can also be used for a simple USERID change as well. Frequently the SDR submitters will request a change of userid when someone leaves a company, but may many reports are tied into a given account.
This procedure CANNOT be used on TC internal userid’s
###########################
/* CHANGE USERID PROCEDURES - SETUP “OLD” AND “DESIRED NEW” USERID’S ON Z980 */
...
UPDATE A01_USER SET PARTY_ID = NULL, ACTIVE_IND = 'N'
WHERE USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;
commit;
SITUATION #2 - ELIMINATION OF INACTIVE WSDRS USERS
The query below identifies inactive WSDRS users having submitted SDR reports.
The statement also generates update statements to
move all SDR reports submitted by those users, under User #1 in each organization
remove user from M79 table
set the password for each user to DELETE on A01
The Overnight routines DELETE any external Userid where password is DELETE
Notes:
Any inactive external user who has never submitted a report, is auto-deleted
Reports submitted from inactive accounts are in a read-only state until transferred to an active account
User #1 of any organization is always maintained as ACTIVE
#############
SELECT
SUBSTR(GET_PARTY_INFO(M79.SUBMITTER_ORGANIZATION_ID ),1,20) AS ORG,
M79.SUBMITTER_USER_ID AS INACTIVE,
SUBSTR(GET_USER_NAME_FROM_ID(M79.SUBMITTER_USER_ID,' '),1,25) AS USERNM,
TO_CHAR(M79.LAST_MOD_TIMESTAMP_DTE,'YYYY-MM-DD') AS LM,
SUBSTR(TO_CHAR(M79.SUBMITTER_ORGANIZATION_ID),1,13) AS ORGID,
SUBSTR(TO_CHAR(
(SELECT COUNT(*) FROM M72_SDR M72
WHERE M72.SUBMITTER_ORGANIZATION_ID = M79.SUBMITTER_ORGANIZATION_ID
AND M72.SUBMITTER_SEQUENCE_NBR = M79.SUBMITTER_SEQUENCE_NBR)
),1,6)
AS SDRKNT,
SUBSTR(TO_CHAR(M79.SUBMITTER_SEQUENCE_NBR),1,5) AS SEQNUM,
'UPDATE M72_SDR SET SUBMITTER_SEQUENCE_NBR = 1 WHERE SUBMITTER_ORGANIZATION_ID = ' ||
M79.SUBMITTER_ORGANIZATION_ID || ' AND SUBMITTER_SEQUENCE_NBR = ' ||
M79.SUBMITTER_SEQUENCE_NBR ||
'; UPDATE A01_USER SET PASSWORD = ''IrZ%.>!9c#'' WHERE USER_ID = ''' ||
M79.SUBMITTER_USER_ID || ''';' ||
' DELETE FROM M79_SDR_SUBMITTER WHERE SUBMITTER_USER_ID = '''
|| M79.SUBMITTER_USER_ID || ''';' UPD1
FROM M79_SDR_SUBMITTER M79
WHERE M79.IS_ACTIVE_IND <> 'Y'
ORDER BY ORG, SUBMITTER_USER_ID ;