...
INSERT INTO A01_USER
SELECT
(SELECT UPPER(TRIM(TO_TXT)) FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = ‘USERCHANGE'), ’XXX’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 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') ;
...
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 */
...