Oracle E-Business Suite: Kullanıcılara Sql Script'i ile Sorumluluk Atanması

Aşağıdakş sorguda belirtilecek kullanıcıya System_Administrator,Functional Developer ve Application Developer sorumlulukları atanır. Atanması istenen diğer sorumluluklar kırmızıyla işaretlenmiş kısmın içine eklenir.


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
                    ('System Administrator',
                     'Functional Administrator',
                     '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;