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_ReportPriceOver500M]

	@p_TR_REQ_DOC_ID	varchar(15) NULL

AS

	----nhà cung cấp đánh giá KNNLNT------

	SELECT DISTINCT

	A.SUPPLIER_1, A.SUPPLIER_2, A.SUPPLIER_3, A.SUPPLIER_4, A.SUPPLIER_5, A.SUPPLIER_6, A.SUPPLIER_7  

	FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KNNLNT A

	LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

	WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	--------đánh giá KNNLNT---------

	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*

	FROM

	(

		SELECT 

		A.RECORD_DT_ID, A.CONTENT_ASSESSMENT, A.ASSESSMENT_OF_HSMT,A.ASSESSMENT_RESULT_1, 

		A.ASSESSMENT_RESULT_2, A.ASSESSMENT_RESULT_3, A.ASSESSMENT_RESULT_4, A.ASSESSMENT_RESULT_5,

		A.ASSESSMENT_RESULT_6, A.ASSESSMENT_RESULT_7

		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KNNLNT A

		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	) AS RESULT

	----nhà cung cấp đánh giá về kỹ thuật------

	SELECT DISTINCT

	A.SUPPLIER_1, A.SUPPLIER_2, A.SUPPLIER_3, A.SUPPLIER_4, A.SUPPLIER_5, A.SUPPLIER_6, A.SUPPLIER_7

	FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KT A

	LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

	WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	----đánh giá về kỹ thuật-----

	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*

	FROM

	(

		SELECT 

		A.RECORD_DT_ID, A.CONTENT_ASSESSMENT, A.ASSESSMENT_OF_HSMT,A.ASSESSMENT_1, A.ASSESSMENT_2,

		A.ASSESSMENT_3, A.ASSESSMENT_4, A.ASSESSMENT_5,A.ASSESSMENT_6, A.ASSESSMENT_7

		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KT A

		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	) AS RESULT

	--đánh giá về giá----

	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*

	FROM

	(

		SELECT 

		A.RECORD_DT_ID, 

		A.SUPPLIER,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.FIRST_PRICE))

		AS FIRST_PRICE,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.REDUCED_PRICE))

		AS REDUCED_PRICE,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.OFFICIAL_FIRST_PRICE))

		AS OFFICIAL_FIRST_PRICE

		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_DGVG A

		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	) AS RESULT

	---xếp hạng NCC đạt yêu cầu-----

	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RATINGS) AS STT, RESULT.*

	FROM

	(

		SELECT A.RECORD_DT_ID ,A.SUPPLIER, A.RATINGS, A.NOTES

		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_XH A

		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	) AS RESULT

	WHERE RESULT.RATINGS IS NOT NULL AND RESULT.RATINGS <> ''

	ORDER BY RESULT.RATINGS ASC

	------------------------Nội dung điều khoản cần thg thảo---------------

	--SELECT

	--	CASE

	--		WHEN A.NEGOTIATE_TERMS IS NOT NULL AND A.NEGOTIATE_TERMS <> ''

	--			THEN N'6)   Các điều khoản cần thương thảo:'

	--		ELSE NULL

	--	END

	--	AS TERMS_TITLE,

	--	A.NEGOTIATE_TERMS AS TERMS,

	--	CASE

	--		WHEN A.OTHER_PROPOSE IS NOT NULL AND A.OTHER_PROPOSE <> '' AND (A.NEGOTIATE_TERMS IS NOT NULL AND A.NEGOTIATE_TERMS <> '')

	--			THEN N'7)   Các đề xuất khác (nếu có):'

	--		WHEN A.OTHER_PROPOSE IS NOT NULL AND A.OTHER_PROPOSE <> '' AND (A.NEGOTIATE_TERMS IS NULL OR A.NEGOTIATE_TERMS = '')

	--			THEN N'6)   Các đề xuất khác (nếu có):'

	--		ELSE NULL

	--	END

	--	AS PROPOSES_TITLE,

	--	A.OTHER_PROPOSE AS PROPOSES

	--FROM TR_REQ_DOC_XETGIA_TREN_500M A

	--WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	---------------------kết luận-------------------------

	SELECT A.CONCLUSION

	FROM TR_REQ_DOC_XETGIA_TREN_500M A

	WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	-------------------------CHỮ KÝ DMMS--------------------------

	DECLARE @t_SIGN_TABLE TABLE(APPROVE_DT VARCHAR(30), SIGN_NAME NVARCHAR(50), TYPE VARCHAR(15))

	DECLARE @p_APPROVE_DT_KSV VARCHAR(25), @p_KSV_NAME NVARCHAR(50), --KSV

			@p_APPROVE_DT_GDDV VARCHAR(25), @p_GDDV_NAME NVARCHAR(50) --GDDV

	INSERT INTO @t_SIGN_TABLE

		-----------------KSV DMMS----------------

		SELECT TOP 1 ISNULL(FORMAT(D.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT, 

		C.TLFullName AS SIGN_NAME,

		'KSV' AS TYPE

		FROM PL_REQUEST_PROCESS_CHILD A

		LEFT JOIN PL_REQUEST_PROCESS B ON A.PROCESS_ID = B.ID

		LEFT JOIN TL_USER C ON A.TLNAME = C.TLNANME

		LEFT JOIN PL_PROCESS D ON B.REQ_ID = D.REQ_ID AND A.TLNAME = D.CHECKER_ID

		WHERE B.REQ_ID = @p_TR_REQ_DOC_ID

		AND A.TYPE_JOB = 'KS'

		AND A.STATUS_JOB = 'P'

		AND B.PROCESS_ID = 'DMMS'

		ORDER BY D.ID DESC

	INSERT INTO @t_SIGN_TABLE

		---------------Trưởng đơn vị DMMS------------

		SELECT 

		DISTINCT ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,

		B.TLFullName AS SIGN_NAME,

		'GDDV' AS TYPE

		FROM PL_REQUEST_PROCESS A

		LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

		WHERE A.REQ_ID = @p_TR_REQ_DOC_ID

		AND A.PROCESS_ID = 'DMMS'

		AND A.STATUS = 'P'

	------KSV DMMS-----

	SET @p_APPROVE_DT_KSV = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE A WHERE A.TYPE = 'KSV')

	SET @p_KSV_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE A WHERE A.TYPE = 'KSV')

	------GDDV DMMS----

	SET @p_APPROVE_DT_GDDV = (SELECT A.APPROVE_DT FROM @t_SIGN_TABLE A WHERE A.TYPE = 'GDDV')

	SET @p_GDDV_NAME = (SELECT A.SIGN_NAME FROM @t_SIGN_TABLE A WHERE A.TYPE = 'GDDV')

	------------LẤY TÊN NG DUYỆT---------------

	SELECT	ISNULL(@p_KSV_NAME, '') AS KSV_NAME, 

			ISNULL(@p_GDDV_NAME,'') AS DMMS_NAME,

			CASE WHEN ISNULL(@p_APPROVE_DT_KSV, '') <> '' THEN N'KIỂM SOÁT VIÊN'

			ELSE '' END AS TITLE_KSV,

			CASE WHEN ISNULL(@p_APPROVE_DT_GDDV, '') <> '' THEN N'TRƯỞNG PHÒNG HÀNH CHÍNH'

			ELSE '' END AS TITLE_GDDV

	-------------LẤY NGÀY DUYỆT--------------

	SELECT	ISNULL(@p_APPROVE_DT_KSV, '') AS APPROVE_DT_KSV, 

			ISNULL(@p_APPROVE_DT_GDDV, '') AS APPROVE_DT_GDDV

	----------------CHỮ KÝ PHÒNG KẾ TOÁN-------------------

	SELECT DISTINCT B.TLFullName AS KT_NAME,

	ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT_KT,

	CASE WHEN ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') <> '' THEN N'TRƯỞNG PHÒNG KẾ TOÁN'

	ELSE '' END AS TITLE_KT

	FROM PL_REQUEST_PROCESS A

	LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

	WHERE A.REQ_ID = @p_TR_REQ_DOC_ID

	AND A.PROCESS_ID = 'DVCM'

	AND A.DVDM_ID = 'DM0000000000006' --DVCM là phòng KT

	AND A.STATUS = 'P'

	--------------- CHỮ KÝ CẤP CÓ THẨM QUYỀN (CẤP DUYỆT CUỐI)-----------------

	-----------------ĐỂ TẠM CHỨ K CÒN XÀI NỮA, ĐỪNG XÓA---------------------

	SELECT DISTINCT C.POS_NAME AS CHUC_DANH, 

	B.TLFullName AS FINAL_NAME,

	ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT_FINAL

	FROM PL_REQUEST_PROCESS A

	LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

	LEFT JOIN CM_EMPLOYEE_LOG C ON C.USER_DOMAIN = B.TLNANME

	WHERE A.REQ_ID = @p_TR_REQ_DOC_ID

	AND A.PROCESS_ID = 

		(

			SELECT TOP 1 PARENT_PROCESS_ID 

			FROM PL_REQUEST_PROCESS B 

			WHERE B.PROCESS_ID = 'APPROVE' 

			AND B.REQ_ID = @p_TR_REQ_DOC_ID

		)

	AND A.STATUS = 'P'

	------------------LẤY ĐẠI DIỆN ĐMMS--------------------

	DECLARE @t_MEMBER_DMMS_TABLE TABLE 

		(STT INT, MEMBER_DMMS_NAME NVARCHAR(100), POSITION_DMMS NVARCHAR(100))

	INSERT INTO @t_MEMBER_DMMS_TABLE

		-------------THÀNH VIÊN 1-------------------

		SELECT 1 AS STT,

			N'Ông/ Bà: ' + TU_1.TLFullName AS MEMBER_DMMS_NAME,

			N'Chức vụ: ' + A.ROLE_DMMS_1 AS POSITION_DMMS

			FROM TR_REQ_DOC_XETGIA_TREN_500M A

			LEFT JOIN TL_USER TU_1 ON A.MEMBER_DMMS_1 = TU_1.TLNANME

			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

		UNION

		-------------THÀNH VIÊN 2-------------------

		SELECT 2 AS STT,

			N'Ông/ Bà: ' + TU_2.TLFullName AS MEMBER_DMMS_NAME,

			N'Chức vụ: ' + A.ROLE_DMMS_2 AS POSITION_DMMS

			FROM TR_REQ_DOC_XETGIA_TREN_500M A

			LEFT JOIN TL_USER TU_2 ON A.MEMBER_DMMS_2 = TU_2.TLNANME

			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

		UNION

		-------------THÀNH VIÊN 3-------------------

		SELECT 2 AS STT,

			N'Ông/ Bà: ' + TU_3.TLFullName AS MEMBER_DMMS_NAME,

			N'Chức vụ: ' + A.ROLE_DMMS_3 AS POSITION_DMMS

			FROM TR_REQ_DOC_XETGIA_TREN_500M A

			LEFT JOIN TL_USER TU_3 ON A.MEMBER_DMMS_3 = TU_3.TLNANME

			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	SELECT A.MEMBER_DMMS_NAME, A.POSITION_DMMS FROM @t_MEMBER_DMMS_TABLE A

	WHERE A.MEMBER_DMMS_NAME IS NOT NULL AND A.POSITION_DMMS IS NOT NULL

	ORDER BY A.STT

		------------------LẤY ĐẠI DIỆN KẾ TOÁN--------------------

	DECLARE @t_MEMBER_KT_TABLE TABLE 

		(STT INT, MEMBER_KT_NAME NVARCHAR(100), POSITION_KT NVARCHAR(100))

	INSERT INTO @t_MEMBER_KT_TABLE

			-------------THÀNH VIÊN 1-------------------

		SELECT 1 AS STT,

			N'Ông/ Bà: ' + TU_1.TLFullName AS MEMBER_KT_NAME,

			N'Chức vụ: ' + A.ROLE_KT_1 AS POSITION_KT

			FROM TR_REQ_DOC_XETGIA_TREN_500M A

			LEFT JOIN TL_USER TU_1 ON A.MEMBER_KT_1 = TU_1.TLNANME

			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

		UNION

			-------------THÀNH VIÊN 2-------------------

		SELECT 2 AS STT,

			N'Ông/ Bà: ' + TU_2.TLFullName AS MEMBER_KT_NAME,

			N'Chức vụ: ' + A.ROLE_KT_2 AS POSITION_KT

			FROM TR_REQ_DOC_XETGIA_TREN_500M A

			LEFT JOIN TL_USER TU_2 ON A.MEMBER_KT_2 = TU_2.TLNANME

			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

		UNION

			-------------THÀNH VIÊN 3-------------------

		SELECT 3 AS STT,

			N'Ông/ Bà: ' + TU_3.TLFullName AS MEMBER_KT_NAME,

			N'Chức vụ: ' + A.ROLE_KT_3 AS POSITION_KT

			FROM TR_REQ_DOC_XETGIA_TREN_500M A

			LEFT JOIN TL_USER TU_3 ON A.MEMBER_KT_3 = TU_3.TLNANME

			WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	SELECT A.MEMBER_KT_NAME, A.POSITION_KT FROM @t_MEMBER_KT_TABLE A

	WHERE A.MEMBER_KT_NAME IS NOT NULL AND A.POSITION_KT IS NOT NULL

	ORDER BY A.STT

	-------------------------Đàm phán giá----------------------

	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.*

	FROM

	(

		SELECT 

		A.RECORD_DT_ID, 

		A.SUPPLIER,

		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_DP))

		AS TOTAL_AMT

		FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_DPG A

		LEFT JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	) AS RESULT

	-----------------CHỮ KÝ PHÊ DUYỆT(GDK TC + GDK HT)-----------------

	DECLARE 

		@p_ROLE_NAME_GDKTC  NVARCHAR(100), @p_GDKTC_NAME NVARCHAR(50), @p_APPROVE_DT_GDKTC VARCHAR(30),

		@p_ROLE_NAME_GDKHT  NVARCHAR(100), @p_GDKHT_NAME NVARCHAR(50), @p_APPROVE_DT_GDKHT VARCHAR(30)

	DECLARE @t_FINAL_TABLE TABLE(

		ROLE_NAME NVARCHAR(100), SIGN_NAME NVARCHAR(50), 

		APPROVE_DT VARCHAR(30), TYPE VARCHAR(15)

	)

	INSERT INTO @t_FINAL_TABLE

		---------GDK TC-----------------

		SELECT CEL.POS_NAME AS ROLE_NAME,

		TU.TLFullName AS SIGN_NAME,

		ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,

		'GDKTC' AS TYPE

		FROM PL_REQUEST_PROCESS PRP

		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME

		LEFT JOIN CM_EMPLOYEE_LOG CEL ON CEL.USER_DOMAIN = TU.TLNANME

		WHERE PRP.REQ_ID = @p_TR_REQ_DOC_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_FINAL_TABLE

		-----------GDK HT------------------

		SELECT DISTINCT CEL.POS_NAME AS ROLE_NAME, 

		TU.TLFullName AS SIGN_NAME,

		ISNULL(FORMAT(PRP.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,

		'GDKHT' AS TYPE

		FROM PL_REQUEST_PROCESS PRP

		LEFT JOIN TL_USER TU ON PRP.CHECKER_ID = TU.TLNANME

		LEFT JOIN CM_EMPLOYEE_LOG CEL ON CEL.USER_DOMAIN = TU.TLNANME

		WHERE PRP.REQ_ID = @p_TR_REQ_DOC_ID

		AND PRP.PROCESS_ID = 

			(

				SELECT TOP 1 PARENT_PROCESS_ID 

				FROM PL_REQUEST_PROCESS B 

				WHERE B.PROCESS_ID = 'APPROVE' 

				AND B.REQ_ID = @p_TR_REQ_DOC_ID

			)

		AND PRP.STATUS = 'P'

	--------GDK TC--------------

	SET @p_ROLE_NAME_GDKTC = (SELECT ROLE_NAME FROM @t_FINAL_TABLE A WHERE A.TYPE = 'GDKTC')

	SET @p_GDKTC_NAME = (SELECT SIGN_NAME FROM @t_FINAL_TABLE A WHERE A.TYPE = 'GDKTC')

	SET @p_APPROVE_DT_GDKTC = (SELECT APPROVE_DT FROM @t_FINAL_TABLE A WHERE A.TYPE = 'GDKTC')

	---------GDKHT--------------

	SET @p_ROLE_NAME_GDKHT = (SELECT ROLE_NAME FROM @t_FINAL_TABLE A WHERE A.TYPE = 'GDKHT')

	SET @p_GDKHT_NAME = (SELECT SIGN_NAME FROM @t_FINAL_TABLE A WHERE A.TYPE = 'GDKHT')

	SET @p_APPROVE_DT_GDKHT = (SELECT APPROVE_DT FROM @t_FINAL_TABLE A WHERE A.TYPE = 'GDKHT')

	------------------LẤY CHỨC DANH------------------

	SELECT	ISNULL(@p_ROLE_NAME_GDKTC, '') AS ROLE_NAME_GDKTC, 

			ISNULL(@p_ROLE_NAME_GDKHT,'') AS ROLE_NAME_GDKHT

	-----------------LẤY TÊN-------------------

	SELECT	ISNULL(@p_GDKTC_NAME, '') AS GDKTC_NAME, 

			ISNULL(@p_GDKHT_NAME,'') AS GDKHT_NAME

	-----------------LẤY NGÀY---------------------

	SELECT	ISNULL(@p_APPROVE_DT_GDKTC, '') AS APPROVE_DT_GDKTC, 

			ISNULL(@p_APPROVE_DT_GDKHT,'') AS APPROVE_DT_GDKHT,

			CASE WHEN ISNULL(@p_APPROVE_DT_GDKTC, '') <> '' THEN N'PHÊ DUYỆT'

				WHEN ISNULL(@p_APPROVE_DT_GDKHT, '') <> '' THEN N'PHÊ DUYỆT'

				ELSE '' END AS TITLE_APPROVE

	---------------------Điều khoản thương thảo------------

	DECLARE @p_TERMS NVARCHAR(1000) = (

		SELECT A.NEGOTIATE_TERMS

		FROM TR_REQ_DOC_XETGIA_TREN_500M A

		WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	)

	IF(@p_TERMS IS NOT NULL AND @p_TERMS <> '')

	BEGIN

		SELECT

		N'TERMS' AS MERGE_REGION,

		A.NEGOTIATE_TERMS AS TERMS

		FROM TR_REQ_DOC_XETGIA_TREN_500M A

		WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	END

	-------------------Đề xuất khác(nếu có)------------------

	DECLARE @p_PROPOSES NVARCHAR(1000) = (

		SELECT A.OTHER_PROPOSE

		FROM TR_REQ_DOC_XETGIA_TREN_500M A

		WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	)

	IF(@p_PROPOSES IS NOT NULL AND @p_PROPOSES <> '')

	BEGIN

		SELECT

		N'PROPOSES' AS MERGE_REGION,

		A.OTHER_PROPOSE AS PROPOSES

		FROM TR_REQ_DOC_XETGIA_TREN_500M A

		WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	END

	-------------------Bảng đánh giá NLNT----------------------

	DECLARE @p_NLNT_COUNT INT 

	SET @p_NLNT_COUNT = (

		SELECT COUNT(*) FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KNNLNT A

		INNER JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	)

	IF(@p_NLNT_COUNT > 0)

	BEGIN

		SELECT

			N'NLNT' AS MERGE_REGION

	END

	-------------------Bảng đánh giá KT----------------------

	DECLARE @p_KT_COUNT INT 

	SET @p_KT_COUNT = (

		SELECT COUNT(*) FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_KT A

		INNER JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	)

	IF(@p_KT_COUNT > 0)

	BEGIN

		SELECT

		N'DGKT' AS MERGE_REGION

	END

	-------------------Bảng DGVG----------------------

	DECLARE @p_DGVG_COUNT INT 

	SET @p_DGVG_COUNT = (

		SELECT COUNT(*) FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_DGVG A

		INNER JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	)

	IF(@p_DGVG_COUNT > 0)

	BEGIN

		SELECT N'DGVG' AS MERGE_REGION, A.CURRENCY AS CURRENCY

		FROM TR_REQ_DOC_XETGIA_TREN_500M A

		WHERE A.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	END

	-------------------Bảng DPG----------------------

	DECLARE @p_DPG_COUNT INT 

	SET @p_DPG_COUNT = (

		SELECT COUNT(*) FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_DPG A

		INNER JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	)

	IF(@p_DPG_COUNT > 0)

	BEGIN

		SELECT

		N'DPG' AS MERGE_REGION

	END

	-------------------Bảng XHNCC----------------------

	DECLARE @p_XHNCC_COUNT INT 

	SET @p_XHNCC_COUNT = (

		SELECT COUNT(*) FROM TR_REQ_DOC_XETGIA_TREN_500M_DT_XH A

		INNER JOIN TR_REQ_DOC_XETGIA_TREN_500M B ON A.RECORD_MASTER_ID = B.RECORD_ID

		WHERE B.REQ_DOC_ID = @p_TR_REQ_DOC_ID

	)

	IF(@p_XHNCC_COUNT > 0)

	BEGIN

		SELECT

		N'XHNCC' AS MERGE_REGION

	END