linking an OID orclguid with a USER_GUID in EBS

Unless you're actually looking for the answer this article provides, you probably won't have any idea what I'm talking about.

Here's the situation. You've put up an Oracle 10g infrastructure, complete with it's own Oracle Internet Directory (OID). By some unknown magic, your 10g infrastructure (including OID and Single Sign-on, SSO) has just been integrated with an existing Oracle Enteprise Business Suite (EBS) Applications instance.

Part of the integration involves setting up automatic provisioning of user accounts in EBS from entries created in OID. You create a new user in OID, and voila! the user gets an account (albeit one with limited access) in EBS.

Your DBAs, developers, consultants, admins and just about everyone who can draw a breath have been haphazardly creating and deleting accounts in both OID and EBS both before and after the integration. Ugh.

Finally, as expected, some of those accounts are now out of sync. You try to create a new account on EBS for an existing OID user and you get the dreaded "account already there" error. Checking the FND_USER table on the EBS database you find that, sure enough, it is there. But no matter how hard you try, you can't seem to get SSO to log you into EBS. The OID entry won't link to the EBS account. Instead of jumping up and down screaming, calm down. Here's what you do.

Log onto the server hosting EBS as the system user (something like "oracle" or "oraebs"). If the .bash_profile for this user doesn't set the environment for you, you'll need to source it -- hopefully from a file you've created like "ebs.env".

First get the orclguid value from the corresponding OID entry. Since this is a system attribute, it won't show up in a normal search. You need to specify it.

ldapsearch -h [oidhost] -D "cn=orcladmin" -w [adminpw] -b "dc=my,dc=corp" \
-s sub "(cn=[userid])" orclguid


This will return a long string of numbers and letters for orclguid. Highlight and copy it.

Now fire up sqlplus and connect to the EBS database as the "APPS" user.

sqlplus APPS/{appspw]@[dbname]


Now follow the following procedure:


SQL> SELECT USER_GUID FROM FND_USER
WHERE USER_NAME='[EBS User ID]';


Where 'USERID' is the ID of the user account you need to fix.

SQL> UPDATE FND_USER
SET USER_GUID = '[orclguid value]'
WHERE USER_NAME = '[EBS User ID]';
...
SQL> COMMIT;
...
SQL>quit


There it is.
Posted by on