Courion Tip – Generating user names

Here’s the scenario… Automatically generate user names for a target system.

  • Does the user already have accounts on similar targets?
  • If so, is it 8 characters or less?
  • Otherwise, calculate it as First Initial, First 7 characters of last name – BUT
    • Is it already taken?
    • If not, use it
    • If so, make the last character a number, and increment until you find one that is free.

So, here’s my solution.

Step 1 create a view showing any taken user name that ends in a number. WHY? Because using MAX() and the default sort order give the wrong results. For instance (pseudo code)

MAX(jbobz, jbob0) (thats the number 0 at the end) returns jbob0 – which isn’t what we want. We want the highest number.

CREATE VIEW [dbo].[dbv_SAPUsernames_ending_in_number]
AS
SELECT DISTINCT USERNAME
FROM         dbo.IDENTITYMAP
WHERE     (RIGHT(USERNAME, 1) IN (‘0’, ‘1’, ‘2’, ‘3’, ‘4’, ‘5’, ‘6’, ‘7’, ‘8’)) AND (TARGETID LIKE ‘%CLNT%’)

Next, create a stored procedure to handle the logic…

— =============================================
— Author: Dan Murphy
— Create date: 10/26/2011
— Description: Generate a users suggested SAP name for Courion workflow
— =============================================
CREATE PROCEDURE [dbo].[sp_GenerateSAPAccountName]
@Emp_id VARCHAR(50),
@SAPAccountName VARCHAR(50) OUTPUT
AS
BEGIN
— Temporary variable internal use only.
DECLARE @TempAccountName VARCHAR(50)
DECLARE @TempLength SmallInt

— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Look to see if the user already has valid SAP account name
SELECT @TempAccountName = Username from IDENTITYMAP where profileuid = @Emp_id and TARGETID like ‘%CLNT%’
IF @TempAccountName is not null and LEN(@tempaccountname)<9
Begin
Select @SAPAccountName = UPPER(@tempaccountname)
Return 0
END

— Calculate SAP name if it isn't found – but make sure it's not already taken
SELECT @TempAccountName = UPPER(LEFT(first_name,1)+LEFT(last_name,7)) from PROFILE where EMP_ID = @Emp_id

— If the resulting name is available, return it.
IF @TempAccountName is not null and LEN(@tempaccountname)<9 AND @TempAccountName not in (select username from IDENTITYMAP where TARGETID like '%CLNT%')
Begin
Select @SAPAccountName = UPPER(@tempaccountname)
Return 0
END
— The resulting name must be already be in use
— If the resulting user name is less than 8 characters
–IF LEN(@TempAccountName) < 8
Select @TempAccountName = LEFT(@tempaccountname, 7)
Select @TempLength = LEN(@tempaccountname)+1

Begin
–Select Max([USERNAME]) FROM [ILM_PROFILE].[dbo].[dbv_SAPUsernames_ending_in_number] where USERNAME like @TempAccountName+'%'
Select @TempAccountName = UPPER(CASE
RIGHT((SELECT MAX(Username) from dbv_SAPUsernames_ending_in_number where USERNAME LIKE @TempAccountName+'%'),1)
WHEN '0' THEN @TempAccountName + '1'
WHEN '1' THEN @TempAccountName + '2'
WHEN '2' THEN @TempAccountName + '3'
WHEN '3' THEN @TempAccountName + '4'
WHEN '4' THEN @TempAccountName + '5'
WHEN '5' THEN @TempAccountName + '6'
WHEN '6' THEN @TempAccountName + '7'
WHEN '7' THEN @TempAccountName + '8'
WHEN '8' THEN @TempAccountName + '9'
ELSE @TempAccountName + '0'
END)
from IDENTITYMAP where TARGETID like '%CLNT%' AND USERNAME LIKE @TempAccountName +'%'
Select @TempAccountName = LEFT(@TempAccountName,@TempLength)

Select @SAPAccountName = @tempaccountname
Return 0
End
RETURN 0
END
GO

Advertisements

Posted October 28, 2011 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: