DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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 TOP 1 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 TOP 1 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 TOP 1 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 TOP 1 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 TOP 1

			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 TOP 1 

			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 TOP 1

			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 TOP 1

			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 TOP 1

			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 TOP 1

			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