Oracle EBS - Script to reset password and assign required responsibilities

 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;