ALTER PROC [dbo].[TR_REQ_PAY_DETAIL]
@p_REQ_PAY_ID VARCHAR(15) = NULL
AS
BEGIN
	
	DECLARE @TABLE_RETURN_01 TABLE ([NAME] NVARCHAR(250))
	DECLARE @TABLE_RETURN_01_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250))
	DECLARE @TABLE_RETURN_02 TABLE ([NAME] NVARCHAR(250))
	DECLARE @TABLE_RETURN_02_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250))
	DECLARE @TABLE_RETURN_03 TABLE  (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250), AMT DECIMAL(18,0))
	DECLARE @TABLE_RETURN_04 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250), AMT DECIMAL(18,0))
	DECLARE @TABLE_RETURN_06 TABLE ([NAME] NVARCHAR(250), AMT DECIMAL(18,0))
	DECLARE @INDEX INT =1
	DECLARE @INDEX_TT INT =0
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'))
	BEGIN
	  SET @INDEX_TT = @INDEX_TT+1
	--INSERT INTO @TABLE_RETURN
	--SELECT N'Tiền mặt' AS PHUONG_THUC, ISNULL(SUM (TOTAL_AMT),0) AS T1 FROM TR_REQ_PAY_METHOD
	--WHERE REQ_PAY_TYPE ='1' AND REQ_PAY_ID = @p_REQ_PAY_ID
	INSERT INTO @TABLE_RETURN_03
	SELECT N'. Người nhận: '+ ACC_NAME + CHAR(10) + N'    CMND số: ' + ACC_NO + char(10) + N'    Được cấp bởi: '+ ISSUED_BY + N' ngày cấp: '+ CONVERT(VARCHAR,ISSUED_DT,103) AS ACC_NAME, SUM(TOTAL_AMT) AS T2 
	FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'
	GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT
	-- INSERT BANG TAM THE HIEN SO TIEN
	INSERT INTO @TABLE_RETURN_04
	SELECT 'N'+ CHAR(10) + 'D'+char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T2 
	FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'
	GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT
	END
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0'))
	BEGIN
		SET @INDEX_TT = @INDEX_TT+1
		--INSERT INTO @TABLE_RETURN
		--SELECT N'Chuyển khoản' AS PHUONG_THUC,ISNULL(SUM (TOTAL_AMT),0) AS T3 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_TYPE ='0' AND REQ_PAY_ID = @p_REQ_PAY_ID
		INSERT INTO @TABLE_RETURN_03
		SELECT N'. Đơn vị thụ hưởng: '+ ACC_NAME + CHAR(10)+ N'    Số tài khoản: ' + ACC_NO + char(10) + N'    Tại ngân hàng: '+ ISSUED_BY AS ACC_NAME, SUM(TOTAL_AMT) AS T4 
		FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0'
		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY
		-- INSERT BANG TAM THE HIEN SO TIEN
		INSERT INTO @TABLE_RETURN_04
		SELECT  'N'+ CHAR(10)+ 'S'+ char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T4 
		FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0'
		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY
	END
	-- KIEM TRA NOI DUNG SU DUNG CHI PHI VA NGAN SACH
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
	BEGIN
		--INSERT INTO @TABLE_RETURN_01 VALUES (CONVERT(VARCHAR(15),@INDEX) +N'. Nội dung tạm ứng: ')
		INSERT INTO @TABLE_RETURN_01 VALUES( N'A - Nội dung thanh toán: ')
		--INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_REASON FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
		--INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	
		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REASON FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
		INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.AMT_PAY FROM  TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	
		
		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_DESC FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
		INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.AMT_PAY_REAL FROM  TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	

		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_DESC FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
		INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM  TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID	
	END
	--- HĐ & CHỨNG TỪ ĐÍNH KÈM
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
	BEGIN
		SET @INDEX = @INDEX+1
		--INSERT INTO @TABLE_RETURN_01 SELECT CONVERT(VARCHAR(15),@INDEX)+ N'. Chứng từ đính kèm: '
		INSERT INTO @TABLE_RETURN_02 SELECT N'B - Chứng từ đính kèm: '
	END
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='C'))
	BEGIN
		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin hợp đồng đính kèm: ' 
		INSERT INTO @TABLE_RETURN_02_DT SELECT ' '+ N'. Hợp đồng số ' + REF_CODE + ISNULL(N' ngày '+  CONVERT(VARCHAR,REF_DT,101),'')+ N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN')  AS T1 FROM TR_REQ_PAY_ATTACH
		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='C'
	END
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='R'))
	BEGIN
		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: ' 
		INSERT INTO @TABLE_RETURN_02_DT SELECT N'. '+ [DESCRIPTION]+ N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') AS T1 FROM TR_REQ_PAY_ATTACH
		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='R'
	END
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
	BEGIN
		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: ' 
		INSERT INTO @TABLE_RETURN_02_DT SELECT N'. Số hóa đơn '+ INVOICE_NO +ISNULL(N' ngày '+  CONVERT(VARCHAR,INVOICE_DT,101),'')+ N', số tiền '+ FORMAT( ISNULL(PRICE+VAT,0),'#,#', 'vi-VN') AS T1 FROM TR_REQ_PAY_INVOICE
		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND TYPE_FUNC='HC'
	END
	SELECT * FROM @TABLE_RETURN_01
	SELECT * FROM @TABLE_RETURN_01_DT
	SELECT * FROM @TABLE_RETURN_02
    SELECT * FROM @TABLE_RETURN_02_DT
	SELECT * FROM @TABLE_RETURN_03
	SELECT * FROM @TABLE_RETURN_04
	SELECT * FROM @TABLE_RETURN_06
	
END