For most of consultants face adding sysadmin responsibility access
issue in Development or Test instance which got refreshed from PROD/UAT, their
responsibilities get refreshed and all development team will lose credentials
of System Administrator responsibility, so I found some easy way to add system
administrator responsibility for all those who have access to APPS schema.
Here is the piece of code.
/*-------------------------------------------------------*/
DECLARE
lv_user_name
FND_USER.USER_NAME%TYPE ='ANIL.BEJUGAM'; --Change your user name here
lv_resp_app fnd_responsibility.responsibility_key%TYPE;
lv_resp_key fnd_application.application_short_name%TYPE;
BEGIN
SELECT fr.responsibility_key,
fa.application_short_name
INTO lv_resp_key, lv_resp_app
FROM fnd_application
fa
,fnd_responsibility fr
,fnd_responsibility_tl frl
WHERE fr.application_id
= frl.application_id
AND fa.application_id
= fr.application_id
AND fr.responsibility_id
= frl.responsibility_id
AND frl.responsibility_name
= ' System Administrator'; -- Change responsibility name if you want
to add some other responsibility name
fnd_user_pkg.addresp
(username => lv_user_name
,resp_app => lv_resp_app
,resp_key => lv_resp_key
,security_group =>'STANDARD'
,description => NULL
,start_date => SYSDATE
,end_date => NULL);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
(SQLERRM);
END;
