ALTER   PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_DT_ById]  
@P_REQ_ID varchar(15)  
AS  
 
DECLARE  
@DETAIL_ID VARCHAR(15),  
@l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,  
@l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,  
@APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),  
@POS NVARCHAR(200),@POS1 NVARCHAR(200),@POS2 NVARCHAR(200),@POS3 NVARCHAR(50),@POS4 NVARCHAR(200),  
@DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50),  
@FULLNAME NVARCHAR(100),@FULLNAME1 NVARCHAR(100),@FULLNAME2 NVARCHAR(100),@FULLNAME3 NVARCHAR(100),@FULLNAME4 NVARCHAR(100)  
 

SET @APPR =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'  
WHERE A.REQ_ID= @P_REQ_ID )  
SET @FULLNAME = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR)  
SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)  
SET @DATE =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
FROM PL_PROCESS A  
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'  
WHERE A.REQ_ID= @P_REQ_ID  
ORDER BY A.APPROVE_DT DESC  
)  

--SET @APPR1 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.NOTES =N'Nhân viên xử lý gửi phê duyệt'  
--WHERE A.REQ_ID= @P_REQ_ID)  
 
--SET @FULLNAME1 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR1)  
--SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)  
--SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
--FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME 
--AND A.NOTES =N'Nhân viên xử lý gửi phê duyệt'  
--WHERE A.REQ_ID= @P_REQ_ID  
--ORDER BY A.APPROVE_DT DESC)  
 
--------------BAODNQ 4/3/2022 : Lấy nhân viên xử lý gửi phê duyệt---------
SET @APPR1 = (
	SELECT DISTINCT B.TLNAME FROM PL_REQUEST_PROCESS A
	INNER JOIN PL_REQUEST_PROCESS_CHILD B ON A.ID = B.PROCESS_ID
	WHERE A.REQ_ID = @P_REQ_ID
	AND A.PROCESS_ID = 'DMMS'
	AND B.STATUS_JOB = 'P'
	AND B.TYPE_JOB = 'XL'
)
SET @FULLNAME1 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @APPR1)
SET @POS1 = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @APPR1)
SET @DATE1 = (
	SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
	FROM PL_PROCESS A
	WHERE A.REQ_ID = @P_REQ_ID
	AND A.PROCESS_ID = 'SEND'
	AND A.CHECKER_ID = @APPR1
	ORDER BY A.APPROVE_DT DESC 
)
----------------------------

--SET @APPR2 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' 
--AND A.NOTES =N'Kiểm soát viên đã phê duyệt'  
--WHERE A.REQ_ID= @P_REQ_ID)  
--SET @FULLNAME2 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR2)  
--SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)  
--SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
--FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' 
--AND A.NOTES = N'Kiểm soát viên đã phê duyệt'  
--WHERE A.REQ_ID= @P_REQ_ID  
--ORDER BY A.APPROVE_DT DESC)  
 
---------------BAODNQ 4/3/2022 Lấy kiểm soát viên phê duyệt----------
SET @APPR2 = (
	SELECT DISTINCT B.TLNAME FROM PL_REQUEST_PROCESS A
	INNER JOIN PL_REQUEST_PROCESS_CHILD B ON A.ID = B.PROCESS_ID
	WHERE A.REQ_ID = @P_REQ_ID
	AND A.PROCESS_ID = 'DMMS'
	AND B.STATUS_JOB = 'P'
	AND B.TYPE_JOB = 'KS'
)
SET @FULLNAME2 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @APPR2)
SET @POS2 = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @APPR2)
SET @DATE2 = (
	SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
	FROM PL_PROCESS A
	WHERE A.REQ_ID = @P_REQ_ID
	AND A.PROCESS_ID = 'DMMS'
	AND A.CHECKER_ID = @APPR2
	ORDER BY A.APPROVE_DT DESC 
)
----------------------------------

--SET @APPR3 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' 
--AND A.NOTES = N'Trưởng đơn vị đã phê duyệt'  
--WHERE A.REQ_ID= @P_REQ_ID)  
 
