Jithin's Oracle Tuning Corner

The Oracle Weblog

Tag Archives: FND_USER

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


Get every new post delivered to your Inbox.

%d bloggers like this: