Skip to content

Migrate Existing IFS Cloud Users to Maintenix

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

As the IFSAPP schema user, use the following code to run a SQL script, against the IFSApp schema:

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

/********************************************************************************************   
* Script Filename: bulkCreateIFSAppUserIntoMaintenixSchema.sql
*
* Script Description: 
*   For existing IFS App clients, when they start to deploy Maintenix solution into their system 
*   they will need to add all existing users in IFS App into Maintenix schema.
*
*   We do not replicate IFS APP system users, that is all users starting with prefix 'IFS', into Maintenix.
*   Every record in fnd_user_tab will create a user record in Maintenix.
*   And every record in person_info_tab, if it does not have a fnd_user_tab record, will create an inactive user record in Maintenix.
*
*   We will use the new IFS_USER_API_PKG in Maintenix schema to create users. 
*   IFS schema should have already 'grant execute' to IFS_USER_API_PKG.
*   This script is to be executed in IFS schema.
*
**********************************************************************************************/

DECLARE
   CURSOR lcur_user IS
      SELECT 
         fnd_user_tab.identity,
         decode(fnd_user_tab.active, 'TRUE', 'FALSE', 'TRUE') as is_locked,
         person_info_tab.person_id,
         person_info_tab.first_name,
         person_info_tab.middle_name,
         person_info_tab.last_name,
         (SELECT VALUE
          FROM fnd_user_property_tab
          WHERE 
            lower(fnd_user_property_tab.name) = 'smtp_mail_address'
            AND
            fnd_user_property_tab.identity = fnd_user_tab.identity
            AND
            ROWNUM = 1
         ) AS email,
         (SELECT VALUE
          FROM comm_method_tab
          WHERE 
            lower(comm_method_tab.method_id) = 'e_mail'
            AND
            comm_method_tab.identity = fnd_user_tab.identity
            AND
            lower(comm_method_tab.name) = 'alert'
            AND
            ROWNUM = 1
         ) AS alert_email
      FROM fnd_user_tab    
      INNER JOIN person_info_tab
      ON 
         fnd_user_tab.identity = person_info_tab.person_id
      WHERE lower(fnd_user_tab.identity) NOT LIKE 'ifs%';    

   lrow_user lcur_user%ROWTYPE;

   CURSOR lcur_person IS
         SELECT 
         person_info_tab.person_id,
         person_info_tab.first_name,
         person_info_tab.middle_name,
         person_info_tab.last_name,
         (SELECT VALUE
          FROM comm_method_tab
          WHERE 
             lower(comm_method_tab.method_id) = 'e_mail'
            AND
            comm_method_tab.identity = person_info_tab.person_id
            AND
            lower(comm_method_tab.name) = 'alert'
            AND
            ROWNUM = 1
         ) AS alert_email
      FROM person_info_tab
      LEFT JOIN fnd_user_tab    
      ON 
         fnd_user_tab.identity = person_info_tab.person_id
      WHERE 
         fnd_user_tab.identity IS NULL
         AND
         lower(person_info_tab.person_id) NOT LIKE 'ifs%'
         AND
         lower(person_info_tab.first_name) NOT LIKE 'ifs%'   
         AND
         lower(person_info_tab.last_name) NOT LIKE 'ifs%';    

   lrow_person lcur_person%ROWTYPE;

   v_err_code NUMBER;
   v_err_msg  VARCHAR2(2000);

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

BEGIN
   -- create mx users based on fnd_user_tab, which are IFS APP users
   OPEN lcur_user;
   LOOP
      FETCH  lcur_user  INTO lrow_user;
      EXIT WHEN lcur_user%NOTFOUND;

      BEGIN
         dbms_output.put_line('Creating user : ' || lrow_user.identity || ' - ' || lrow_user.first_name || ' ' || lrow_user.last_name);
         maintenix.IFS_USER_API_PKG.create_user_v2(lrow_user.identity, 'IFSAPP', lrow_user.person_id);

         maintenix.IFS_USER_API_PKG.update_user(lrow_user.identity, 
                                                'IFSAPP', 
                                                lrow_user.is_locked, 
                                                a_first_name  => lrow_user.first_name,
                                                a_last_name   => lrow_user.last_name,
                                                a_middle_name => lrow_user.middle_name,
                                                a_email       => lrow_user.email,
                                                a_alert_email => lrow_user.alert_email
                                                );
         v_user_created := v_user_created + 1;
      EXCEPTION
         WHEN OTHERS THEN
            v_err_code := SQLCODE;
            v_err_msg  := substr(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;

   -- create inactive mx users based on person_info_tab, which do not login IFS APP. for example, pilots
   OPEN lcur_person;
   LOOP
      FETCH  lcur_person  INTO lrow_person;
      EXIT WHEN lcur_person%NOTFOUND;

      BEGIN
         dbms_output.put_line('Creating person : ' || lrow_person.person_id || ' - ' || lrow_person.first_name || ' ' || lrow_person.last_name);
         maintenix.IFS_USER_API_PKG.create_user_v2(lrow_person.person_id, 'IFSAPP', lrow_person.person_id);

         maintenix.IFS_USER_API_PKG.update_user(lrow_person.person_id, 
                                                'IFSAPP', 
                                                'false', 
                                                a_first_name  => lrow_person.first_name,
                                                a_last_name   => lrow_person.last_name,
                                                a_middle_name => lrow_person.middle_name,
                                                a_alert_email => lrow_person.alert_email
                                                );
         v_user_created := v_user_created + 1;
      EXCEPTION
         WHEN OTHERS THEN
            v_err_code := SQLCODE;
            v_err_msg  := substr(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_person;

   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;
/