--SET @FULLNAME3 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR3)  
--SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)  
--SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
--FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.NOTES = N'Trưởng đơn vị đã phê duyệt' 
--AND A.PROCESS_ID ='DMMS'  
--WHERE A.REQ_ID= @P_REQ_ID  
--ORDER BY A.APPROVE_DT DESC)  

-----------------------BAODQN 4/3/2022 Lấy trưởng đơn vị duyệt DMMS------
SET @APPR3 = (
	SELECT DISTINCT B.TLNAME FROM PL_REQUEST_PROCESS A
	INNER JOIN PL_REQUEST_PROCESS_CHILD B ON A.ID = B.PROCESS_ID
	WHERE A.REQ_ID = @P_REQ_ID
	AND A.PROCESS_ID = 'DMMS'
	AND B.STATUS_JOB = 'P'
	AND B.TYPE_JOB NOT IN ('XL', 'KS')
)
SET @FULLNAME3 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR3)  
SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)
SET @DATE3 = (
	SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
	FROM PL_PROCESS A
	WHERE A.REQ_ID = @P_REQ_ID
	AND A.PROCESS_ID = 'DMMS'
	AND A.CHECKER_ID = @APPR3
	ORDER BY A.APPROVE_DT DESC 
)
------------------------
  
--SET @APPR4 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='GDK_PYC' 
--AND A.NOTES = N'Giám đốc khối đã phê duyệt'  
--WHERE A.REQ_ID= @P_REQ_ID)  
--SET @FULLNAME4 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR4)  
--SET @POS4 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)  
--SET @DATE4 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
--FROM PL_PROCESS A  
--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME 
--AND A.NOTES = N'Giám đốc khối đã phê duyệt' 
--AND A.PROCESS_ID ='GDK_PYC'  
--WHERE A.REQ_ID= @P_REQ_ID  
--ORDER BY A.APPROVE_DT DESC)  

-----------BAODNQ 4/3/2022 Lấy GDK_PYC phê duyệt-----------
SET @APPR4 =(
	SELECT DISTINCT CHECKER_ID FROM PL_REQUEST_PROCESS
	WHERE REQ_ID = @P_REQ_ID
	AND PROCESS_ID = 'GDK_PYC'
)  
SET @FULLNAME4 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR4)  
SET @POS4 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)  
SET @DATE4 =(
	SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
	FROM PL_PROCESS A
	WHERE A.REQ_ID = @P_REQ_ID
	AND A.PROCESS_ID = 'GDK_PYC'
	AND A.CHECKER_ID = @APPR4
	ORDER BY A.APPROVE_DT DESC 
)  
---------------------------


SET @DETAIL_ID = (SELECT TOP 1 REQDT_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID)  
SELECT ROW_NUMBER() OVER (ORDER BY KQ.HH_NAME DESC) AS STT, KQ.* FROM  
(   
	SELECT HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,
	--CASE 
	--	WHEN TRDT.UNIT_NAME IS NOT NULL AND TRDT.UNIT_NAME <> '' AND LEN(TRDT.UNIT_NAME) <10 
	--		THEN TRDT.UNIT_NAME 
	--	ELSE DT.UNIT_NAME 
	--END AS UNIT_NAME,
	------------BAODNQ 25/10/2022 : LẤY UNIT_NAME TRONG TR_REQUEST_DOC_DT
	TRDT.UNIT_NAME AS UNIT_NAME,
	TRDT.QUANTITY,TRDT.CURRENCY,  
	ISNULL(TRDT.PRICE_ETM,0) AS PRICE_ETM,ISNULL(TRDT.TOTAL_AMT_ETM,0) AS TOTAL_AMT_ETM,ISNULL(TRDT.PRICE,0) AS PRICE,ISNULL(TRDT.TOTAL_AMT,0) AS TOTAL_AMT,  
	CTT.TRN_TYPE_NAME,CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,  
	TRDT.EXCHANGE_RATE AS RATE
	FROM dbo.TR_REQUEST_DOC_DT TRDT  
	LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID  
	LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID  
	LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID  
	LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID  
	LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID
	------------BAODNQ 19/5/2022: Lấy thêm cột hình thức mua sắm------
	LEFT JOIN CM_TRAN_TYPE CTT ON TRDT.TRAN_TYPE_ID = CTT.TRN_TYPE
	WHERE REQ_DOC_ID=@P_REQ_ID
)  
AS KQ  


