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
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
DECLARE @TABLE_RETURN TABLE (TRN_ID VARCHAR(15), PrintDate VARCHAR(15),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))
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,(DAY(TRN_DATE))+N'/'+convert(varchar(10),MONTH(TRN_DATE)) +N'/'+convert(varchar(10),YEAR(TRN_DATE)),
	(SELECT SUM (AMT) FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND TRN_ID =@p_REF_ID),
	dbo.ReadMoney((SELECT SUM (AMT) 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, TRN_DESC
	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