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.
...
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
...
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 already
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
...