Oracle will allow you to update a single users user name as everything is stored against the USER_ID (from the FND_USER table under APPLSYS) in the database.
The package below (XXXX_APPUSERMANAGER) allows you to specify an old user name and a new user name and does the switch for you (after validating that it should let you). The source code for the package is below;
create or replace package XXXX_APPUSERMANAGER is
procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
p_ReturnCode OUT NUMBER,
p_OldUserName IN VARCHAR2 default '',
p_NewUserName IN VARCHAR2 default '');
end XXXX_APPUSERMANAGER;
procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
p_ReturnCode OUT NUMBER,
p_OldUserName IN VARCHAR2 default '',
p_NewUserName IN VARCHAR2 default '');
end XXXX_APPUSERMANAGER;
And here is the required code for the package body;
create or replace package body XXXX_APPUSERMANAGER is
c_NOTFOUND constant number := -998;
c_BLANK constant number := -999;
/* ********** ********** ********** ********** ********** --
-- PRIVATE Routines (available only to this package) --
-- ********** ********** ********** ********** ********** */
function private_GetUser(p_Username in varchar2) return number as
cursor c_GetUser is
select u.user_id
from applsys.fnd_user u
where upper(u.user_name) = upper(p_UserName);
v_UserId applsys.fnd_user.user_id%type;
begin
open c_GetUser;
fetch c_GetUser
into v_UserId;
close c_GetUser;
if v_UserId is null then
if length(p_UserName) = 0 then
v_UserId := c_BLANK;
else
v_UserId := c_NOTFOUND;
end if;
end if;
return v_UserId;
end private_GetUser;
function private_emailSubjectTag return varchar2 as
v_Database v$database.name%TYPE;
begin
select distinct name into v_Database from v$database;
return '[User Admin - ' || nvl(v_Database, 'Null') || ']';
end private_emailSubjectTag;
function private_passwordGenerator return varchar2 as
-- This is not massively secure, but it's secure enough (providing you don't have external users!)
c_ALPHA constant varchar2(255) := '1DFC84XSW3ERTGBZA52KI9JM6LOPYH70N';
v_Position number := mod(to_number(to_char(sysdate, 'SSSSS')),
length(c_ALPHA));
v_Length number := mod(to_number(to_char(sysdate, 'SSSSS')), 4) + 6;
begin
return substr(c_ALPHA || c_ALPHA, v_Position, v_Length);
end;
/* ********** ********** ********** ********** ********** --
-- PUBLIC Routines (available globally) --
-- ********** ********** ********** ********** ********** */
procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
p_ReturnCode OUT NUMBER,
p_OldUserName IN VARCHAR2 default '',
p_NewUserName IN VARCHAR2 default '') as
pragma autonomous_transaction;
v_OldUserId number;
v_NewUserId number;
v_RowCount number;
v_Password varchar2(255);
v_EmailAddress applsys.fnd_user.email_address%TYPE;
v_URL varchar2(255) := 'http://%HOST%:8000/OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp&langCode=US&username=' ||
upper(p_NewUserName);
v_Host v$instance.HOST_NAME%TYPE;
procedure LogMessage(p_Text in varchar2) as
begin
apps.fnd_file.put_line(apps.fnd_file.log, p_Text);
end;
begin
LogMessage('START');
v_OldUserId := private_GetUser(p_OldUserName);
v_NewUserId := private_GetUser(p_NewUserName);
LogMessage('Renaming ' || p_OldUserName || '(' || to_char(v_OldUserId) ||
') to ' || p_NewUserName || '(' || to_char(v_NewUserId) || ')');
-- If the user details are blank raise an error
if (v_OldUserId = c_BLANK) or (Length(trim(p_NewUserName)) = 0) then
raise_application_error(-20005,
'ERROR: You cannot rename from/to a blank username!');
end if;
-- If the old user is not found then raise an error
if v_OldUserId = c_NOTFOUND then
raise_application_error(-20005,
'ERROR: The user "' || p_OldUsername ||
'" does not exist');
end if;
-- If the old user is not found then raise an error
if upper(p_OldUserName) = upper(p_NewUserName) then
raise_application_error(-20005,
'ERROR: Old and new usernames are the same!');
end if;
-- Get the email address, if there is no email address raise an error
select email_address
into v_EmailAddress
from applsys.fnd_user u
where u.user_id = v_OldUserId;
if v_EmailAddress is null then
raise_application_error(-20005,
'ERROR: Unable to update user "' ||
upper(p_OldUserName) ||
'" because they do not have a valid email address');
end if;
-- If the old user *is* found then raise an error
if v_NewUserId <> c_NOTFOUND then
raise_application_error(-20005,
'ERROR: The user "' || p_NewUserName ||
'" already exists');
end if;
-- Perform the rename
LogMessage('Updating user record (ID=' || to_char(v_OldUserId) || ')');
update applsys.fnd_user u
set u.user_name = upper(p_NewUserName)
where u.user_id = v_OldUserId;
v_RowCount := SQL%ROWCOUNT;
-- If anything other than a single record has been updated raise an error
if v_RowCount <> 1 then
raise_application_error(-20005,
'ERROR: Updating the user record has failed (' ||
to_char(v_RowCount) || ')');
end if;
-- Reset the users password. This is necessary because the encryption on the account includes
-- the Username in the key (so when we change the username we make it impossible for the user
-- to login - hence the resetting of the users password to a known value).
LogMessage('Initialising Oracle Application Suite (required for Password Change)');
execute immediate 'alter session set NLS_LANGUAGE = AMERICAN'; -- oddly we need these lines
execute immediate 'alter session set NLS_TERRITORY = AMERICA';
apps.fnd_global.apps_initialize(0, 20420, 1); -- switch to SYSADMIN under apps.
v_Password := private_passwordGenerator; -- this is a pretty poor generator, but is almost certainly secure enough for us.
-- Get the hostname (necessary to customise the URLs).
select lower(host_name) into v_Host from v$instance where rownum = 1;
LogMessage('Changing the users password');
fnd_user_pkg.UpdateUser(x_user_name => upper(p_NewUserName),
x_owner => 'SEED',
x_unencrypted_password => v_Password,
x_password_lifespan_days => '',
x_password_lifespan_accesses => '',
x_password_accesses_left => '');
-- Notify the user their account name and password have changed
XXXX_emailmanager.addEmail(p_From => v_EmailAddress,
p_To => v_EmailAddress,
p_Subject => private_emailSubjectTag ||
' User/Password Change Notification',
p_Body => '
Your username (which was "' ||
upper(p_OldUserName) ||
'") has been changed to "' ||
upper(p_NewUserName) ||
'". Your password has also been reset to "' ||
v_Password ||
'" (without the quotes).
Please click the Oracle link below to logon and change your password:
' ||
Replace(v_URL, '%HOST%', v_Host) ||
'
If you have any problems accessing the system please contact the IT Helpdesk.
');
XXXX_emailmanager.processMails;
commit;
p_ReturnMessage := 'OK';
p_ReturnCode := 0;
LogMessage('END');
exception
when others then
begin
p_ReturnMessage := '(' || TO_CHAR(SQLCODE) || ') ' || SQLERRM;
p_ReturnCode := 1;
logMessage('999 ERROR: (' || TO_CHAR(SQLCODE) || '): ' || SQLERRM);
XXXX_emailmanager.addEmail(p_From => 'errors@company.com',
p_To => 'errors@company.com',
p_Subject => private_emailSubjectTag ||
' Error',
p_Body => '999 ERROR: (' ||
TO_CHAR(SQLCODE) || '): ' ||
SQLERRM ||
'Please raise this issue with the helpdesk.');
XXXX_emailmanager.processMails;
rollback;
end;
end RenameUsers;
end XXXX_APPUSERMANAGER;
Now the bad news; this won't work (yes, I know that's a bit of a biggie). For copyright reasons several routines are missing from the package, the routine that sends the notification email to the user letting them know that their user name and password have been changed. In the packages these routines are referred to as:
XXXX_emailmanager.addEmail, and
XXXX_emailmanager.processMails
The former allows you to add an e-mail to a queue, the latter processes all emails in the queue (rather than waiting for an automated process to pick the new email up).
These are custom packages that sit on top of two packages from oracle; BASE64ENCODER and EMAILER. Unfortunately the links I have for these packages are no longer working but as this is pretty standard functionality that most people would want to do it shouldn't be too hard to find a replacement (or the packages themselves - try google).
One day it's my goal to write an installation script to get everything installed nicely, but that goal is not going to be met today (and as I'm updating this post 3 years later and I still haven't done it it's looking like "never" is the window of time this work will be dropping into!).
