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

              DECLARE @TLNAME VARCHAR(200) = NULL

              DECLARE @BLOCK_ID_OLD VARCHAR(15) = NULL --Mã khối cũ check điều chuyển nhân sự
              DECLARE @CENTER_ID_OLD VARCHAR(15) = NULL  --Mã trung tâm cũ check điều chuyển nhân sự
              DECLARE @DEP_ID_OLD VARCHAR(15) = NULL  --Mã phòng ban cũ check điều chuyển nhân sự

              DECLARE @BLOCK_ID_NEW VARCHAR(15) = NULL  --Mã khối mới check điều chuyển nhân sự
              DECLARE @CENTER_ID_NEW VARCHAR(15) = NULL  --Mã trung tâm mới check điều chuyển nhân sự
              DECLARE @DEP_ID_NEW VARCHAR(15) = NULL  --Mã phòng ban mới check điều chuyển nhân sự
    
            --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        
            
            --GET KHỐI, TRUNG TÂM, PHÒNG BAN
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Cu ,@BLOCK_ID_OLD OUT ,@CENTER_ID_OLD OUT , @DEP_ID_OLD OUT   
                                   
            --GET KHỐI, TRUNG TÂM, PHÒNG BAN
            EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Moi, @BLOCK_ID_NEW OUT ,@CENTER_ID_NEW OUT ,@DEP_ID_NEW OUT     
                               
            IF(@p_BRANCH_ID <> 'DV0001')
            BEGIN
                SET @p_DEP_ID = NULL
            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
            
              IF((SELECT COUNT(*) FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE) > 1)
              BEGIN
                    SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE 
                              AND CEL.BRANCH_CODE = @MaDV_Cu_SYNC
                              AND (CEL.DEP_CODE = @C_Ma_PB_Cu OR @C_Ma_PB_Cu IS NULL))
              END
              ELSE
              BEGIN
                  SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE)
              END

            --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 ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'') AND ISNULL(atmd.KHOI_ID,'') = ISNULL(@BLOCK_ID_NEW,'')
                                                    AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'') AND ISNULL(atmd.CENTER_ID,'') = ISNULL(@CENTER_ID_NEW,'')
                                                    AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'') AND ISNULL(atmd.DEPT_ID,'') = ISNULL(@DEP_ID_NEW,''))  
      																				 AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC CHO CÙNG NV
                              OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.EMP_ID <> @I_EMP_ID  
                                  AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu 
                                  AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'')
                                  AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'')
                                  AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'')
                                  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 ISNULL(am.DEPT_ID,'') = ISNULL(@C_Ma_PB_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, DEP_ID_OLD, BRANCH_ID_NEW, DEP_ID_NEW, STATUS)
                VALUES (@I_EMP_ID, CONVERT(DATETIME,(SELECT TOP 1 TGTao FROM INSERTED),103), @C_Ma_DV_Cu,@C_Ma_PB_Cu, @C_Ma_DV_Moi, @C_Ma_PB_Moi, @I_STATUS);  
      
                --CHECK TRƯỜNG HỢP 1 NHÂN VIÊN CÓ QUYỀN 2 CHI NHÁNH TRỞ TRÊN (KHÁC TLNAME)

                --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(@p_BRANCH_ID,'') <> ''
                              AND ((ISNULL(@p_BRANCH_ID,'') = 'DV0001' AND ISNULL(@p_DEP_ID,'') <> '')
                                    OR (ISNULL(@p_BRANCH_ID,'') <> 'DV0001' AND ISNULL(@p_DEP_ID,'') = ''))
                              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 AND USER_DOMAIN = @TLNAME
    
                          UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID
                          WHERE TLNANME = @TLNAME
                      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 AND USER_DOMAIN = @TLNAME
                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

