This is very helpful when instances are cloned and we need to reset the password of any existing user for testing in a test instance.
Of course, this can't be done by anybody unless specific rights are not allocated.
SET serveroutput ON;
DECLARE
v_user_name VARCHAR2(30):= UPPER('SYSADMIN');v_new_password VARCHAR2(30):= 'welcome1';v_add_resp VARCHAR2(5):='Y';
v_status BOOLEAN;
procedure Add_responsibility(p_user_name varchar2,p_responsibility_name varchar2) is
v_user_name VARCHAR2 (30) := p_user_name;
v_responsibility_name VARCHAR2 (100) := p_responsibility_name;
v_application_name VARCHAR2 (100) := NULL;
v_responsibility_key VARCHAR2 (100) := NULL;
v_security_group VARCHAR2 (100) := NULL;
v_description VARCHAR2 (100) := NULL;
BEGIN
SELECT fa.application_short_name, fr.responsibility_key,
fsg.security_group_key, frt.description
INTO v_application_name, v_responsibility_key,
v_security_group, v_description
FROM apps.fnd_responsibility fr,
fnd_application fa,
fnd_security_groups fsg,
fnd_responsibility_tl frt
WHERE frt.responsibility_name = v_responsibility_name
AND frt.LANGUAGE = USERENV ('LANG')
AND frt.responsibility_id = fr.responsibility_id
AND fr.application_id = fa.application_id
AND fr.data_group_id = fsg.security_group_id
AND rownum<=1;
fnd_user_pkg.addresp (username => v_user_name,
resp_app => v_application_name,
resp_key => v_responsibility_key,
security_group => v_security_group,
description => v_description,
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsiblity ' || v_responsibility_name|| ' is attached to the user '|| v_user_name || ' Successfully');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unable to attach responsibility to user due to'|| SQLCODE|| ' ' || SUBSTR (SQLERRM, 1, 250));
END;
BEGIN
v_status := fnd_user_pkg.ChangePassword ( username => v_user_name, newpassword => v_new_password );
IF v_status =TRUE THEN
dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END IF;
---
IF v_add_resp = 'Y' THEN
Add_responsibility(p_user_name => v_user_name,p_responsibility_name => 'Functional Administrator');
Add_responsibility(p_user_name => v_user_name,p_responsibility_name => 'XML Publisher Administrator');
Add_responsibility(p_user_name => v_user_name,p_responsibility_name => 'Application Developer');
Add_responsibility(p_user_name => v_user_name,p_responsibility_name => 'Workflow Administrator');
Add_responsibility(p_user_name => v_user_name,p_responsibility_name => 'System Administrator');
Add_responsibility(p_user_name => v_user_name,p_responsibility_name => 'Alert Manager');
Add_responsibility(p_user_name => v_user_name,p_responsibility_name => 'Desktop Integration Manager');
END If;
END;
