API-to Create User,Reset Password and Responsibulity

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.

view sourceprint?

01

declare

02	v_user_name varchar2(30):=upper('&Enter_User_Name');

03	v_password varchar2(30):='&Enter_Password';

04	v_session_id integer := userenv('sessionid');

05	v_email varchar2(30):=upper('&Enter_Email_Id');

06

begin

07	fnd_user_pkg.createuser (

08	x_user_name => v_user_name,

09	x_owner => null,

10	x_unencrypted_password => v_password,

11	x_session_number => v_session_id,

12	x_start_date => sysdate,

13	x_end_date => null,

14	x_email_address => v_email

15

);

16

commit;

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

18

EXCEPTION

19	when others then

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

21

ROLLBACK;

22

end;

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

view sourceprint?

01

declare

02	v_user_name varchar2(30):=upper('&Enter_User_Name');

03	v_new_password varchar2(30):='&Enter_New_Password';

04	v_status boolean;

05

begin

06	v_status:= fnd_user_pkg.ChangePassword (

07	username => v_user_name,

08	newpassword => v_new_password

09

);

10	if v_status =true then

11	dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

12

commit;

13

else

14	DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

15

rollback;

16

END if;

17

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.

view sourceprint?

01

declare

02	v_user_name varchar2(30):=upper('&Enter_User_Name');

03	v_resp varchar2(30):='&Enter_Responsibility';

04	v_resp_key varchar2(30);

05	v_app_short_name varchar2(50);

06

begin

07

select

08	r.responsibility_key ,

09	a.application_short_name

10	into v_resp_key,v_app_short_name

11	from fnd_responsibility_vl r,

12	fnd_application_vl a

13

where

14	r.application_id =a.application_id

15	and upper(r.responsibility_name) = upper(v_resp);

16

17	fnd_user_pkg.AddResp (

18	username => v_user_name,

19	resp_app => v_app_short_name,

20	resp_key => v_resp_key,

21	security_group => 'STANDARD',

22	description => null,

23	start_date => sysdate,

24	end_date => null

25

);

26

commit;

27	DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);

28

EXCEPTION

29	when others then

30	DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

31

rollback;

32

end;