GO
ALTER PROC dbo.ASS_PRIVATE_TRANSFER_MULTI_MASTER_BBBG
@TRANS_MULTI_MASTER_ID	varchar(15) = null
AS 
BEGIN
DECLARE @EMP_RECEIVER VARCHAR(1) = NULL
DECLARE @EMP_HANDOVER VARCHAR(1) = NULL
--Table 0 Nội dung && BRANCH
SELECT TOP 1 B.BRANCH_NAME + ISNULL(' - ' + C.DEP_NAME,'') BRANCH_NAME, 
(N'Hôm nay, ngày ' + CONVERT(VARCHAR(5),DAY(GETDATE())) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(GETDATE())) + N' năm ' + CONVERT(VARCHAR(5),YEAR(GETDATE())) + N' tại ') AS DD_MM_YY
FROM ASS_PRIVATE_TRANSFER_DT A
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID_OLD = B.BRANCH_ID
LEFT JOIN CM_DEPARTMENT C ON A.DEPT_ID = C.DEP_ID
WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID


--Table 1 Bên giao
SELECT TOP 1 D.EMP_NAME TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + F.DEP_NAME,'') AS BRANCH_NAME
FROM ASS_PRIVATE_TRANSFER_DT A 
LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID_OLD = D.EMP_ID
LEFT JOIN CM_EMPLOYEE_LOG C ON D.EMP_CODE = C.EMP_CODE
LEFT JOIN TL_USER B ON C.USER_DOMAIN = B.TLNANME
LEFT JOIN CM_BRANCH E ON B.TLSUBBRID = E.BRANCH_ID
LEFT JOIN CM_DEPARTMENT F ON B.DEP_ID = F.DEP_ID
WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID 
ORDER BY A.EMP_ID_OLD DESC

--Table 2 Bên nhận
SELECT TOP 1 D.EMP_NAME TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + F.DEP_NAME,'') AS BRANCH_NAME
FROM ASS_PRIVATE_TRANSFER_DT A 
LEFT JOIN CM_EMPLOYEE D ON A.EMP_ID = D.EMP_ID
LEFT JOIN CM_EMPLOYEE_LOG C ON D.EMP_CODE = C.EMP_CODE
LEFT JOIN TL_USER B ON C.USER_DOMAIN = B.TLNANME
LEFT JOIN CM_BRANCH E ON B.TLSUBBRID = E.BRANCH_ID
LEFT JOIN CM_DEPARTMENT F ON B.DEP_ID = F.DEP_ID
WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID 
ORDER BY A.EMP_ID DESC

--TABLE 3 DANH SÁCH TÀI SẢN
SELECT 
CASE 
 	WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE
 	ELSE B.ASS_CODE_TMP
 END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY, A.DESCRIPTION AS NOTES, 
 E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, ISNULL(B.PL,'') REQ_CODE, B.NOTES AS QUY_CACH, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT
FROM ASS_PRIVATE_TRANSFER_DT A
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID
LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID 
LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

--TABLE 4 CHỮ KÝ
SELECT TOP 1 B.EMP_NAME AS MAKER_NAME, --BÊN GIAO
ISNULL(B.POS_NAME + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1   PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = D.TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'SEND'
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss'),'')  AS POS_NAME,--BÊN GIAO
C.EMP_NAME AS CHECKER_NAME, --BÊN NHẬN
ISNULL(C.POS_NAME + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1   PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = D.TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'APPROVE'
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss'),'')  AS POS_NAME_2  --BÊN NHẬN
FROM ASS_PRIVATE_TRANSFER_DT A
LEFT JOIN ASS_PRIVATE_TRANSFER_MASTER D ON A.TRANS_MULTI_MASTER_ID = D.TRANS_MULTI_MASTER_ID
LEFT JOIN CM_EMPLOYEE B ON A.EMP_ID_OLD = B.EMP_ID --BÊN GIAO
LEFT JOIN CM_EMPLOYEE C ON A.EMP_ID = C.EMP_ID -- BÊN NHẬN
WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

--TABLE 5 SỐ
SELECT @TRANS_MULTI_MASTER_ID AS NO

--TABLE 6 ĐƠN VỊ TRUNG GIAN
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME
--FROM  ASS_PRIVATE_TRANSFER_MASTER A
--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
--WHERE A.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID AND A.DEPT_CREATE = 'DEP000000000048'


END