USERID CONFLICTS AND CLEANUP

NOTE: The CAWIS release of April 2022 gave ADMIN users the ability to Alter existing User-ids under the following conditions :

1. You cannot change a TC userid (user exists on TC-Directory -or- has an email domain tc.gc.ca)

2. 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’)

3. The New USERID cannot ALREADY exist on A01_user

4. 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)

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 TC employee (anyone on TC directory), the external 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.

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 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 Z980_TERM_CONVERSION
SET FROM_TXT = 'old-userid',
TO_TXT = 'new-userid'
WHERE TERM_TYPE_CD = 'USERCHANGE';

COMMIT ;

/* Ensure desired new userid is not in use */

SELECT * FROM A01_USER WHERE USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

/* Create a new Userid on the A01 table having the same party_id as the old userid */

INSERT INTO A01_USER
SELECT
(SELECT UPPER(TRIM(TO_TXT)) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = ‘USERCHANGE'), ’PASSWORD', TRUNC(SYSDATE), 'TCUSER', TRUNC(SYSDATE),0,
PARTY_ID, ACTIVE_IND, NVL(TC_REGION_CD,'7')
FROM A01_USER
WHERE USER_ID = (SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION
WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT ;

/* Update all instances of old userid to new – Note an SDR account generally has usage limited to the tables updated below */

UPDATE M79_SDR_SUBMITTER

SET SUBMITTER_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE SUBMITTER_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE M79_SDR_SUBMITTER SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE SUBMITTER_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

UPDATE M72_SDR SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE TR87_USER_TATC SET USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

UPDATE A30_USER_PREFERENCE SET USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE A30_USER_PREFERENCE SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE A103_USER_ROLES SET USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

UPDATE A103_USER_ROLES SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE Y90_PARTY_CONTACT SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

UPDATE A90_ADDRESS SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE M80_SDR_CHANGE_EVENT SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;
COMMIT;

UPDATE A92_ADDRESS_PROPERTY SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE Y50_PARTY SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE Y55_INDIVIDUAL SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

UPDATE Y56_ORGANIZATION SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE M73_SDR_PRODUCT_MAKE_MODEL SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

UPDATE M74_SDR_JASC SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE M75_SDR_SUPPLEMENTARY_TEXT SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

UPDATE M77_SDR_NATURE SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE M78_SDR_ACTION_TAKEN SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

UPDATE M80_SDR_CHANGE_EVENT SET LAST_MOD_BY_USER_ID =
(SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE')
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

DELETE FROM A31_USER_QUERY
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

DELETE FROM A31_USER_QUERY
WHERE OWNER_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

DELETE FROM A30_USER_PREFERENCE
WHERE LAST_MOD_BY_USER_ID =
(SELECT TRIM(FROM_TXT) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'USERCHANGE') ;

COMMIT;

/* Setup the Old-userid for overnight deletion – Any userid with a NULL party_id or having a password=DELETE will be deleted overnight */

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

1. move all SDR reports submitted by those users, under User #1  in each organization

2. remove user from M79 table

3. set the password for each user to DELETE on A01

The Overnight routines DELETE any external Userid where password is DELETE

Notes:

1. Any inactive external user who has never submitted a report, is auto-deleted already

2. Reports submitted from inactive accounts are in a read-only state until transferred to an active account

3. 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 ;