--BEGIN TRANSACTION
--nb20071017 requried by dbUpdate

Declare @UserIDNumber int
Declare @UserIDCode varchar(20)
Declare @ActivePerson bit
Declare @LastUserIDNumber int
Declare @LastUserIDCode varchar(20)
Declare @LastActivePerson bit

set @LastUserIDNumber = -1
set @LastUserIDCode =   ''
set @LastActivePerson = null 

Declare CurPersonel Cursor Fast_Forward Local For
SELECT DISTINCT p1.UserIDNumber, p1.UserIDCode, p1.ActivePerson FROM Personel AS p1
INNER JOIN Personel AS p2 ON p1.UserIDCode = p2.UserIDCode
WHERE p1.UserIDNumber <> p2.UserIDNumber
ORDER BY p1.UserIDCode, p1.ActivePerson ,p1.UserIDNumber
Open CurPersonel

Fetch Next From CurPersonel Into @UserIDNumber, @UserIDCode, @ActivePerson
while  (@@FETCH_STATUS = 0)
begin

	if (@UserIDCode = @LastUserIDCode) 
	begin
		UPDATE Personel SET UserIDCode = rtrim(left(@LastUserIDCode,19 - len(str(@UserIDNumber)))) + '_' + LTrim(str(@LastUserIDNumber)), ActivePerson = 0
		WHERE UserIDNumber = @LastUserIDNumber
	end
	
	set @LastUserIDNumber = @UserIDNumber
	set @LastUserIDCode = @UserIDCode	
	set @LastActivePerson = @ActivePerson

	Fetch Next From CurPersonel Into @UserIDNumber, @UserIDCode, @ActivePerson
end

Close CurPersonel
Deallocate CurPersonel

--COMMIT TRANSACTION
