CREATE 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), USER_DOMAIN VARCHAR(50))
	DECLARE @p_APPROVE_DT_KSV VARCHAR(25), @p_KSV_NAME NVARCHAR(50),@p_USER_DOMAIN_KSV   VARCHAR(50),--KSV
			@p_APPROVE_DT_GDDV VARCHAR(25), @p_GDDV_NAME NVARCHAR(50), --GDDV, 
			@p_USER_DOMAIN VARCHAR(50)

	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, D.CHECKER_ID
		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, A.CHECKER_ID
		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')
	SET @p_USER_DOMAIN_KSV = (SELECT A.USER_DOMAIN 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')
	SET @p_USER_DOMAIN = (SELECT A.USER_DOMAIN 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 (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@p_USER_DOMAIN_KSV)
			ELSE '' END AS TITLE_KSV,
			CASE WHEN ISNULL(@p_APPROVE_DT_GDDV, '') <> '' THEN (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@p_USER_DOMAIN) --- LUCTV 30112023: FIX LẠI TÊN CHỨC DANH CHÍNH XÁC
			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
GO