ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_ById_Report]
@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),
        NOTES NVARCHAR(1000),
        AMT_ETM_TMP DECIMAL (18,2)
				
			)

      IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID IN ('','SIGN','APPNEW','REJECT')))
      BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,
          (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
              FROM dbo.PL_REQUEST_DOC_DT DDT
              LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
              WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
              AND DDT.TRADE_ID = PL.TRADE_ID
              AND DOC.REQ_ID <> @p_REQ_ID)
          +
          (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
          FROM dbo.PL_REQUEST_TRANSFER DDT
          LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
          WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
          AND DDT.FR_TRADE_ID = PL.TRADE_ID
          AND DOC.REQ_ID <> @p_REQ_ID)
          AS AMT_ETM_TMP
          FROM dbo.PL_TRADEDETAIL PL
          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
      ELSE
			BEGIN
--					INSERT INTO @listTRADE
--					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
--					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,'',AMT_ETM_TMP FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID
          INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
          SELECT * FROM
          (
          SELECT DISTINCT GOODS_ID,TRADE_ID,PLAN_ID,ISNULL(AMT_APP,0) AS AMT_APP,ISNULL(AMT_EXE,0) AS AMT_EXE,ISNULL(AMT_ETM,0) AS AMT_ETM,ISNULL(AMT_TF,0) AS AMT_TF,ISNULL(AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,'' NOTES,ISNULL(AMT_ETM_TMP,0) AS AMT_ETM_TMP FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
          UNION ALL
          SELECT DISTINCT FR_GOOD_ID GOODS_ID,FR_TRADE_ID TRADE_ID,FR_PLAN_ID PLAN_ID,ISNULL(FR_AMT_APP,0) AMT_APP,ISNULL(FR_AMT_EXE,0) AMT_EXE,ISNULL(FR_AMT_ETM,0) AMT_ETM,ISNULL(FR_AMT_TF,0) AMT_TF,ISNULL(FR_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(FR_AMT_ETM_TMP,0) AMT_ETM_TMP 
		  FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID AND ISNULL(FR_GOOD_ID,'') <> ''
          UNION ALL
          SELECT DISTINCT TO_GOOD_ID GOODS_ID,TO_TRADE_ID TRADE_ID,TO_PLAN_ID PLAN_ID,ISNULL(TO_AMT_APP,0) AMT_APP,ISNULL(TO_AMT_EXE,0) AMT_EXE,ISNULL(TO_AMT_ETM,0) AMT_ETM,ISNULL(TO_AMT_TF,0) AMT_TF,ISNULL(TO_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(TO_AMT_ETM_TMP,0) AMT_ETM_TMP FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
          ) T GROUP BY GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP
			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_PROCESS A
			INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
			WHERE REQ_ID =@P_REQ_ID AND A.PROCESS_ID ='DVCM'
			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),
				TOTAL_AMT_RECIVE DECIMAL(18, 2)
			)
			INSERT INTO @TABLE1(STT,GD_NAME,AMT_APP,AMT_EXE,AMT_REMAIN,TOTAL_AMT,TOTAL_AMT_FN,CHECKER_NAME,XN_NAME,TOTAL_AMT_RECIVE)
			SELECT ROW_NUMBER() OVER (ORDER BY CG.GD_NAME) AS STT ,CG.GD_NAME,
			ISNULL(PT.AMT_APP,0) AS AMT_APP,
			ISNULL(PT.AMT_ETM,0) + ISNULL(PT.AMT_ETM_TMP,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) - ISNULL(PT.AMT_ETM_TMP,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) -ISNULL(PT.AMT_ETM_TMP,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,
			ISNULL(PT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF
			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,PT.AMT_ETM_TMP



DECLARE @TABLE TABLE (
	FR_AMT_APP DECIMAL(18),
	FR_AMT_REMAIN DECIMAL(18),
	TO_AMT_APP DECIMAL(18),
	TO_AMT_EXE DECIMAL(18),
	TO_AMT_REMAIN DECIMAL(18),
	TOTAL_AMT DECIMAL(18),
	FR_AMT_APP_FINAL DECIMAL(18),
	FR_AMT_REMAIN_FINAL DECIMAL(18),
	TO_AMT_APP_FINAL DECIMAL(18),
	TOTAL_AMT_SIGN DECIMAL(18),
	TOTAL_AMT_SIGN_FN DECIMAL(18),
  TO_AMT_REMAIN_FINAL DECIMAL(18)
)
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,TO_AMT_REMAIN_FINAL)
SELECT  ISNULL(PTF.AMT_APP,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) - ISNULL(PTF.AMT_ETM_TMP,0) AS FR_AMT_REMAIN,
		ISNULL(PTT.AMT_APP,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) - ISNULL(PTT.AMT_ETM_TMP,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(PTF.AMT_ETM_TMP,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,
		
		CASE WHEN ISNULL(PLTF.TO_TRADE_ID,'') <> '' THEN
		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) 
		ELSE
		ISNULL(PLTF.TOTAL_AMT, 0)
		END
		AS TO_AMT_APP_FINAL,
		CASE WHEN ISNULL(PLTF.TO_TRADE_ID,'') <> '' THEN ISNULL(PLDT.TOTAL_AMT, 0) ELSE PLDT.TOTAL_AMT END AS TOTAL_AMT_SIGN, -- Số tiền trình sử dụng
		-- LUCTV 23042021 BO SUNG NEU DIEU CHUYEN NGOAI KE HOACH (KHONG CO TRADE ID THI SO TIEN CON LAI SAU KHI TRINH LUON =0)
		CASE WHEN ISNULL(PLTF.TO_TRADE_ID,'') <> '' THEN
		ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)- ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0) - ISNULL(PTT.AMT_ETM_TMP,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(PLTF.TOTAL_AMT, 0) 
				- ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT T WHERE T.TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_ID=PLTF.REQ_DOC_ID), 0) -- LUCTV 1-11-2022 DOI CONG THUC TINH NGAN SACH CON LAI SAU KHI DIEU CHUYEN
		ELSE
		0
		END
		AS TOTAL_AMT_SIGN_FN,
    ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)- ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0) - ISNULL(PTT.AMT_ETM_TMP,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_REMAIN_FINAL
		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, TRADE_ID, SUM(TOTAL_AMT) AS TOTAL_AMT FROM ---LUCTV: 02082022 BO SUNG GROUP BY THEO TRADE_ID
			dbo.PL_REQUEST_DOC_DT 
			WHERE REQ_ID=@P_REQ_ID 
			GROUP BY GOODS_ID,REQ_ID,TRADE_ID )  PLDT ON PLDT.REQ_ID = PL.REQ_ID AND PLDT.GOODS_ID = PLTF.TO_GOOD_ID AND PLTF.TO_TRADE_ID =PLDT.TRADE_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 FORMAT(SUM(TOTAL_AMT),'0')
	   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 RP_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) +
	   ISNULL((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_C.BRANCH_NAME END AS BRANCH_NAME,BR.BRANCH_CODE,
	   (SELECT FORMAT(SUM(AMT_APP),'0') FROM @TABLE1) AS TOTAL_AMT_APP,
	   (SELECT FORMAT(SUM(AMT_EXE),'0') FROM @TABLE1) AS TOTAL_AMT_EXE,
	   (SELECT FORMAT(SUM(AMT_REMAIN),'0') 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,
	   ISNULL((SELECT FORMAT(SUM(TOTAL_AMT),'0') FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE IN ('I')),0) AS AMT_IN,
	   ISNULL((SELECT FORMAT(SUM(TOTAL_AMT),'0') FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE IN ('I','O')),0) AS TOTAL_AMT_SIGN,
	   (SELECT FORMAT(SUM(TOTAL_AMT_RECIVE),'0') FROM @TABLE1) AS TOTAL_AMT_RECIVE, --- LUCTV 16.11.2022 BO SUNG THEM SUM NHAN DIEU CHUYEN
	   CASE 
     WHEN (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) <0 THEN '0'
	   ELSE FORMAT((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),'0') END AS AMT_IN_TKH,
	   (SELECT FORMAT(SUM(TOTAL_AMT_FN),'0') 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 FORMAT(SUM(TOTAL_AMT),'0') FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID AND REQDT_TYPE='O') AS AMT_OUT,
		(SELECT ISNULL(FORMAT(SUM(TOTAL_AMT),'0'),N'(Nếu có)') 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,
		,(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_ETM,
    (SELECT FORMAT(SUM(ISNULL(FR_AMT_REMAIN_FINAL, 0)),'0') FROM @TABLE) AS SUM_FR_AMT_REMAIN_ETM_FINAL,
		(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_REMAIN, 0)),'0') FROM @TABLE) AS SUM_TO_AMT_REMAIN_ETM,
    (SELECT FORMAT(SUM(ISNULL(TO_AMT_REMAIN_FINAL, 0)),'0') FROM @TABLE) AS SUM_TO_AMT_REMAIN_ETM_FINAL,
		(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, 0)),'0') FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@P_REQ_ID) 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
        WHEN RD.BRANCH_FEE = 'DV0001' AND (RD.NOTES ='' OR RD.NOTES IS NULL) THEN ISNULL(DP.DEP_NAME,'') +' - '+ ISNULL(BR.BRANCH_NAME,'')
        WHEN RD.BRANCH_FEE <> 'DV0001' THEN ISNULL(BR.BRANCH_NAME,'') END AS DV_CP,
		CASE WHEN HDQT.ID IS NOT NULL AND ISNULL(RD.OTHER_NOTES,'') <> '' THEN N'Thay mặt Hội Đồng Quản Trị' 
		WHEN HDQT.ID IS NOT NULL AND ISNULL(RD.OTHER_NOTES,'') ='' THEN N'Phê duyệt cấp HĐQT (6)' ELSE N'' END AS  HDQT_TITLE,
		CASE WHEN PRHDQT.ID IS NOT NULL AND RD.IS_AUTHORITY = 1 THEN N'Thay mặt Hội Đồng Quản Trị' + CHAR(10)+ N'Đã chấp thuận '+ ISNULL(FORMAT(PRHDQT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')
		WHEN PRHDQT.ID IS NOT NULL AND (RD.IS_AUTHORITY = 0 OR RD.IS_AUTHORITY ='') THEN N'Chủ Tịch Hội Đồng Quản Trị' + CHAR(10)+ N'Đã chấp thuận '+ ISNULL(FORMAT(PRHDQT.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END 
		AS HDQT_APPROVE_DT, -- LUCTV 10.11.2022 THEM DOAN CHECK ='' DE FIX LOI IN THIEU CHU TICH
		CASE WHEN PRTGD.ID IS NOT NULL THEN N'Phê duyệt cấp TGĐ (5)'
		ELSE N'' END AS  TGD_TITLE,
		CASE WHEN TGD.ID IS NOT NULL THEN N'Tổng Giám Đốc' + CHAR(10)+ N'Đã chấp thuận '+ ISNULL(FORMAT(TGD.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS TGD_APPROVE_DT,
		CASE WHEN DVDC.ID IS NOT NULL THEN (SELECT TLFullName FROM TL_USER WHERE TLNANME =DVDC.CHECKER_ID) + N' đã chấp thuận '+ ISNULL(FORMAT(DVDC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS DV_DC_NS_CONFIRM,
		CASE WHEN DVDM_DC.ID IS NOT NULL THEN (SELECT TLFullName FROM TL_USER WHERE TLNANME =DVDM_DC.CHECKER_ID) + N' đã chấp thuận '+  
		ISNULL(FORMAT(DVDM_DC.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END  AS DV_QL_NS_CONFIRM,
		--CASE WHEN PRHDQT.ID IS NOT NULL THEN UHDQT.TLFullName ELSE '' END AS  HDQT_TITLE,
		------------BAODNQ 27/6/2022: Lấy thêm số tiền trong kế hoạch, ngoài kế hoạch------
		(SELECT FORMAT(SUM(TOTAL_AMT),'0') FROM PL_REQUEST_DOC_DT WHERE REQ_ID = RD.REQ_ID AND REQDT_TYPE = 'I') AS IN_PLAN_TOTAL_AMT,
		(SELECT FORMAT(SUM(TOTAL_AMT),'0') FROM PL_REQUEST_DOC_DT WHERE REQ_ID = RD.REQ_ID AND REQDT_TYPE = 'O') AS OFF_PLAN_TOTAL_AMT,
    (SELECT FORMAT(SUM(PRICE),'0') FROM PL_REQUEST_DOC_DT WHERE REQ_ID = RD.REQ_ID) AS TOTAL_PRICE
		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.PL_REQUEST_PROCESS TGD ON TGD.REQ_ID=RD.REQ_ID AND TGD.PROCESS_ID LIKE 'TGD%' AND TGD.STATUS ='P' 
		LEFT JOIN dbo.PL_REQUEST_PROCESS HDQT ON HDQT.REQ_ID=RD.REQ_ID AND HDQT.PROCESS_ID='HDQT'
		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.PL_REQUEST_PROCESS DVDM_DC ON DVDM_DC.REQ_ID=RD.REQ_ID AND DVDM_DC.PROCESS_ID='DVDM_DC' AND DVDM_DC.STATUS ='P'
		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
		LEFT JOIN dbo.PL_REQUEST_PROCESS DVDC ON DVDC.REQ_ID=RD.REQ_ID AND DVDC.PROCESS_ID LIKE 'DVDC%' AND DVDC.STATUS ='P' 

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