ALTER PROC [dbo].[rpt_TR_REQ_PAYMENT_ByID]
@p_REQ_PAY_ID	varchar(15)= NULL
AS
		-------
		DECLARE @REQ_TYPE VARCHAR(15)
		SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
		-------------------------------------------
		DECLARE @CONTENT NVARCHAR(MAX)='', @CONTENT_CK NVARCHAR(MAX)='', @TM_TITLE NVARCHAR(100), @CK_TITLE NVARCHAR(100), @NDTM NVARCHAR(MAX) ='', @NDCK NVARCHAR(MAX) ='',
		@REQ_PAY_TYPE VARCHAR(5), @ACCNO VARCHAR(50), @ACCNAME NVARCHAR(500), @BANK NVARCHAR(500), @ISUED_DT DATE,
		@REASON NVARCHAR(MAX), @DESC NVARCHAR(MAX),@NOIDUNG NVARCHAR(MAX) ='',@AMT DECIMAL(18,2),@TYPE_TRANSFER VARCHAR(15)
        SELECT A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
		BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, 0 TOTAL_AMT_TEMP, 0 TOTAL_AMT_PAY_HIS,TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
		CASE WHEN @REQ_TYPE <> 'I' THEN (ISNULL(X2.SO_TIEN_TAM_UNG,0) + ISNULL(X3.SO_TIEN_TAM_UNG,0)) ELSE ISNULL(X.SOTIEN_TAMUNG,0) END AS SOTIEN_TAMUNG,
		CASE WHEN @REQ_TYPE <> 'I' THEN  ISNULL(A.REQ_AMT,0) ELSE ISNULL(X.AMT_USE,0) END AS SO_TIEN_DE_NGHI,
		CASE WHEN (ISNULL(X.SOTIEN_TAMUNG,0) - ISNULL(X.AMT_USE,0)) >0 THEN (ISNULL(X.SOTIEN_TAMUNG,0) - ISNULL(X.AMT_USE,0)) ELSE 0 END  AS SO_TIEN_CL,
		ISNULL(X.ST_BO_SUNG,0) SO_TIEN_VUOT,
		ISNULL(X.ST_BO_SUNG,0) + ISNULL(X.ST_HOAN_UNG,0)  CHI_BO_SUNG,
		DAY(A.CREATE_DT) D_, MONTH(A.CREATE_DT) M_, YEAR(A.CREATE_DT) Y_,
		'' AS NOIDUNG,
		'' AS PHUONG_THUC_THANH_TOAN, 'VND' TYPE_CUR,
		ISNULL(X.ST_HOAN_UNG,0) + ISNULL(X.ST_BO_SUNG,0) AS SO_TIEN_PTTT
        FROM TR_REQ_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE'
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			 LEFT JOIN 
			 (
				SELECT  A.PAY_ID, SUM(A.AMT_REMAIN) SOTIEN_TAMUNG, 
				SUM(A.AMT_DO) ST_DA_HOAN, 
				SUM(A.AMT_ADD) ST_BO_SUNG,SUM(A.AMT_USE) AS AMT_USE, SUM(A.AMT_REVERT) AS ST_HOAN_UNG FROM TR_REQ_PAYMENT_DT A
				GROUP BY A.PAY_ID
			 ) X ON A.REQ_PAY_ID = X.PAY_ID
			 LEFT JOIN
			 (
			 SELECT P.REQ_PAY_ID,SUM(P.AMT_PAY) AS SO_TIEN_TAM_UNG FROM TR_REQ_PAY_PERIOD P 
			 WHERE P.REQ_PAY_ID=@p_REQ_PAY_ID AND P.PROCESS <>'1'
			 GROUP BY P.REQ_PAY_ID
			 )
			 X2 ON A.REQ_PAY_ID = X2.REQ_PAY_ID
			  LEFT JOIN
			 (
			 SELECT P.REQ_PAY_ID,SUM(P.AMT_PAY) AS SO_TIEN_TAM_UNG FROM TR_REQ_PAY_SCHEDULE P 
			 WHERE P.REQ_PAY_ID=@p_REQ_PAY_ID AND P.PROCESS <>'1'
			 GROUP BY P.REQ_PAY_ID
			 )
			 X3 ON A.REQ_PAY_ID = X3.REQ_PAY_ID
        WHERE 1=1 
			  AND(A.REQ_PAY_ID=@p_REQ_PAY_ID)
ORDER BY A.CREATE_DT DESC;