CREATE User using Oracle FND User API
-- --------------------------------------
-- API to CREATE FND User
-- --------------------------------------
Using the below code, you can create a User in Oracle application and parameters are user name, password and email id .
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;
UPDATE User using Oracle FND User API
-- --------------------------------------
-- API to UPDATE FND User
-- --------------------------------------
To update email Id from ORA.User@abc.com to ORA.User@xyz.com
DECLARE
v_user_name VARCHAR2 (100) := 'ORA_USER';
v_password VARCHAR2 (100) := 'Oracle123';
v_user_start_date DATE := TO_DATE ('01-OCT-2015');
v_user_end_date VARCHAR2 (100) := NULL;
v_password_date VARCHAR2 (100) := TO_DATE ('01-NOV-2015');
v_password_lifespan_days NUMBER := 90;
v_email_address VARCHAR2 (100) := 'ORA_USER@xyz.com';
BEGIN
fnd_user_pkg.updateuser
(x_user_name => v_user_name,
x_owner => NULL,
x_unencrypted_password => v_password,
x_start_date => v_user_start_date,
x_end_date => v_user_end_date,
x_password_date => v_password_date,
x_password_lifespan_days => v_password_lifespan_days,
x_employee_id => NULL,
x_email_address => v_email_address
);
DBMS_OUTPUT.put_line ('User:' || v_user_name || ' Updated Successfully');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
You can use the below code to reset the password.
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;
ADD Responsibility to User using Oracle FND User API
-- --------------------------------------
-- API to ADD Responsibility FND User
-- --------------------------------------
DECLARE
v_user_name VARCHAR2 (100) := 'ORA_USER';
v_resp_appl_short_name VARCHAR2 (100) := 'FND';
v_responsibility_key VARCHAR2 (100) := 'APPLICATION_DEVELOPER';
v_security_group_key VARCHAR2 (100) := 'STANDARD';
v_resp_start_date DATE := TO_DATE ('02-Oct-2015');
v_resp_end_date DATE := NULL;
BEGIN
fnd_user_pkg.addresp (username => v_user_name,
resp_app => v_resp_appl_short_name,
resp_key => v_responsibility_key,
security_group => v_security_group_key,
description => NULL,
start_date => v_resp_start_date,
end_date => v_resp_end_date
);
DBMS_OUTPUT.put_line ( 'Responsibility added to :'
|| v_user_name
|| ' Successfully'
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
END Date Responsibility to User using Oracle FND User API
-- --------------------------------------
-- API to Update Responsibility FND User
-- --------------------------------------
DECLARE
v_user_name VARCHAR2 (100) := 'ORA_USER';
v_responsibility_name VARCHAR2 (100) := 'Application Developer';
v_application_name VARCHAR2 (100) := NULL;
v_responsibility_key VARCHAR2 (100) := NULL;
v_security_group VARCHAR2 (100) := NULL;
BEGIN
SELECT fa.application_short_name, fr.responsibility_key,
frg.security_group_key
INTO v_application_name, v_responsibility_key,
v_security_group
FROM fnd_responsibility fr,
fnd_application fa,
fnd_security_groups frg,
fnd_responsibility_tl frt
WHERE fr.application_id = fa.application_id
AND fr.data_group_id = frg.security_group_id
AND fr.responsibility_id = frt.responsibility_id
AND frt.LANGUAGE = USERENV ('LANG')
AND frt.responsibility_name = v_responsibility_name;
fnd_user_pkg.delresp (username => v_user_name,
resp_app => v_application_name,
resp_key => v_responsibility_key,
security_group => v_security_group
);
COMMIT;
DBMS_OUTPUT.put_line ( 'Responsiblity '
|| v_responsibility_name
|| ' is removed from the user '
|| v_user_name
|| ' Successfully'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error encountered while deleting responsibilty from the user and the error is '
|| SQLERRM
);
END;
