In the PROD instance, there is a user X who has 10 responsibilities assigned. Now you want to assign these 10 responsibilities to another user Y. User Y already has 2 responsibility and want to copy remaining 8 responsibilities to user Y. You do not want to assign these responsibilities manually on the User Define form in System Administrator responsibility.
You can use a PL/SQL block similar to below to get it done.
DECLARE
resp_count NUMBER := 0;
CURSOR src_user_resps
IS
SELECT DISTINCT fa.application_short_name,
fr.responsibility_key ,
fsg.security_group_key
FROM fnd_application fa ,
fnd_responsibility fr ,
fnd_user fu ,
fnd_user_resp_groups_all furga,
fnd_security_groups fsg,
fnd_responsibility_tl frt
WHERE 1=1
AND fu.user_name = '&From_Username'
AND fu.user_id = furga.user_id
AND fa.application_id = fr.application_id
AND furga.responsibility_id = fr.responsibility_id
and furga.responsibility_id = frt.responsibility_id
AND furga.responsibility_application_id = fa.application_id
AND fsg.security_group_id = furga.security_group_id
AND furga.end_date IS NULL
minus
SELECT DISTINCT fa.application_short_name,
fr.responsibility_key ,
fsg.security_group_key
FROM fnd_application fa ,
fnd_responsibility fr ,
fnd_user fu ,
fnd_user_resp_groups_all furga,
fnd_security_groups fsg,
fnd_responsibility_tl frt
WHERE 1=1
AND fu.user_name = '&To_Username'
AND fu.user_id = furga.user_id
AND fa.application_id = fr.application_id
AND furga.responsibility_id = fr.responsibility_id
and furga.responsibility_id = frt.responsibility_id
AND furga.responsibility_application_id = fa.application_id
AND fsg.security_group_id = furga.security_group_id
AND furga.end_date IS NULL;
BEGIN
FOR user_resp_rec IN src_user_resps
LOOP
BEGIN
--
fnd_user_pkg.addresp
(username => '&To_Username',
resp_app => user_resp_rec.application_short_name,
resp_key => user_resp_rec.responsibility_key,
security_group => user_resp_rec.security_group_key,
description => 'Demo',
start_date => SYSDATE,
end_date => NULL
);
resp_count := resp_count + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_rec.application_short_name );
DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_rec.responsibility_key );
END;
END LOOP;
DBMS_OUTPUT.put_line (resp_count || ' Responsibilities copied Successfully!!' );
COMMIT;
END;
