To Find/Add/Remove Responsibilities assigned to a User in Bulk in ORACLE APPS

--------------------------------------------------------------------------------------------------------------
To get list of responsibilities assigned to a user
--------------------------------------------------------------------------
select fu.user_name,
  frt.responsibility_name,
  furg.start_date,
  furg.end_date
from fnd_user fu ,
  fnd_user_resp_groups_direct furg ,
  fnd_responsibility_vl frt
where fu.user_id                 = furg.user_id
and frt.responsibility_id        = furg.responsibility_id
and frt.application_id           = furg.responsibility_application_id
and nvl(furg.end_date,sysdate+1) > sysdate
and nvl(frt.end_date,sysdate +1) > sysdate
and fu.user_name                   = :p_user_name ---> fnd_user name is needed

--------------------------------------------------------------------------------------------------------------
To Add bulk of responsibilities to a user
--------------------------------------------------------------------------
set serveroutput on size 1000000;
    DECLARE
    v_user_name VARCHAR2 (100) := &1; ---> fnd_user name is needed
    v_responsibility_name VARCHAR2 (100);
    v_application_name VARCHAR2 (100) := NULL;
    v_responsibility_key VARCHAR2 (100) := NULL;
    v_security_group VARCHAR2 (100) := NULL;
    v_description VARCHAR2 (100) := NULL;
    CURSOR c1
    IS
    SELECT fa.application_short_name,
    fr.responsibility_key,
    frg.security_group_key,
    frt.description,
frt.responsibility_name
    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 IN (
'responsibility1',
'responsibility2',
'responsibility3',
'responsibility4'
);
    BEGIN
    BEGIN
           FOR C IN C1
      LOOP
       BEGIN
        fnd_user_pkg.addresp (username => v_user_name,
        resp_app => c.application_short_name,
        resp_key => c.responsibility_key,
        security_group => c.security_group_key,
        description => c.description,
        start_date => SYSDATE,
        end_date => NULL);
        COMMIT;
        DBMS_OUTPUT.put_line ('Responsibility ' || c.responsibility_name || ' is attached to the user '|| v_user_name
        || ' Successfully');
            EXCEPTION
        WHEN OTHERS
      THEN
        DBMS_OUTPUT.put_line ('Issue while calling standard api' ||SQLERRM);
       END;
      END LOOP;
     EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
      DBMS_OUTPUT.put_line ('NO data found while attaching responsibilty to the user and the error is '|| SQLERRM);
     WHEN OTHERS
     THEN
     DBMS_OUTPUT.put_line ('Error encountered while attaching responsibilty to the user and the error is '|| SQLERRM);
     END;
    END;
/

--------------------------------------------------------------------------------------------------------------------
To End date List of responsibilities assigned to a user
------------------------------------------------------------------------------
DECLARE
CURSOR c1
IS
SELECT fu.user_name,
fa.application_short_name,
frt.responsibility_name,
fr.responsibility_key,
fsg.security_group_key
FROM fnd_user_resp_groups_all ful,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_responsibility fr,
fnd_security_groups fsg,
fnd_application fa

WHERE fu.user_id = ful.user_id
AND frt.responsibility_id = ful.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fsg.security_group_id = ful.security_group_id
AND fa.application_id = ful.responsibility_application_id
AND frt.language = 'US'
AND fu.user_name = :p_user_name ---> fnd_user name is needed

AND RESPONSIBILITY_NAME IN
('responsibility1',
'responsibility2',
'responsibility3',
'responsibility4');


BEGIN
FOR i IN c1
LOOP
BEGIN
fnd_user_pkg.delresp (username => i.user_name,
resp_app => i.application_short_name,
resp_key => i.responsibility_key,
security_group => i.security_group_key);
COMMIT;
DBMS_OUTPUT.
put_line (
i.responsibility_name || ' has been End Dated Successfully !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Inner Exception: '
|| ' - '
|| i.responsibility_key
|| ' - '
|| SQLERRM);
END;
END LOOP;

EXCEPTION

WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;
----------------------------------------------------------------------------------------------------------------------