Oracle API’s to Create User,Reset Password and Add Responsibility

I have created few queries using Oracle provided package:’FND_USER_PKG’. These queries might be very useful when you donot have the Oracle Apps front end access or you like to get in done through backend.

Using the below query, you can create a User in Oracle application.Just pass username, password and email id as parameters and it will create a user.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_password varchar2(30):='&Enter_Password';
v_session_id integer := userenv('sessionid');
v_email varchar2(30):=upper('&Enter_Email_Id');
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,
  x_email_address => v_email
  );
  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;

May a times we forgot the apps password. Then you can use the below query to resent the password just in few seconds.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
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;
end;
Use the below query to add a responsibility to a user. The advantage here is that you donot require system administrator responsibility access to add a responsibility.


declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_resp varchar2(30):='&Enter_Responsibility';
v_resp_key varchar2(30);
v_app_short_name varchar2(50);
begin
  select
    r.responsibility_key ,
    a.application_short_name
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
  where
    r.application_id =a.application_id
    and upper(r.responsibility_name) = upper(v_resp);

  fnd_user_pkg.AddResp (
  username => v_user_name,
  resp_app => v_app_short_name,
  resp_key => v_resp_key,
  security_group => 'STANDARD',
  description => null,
  start_date => sysdate,
  end_date => null
  );
  commit;
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
end;