Introduction:-
In this article, we will learn about API to reset password and add/end responsibility to user in oracle apps. To reset password and responsibility below API will is very useful. Put the username once and it will reuse till all task done.
Here I am adding the API to reset the password for EBS user.
DECLARE
lb_status BOOLEAN;
v_username varchar2(100):='&&Username';
v_password varchar2(200);
BEGIN
select user_password into v_password from ace_users where user_code=v_username ;
if v_password is not null then
lb_status := FND_USER_PKG.CHANGEPASSWORD
(
username => v_username,
newpassword => v_password
);
end if ;
IF lb_status
THEN
DBMS_OUTPUT.PUT_LINE('Request processed sucessfully');
ELSE
DBMS_OUTPUT.PUT_LINE('Error while processing the request');
END IF;
END;
/Now will see how API to end date user responsibility in oracle apps.
DECLARE
v_user_name VARCHAR2 (100) := '&&Username';
v_responsibility_name VARCHAR2 (100) := 'Sourcing Supplier';
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;
/Next API is to add responsibility to user in oracle apps.
DECLARE
v_user_name VARCHAR2 (30) := '&&Username';
v_responsibility_name VARCHAR2 (100) := 'ACE TA/DA Users';
v_application_name VARCHAR2 (100) := NULL;
v_responsibility_key VARCHAR2 (100) := NULL;
v_security_group VARCHAR2 (100) := NULL;
v_description VARCHAR2 (100) := NULL;
BEGIN
SELECT fa.application_short_name, fr.responsibility_key,
fsg.security_group_key, frt.description
INTO v_application_name, v_responsibility_key,
v_security_group, v_description
FROM apps.fnd_responsibility fr,
fnd_application fa,
fnd_security_groups fsg,
fnd_responsibility_tl frt
WHERE frt.responsibility_name = v_responsibility_name
AND frt.LANGUAGE = USERENV ('LANG')
AND frt.responsibility_id = fr.responsibility_id
AND fr.application_id = fa.application_id
AND fr.data_group_id = fsg.security_group_id;
fnd_user_pkg.addresp (username => v_user_name,
resp_app => v_application_name,
resp_key => v_responsibility_key,
security_group => v_security_group,
description => v_description,
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line( 'Responsiblity '
|| v_responsibility_name
|| ' is attached to the user '
|| v_user_name
|| ' Successfully'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Unable to attach responsibility to user due to'
|| SQLCODE
|| ' '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
/
SHOW ERR;
exit;