ALTER   PROC dbo.rpt_PYC_DIEUCHUYEN
@REQ_ID VARCHAR(15) = NULL

AS
BEGIN

DECLARE 
@SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL

--TABLE 0
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, 
am.ASSET_NAME AS ASS_NAME, 
ISNULL(am.ASSET_CODE,am.ASS_CODE_TMP) AS ASSET_CODE,
ISNULL(PB.DEP_NAME,ISNULL(TT.DEP_NAME,K.DEP_NAME))  AS DEP_NAME, 
ce.EMP_NAME, '1' QTY_ETM, 
B.REASON AS REQ_CONTENT
FROM TR_REQUEST_SHOP_DOC A 
LEFT JOIN TR_REQUEST_SHOP_DOC_DT B ON A.REQ_ID = B.REQ_DOC_ID
LEFT JOIN CM_DEPARTMENT K ON B.SUBBRANCH_OLD = K.DEP_ID
LEFT JOIN CM_DEPARTMENT TT ON B.FATHER_OLD = TT.DEP_ID
LEFT JOIN CM_DEPARTMENT PB ON B.DEP_OLD = PB.DEP_ID
LEFT JOIN ASS_MASTER am ON am.ASSET_ID = B.ASS_ID
LEFT JOIN CM_EMPLOYEE ce ON B.EMP_ID = ce.EMP_ID 
WHERE A.REQ_ID = @REQ_ID 
  
--TABLE 1 DVCM KHOI CNTT
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00'))
BEGIN
    SELECT TOP 1 A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(D.APPROVE_DT,'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    LEFT JOIN PL_PROCESS D ON A.REQ_ID = D.REQ_ID AND D.PROCESS_ID = 'S_GDDVDVCM' AND A.MAKER_ID = D.CHECKER_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00'
    ORDER BY D.ID DESC

    SET @SHOW_TABLE_DVCM_IT = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END


--TABLE 2 DVCM KHAC
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00'))
BEGIN
    SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, 
      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 = A.REQ_ID AND PP.PROCESS_ID = 'S_GDDVDVCM'
                                                                  AND PP.CHECKER_ID = A.MAKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00'
    ORDER BY A.COST_ID

    SET @SHOW_TABLE_DVCM_KHAC = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END

--TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS 
SELECT TOP 1 A.HO_NOTES, D.TLFullName, 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 = A.REQ_ID AND (PP.PROCESS_ID = 'S_DVCM' OR PP.PROCESS_ID = 'S_TBP_QLTS')
                                                                  AND PP.CHECKER_ID = PL.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
FROM TR_REQUEST_SHOP_DOC A 
LEFT JOIN PL_REQUEST_PROCESS PL ON A.REQ_ID = PL.REQ_ID AND PL.PROCESS_ID = 'QLTS_N'
LEFT JOIN CM_EMPLOYEE_LOG C ON PL.CHECKER_ID = C.USER_DOMAIN
LEFT JOIN TL_USER D ON C.USER_DOMAIN = D.TLNANME
WHERE A.REQ_ID = @REQ_ID 

--TABLE 4 PHE DUYET DVCM KHOI CNTT
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'))
    BEGIN  
    	    SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'
          ORDER BY C.DEP_ID
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES        
    END
END
ELSE
BEGIN
SELECT N'' NOTES
END

--table 5 PHÊ DUYỆT PHÒNG QLTS
IF(EXISTS(SELECT 1
        FROM  PL_REQUEST_PROCESS A
        LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
        LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME        
        WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'QLTS_D'))
BEGIN
    SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, C.TLFullName, 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 = A.REQ_ID AND PP.PROCESS_ID = 'QLTS_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME 
    FROM  PL_REQUEST_PROCESS A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME    
    WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'QLTS_D'
END
ELSE
BEGIN
    SELECT N'' NOTES, '' TLFullName, '' POS_NAME
END

--Phucvh table 6 USER TẠO DUYỆT
SELECT TOP 1 C.TLFullName AS USER_CREATE, CHAR(13) + ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' +  FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 
                                                                  WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'S_DVKD'
                                                                  AND PP1.CHECKER_ID = A.MAKER_ID
                                                                  ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS  POS_NAME, 
E.TLFullName AS USER_APPROVE, CHAR(13) + ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 
                                                                  WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'DVKD_D'                                                                  
                                                                  ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss')  AS POS_NAME_1
FROM TR_REQUEST_SHOP_DOC A
LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN --NGƯỜI TẠO
LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --NGƯỜI TẠO
LEFT JOIN PL_REQUEST_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND PP.PROCESS_ID = 'APPNEW'
LEFT JOIN CM_EMPLOYEE_LOG D ON PP.CHECKER_ID = D.USER_DOMAIN --NGƯỜI DUYỆT
LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME --NGƯỜI DUYỆT
WHERE A.REQ_ID = @REQ_ID

--PHUCVH TABLE 7 SỐ PYC
SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID

--PHUCVH TABLE 8 NGÀY TDV DUYỆT
SELECT TOP 1 N'Ngày ' +  CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + 
            N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + 
            N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT
FROM PL_REQUEST_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW'
END

--TABLE 9 PHÊ DUYỆT DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN  
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'))
    BEGIN  
    	    SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'
          ORDER BY C.DEP_ID
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES
        FROM TR_REQUEST_SHOP_COSTCENTER A
        LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID
        WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00'
    END   
END
ELSE
BEGIN
    SELECT N'' NOTES
END

--TABLE 10 PHÊ DUYỆT TRUNG TÂM DỊCH VỤ NỘI BỘ 
DECLARE @PRICE_OF_ASSET DECIMAL = (SELECT MAX(b.BUY_PRICE) 
                                      FROM TR_REQUEST_SHOP_DOC_DT A 
                                      LEFT JOIN ASS_MASTER B ON A.ASS_ID = B.ASSET_ID 
                                      WHERE A.REQ_DOC_ID = @REQ_ID)
IF(@PRICE_OF_ASSET > 30000000)
BEGIN
      IF(EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS prp WHERE prp.REQ_ID = @REQ_ID AND prp.PROCESS_ID = 'TTQLTS_D'))
      BEGIN
    	    SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'TTQLTS_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'TTQLTS_D' 
      END
      ELSE
      BEGIN
          SELECT N'' NOTES, '' TLFullName, '' POS_NAME
      END
