FND_USER Scripts

Create User:

DECLARE

  v_user_name  VARCHAR2(30):=UPPER('TGSWAMY');

  v_password   VARCHAR2(30):='welcome12';

  v_session_id INTEGER     := USERENV('sessionid');

BEGIN

  fnd_user_pkg.createuser (

    x_user_name => v_user_name,

    x_owner => NULL,

    x_unencrypted_password => v_password,

    x_session_number => v_session_id,

    x_start_date => SYSDATE,

    x_end_date => NULL

  );

  COMMIT;

  DBMS_OUTPUT.put_line ('User: '||v_user_name||'Created Successfully');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

END;

Add Responsibility:

BEGIN

fnd_user_pkg.addresp ('TGSWAMY','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);

commit;

dbms_output.put_line('Responsibility Added Successfully');

exception WHEN others THEN

       dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));

       ROLLBACK;

END;

Reset Password for User:

DECLARE

      l_ret_val BOOLEAN;

      l_user_name   varchar2(50) := 'TGSWAMY';

      l_new_pwd     varchar2(20) := 'welcome123';

BEGIN

      l_ret_val := fnd_user_pkg.changepassword(username=> l_user_name,newpassword => l_new_pwd);

     IF l_ret_val

     THEN

        DBMS_OUTPUT.PUT_LINE('The password is successfully reset to '||  l_new_pwd);

        COMMIT;

     ELSE

        DBMS_OUTPUT.PUT_LINE('The password reset has failed');

     END IF;

END;