API to Purge / Delete User Responsibility

Step 1: End Date Desired Responsibility using “fnd_user_pkg.delresp”

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 = 'BIJOYJ'

             AND UPPER(frt.responsibility_name) = 'DOPA GENERAL LEDGER SUPER USER';

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;

Step 2: Purge User and Assignment Roles

DECLARE

   CURSOR c1

   IS

      SELECT wlcu.user_name,

             wlcu.role_name,

             wlcu.user_orig_system,

             wlcu.user_orig_system_id,

             wlcu.role_orig_system,

             wlcu.role_orig_system_id

        FROM wf_local_user_roles wlcu, fnd_responsibility_tl frt

       WHERE     user_name = 'BIJOYJ'

             AND frt.responsibility_id = wlcu.role_orig_system_id

             AND frt.language = 'US'

             AND UPPER (frt.responsibility_name) = 'DOPA GENERAL LEDGER SUPER USER';

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         wf_directory.

          deleteuserrole (p_username           => i.user_name,

                          p_rolename           => i.role_name,

                          p_userorigsystem     => i.user_orig_system,

                          p_userorigsystemid   => i.user_orig_system_id,

                          p_roleorigsystem     => i.role_orig_system,

                          p_roleorigsystemid   => i.role_orig_system_id);

         COMMIT;

         DBMS_OUTPUT.

          put_line (i.role_name || ' has been Deleted from ' || i.user_name);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;

Note: Once the User and Assignment Roles are Purged from the System, the Responsibility would be automatically removed from User Roles and would be no longer available.

Also please note one cannot Purge User and Assignment Roles having Responsibility as Active