Group Team

This article helps in deleting a Responsibility to an Oracle user using API FND_USER_PKG.DELRESP.

This API is tested in R12.1.3

We will try to delete responsibility ‘System Administrator’ for the user ‘TEST_USER’ using the API.
Now lets run the below API

DECLARE
   v_user_name                  VARCHAR2 (100) := 'TEST_USER';
   v_responsibility_name  VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key      VARCHAR2 (100) := NULL;
   v_security_group           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.delresp (username          => v_user_name,
                                              resp_app           => v_application_name,
                                              resp_key            => v_responsibility_key,
                                              security_group => v_security_group
                                             );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is removed from the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while deleting responsibilty from the user and the error is '
          || SQLERRM
         );
END;

Output we got after execution is
Responsiblity System Administrator is removed from the user TEST_USER Successfully.

We can cross verify if the responsibility is attached to the user ‘TEST_USER’ by using the following query

SELECT  count(1)
  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 = 'TEST_USER'
   AND    frt.responsibility_name = ‘System Administrator’



Labels: , edit post