--ADD RESPONSBILITY
DECLARE
v_user_name VARCHAR2 (100) := :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,
frg.security_group_key, frt.description
INTO v_application_name, v_responsibility_key,
v_security_group,
v_description
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.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
( 'Error encountered while attaching responsibilty to the user and the
error is '
|| SQLERRM
);
END;
--CROSS VERIFY
SELECT
frt.responsibility_name, frt.description
FROM fnd_user_resp_groups furg, fnd_user fu, fnd_responsibility_tl frt
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fu.user_name = :user_name
AND frt.responsibility_name = NVL (:responsibility_name, frt.responsibility_name)
ORDER BY frt.responsibility_name