We're running an interface with our HR database where new users created on the HR database are automatically created on PPM using the standard open interface. This applies to updates where email addresses or end dates are automatically populated on PPM.
We've run into a problem where duplicate users have been created. The problem is very simple.
1.Some of the users on PPM were created manually on PPM workbench. Let's say the username was set as 'user'
2.The HR database containing the same user has the user named as 'User'.
3.Whenever a change/update occurs on the HR database for the user named 'User' a trigger is sent to PPM to update the said user.
4.PPM searches for the user 'User' and finds none and creates a new user.
5.The original user 'user' cannot log onto PPM because ppm detects a duplicate user.
6.The original user 'user' contacts PPM support looking for help. PPM administrator detects the problem but finds two users , one the original 'user' and second the new user 'User'. The administrator disables and new user 'User' but realizes the usernames are not case sensitive and renames the new user to 'User-new'.
7.The original user 'user' can now log in.
8.Updates on the HR system continue and PPM finds that the user 'User' does not exist and the same procedure is repeated.
1. If I create a user directly on the db – knta_users, the username (logon identifier) is case sensitive. So if the user ‘user’ exists on the table and I create a new user by the name ‘User’ the record is stored on the db.
2. The user interface is not case sensitive. In other words from the workbench you cannot create duplicate users. So if the user ‘user’ exists, you cannot create a new user by the name ‘User’.
I’ve logged a call with HP support to maybe create an enhancement on the PACKAGE KNTA_USER_INT so that it checks for lowercase(username) from knta_users = lowercase(username) knta_user_int so that duplicates are not created. I’m yet to get a solution from HP.
How do I identify the duplicated users? How do I fix them? I’ve temporarily stopped the interface. I need a query to help identify.
Not sure how large this user base is, but seems like you need something unchangable to act as a "key" between the two systems. Most times the username is set to an employee_id so that would avoid duplicates.
Are your usernames coming from HRMS really case-sensitive? Can you transform them to lower() during load of the interface tables?
If you're just looking for differences in the case of the username, you could try something like:
select uname,count(user_id) from ( select lower(username)"uname" ,user_id from knta_users ) group by uname having count(user_id) > 1