Page Comparison

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)

5. No special characters. Data_compression is applied to New Userid. (A THRU Z, 0 THRU 9 are the only values permitted)

6. 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 should Userid needs to be altered to have a numeric on the end (SMITHJ becomes SMITHJ1 – this is a manual processshould become SMITHJ1 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 PROCDURES 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';

...

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

/* create 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‘USERCHANGE'),
REPLACE(PASSWORD,'''’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 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') ;

...

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

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 ;