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

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.

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');
  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
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
when others then
  DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

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

v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
 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);
  DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  END if;

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.

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);
    r.responsibility_key ,
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
    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
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

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

  1. JJ says:

    I get these error: ‘FND_USER_PKG.CREATEUSER’ must be declared

    • I have tested these scripts in R12.1.3 and its works fine. Which instance are you using for these scripts?

      Dibyajyoti Koch

    • mein says:

      i get the same error while using fnd_user_pkg.createuser from a non-apps user(i mean if i run this through some other user id not a APPS userid) , how can we use it from any user id instead of just using it through APPS user.

  2. Daniel Longmore says:

    These are really helpful. Any ideas on how might I web enable the reset password function? Present to the user a web page similar to the login page where they could reset their own password.

  3. Hi Daniel,

    This can be done by accessing the link ‘Forgot your password’ from the applications login page.

    Now how it works?

    a. A user access the apps login page.
    b. Select TIP Forgot your password? link
    c. A web page asks for the user name
    d. A new web page says:

    Your request for a new password is being processed.
    An email has been sent to the email address on file. Contact the system
    administrator if you do not receive your password within the next few hours

    e. A workflow process is started.
    f. Requester receives an workflow e-mail which subject is ‘Password reset requires approval.’
    g. The requester needs to respond the notification via e-mail.
    h. The workflow java mailer receives, reads and process the response.
    i. The User management workflow process changes the password to a automatically generated password and sends back an e-mail notification informing the new password.
    j. The users can now use the new password to access the EBS.
    k. The EBS requires the user to set his password again.

    How to implement the Forgot Password Reset Functionality?

    Please go through the steps mentioned in MOS Note:Reset Password Functionality FAQ [ID 399766.1]

    Hope It Helps…

  4. Asm says:


    I have used reset password package to change the user “operations” in EBS vision instance.Procedure successfully completed, but still able to login with old password.


  5. Pravin says:

    These scripts helped my team today. Thank you so much.

  6. abhistraj says:

    Can you tell me the scripting language used I.e which format, how to use it and all… I am a novice . thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: