ALTER PROCEDURE [dbo].[CM_EMPLOYEE_Sync]
@p_ACTION	varchar(15)  = NULL,
@p_CODE	varchar(15)  = NULL,
@p_NAME	nvarchar(500)  = NULL,
@p_BRANCH_CODE	varchar(15)  = NULL,
@p_DEP_CODE	varchar(15)  = NULL,
--- BO SUNG THEM MA CHUC DANH, TEN  CHUC DANH
@p_USER_DOMAIN VARCHAR(15) = NULL,
@p_MA_CHUC_DANH NVARCHAR(50) = NULL,
@p_TEN_CHUC_DANH NVARCHAR(500) = NULL
AS
--Validation is here
--04032024_secretkey: LUCTV - FIX LOI NGAY TẠO USER ĐANG LẤY 2021
DECLARE @ERRORSYS NVARCHAR(15) = '' 
DECLARE @p_BRANCH_NAME NVARCHAR(500) =''
DECLARE @p_BRANCH_TYPE NVARCHAR(50) =''
DECLARE @p_BRANCH_ID NVARCHAR(50) =''
DECLARE @p_DEP_ID NVARCHAR(50) =''
----LUCTV 18.04.2023 SET LẠI BRANCH CODE BẰNG 3 KÝ TỰ ĐẦU CỦA BRANCH CODE 18042023_secretkey
SET @p_BRANCH_CODE =(LEFT(@p_BRANCH_CODE,3))
IF(@p_BRANCH_CODE IN ('899','799','690')) --- LUCTV 05042024_SECRETKEY: HOTFIX ĐỐI VỚI NHỮNG BRANCH ẢO THÌ CHUYỂN VỀ 069
BEGIN
	SET @p_BRANCH_CODE ='069'
END
IF(@p_DEP_CODE LIKE 'HC%') --- LUCTV 26072023_SECRETKEY: HOTFIX ĐỐI VỚI NHỮNG PHÒNG BAN CÓ MÃ HC THÌ LIỆT KÊ VÀO PHÒNG HÀNH CHÍNH
BEGIN
	SET @p_DEP_CODE ='0690604'
END
SET @p_BRANCH_NAME = (SELECT TOP 1 A.BRANCH_NAME FROM CM_BRANCH A WHERE A.BRANCH_CODE = @p_BRANCH_CODE)
SET @p_BRANCH_TYPE = (SELECT TOP 1 A.BRANCH_TYPE FROM CM_BRANCH A WHERE A.BRANCH_CODE = @p_BRANCH_CODE)
SET @p_BRANCH_ID   = (SELECT TOP 1 A.BRANCH_ID FROM CM_BRANCH A WHERE A.BRANCH_CODE = @p_BRANCH_CODE)
SET @p_DEP_ID      = (SELECT TOP 1 A.DEP_ID FROM CM_DEPARTMENT A WHERE A.DEP_CODE = @p_DEP_CODE AND A.BRANCH_ID =@p_BRANCH_ID)
---- END 
--- LUCTV BO SUNG NGAY 08 07 2020 18042023_secretkey
DECLARE @ROLE_ID VARCHAR(20) =''
SET @ROLE_ID =(SELECT TOP 1 ROLE_ID FROM CM_MAPPING_CHUCDANH_ROLE WHERE (MA_CHUC_DANH = @p_MA_CHUC_DANH AND TEN_CHUC_DANH =@p_TEN_CHUC_DANH) OR TEN_CHUC_DANH =@p_TEN_CHUC_DANH)
PRINT @ROLE_ID
DECLARE @EMAIL VARCHAR(50) =''
--SET @EMAIL =@p_USER_DOMAIN+'@vietcapitalbank.com.vn'--NGUYENTD 08072024_SECRETKEY: FIX MAIL MỚI KHI ĐỒNG BỘ
SET @EMAIL =@p_USER_DOMAIN+'@bvbank.net.vn'
DECLARE @l_TLID VARCHAR(15)
----END LUCTV BO SUNG 
IF(NOT EXISTS(SELECT * FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN=@p_USER_DOMAIN))
BEGIN
	INSERT INTO CM_EMPLOYEE_LOG (EMP_CODE,EMP_NAME,BRANCH_CODE,DEP_CODE,USER_DOMAIN,POS_CODE,POS_NAME,CREATE_DT) 
	VALUES (@p_CODE,@p_NAME,@p_BRANCH_CODE,@p_DEP_CODE,@p_USER_DOMAIN,@p_MA_CHUC_DANH,@p_TEN_CHUC_DANH,GETDATE())
