How to Create, Disable user, Add Responsibility and Reset Password in Oracle Apps from Backend

FND_USER_PKG is built-in API in Oracle apps for user management from the back-end. In this article, I will share how to use this API for common user management task like user creation, reset password, adding and deleting responsibility.

There are many methods available, but mostly we will focus on common methods.

create_user

Use this API to create application user in Oracle Apps.

Syntax:

procedure CreateUser (
x_user_name in varchar2,
x_owner in varchar2,
x_unencrypted_password in varchar2 default null,
x_session_number in number default 0,
x_start_date in date default sysdate,
x_end_date in date default null,
x_last_logon_date in date default null,
x_description in varchar2 default null,
x_password_date in date default null,
x_password_accesses_left in number default null,
x_password_lifespan_accesses in number default null,
x_password_lifespan_days in number default null,
x_employee_id in number default null,
x_email_address in varchar2 default null,
x_fax in varchar2 default null,
x_customer_id in number default null,
x_supplier_id in number default null);

Parameters are self-explanatory by their name and most of them accept default input value. You should pass minimum x_user_name, x_owner and x_unencrypted_password to keep it simple. Below example creates DEMO_USER with default password welcome.

SET serveroutput ON;
DECLARE
 l_user_name VARCHAR2(30) := 'DEMO_USER';
 l_unencrypted_password VARCHAR2(30) := 'welcome';
BEGIN
 FND_USER_PKG.CreateUser ( x_user_name => l_user_name
 , x_owner =>NULL 
 , x_unencrypted_password => l_unencrypted_password);
EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line('Error while creating user ' || SQLERRM);
END;
/

You can query fnd_user table from backend to check if the user created or not. Let’s log in to Application using new user and password. It will ask to change the password as the first-time login.

You can see there are no responsibilities added to the user. Let’s see further in the article how to add responsibility to the user.

AddResp

Use this API to add a valid responsibility to the user.

Syntax:

procedure AddResp(username varchar2,
 resp_app varchar2,
 resp_key varchar2,
 security_group varchar2,
 description varchar2,
 start_date date,
 end_date date);

 

Below is working example which adds Application Developer to DEMO_USER.

BEGIN
 fnd_user_pkg.Addresp('DEMO_USER' , 'FND' , 'APPLICATION_DEVELOPER' , 'STANDARD' , 'Test' , SYSDATE , NULL);
END;

 

Use below query to get resp_app – Responsibility Application ,resp_key.

SELECT fa.application_short_name, 
 fa.application_name, 
 fr.responsibility_key, 
 fr.responsibility_name 
FROM fnd_application_vl fa, 
 fnd_responsibility_vl fr 
WHERE fr.application_id = fa.application_id 
ORDER BY fr.responsibility_name; 

changepassword

Use this API to change the user password. There is no need to mention an old password.

DECLARE
 l_ret_status BOOLEAN;
BEGIN
 l_ret_status := fnd_user_pkg.ChangePassword(username => 'DEMO_USER' 
 ,newpassword => 'welcome123');
 IF l_ret_status THEN
 dbms_output.put_line('Password changed successfully ');
 ELSE
 dbms_output.put_line('Error while changing password ');
 END IF;
 
EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line('Error while changing password ' || SQLERRM);
END;

 

disableuser

Use this API to disable a user.

BEGIN
 fnd_user_pkg.disableuser(username => 'DEMO_USER') ;
END;

I hope this article on fnd_user_pkg and examples is helpful.