Friday, January 9, 2015

Script to Create New FND_USER and Assign Responsilities and also Update/Create Profile For ICX_FORMS_LAUNCHER

------------------------------------------------------------------------------------------------
-- Replace the Value for l_user_name for creating multiple users and assign the responsibilities
------------------------------------------------------------------------------------------------

DECLARE
   l_user_name                VARCHAR2 (100);
   l_user_password            VARCHAR2 (100) := 'welcome1';
   l_user_start_date          DATE := SYSDATE;
   l_user_end_date            VARCHAR2 (100) := NULL;
   l_password_date            VARCHAR2 (100) := SYSDATE;
   l_password_lifespan_days   NUMBER;
   l_person_id                NUMBER;
   l_email_address            VARCHAR2 (100);
   l_count                    NUMBER;
   l_change_password          BOOLEAN;
   l_user_id                  NUMBER;

   PROCEDURE assign_responsibility (p_user_name IN VARCHAR2)
   IS
      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 ('US Receivables Super User',
                                                'US Service Contracts Super User',
                                                'US Order Management Super User',
                                                'Installed Base User');

      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';
      l_count             NUMBER;
   BEGIN
      FOR resp_rec IN c_resp
      LOOP
         SELECT COUNT (1)
           INTO l_count
           FROM fnd_user fuser,
                per_people_f per,
                fnd_user_resp_groups furg,
                fnd_responsibility_tl frt
          WHERE     fuser.employee_id = per.person_id
                AND fuser.user_id = furg.user_id
                AND (   TO_CHAR (fuser.end_date) IS NULL
                     OR fuser.end_date > SYSDATE)
                AND frt.responsibility_id = furg.responsibility_id
                AND (   TO_CHAR (furg.end_date) IS NULL
                     OR furg.end_date > SYSDATE)
                AND frt.language = 'US'
                AND fuser.user_name = p_user_name
                AND frt.responsibility_name = resp_rec.responsibility_name;

         IF l_count = 0
         THEN
            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         => p_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 '
                  || p_user_name);
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (
                        'Responsibility '
                     || l_resp_name
                     || ' IS NOT added to the user '
                     || p_user_name
                     || ' due to '
                     || SQLCODE
                     || '; '
                     || SUBSTR (SQLERRM, 1, 250));
                  DBMS_OUTPUT.put_line ('');
                  ROLLBACK;
            END;
         ELSE
            DBMS_OUTPUT.put_line (
                  'Responsibility : '
               || resp_rec.responsibility_name
               || ' Already assigned');
         END IF;
      END LOOP;
   END;

   PROCEDURE update_profile (p_user_id IN NUMBER)
   IS
      l_result   BOOLEAN;
   BEGIN
      l_result :=
         fnd_profile.save (
            x_name                 => 'ICX_FORMS_LAUNCHER',
            x_value                => 'https://www.google.com',
            x_level_name           => 'USER',
            x_level_value          => p_user_id,
            x_level_value_app_id   => NULL,
            x_level_value2         => NULL);

      IF l_result
      THEN
         DBMS_OUTPUT.put_line ('Profile Updated');
      ELSE
         DBMS_OUTPUT.put_line ('Profile Not Updated');
      END IF;

      COMMIT;
   END;
BEGIN
   fnd_global.apps_initialize (17959, 20420, 1);

   FOR i IN 1 .. 200
   LOOP
      l_user_name := 'TESTUSER' || i;
      fnd_user_pkg.createuser (
         x_user_name                => l_user_name,
         x_owner                    => NULL,
         x_unencrypted_password     => l_user_password,
         x_start_date               => l_user_start_date,
         x_end_date                 => l_user_end_date,
         x_password_date            => l_password_date,
         x_password_lifespan_days   => l_password_lifespan_days,
         x_employee_id              => 12345,
         x_email_address            => 'xxxx@gmail.com');

      COMMIT;

      SELECT user_id
        INTO l_user_id
        FROM fnd_user
       WHERE user_name = l_user_name;

      IF l_user_id IS NOT NULL AND l_user_id > 0
      THEN
         assign_responsibility (l_user_name);
         update_profile (l_user_id);

         BEGIN
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_NORMAL_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name, 'CSI_ADMIN_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_READ_ONLY_USER');
            COMMIT;
            DBMS_OUTPUT.put_line ('Assign IB Roles Successfully!!!');
         END;

         l_change_password :=
            fnd_user_pkg.changepassword (l_user_name, 'welcome123');

         IF l_change_password
         THEN
            DBMS_OUTPUT.put_line ('Password Changed successfully!!!');
            COMMIT;
         ELSE
            DBMS_OUTPUT.put_line ('Unable to Change Password!!!');
            ROLLBACK;
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (
               'User : '
            || l_user_name
            || ' Not found, hence unable to assign the responsibilities');
      END IF;

      l_user_name := NULL;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

1 comment:

Note: Only a member of this blog may post a comment.