END
BEGIN TRANSACTION
		IF(@p_ACTION ='I' OR @p_ACTION ='U')
		BEGIN
			--KIEM TRA MA NHAN VIEN KHONG DUOC BO TRONG
			IF (@p_CODE='' OR @p_CODE IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã nhân viên không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA TEN NHAN VIEN KHONG DUOC BO TRONG
			IF (@p_NAME='' OR @p_NAME IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Tên nhân viên không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA DON VI NAY CO TON TAI HAY KHONG
			IF (@p_BRANCH_CODE='' OR @p_BRANCH_CODE IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã đơn vị không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			--KIEM TRA PHONG BAN NAY CO TON TAI HAY KHONG
			IF (@p_DEP_CODE='' OR @p_DEP_CODE IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã phòng ban không được bỏ trống' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			---LUCTV BO SUNG NGAY 08 07 2020
			--KIEM TRA USER DOMAIN KHONG DUOC RONG
			--IF (@p_USER_DOMAIN='' OR @p_USER_DOMAIN IS NULL)
			--BEGIN
			--	ROLLBACK TRANSACTION
			--	SELECT '-1' Result, ''  EMP_ID, N'User domain không được để trống'  ErrorDesc
			--	RETURN '-1'
			--END
			--KIEM TRA MA CHUC DANH KHAC RONG
			IF (@p_MA_CHUC_DANH='' OR @p_MA_CHUC_DANH IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã chức danh không được để trống'  ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA TEN CHUC DANH KHAC RONG
			IF (@p_TEN_CHUC_DANH='' OR @p_TEN_CHUC_DANH IS NULL)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Tên chức danh không được để trống'  ErrorDesc
				RETURN '-1'
			END
			----KIEM TRA TEN CHUC DANH CO TON TAI TRONG DANH SACH CHUC DANH VCCB CAP HAY KHONG
			--IF (NOT EXISTS(SELECT * FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH =@p_MA_CHUC_DANH))
			--BEGIN
			--	ROLLBACK TRANSACTION
			--	SELECT '-1' Result, ''  EMP_ID, N'Mã chức danh này chưa tồn tại trong hệ thống'  ErrorDesc
			--	RETURN '-1'
			--END
			----KIEM TRA TEN CHUC DANH CO TON TAI TRONG DANH SACH CHUC DANH VCCB CAP HAY KHONG
			--IF (NOT EXISTS(SELECT * FROM CM_MAPPING_CHUCDANH_ROLE WHERE TEN_CHUC_DANH =@p_TEN_CHUC_DANH))
			--BEGIN
			--	ROLLBACK TRANSACTION
			--	SELECT '-1' Result, ''  EMP_ID, N'Tên chức danh này chưa tồn tại trong hệ thống'  ErrorDesc
			--	RETURN '-1'
			--END
			-----------------------------------------------------------------------------------
			--KIEM TRA ROLE NAY CO TON TAI HAY KHONG
			IF (@ROLE_ID='' OR @ROLE_ID IS NULL)
			BEGIN
				IF(@p_BRANCH_CODE ='069')
				BEGIN
					SET @ROLE_ID ='NVMS'
				END
				ELSE
				BEGIN
					SET @ROLE_ID ='NVTT'
				END
				 -- 05032025_SECRETKEY : NGUYENTD SỬA MẶC ĐỊNH LÀ NVMS
				--ROLLBACK TRANSACTION
				--SELECT '-1' Result, ''  EMP_ID, N'Chưa có ROLE tương ứng cho chức danh có mã ' +@p_MA_CHUC_DANH  ErrorDesc
				--RETURN '-1'
			END
			---------------------------------------------------------------------------------
		END
		IF(@p_ACTION='I')
		BEGIN
		    -- INSERT NHAN VIEN NAY VAO BANG TAMP
			IF(NOT EXISTS(SELECT * FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN=@p_USER_DOMAIN))
			BEGIN
				INSERT INTO CM_EMPLOYEE_LOG (EMP_CODE,EMP_NAME,BRANCH_CODE,DEP_CODE,USER_DOMAIN,POS_CODE,POS_NAME,CREATE_DT) 
				VALUES (@p_CODE,@p_NAME,@p_BRANCH_CODE,@p_DEP_CODE,@p_USER_DOMAIN,@p_MA_CHUC_DANH,@p_TEN_CHUC_DANH,GETDATE())
			END
			--KIEM TRA NHAN VIEN NAY CO TON TAI HAY KHONG
			IF (EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE EMP_CODE=@p_CODE AND RECORD_STATUS ='1'))
			BEGIN
				SET @ERRORSYS = 'EMP-0001'
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã nhân viên này đã tồn tại' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA DON VI NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã đơn vị này không tồn tại' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			--KIEM TRA PHONG BAN NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE =@p_DEP_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã phòng ban: '+@p_DEP_CODE+ N' không tồn tại' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			--KIEM TRA PHONG BAN CO THUOC VE DUNG DON VI CUA NO HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE BRANCH_ID =@p_BRANCH_ID AND DEP_ID=@p_DEP_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Phòng ban có mã '+@p_DEP_CODE+ N' không thuộc về đơn vị '+@p_BRANCH_CODE ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			--LUCTV 09072020 BO SUNG KIEM TRA USER DOMAIN DA TON TAI
			IF (EXISTS ( SELECT * FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'User domain: '+@p_USER_DOMAIN +N' đã tồn tại trong hệ thống'  ErrorDesc
				RETURN '-1'
			END
			--LUCTV 09072020 BO SUNG KIEM TRA HO VA TEN DA TON TAI
			--IF (EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE EMP_NAME =@p_NAME))
			--BEGIN
			--	ROLLBACK TRANSACTION
			--	SELECT '-1' Result, ''  EMP_ID, N'Tên nhân viên '+@p_NAME +N' đã tồn tại trong hệ thống'  ErrorDesc
			--	RETURN '-1'
			--END
			--KIEM TRA ROLE CO HOP LE HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM TL_SYSROLE WHERE ROLE_ID =@ROLE_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Role không tồn tại'+@ROLE_ID ErrorDesc
				RETURN '-1'
			END
			IF (NOT EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE EMP_CODE=@p_CODE AND RECORD_STATUS ='1'))
			BEGIN
				DECLARE @l_EMP_ID VARCHAR(15)
				EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out		
				IF @l_EMP_ID='' OR @l_EMP_ID IS NULL GOTO ABORT
				print @l_EMP_ID
				INSERT INTO CM_EMPLOYEE([EMP_ID],[EMP_CODE],[EMP_NAME],[BRANCH_ID],[DEP_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])
				VALUES(@l_EMP_ID ,@p_CODE ,@p_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,NULL ,'1' ,'A' ,NULL ,CONVERT(DATETIME, GETDATE(), 103) ,NULL ,CONVERT(DATETIME, GETDATE(), 103) )
			END
			---- DONG BO XONG CM_EMPLOYEE THI DONG BO NGUOI DUNG LUON - LUCTV BO SUNG NGAY 08 07 2020
			IF(@p_USER_DOMAIN IS NOT NULL AND @p_USER_DOMAIN <> '')
			BEGIN
				--EXEC [TL_USER_Sync] 'I',@p_USER_DOMAIN,@p_NAME,@p_BRANCH_CODE,@p_DEP_CODE,@EMAIL,@ROLE_ID
			EXEC SYS_CodeMasters_Gen 'TL_USER', @l_TLID out
			IF @l_TLID='' OR @l_TLID IS NULL GOTO ABORT
			--INSERT INTO TL_USER([TLID],[TLNANME],[Password],[TLFullName],[TLSUBBRID],[BRANCH_NAME],[BRANCH_TYPE],[RoleName],[EMAIL],[ADDRESS],[PHONE],[AUTH_STATUS],[MARKER_ID],[AUTH_ID],[APPROVE_DT],[ISAPPROVE],[Birthday],[ISFIRSTTIME],[SECUR_CODE],DEP_ID,[CreationTime],[IsActive])
			--VALUES(@L_TLID ,@p_USER_DOMAIN ,'d41d8cd98f00b204e9800998ecf8427e' ,@p_NAME ,@p_BRANCH_ID ,@p_BRANCH_NAME ,@p_BRANCH_TYPE ,@ROLE_ID ,@EMAIL ,NULL ,NULL ,'A' ,NULL ,NULL ,CONVERT(DATETIME, GETDATE(), 103) ,NULL ,NULL ,NULL ,@p_DEP_ID,@p_DEP_ID,CONVERT(DATETIME, GETDATE(),103),1)

			INSERT INTO [TL_USER] ([TLID],[TLNANME],[Password],[TLFullName],[TLSUBBRID],[BRANCH_NAME],[BRANCH_TYPE],[RoleName],[EMAIL],[ADDRESS],[PHONE],[AUTH_STATUS],[MARKER_ID],[AUTH_ID],[APPROVE_DT],[ISAPPROVE],[Birthday],[ISFIRSTTIME],[SECUR_CODE],[AccessFailedCount],[AuthenticationSource],[ConcurrencyStamp],[CreatorUserId],[DeleterUserId],[EmailAddress],[EmailConfirmationCode],[IsActive],[IsDeleted],[IsEmailConfirmed],[IsLockoutEnabled],[IsPhoneNumberConfirmed],[IsTwoFactorEnabled],[LastModifierUserId],[LockoutEndDateUtc],[Name],[NormalizedEmailAddress],[NormalizedUserName],[PasswordResetCode],[PhoneNumber],[ProfilePictureId],[SecurityStamp],[ShouldChangePasswordOnNextLogin],[Surname],[TenantId],[SignInToken],[SignInTokenExpireTimeUtc],[GoogleAuthenticatorKey],[SendActivationEmail],[DEP_ID],[CreationTime],[UamFullName],[UamEmployeeId],[UamCompanyCode],[UamWfDataId],[UamCompanyName],[UamJobTitle],IsBlockUserMobile)
			VALUES(@L_TLID,@p_USER_DOMAIN,N'd41d8cd98f00b204e9800998ecf8427e',@p_NAME,@p_BRANCH_ID,@p_BRANCH_NAME,@p_BRANCH_TYPE,@ROLE_ID,@EMAIL,N'',N'',N'A',N'tscd_hoiso',NULL,CONVERT(DATETIME, GETDATE(), 103),N'1',N'Jan  1 1900 12:00AM',N'1',@p_DEP_ID,0,NULL,N'1360eb6c-4581-4870-aaf7-a2986428b91d',NULL,NULL,@EMAIL,NULL,1,0,1,0,1,0,810,NULL,NULL,UPPER(@EMAIL),UPPER(@p_USER_DOMAIN),NULL,NULL,NULL,N'QSTE4B7VTYWRQON4JZTJST6QN4PEVVLB',1,NULL,1,NULL,NULL,NULL,0,@p_DEP_ID,GETDATE(),NULL,NULL,NULL,NULL,NULL,NULL,0)

			--- INSERT VAO BANG TL_USER_V2
			IF(NOT EXISTS(SELECT * FROM TL_USER_V2 WHERE TLNANME =@p_USER_DOMAIN))
			BEGIN
				INSERT INTO TL_USER_V2 (TLID,TLNANME,RoleName,TLFullName,TLSUBBRID,SECUR_CODE) VALUES (@l_TLID,@p_USER_DOMAIN,@ROLE_ID,@p_NAME,@p_BRANCH_ID,@p_DEP_ID)
			END
			--INSERT VAO BAN AbpUserRoles

			INSERT INTO [dbo].[AbpUserRoles] ([CreationTime],[CreatorUserId],[RoleId],[TenantId],[UserId])
			VALUES
					   (GETDATE()
					   ,NULL
					   ,(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID)
					   ,1
					   ,(SELECT ID FROM TL_USER WHERE TLID=@l_TLID))
			-- insert vao bang 
			UPDATE TL_USER SET ROLENAME ='GDV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giao dịch viên' and branch_code <> '069') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='KSV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Kiểm soát viên' and branch_code <> '069') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giám đốc chi nhánh' and branch_code <> '069') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc chi nhánh' and branch_code <> '069') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Trưởng phòng giao dịch' and branch_code <> '069') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='PPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó phòng giao dịch' and branch_code <> '069')
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giám đốc trung tâm kinh doanh') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc TTKD')
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giám đốc TTKD') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc trung tâm kinh doanh') 
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc chi nhánh' and branch_code <> '069')
			AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
			--UPDATE TL_USER SET ROLENAME ='PP' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'%Phó phòng%' and branch_code = '069' AND DEP_CODE <> '0690802') AND ROLENAME <> 'DISABLE'
			--UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'%Trưởng phòng%' and branch_code = '069' AND DEP_CODE <> '0690802') AND ROLENAME <> 'DISABLE'
			END
		END
		ELSE IF(@p_ACTION='U')
		BEGIN
			IF(NOT EXISTS(SELECT * FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@p_USER_DOMAIN))
			BEGIN
				INSERT INTO CM_EMPLOYEE_LOG (EMP_CODE,EMP_NAME,BRANCH_CODE,DEP_CODE,USER_DOMAIN,POS_CODE,POS_NAME,CREATE_DT) 
				VALUES (@p_CODE,@p_NAME,@p_BRANCH_CODE,@p_DEP_CODE,@p_USER_DOMAIN,@p_MA_CHUC_DANH,@p_TEN_CHUC_DANH,GETDATE())
			END
			ELSE
			BEGIN
				UPDATE CM_EMPLOYEE_LOG SET POS_CODE=@p_MA_CHUC_DANH,POS_NAME = @p_TEN_CHUC_DANH, DEP_CODE =@p_DEP_CODE, BRANCH_CODE =@p_BRANCH_CODE, CREATE_DT = GETDATE() WHERE USER_DOMAIN =@p_USER_DOMAIN
			END
			DECLARE @EMP_ID VARCHAR(15)
			SET @EMP_ID =(SELECT EMP_ID FROM CM_EMPLOYEE WHERE EMP_CODE=@p_CODE)
			--KIEM TRA MA NHAN VIEN CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE EMP_CODE = @p_CODE AND RECORD_STATUS ='1'))	 
			BEGIN
				DECLARE @l_EMP_ID_U VARCHAR(15)
				EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID_U out		
				IF @l_EMP_ID_U='' OR @l_EMP_ID_U IS NULL GOTO ABORT
				INSERT INTO CM_EMPLOYEE([EMP_ID],[EMP_CODE],[EMP_NAME],[BRANCH_ID],[DEP_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])
				VALUES(@l_EMP_ID_U ,@p_CODE ,@p_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,NULL ,'1' ,'A' ,NULL ,CONVERT(DATETIME, GETDATE(), 103) ,NULL ,CONVERT(DATETIME, GETDATE(), 103) )
			END
			--KIEM TRA XEM CUNG MA CODE DO CO BI TRUNG VOI NHUNG NHAN VIEN KHAC HAY KHONG
			IF (EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE EMP_CODE = @p_CODE AND EMP_ID <> @EMP_ID AND RECORD_STATUS ='1'))	 
			BEGIN
				SET @ERRORSYS = 'EMP-0001'
				ROLLBACK TRANSACTION
				SELECT ErrorCode Result, ''  EMP_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
				RETURN '-1'
			END	
			IF(NOT EXISTS(SELECT * FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN))
			BEGIN
				IF(@p_USER_DOMAIN IS NOT NULL AND @p_USER_DOMAIN <> '')
				BEGIN
					--EXEC [TL_USER_Sync] 'I',@p_USER_DOMAIN,@p_NAME,@p_BRANCH_CODE,@p_DEP_CODE,@EMAIL,@ROLE_ID
					EXEC SYS_CodeMasters_Gen 'TL_USER', @l_TLID out
					IF @l_TLID='' OR @l_TLID IS NULL GOTO ABORT

					INSERT INTO [TL_USER] ([TLID],[TLNANME],[Password],[TLFullName],[TLSUBBRID],[BRANCH_NAME],[BRANCH_TYPE],[RoleName],[EMAIL],[ADDRESS],[PHONE],[AUTH_STATUS],[MARKER_ID],[AUTH_ID],[APPROVE_DT],[ISAPPROVE],[Birthday],[ISFIRSTTIME],[SECUR_CODE],[AccessFailedCount],[AuthenticationSource],[ConcurrencyStamp],[CreatorUserId],[DeleterUserId],[EmailAddress],[EmailConfirmationCode],[IsActive],[IsDeleted],[IsEmailConfirmed],[IsLockoutEnabled],[IsPhoneNumberConfirmed],[IsTwoFactorEnabled],[LastModifierUserId],[LockoutEndDateUtc],[Name],[NormalizedEmailAddress],[NormalizedUserName],[PasswordResetCode],[PhoneNumber],[ProfilePictureId],[SecurityStamp],[ShouldChangePasswordOnNextLogin],[Surname],[TenantId],[SignInToken],[SignInTokenExpireTimeUtc],[GoogleAuthenticatorKey],[SendActivationEmail],[DEP_ID],[CreationTime],[UamFullName],[UamEmployeeId],[UamCompanyCode],[UamWfDataId],[UamCompanyName],[UamJobTitle],[IsBlockUserMobile])
					VALUES(@L_TLID,@p_USER_DOMAIN,N'd41d8cd98f00b204e9800998ecf8427e',@p_NAME,@p_BRANCH_ID,@p_BRANCH_NAME,@p_BRANCH_TYPE,@ROLE_ID,@EMAIL,N'',N'',N'A',N'tscd_hoiso',NULL,CONVERT(DATETIME, GETDATE(), 103),N'1',N'Jan  1 1900 12:00AM',N'1',@p_DEP_ID,0,NULL,N'1360eb6c-4581-4870-aaf7-a2986428b91d',NULL,NULL,@EMAIL,NULL,1,0,1,0,1,0,810,NULL,NULL,UPPER(@EMAIL),UPPER(@p_USER_DOMAIN),NULL,NULL,NULL,N'QSTE4B7VTYWRQON4JZTJST6QN4PEVVLB',1,NULL,1,NULL,NULL,NULL,0,@p_DEP_ID,GETDATE(),NULL,NULL,NULL,NULL,NULL,NULL,0)

					--INSERT INTO TL_USER([TLID],[TLNANME],[Password],[TLFullName],[TLSUBBRID],[BRANCH_NAME],[BRANCH_TYPE],[RoleName],[EMAIL],[ADDRESS],[PHONE],[AUTH_STATUS],[MARKER_ID],[AUTH_ID],[APPROVE_DT],[ISAPPROVE],[Birthday],[ISFIRSTTIME],[SECUR_CODE],[CreationTime],[IsActive])
					--VALUES(@L_TLID ,@p_USER_DOMAIN ,'d41d8cd98f00b204e9800998ecf8427e' ,@p_NAME ,@p_BRANCH_ID ,@p_BRANCH_NAME ,@p_BRANCH_TYPE ,@ROLE_ID ,@EMAIL ,NULL ,NULL ,'A' ,NULL ,NULL ,CONVERT(DATETIME, GETDATE(), 103) ,NULL ,NULL ,NULL ,@p_DEP_ID,GETDATE(),1)
					--- INSERT VAO BANG TL_USER_V2
					IF(NOT EXISTS(SELECT * FROM TL_USER_V2 WHERE TLNANME =@p_USER_DOMAIN))
					BEGIN
						INSERT INTO TL_USER_V2 (TLID,TLNANME,RoleName,TLFullName,TLSUBBRID,SECUR_CODE) VALUES (@l_TLID,@p_USER_DOMAIN,@ROLE_ID,@p_NAME,@p_BRANCH_ID,@p_DEP_ID)
					END
				END
			END
			ELSE
			BEGIN
				UPDATE TL_USER SET [TLFullName] = @p_NAME,[TLSUBBRID] =@p_BRANCH_ID,[BRANCH_NAME]=@p_BRANCH_NAME,[BRANCH_TYPE]=@p_BRANCH_TYPE,[RoleName]=@ROLE_ID,[DEP_ID]=@p_DEP_ID,[SECUR_CODE]=@p_DEP_ID,IsActive=1 
				WHERE TLNANME = @p_USER_DOMAIN
				AND (RoleName NOT IN ('PGDK','GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT')
                OR NOT EXISTS(SELECT 1 FROM AbpUserRoles aur JOIN AbpRoles ar ON aur.RoleId = ar.Id JOIN TL_USER tu ON (tu.ID = aur.UserId AND tu.TLNANME=@p_USER_DOMAIN)
						WHERE ar.DisplayName IN ('PGDK','GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT')))

				SET @l_TLID =(SELECT TLID FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN)
			END
			IF(NOT EXISTS (SELECT * FROM AbpUserRoles WHERE UserId=(SELECT ID FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN)
      --(SELECT ID FROM TL_USER WHERE TLID=@l_TLID)
      AND RoleId=(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID) ))
			BEGIN
				INSERT INTO [dbo].[AbpUserRoles] ([CreationTime],[CreatorUserId],[RoleId],[TenantId],[UserId])
				VALUES
					   (GETDATE()
					   ,NULL
					   ,(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID)
					   ,1
             ,(SELECT ID FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN))
					   --,(SELECT ID FROM TL_USER WHERE TLID=@l_TLID))
			END
			---------------------------------------------------------------------------------
			--KIEM TRA DON VI NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã đơn vị này không tồn tại' ErrorDesc
				RETURN '-1'
			END
			--KIEM TRA PHONG BAN NAY CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE =@p_DEP_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Mã phòng ban này không tồn tại' ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------
			--KIEM TRA PHONG BAN CO THUOC VE DUNG DON VI CUA NO HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_DEPARTMENT WHERE BRANCH_ID =@p_BRANCH_ID AND DEP_ID =@p_DEP_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, ''  EMP_ID, N'Phòng ban có mã '+@p_DEP_CODE+ N' không thuộc về đơn vị '+@p_BRANCH_CODE ErrorDesc
				RETURN '-1'
			END
			---------------------------------------------------------------------------------

				UPDATE CM_EMPLOYEE 
				SET [EMP_CODE] = @p_CODE,
				[EMP_NAME] = @p_NAME,
				[BRANCH_ID] = @p_BRANCH_ID,
				[DEP_ID] = @p_DEP_ID, AUTH_STATUS ='A', RECORD_STATUS ='1'
				WHERE  EMP_CODE= @p_CODE
				--
				--SET @p_BRANCH_NAME = (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_CODE)
				UPDATE TL_USER SET 
				[TLSUBBRID] = @p_BRANCH_ID,
				[BRANCH_NAME] = @p_BRANCH_NAME,
				[BRANCH_TYPE] = @p_BRANCH_TYPE,
				AUTH_STATUS ='A',
				--TLNANME = @p_USER_DOMAIN,
				--RoleName=@p_ROLE,
				EMAIL=@EMAIL,
				TLFullName =@p_NAME,
				SECUR_CODE=@p_DEP_ID,
				 DEP_ID=@p_DEP_ID,
				 CreationTime = GETDATE()
				WHERE  TLNANME= @p_USER_DOMAIN 
				AND (RoleName NOT IN ('PGDK','GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT')
                OR NOT EXISTS(SELECT 1 FROM AbpUserRoles aur JOIN AbpRoles ar ON aur.RoleId = ar.Id JOIN TL_USER tu ON (tu.ID = aur.UserId AND tu.TLNANME=@p_USER_DOMAIN) 
				WHERE ar.DisplayName IN ('PGDK','GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT')))


				UPDATE TL_USER SET RoleName ='NVMS' WHERE TLNANME= @p_USER_DOMAIN AND RoleName ='DISABLE'


