ALTER   PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_ReportPrice10To100M]  
@p_REQ_ID varchar(15)  
AS  
 
	----Nhà cung cấp-------
	SELECT DISTINCT A.SUPPLIER_1, A.SUPPLIER_2, A.SUPPLIER_3, A.SUPPLIER_4, A.SUPPLIER_5
	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-------Thông tin chi tiết xét giá-----------
	SELECT ROW_NUMBER() OVER (ORDER BY RESULT.RECORD_DT_ID) AS STT, RESULT.* FROM  
	(   
		SELECT 
		A.RECORD_DT_ID,
		A.GOOD_NAME,
		A.TECH_SPECIFICATION,
		A.UNIT,
		A.QUANTITY,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_1)) 
		AS UNIT_PRICE_1,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_1)) 
		AS TOTAL_AMT_1,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_2)) 
		AS UNIT_PRICE_2,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_2)) 
		AS TOTAL_AMT_2,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_3)) 
		AS UNIT_PRICE_3,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_3)) 
		AS TOTAL_AMT_3,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_4)) 
		AS UNIT_PRICE_4,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_4)) 
		AS TOTAL_AMT_4,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.UNIT_PRICE_5)) 
		AS UNIT_PRICE_5,
		(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (A.TOTAL_AMT_5)) 
		AS TOTAL_AMT_5

		FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
		LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
		WHERE B.REQ_DOC_ID = @p_REQ_ID
	) 
	AS RESULT
	
	-------Thành tiền trước thuế-----------
	SELECT
	--CAST(ISNULL(SUM(A.TOTAL_AMT_1),0) AS DECIMAL(18,2)) 
	--AS SUM_TOTAL_AMT_1,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_1),0))) 
	AS SUM_TOTAL_AMT_1,
	--CAST(ISNULL(SUM(A.TOTAL_AMT_2),0) AS DECIMAL(18,2)) 
	--AS SUM_TOTAL_AMT_2,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_2),0))) 
	AS SUM_TOTAL_AMT_2,
	--CAST(ISNULL(SUM(A.TOTAL_AMT_3),0) AS DECIMAL(18,2)) 
	--AS SUM_TOTAL_AMT_3,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_3),0))) 
	AS SUM_TOTAL_AMT_3,
	--CAST(ISNULL(SUM(A.TOTAL_AMT_4),0) AS DECIMAL(18,2)) 
	--AS SUM_TOTAL_AMT_4,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_4),0))) 
	AS SUM_TOTAL_AMT_4,
	--CAST(ISNULL(SUM(A.TOTAL_AMT_5),0) AS DECIMAL(18,2)) 
	--AS SUM_TOTAL_AMT_5
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_5),0))) 
	AS SUM_TOTAL_AMT_5

	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-------số tiền thuế----------
	SELECT
	--CAST(ISNULL(SUM(A.VAT_AMT_1),0) AS DECIMAL(18,2)) 
	--AS VAT_TOTAL_AMT_1,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_1 * A.QUANTITY),0))) 
	AS VAT_TOTAL_AMT_1,
	--CAST(ISNULL(SUM(A.VAT_AMT_2),0) AS DECIMAL(18,2)) 
	--AS VAT_TOTAL_AMT_2,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_2 * A.QUANTITY),0))) 
	AS VAT_TOTAL_AMT_2,
	--CAST(ISNULL(SUM(A.VAT_AMT_3),0) AS DECIMAL(18,2)) 
	--AS VAT_TOTAL_AMT_3,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_3 * A.QUANTITY),0))) 
	AS VAT_TOTAL_AMT_3,
	--CAST(ISNULL(SUM(A.VAT_AMT_4),0) AS DECIMAL(18,2)) 
	--AS VAT_TOTAL_AMT_4,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_4 * A.QUANTITY),0))) 
	AS VAT_TOTAL_AMT_4,
	--CAST(ISNULL(SUM(A.VAT_AMT_5),0) AS DECIMAL(18,2)) 
	--AS VAT_TOTAL_AMT_5
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.VAT_AMT_5 * A.QUANTITY),0))) 
	AS VAT_TOTAL_AMT_5

	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
	LEFT JOIN TR_REQUEST_DOC C ON B.REQ_DOC_ID = C.REQ_ID
	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-------thành tiền sau thuế------------
	SELECT
	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_1), 0) 
	--AS TOTAL_AMT_AFTER_VAT_1,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_1),0))) 
	AS TOTAL_AMT_AFTER_VAT_1,
	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_2), 0) 
	--AS TOTAL_AMT_AFTER_VAT_2,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_2),0))) 
	AS TOTAL_AMT_AFTER_VAT_2,
	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_3), 0)
	--AS TOTAL_AMT_AFTER_VAT_3,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_3),0))) 
	AS TOTAL_AMT_AFTER_VAT_3,
	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_4), 0)
	--AS TOTAL_AMT_AFTER_VAT_4,
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_4),0))) 
	AS TOTAL_AMT_AFTER_VAT_4,
	--ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_5), 0)
	--AS TOTAL_AMT_AFTER_VAT_5
	(SELECT dbo.FN_FORMAT_MONEY_REPORT_PRICE (ISNULL(SUM(A.TOTAL_AMT_AFTER_VAT_5),0))) 
	AS TOTAL_AMT_AFTER_VAT_5

	FROM TR_REQ_DOC_XETGIA_DUOI_100M_DT A
	LEFT JOIN TR_REQ_DOC_XETGIA_DUOI_100M B ON A.RECORD_MASTER_ID = B.RECORD_ID
	LEFT JOIN TR_REQUEST_DOC C ON B.REQ_DOC_ID = C.REQ_ID
	WHERE B.REQ_DOC_ID = @p_REQ_ID

	-----Tên đơn vị DMMS--------
	--SELECT N'Trưởng ĐMMS ' + RESULT.DVDM_NAME + ' ' + RESULT.DVDM_BRANCH AS DMMS_TITLE
	--FROM(
	--	SELECT
	--	CASE
	--		----TH có DMMS---
	--		WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
	--		---TH DMMS là đơn vị tạo---
	--		ELSE BR.BRANCH_NAME
	--	END AS DVDM_NAME,
	--	CASE
	--		---Nếu DMMS nằm trong hội sở
	--		WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN N'Hội sở'
	--		ELSE ''
	--	END AS DVDM_BRANCH
	--	FROM PL_REQUEST_PROCESS PL
	--	LEFT JOIN CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	--	LEFT JOIN CM_BRANCH BR ON PL.DVDM_ID = BR.BRANCH_ID
	--	WHERE PL.REQ_ID = @p_REQ_ID
	--) AS RESULT
	--WHERE RESULT.DVDM_NAME IS NOT NULL
	
	---------------Kết luận + ghi chú-----------------------
	SELECT A.CONCLUSION, A.NOTES, 
	CASE WHEN ISNULL(A.NOTES, '') <> '' THEN 'Ghi chú:'
	ELSE ''
	END
	AS TITLE_NOTES
	FROM TR_REQ_DOC_XETGIA_DUOI_100M A
	LEFT JOIN TR_REQUEST_DOC B ON A.REQ_DOC_ID = B.REQ_ID
	WHERE A.REQ_DOC_ID = @p_REQ_ID

	----------------------CHỮ KÝ-----------------------------
	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_REQ_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_REQ_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 TITLE_KSV,
			CASE WHEN ISNULL(@p_APPROVE_DT_GDDV, '') <> '' THEN N'TRƯỞNG PHÒNG HÀNH CHÍNH'
			ELSE '' END 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

	-------------KSV DMMS-------------------
	--SELECT TOP 1 ISNULL(FORMAT(D.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT, 
	--C.TLFullName AS KSV_NAME
	--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_REQ_ID
	--AND A.TYPE_JOB = 'KS'
	--AND A.STATUS_JOB = 'P'
	--ORDER BY D.ID DESC

	---------Trưởng đơn vị DMMS--------
	--SELECT DISTINCT C.TLFullName AS DMMS_NAME
	--FROM PL_REQUEST_PROCESS A
	--LEFT JOIN PL_REQUEST_PROCESS_CHILD B ON A.ID = B.PROCESS_ID
	--LEFT JOIN TL_USER C ON B.TLNAME = C.TLNANME
	--WHERE A.REQ_ID = @p_REQ_ID
	--AND B.TYPE_JOB = 'TP'
	--AND A.PROCESS_ID = 'DMMS'
	--AND B.STATUS_JOB = 'P'
	--SELECT 
	--DISTINCT ISNULL(FORMAT(A.APPROVE_DT, 'dd/MM/yyyy,HH:mm:ss'),'') AS APPROVE_DT,
	--B.TLFullName AS DMMS_NAME
	--FROM PL_REQUEST_PROCESS A
	--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
	--WHERE A.REQ_ID = @p_REQ_ID
	--AND A.PROCESS_ID = 'DMMS'
	--AND A.STATUS = 'P'