Monday, August 15, 2011

How to change User Name/Login name in Oracle EBS (FND_USER_PKG)

A very rare requirement, this is what I did to change the name for an active login. Oracle provides a user maintenance package FND_USER_PKG, this has a routine that allows login name change. For example, existing application user name is AMARKP and this needs to be changed to AMAR.PADHI.










() Before Change
The Application FND tables have below information.

SQL> select user_name, end_date from fnd_user 

  2  where user_name in ('AMARKP', 'AMAR.PADHI');

USER_NAME       |END_DATE
----------------|---------
AMARKP          |


SQL> select user_name, role_name from wf_local_user_roles
  2  where user_name in ('AMARKP', 'AMAR.PADHI');



USER_NAME              |ROLE_NAME
-----------------------|-------------------------------------
AMARKP                 |FND_RESP|SQLGL|MS_GL|STANDARD

AMARKP                 |FND_RESP|SYSADMIN|SYSTECH|STANDARD
AMARKP                 |FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD


() Performing the change
Below simple routine call is made to change the user name.


begin
  fnd_user_pkg.change_user_name(
                               x_old_user_name => 'AMARKP',
                               x_new_user_name => 'AMAR.PADHI'
                              );


  commit;
end;
/


() After change

SQL> select user_name, end_date from fnd_user
  2  where user_name in ('AMARKP', 'AMAR.PADHI');

USER_NAME         |END_DATE
------------------|---------
AMAR.PADHI        |

SQL> select user_name, role_name from wf_local_user_roles
  2  where user_name in ('AMARKP', 'AMAR.PADHI');

USER_NAME              |ROLE_NAME
-----------------------|-------------------------------------

AMAR.PADHI             |FND_RESP|SQLGL|MS_GL|STANDARD
AMAR.PADHI             |FND_RESP|SYSADMIN|SYSTECH|STANDARD
AMAR.PADHI             |FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD


The new user name is now being accepted at login time. Checked few old transactions created by the user, all are now showing the new user in Audit information.


No comments:

Post a Comment