--				UPDATE AbpUserRoles SET RoleId=(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID)
--				WHERE UserId=((SELECT ID FROM TL_USER WHERE TLNANME= @p_USER_DOMAIN AND RoleName NOT IN ('PGDK','GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT')) )

        -- PHONGNT 7/12/2022 KTRA CHUA TỒN TẠI AbpUserRoles sẽ được thêm mới 
        IF(NOT EXISTS (SELECT 1 FROM AbpUserRoles WHERE UserId=(SELECT ID FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN)
          AND RoleId=(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID) ) AND @ROLE_ID NOT IN ('PGDK','GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT'))
    			BEGIN
    				INSERT INTO [dbo].[AbpUserRoles] ([CreationTime],[CreatorUserId],[RoleId],[TenantId],[UserId])
    				VALUES
    					   (GETDATE()
    					   ,NULL
    					   ,(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID)
    					   ,1
                 ,(SELECT ID FROM TL_USER WHERE TLNANME=@p_USER_DOMAIN))
    			END
				---
				IF @@Error <> 0 GOTO ABORT	
				---- DONG BO XONG CM_EMPLOYEE THI DONG BO NGUOI DUNG LUON - LUCTV BO SUNG NGAY 08 07 2020
				--EXEC [TL_USER_Sync] 'U',@p_USER_DOMAIN,@p_NAME,@p_BRANCH_CODE,@p_DEP_CODE,@EMAIL,@ROLE_ID
				---  END LUCTV
				UPDATE TL_USER SET ROLENAME ='GDV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giao dịch viên' and branch_code <> '069') 
				AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
				UPDATE TL_USER SET ROLENAME ='KSV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Kiểm soát viên' and branch_code <> '069') 
				AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
				UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giám đốc chi nhánh' and branch_code <> '069')
				AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
				UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc chi nhánh' and branch_code <> '069') 
				AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
				UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Trưởng phòng giao dịch' and branch_code <> '069')
				AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
				UPDATE TL_USER SET ROLENAME ='PPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó phòng giao dịch' and branch_code <> '069')
				AND ROLENAME <> 'DISABLE' AND TLNANME =@p_USER_DOMAIN
				--UPDATE TL_USER SET ROLENAME ='PP' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'%Phó phòng%' and branch_code = '069' AND DEP_CODE <> '0690802') AND ROLENAME <> 'DISABLE'
				--UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'%Trưởng phòng%' and branch_code = '069' AND DEP_CODE <> '0690802') AND ROLENAME <> 'DISABLE'

		END
		ELSE IF(@p_ACTION='D')
		BEGIN
			--KIEM TRA XEM MA NHAN VIEN CO TON TAI HAY KHONG
			IF (NOT EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE  EMP_CODE= @p_CODE))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, N'Mã nhân viên không tồn tại' ErrorDesc
				RETURN '-1'
			END
			BEGIN
			IF( (SELECT AUTH_STATUS FROM CM_EMPLOYEE WHERE  EMP_CODE= @p_CODE) = 'A')
			BEGIN
				UPDATE CM_EMPLOYEE SET RECORD_STATUS = '0' WHERE EMP_CODE=@p_CODE
				IF @@Error <> 0 GOTO ABORT
			END
			ELSE
			BEGIN
				Delete FROM CM_EMPLOYEE WHERE  EMP_ID= @p_CODE
				IF @@Error <> 0 GOTO ABORT
			END
			END
			-- DUA NHAN VIEN VE DISABLE
			UPDATE TL_USER SET AUTH_STATUS ='U', RoleName ='DISABLE' WHERE TLNANME =@p_USER_DOMAIN
			UPDATE TL_SYS_ROLE_MAPPING SET EFF_DATE= CONVERT(DATETIME,'1-1-2000',103) ,EXP_DATE=CONVERT(DATETIME,'1-1-2000',103) WHERE TLNAME = @p_USER_DOMAIN
			---LUCTV 22052023_SECRETKEY NHÂN VIÊN ĐÓ NGHĨ VIỆC THÌ GỠ HẾT ROLE CŨ RA VÀ CHỈ CÒN DUY NHẤT ROLE DISABLE
			DELETE AbpUserRoles WHERE UserId= (SELECT ISNULL(ID,'') FROM TL_USER WHERE TLNANME=@p_USER_DOMAIN)
		END
		--- BẮT ĐẦU CHẠY LỆNH QUÉT CÁC ROLE
		UPDATE TL_USER SET ROLENAME ='GDV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giao dịch viên' and branch_code <> '069') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='KSV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Kiểm soát viên' and branch_code <> '069') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giám đốc chi nhánh' and branch_code <> '069') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc chi nhánh' and branch_code <> '069') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Trưởng phòng giao dịch' and branch_code <> '069') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='PPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó phòng giao dịch' and branch_code <> '069') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giám đốc trung tâm kinh doanh') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc trung tâm kinh doanh') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Phó giám đốc TTKD') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME =N'Giám đốc TTKD') AND ROLENAME <> 'DISABLE'
		AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Giám đốc chi nhánh%' and branch_code <> '069')
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='PGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Phó giám đốc chi nhánh%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng giao dịch%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='PPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Phó phòng giao dịch%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='NVMS' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng%' and branch_code <> '069')
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='CN' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='NVMS' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Phó phòng%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='CN' AND TLNANME =@p_USER_DOMAIN--2021824
		UPDATE TL_USER SET ROLENAME ='KSV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng dịch vụ khách hàng' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='KSV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Phó phòng dịch vụ khách hàng' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='KSV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng DVKH' and branch_code <> '069')
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='KSV_DVKD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Phó phòng DVKH' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='PP' AND TLSUBBRID <>'DV0001' AND TLNANME =@p_USER_DOMAIN AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='TP' AND TLSUBBRID <>'DV0001' AND TLNANME =@p_USER_DOMAIN AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='TPGD' AND TLSUBBRID = 'DV0001' AND TLNANME =@p_USER_DOMAIN AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='KSV' AND SECUR_CODE <>'DEP000000000022' AND TLNANME =@p_USER_DOMAIN AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='GDV' AND SECUR_CODE <>'DEP000000000022' AND TLNANME =@p_USER_DOMAIN AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng SMES%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='PGD' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng SMES%' and branch_code <> '069')
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='CN' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng QHKH SMES%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='PGD' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng QHKH SMES%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='CN' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng SMES%' and branch_code <> '069') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='PGD' AND TLNANME =@p_USER_DOMAIN
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng SMES%' and branch_code <> '069')
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='CN' AND TLNANME =@p_USER_DOMAIN
		----LUCTV.12052023_SECRETKEY - FIX ĐỐI VỚI KSV PHÒNG KẾ TOÁN ƯU TIÊN ROLENAME ='KSV'
		UPDATE TL_USER SET RoleName ='KSV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng bộ phận%' and branch_code = '069') 
		AND SECUR_CODE ='DEP000000000022' AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='KSV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng%' and branch_code = '069') AND SECUR_CODE ='DEP000000000022' AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='KSV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Phó phòng%' and branch_code = '069') AND SECUR_CODE ='DEP000000000022' AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='GDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Chuyên viên%' and branch_code = '069') AND SECUR_CODE ='DEP000000000022' AND ROLENAME <> 'DISABLE'
		UPDATE TL_USER SET RoleName ='TC' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng%' and branch_code = '069') AND SECUR_CODE ='DEP000000000023' AND ROLENAME <> 'DISABLE' 
    --NGUYENTD 19092024_SECRETKEY: FIX LỖI USER NGHỈ VIỆC NHƯNG VẪN CẬP NHẬT LẠI ROLE
		----LUCTV.16052023_SECRETKEY - FIX ĐỐI VỚI CHỨC DANH Trưởng phòng QHKH Doanh nghiệp thuộc BRNACH 60x MAPPING GDDV
		UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT ISNULL(USER_DOMAIN,'') FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Trưởng phòng QHKH Doanh nghiệp%' and branch_code LIKE '60%') 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='CN' AND TLNANME =@p_USER_DOMAIN
		---- LUCTV 13102023_SECRETKEY: GIÁM ĐỐC PGD ĐẶC THÙ VẪN LÀ TPGD
		UPDATE TL_USER SET ROLENAME ='TPGD' WHERE TLNANME IN (SELECT ISNULL(USER_DOMAIN,'') FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Giám đốc phòng giao dịch%' AND BRANCH_CODE =@p_BRANCH_CODE
		AND USER_DOMAIN =@p_USER_DOMAIN) 
		AND ROLENAME <> 'DISABLE' AND TLSUBBRID <> 'DV0001' AND BRANCH_TYPE ='PGD' AND TLNANME =@p_USER_DOMAIN
		---END
		SET @ROLE_ID =(SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN)
		--- LUCTV 17052023_SECRETKEY NHỮNG ROLE NVTT /NVMS CÓ THỂ XÓA ĐI ĐỂ GHI LẠI BẢN GHI MỚI VÌ ĐÂY LÀ NHỮNG ROLE BASIC, ƯU TIÊN NHỮNG ROLE CAO HƠN, VÍ DỤ ÔNG A TỪ NVMS LÊN GDDV
		DELETE AbpUserRoles WHERE UserId= (SELECT ISNULL(ID,'') FROM TL_USER WHERE TLNANME=@p_USER_DOMAIN) AND RoleId IN (SELECT Id FROM AbpRoles WHERE DisplayName IN ('NVTT','NVMS','GDV_DVKD','KSV_DVKD'))
		IF(NOT EXISTS (SELECT 1 FROM AbpUserRoles WHERE UserId=(SELECT ID FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN)
		AND RoleId=(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID) ) AND @ROLE_ID NOT IN ('PGDK','GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT'))
			BEGIN
				INSERT INTO [dbo].[AbpUserRoles] ([CreationTime],[CreatorUserId],[RoleId],[TenantId],[UserId])
				VALUES
					   (GETDATE()
					   ,NULL
					   ,(SELECT Id FROM AbpRoles WHERE DisplayName=@ROLE_ID)
					   ,1
             ,(SELECT ID FROM TL_USER WHERE TLNANME=@p_USER_DOMAIN))
			END
  --  END
    IF @@Error <> 0 GOTO ABORT  
		--UPDATE TL_USER SET ROLENAME ='GDDV' WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE POS_NAME LIKE N'Giám đốc%' and branch_code = '069') AND ROLENAME <> 'DISABLE' AND BRANCH_TYPE ='HS'

    EXEC TL_SYS_ROLE_MAPPING_Sync @p_USER_DOMAIN, 'admin'
	-- NGUYENTD 13.03.2025 NẾU USER BỊ DISABLE ( NGHỈ VIỆC) THÌ CHẠY XOÁ PERMISION KIÊM NHIỆM CỦA USER ĐÓ
	IF(EXISTS(SELECT 1 FROM TL_USER WHERE TLNANME = @p_USER_DOMAIN AND RoleName = 'DISABLE'))
	BEGIN
		EXEC dbo.SYS_PERMISSIONS_PAGE_FOR_USER_AutoRemove
	END
COMMIT TRANSACTION
SELECT '0' as Result, @p_CODE  TLID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' TLID, '' ErrorDesc
		RETURN '-1'
END