SELECT TOP 1 HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,
--CASE 
--	WHEN TRDT.UNIT_NAME IS NOT NULL AND TRDT.UNIT_NAME <> '' AND LEN(TRDT.UNIT_NAME) <10 
--		THEN TRDT.UNIT_NAME 
--	ELSE DT.UNIT_NAME 
--END AS UNIT_NAME,
------------BAODNQ 25/10/2022 : LẤY UNIT_NAME TRONG TR_REQUEST_DOC_DT
TRDT.UNIT_NAME AS UNIT_NAME,
TRDT.QUANTITY,TRDT.CURRENCY,  
ISNULL(TRDT.PRICE_ETM,0) AS PRICE_ETM,ISNULL(TRDT.TOTAL_AMT_ETM,0) AS TOTAL_AMT_ETM,ISNULL(TRDT.PRICE,0) AS PRICE,ISNULL(TRDT.TOTAL_AMT,0) AS TOTAL_AMT,  
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,
------PHÒNG BAN ĐỀ XUẤT----------
@FULLNAME AS DEP_NAME1,  
@POS AS TP_NAME1,
@DATE AS TP_DATE_SIGN1,  
 ------------PHÒNG HÀNH CHÍNH (Nhân viên xử lý gửi phê duyệt)-----
@FULLNAME1 AS DEP_NAME2,  
--@POS1 AS TP_NAME2,
CASE
	WHEN @FULLNAME1 IS NULL OR @FULLNAME1 = '' THEN ''
	ELSE N'NHÂN VIÊN XỬ LÝ' 
END
AS TP_NAME2,
@DATE1 AS TP_DATE_SIGN2,  
  ------------PHÒNG HÀNH CHÍNH (Kiểm soát viên phê duyệt)-----
@FULLNAME2 AS DEP_NAME3,  
--@POS2 AS TP_NAME3,
CASE
	WHEN @FULLNAME2 IS NULL OR @FULLNAME2 = '' THEN ''
	ELSE N'NHÂN VIÊN KIỂM SOÁT'
END
AS TP_NAME3,
@DATE2 AS TP_DATE_SIGN3,  
  ------------PHÒNG HÀNH CHÍNH  (Trưởng đơn vị DMMS phê duyệt)-----
@FULLNAME3 AS DEP_NAME4,  
@POS3 AS TP_NAME4,  
@DATE3 AS TP_DATE_SIGN4,  
  ------------PHÊ DUYỆT-----
@FULLNAME4 AS DEP_NAME12,  
@POS4 AS TP_NAME12,  
@DATE4 AS TP_DATE_SIGN12 ,

TRDT.EXCHANGE_RATE AS RATE,
DP.DEP_NAME AS DEP_CREATE,
N'Ghi chú: ' + A.NOTES AS TP_NAME15
FROM dbo.TR_REQUEST_DOC_DT TRDT  
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID  
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID  
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID  
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID  
LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID  
LEFT JOIN TR_REQUEST_DOC A ON TRDT.REQ_DOC_ID = A.REQ_ID
LEFT JOIN CM_DEPARTMENT DP ON A.DEP_CREATE = DP.DEP_ID
WHERE REQ_DOC_ID=@P_REQ_ID

--------BAODNQ : 4/3/2022 Select tổng cộng------------
SELECT	ISNULL(SUM(KQ.QUANTITY),0) AS TOTAL_QUANTITY, 
		ISNULL(SUM(KQ.TOTAL_AMT_ETM),0) AS TOTAL_AMT_ETM, 
		ISNULL(SUM(KQ.TOTAL_AMT),0) AS TOTAL_AMT
