ALTER PROCEDURE [dbo].[rpt_PL_REQUEST_DOC_DT_ById]
@P_REQ_ID varchar(15)
AS

DECLARE
			 @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
			
			DECLARE @listTRADE TABLE
			(
				GOODS_ID VARCHAR(20),
				TRADE_ID VARCHAR(20),
				PLAN_ID VARCHAR(20),
				AMT_APP DECIMAL(18,2),
				AMT_EXE DECIMAL (18,2),
				AMT_ETM DECIMAL (18,2),
				AMT_TF DECIMAL (18,2),
				AMT_RECEIVE_TF DECIMAL (18,2)
			)

			IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID='APPROVE'))
			BEGIN
				IF(EXISTS(SELECT * FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID))
				BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID
				END
				ELSE
				BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
					SELECT PT.GOODS_ID,PT.TRADE_ID,PT.PLAN_ID,PT.AMT_APP,PT.AMT_EXE,PT.AMT_ETM-PLDT.TOTAL_AMT,PT.AMT_TF-PLFT.TOTAL_AMT,PT.AMT_RECEIVE_TF-PLTT.TOTAL_AMT FROM dbo.PL_TRADEDETAIL PT
					LEFT JOIN 
					(
					SELECT TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
					dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
					GROUP BY TRADE_ID
					) PLDT ON PLDT.TRADE_ID = PT.TRADE_ID
					LEFT JOIN 
					(
					SELECT FR_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
					GROUP BY FR_TRADE_ID
					) PLFT ON PLFT.FR_TRADE_ID = PT.TRADE_ID

					LEFT JOIN 
					(
					SELECT TO_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
					GROUP BY TO_TRADE_ID
					) PLTT ON PLTT.TO_TRADE_ID = PT.TRADE_ID
					WHERE (PT.TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR PT.TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
				END
			END
			ELSE
			BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF FROM dbo.PL_TRADEDETAIL WHERE (TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
			END






			SELECT  ROW_NUMBER() OVER (ORDER BY HH.HH_NAME) AS STT, HH.HH_NAME,PLDT.DESCRIPTION,PLDT.CURRENCY,PLDT.UNIT_NAME,
			FORMAT(ISNULL(PLDT.QUANTITY,0), '#,#') AS QUANTITY,
			FORMAT(ISNULL(PLDT.PRICE,0), '#,#.##') AS PRICE,
			FORMAT(ISNULL(PLDT.QUANTITY,0) * ISNULL(PLDT.PRICE,0), '#,#') AS TOTAL_AMT_NT,
			FORMAT(ISNULL(PLDT.TAXES,0), '#,#') AS TAXES,
			FORMAT(ISNULL(PLDT.EXCHANGE_RATE,1), '#,#') AS EXCHANGE_RATE,
			FORMAT(ISNULL(PLDT.QUANTITY,0) * ISNULL(PLDT.PRICE,0)+ISNULL(PLDT.TAXES,0), '#,#') AS TOTAL_AMT_TAX,
			FORMAT((ISNULL(PLDT.QUANTITY,0) * ISNULL(PLDT.PRICE,0)+ISNULL(PLDT.TAXES,0)) * ISNULL(PLDT.EXCHANGE_RATE,1), '#,#') AS TOTAL_AMT_EXC,
			CASE
				WHEN PLDT.TRADE_TYPE='VCCB' THEN N'Theo quy định của BVB'
				
				ELSE N'Chỉ định'
			END AS HTMS,
			PLDT.NAME AS REASON,
			CASE
				WHEN PLDT.TRADE_TYPE='VCCB' THEN '' ELSE CS.SUP_NAME END SUP_NAME,
			CG.GD_NAME
			FROM 
			dbo.PL_REQUEST_DOC_DT PLDT
			LEFT JOIN dbo.CM_HANGHOA HH ON PLDT.HANGHOA_ID=HH.HH_ID
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID
			LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID
			LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=PLDT.SUP_ID
			WHERE PLDT.REQ_ID=@P_REQ_ID


			DECLARE @APP_DATE DATETIME = (SELECT APPROVE_DT FROM TR_REQUEST_DOC WHERE REQ_ID = @P_REQ_ID)

			SELECT ROW_NUMBER() OVER (ORDER BY ISNULL(CG.GD_NAME,LOG_DT.GOOD_NAME)) AS STT ,ISNULL(CG.GD_NAME,LOG_DT.GOOD_NAME) AS GD_NAME,
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0) AS AMT_APP,
			ISNULL(PT.AMT_ETM,0) AS AMT_EXE,
			--CASE WHEN (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0) -ISNULL(PT.AMT_ETM,0)) < 0 THEN (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-SUM(ISNULL(PLDT.TOTAL_AMT,0))) 
			--	ELSE ISNULL(PT.AMT_ETM,0) END AS AMT_EXE,
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) - ISNULL(PT.AMT_ETM,0) AS AMT_REMAIN,
			--CASE WHEN (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0)) < 0 THEN 0 ELSE (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0)) END AS AMT_REMAIN,
			SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT,
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) -ISNULL(PT.AMT_ETM,0) -SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT_FN,
			--CASE WHEN (ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0)) < 0 THEN 0
			--	ELSE (ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0) -SUM(ISNULL(PLDT.TOTAL_AMT,0))) END AS TOTAL_AMT_FN,
			
			CASE WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName				
				ELSE N'' END AS CHECKER_NAME,			
			'' AS XN_NAME
			FROM
			dbo.PL_REQUEST_DOC_DT PLDT
			LEFT JOIN 
			(
				SELECT A.TRADE_ID, ISNULL(SUM(A.TOTAL_AMT),0) AS AMT_EXE
				FROM PL_REQUEST_DOC_DT A
				INNER JOIN PL_REQUEST_DOC B ON A.REQ_ID = B.REQ_ID AND B.AUTH_STATUS = 'A'
				WHERE A.REQ_ID <> @P_REQ_ID
				AND A.APPROVE_DT <= @APP_DATE
				AND A.TRADE_ID IN (SELECT TRADE_ID FROM PL_REQUEST_DOC_DT WHERE REQ_ID = @P_REQ_ID AND TRADE_ID IS NOT NULL AND TRADE_ID <> '' GROUP BY TRADE_ID)
				GROUP BY A.TRADE_ID
			) AS TR_AMT ON TR_AMT.TRADE_ID = PLDT.TRADE_ID
			LEFT JOIN @listTRADE PT ON PT.TRADE_ID=PLDT.TRADE_ID
			LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PLDT.PLAN_ID
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID
			LEFT JOIN dbo.PL_IMPORT_DT LOG_DT ON LOG_DT.TRADE_ID=PLDT.TRADE_ID
			LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLDT.CHECKER_ID
			LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLDT.REQ_ID
			WHERE PLDT.REQ_ID=@P_REQ_ID
			GROUP BY PT.GOODS_ID,PT.AMT_APP,/*PT.AMT_EXE*/TR_AMT.AMT_EXE,PT.AMT_RECEIVE_TF,PT.AMT_TF,PT.AMT_ETM,CG.GD_NAME,PLDT.REQ_ID,USC.TLFullName,PL.PROCESS_ID,LOG_DT.GOOD_NAME
			


			-- Điều chuyển ngân sách
			SELECT  ROW_NUMBER() OVER (ORDER BY ISNULL(CGF.GD_NAME,LF_DT.GOOD_NAME)) AS STT ,ISNULL(CGF.GD_NAME,LF_DT.GOOD_NAME) AS FR_GD_NAME,
			(ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0)) AS FR_AMT_APP, -- được duyệt (ngân sách trước khi điều chuyển)
			ISNULL(PTF.AMT_ETM,0) AS FR_AMT_EXE,
			ISNULL(PTF.AMT_APP,0)  + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0) -ISNULL(PTF.AMT_ETM,0) AS FR_AMT_REMAIN, -- còn lại (ngân sách trước khi điều chuyển)
			ISNULL(CGT.GD_NAME,LT_DT.GOOD_NAME) AS TO_GD_NAME,
			(ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)) AS TO_AMT_APP,
			ISNULL(PTT.AMT_ETM,0) AS TO_AMT_EXE,
			ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0) AS TO_AMT_REMAIN,
			ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_AMT,
			ISNULL(PTF.AMT_APP, 0) + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0) -  ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_APP_FINAL,
			(ISNULL(PTF.AMT_APP,0)  + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0) -ISNULL(PTF.AMT_ETM,0)) - ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_REMAIN_FINAL,
		    ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0) + ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS TO_AMT_APP_FINAL,
			FORMAT(ISNULL(PLDT.TOTAL_AMT, 0),'#,0') AS TOTAL_AMT_SIGN, -- Số tiền trình sử dụng
			(ISNULL(PTT.AMT_APP,0)+ ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0)) + ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) - ISNULL(PLDT.TOTAL_AMT, 0) AS TOTAL_AMT_SIGN_FN,
			PR.APPROVE_DT,
			US.TLFullName AS TC_NAME,
			CASE
				WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName
				ELSE N''
			END AS CHECKER_NAME	
			FROM dbo.PL_REQUEST_TRANSFER PLTF
			LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID
			LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID
			LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID
			LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID
			LEFT JOIN dbo.PL_IMPORT_DT LF_DT ON LF_DT.TRADE_ID=PLTF.FR_TRADE_ID
			LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=PLTF.TO_TRADE_ID
			LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'
			LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID
			LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID
			LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID
			

			LEFT JOIN
			(
			SELECT GOODS_ID,REQ_ID, SUM(TOTAL_AMT) AS TOTAL_AMT FROM
			dbo.PL_REQUEST_DOC_DT 
			WHERE REQ_ID=@P_REQ_ID 
			GROUP BY GOODS_ID,REQ_ID ) PLDT ON PLDT.REQ_ID = PL.REQ_ID AND PLDT.GOODS_ID = PLTF.TO_GOOD_ID
			WHERE PLTF.REQ_DOC_ID=@P_REQ_ID

			--DECLARE @TABLE TABLE(TLFullName NVARCHAR(100))
			--INSERT INTO @TABLE 
			--SELECT '- ' + TLFullName + ';' AS NAME FROM PL_PROCESS F1
			--LEFT JOIN TL_USER F2 ON F1.CHECKER_ID = F2.TLNANME
			--WHERE (1=1)
			--AND F1.REQ_ID =@P_REQ_ID
			--AND RoleName in ('TPGD','GDDV', 'GDK', 'PTGD', 'TGD', 'HDQT','KTT','KSV')
			
			--UPDATE @TABLE SET TLFullName = REPLACE(TLFullName,';','.')
			--WHERE TLFullName =(SELECT TOP 1 TLFullName AS NAME FROM @TABLE ORDER BY TLFullName DESC)

			--SELECT DISTINCT(TLFullName) AS NAME FROM @TABLE ORDER BY TLFullName

			-- TABLE3
			DECLARE @TABLE TABLE(ID INT, POS_NAME NVARCHAR(100))
			INSERT INTO @TABLE 
			SELECT F1.ID , '- ' + F3.POS_NAME + ';' AS NAME FROM PL_REQUEST_PROCESS F1
			LEFT JOIN TL_USER F2 ON F1.CHECKER_ID = F2.TLNANME
			LEFT JOIN CM_EMPLOYEE_LOG F3 ON F3.USER_DOMAIN = F2.TLNANME
			WHERE (1=1)
			AND F1.REQ_ID = @P_REQ_ID
			AND RoleName in ('HDQT', 'TGD', 'PTGD', 'GDK')
			AND ((F1.STATUS='C' AND F1.PROCESS_ID <>'APPROVE') OR (F1.PROCESS_ID = (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS  T WHERE T.REQ_ID=@P_REQ_ID AND T.PROCESS_ID='APPROVE' AND T.STATUS='C')))
			UPDATE @TABLE SET POS_NAME = REPLACE(POS_NAME,';','.')
			WHERE POS_NAME =(SELECT TOP 1 POS_NAME AS NAME FROM @TABLE ORDER BY POS_NAME DESC)

			SELECT POS_NAME AS NAME FROM @TABLE ORDER BY ID DESC

			SELECT NULL
			--SELECT  ROW_NUMBER() OVER (ORDER BY CGF.GD_NAME) AS STT ,CGF.GD_NAME AS GD_NAME_ADD,
			--ISNULL(PTF.AMT_APP,0) AS FR_AMT_APP_ADD,
			--ISNULL(PTF.AMT_EXE,0) AS FR_AMT_EXE_ADD,
			--ISNULL(PTF.AMT_APP,0)-ISNULL(PTF.AMT_EXE,0) AS FR_AMT_REMAIN_ADD,
			--ISNULL(PTT.AMT_APP,0) AS TO_AMT_APP_ADD,
			--ISNULL(PTT.AMT_EXE,0) AS TO_AMT_EXE_ADD,
			--ISNULL(PTT.AMT_APP,0)-ISNULL(PTT.AMT_EXE,0) AS TO_AMT_REMAIN_ADD,
			--ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_AMT_ADD,
			--PR.APPROVE_DT,
			--US.TLFullName AS TC_NAME,
			--CASE
			--	WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName
				
			--	ELSE N''
			--END AS CHECKER_NAME	
			--FROM dbo.PL_REQUEST_TRANSFER PLTF 
			--LEFT JOIN dbo.PL_TRADEDETAIL PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID
			--LEFT JOIN dbo.PL_TRADEDETAIL PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID
			--LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID
			--LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID
			--LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'
			--LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID
			--LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID
			--LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID
			--WHERE PLTF.REQ_DOC_ID=@P_REQ_ID

						
			-- LAY THONG TIN CAP PHE DUYET
			IF(EXISTS(SELECT * FROM  PL_REQUEST_DOC_DT WHERE REQ_ID =@P_REQ_ID ))
			BEGIN
				DECLARE @TABLE_RETURN_05 TABLE (APPR NVARCHAR(250),APPR1 NVARCHAR(250),APPR2 NVARCHAR(250),APPR3 NVARCHAR(250))
				DECLARE @APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15)
				DECLARE @POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50)
				DECLARE @DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50)

				IF((SELECT COUNT(*) FROM PL_REQUEST_PROCESS WHERE ROLE_USER ='GDK' AND CHECKER_ID IS NOT NULL AND CHECKER_ID <>'' AND REQ_ID =@P_REQ_ID) =1)
				BEGIN
					IF((SELECT COUNT(*) FROM PL_REQUEST_PROCESS WHERE ROLE_USER ='PTGD' AND CHECKER_ID IS NOT NULL AND CHECKER_ID <>'' AND REQ_ID =@P_REQ_ID) =1)
					BEGIN
						SET @APPR =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK' AND A.ROLE_USER ='GDK'
						WHERE A.REQ_ID= @P_REQ_ID )

						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 tt'),'')
						FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK'
						WHERE A.REQ_ID= @P_REQ_ID)
	
						SET @APPR1 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD' AND A.ROLE_USER ='PTGD'
						WHERE A.REQ_ID= @P_REQ_ID)
						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 tt'),'') FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD'
						WHERE A.REQ_ID= @P_REQ_ID)
		
	

						SET @APPR2 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD' AND A.ROLE_USER ='TGD'
						WHERE A.REQ_ID= @P_REQ_ID)
						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 tt'),'') FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD'
						WHERE A.REQ_ID= @P_REQ_ID)
		

						SET @APPR3 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='HDQT' AND A.ROLE_USER ='HDQT'
						WHERE A.REQ_ID= @P_REQ_ID)
						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 tt'),'') FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='HDQT'
						WHERE A.REQ_ID= @P_REQ_ID)	
					END
					ELSE
					BEGIN 
						SET @APPR =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK' AND A.ROLE_USER ='GDK'
						WHERE A.REQ_ID= @P_REQ_ID )

						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 tt'),'')
						FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK'
						WHERE A.REQ_ID= @P_REQ_ID)
	
						SET @APPR1 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD' AND A.ROLE_USER ='PTGD'
						WHERE A.REQ_ID= @P_REQ_ID)
						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 tt'),'') FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD'
						WHERE A.REQ_ID= @P_REQ_ID)
		
	

						SET @APPR2 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD' AND A.ROLE_USER ='PTGD' AND A.CHECKER_ID <>@APPR1
						WHERE A.REQ_ID= @P_REQ_ID)
						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 tt'),'') FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD' AND A.CHECKER_ID <>@APPR1
						WHERE A.REQ_ID= @P_REQ_ID)
		

						SET @APPR3 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD' AND A.ROLE_USER ='TGD'
						WHERE A.REQ_ID= @P_REQ_ID)
						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 tt'),'') FROM PL_REQUEST_PROCESS  A 
						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD'
						WHERE A.REQ_ID= @P_REQ_ID)
					END
						BEGIN
						INSERT INTO @TABLE_RETURN_05 VALUES (
															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR),
															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),
															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME =  @APPR2),
															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3)
															)
						INSERT INTO @TABLE_RETURN_05 VALUES (@POS,@POS1,@POS2,@POS3)
						INSERT INTO @TABLE_RETURN_05 VALUES ((CASE WHEN @DATE IS NOT NULL AND @DATE <>'' THEN N'Đã chấp thuận lúc: '+@DATE ELSE '' END),
						(CASE WHEN @DATE1 IS NOT NULL AND @DATE1 <>'' THEN N'Đã chấp thuận lúc: '+@DATE1 ELSE '' END),
						(CASE WHEN @DATE2 IS NOT NULL AND @DATE2 <>'' THEN N'Đã chấp thuận lúc: '+@DATE2 ELSE '' END),
						(CASE WHEN @DATE3 IS NOT NULL AND @DATE3 <>'' THEN N'Đã chấp thuận lúc: '+@DATE3 ELSE '' END))
						END
				END
				ELSE
				BEGIN
					SET @APPR =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK' AND A.ROLE_USER ='GDK'
					WHERE A.REQ_ID= @P_REQ_ID )

					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 tt'),'')
					FROM PL_REQUEST_PROCESS  A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK'
					WHERE A.REQ_ID= @P_REQ_ID)
					-- VAN CON LA GDK
					SET @APPR1 =(SELECT  TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK' AND A.ROLE_USER ='GDK' AND A.CHECKER_ID <> @APPR
					WHERE A.REQ_ID= @P_REQ_ID)
					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 tt'),'') FROM PL_REQUEST_PROCESS  A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='GDK' AND A.CHECKER_ID <> @APPR
					WHERE A.REQ_ID= @P_REQ_ID)
		
					-- PHO TONG
					--SET @APPR1 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
					--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD' AND A.ROLE_USER ='PTGD'
					--WHERE A.REQ_ID= @P_REQ_ID)
					--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 tt'),'') FROM PL_REQUEST_PROCESS  A 
					--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD'
					--WHERE A.REQ_ID= @P_REQ_ID)
		
					SET @APPR2 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD' AND A.ROLE_USER ='PTGD'
					WHERE A.REQ_ID= @P_REQ_ID)
					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 tt'),'') FROM PL_REQUEST_PROCESS  A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD'
					WHERE A.REQ_ID= @P_REQ_ID)
		

					SET @APPR3 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD' AND A.ROLE_USER ='TGD'
					WHERE A.REQ_ID= @P_REQ_ID)
					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 tt'),'') FROM PL_REQUEST_PROCESS  A 
					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD'
					WHERE A.REQ_ID= @P_REQ_ID)	

					BEGIN
					INSERT INTO @TABLE_RETURN_05 VALUES (
														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR),
														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),
														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME =  @APPR2),
														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3)
														)
					INSERT INTO @TABLE_RETURN_05 VALUES (@POS,@POS1,@POS2,@POS3)
					INSERT INTO @TABLE_RETURN_05 VALUES ((CASE WHEN @DATE IS NOT NULL AND @DATE <>'' THEN N'Đã chấp thuận lúc: '+@DATE ELSE '' END),
						(CASE WHEN @DATE1 IS NOT NULL AND @DATE1 <>'' THEN N'Đã chấp thuận lúc: '+@DATE1 ELSE '' END),
						(CASE WHEN @DATE2 IS NOT NULL AND @DATE2 <>'' THEN N'Đã chấp thuận lúc: '+@DATE2 ELSE '' END),
						(CASE WHEN @DATE3 IS NOT NULL AND @DATE3 <>'' THEN N'Đã chấp thuận lúc: '+@DATE3 ELSE '' END))
					END
				END
			END
				SELECT * FROM  @TABLE_RETURN_05

			¿
			
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_ById]
@P_REQ_ID varchar(15)
AS

	DECLARE  @STEP_KT_PROCESS_ID VARCHAR(1)
	SET @STEP_KT_PROCESS_ID= (SELECT MAX(ID) FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='KT')
	DECLARE @listTRADE TABLE
			(
				GOODS_ID VARCHAR(20),
				TRADE_ID VARCHAR(20),
				PLAN_ID VARCHAR(20),
				AMT_APP DECIMAL(18,2),
				AMT_EXE DECIMAL (18,2),
				AMT_ETM DECIMAL (18,2),
				AMT_TF DECIMAL (18,2),
				AMT_RECEIVE_TF DECIMAL (18,2)
			)

			IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID='APPROVE'))
			BEGIN
				IF(EXISTS(SELECT * FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID))
				BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID
				END
				ELSE
				BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
					SELECT PT.GOODS_ID,PT.TRADE_ID,PT.PLAN_ID,PT.AMT_APP,PT.AMT_EXE,PT.AMT_ETM-PLDT.TOTAL_AMT,PT.AMT_TF-PLFT.TOTAL_AMT,PT.AMT_RECEIVE_TF-PLTT.TOTAL_AMT FROM dbo.PL_TRADEDETAIL PT
					LEFT JOIN 
					(
					SELECT TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
					dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
					GROUP BY TRADE_ID
					) PLDT ON PLDT.TRADE_ID = PT.TRADE_ID
					LEFT JOIN 
					(
					SELECT FR_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
					GROUP BY FR_TRADE_ID
					) PLFT ON PLFT.FR_TRADE_ID = PT.TRADE_ID

					LEFT JOIN 
					(
					SELECT TO_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
					GROUP BY TO_TRADE_ID
					) PLTT ON PLTT.TO_TRADE_ID = PT.TRADE_ID
					WHERE (PT.TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR PT.TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
				END
			END
			ELSE
			BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)
					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF FROM dbo.PL_TRADEDETAIL WHERE (TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
			END



DECLARE @DV_QLNS NVARCHAR(1000)
DECLARE @TB_DV TABLE ( ID INT IDENTITY NOT NULL PRIMARY KEY,DV_QL_NS NVARCHAR(1000))
INSERT INTO @TB_DV SELECT B.TLFullName +N' chấp thuận '+ ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS TEN_DVCM FROM PL_REQUEST_COSTCENTER A
			INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
			WHERE REQ_ID =@P_REQ_ID
			SET @DV_QLNS = (SELECT DV_QL_NS FROM @TB_DV WHERE ID =1) +  ISNULL(' , ' + (SELECT DV_QL_NS FROM @TB_DV WHERE ID =2) ,'')+ ISNULL( ', '+ (SELECT DV_QL_NS FROM @TB_DV WHERE ID =3),'') + ISNULL(', '+ (SELECT DV_QL_NS FROM @TB_DV WHERE ID =4),'')
			DECLARE @TABLE1 TABLE (
				STT INT,
				GD_NAME NVARCHAR(200),
				AMT_APP DECIMAL(18, 2),
				AMT_EXE DECIMAL(18, 2),
				AMT_REMAIN DECIMAL(18, 2),
				TOTAL_AMT DECIMAL(18, 2),
				TOTAL_AMT_FN DECIMAL(18, 2),
				CHECKER_NAME NVARCHAR(200),
				XN_NAME VARCHAR(15)
			)
			INSERT INTO @TABLE1(STT,GD_NAME,AMT_APP,AMT_EXE,AMT_REMAIN,TOTAL_AMT,TOTAL_AMT_FN,CHECKER_NAME,XN_NAME)
			SELECT ROW_NUMBER() OVER (ORDER BY CG.GD_NAME) AS STT ,CG.GD_NAME,
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) AS AMT_APP,
			ISNULL(PT.AMT_ETM,0) AS AMT_EXE,
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) - ISNULL(PT.AMT_ETM,0) AS AMT_REMAIN,
			SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT,
			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) -ISNULL(PT.AMT_ETM,0) -SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT_FN,
			CASE
				WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName
				
				ELSE N''
			END AS CHECKER_NAME,
			
			'' AS XN_NAME
			FROM
			dbo.PL_REQUEST_DOC_DT PLDT

			LEFT JOIN @listTRADE PT ON PT.TRADE_ID=PLDT.TRADE_ID
			LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PLDT.PLAN_ID
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID 
			LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLDT.CHECKER_ID
			LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLDT.REQ_ID
			WHERE PLDT.REQ_ID=@P_REQ_ID
			GROUP BY PT.GOODS_ID,PT.AMT_APP,PT.AMT_ETM,PT.AMT_RECEIVE_TF,PT.AMT_TF,CG.GD_NAME,PLDT.REQ_ID,USC.TLFullName,PL.PROCESS_ID

