API to Attach Responsiblity

DECLARE

   l_user_name                VARCHAR2 (500) := 'BIJOYJ';

   l_effective_date           DATE := TO_DATE ('01-JAN-2014');

   l_error                    VARCHAR2 (500) := NULL;

   l_responsibility_name      VARCHAR2 (1000) := NULL;

   l_responsibility_key       VARCHAR2 (150) := NULL;

   l_application_short_name   VARCHAR2 (150) := NULL;

BEGIN

   BEGIN

      SELECT fr.responsibility_key,

             fa.application_short_name,

             frt.responsibility_name

        INTO l_responsibility_key,

             l_application_short_name,

             l_responsibility_name

        FROM fnd_responsibility fr,

             fnd_responsibility_tl frt,

             fnd_application fa

       WHERE fr.responsibility_id = frt.responsibility_id

             AND fa.application_id = fr.application_id

             AND UPPER (frt.responsibility_name) =

                    'XXX HR EMPLOYEE SELF-SERVICE'

             AND frt.LANGUAGE = 'US';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_responsibility_key := NULL;

         l_responsibility_name := NULL;

   END;

   IF l_responsibility_key IS NOT NULL

   THEN

      BEGIN

         fnd_user_pkg.addresp (username         => l_user_name,

                               resp_app         => l_application_short_name,

                               resp_key         => l_responsibility_key,

                               security_group   => 'STANDARD',

                               description      => NULL,

                               start_date       => l_effective_date,

                               end_date         => NULL);

         COMMIT;

         DBMS_OUTPUT.

          put_line (

            l_responsibility_name || ' has been attached to ' || l_user_name);

      EXCEPTION

         WHEN OTHERS

         THEN

            l_error := SUBSTR (SQLERRM, 0, 500);

            DBMS_OUTPUT.put_line ('Failure: ' || l_error);

      END;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;