END
ELSE
BEGIN
    SELECT N'' NOTES, '' TLFullName, '' POS_NAME
END

--TABLE 11 PHÊ DUYỆT TBP_QLTS
SELECT TOP 1 ISNULL(A.NOTES,'') AS HO_NOTES, C.TLFullName, 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 = A.REQ_ID AND PP.PROCESS_ID = 'TBP_D'
                                                              AND PP.CHECKER_ID = A.CHECKER_ID
                                                              ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME 
FROM  PL_REQUEST_PROCESS A
LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME    
WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'TBP_D'

--SHOW TABLE DVCM IT
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    SELECT N'DVCMIT' AS MERGE_REGION
END

--SHOW TABLE DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'DVCMKHAC' AS MERGE_REGION
END

--SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'TITLEDVCM' AS MERGE_REGION
END

GO

ALTER PROC dbo.rpt_PYC_SUACHUA
@REQ_ID VARCHAR(15) = NULL

AS
BEGIN

DECLARE 
@SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL


--TABLE 0
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, 
am.ASSET_NAME AS ASS_NAME, 
ISNULL(am.ASSET_CODE,am.ASS_CODE_TMP) AS ASSET_CODE,
ISNULL(PB.DEP_NAME,ISNULL(TT.DEP_NAME,K.DEP_NAME))  AS DEP_NAME, 
ce.EMP_NAME, '1' QTY_ETM, 
B.REPAIR_REASON AS REQ_CONTENT
FROM TR_REQUEST_SHOP_DOC A 
LEFT JOIN TR_REQUEST_SHOP_DOC_DT B ON A.REQ_ID = B.REQ_DOC_ID
LEFT JOIN CM_DEPARTMENT K ON B.SUBBRANCH_OLD = K.DEP_ID
LEFT JOIN CM_DEPARTMENT TT ON B.FATHER_OLD = TT.DEP_ID
LEFT JOIN CM_DEPARTMENT PB ON B.DEP_OLD = PB.DEP_ID
LEFT JOIN ASS_MASTER am ON am.ASSET_ID = B.ASS_ID
LEFT JOIN CM_EMPLOYEE ce ON B.EMP_ID = ce.EMP_ID 
WHERE A.REQ_ID = @REQ_ID 
  

