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