Adding Mutiple Responsibilty to Mutiple User from Back End in Oracle Apps
With the following we can add multiple user name and multiple responsibilities through backend.This script can be used to assign multiple responsibilities to multiple user in Oracle Apps
Declare
cursor get_user
is
select USER_NAME
from fnd_user
where END_DATE is null
and LAST_UPDATED_BY not in (1,0)
and USER_ID not in (0,1110,1120,1606,1826,2126,2608,2869,3030)
--and user_id =3030--ERP_SUPPORT
ORDER BY 1;
cursor create_responsibilities
is
select resp.responsibility_key
,resp.responsibility_name
,app.application_short_name
from fnd_responsibility_vl resp,
fnd_application app
where resp.application_id = app.application_id
and resp.responsibility_name in ( 'XX ERP Issue Register','System Administrator' ) ;
begin
for c_user in get_user loop
for get_resp in create_responsibilities
loop
fnd_user_pkg.addresp (
username => c_user.user_name
,resp_app => get_resp.application_short_name
,resp_key => get_resp.responsibility_key
,security_group => 'STANDARD'
,description => null
,start_date => sysdate
,end_date => null);
dbms_output.put_line('Responsibility '||get_resp.responsibility_name||' added !!!!!!');
end loop;
end loop;
commit;
exception
when others then
dbms_output.put_line ('Exception : '||SUBSTR(SQLERRM, 1, 500));
rollback;
end;
cursor get_user
is
select USER_NAME
from fnd_user
where END_DATE is null
and LAST_UPDATED_BY not in (1,0)
and USER_ID not in (0,1110,1120,1606,1826,2126,2608,2869,3030)
--and user_id =3030--ERP_SUPPORT
ORDER BY 1;
cursor create_responsibilities
is
select resp.responsibility_key
,resp.responsibility_name
,app.application_short_name
from fnd_responsibility_vl resp,
fnd_application app
where resp.application_id = app.application_id
and resp.responsibility_name in ( 'XX ERP Issue Register','System Administrator' ) ;
begin
for c_user in get_user loop
for get_resp in create_responsibilities
loop
fnd_user_pkg.addresp (
username => c_user.user_name
,resp_app => get_resp.application_short_name
,resp_key => get_resp.responsibility_key
,security_group => 'STANDARD'
,description => null
,start_date => sysdate
,end_date => null);
dbms_output.put_line('Responsibility '||get_resp.responsibility_name||' added !!!!!!');
end loop;
end loop;
commit;
exception
when others then
dbms_output.put_line ('Exception : '||SUBSTR(SQLERRM, 1, 500));
rollback;
end;