ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_CASA_ByRefId] 
@p_REF_ID VARCHAR(200) = NULL, 
@p_BRANCH_ID VARCHAR(50) = NULL, 
@RefNo VARCHAR(500) = NULL
AS
DECLARE @l_SUM_AMT DECIMAL,
        @l_REF_NO VARCHAR(20),
        @l_ASSET_ID VARCHAR(20);
DECLARE @l_TRN_TYPE VARCHAR(50) = '';
DECLARE @l_CASA_CR_ACCT VARCHAR(20) = '';
DECLARE @l_CASA_DR_ACCT VARCHAR(20) = '';
DECLARE @l_CASA_CR_ACCT_NAME NVARCHAR(500) = '';
DECLARE @l_CASA_DR_ACCT_NAME NVARCHAR(500) = '';

DECLARE @MW_ENTRI TABLE (ET_ID varchar(15), REF_NO varchar(20), TRN_ID varchar(15), REF_ID varchar(15), TRN_TYPE nvarchar(40), ENTRY_PAIR varchar(15), DO_BRN varchar(15), DRCR varchar(2), ACCT varchar(100), BRN_ID varchar(20), DEPT_ID varchar(15), AMT decimal(18, 2), CURRENCY varchar(20), EXC_RATE numeric(18, 2), EXP_TO_CORE varchar(1), TRN_DATE datetime, TRN_DESC nvarchar(1000), MAKER_ID varchar(15), CHECKER_ID varchar(15))

INSERT INTO @MW_ENTRI 
SELECT * FROM PAY_ENTRIES_POST 
WHERE TRN_ID = @p_REF_ID AND (DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' ) AND (REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')

--SELECT * FROM @MW_ENTRI
DECLARE @ENTRY_PAIR VARCHAR(100)
SET @ENTRY_PAIR = (SELECT TOP 1 ENTRY_PAIR FROM @MW_ENTRI WHERE LEN(ACCT)>9)--------------*********

--SELECT @ENTRY_PAIR
SELECT MAX(KQ.TRN_ID) AS TRN_ID , MAX(KQ.PrintDate) AS PrintDate, SUM(KQ.SUM_AMT) AS SUM_AMT, MAX(KQ.SUM_stringMoney) AS SUM_stringMoney, MAX(KQ.CASA_CR_ACCT) AS CASA_CR_ACCT,
MAX(KQ.CASA_CR_ACCT_NAME) AS CASA_CR_ACCT_NAME,MAX(KQ.CASA_DR_ACCT) AS CASA_DR_ACCT,MAX(KQ.CASA_DR_ACCT_NAME) AS CASA_DR_ACCT_NAME, KQ.REF_NO, MAX(KQ.TRN_DESC) AS TRN_DESC, MAX(KQ.MAKER_ID) AS  [USER_NAME]
FROM 
(
SELECT  A.TRN_ID, 
	convert(varchar(10),DAY(A.TRN_DATE))+N'/'+convert(varchar(10),MONTH(A.TRN_DATE)) +N'/'+convert(varchar(10),YEAR(A.TRN_DATE)) AS PrintDate,
	A.SUM_AMT,
    A.SUM_stringMoney,
	C.CR_ACCT AS CASA_CR_ACCT, C.CASA_CR_ACCT_NAME,	
	B.DR_ACCT AS CASA_DR_ACCT, B.CASA_DR_ACCT_NAME,
	D.TRN_DESC,
    D.REF_NO,
    E.REF_NO_SUM, 
	A.MAKER_ID
FROM 
(
	SELECT A.TRN_ID,
		   CONVERT(DATE,A.TRN_DATE,103) TRN_DATE,
           SUM(A.AMT) AS SUM_AMT,
           dbo.ReadMoney(SUM(A.AMT)) AS SUM_stringMoney,
		   A.MAKER_ID
	FROM @MW_ENTRI A
	WHERE DRCR = 'D'
	GROUP BY A.TRN_ID, CONVERT(DATE,A.TRN_DATE,103), A.REF_NO,A.MAKER_ID
) AS A
LEFT JOIN 
(
	SELECT TOP 100 A.TRN_ID, A.ACCT DR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) AS CASA_DR_ACCT_NAME
	FROM @MW_ENTRI A
	WHERE LEN(A.ACCT) > 9 AND A.DRCR = 'D' AND ENTRY_PAIR = @ENTRY_PAIR
	) AS B ON B.TRN_ID = A.TRN_ID
LEFT JOIN 
(
	SELECT TOP 100 A.TRN_ID, A.ACCT CR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) CASA_CR_ACCT_NAME
	FROM @MW_ENTRI A	
	WHERE LEN(A.ACCT) > 9 AND A.DRCR = 'C' AND ENTRY_PAIR = @ENTRY_PAIR
	) AS C ON C.TRN_ID = A.TRN_ID
LEFT JOIN 
(
	SELECT TOP 100 A.TRN_DESC, A.TRN_ID, A.REF_NO
	FROM @MW_ENTRI A		
	) AS D ON D.TRN_ID = A.TRN_ID
LEFT JOIN
(
    SELECT STUFF((SELECT ', ' + C1.REF_NO
                FROM @MW_ENTRI C1
                WHERE C2.TRN_ID = C1.TRN_ID
                GROUP BY C1.TRN_ID,C1.REF_NO                
                FOR XML PATH('')
            ),1,1,'') AS REF_NO_SUM, C2.TRN_ID
    FROM @MW_ENTRI C2
    GROUP BY C2.TRN_ID, C2.REF_NO
) AS E ON E.TRN_ID = A.TRN_ID
) AS KQ
GROUP BY KQ.REF_NO