ALTER PROC dbo.THREAD_GET_CONTENT_SEND_MAIL
@p_TIME_RUN_TOOL VARCHAR(50) = NULL,
@p_TIME_SEND_ID INT = NULL, 
@p_TIME_VALUE VARCHAR(100) = NULL,
@p_LOCATION VARCHAR(20) = NULL
AS
BEGIN 

  IF(@p_TIME_VALUE IS NULL OR @p_TIME_VALUE = '' OR @p_TIME_SEND_ID IS NULL OR @p_TIME_SEND_ID = '')
  RETURN '-1'

  DECLARE   @DISTANCE_TIME INT = NULL,
            @DISTANCE_TYPE VARCHAR(20) = NULL,
            @TOOL_NAME NVARCHAR(1000) = NULL,
            @TOOL_VALUE VARCHAR(100) = NULL,
            @EXEC_DT DATETIME = NULL,            
            @MESSAGE NVARCHAR(1000) = NULL,
            @EXEC_TYPE VARCHAR(20) = NULL

  IF(@p_LOCATION = 'EXEC')
  BEGIN
      SET @EXEC_TYPE = 'EXECUTE'
  END
  ELSE
  BEGIN
      SET @EXEC_TYPE = 'AUTO'
  END

  SELECT  @DISTANCE_TIME = TIME_SEND        
        ,@DISTANCE_TYPE = TIME_SEND_TYPE
        ,@TOOL_NAME = TIME_CONTENT
        ,@TOOL_VALUE = TIME_VALUE,
        @EXEC_DT = SENT_DATE                
  FROM THREAD_TIME_SEND 
  WHERE TIME_VALUE = @p_TIME_VALUE

	DECLARE	@TEMP TABLE
			(
				[ID]				VARCHAR(20),
				[EMAIL]				VARCHAR(50),
				[NF_MESSAGE_TYPE]	VARCHAR(500),
				[ROLE_TIFI_TYPE]	VARCHAR(500),
        IS_SENDMAIL BIT,
        IS_RUN_TOOL BIT,
        TOOL_NAME NVARCHAR(100),
        QUERY_SELECT NVARCHAR(MAX)
        
			)
	DECLARE	@TIME_SEND			INT = NULL,
			@TIME_SEND_TYPE		VARCHAR(20) = NULL,
			@TIME_VALUE			VARCHAR(25) = NULL,
			@NF_MESSAGE_TYPE	VARCHAR(50) = NULL,
			@ROLE_TIFI_TYPE		VARCHAR(50) = NULL,
      @SEND_DATE DATETIME = NULL,
      @QUERY_SELECT NVARCHAR(MAX) = NULL,

      @EXEC_DATE VARCHAR(50) = NULL

	SELECT @TIME_SEND = A.TIME_SEND,@TIME_SEND_TYPE = A.TIME_SEND_TYPE,
  @TIME_VALUE = A.TIME_VALUE,@NF_MESSAGE_TYPE = A.NF_MESSAGE_TYPE, 
  @ROLE_TIFI_TYPE = A.ROLE_TIFI_TYPE,@SEND_DATE = A.SENT_DATE
	FROM THREAD_TIME_SEND A
	WHERE (A.[STATUS] = 1 OR @p_LOCATION = 'EXEC') AND A.TIME_VALUE = @p_TIME_VALUE AND A.TIME_SEND_ID = @p_TIME_SEND_ID
	 
  
  --FIX THREAD NẾU START VÀ STOP TOOL NHIỀU LẦN THÌ BỊ DUP EXEC
  IF(@p_LOCATION <> 'EXEC' OR @p_LOCATION IS NULL OR @p_LOCATION = '')
  BEGIN
      IF(DATEDIFF(DAYOFYEAR,CONVERT(DATETIME,@p_TIME_RUN_TOOL,103),@SEND_DATE) <> 0)
      BEGIN  
      	  SET @MESSAGE = N'Ngày không hợp lệ'
          GOTO ABORT
      END
      
  END

  BEGIN --TRANSACTION T1

      IF(@p_TIME_VALUE = 'SYNC_EMP')
      BEGIN --TOOL ĐỒNG BỘ NGƯỜI DÙNG
          --SET @QUERY_SELECT = N'SELECT Id,Loai,MaNS,HoTen,MaDVCu,DVCu,MaDVMoi,DVMoi,MaCDCu,CDCu,MaCDMoi,CDMoi,LoaiHDLD,Email,Ngay,So,NgayHLuc,NgayHetHLuc,TGTao FROM CM_EMPLOYEE_SYNC WHERE DATEDIFF(DAYOFYEAR,CONVERT(DATETIME,''' + (SELECT TOP 1 CDVAL FROM CM_ALLCODE WHERE CDNAME = 'LAST_SYNC_DATE' AND CDTYPE = 'LAST_SYNC_DATE') + '''),TGTao) >= 0  ORDER BY TGTao'
          SET @QUERY_SELECT = N'SELECT Id,Loai,MaNS,HoTen,MaDVCu,DVCu,MaDVMoi,DVMoi,MaCDCu,CDCu,MaCDMoi,CDMoi,LoaiHDLD,Email,Ngay,So,NgayHLuc,NgayHetHLuc,TGTao FROM CM_EMPLOYEE_SYNC ORDER BY TGTao'

          INSERT INTO @TEMP (IS_RUN_TOOL, TOOL_NAME, QUERY_SELECT)
          VALUES (1,'CM_EMPLOYEE_SYNC',@QUERY_SELECT);
    
          IF(@p_LOCATION IS NULL)
          BEGIN
                IF @@Error <> 0 GOTO ABORT

                UPDATE SYS_PARAMETERS SET ParaValue = CONVERT(DATE,GETDATE()) WHERE ParaKey = 'LAST_SYNC_DATE' 
          END
    
      END  
      ELSE IF(@p_TIME_VALUE = 'CHECK_WARRANTY_DT')
      BEGIN --TOOL GỬI MAIL THÔNG BÁO TÀI SẢN SẮP HẾT HẠN BẢO HÀNH
          INSERT INTO @TEMP (IS_SENDMAIL, TOOL_NAME,NF_MESSAGE_TYPE,ROLE_TIFI_TYPE)
          VALUES (1,N'GỬI MAIL',@NF_MESSAGE_TYPE,@ROLE_TIFI_TYPE);
      END
      ELSE IF(@p_TIME_VALUE = 'CHECK_TRANS_NOT_APPROVE')
      BEGIN --GỬI MAIL NHỮNG GIAO DỊCH CHƯA ĐƯỢC PHÊ DUYỆT
          INSERT INTO @TEMP (IS_SENDMAIL, TOOL_NAME,NF_MESSAGE_TYPE,ROLE_TIFI_TYPE)
          
          SELECT 1,N'CHECK GIAO DỊCH CHƯA DUYỆT',@NF_MESSAGE_TYPE + '|' + PP.ID, @ROLE_TIFI_TYPE + '|' + PP.ID
          FROM (
              SELECT A.USER_MASTER_ID + '|ASS_USE|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_USE_MULTI_MASTER A
              LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E' AND B.USER_CONFIRM_MASTER_ID IS NOT NULL) 
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.TRANS_MULTI_MASTER_ID + '|ASS_TRANSFER|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_TRANSFER_MULTI_MASTER A                  
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E')
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.COL_MULTI_MASTER_ID + '|ASS_COLLECT|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_COLLECT_MULTI_MASTER A
              LEFT JOIN ASS_COLLECT_CONFIRM_MASTER B ON A.COL_MULTI_MASTER_ID = B.COL_MULTI_MASTER_ID
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E' AND B.COL_MULTI_MASTER_CONFIRM_ID IS NOT NULL)
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.LIQ_ID + '|ASS_LIQ|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_LIQUIDATION A
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E')
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U')
              UNION
              SELECT A.ADDNEW_ID + '|ASS_ADDNEW|' + CASE WHEN A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U' THEN 'KSV'	ELSE 'GDV' END AS ID
              FROM ASS_ADDNEW A
              WHERE (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'E')
                    OR (A.AUTH_STATUS = 'A' AND A.AUTH_STATUS_KT = 'U'))PP              
    
      END
      ELSE IF(@p_TIME_VALUE = 'KH')
      BEGIN --TOOL CHẠY KHẤU HAO             
          SET @EXEC_DATE = CONVERT(VARCHAR(50),@SEND_DATE)
          IF((SELECT (((DATEPART(DW, @SEND_DATE) - 1 ) + @@DATEFIRST ) % 7)) NOT IN ('0','6'))
          BEGIN -- NẾU KHÁC NGÀY T7 VÀ CN THÌ CHẠY
              EXEC ASS_AMORT_StartDO @p_EXECUTE_DATE =  @EXEC_DATE
                                    ,@p_MAKER_ID = 'bichnn'
                                    ,@p_CHECKER_ID = 'bichnn'
                                    ,@p_ASSET_TYPE = NULL
                                    ,@p_BRANCH_ID_LST = NULL   
                                    ,@p_EXEC_TYPE = @EXEC_TYPE
         END                                     
      END

      IF @@Error <> 0 GOTO ABORT
    
      IF(@p_LOCATION IS NULL OR @p_LOCATION = '')
      BEGIN -- NẾU CHẠY TOOL THÌ UPDATE NGÀY, EXEC TRỰC TIẾP THÌ KHÔNG UPDATE NGÀY
          IF(@TIME_SEND_TYPE = 'NGAY')
    			BEGIN
                UPDATE THREAD_TIME_SEND 
                SET SENT_DATE = DATEADD(DAY,@TIME_SEND,@SEND_DATE)
                WHERE TIME_SEND_ID = @p_TIME_SEND_ID AND TIME_VALUE = @p_TIME_VALUE
          END
    			ELSE IF(@TIME_SEND_TYPE = 'THANG')
    			BEGIN
                UPDATE THREAD_TIME_SEND 
                SET SENT_DATE = DATEADD(MONTH,@TIME_SEND,@SEND_DATE)
                WHERE TIME_SEND_ID = @p_TIME_SEND_ID AND TIME_VALUE = @p_TIME_VALUE
    			END
      END

      
  
--  COMMIT TRANSACTION T1
  END
  SELECT * FROM @TEMP  
  
  IF(@p_TIME_VALUE <> 'KH')
  BEGIN
      INSERT INTO THREAD_TIME_SEND_LOG (DISTANCE_TIME, DISTANCE_TYPE, TOOL_NAME, TOOL_VALUE, EXEC_DT, EXEC_TYPE, MESSAGE, CREATE_DT)
      VALUES (@DISTANCE_TIME,@DISTANCE_TYPE, @TOOL_NAME, @TOOL_VALUE, @EXEC_DT,@EXEC_TYPE, N'Thực thi thành công', GETDATE()); 
  END

  RETURN '0'
  ABORT:
  BEGIN
  		--ROLLBACK TRANSACTION T1
      --GHI LOG
        IF(@p_TIME_VALUE <> 'KH')
        BEGIN
            INSERT INTO THREAD_TIME_SEND_LOG (DISTANCE_TIME, DISTANCE_TYPE, TOOL_NAME, TOOL_VALUE, EXEC_DT, EXEC_TYPE, MESSAGE, CREATE_DT)
            VALUES (@DISTANCE_TIME,@DISTANCE_TYPE, @TOOL_NAME, @TOOL_VALUE, @EXEC_DT,@EXEC_TYPE, ISNULL(@MESSAGE,ERROR_MESSAGE()), GETDATE()); 
        END
      RETURN '-1'
  End

	
END

GO

ALTER TRIGGER dbo.TRIG_CM_EMPLOYEE_SYNC_Ins
	ON dbo.CM_EMPLOYEE_SYNC
	AFTER INSERT
AS 
BEGIN
  --STORE ĐỒNG BỘ NHÂN VIÊN VÀ ĐIỀU CHUYỂN TÀI SẢN NHÂN SỰ
  --29/09/22
	SET NOCOUNT ON;
          IF (EXISTS(SELECT TOP 1 MaNS FROM INSERTED)) 
          BEGIN  
          
              DECLARE @MaDV_Cu_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVCu FROM INSERTED)
              DECLARE @MaDV_Moi_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVMoi FROM INSERTED) 

              --UPDATE MÃ MỚI DO KT UPDATE
              IF(@MaDV_Cu_SYNC = '0100')
              BEGIN
                  SET @MaDV_Cu_SYNC = '0600'
              END

              IF(@MaDV_Cu_SYNC = '0101')
              BEGIN
                  SET @MaDV_Cu_SYNC = '0601'
              END

              IF(@MaDV_Moi_SYNC = '0100')
              BEGIN
                  SET @MaDV_Moi_SYNC = '0600'
              END

              IF(@MaDV_Moi_SYNC = '0101')
              BEGIN
                  SET @MaDV_Moi_SYNC = '0601'
              END
                
              --MAP DATA HR VỚI KT
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC))
              BEGIN
                  SET @MaDV_Cu_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC)
              END
              IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC))
              BEGIN
                  SET @MaDV_Moi_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC)
              END
              DECLARE @MACD_CU_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDCu FROM INSERTED)
              DECLARE @MACD_MOI_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDMoi FROM INSERTED)
    
            --START: PARAM INSERT NHÂN VIÊN MỚI
              DECLARE @l_EMP_ID VARCHAR(500)
              DECLARE @p_EMP_CODE	varchar(500)  = (SELECT TOP 1 MaNS FROM INSERTED)
              DECLARE @p_EMP_NAME	nvarchar(500)  = (SELECT TOP 1 HoTen FROM INSERTED)
      
              DECLARE @p_BRANCH_ID	varchar(15) = NULL 
              DECLARE @p_DEP_ID	varchar(15) = NULL             
              
              DECLARE @p_POS_CODE VARCHAR(50) = NULL
              DECLARE @p_POS_NAME NVARCHAR(MAX) = NULL             
      
              DECLARE @p_NOTES	nvarchar(1000)  = N'ĐỒNG BỘ'
              DECLARE @p_RECORD_STATUS	varchar(1)  = '1'
              DECLARE @p_AUTH_STATUS	varchar(1)  = 'A'
              DECLARE @p_MAKER_ID	varchar(15)  = 'ADMIN'
              DECLARE @p_CREATE_DT	VARCHAR(50) = GETDATE()
              DECLARE @p_CHECKER_ID	varchar(15)  = 'ADMIN'
              DECLARE @p_APPROVE_DT	VARCHAR(50) = GETDATE()
            --END: PARAM INSERT NHÂN VIÊN MỚI
    
            --START: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
              DECLARE @I_EMP_ID VARCHAR(20) = (SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED))
              DECLARE @I_STATUS VARCHAR(5) 
    
              DECLARE @C_Ma_DV_Cu VARCHAR(500) = NULL  --MÃ ĐV CŨ ĐỂ CHECK IF
              DECLARE @C_Ma_DV_Moi VARCHAR(500) = NULL --MÃ DV MỚI ĐỂ CHECK IF
              DECLARE @C_Ma_PB_Cu VARCHAR(500) = NULL  --MÃ PB CŨ ĐỂ CHECK IF
              DECLARE @C_Ma_PB_Moi VARCHAR(500) = NULL --MÃ PB MỚI ĐỂ CHECK IF
            --END: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG
    
            --START: PARAM INSERT 
    
            --CHECK XEM ĐƠN VỊ/PHÒNG BAN CỦA NHÂN VIÊN MỚI CỬA KH LƯU TRONG CỘT MaDVCu HAY CỘT MaDVMoi
            IF (EXISTS(SELECT TOP 1 MaDVCu FROM INSERTED WHERE MaDVCu IS NOT NULL AND MaDVCu <> ''))
            BEGIN 
                --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC))
                BEGIN
                    SET @p_BRANCH_ID = 'DV0001'
                    SET @C_Ma_DV_Cu = 'DV0001'
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
                    SET @C_Ma_PB_Cu = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)
                END
                ELSE
                BEGIN
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
                    SET @C_Ma_DV_Cu = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC)
                END         
            END
            --ELSE
    
            IF(EXISTS(SELECT TOP 1 MaDVMoi FROM INSERTED WHERE MaDVMoi IS NOT NULL AND MaDVMoi <> ''))
            BEGIN
                --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN
                IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = (SELECT MaDVMoi FROM INSERTED)))
                BEGIN
                    SET @p_BRANCH_ID = 'DV0001'
                    SET @C_Ma_DV_Moi = 'DV0001'
                    SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
                    SET @C_Ma_PB_Moi = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC)
                END
                ELSE
                BEGIN
                    SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
                    SET @C_Ma_DV_Moi = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC)
                END
            END              
                               
            PRINT @C_Ma_DV_Cu
            PRINT @C_Ma_DV_Moi
        		IF((@C_Ma_DV_Cu IS NULL OR @C_Ma_DV_Cu = '') AND (@C_Ma_DV_Moi IS NULL OR @C_Ma_DV_Moi = ''))
        		BEGIN
          			--N'Mã DV cũ và Mã DV mới không có giá trị'
                PRINT 1
        		END
            ELSE
            BEGIN
--CHECK DATA CHỨC DANH LƯU CỘT CDMOI HAY CDCU
            IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDMoi IS NOT NULL AND MaCDMoi <> ''))
            BEGIN
                  --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_MOI_SYNC))
                  BEGIN
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
                       VALUES (@MACD_MOI_SYNC, (SELECT TOP 1 CDMoi FROM INSERTED), NULL, NULL);
                  END
    
                  SET @p_POS_CODE = @MACD_MOI_SYNC
                  SET @p_POS_NAME = (SELECT TOP 1 CDMoi FROM INSERTED)
            END
            ELSE IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDCu IS NOT NULL AND MaCDCu <> ''))
            BEGIN
                  --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH
                  IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_CU_SYNC))
                  BEGIN
                       INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID)
                       VALUES (@MACD_CU_SYNC, (SELECT TOP 1 CDCu FROM INSERTED), NULL, NULL);
                  END
    
                  SET @p_POS_CODE = @MACD_CU_SYNC
                  SET @p_POS_NAME = (SELECT TOP 1 CDCu FROM INSERTED)
            END
    
    
             --CHECK NHÂN VIÊN XEM CÓ XEM TRONG HỆ THỐNG KHÔNG
             IF(EXISTS(SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED)))
             BEGIN
                
                IF(EXISTS(
                        SELECT atmd.ASSET_ID FROM ASS_TRANSFER_MULTI_DT atmd
                        WHERE (atmd.EMP_ID = @I_EMP_ID AND atmd.EMP_ID_OLD = @I_EMP_ID AND (atmd.BRANCH_ID = @C_Ma_DV_Moi AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu) 
      																				 AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC CHO CHÙNG NV
                              OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu AND atmd.EMP_ID <> @I_EMP_ID AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC NHƯNG KHÁC NV
                        UNION ALL
                        SELECT am.ASSET_ID FROM ASS_MASTER am WHERE am.BRANCH_ID = @C_Ma_DV_Cu AND am.EMP_ID = @I_EMP_ID))
                  SET @I_STATUS = '0'
                ELSE
                  SET @I_STATUS = '1'
      
                INSERT INTO CM_EMPLOYEE_TRANSFER_LOG (EMP_ID, TRANSFER_DT, BRANCH_ID_OLD, BRANCH_ID_NEW, STATUS)
                VALUES (@I_EMP_ID, CONVERT(DATETIME,(SELECT TOP 1 TGTao FROM INSERTED),103), @C_Ma_DV_Cu, @C_Ma_DV_Moi, @I_STATUS);  
      
                --PHUCVH 22/12/22 CHECK NẾU MADV,PB MỚI THAY ĐỔI THÌ UPDATE LẠI MADV,PB CHO NHANVIEN
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE 
                              AND (ISNULL(A.BRANCH_ID,'') <> ISNULL(@p_BRANCH_ID,'')
                                  OR ISNULL(A.DEP_ID,'') <> ISNULL(@p_DEP_ID,''))))
                BEGIN
                      UPDATE CM_EMPLOYEE SET BRANCH_ID = @p_BRANCH_ID, DEP_ID = @p_DEP_ID 
                      WHERE EMP_CODE = @p_EMP_CODE
    
                      --CHECK UPDATE TL_USER VÀ CM_EMPLOYEE_LOG
--                      IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE))
--                      BEGIN
--                          UPDATE CM_EMPLOYEE_LOG SET BRANCH_CODE = (SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID),
--                                                     DEP_CODE = (SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
--                          WHERE EMP_CODE = @p_EMP_CODE
--    
--                          UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID
--                          WHERE TLNANME IN (SELECT USER_DOMAIN FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE)
--                      END
    
                END  
                
                --PHUCVH 22/12/22 CHECK NẾU MA CHUC DANH THAY ĐỔI THÌ UPDATE
                IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE
                                AND ISNULL(A.POS_CODE,'') <> ISNULL(@p_POS_CODE,'')))
                BEGIN
                     UPDATE CM_EMPLOYEE SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
                     WHERE EMP_CODE = @p_EMP_CODE
    
                     UPDATE CM_EMPLOYEE_LOG SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME
                     WHERE EMP_CODE = @p_EMP_CODE
                END                     
    
                --IF @@Error <> 0 GOTO ABORT
          		  --SET @Message = N'Đồng bộ điều chuyển nhân sự thành công'
             END
             ELSE
             BEGIN
                --KHÔNG CÓ TRONG HỆ THỐNG
                --THÊM THÔNG TIN NHÂN VIÊN
                		EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out		       
                		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],[POS_CODE],[POS_NAME])
                		VALUES(@l_EMP_ID ,@p_EMP_CODE ,@p_EMP_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_POS_CODE, @p_POS_NAME )
                    --IF @@Error <> 0 GOTO ABORT
            				--SET @Message = N'Đồng bộ nhân viên mới thành công'
             END
            END
    
            
          END
END