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