DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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