--TABLE 1 DVCM KHOI CNTT
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00'))
BEGIN
    SELECT TOP 1 A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(D.APPROVE_DT,'dd/MM/yyyy HH:mm:ss') AS POS_NAME 
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    LEFT JOIN PL_PROCESS D ON A.REQ_ID = D.REQ_ID AND D.PROCESS_ID = 'S_GDDVDVCM' AND A.MAKER_ID = D.CHECKER_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00'

    SET @SHOW_TABLE_DVCM_IT = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END
 
--TABLE 2 DVCM KHAC
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00'))
BEGIN
    SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, 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 = A.REQ_ID AND PP.PROCESS_ID = 'S_GDDVDVCM'
                                                                  AND PP.CHECKER_ID = A.MAKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00'
    ORDER BY A.COST_ID


    SET @SHOW_TABLE_DVCM_KHAC = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END

--TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS 
SELECT TOP 1 A.HO_NOTES, D.TLFullName, 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 = A.REQ_ID AND (PP.PROCESS_ID = 'S_DVCM' OR PP.PROCESS_ID = 'S_TBP_QLTS')
                                                                  AND PP.CHECKER_ID = PL.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
FROM TR_REQUEST_SHOP_DOC A 
LEFT JOIN PL_REQUEST_PROCESS PL ON A.REQ_ID = PL.REQ_ID AND PL.PROCESS_ID = 'QLTS_NL'
LEFT JOIN CM_EMPLOYEE_LOG C ON PL.CHECKER_ID = C.USER_DOMAIN
LEFT JOIN TL_USER D ON C.USER_DOMAIN = D.TLNANME
--LEFT JOIN CM_EMPLOYEE D ON C.EMP_CODE = D.EMP_CODE
WHERE A.REQ_ID = @REQ_ID 

--table 4 phe duyet dvcm khoi cntt
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'))
    BEGIN  
    	    SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'
          ORDER BY C.DEP_ID
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES        
    END
END
ELSE
BEGIN
SELECT N'' NOTES
END

--table 5 PHÊ DUYỆT PHÒNG QLTS
IF(EXISTS(SELECT 1
        FROM  PL_REQUEST_PROCESS A
        LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
        LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME
        --LEFT JOIN CM_EMPLOYEE C ON B.EMP_CODE = C.EMP_CODE
        WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'QLTS_D'))
BEGIN
    SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, C.TLFullName, 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 = A.REQ_ID AND PP.PROCESS_ID = 'QLTS_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME 
    FROM  PL_REQUEST_PROCESS A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME
    --LEFT JOIN CM_EMPLOYEE C ON B.EMP_CODE = C.EMP_CODE
    WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'QLTS_D'
    ORDER BY A.ID DESC
END
ELSE
BEGIN
    SELECT N'' NOTES
END

--Phucvh table 6 USER TẠO DUYỆT
SELECT TOP 1 C.TLFullName AS USER_CREATE, CHAR(13) + ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 
                                                                  WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'S_DVKD'
                                                                  AND PP1.CHECKER_ID = A.MAKER_ID
                                                                  ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS  POS_NAME, 
E.TLFullName AS USER_APPROVE, CHAR(13) + ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 
                                                                  WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'DVKD_D'                                                                  
                                                                  ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss')  AS POS_NAME_1
FROM TR_REQUEST_SHOP_DOC A
LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN --NGƯỜI TẠO
LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --NGƯỜI TẠO
LEFT JOIN PL_REQUEST_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND PP.PROCESS_ID = 'APPNEW'
LEFT JOIN CM_EMPLOYEE_LOG D ON PP.CHECKER_ID = D.USER_DOMAIN --NGƯỜI DUYỆT
LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME --NGƯỜI DUYỆT
WHERE A.REQ_ID = @REQ_ID

