How to Add Responsibility to User from Back-end in Oracle Apps 12.2
-----------------------------------------------------PROCEDURE AddResp(
username VARCHAR2, -- User Name --
resp_app VARCHAR2, -- Responsibility Application Short Name --
resp_key VARCHAR2, -- Responsibility Key --
security_group VARCHAR2, -- Security Group Id --
description VARCHAR2, -- Any Description --
start_date DATE, -- Strat Date -- Default SYSDATE --
end_date DATE -- End Date --Default NULL --
);
Here is the query to find the parameter values
--------------------------------------------------------
SELECT fa.application_short_name,
fr.responsibility_key,
fsg.security_group_key,
frt.description
FROM apps.fnd_responsibility fr,
fnd_application fa,
fnd_security_groups fsg,
fnd_responsibility_tl frt
WHERE frt.responsibility_name = 'System Administrator'
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;
API to add responsibility to user
-------------------------------------------------------------
-- You can use following api to add responsibility to the user you want. It --- will prompt you for the username and responsibility name.
-------------------------------------------------------------
DECLARE
v_user_name VARCHAR2 (30) := '&user_name';
v_responsibility_name VARCHAR2 (100) := '&responsibility_name';
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;
Once this is complete, Here is the query to verify whether the responsibility is attached to the user.
----------------------------------------------------------------
SELECT frt.responsibility_name
FROM fnd_user_resp_groups furg, fnd_user fu, fnd_responsibility_tl frt
WHERE fu.user_name = '&USER_NAME'
AND fu.user_id = furg.user_id
AND furg.responsibility_id = frt.responsibility_id;
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment
Note: only a member of this blog may post a comment.