SITUATION #1 - WSDRS USERID CONFLICTS WITH TC USERID
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 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.
Note: any inactive external user who has never submitted a report, is auto-deleted already
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
#############
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 ;