--PHUCVH TABLE 7 SỐ PYC
SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID

--PHUCVH TABLE 8 NGÀY TDV DUYỆT
SELECT TOP 1 N'Ngày ' +  CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + 
            N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + 
            N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT
FROM PL_REQUEST_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW' ORDER BY pp.ID DESC

END

--TABLE 9 PHÊ DUYỆT DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN  
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'))
    BEGIN  
    	    SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'
          ORDER BY C.DEP_ID
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES
        FROM TR_REQUEST_SHOP_COSTCENTER A
        LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID
        WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00'
    END   
END
ELSE
BEGIN
    SELECT N'' NOTES
END

--TABLE 10 PHÊ DUYỆT TBP_QLTS
SELECT TOP 1 ISNULL(A.NOTES,'') AS HO_NOTES, C.TLFullName, 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 = A.REQ_ID AND PP.PROCESS_ID = 'TBP_D'
                                                              AND PP.CHECKER_ID = A.CHECKER_ID
                                                              ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME 
FROM  PL_REQUEST_PROCESS A
LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME
--LEFT JOIN CM_EMPLOYEE C ON B.EMP_CODE = C.EMP_CODE
WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'TBP_D'
ORDER BY A.ID DESC

--SHOW TABLE DVCM IT
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    SELECT N'DVCMIT' AS MERGE_REGION
    SET @SHOW_TABLE_DVCM_IT = '1'
END

--SHOW TABLE DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'DVCMKHAC' AS MERGE_REGION
END

--SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'TITLEDVCM' AS MERGE_REGION
END



GO


ALTER PROC dbo.rpt_PYC_THUHOI
@REQ_ID VARCHAR(15) = NULL

AS
BEGIN

DECLARE 
@SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL

--TABLE 0
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, 
am.ASSET_NAME AS ASS_NAME, 
ISNULL(am.ASSET_CODE,am.ASS_CODE_TMP) AS ASSET_CODE,
ISNULL(PB.DEP_NAME,ISNULL(TT.DEP_NAME,K.DEP_NAME))  AS DEP_NAME, 
ce.EMP_NAME, '1' QTY_ETM, 
CA.CONTENT AS REQ_CONTENT
FROM TR_REQUEST_SHOP_DOC A 
LEFT JOIN TR_REQUEST_DOC_ASSET_DT B ON A.REQ_ID = B.REQ_DOC_ID
LEFT JOIN CM_ALLCODE CA ON B.REQ_ASSET_REASON = CA.CDVAL AND CA.CDNAME = 'REASON_ASS_REQ' AND CA.CDTYPE = 'REQ_ASSET'
LEFT JOIN CM_DEPARTMENT K ON B.SUBBRANCH_OLD = K.DEP_ID
LEFT JOIN CM_DEPARTMENT TT ON B.FATHER_OLD = TT.DEP_ID
LEFT JOIN CM_DEPARTMENT PB ON B.DEP_OLD = PB.DEP_ID
LEFT JOIN ASS_MASTER am ON am.ASSET_ID = B.ASSET_ID
LEFT JOIN CM_EMPLOYEE ce ON B.EMP_OLD = ce.EMP_ID 
WHERE A.REQ_ID = @REQ_ID 
  
