Skip to content

Migrate Existing Maintenix Users to IFS Cloud

If you have existing users configured in a Maintenix database and are starting to use IFS Cloud, migrate users into the IFSApps database.

As the IFSAPP schema user, use the following code to run an sql script against the Maintenix schema:

echo  quit | sqlplus IFSAPP/<db_user_pwd>@<hostname>:<port>/<SID> "@bulkCreateMxUserIntoIFSAppSchema"

/********************************************************************************************
* Script Filename: bulkCreateMxUserIntoIFSAppSchema.sql
*
* Script Description: 
*   When existing Maintenix customers are going to upgrade to co-deployed version, 
*   they will need to create all existing Maintenix users in IFS APP, and IFS APP will be the system of record for user records.

*   We will not replicate Maintenix system users to IFS App database.
*   That is 0-level user data, i.e. utl_user records with uti_id =0.
*   All user records will be added into fnd_user with active/inactive status. We will not create person only records in IFS APP.
*
*   We will use the following API: Fnd_User_API, Person_Info_API, Fnd_User_Property_API, Comm_Method_API
*   IFS schema should have already 'grant select' to utl_user table in maintenix schema
*   This script is to be executed in IFS schema
*
***********************************************************************************************/

DECLARE
   CURSOR lcur_user IS
      SELECT 
         username,
         -- when not locked, this is an active user.
         decode(locked_bool, 0, 'TRUE', 'FALSE') as is_active,
         first_name,
         middle_name,
         last_name,
         email_addr,
         alert_email_addr
      FROM maintenix.utl_user
      WHERE utl_id != 0;

   lrow_user lcur_user%ROWTYPE;

   v_step     VARCHAR2(50);
   v_err_code NUMBER;
   v_err_msg  VARCHAR2(2000);

   v_user_created NUMBER := 0;
   v_user_error NUMBER := 0;

   attr_       VARCHAR2(32000);
   info_       VARCHAR2(2000);
   objid_      VARCHAR2(2000);
   objversion_ VARCHAR2(2000);
   action_     VARCHAR2(100) := 'DO';
   username_   VARCHAR2(40);

BEGIN
   -- disable IFS APP to create mx user so the Fnd_User_API.New__() won't insert new record into mx schema
   maintenix.IFS_USER_API_PKG.enable_ifsapp_create_user('FALSE');

   OPEN lcur_user;
   LOOP
      FETCH  lcur_user  INTO lrow_user;
      EXIT WHEN lcur_user%NOTFOUND;

      BEGIN
         dbms_output.put_line('Creating user : ' || lrow_user.username || ' - ' || lrow_user.first_name || ' ' || lrow_user.last_name);
         username_ := UPPER(lrow_user.username);

         -- fnd_user_tab
         v_step := 'fnd_user_tab - ';
         Client_SYS.Clear_Attr(attr_);
         Client_SYS.Add_To_Attr('IDENTITY', username_, attr_);
         Client_SYS.Add_To_Attr('DESCRIPTION', lrow_user.first_name || ' ' || lrow_user.last_name, attr_);
         Client_SYS.Add_To_Attr('ACTIVE', lrow_user.is_active, attr_);
         Client_SYS.Add_To_Attr('WEB_USER', username_, attr_);
         Fnd_User_API.New__(info_, objid_, objversion_, attr_, action_);


         -- person_info_tab
         v_step := 'person_info_tab - ';
         Client_SYS.Clear_Attr(attr_);
         Client_SYS.Add_To_Attr('PERSON_ID', username_, attr_);
         Client_SYS.Add_To_Attr('NAME', lrow_user.first_name || ' ' || lrow_user.last_name, attr_);
         Client_SYS.Add_To_Attr('FIRST_NAME', lrow_user.first_name, attr_);
         Client_SYS.Add_To_Attr('MIDDLE_NAME', lrow_user.middle_name, attr_);
         Client_SYS.Add_To_Attr('LAST_NAME', lrow_user.last_name, attr_);
         Client_SYS.Add_To_Attr('CREATION_DATE', SYSDATE, attr_);
         Client_SYS.Add_To_Attr('PROTECTED', 'FALSE', attr_);
         Client_SYS.Add_To_Attr('DEFAULT_DOMAIN', 'TRUE', attr_);
         Client_SYS.Add_To_Attr('PARTY_TYPE_DB', 'PERSON', attr_);
         Client_SYS.Add_To_Attr('USER_ID', username_, attr_);
         Person_Info_API.New__(info_, objid_, objversion_, attr_, action_);


         -- fnd_user_property_tab
         v_step := 'fnd_user_property_tab - ';
         IF lrow_user.email_addr IS NOT NULL THEN
            Client_SYS.Clear_Attr(attr_);
            Client_SYS.Add_To_Attr('IDENTITY', username_, attr_);
            Client_SYS.Add_To_Attr('NAME', 'SMTP_MAIL_ADDRESS', attr_);
            Client_SYS.Add_To_Attr('VALUE', lrow_user.email_addr, attr_);
            Fnd_User_Property_API.New__(info_, objid_, objversion_, attr_, action_);
         END IF;

         -- comm_method_tab
         v_step := 'comm_method_tab - ';
         IF lrow_user.alert_email_addr IS NOT NULL THEN
            Client_SYS.Clear_Attr(attr_);
            Client_SYS.Add_To_Attr('IDENTITY', username_, attr_);
            Client_SYS.Add_To_Attr('PARTY_TYPE_DB', 'PERSON', attr_);
            Client_SYS.Add_To_Attr('NAME', 'ALERT', attr_);
            Client_SYS.Add_To_Attr('METHOD_ID', 'E_MAIL', attr_);
            Client_SYS.Add_To_Attr('VALUE', lrow_user.alert_email_addr, attr_);
            Comm_Method_API.New__(info_, objid_, objversion_, attr_, action_);
         END IF;

         -- fnd_user_role_tab
         v_step := 'fnd_user_role_tab - ';
         Client_SYS.Clear_Attr(attr_);
         Client_SYS.Add_To_Attr('IDENTITY', username_, attr_);
         Client_SYS.Add_To_Attr('ROLE', 'FND_WEBENDUSER_MAIN', attr_);
         Fnd_User_Role_API.New__(info_, objid_, objversion_, attr_, action_);

         v_user_created := v_user_created + 1;
      EXCEPTION
         WHEN OTHERS THEN
            v_err_code := SQLCODE;
            v_err_msg  := substr(v_step || v_err_code || ' ERROR: ' || SQLERRM, 1, 2000);
            dbms_output.put_line('Error : ' || v_err_msg);
            v_user_error := v_user_error + 1;
      END;

   END LOOP;
   CLOSE lcur_user;

   maintenix.IFS_USER_API_PKG.enable_ifsapp_create_user('TRUE');

   dbms_output.put_line('');
   dbms_output.put_line('Total successfully created user: ' || v_user_created);
   dbms_output.put_line('Total error creating user: ' || v_user_error);

END;
/