Migrate 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"
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: 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;
/