--TABLE 1 DVCM KHOI CNTT
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00'))
BEGIN
    SELECT DISTINCT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(D.APPROVE_DT,'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    LEFT JOIN PL_PROCESS D ON A.REQ_ID = D.REQ_ID AND D.PROCESS_ID = 'S_GDDVDVCM' AND A.MAKER_ID = D.CHECKER_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00'

    SET @SHOW_TABLE_DVCM_IT = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END
 
--TABLE 2 DVCM KHAC
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00'))
BEGIN
    SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, 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 = A.REQ_ID AND PP.PROCESS_ID = 'S_GDDVDVCM'
                                                                  AND PP.CHECKER_ID = A.MAKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00'
    ORDER BY A.COST_ID

    SET @SHOW_TABLE_DVCM_KHAC = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END

--TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS 
SELECT TOP 1 A.HO_NOTES, D.TLFullName, 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 = A.REQ_ID AND (PP.PROCESS_ID = 'S_DVCM' OR PP.PROCESS_ID = 'S_TBP_QLTS')
                                                                  AND PP.CHECKER_ID = PL.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
FROM TR_REQUEST_SHOP_DOC A 
LEFT JOIN PL_REQUEST_PROCESS PL ON A.REQ_ID = PL.REQ_ID AND PL.PROCESS_ID = 'QLTS_N'
LEFT JOIN CM_EMPLOYEE_LOG C ON PL.CHECKER_ID = C.USER_DOMAIN
LEFT JOIN TL_USER D ON C.USER_DOMAIN = D.TLNANME
WHERE A.REQ_ID = @REQ_ID 

--TABLE 4 PHE DUYET DVCM KHOI CNTT
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'))
    BEGIN  
    	    SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'
          ORDER BY C.DEP_ID
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES        
    END
END
ELSE
BEGIN
SELECT N'' NOTES
END

--table 5 PHÊ DUYỆT PHÒNG QLTS
IF(EXISTS(SELECT 1
        FROM  PL_REQUEST_PROCESS A
        LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
        LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME        
        WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'QLTS_D'))
BEGIN
    SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, C.TLFullName, 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 = A.REQ_ID AND PP.PROCESS_ID = 'QLTS_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME 
    FROM  PL_REQUEST_PROCESS A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME    
    WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'QLTS_D'
END
ELSE
BEGIN
    SELECT N'' NOTES
END

--Phucvh table 6 USER TẠO DUYỆT
SELECT TOP 1 C.TLFullName AS USER_CREATE, CHAR(13) + ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 
                                                                  WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'S_DVKD'
                                                                  AND PP1.CHECKER_ID = A.MAKER_ID
                                                                  ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS  POS_NAME, 
E.TLFullName AS USER_APPROVE, CHAR(13) + ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 
                                                                  WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'DVKD_D'                                                                  
                                                                  ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss')  AS POS_NAME_1
FROM TR_REQUEST_SHOP_DOC A
LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN --NGƯỜI TẠO
LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --NGƯỜI TẠO
LEFT JOIN PL_REQUEST_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND PP.PROCESS_ID = 'APPNEW'
LEFT JOIN CM_EMPLOYEE_LOG D ON PP.CHECKER_ID = D.USER_DOMAIN --NGƯỜI DUYỆT
LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME --NGƯỜI DUYỆT
WHERE A.REQ_ID = @REQ_ID
ORDER BY PP.ID DESC

--PHUCVH TABLE 7 SỐ PYC
SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID

--PHUCVH TABLE 8 NGÀY TDV DUYỆT
SELECT TOP 1 N'Ngày ' +  CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + 
            N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + 
            N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT
FROM PL_REQUEST_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW' ORDER BY pp.ID DESC
END

--TABLE 9 PHÊ DUYỆT DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN  
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'))
    BEGIN  
    	    SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'
          ORDER BY C.DEP_ID
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES
        FROM TR_REQUEST_SHOP_COSTCENTER A
        LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID
        WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00'
    END   
END
ELSE
BEGIN
    SELECT N'' NOTES
END

--TABLE 10 PHÊ DUYỆT TBP_QLTS
SELECT TOP 1 ISNULL(A.NOTES,'') AS HO_NOTES, C.TLFullName, 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 = A.REQ_ID AND PP.PROCESS_ID = 'TBP_D'
                                                              AND PP.CHECKER_ID = A.CHECKER_ID
                                                              ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME 
FROM  PL_REQUEST_PROCESS A
LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN
LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME    
WHERE A.REQ_ID = @REQ_ID  AND A.PROCESS_ID = 'TBP_D'

--SHOW TABLE DVCM IT
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    SELECT N'DVCMIT' AS MERGE_REGION    
END

--SHOW TABLE DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'DVCMKHAC' AS MERGE_REGION
END

--SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'TITLEDVCM' AS MERGE_REGION
END