Thursday, 22 June 2017
Oracle Technical Useful Scripts and Queries

User Management using FND_USER_PKG API in Oracle Apps

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 below mentioned common methods.

1. CreateUser
2. DisableUser
3. ChangePassword
4. DelResp
5. AddResp
6. EnableUser

FND_USER_PKG.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.

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 back-end to check if the user created or not.

Let’s login to Application using new user and password. It will ask to change the password as the first-time login.

Change Oracle Apps Password first login

no active responsibility present

There is no responsibility assigned to the user.

Let’s see further in the article how to add responsibility to the user.

FND_USER_PKG.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; 

responsibility added using fnd_user_pkg.addresp

FND_USER_PKG.CHANGEPASSWORD

Use this API to change user password. There is no need to mention 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;

 

FND_USER_PKG.DISABLEUSER

Use this API to disable user.

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

disableuser

Leave a Reply

%d bloggers like this: