CREATE OR ALTER PROCEDURE MIG_USERS_Sp
AS
BEGIN

	DELETE FROM MIG_USERS

	INSERT INTO [MIG_USERS] 
	(
		[USER_NAME], 
		[LN_USER],
		[REPL_USER_NAME],
		[LN_REPL_USER],
		[WORK_AREA_KEY]
	)
	SELECT 
		USERS.[USER_NAME], 
		UPPER(USERS.[USER_NAME]) AS LN_USER,
		[REPL_USER_NAME],
		[REPL_USER_NAME] AS LN_REPL_USER,
		BASE_CONFIG.[PREF_VALUE] AS [WORK_AREA_KEY]
	FROM USERS 
	LEFT JOIN BASE_CONFIG 
		ON BASE_CONFIG.PROJECT_ID = '*' AND BASE_CONFIG.CLASS_ID = '*' AND BASE_CONFIG.[USER_NAME] = USERS.[USER_NAME]
		AND BASE_CONFIG.PREF_NAME = 'WORKING_AREA'

	ORDER BY USER_NAME 


	-- Insert an empty record (to avoid using LEFT JOIN).
	INSERT INTO MIG_USERS([USER_NAME], [LN_USER], [REPL_USER_NAME], [LN_REPL_USER]) VALUES('', '', '', '') -- To be validated

	-- 'system' user is renamed as 'PLMADMIN' in target.
	-- 'WF_DEMON' user is renamed as 'WF_DEMON' in target.

	UPDATE MIG_USERS SET LN_USER = 'PLMADMIN' WHERE [USER_NAME] = 'system'
	UPDATE MIG_USERS SET LN_USER = 'WF_DAEMON' WHERE [USER_NAME] = 'WF_DEMON'

	UPDATE MIG_USERS SET LN_REPL_USER = 'PLMADMIN' WHERE [REPL_USER_NAME] = 'system'
	UPDATE MIG_USERS SET LN_REPL_USER = 'WF_DAEMON' WHERE [REPL_USER_NAME] = 'WF_DEMON'

	--BEGIN TRAN

	-- --Insert Empty User
	-- INSERT INTO dbo.MIG_USERS(USER_NAME, LN_USER) VALUES('', '')

	-- UPDATE dbo.MIG_USERS SET LN_USER = 'WF_DAEMON' WHERE USER_NAME = 'WF_DEMON'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'AKARRA' WHERE USER_NAME = 'alekhya'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'BKAVURU' WHERE USER_NAME = 'balakrishna'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'BMANAPRAG' WHERE USER_NAME = 'bharath'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'CPARUSHA' WHERE USER_NAME = 'charan'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'GVIDIYALA' WHERE USER_NAME = 'ganesh'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'GGOSULA' WHERE USER_NAME = 'govind'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'GPIETERS' WHERE USER_NAME = 'gpieters'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'HMODEPAL' WHERE USER_NAME = 'hariprasad'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'KGOKA' WHERE USER_NAME = 'kiran'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'KPEDDIBH' WHERE USER_NAME = 'krishna'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'NKATIKE' WHERE USER_NAME = 'nagadeepthi'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'PDANTURT' WHERE USER_NAME = 'phani'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'PPALADUGU' WHERE USER_NAME = 'pallavi'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'PGANJI' WHERE USER_NAME = 'prashanth'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'RTHELKAP' WHERE USER_NAME = 'rakesh'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'RBYNA' WHERE USER_NAME = 'ramesh'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'RVADIYAL' WHERE USER_NAME = 'rohith'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'SBHEEMA' WHERE USER_NAME = 'sreelakshmi'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'SVADAKAT' WHERE USER_NAME = 'srini'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'SVALLURI' WHERE USER_NAME = 'sriram'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'SPOTTI' WHERE USER_NAME = 'suraj'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'TJUNEJA' WHERE USER_NAME = 'tanvi'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'USHRIVAS' WHERE USER_NAME = 'umesh'
	--UPDATE dbo.MIG_USERS SET LN_USER = 'VNADIKOTI' WHERE USER_NAME = 'varaprasad'

	-- UPDATE dbo.MIG_USERS SET LN_USER = 'VMALLIPE' WHERE USER_NAME = 'vijay'

	--UPDATE dbo.MIG_USERS SET LN_USER = 'NBANDI2' WHERE USER_NAME = 'bandinagarjuna'

	---- COMMIT TRAN

END


-- exec MIG_USERS_Sp
-- SELECT * FROM MIG_USERS