DECLARE @TABLE TABLE (
	FR_AMT_APP DECIMAL(18,2),
	FR_AMT_REMAIN DECIMAL(18,2),
	TO_AMT_APP DECIMAL(18,2),
	TO_AMT_EXE DECIMAL(18,2),
	TO_AMT_REMAIN DECIMAL(18,2),
	TOTAL_AMT DECIMAL(18,2),
	FR_AMT_APP_FINAL DECIMAL(18,2),
	FR_AMT_REMAIN_FINAL DECIMAL(18,2),
	TO_AMT_APP_FINAL DECIMAL(18,2),
	TOTAL_AMT_SIGN DECIMAL(18,2),
	TOTAL_AMT_SIGN_FN DECIMAL(18,2)
)
INSERT INTO @TABLE(FR_AMT_APP,FR_AMT_REMAIN, TO_AMT_APP,TO_AMT_EXE, TO_AMT_REMAIN,
    TOTAL_AMT, FR_AMT_APP_FINAL,FR_AMT_REMAIN_FINAL,TO_AMT_APP_FINAL,
    TOTAL_AMT_SIGN,TOTAL_AMT_SIGN_FN)
SELECT  ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0) AS FR_AMT_APP,
		ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0)-ISNULL(PTF.AMT_ETM,0) AS FR_AMT_REMAIN,
		ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) AS TO_AMT_APP,
		ISNULL(PTT.AMT_ETM,0) AS TO_AMT_EXE,
		ISNULL(PTT.AMT_APP,0)  + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) -ISNULL(PTT.AMT_ETM,0) AS TO_AMT_REMAIN,
		ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_AMT,
		ISNULL(PTF.AMT_APP, 0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0) - ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_APP_FINAL,
		ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0) - ISNULL(PTF.AMT_TF,0)-ISNULL(PTF.AMT_ETM,0) - ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_REMAIN_FINAL,
		ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) +ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS TO_AMT_APP_FINAL,
		ISNULL(PLDT.TOTAL_AMT, 0) AS TOTAL_AMT_SIGN, -- Số tiền trình sử dụng
		ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0) - ISNULL(PTT.AMT_TF,0) -ISNULL(PTT.AMT_ETM,0) + ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) - PLDT.TOTAL_AMT AS TOTAL_AMT_SIGN_FN
