5 Replies Latest reply: Sep 18, 2010 11:10 PM by 679747 Branched to a new discussion. RSS

    How to create large number of Oracle R12 EBS users in mass?

    786907
      Hi all,

      Is there a way to create a very large number of users in Oracle R12 EBS and to assign them the same roles and responsibillties in mass? For example, lets say I need to create 3000
      new users to access the Oracle R12 Financials system. I know that I can login as SYSADMIN then create each user one by one with system administrator then user management tasks.
      BUT, how would I create 3000 users at one time? Is there a utility or script to do this?

      Thanks in advance,
      Scott
        • 1. Re: How to create large number of Oracle R12 EBS users in mass?
          Hussein Sawwan-Oracle
          Hi,

          Use FND_USER_PKG API.

          FND_USER_PKG
          http://forums.oracle.com/forums/search.jspa?threadID=&q=FND_USER_PKG&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

          Thanks,
          Hussein
          • 2. Re: How to create large number of Oracle R12 EBS users in mass?
            786907
            Thanks so how would I enable the creation of large number of users with FND_USER_PKG.CREATEUSER script to add 3000 different users at same time?
            If I have a list of users, is there a way to write a PL/SQL script and embed into a Linux shell script? Below is for one user but not sure if I can do this for 1000 user accounts:

            BEGIN
            for i in 1..1000 loop
            fnd_user_pkg.createuser(x_user_name => v_user_name
            *,x_owner => ''*
            *,x_unencrypted_password => 'welcome1'*
            *,x_session_number => v_session_id*
            *,x_start_date => SYSDATE - 10*
            *,x_end_date => SYSDATE + 100*
            *,x_last_logon_date => SYSDATE - 10*
            *,x_description => 'EBS user creation'*
            *,x_password_date => SYSDATE - 10*
            *,x_password_accesses_left => 10000*
            *,x_password_lifespan_accesses => 10000*
            *,x_password_lifespan_days => 10000*
            *,x_email_address => 'user@myemail.com'*
            *,x_fax => ''*
            *,x_customer_id => ''*
            *,x_supplier_id => '');*
            fnd_user_pkg.addresp(username       => v_user_name
            *,resp_app => 'SYSADMIN'*
            *,resp_key => 'SYSTEM_ADMINISTRATOR'*
            *,security_group => 'STANDARD'*
            *,description => 'Auto Assignment'*
            *,start_date => SYSDATE - 10*
            *,end_date => SYSDATE + 1000);*
            end loop;
            END;
            */*

            I get these errors:

            ERROR at line 3:
            ORA-06550: line 3, column 42:
            PLS-00201: identifier 'V_USER_NAME' must be declared
            ORA-06550: line 3, column 3:
            PL/SQL: Statement ignored
            ORA-06550: line 19, column 43:
            PLS-00201: identifier 'V_USER_NAME' must be declared
            ORA-06550: line 19, column 4:
            PL/SQL: Statement ignored
            • 3. Re: How to create large number of Oracle R12 EBS users in mass?
              679747
              use this code
              declare
              v_user_name := 'euser' ;
              v_session_id := sid;// Pass here sid from this query SELECT username, sid, serial#, status FROM v$session WHERE username = 'APPS';
              BEGIN
              for i in 1..1000 loop
              fnd_user_pkg.createuser(x_user_name => v_user_name||i
              ,x_owner => ''
              ,x_unencrypted_password => 'welcome1'
              ,x_session_number => v_session_id
              ,x_start_date => SYSDATE - 10
              ,x_end_date => SYSDATE + 100
              ,x_last_logon_date => SYSDATE - 10
              ,x_description => 'EBS user creation'
              ,x_password_date => SYSDATE - 10
              ,x_password_accesses_left => 10000
              ,x_password_lifespan_accesses => 10000
              ,x_password_lifespan_days => 10000
              ,x_email_address => 'user@myemail.com'
              ,x_fax => ''
              ,x_customer_id => ''
              ,x_supplier_id => '');
              fnd_user_pkg.addresp(username => v_user_name||i
              ,resp_app => 'SYSADMIN'
              ,resp_key => 'SYSTEM_ADMINISTRATOR'
              ,security_group => 'STANDARD'
              ,description => 'Auto Assignment'
              ,start_date => SYSDATE - 10
              ,end_date => SYSDATE + 1000);
              end loop;
              END;
              /
              Thanks
              AJ
              • 4. Re: How to create large number of Oracle R12 EBS users in mass?
                786907
                Is there any way to pipe in a list of 1000 users from an Excel spreadsheet to the script to generate the new users?

                I know that there is a third party tool called Data Load that can do this for Oracle R12 EBS to generate the new user accounts in mass.
                • 5. Re: How to create large number of Oracle R12 EBS users in mass?
                  679747
                  Hi

                  The easiest solution will be to load the data into a table in your database from excel spread sheet through SQL Loader tool and then you can use this table to pipe the user data.

                  For information regarding SQL Loader you may go through
                  http://download.oracle.com/docs/html/A97312_01/sql_lodr.htm

                  It may be possible through WebAdi too but it would be more complex you may refer below link

                  http://www.aboutoracleapps.com/2007/07/web-adi.html


                  Thanks
                  AJ