Selectively nulling USER_GUID values on EBS

Two previous posts ( this and this) dealt with nulling USER_GUIDs in the FND_USER table on Oracle’s EBS (Enterprise Business Suite) in a post clone situation. This is done for the purpose of getting synchronization between EBS and OID (Oracle Internet Directory) working. In this post I’ll share a technique discovered by my colleague Carl for doing things a bit more selectively to avoid whacking users who are already working OK.

For the most part you’re only going to need to do this in the immediate aftermath of cloning an EBS instance. As described in the previous posts, in their infinite wisdom Oracle didn’t provide for the clearing of USER_GUID values from user records in FND_USER as part of the cloning process. This even though everyone knows the resulting cloned FND_USER will then contain the USER_GUID values from the source EBS, which are not going to match the orclguid values in the target OID (unless it is the same OID as the source EBS was integrated with — not likely).

So here’s the trick. All you need to do to be more surgical about nulling those USER_GUID values is to eyeball the orclguid values on OID and note how they begin. We recommend looking at the first 5 characters at least.

Something like:

SQL> update FND_USER
SQL> set USER_GUID = ''
SQL> where USER_GUID >= '7056F%';