PL/SQL API to attach responsibility to User from backend r12

FND_USER_PKG.ADDRESP API is used to attach a responsibility to a User in r12.

We are going to attach System Administrator responsibility to OPERATIONS user using below script.

DECLARE

   v_application_short_name   VARCHAR2 (100) := NULL;

   v_responsibility_key       VARCHAR2 (100) := NULL;

   v_security_group           VARCHAR2 (100) := NULL;

   v_description              VARCHAR2 (100) := NULL;

   v_user_name                VARCHAR2 (100) := 'OPERATIONS';

   v_responsibility_name      VARCHAR2 (100) := 'System Administrator';

BEGIN

   SELECT fap.application_short_name,

          frp.responsibility_key,

          frg.security_group_key,

          frt.description

     INTO v_application_short_name,

          v_responsibility_key,

          v_security_group,

          v_description

     FROM fnd_responsibility frp,

          fnd_application fap,

          fnd_security_groups frg,

          fnd_responsibility_tl frt

    WHERE frp.application_id = fap.application_id

      AND frp.responsibility_id = frt.responsibility_id

      AND frp.data_group_id = frg.security_group_id

      AND frt.LANGUAGE = USERENV ('LANG')

      AND frt.responsibility_name = v_responsibility_name;

   fnd_user_pkg.addresp (username            => v_user_name,

                         resp_app            => v_application_short_name,

                         resp_key            => v_responsibility_key,

                         security_group      => v_security_group,

                         description         => v_description,

                         start_date          => SYSDATE,

                         end_date            => NULL

                        );

  

   COMMIT;

   DBMS_OUTPUT.put_line (   v_responsibility_name

                         || ' Responsibility Added Successfully to user '

                         || v_user_name

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (   ' Failed to attach responsibility to user '

                            || SQLCODE

                            || SUBSTR (SQLERRM, 1, 100)

                           );

      ROLLBACK;

END;

To cross verify if the responsibility is attached to the user ‘OPERATIONS’ use the following query

SELECT fu.user_name,

       frt.responsibility_name

  FROM fnd_user fu,

       fnd_user_resp_groups furg,

       fnd_responsibility_tl frt

 WHERE fu.user_id = furg.user_id

   AND furg.responsibility_id = frt.responsibility_id

   AND fu.user_name = 'OPERATIONS'

Posted in: r12