FROM
	(SELECT HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,
	--CASE 
	--	WHEN TRDT.UNIT_NAME IS NOT NULL AND TRDT.UNIT_NAME <> '' AND LEN(TRDT.UNIT_NAME) <10 
	--		THEN TRDT.UNIT_NAME 
	--	ELSE DT.UNIT_NAME 
	--END AS UNIT_NAME,
	------------BAODNQ 25/10/2022 : LẤY UNIT_NAME TRONG TR_REQUEST_DOC_DT
	TRDT.UNIT_NAME AS UNIT_NAME,
	TRDT.QUANTITY,TRDT.CURRENCY,  
	ISNULL(TRDT.PRICE_ETM,0) AS PRICE_ETM,ISNULL(TRDT.TOTAL_AMT_ETM,0) AS TOTAL_AMT_ETM,ISNULL(TRDT.PRICE,0) AS PRICE,ISNULL(TRDT.TOTAL_AMT,0) AS TOTAL_AMT,  
	CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,  
	TRDT.EXCHANGE_RATE AS RATE
	FROM dbo.TR_REQUEST_DOC_DT TRDT  
	LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID  
	LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID  
	LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID  
	LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID  
	LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID  
	WHERE REQ_DOC_ID=@P_REQ_ID
	)  
	AS KQ 
	
