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 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 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.
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 ;
0 Comments