Change user_name in FND_USER from back-end

PROBLEM: Identify a method to update the existing usernames in FND_USER to a value matching the Active Directory username. This is required for implementing stuff like Single Sign On (SSO). The process works fine when done from ERP Application front end, however a simple update of the FND_USER table from back-end does not work.

CAUSE: The reason why a simple update of FND_USER doesn’t work is that Oracle uses Workflows (WF) to maintain this information and when we update the FND_USER the foreign key references in the Workflow are not updated automatically.

SOLUTION:We can use the oracle provided procedure APPS.FND_USER_PKG.CHANGE_USER_NAME () to change the username as well as update the related WF tables.

I tested this on my username SARATHJ and changed it to SARATHJ1. All the responsibilities that existed for the user SARATHJ still exists for the changes user SARATHJ1. I validated this from both back-end and front-end. I could also login to the Application with my old password and work. A script can be written using this API and we can use SQL Loader to import data from a spreadsheet (.xls file) into a table in the Database. Then the API can be called in a loop to update the records. A pseudo code for the same is given below:

1. Place the usernames.xls in the interface directory

2. Run SQL Loader and import data into a loading table in the Database.

3.  SQL Script

a. For each row in loading table, call the API.
b. COMMIT every 500 rows

2 thoughts on “Change user_name in FND_USER from back-end

    • A quick example for a single user would be something like below:


      Before and after executing the procedure, you can check FND_USERS to see the current name and new name. You may also use user_name column in WF_LOCAL_USER_ROLES tables for this check.

      BTW, what is the version of EBS that you intend to use this on?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.