USERID CONFLICTS AND CLEANUP

When an external WSDRS userid is found to conflict with the userid of a TC employee (anyone on TC directory), the external userid should be altered to have a numeric on the end (SMITHJ becomes SMITHJ1 – this is a manual process - However, it should be available to the SDR manager on the side menu - 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 PROCDURES - 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 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'),
REPLACE(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 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;