USE PatientFirstIntranet ;
GO
ALTER PROCEDURE dbo.PFAD_UMRA_LoadNewEmployee AS
SET NOCOUNT ON
;
EXEC PatientFirstIntranet.dbo.PFAD_UMRA_LoadTempNewHireTracking
;
EXEC PatientFirstIntranet.dbo.PFAD_UMRA_LoadNewHireToEmployeeChangeTracker
;
EXEC PatientFirstIntranet.dbo.PFAD_UMRA_LoadNewPositionToLMS
;
IF EXISTS
(
	SELECT ECT.* FROM
	[umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker ECT
	INNER JOIN
	[umra\sqlexpress].UMRA.dbo.Employees E
		ON ECT.EmployeeID = E.EmployeeID
	INNER JOIN
	Tracker3.dbo.Tracker_Positions TP
		ON E.Title = TP.PositionName
	LEFT JOIN
	[umra\sqlexpress].UMRA.dbo.xxEmployeeLookupForADIntegration LU
		ON ECT.EmployeeID = LU.EmployeeID
	LEFT JOIN
	PatientFirstIntranet.dbo.Users U
		ON LU.UserID = U.UserID
	LEFT JOIN
	PatientFirstIntranet.dbo.aspnet_users AU
		ON LOWER(U.UserName) = AU.LoweredUserName
	WHERE
	ECT.ProcessType = 'NH'
	AND ECT.IsProcessed = 'N'
	AND U.UserID IS NULL
	AND AU.UserID IS NULL
)
BEGIN TRY
	DECLARE
	@EmployeeID VARCHAR(10)
	,@Username VARCHAR(100)
	,@LoweredUserName VARCHAR(100)
	,@FirstName VARCHAR(50)
	,@LastName VARCHAR(50)
	,@Email VARCHAR(256)
	,@DisplayName VARCHAR(100)
	,@Password VARCHAR(50)
	,@RegionID VARCHAR(5)
	,@CenterID VARCHAR(5)
	,@DepartmentID VARCHAR(5)
	,@PositionID VARCHAR(5)
	,@ReporterIDInHierarchy VARCHAR(30)
	,@SQLExecAddUser NVARCHAR(600)
	,@SQLLogAddUser NVARCHAR(4000)
	,@SQLUpdateECTwU NVARCHAR(600)
	,@SQLInsertAU NVARCHAR(4000)
	,@SQLLogInsertAU NVARCHAR(3000)
	,@SQLUpdateECTwAU NVARCHAR(600)
	,@SQLInsertAM NVARCHAR(4000)
	,@SQLLogInsertAM NVARCHAR(3000)
	,@SQLUpdateECTwAM NVARCHAR(600)
	,@SQLExecTSInsertStudent NVARCHAR(4000)
	,@SQLLogTSInsertSudent NVARCHAR(3000)
	,@SQLUpdateECTwTS NVARCHAR(600)
	,@SQLInsertUP NVARCHAR(4000)
	,@SQLLogInsertUP NVARCHAR(3000)
	,@SQLUpdateECTwUP NVARCHAR(600)
	,@SQLInsertELU NVARCHAR(4000)
	,@SQLLogInsertELU NVARCHAR(3000)
	,@SQLUpdateECTwLU NVARCHAR(600)
	,@SQLUpdateECTwY NVARCHAR(600)
	,@StatusID VARCHAR(5)
	;
	DECLARE curNewHireList CURSOR READ_ONLY FOR
	SELECT --TOP 1
	ECT.EmployeeID
	,Username = 'PF\' + ECT.NewUserName
	,LoweredUserName = 'pf\' + LOWER(ECT.NewUserName)
	,FirstName = REPLACE(ECT.NewFirstName,'''','')
	,LastName = REPLACE(ECT.NewLastName,'''','')
	,Email = ECT.NewEmailAddress
	,DisplayName = ECT.NewDisplayName
	,[Password] = LOWER(LEFT(REPLACE(ECT.NewLastName,'''',''),4)) + E.SSN
	,RegionID = CLU.RegionID
	,CenterID = CLU.LMS_CenterID
	,DepartmentID = DLU.DepartmentID
	,PositionID = TP.PositionID
	,ReporterIDInHierarchy = '1' + CAST(CLU.RegionID AS VARCHAR(6)) + CAST(CLU.LMS_CenterID AS VARCHAR(6)) + CAST(DLU.DepartmentID AS VARCHAR(6)) + CAST(TP.PositionID AS VARCHAR(6))
	,StatusID = 
		CASE E.EmpStatus
			WHEN 'XPT' THEN '2'
			WHEN 'MDP' THEN '2'
			WHEN 'STU' THEN '6'
			WHEN 'RPT' THEN '2'
			WHEN 'FLX' THEN '4'
			WHEN 'MDF' THEN '1'
			WHEN 'PRN' THEN '3'
			WHEN 'VIP' THEN '1'
			WHEN 'XFT' THEN '1'
			WHEN 'WKD' THEN '5'
			WHEN 'RFT' THEN '1'
		END
	FROM
	[umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker ECT
	INNER JOIN
	[umra\sqlexpress].UMRA.dbo.Employees E
		ON ECT.EmployeeID = E.EmployeeID
	INNER JOIN
	Tracker3.dbo.vCenterRegionLookupForADIntegration CLU
		ON ECT.NewCenterName = CLU.HR_CenterName
	INNER JOIN
	Tracker3.dbo.vDeptLookupForADIntegration DLU
		ON ECT.NewFunction = DLU.HR_Function
	INNER JOIN
	Tracker3.dbo.Tracker_Positions TP
		ON ECT.NewJobTitle = TP.PositionName
	LEFT JOIN
	[umra\sqlexpress].UMRA.dbo.xxEmployeeLookupForADIntegration LU
		ON ECT.EmployeeID = LU.EmployeeID
	LEFT JOIN
	PatientFirstIntranet.dbo.Users U
		ON LU.UserID = U.UserID
	LEFT JOIN
	PatientFirstIntranet.dbo.aspnet_users AU
		ON LOWER(U.UserName) = AU.LoweredUserName
	WHERE
	ECT.ProcessType = 'NH'
	AND ECT.IsProcessed = 'N'
	AND LEN(ECT.EmployeeID) = 4
	AND ISNUMERIC(ECT.EmployeeID) = 1
	AND U.UserID IS NULL
	AND AU.UserID IS NULL
	;
	OPEN curNewHireList
	FETCH NEXT FROM curNewHireList INTO @EmployeeID,@Username,@LoweredUserName,@FirstName,@LastName,@Email,@DisplayName,@Password,@RegionID,@CenterID,@DepartmentID,@PositionID,@ReporterIDInHierarchy,@StatusID
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		SELECT @SQLExecAddUser = N'
BEGIN TRY
	EXEC PatientFirstIntranet.dbo.AddUser 0,'''+ @Username + ''',''' + @FirstName + ''',''' + @LastName + ''',NULL,0,''' + @Email + ''',''' + @DisplayName + ''',0,1,NULL ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLLogAddUser = N'
BEGIN TRY
	INSERT INTO MIS.MIS.Logging (MachineName,LogSource,LogLevel,LogMessage,LogDetailLocation,LogTime)
	SELECT MachineName = CAST(SERVERPROPERTY(''MACHINENAME'') AS VARCHAR(80)),LogSource = ''UMRA-NewHire'',LogLevel = ''INFO''
	,LogMessage = ''SUCCESS creating "' + @EmployeeID + '::' + @Username + '" in the PatientFirstIntranet.dbo.Users table.''
	,LogDetailLocation = ''pLoadNewEmployee (SQLLogAddUser)'',LogTime = GETDATE() ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLUpdateECTwU = N'
BEGIN TRY
	UPDATE [umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker SET
	UserID = U.UserID
	,IsProcessed = ''U''
	FROM
	[umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker ECT
	INNER JOIN
	PatientFirstIntranet.dbo.Users U
		ON ''PF\'' + ECT.NewUserName = U.UserName
	WHERE
	ECT.ProcessType = ''NH'' AND ECT.EmployeeID = ''' + @EmployeeID + '''
	AND ECT.IsProcessed = ''N'' ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLInsertAU = N'
BEGIN TRY
	INSERT INTO PatientFirstIntranet.dbo.aspnet_users
	(ApplicationId,UserName,LoweredUserName,LastActivityDate)
	SELECT
	ApplicationId = ''BE05582B-C771-47EC-885D-E2C035661F5E''
	,UserName = ''' + @Username + '''
	,LoweredUserName = ''' + @LoweredUserName + '''
	,LastActivityDate = GETDATE() ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLLogInsertAU = N'
BEGIN TRY
	INSERT INTO MIS.MIS.Logging (MachineName,LogSource,LogLevel,LogMessage,LogDetailLocation,LogTime)
	SELECT MachineName = CAST(SERVERPROPERTY(''MACHINENAME'') AS VARCHAR(80)),LogSource = ''UMRA-NewHire'',LogLevel = ''INFO''
	,LogMessage = ''SUCCESS creating "' + @EmployeeID + '::' + @Username + '" in the PatientFirstIntranet.dbo.aspnet_users table.''
	,LogDetailLocation = ''pLoadNewEmployee (SQLLogInsertAU)'',LogTime = GETDATE() ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLUpdateECTwAU = N'
BEGIN TRY
	UPDATE [umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker SET
	IsProcessed = ''AU'' 
	WHERE ProcessType = ''NH'' AND EmployeeID = ''' + @EmployeeID + '''
	AND IsProcessed = ''U'' ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLInsertAM = N'
BEGIN TRY
	INSERT INTO PatientFirstIntranet.dbo.aspnet_membership
	(
		ApplicationId,UserId,[Password],PasswordSalt,Email,LoweredEmail,IsApproved
		,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate
		,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart
		,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart
	)
	SELECT
	ApplicationId = ''BE05582B-C771-47EC-885D-E2C035661F5E''
	,AU.UserId
	,[Password] = ''' + @Password + '''
	,PasswordSalt = ''NHPwdSalt_' + @Password + '''
	,Email = ''' + @Email + '''
	,LoweredEmail = ''' + @Email + '''
	,IsApproved = 1
	,IsLockedOut = 0
	,CreateDate = GETDATE()
	,LastLoginDate = ''1900-01-01''
	,LastPasswordChangedDate = ''1900-01-01''
	,LastLockoutDate = ''1900-01-01''
	,FailedPasswordAttemptCount = 0
	,FailedPasswordAttemptWindowStart = ''1900-01-01''
	,FailedPasswordAnswerAttemptCount = 0
	,FailedPasswordAnswerAttemptWindowStart = ''1900-01-01''
	FROM
	PatientFirstIntranet.dbo.aspnet_users AU
	LEFT JOIN
	PatientFirstIntranet.dbo.aspnet_membership AM
		ON AU.UserId = AM.UserId
	WHERE
	AU.LoweredUserName = ''' + @LoweredUserName + '''
	AND AM.UserId IS NULL ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLLogInsertAM = N'
BEGIN TRY
	INSERT INTO MIS.MIS.Logging (MachineName,LogSource,LogLevel,LogMessage,LogDetailLocation,LogTime)
	SELECT MachineName = CAST(SERVERPROPERTY(''MACHINENAME'') AS VARCHAR(80)),LogSource = ''UMRA-NewHire'',LogLevel = ''INFO''
	,LogMessage = ''SUCCESS creating "' + @EmployeeID + '::' + @Username + '" in the PatientFirstIntranet.dbo.aspnet_membership table.''
	,LogDetailLocation = ''pLoadNewEmployee (SQLLogInsertAM)'',LogTime = GETDATE() ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLUpdateECTwAM = N'
BEGIN TRY
	UPDATE [umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker SET
	IsProcessed = ''AM'' 
	WHERE ProcessType = ''NH'' AND EmployeeID = ''' + @EmployeeID + '''
	AND IsProcessed = ''AU'' ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLExecTSInsertStudent = N'
BEGIN TRY
	DECLARE @StudentID VARCHAR(10)
	;
	SELECT @StudentID = UserID
	FROM PatientFirstIntranet.dbo.Users
	WHERE UserName = ''' + @Username + '''
	;
	EXEC Tracker3.dbo.Tracker_Student_InsertStudent
	@StudentID
	,@UserName = ''' + @Username + '''
	,@LastName = ''' + @LastName + ''' 
	,@FirstName = ''' + @FirstName + '''
	,@StudentName = ''' + @DisplayName + '''
	,@Password = ''BOVTymN+sOU=''
	,@OrganizationID = ''1''
	,@RegionID = ''' + @RegionID + '''
	,@CenterID = ''' + @CenterID + '''
	,@DepartmentID = ''' + @DepartmentID + '''
	,@PositionID = ''' + @PositionID + '''
	,@StatusID = ''' + @StatusID + '''
	,@Email = ''' + @Email + '''
	,@Administrator = ''0''
	,@Reporter = ''0''
	,@IsActive = ''1''
	,@AdminLevelID = ''0''
	,@ReporterIDInHierarchy = ''' + @ReporterIDInHierarchy + '''
	,@IsHoldingMultiPositions = ''1''
	,@AdminName = ''UMRA NewHire''
	;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLLogTSInsertSudent = N'
BEGIN TRY
	INSERT INTO MIS.MIS.Logging (MachineName,LogSource,LogLevel,LogMessage,LogDetailLocation,LogTime)
	SELECT MachineName = CAST(SERVERPROPERTY(''MACHINENAME'') AS VARCHAR(80)),LogSource = ''UMRA-NewHire'',LogLevel = ''INFO''
	,LogMessage = ''SUCCESS creating "' + @EmployeeID + '::' + @Username + '" in the Tracker3.dbo.Tracker_Students table.''
	,LogDetailLocation = ''pLoadNewEmployee (SQLLogInsertTS)'',LogTime = GETDATE() ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLUpdateECTwTS = N'
BEGIN TRY
	UPDATE [umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker SET
	IsProcessed = ''TS'' 
	WHERE ProcessType = ''NH'' AND EmployeeID = ''' + @EmployeeID + '''
	AND IsProcessed = ''AM'' ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLInsertUP = N'
BEGIN TRY
	INSERT INTO PatientFirstIntranet.dbo.UserProfile
	(UserID,PropertyDefinitionID,PropertyValue,Visibility,LastUpdatedDate)
	SELECT DISTINCT
	U.UserID
	,PropertyDefinitionID = (43)
	,PropertyValue = ECT.EmployeeID
	,Visibility = (0)
	,LastUpdatedDate = GETDATE()
	FROM
	[umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker ECT
	INNER JOIN
	PatientFirstIntranet.dbo.Users U
		ON ''PF\'' + ECT.NewUserName = U.Username
	INNER JOIN
	PatientFirstIntranet.dbo.aspnet_Users AU
		ON U.Username = AU.UserName
	INNER JOIN
	PatientFirstIntranet.dbo.aspnet_Membership AM
		ON AU.UserId = AM.UserId
	LEFT JOIN
	(
		SELECT UserID
		FROM PatientFirstIntranet.dbo.UserProfile
		WHERE PropertyDefinitionID = (43)
	)  UP
		ON U.UserID = UP.UserID
	WHERE
	ECT.ProcessType = ''NH''
	AND ECT.EmployeeID = ''' + @EmployeeID + '''
	AND ECT.IsProcessed = ''TS''
	AND UP.UserID IS NULL ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLLogInsertUP = N'
BEGIN TRY
	INSERT INTO MIS.MIS.Logging (MachineName,LogSource,LogLevel,LogMessage,LogDetailLocation,LogTime)
	SELECT MachineName = CAST(SERVERPROPERTY(''MACHINENAME'') AS VARCHAR(80)),LogSource = ''UMRA-NewHire'',LogLevel = ''INFO''
	,LogMessage = ''SUCCESS creating "' + @EmployeeID + '::' + @Username + '" in the PatientFirstIntranet.dbo.UserProfile table.''
	,LogDetailLocation = ''pLoadNewEmployee (SQLLogInsertUP)'',LogTime = GETDATE() ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLUpdateECTwUP = N'
BEGIN TRY
	UPDATE [umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker SET
	IsProcessed = ''UP'' 
	WHERE ProcessType = ''NH'' AND EmployeeID = ''' + @EmployeeID + '''
	AND IsProcessed = ''TS'' ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLInsertELU = N'
BEGIN TRY
	INSERT INTO [umra\sqlexpress].UMRA.dbo.xxEmployeeLookupForADIntegration
	(EmployeeID,UserID,UserName,AU_UserID)
	SELECT
	V.EmployeeID
	,V.UserID
	,V.UserName
	,V.AU_UserID
	FROM
	[umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker ECT
	INNER JOIN
	PatientFirstIntranet.dbo.vwUMRAEmployeeIDLookUp V
		ON ECT.EmployeeID = V.EmployeeID
	LEFT JOIN
	[umra\sqlexpress].UMRA.dbo.xxEmployeeLookupForADIntegration LU
		ON V.UserID = LU.UserID
	WHERE
	V.EmployeeID = ''' + @EmployeeID + '''
	AND LU.UserID IS NULL ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLLogInsertELU = N'
BEGIN TRY
	INSERT INTO MIS.MIS.Logging (MachineName,LogSource,LogLevel,LogMessage,LogDetailLocation,LogTime)
	SELECT MachineName = CAST(SERVERPROPERTY(''MACHINENAME'') AS VARCHAR(80)),LogSource = ''UMRA-NewHire'',LogLevel = ''INFO''
	,LogMessage = ''SUCCESS creating "' + @EmployeeID + '::' + @Username + '" in the UMRA.dbo.xxEmployeeLookupForADIntegration table.''
	,LogDetailLocation = ''pLoadNewEmployee (SQLLogInsertELU)'',LogTime = GETDATE() ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLUpdateECTwLU = N'
BEGIN TRY
	UPDATE [umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker SET
	IsProcessed = ''LU'' 
	WHERE ProcessType = ''NH'' AND EmployeeID = ''' + @EmployeeID + '''
	AND IsProcessed = ''UP'' ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'
		SELECT @SQLUpdateECTwY = N'
BEGIN TRY
	UPDATE [umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker SET
	IsProcessed = ''Y'' 
	WHERE ProcessType = ''NH'' AND EmployeeID = ''' + @EmployeeID + '''
	AND IsProcessed = ''LU'' ;
END TRY
BEGIN CATCH
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging ''' + @EmployeeID + ''' ;
END CATCH'

		EXEC dbo.sp_executesql @SQLExecAddUser
		EXEC dbo.sp_executesql @SQLLogAddUser
		EXEC dbo.sp_executesql @SQLUpdateECTwU
		EXEC dbo.sp_executesql @SQLInsertAU
		EXEC dbo.sp_executesql @SQLLogInsertAU
		EXEC dbo.sp_executesql @SQLUpdateECTwAU
		EXEC dbo.sp_executesql @SQLInsertAM
		EXEC dbo.sp_executesql @SQLLogInsertAM
		EXEC dbo.sp_executesql @SQLUpdateECTwAM
		EXEC dbo.sp_executesql @SQLExecTSInsertStudent
		EXEC dbo.sp_executesql @SQLLogTSInsertSudent
		EXEC dbo.sp_executesql @SQLUpdateECTwTS
		EXEC dbo.sp_executesql @SQLInsertUP
		EXEC dbo.sp_executesql @SQLLogInsertUP
		EXEC dbo.sp_executesql @SQLUpdateECTwUP
		EXEC dbo.sp_executesql @SQLInsertELU
		EXEC dbo.sp_executesql @SQLLogInsertELU
		EXEC dbo.sp_executesql @SQLUpdateECTwLU
		EXEC dbo.sp_executesql @SQLUpdateECTwY

		FETCH NEXT FROM curNewHireList INTO @EmployeeID,@Username,@LoweredUserName,@FirstName,@LastName,@Email,@DisplayName,@Password,@RegionID,@CenterID,@DepartmentID,@PositionID,@ReporterIDInHierarchy,@StatusID
	END
	CLOSE curNewHireList
	;
	DEALLOCATE curNewHireList
	;

-----------------------------------------------------------------------------------

	UPDATE [umra\sqlexpress].UMRA.dbo.xxTempNewHireTracking SET
	IsProcessed_DNN = 'Y'
	FROM
	[umra\sqlexpress].UMRA.dbo.xxTempNewHireTracking TNHT
	INNER JOIN
	[umra\sqlexpress].UMRA.dbo.xxEmployeeChangeTracker ECT
		ON TNHT.EmployeeID = ECT.EmployeeID
	WHERE
	ECT.ProcessType = 'NH'
	AND ECT.IsProcessed = 'Y'
	AND TNHT.IsProcessed_ECT = 'Y'	
	AND IsProcessed_DNN = 'N'
	;

-----------------------------------------------------------------------------------

END TRY
BEGIN CATCH
	DECLARE @cursorstatus INT ;
	SELECT @cursorstatus = CURSOR_STATUS('GLOBAL','curNewHireList')
	IF @cursorstatus > -2
		BEGIN
			CLOSE curNewHireList
			DEALLOCATE curNewHireList
		END
	EXEC PatientFirstIntranet.dbo.PFAD_UMRA_NewEmployeeErrorLogging 'Root' ;
END CATCH
ELSE
	PRINT 'No NewHires To Load to DNN'
	RETURN
	
/*
EXEC PatientFirstIntranet.dbo.PFAD_UMRA_LoadNewEmployee ;
*/