-------------BAODNQ 3/8/2022 LẤY CHỮ KÝ PHÊ DUYỆT SAU ĐMMS-----------------
DECLARE
	---PTGDK HỖ TRỢ----
	@p_ROLE_PTGD NVARCHAR(100), @p_PTGD_NAME NVARCHAR(100), @p_APPROVE_DT_PTGD NVARCHAR(50),
	---GDK HỖ TRỢ----
	@p_ROLE_GDK NVARCHAR(100), @p_GDK_NAME NVARCHAR(100), @p_APPROVE_DT_GDK NVARCHAR(50),
	---GDK TÀI CHÍNH----
	@p_ROLE_GDKTC NVARCHAR(100), @p_GDKTC_NAME NVARCHAR(100), @p_APPROVE_DT_GDKTC NVARCHAR(50),
	---TP KẾ TOÁN----
	@p_ROLE_KT NVARCHAR(100), @p_KT_NAME NVARCHAR(100), @p_APPROVE_DT_KT NVARCHAR(50),
	---TKTGD----
	@p_ROLE_TKTGD NVARCHAR(100), @p_TKTGD_NAME NVARCHAR(100), @p_APPROVE_DT_TKTGD NVARCHAR(50),
	---TGD----
	@p_ROLE_TGD NVARCHAR(100), @p_TGD_NAME NVARCHAR(100), @p_APPROVE_DT_TGD NVARCHAR(50)

	DECLARE @t_SIGN_TABLE_FINAL TABLE(
		ROLE_NAME NVARCHAR(100), APPROVE_DT NVARCHAR(50),
		SIGN_NAME NVARCHAR(100), TYPE VARCHAR(20)
	)

	INSERT INTO @t_SIGN_TABLE_FINAL
		---PTGDK HỖ TRỢ----
		SELECT
			N'PHÓ TỔNG GIÁM ĐỐC KHỐI HỖ TRỢ' AS ROLE_NAME,
			ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
			TU.TLFullName AS SIGN_NAME,
			'PTGDK' AS TYPE
		FROM PL_REQUEST_PROCESS PRP
		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME
		WHERE PRP.REQ_ID = @P_REQ_ID
		AND PRP.PROCESS_ID = 'PTGDK_CDT'
		AND PRP.DVDM_ID = 'DM0000000000015' ---PHÒNG HỖ TRỢ
		AND PRP.STATUS = 'P'
		
	INSERT INTO @t_SIGN_TABLE_FINAL
		-----GDK hỗ trợ--------
		SELECT
			N'GIÁM ĐỐC KHỐI HỖ TRỢ' AS ROLE_NAME,
			ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
			TU.TLFullName AS SIGN_NAME,
			'GDKHT' AS TYPE
		FROM PL_REQUEST_PROCESS PRP
		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME
		WHERE PRP.REQ_ID = @P_REQ_ID
		AND PRP.PROCESS_ID = 'GDK_PYC'
		AND PRP.DVDM_ID = 'DM0000000000015' ---PHÒNG HỖ TRỢ
		AND PRP.STATUS = 'P'

	INSERT INTO @t_SIGN_TABLE_FINAL
		------GDK tài chính------
		SELECT
			N'GIÁM ĐỐC KHỐI TÀI CHÍNH' AS ROLE_NAME,
			ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
			TU.TLFullName AS SIGN_NAME,
			'GDKTC' AS TYPE
		FROM PL_REQUEST_PROCESS PRP
		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME
		WHERE PRP.REQ_ID = @P_REQ_ID
		AND (PRP.PROCESS_ID = 'GDK_CDT_TC' OR PRP.PROCESS_ID = 'GDK_TC')
		AND PRP.DVDM_ID = 'DM0000000000017'
		AND PRP.STATUS = 'P'

	INSERT INTO @t_SIGN_TABLE_FINAL
		------TP KẾ TOÁN---------
		SELECT 
			N'TRƯỞNG PHÒNG KẾ TOÁN' AS ROLE_NAME,
			ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
			TU.TLFullName AS SIGN_NAME,
			'KT' AS TYPE
		FROM PL_REQUEST_PROCESS PRP
		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME
		WHERE PRP.REQ_ID = @P_REQ_ID
		AND PRP.PROCESS_ID = 'DVCM'
		AND PRP.DVDM_ID = 'DM0000000000006' --DVCM là phòng KT
		AND PRP.STATUS = 'P'

	INSERT INTO @t_SIGN_TABLE_FINAL
		------TKTGD------
		SELECT
			N'THƯ KÝ BAN TỔNG GIÁM ĐỐC' AS ROLE_NAME,
			ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
			TU.TLFullName AS SIGN_NAME,
			'TKTGD' AS TYPE
		FROM PL_REQUEST_PROCESS PRP
		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME
		WHERE PRP.REQ_ID = @P_REQ_ID
		AND PRP.PROCESS_ID = 'TKTGD_CDT'
		AND PRP.DVDM_ID = 'DM0000000000013'
		AND PRP.STATUS = 'P'

	INSERT INTO @t_SIGN_TABLE_FINAL
		------TGD------
		SELECT
			N'TỔNG GIÁM ĐỐC' AS ROLE_NAME,
			ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
			TU.TLFullName AS SIGN_NAME,
			'TGD' AS TYPE
		FROM PL_REQUEST_PROCESS PRP
		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME
		WHERE PRP.REQ_ID = @P_REQ_ID
		AND PRP.PROCESS_ID = 'TGD_CDT'
		AND PRP.DVDM_ID = 'DM0000000000013'
		AND PRP.STATUS = 'P'

	---PTGDK HỖ TRỢ----
	SET @p_ROLE_PTGD = (SELECT A.ROLE_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'PTGDK')
	SET @p_PTGD_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'PTGDK')
	SET @p_APPROVE_DT_PTGD = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'PTGDK')
	---GDK HỖ TRỢ----
	SET @p_ROLE_GDK = (SELECT A.ROLE_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'GDKHT')
	SET @p_GDK_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'GDKHT')
	SET @p_APPROVE_DT_GDK = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'GDKHT')
	---GDK TÀI CHÍNH----
	SET @p_ROLE_GDKTC = (SELECT A.ROLE_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'GDKTC')
	SET @p_GDKTC_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'GDKTC')
	SET @p_APPROVE_DT_GDKTC = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'GDKTC')
	---TP KẾ TOÁN----
	SET @p_ROLE_KT = (SELECT A.ROLE_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'KT')
	SET @p_KT_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'KT')
	SET @p_APPROVE_DT_KT = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'KT')
	---TKTGD----
	SET @p_ROLE_TKTGD = (SELECT A.ROLE_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'TKTGD')
	SET @p_TKTGD_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'TKTGD')
	SET @p_APPROVE_DT_TKTGD = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'TKTGD')
	---TGD----
	SET @p_ROLE_TGD = (SELECT A.ROLE_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'TGD')
	SET @p_TGD_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'TGD')
	SET @p_APPROVE_DT_TGD = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE_FINAL A WHERE A.TYPE = 'TGD')

	--SELECT  
	--	ISNULL(@p_ROLE_PTGD, '') AS ROLE_PTGD,
	--	ISNULL(@p_PTGD_NAME, '') AS PTGD_NAME,
	--	ISNULL(@p_APPROVE_DT_PTGD, '') AS APPROVE_DT_PTGD,

	--	ISNULL(@p_ROLE_GDK, '') AS ROLE_GDK,
	--	ISNULL(@p_GDK_NAME, '') AS GDK_NAME,
	--	ISNULL(@p_APPROVE_DT_GDK, '') AS APPROVE_DT_GDK,

	--	ISNULL(@p_ROLE_GDKTC, '') AS ROLE_GDKTC,
	--	ISNULL(@p_GDKTC_NAME, '') AS GDKTC_NAME,
	--	ISNULL(@p_APPROVE_DT_GDKTC, '') AS APPROVE_DT_GDKTC,

	--	ISNULL(@p_ROLE_KT, '') AS ROLE_KT,
	--	ISNULL(@p_KT_NAME, '') AS KT_NAME,
	--	ISNULL(@p_APPROVE_DT_KT, '') AS APPROVE_DT_KT,

	--	ISNULL(@p_ROLE_TKTGD, '') AS ROLE_TKTGD,
	--	ISNULL(@p_TKTGD_NAME, '') AS TKTGD_NAME,
	--	ISNULL(@p_APPROVE_DT_TKTGD, '') AS APPROVE_DT_TKTGD,

	--	ISNULL(@p_ROLE_TGD, '') AS ROLE_TGD,
	--	ISNULL(@p_TGD_NAME, '') AS TGD_NAME,
	--	ISNULL(@p_APPROVE_DT_TGD, '') AS APPROVE_DT_TGD

	---------------nếu PYCMS theo chỉ định thầu----------------------
	IF(EXISTS (SELECT * FROM PL_APPOINT_CONTRACTOR WHERE TR_REQUEST_DOC_ID = @P_REQ_ID))
	BEGIN
		SELECT
			ISNULL(@p_ROLE_GDKTC, '') AS ROLE_KT_GDKTC,
			ISNULL(@p_GDKTC_NAME, '') AS KT_GDKTC_NAME,
			ISNULL(@p_APPROVE_DT_GDKTC, '') AS APPROVE_DT_KT_GDKTC,

			ISNULL(@p_ROLE_PTGD, '') AS ROLE_GDKTC_PTGD,
			ISNULL(@p_PTGD_NAME, '') AS GDKTC_PTGD_NAME,
			ISNULL(@p_APPROVE_DT_PTGD, '') AS APPROVE_DT_GDKTC_PTGD,

			ISNULL(@p_ROLE_GDK, '') AS ROLE_GDK,
			ISNULL(@p_GDK_NAME, '') AS GDK_NAME,
			ISNULL(@p_APPROVE_DT_GDK, '') AS APPROVE_DT_GDK,

			ISNULL(@p_ROLE_TKTGD, '') AS ROLE_TKTGD,
			ISNULL(@p_TKTGD_NAME, '') AS TKTGD_NAME,
			ISNULL(@p_APPROVE_DT_TKTGD, '') AS APPROVE_DT_TKTGD,

			ISNULL(@p_ROLE_TGD, '') AS ROLE_TGD,
			ISNULL(@p_TGD_NAME, '') AS TGD_NAME,
			ISNULL(@p_APPROVE_DT_TGD, '') AS APPROVE_DT_TGD
	END
	-----------------nếu PYCMS theo quy định------------------
	ELSE
	BEGIN
		SELECT
			ISNULL(@p_ROLE_KT, '') AS ROLE_KT_GDKTC,
			ISNULL(@p_KT_NAME, '') AS KT_GDKTC_NAME,
			ISNULL(@p_APPROVE_DT_KT, '') AS APPROVE_DT_KT_GDKTC,

			ISNULL(@p_ROLE_GDKTC, '') AS ROLE_GDKTC_PTGD,
			ISNULL(@p_GDKTC_NAME, '') AS GDKTC_PTGD_NAME,
			ISNULL(@p_APPROVE_DT_GDKTC, '') AS APPROVE_DT_GDKTC_PTGD,

			ISNULL(@p_ROLE_GDK, '') AS ROLE_GDK,
			ISNULL(@p_GDK_NAME, '') AS GDK_NAME,
			ISNULL(@p_APPROVE_DT_GDK, '') AS APPROVE_DT_GDK
	END