EOFFICE - GD4

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