FROM dbo.PL_REQUEST_TRANSFER PLTF
LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID
LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID
LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID
LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID
LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'
LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID
LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID
LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID
LEFT JOIN
			(
			SELECT GOODS_ID,REQ_ID, SUM(TOTAL_AMT) AS TOTAL_AMT FROM
			dbo.PL_REQUEST_DOC_DT 
			WHERE REQ_ID=@P_REQ_ID 
			GROUP BY GOODS_ID,REQ_ID )  PLDT ON PLDT.REQ_ID = PL.REQ_ID AND PLDT.GOODS_ID = PLTF.TO_GOOD_ID
WHERE PLTF.REQ_DOC_ID=@P_REQ_ID

SELECT TOP 1 RD.REQ_ID,
       RD.REQ_CODE,
       RD.REQ_NAME,
       RD.REQ_DT,
       RD.REQ_TYPE,
       RD.REQ_CONTENT,
       RD.REQ_REASON,
       RD.BRANCH_ID,	   
      (SELECT SUM(TOTAL_AMT) 
	   FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID ) AS TOTAL_AMT,
       RD.NOTES,
       RD.RECORD_STATUS,
       RD.MAKER_ID,
       RD.CREATE_DT,
       RD.AUTH_STATUS,
       RD.CHECKER_ID,
	   CASE WHEN (SELECT ISNULL(FORMAT(RD.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')) <>'' THEN N'Đã chấp thuận '+ ISNULL(FORMAT(RD.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS APPROVE_DT,
	   CASE WHEN (SELECT ISNULL(FORMAT(PKT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')) <> '' THEN N'Đã chấp thuận '+ ISNULL(FORMAT(PKT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') + CHAR (10) +
	   (SELECT PROCESS_DESC FROM PL_PROCESS WHERE PROCESS_ID ='KT' AND REQ_ID =@P_REQ_ID AND NOTES =N'Trưởng đơn vị đã phê duyệt' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID =@P_REQ_ID AND PROCESS_ID ='KT') )
	   ELSE '' END AS APPROVE_DT_KT,	   
       RD.PROCESS_ID,
       RD.DVDM_APP_ID,
       RD.REQ_PARENT_ID,
       RD.BRANCH_FEE,
       RD.EFFEC_DT,
       RD.IS_BACKDAY,
       RD.DEP_ID,
	   RD.BASED_CONTENT,
	   BR.BRANCH_NAME AS BRANCH_FEE_NAME,
       RD.DEP_FEE, CASE WHEN RD.NOTES IS NOT NULL AND RD.NOTES <> '' THEN RD.NOTES ELSE BR.BRANCH_NAME END AS BRANCH_NAME,BR.BRANCH_CODE,
	   (SELECT SUM(AMT_APP) FROM @TABLE1) AS TOTAL_AMT_APP,
	   (SELECT SUM(AMT_EXE) FROM @TABLE1) AS TOTAL_AMT_EXE,
	   (SELECT SUM(AMT_REMAIN) FROM @TABLE1) AS TOTAL_AMT_REMAIN,
	   --(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID) - (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE='O') AS AMT_IN,
	   (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID) AS AMT_IN,
	   (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID) - ISNULL((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID ),0) AS AMT_IN_TKH,
	   (SELECT SUM(TOTAL_AMT_FN) FROM @TABLE1) AS TOTAL_AMT_ETM,
		CASE
		WHEN NOT EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(0 AS BIT)  
		WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(1 AS BIT)  
		END AS IS_VCCB,
		CASE
		WHEN NOT EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(1 AS BIT)  
		WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND TRADE_TYPE ='VCCB') THEN CAST(0 AS BIT)  
		END AS IS_OTHER,

		(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE='O') AS AMT_OUT,
		(SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID) AS AMT_TRANSFER, -- Số tiền nhận điều chuyển
		DAY(RD.REQ_DT) AS DATE,
		MONTH(RD.REQ_DT) AS MONTH,
		YEAR(RD.REQ_DT) AS YEAR,
		--CASE WHEN DP.DEP_NAME IS NULL THEN BR.BRANCH_NAME  ELSE DP.DEP_NAME  END AS BRANCH_DEP,
		--'' BRANCH_DEP,
		 DP.DEP_NAME AS BRANCH_DEP ,
		--BR.BRANCH_NAME AS BRANCH_NAME,
		CASE WHEN PRC.ID IS NULL THEN UPRD.TLFullName ELSE UPRC.TLFullName END AS TDV_NAME,
		UTGD.TLFullName AS TGD_NAME,
		UHDQT.TLFullName AS HDQT_NAME,
		UPKT.TLFullName AS PKT_NAME,
		RD.IS_CHECKALL,RD.BASED_CONTENT,RD.PL_BASED_ID, '' AS PL_BASED_CONTENT, '' AS PL_BASED_CODE,
		CASE WHEN DP_C.DEP_NAME IS NULL THEN  BR_C.BRANCH_NAME ELSE DP_C.DEP_NAME END AS DV_CREATE, (SELECT @DV_QLNS) AS DV_QL_CONFIRM,
		--UPTC.TLFullName + N' Đã chấp thuận '+ ISNULL(FORMAT(PTC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')  AS TC_CONFIRM,
		'' AS TC_CONFIRM,
		UPTC.TLFullName + N' Đã chấp thuận lúc: '+ ISNULL(FORMAT(PTC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')  AS TAI_CHINH_CONFIRM,
		CASE WHEN EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID = @p_REQ_ID AND CURRENCY <>'VND') THEN (SELECT TOP 1 CURRENCY FROM PL_REQUEST_DOC_DT WHERE REQ_ID = @p_REQ_ID AND CURRENCY <>'VND') ELSE 'VND' END AS CURR
		,(SELECT FORMAT(SUM(ISNULL(FR_AMT_APP, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_APP,
		(SELECT FORMAT(SUM(ISNULL(FR_AMT_REMAIN, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_REMAIN,
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_APP, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_APP,
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_EXE, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_EXE,
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_REMAIN, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_REMAIN,
		(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT,
		(SELECT FORMAT(SUM(ISNULL(FR_AMT_APP_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_APP_FINAL,
		(SELECT FORMAT(SUM(ISNULL(FR_AMT_REMAIN_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_FR_AMT_REMAIN_FINAL,
		(SELECT FORMAT(SUM(ISNULL(TO_AMT_APP_FINAL, 0)),'#,0') FROM @TABLE) AS SUM_TO_AMT_APP_FINAL,
		(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT_SIGN, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT_SIGN,
		(SELECT FORMAT(SUM(ISNULL(TOTAL_AMT_SIGN_FN, 0)),'#,0') FROM @TABLE) AS SUM_TOTAL_AMT_SIGN_FN,
		CASE WHEN RD.NOTES IS NOT NULL AND RD.NOTES <> '' THEN RD.NOTES ELSE ISNULL(DP.DEP_NAME,'') +' '+ ISNULL(BR.BRANCH_NAME,'') END AS DV_CP,
		CASE WHEN PRHDQT.ID IS NOT NULL THEN N'Phê duyệt cấp HĐQT(5)' ELSE '' END AS  HDQT_TITLE,
		CASE WHEN PRHDQT.ID IS NOT NULL THEN N'Đã chấp thuận '+ ISNULL(FORMAT(PRHDQT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS HDQT_APPROVE_DT
		--CASE WHEN PRHDQT.ID IS NOT NULL THEN UHDQT.TLFullName ELSE '' END AS  HDQT_TITLE,
		FROM dbo.PL_REQUEST_DOC RD
		LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID=RD.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=RD.DEP_FEE
		LEFT JOIN dbo.TL_USER UD ON UD.TLNANME=RD.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRC ON PRC.REQ_ID=RD.REQ_ID AND PRC.PROCESS_ID='DVC'
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRD ON PRD.REQ_ID=RD.REQ_ID AND PRD.PROCESS_ID='APPNEW'
		LEFT JOIN dbo.TL_USER UPRC ON UPRC.TLNANME=PRC.CHECKER_ID
		LEFT JOIN dbo.TL_USER UPRD ON UPRD.TLNANME=PRD.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRTGD ON PRTGD.REQ_ID=RD.REQ_ID AND PRTGD.PROCESS_ID LIKE 'TGD%'
		LEFT JOIN dbo.TL_USER UTGD ON UTGD.TLNANME=PRTGD.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRHDQT ON PRHDQT.REQ_ID=RD.REQ_ID AND PRHDQT.PROCESS_ID='HDQT' AND PRHDQT.STATUS='P'
		LEFT JOIN dbo.TL_USER UHDQT ON UHDQT.TLNANME=PRHDQT.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS PKT ON PKT.REQ_ID=RD.REQ_ID AND PKT.PROCESS_ID='KT'
		LEFT JOIN dbo.PL_REQUEST_PROCESS PTC ON PTC.REQ_ID=RD.REQ_ID AND PTC.PROCESS_ID='TC'
		LEFT JOIN dbo.TL_USER UPKT ON UPKT.TLNANME=PKT.CHECKER_ID
		LEFT JOIN dbo.TL_USER UPTC ON UPTC.TLNANME=PTC.CHECKER_ID
		LEFT JOIN CM_BRANCH BR_C ON RD.BRANCH_CREATE = BR_C.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT DP_C ON RD.DEP_CREATE = DP_C.DEP_ID
		
		-- điều chuyển ngân sách
		LEFT JOIN dbo.PL_REQUEST_TRANSFER PLTF ON PLTF.REQ_DOC_ID = RD.REQ_ID
		LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID
		LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID
		LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID
		LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'
		LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID
		LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID


		-- DON VI QUAN LY NGAN SACH
		 WHERE RD.REQ_ID=@P_REQ_ID 
		 ORDER BY RD.REQ_ID