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
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) =''

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)

--- LUCTV BO SUNG NGAY 08 07 2020
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)
DECLARE @EMAIL VARCHAR(50) =''
SET @EMAIL =@p_USER_DOMAIN+'@vietcapitalbank.com.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
				SET @ROLE_ID ='NVTT'
				--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])
			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,N'Jul  4 2021 11:22AM',NULL,NULL,NULL,NULL,NULL,NULL)

			--- 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'
			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'
			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'
			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'
			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'
			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'
			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'
			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'
			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'
			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'
			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'
			--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_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])
					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,N'Jul  4 2021 11:22AM',NULL,NULL,NULL,NULL,NULL,NULL)

					--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
				SET @l_TLID =(SELECT ID FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN)
			END
			IF(NOT EXISTS (SELECT * FROM AbpUserRoles WHERE UserId=@l_TLID
      --(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
             ,@l_TLID)
					   --,(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
				WHERE  TLNANME= @p_USER_DOMAIN AND RoleName NOT IN ('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 ('GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT')) )
				---
				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'
				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'
				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'
				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'
				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'
				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'
				--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
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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'
		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' --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'
		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'
		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'
		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'
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='PP' AND TLSUBBRID <>'DV0001'
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='TP' AND TLSUBBRID <>'DV0001'
		UPDATE TL_USER SET RoleName ='NVMS' WHERE RoleName ='TPGD' AND TLSUBBRID = 'DV0001'
		UPDATE TL_USER SET RoleName ='NVTT' WHERE RoleName ='KSV' AND SECUR_CODE <>'DEP000000000022'
		UPDATE TL_USER SET RoleName ='NVTT' WHERE RoleName ='GDV' AND SECUR_CODE <>'DEP000000000022'
		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'
		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'
		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'
		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'
		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'
		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'
		
		SET @ROLE_ID =(SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_DOMAIN)
		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 ('GDK','PTGD','TGD','HDQT','KSV','GDV','KT','TC','KTT','TKTGD','TKHDQT')))
		--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'
		IF @@Error <> 0 GOTO ABORT
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