Migrate 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"
Note: The code is not displayed in full. To view the code, hover over the code on the right end, click the Copy to clipboard icon and paste the code into a text editor.
/********************************************************************************************
* 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;
/