Tuesday 24 June 2014

API Script to add the responsibilities to a user

/* Formatted on 6/24/2014 12:35:52 PM (QP5 v5.115.810.9015) */
DECLARE
   CURSOR c_resp
   IS
      SELECT fav.application_short_name,
             fav.application_name,
             frv.responsibility_key,
             frv.responsibility_name
      FROM fnd_application_vl fav, fnd_responsibility_vl frv
      WHERE frv.application_id = fav.application_id
            AND frv.responsibility_name IN ('Application Developer');

   l_user_name         VARCHAR2 (15) := '&USER_NAME';
   l_appl_short_name   fnd_application_vl.application_short_name%TYPE;
   l_resp_name         fnd_responsibility_vl.responsibility_name%TYPE;
   l_resp_key          fnd_responsibility_vl.responsibility_key%TYPE;
   l_description VARCHAR2 (100)
         := 'Adding Responsibility to the user using script' ;
BEGIN
   FOR resp_rec IN c_resp
   LOOP
      l_appl_short_name   := resp_rec.application_short_name;
      l_resp_key          := resp_rec.responsibility_key;
      l_resp_name         := resp_rec.responsibility_name;

      BEGIN
         fnd_user_pkg.addresp (username => l_user_name,
                               resp_app => l_appl_short_name,
                               resp_key => l_resp_key,
                               security_group => 'STANDARD',
                               description => l_description,
                               start_date => SYSDATE,
                               end_date => NULL
         );
         COMMIT;
         DBMS_OUTPUT.put_line(   'The responsibility '
                              || l_resp_name
                              || ' is added to the user '
                              || l_user_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   'Responsibility '
                                 || l_resp_name
                                 || ' IS NOT added to the user '
                                 || l_user_name
                                 || ' due to '
                                 || SQLCODE
                                 || '; '
                                 || SUBSTR (SQLERRM, 1, 250));
            DBMS_OUTPUT.put_line ('');
            ROLLBACK;
      END;
   END LOOP;
END;

No comments:

Post a Comment