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)--------------*********
DECLARE @GDV VARCHAR(50), @KSV VARCHAR(50)
SET @GDV = (SELECT TOP 1  MAKER_ID FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID)
SET @KSV =(SELECT TOP 1  CHECKER_ID FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID)
--SELECT @ENTRY_PAIR
DECLARE @TABLE_REF TABLE (REF_NO VARCHAR(50))
INSERT INTO @TABLE_REF SELECT DISTINCT REF_NO FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID AND (REF_NO =@RefNo OR @RefNo IS NULL OR @RefNo ='')
DECLARE @TABLE_RETURN TABLE (TRN_ID VARCHAR(15), PrintDate VARCHAR(25),SUM_AMT DECIMAL(18,0),SUM_stringMoney VARCHAR(100),CASA_CR_ACCT VARCHAR(20),
CASA_CR_ACCT_NAME VARCHAR(250), CASA_DR_ACCT VARCHAR(20),CASA_DR_ACCT_NAME NVARCHAR(250),REF_NO VARCHAR(50),TRN_DESC VARCHAR(250), G_D_V NVARCHAR(50), 
K_S_V NVARCHAR(50), RATE VARCHAR(15), G_DV VARCHAR(35), DV_KD VARCHAR(50),TELL VARCHAR(50),SUP VARCHAR(50), DIR VARCHAR(50))
DECLARE @COUNT_SL INT, @REF_REFENCE VARCHAR(50)
SET @COUNT_SL =1
WHILE ((SELECT COUNT (*) FROM @TABLE_REF)>0)
BEGIN
	SET @REF_REFENCE=(SELECT TOP 1 REF_NO FROM @TABLE_REF)
	SET @COUNT_SL = @COUNT_SL+1
	INSERT INTO @TABLE_RETURN SELECT TOP 1 TRN_ID,ISNULL(FORMAT(TRN_DATE,'dd/MM/yyyy'),'') ,
	(SELECT SUM (AMT)/2 FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND TRN_ID =@p_REF_ID),
	REPLACE(dbo.ReadMoney((SELECT SUM (AMT) /2 FROM PAY_ENTRIES_POST WHERE  REF_NO =@REF_REFENCE AND TRN_ID =@p_REF_ID)),'  ',' '), 
	(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='C' AND TRN_ID =@p_REF_ID),
	[dbo].[FN_GET_ACC_NAME_PAY]((SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='C' AND TRN_ID =@p_REF_ID)),
	(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='D' AND TRN_ID =@p_REF_ID),
	[dbo].[FN_GET_ACC_NAME_PAY]((SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='D' AND TRN_ID =@p_REF_ID)),@REF_REFENCE, UPPER(TRN_DESC) , 
	CHAR(10) + dbo.fChuyenCoDauThanhKhongDau((SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME =@GDV)),CHAR(10) + dbo.fChuyenCoDauThanhKhongDau((SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME =@KSV)),
	'1', UPPER(@GDV),'HOI SO','Teller', 'Supervisor', 'Director'
	FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID AND REF_NO =@REF_REFENCE
	DELETE FROM @TABLE_REF WHERE REF_NO =@REF_REFENCE
END
SELECT * FROM @TABLE_RETURN
--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,
--   '' AS 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 1 A.REF_NO, 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
--	GROUP BY A.REF_NO
--	) AS B ON B.TRN_ID = A.TRN_ID
--LEFT JOIN 
--(
--	SELECT TOP 1 A.REF_NO, 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
--	GROUP BY A.REF_NO
--	) 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