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

ALTER PROCEDURE [dbo].[rpt_PAY_LIET_KE_CHUNG_TU_BanViet] @p_DATE VARCHAR(10), @p_BRANCH_ID VARCHAR(15) ='', @p_MAKER_ID VARCHAR(15) =NULL

AS

-- LẤY MỖI SỐ REF NO RA

--DECLARE @ET TABLE (REF_NO VARCHAR(25), TK_NO VARCHAR(15), TK_CO VARCHAR(15), BRN_NO VARCHAR(15), BRANCH_CO VARCHAR(15), DEP_NO VARCHAR(15), DEP_CO VARCHAR(15))

--SELECT * FROM PAY_ENTRIES_POST WHERE TRN_ID='TRPTF0000000018' AND REF_NO ='069CCfffyutmyiiii09'

--1 LẤY DANH SÁCH HẠCH TOÁN THEO TỪNG REF NO 

--

DECLARE @REF_NO VARCHAR(45), @DR_ACCT VARCHAR(15), @CR_ACCT VARCHAR(15), @TRN_DATE VARCHAR(15), @TRN_ID VARCHAR(15), @DR_BRN VARCHAR(15), @CR_BRN VARCHAR(15), @DR_DEP VARCHAR(15), @CR_DEP VARCHAR(15),

@TRN_DESC VARCHAR(250),@MAKER_ID VARCHAR(15), @CHECKER_ID VARCHAR(15),@ET_ID VARCHAR(15), @AMT_DRCR DECIMAL(18,0), @INDEX_STT INT =0

DECLARE @TABLE_REF_NO TABLE (ID INT IDENTITY(1,1),REF_NO VARCHAR(25), TRN_ID VARCHAR(15))

INSERT INTO @TABLE_REF_NO SELECT DISTINCT A.REF_NO, TRN_ID FROM PAY_ENTRIES_POST A

WHERE (A.REF_NO IS NOT NULL AND A.REF_NO <> '' )

AND (CONVERT(DATE, A.TRN_DATE, 103) = CONVERT(DATE, @p_DATE, 103) OR @p_DATE IS NULL OR @p_DATE ='')

AND A.DO_BRN=@p_BRANCH_ID

AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')

AND (A.EXP_TO_CORE='Y' OR A.EXP_TO_CORE ='1')

-- KHAI BAO TABLE CHUA THÔNG TIN HẠCH TOÁN THEO CẤU TRÚC NỢ - CÓ CHUNG 1 HÀNG

DECLARE @MW_ENTRIES_POST TABLE (ID INT IDENTITY(1,1),TRN_DATE VARCHAR(15), TRN_ID VARCHAR(15),ET_ID VARCHAR(15), DR_BRANCH_CODE VARCHAR(15),DR_BRN VARCHAR(15), CR_BRANCH_CODE VARCHAR(15),

		CR_BRN VARCHAR(15),AMT DECIMAL(18,0),TRN_DESC NVARCHAR(1000),MAKER_ID VARCHAR(15),CHECKER_ID VARCHAR(15), DO_BRN VARCHAR(15),EXP_TO_CORE VARCHAR(1),REF_NO VARCHAR(50), CRDR VARCHAR(15), STT INT)

--TABLE CHUA DANH SACH HACH TOAN CÓ

DECLARE @ET_CR_DR TABLE (ID INT IDENTITY(1,1),TK VARCHAR(15), DV VARCHAR(15), AMT DECIMAL(18,0),ET_ID VARCHAR(15))

WHILE((SELECT COUNT(*) FROM @TABLE_REF_NO) >0)

BEGIN

	SET @REF_NO =(SELECT TOP 1 REF_NO FROM @TABLE_REF_NO)

	PRINT @REF_NO

	IF(NOT EXISTS(SELECT * FROM @MW_ENTRIES_POST WHERE TRN_ID =(SELECT TOP 1 TRN_ID WHERE REF_NO =@REF_NO)))

	BEGIN

		SET @INDEX_STT = @INDEX_STT +1

	END

	SET @TRN_DATE =(SELECT TOP 1 FORMAT(TRN_DATE,'dd/MM/yyyy') FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)

	PRINT @TRN_DATE

	SET @TRN_ID =(SELECT TOP 1 TRN_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)

	SET @TRN_DESC =(SELECT TOP 1 TRN_DESC FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)

	SET @MAKER_ID =(SELECT TOP 1 MAKER_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)

	SET @CHECKER_ID =(SELECT TOP 1 CHECKER_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)

	--SET @AMT =(SELECT TOP 1 AMT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)

	IF((SELECT COUNT(*)  FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')=1)

	BEGIN

		-- TAI KHOAN NO

		SET @DR_ACCT =(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')

		SET @DR_BRN =(SELECT TOP 1 BRN_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')

		--SET @DR_BRN =(SELECT TOP 1 DEPT_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')

		SET @ET_ID = (SELECT TOP 1 ET_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')

		SET @AMT_DRCR =(SELECT TOP 1 AMT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR='D')

		-- ĐỔ DỮ LIỆU VÀO BẢNG CHƯA HẠCH TOÁN CÓ

		INSERT INTO @ET_CR_DR SELECT ACCT,BRN_ID,AMT ,ET_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C'

		--SELECT * FROM @ET_CO

		INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR) ,@DR_ACCT ,@DR_BRN,'','',@AMT_DRCR,

			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'D',@INDEX_STT)

		WHILE ((SELECT COUNT(*) FROM @ET_CR_DR)>0)

		BEGIN

			INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR) ,'' ,'',(SELECT TOP 1 TK FROM @ET_CR_DR),(SELECT TOP 1 DV FROM @ET_CR_DR),(SELECT TOP 1 AMT FROM @ET_CR_DR),

			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'C',@INDEX_STT)

			DELETE FROM @ET_CR_DR WHERE ID =(SELECT TOP 1 ID FROM @ET_CR_DR)

		END

	END

	ELSE

	BEGIN -- XỬ LÝ NẾU 1 CÓ NHIỀU NỢ

		-- TAI KHOAN NO

		SET @CR_ACCT =(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C')

		SET @CR_BRN =(SELECT TOP 1 BRN_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C')

		SET @CR_DEP =(SELECT TOP 1 DEPT_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C')

		SET @AMT_DRCR =(SELECT TOP 1 AMT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR='C')

		-- ĐỔ DỮ LIỆU VÀO BẢNG CHƯA HẠCH TOÁN CÓ

		INSERT INTO @ET_CR_DR SELECT ACCT,BRN_ID,AMT,ET_ID  FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D'

		INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR),'','',@CR_ACCT,@CR_BRN,@AMT_DRCR,

			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'C',@INDEX_STT)

		WHILE ((SELECT COUNT(*) FROM @ET_CR_DR)>0)

		BEGIN

			INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR) ,(SELECT TOP 1 TK FROM @ET_CR_DR) ,(SELECT TOP 1 DV FROM @ET_CR_DR),'','',(SELECT TOP 1 AMT FROM @ET_CR_DR),

			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'D',@INDEX_STT)

			DELETE FROM @ET_CR_DR WHERE ID =(SELECT TOP 1 ID FROM @ET_CR_DR)

		END

	END

DELETE FROM @TABLE_REF_NO WHERE ID =(SELECT TOP 1 ID FROM @TABLE_REF_NO)

END -- KET THU WHILE

--LAY MA DON VI CUA HS NEU TIM THEO HS

IF @p_BRANCH_ID='HS' BEGIN

    SELECT @p_BRANCH_ID=BRANCH_ID FROM CM_BRANCH WHERE BRANCH_TYPE='HS';

END;

--SELECT CC.STT, CC.TRN_ID, CC.ET_ID, CC.DBRANCH_CODE, CC.CBRANCH_CODE, CC.CR_ACCT, CC.CR_BRN, CC.DR_ACCT, CC.DR_BRN, CC.AMT, CC.TRN_DESC,

--       CC.MAKER_ID, CC.CHECKER_ID, CC.CR_BRANCH_CODE, CC.DR_BRANCH_CODE, CC.MAPHONGBAN, CC.CCY, CC.REF_NO, CC.TRN_DATE

--FROM(

--    SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, MIN(A.TRN_DATE) AS TRN_DATE,A.TRN_ID,MAX(A.ET_ID) AS ET_ID,

--	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,

--    UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,'' AS CR_BRANCH_CODE,A.DR_ACCT+' '+D.BRANCH_CODE AS DR_BRANCH_CODE,

--	'' AS MAPHONGBAN, 'VND' AS CCY, A.REF_NO as REF_NO

--    FROM @MW_ENTRIES_POST A

--	     --LEFT JOIN dbo.MW_MAST_BAL B ON A.MAST_PRICE_ID=B.PRICE_ID

--         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN

--         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN

--	WHERE 1=1

--	AND CONVERT(DATE, A.TRN_DATE, 103)= CONVERT(DATE, @p_DATE, 103) AND A.DO_BRN=@p_BRANCH_ID

--    AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')

--	AND (A.EXP_TO_CORE='Y' OR A.EXP_TO_CORE ='1')

--	GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE,A.REF_NO

--    UNION ALL

--    SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, 

--	MIN(A.TRN_DATE) AS TRN_DATE,

--	A.TRN_ID,

--	MAX(A.ET_ID) AS ET_ID,

--	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,

--        --A.ASSET_ID,

--        --A.TRN_TYPE,

--        A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,

--        --A.REF_NO,

--        UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,

--        --B.ASSET_CODE,

--        --B.ASSET_NAME,

--        A.CR_ACCT+' '+C.BRANCH_CODE AS CR_BRANCH_CODE,

--        '' AS CR_BRANCH_CODE,

--        '' AS MAPHONGBAN, 'VND' AS CCY,

--		A.REF_NO as REF_NO

--       FROM @MW_ENTRIES_POST A

--        --LEFT JOIN dbo.MW_MAST_BAL B ON A.MAST_PRICE_ID=B.PRICE_ID

--         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN

--         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN

--    --LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID=A.CR_BRN

--    --LEFT JOIN CM_DEPARTMENT DD ON DD.DEP_ID=A.DR_BRN

--	WHERE 1=1

--	AND CONVERT(DATE, A.TRN_DATE, 103)= CONVERT(DATE, @p_DATE, 103) AND A.DO_BRN=@p_BRANCH_ID

--	 AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')

--	AND (A.EXP_TO_CORE='Y' OR A.EXP_TO_CORE ='1')

--	  GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE,A.REF_NO

--    ) AS CC

--ORDER BY cc.STT,CC.ET_ID, CC.CR_BRANCH_CODE, CC.TRN_DATE DESC;

SELECT CHAR(10) + A.TRN_DATE + CHAR(10) AS TRN_DATE, CHAR(10) + A.TRN_DESC + CHAR(10) AS TRN_DESC, CHAR(10) + A.TRN_ID + CHAR(10) AS TRN_ID, CHAR(10) + A.REF_NO + CHAR(10) AS REF_NO ,CHAR(10) + REPLACE (FORMAT ((A.AMT),'#,###'),',','.') +',00' + CHAR(10) AS AMT, CHAR(10) + 'VND' + CHAR(10)  AS CCY , 

CHAR(10) + REPLACE (FORMAT ((A.AMT),'#,###'),',','.') +',00' + CHAR(10) AS QUY_DOI, 

CHAR(10) + A.MAKER_ID + CHAR(10) AS MAKER_ID,CHAR(10)+ A.CHECKER_ID + CHAR(10) AS CHECKER_ID, CHAR(10)+  A.DR_BRANCH_CODE + ' ' + BR.BRANCH_CODE +  CHAR(10) AS DR_BRANCH_CODE  , CHAR(10)+ A.CR_BRANCH_CODE + CHAR(10) AS CR_BRANCH_CODE, CHAR(10) + CONVERT(VARCHAR,A.STT,5) + CHAR(10) AS STT

FROM @MW_ENTRIES_POST A

LEFT JOIN CM_BRANCH BR ON A.DR_BRN = BR.BRANCH_ID

ORDER BY A.REF_NO, A.CRDR DESC

SELECT N' Ngày lập phiếu: '+ FORMAT (GETDATE(),'dd/MM/yyyy H:mm:ss') AS NGAY_TAO, N'CHI NHÁNH /PGD : '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_ID) AS DVKD,

N'Ngày: '+ FORMAT (GETDATE(),'dd/MM/yyyy') AS NGAY_LAP_PHIEU 

SELECT CHAR(10) +'STT'+ CHAR(10) AS STT,

CHAR(10) +N'SỐ CHỨNG TỪ'+ CHAR(10) AS SO_CT,

CHAR(10) +N'SỐ GIAO DỊCH / REF NO'+ CHAR(10) AS SO_GD,

CHAR(10) +N'DIỄN GIẢI TRANSACTION'+ CHAR(10) AS CORE_NOTE,

CHAR(10) +N'THỜI GIAN '+ CHAR(10) AS NGAY_GD,

CHAR(10) +N'NỢ '+ CHAR(10) AS [NO],

CHAR(10) +N'CÓ '+ CHAR(10) AS [CO],

CHAR(10) +N'CCY '+ CHAR(10) AS CCY,

CHAR(10) +N'NGUYÊN TỆ '+ CHAR(10) AS NT,

CHAR(10) +N'QUY ĐỔI '+ CHAR(10) AS QD,

CHAR(10) +N'GDV '+ CHAR(10) AS GDV,

CHAR(10) +N'KSV '+ CHAR(10) AS KSV

¿

ALTER PROCEDURE [dbo].[rpt_LIET_KE_CHUNG_TU_BanViet] @p_DATE VARCHAR(10), @p_BRANCH_ID VARCHAR(15) ='', @p_MAKER_ID VARCHAR(15) =NULL

AS

--LAY MA DON VI CUA HS NEU TIM THEO HS

IF @p_BRANCH_ID='HS' BEGIN

    SELECT @p_BRANCH_ID=BRANCH_ID FROM CM_BRANCH WHERE BRANCH_TYPE='HS';

END;

SELECT CHAR(10)+ CONVERT(VARCHAR,CC.STT,10) + CHAR(10) AS STT, CHAR(10) + CC.TRN_ID + CHAR(10) AS TRN_ID, CHAR(10)+ CC.ET_ID + CHAR(10) AS ET_ID, CC.DBRANCH_CODE, CC.CBRANCH_CODE, 

	   CHAR(10) + CC.CR_ACCT + CHAR(10) AS CR_ACCT , CC.CR_BRN, CHAR(10) + CC.DR_ACCT + CHAR(10) AS DR_ACCT, CC.DR_BRN, CHAR(10) + REPLACE (FORMAT ((CC.AMT),'#,###'),',','.') +',00 ' + CHAR(10)  AS AMT, CHAR(10)+  CC.TRN_DESC + CHAR(10) AS TRN_DESC,

       CHAR(10)+ CC.MAKER_ID + CHAR(10) AS MAKER_ID , CHAR(10) + CC.CHECKER_ID + CHAR(10) AS CHECKER_ID , CHAR(10)+ CC.CR_BRANCH_CODE + CHAR(10) AS CR_BRANCH_CODE , CHAR(10)+ CC.DR_BRANCH_CODE + CHAR(10) AS DR_BRANCH_CODE , CC.MAPHONGBAN, CHAR(10)+ CC.CCY+ CHAR(10) AS CCY, CHAR(10) + CC.REF_NO + CHAR(10) AS REF_NO , 

	   CHAR(10) + FORMAT(CC.TRN_DATE,'dd/MM/yyyy') + CHAR(10) AS TRN_DATE

FROM(

    SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, 

	MIN(A.TRN_DATE) AS TRN_DATE,

	A.TRN_ID,

	MAX(A.ET_ID) AS ET_ID,

	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,

        --A.ASSET_ID,

        --A.TRN_TYPE,

        A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,

        --A.REF_NO,

        UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,

        --B.ASSET_CODE,

        --B.ASSET_NAME,

        '' AS CR_BRANCH_CODE,

        --(ISNULL(REPLACE(C.BRANCH_CODE, 'VN001', ''), '00'+CD.DAO_CODE)+'-'+ISNULL(C.BRANCH_NAME, CD.DEP_NAME)) AS CR_BRANCH_NAME,

        A.DR_ACCT+' '+D.BRANCH_CODE AS DR_BRANCH_CODE,

        --(ISNULL(REPLACE(D.BRANCH_CODE, 'VN001', ''), '00'+DD.DAO_CODE)+'-'+ISNULL(D.BRANCH_NAME, DD.DEP_NAME)) AS DR_BRANCH_NAME,

        --CD.DEP_CODE + ' - '+ CD.DEP_NAME AS CD_DEP_CODE,

        --DD.DEP_CODE + ' - '+  CD.DEP_NAME AS DD_DEP_CODE

        '' AS MAPHONGBAN, 'VND' AS CCY,

        --A.REF_ID,

     --   STUFF(

     --   (

     --   SELECT ', '+C1.REF_NO

     --   FROM ASS_ENTRIES_POST C1

     --   WHERE A.TRN_ID=C1.TRN_ID AND A.CR_ACCT=C1.CR_ACCT AND A.CR_BRN=C1.CR_BRN AND A.DR_ACCT=C1.DR_ACCT AND A.DR_BRN=C1.DR_BRN AND A.TRN_DESC=C1.TRN_DESC

     --         AND CONVERT(DATE, A.TRN_DATE, 103)=CONVERT(DATE, C1.TRN_DATE, 103)

			  --AND A.MAKER_ID=A.MAKER_ID AND A.CHECKER_ID=A.CHECKER_ID AND D.BRANCH_CODE=D.BRANCH_CODE

     --   FOR XML PATH('')

     --   ),   1, 1, ''

     --        ) AS REF_NO

			 A.REF_NO as REF_NO

    FROM ASS_ENTRIES_POST A

         LEFT JOIN ASS_MASTER B ON A.ASSET_ID=B.ASSET_ID

         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN

         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN

    --LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID=A.CR_BRN

    --LEFT JOIN CM_DEPARTMENT DD ON DD.DEP_ID=A.DR_BRN

	WHERE DATEDIFF(DAYOFYEAR, A.TRN_DATE, CONVERT(DATETIME, @p_DATE, 103))=0 AND A.DO_BRN=@p_BRANCH_ID

          AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')AND A.EXP_TO_CORE='Y'

		      GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE,A.REF_NO

    UNION ALL

       SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, 

	MIN(A.TRN_DATE) AS TRN_DATE,

	A.TRN_ID,

	MAX(A.ET_ID) AS ET_ID,

	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,

        --A.ASSET_ID,

        --A.TRN_TYPE,

        A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,

        --A.REF_NO,

        UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,

        --B.ASSET_CODE,

        --B.ASSET_NAME,

        A.CR_ACCT+' '+C.BRANCH_CODE AS CR_BRANCH_CODE,

        --(ISNULL(REPLACE(C.BRANCH_CODE, 'VN001', ''), '00'+CD.DAO_CODE)+'-'+ISNULL(C.BRANCH_NAME, CD.DEP_NAME)) AS CR_BRANCH_NAME,

        '' AS CR_BRANCH_CODE,

        --(ISNULL(REPLACE(D.BRANCH_CODE, 'VN001', ''), '00'+DD.DAO_CODE)+'-'+ISNULL(D.BRANCH_NAME, DD.DEP_NAME)) AS DR_BRANCH_NAME,

        --CD.DEP_CODE + ' - '+ CD.DEP_NAME AS CD_DEP_CODE,

        --DD.DEP_CODE + ' - '+  CD.DEP_NAME AS DD_DEP_CODE

        '' AS MAPHONGBAN, 'VND' AS CCY,

        --A.REF_ID,

     --   STUFF(

     --   (

     --   SELECT ', '+C1.REF_NO

     --   FROM ASS_ENTRIES_POST C1

     --   WHERE A.TRN_ID=C1.TRN_ID AND A.CR_ACCT=C1.CR_ACCT AND A.CR_BRN=C1.CR_BRN AND A.DR_ACCT=C1.DR_ACCT AND A.DR_BRN=C1.DR_BRN AND A.TRN_DESC=C1.TRN_DESC

     --         AND CONVERT(DATE, A.TRN_DATE, 103)=CONVERT(DATE, C1.TRN_DATE, 103)

			  --AND A.MAKER_ID=A.MAKER_ID AND A.CHECKER_ID=A.CHECKER_ID AND D.BRANCH_CODE=D.BRANCH_CODE

     --   FOR XML PATH('')

     --   ),   1, 1, ''

     --        ) AS REF_NO

			 A.REF_NO as REF_NO

    FROM ASS_ENTRIES_POST A

         LEFT JOIN ASS_MASTER B ON A.ASSET_ID=B.ASSET_ID

         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN

         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN

    --LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID=A.CR_BRN

    --LEFT JOIN CM_DEPARTMENT DD ON DD.DEP_ID=A.DR_BRN

		   WHERE DATEDIFF(DAYOFYEAR, A.TRN_DATE, CONVERT(DATETIME, @p_DATE, 103))=0 AND A.DO_BRN=@p_BRANCH_ID

          AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')AND A.EXP_TO_CORE='Y'

		      GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE,A.REF_NO

    ) AS CC

ORDER BY cc.STT,CC.ET_ID, CC.CR_BRANCH_CODE, CC.TRN_DATE DESC;

SELECT N' Ngày lập phiếu: '+ FORMAT (GETDATE(),'dd/MM/yyyy H:mm:ss') AS NGAY_TAO, 'CN /PGD : '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_ID) AS DVKD,

FORMAT (GETDATE(),'dd/MM/yyyy') AS NGAY_LAP_PHIEU

SELECT CHAR(10) +'STT'+ CHAR(10) AS STT,

CHAR(10) +N'SỐ CHỨNG TỪ'+ CHAR(10) AS SO_CT,

CHAR(10) +N'SỐ GIAO DỊCH / REF NO'+ CHAR(10) AS SO_GD,

CHAR(10) +N'DIỄN GIẢI TRANSACTION'+ CHAR(10) AS CORE_NOTE,

CHAR(10) +N'THỜI GIAN '+ CHAR(10) AS NGAY_GD,

CHAR(10) +N'NỢ '+ CHAR(10) AS [NO],

CHAR(10) +N'CÓ '+ CHAR(10) AS [CO],

CHAR(10) +N'CCY '+ CHAR(10) AS CCY,

CHAR(10) +N'NGUYÊN TỆ '+ CHAR(10) AS NT,

CHAR(10) +N'QUY ĐỔI '+ CHAR(10) AS QD,

CHAR(10) +N'GDV '+ CHAR(10) AS GDV,

CHAR(10) +N'KSV '+ CHAR(10) AS KSV,

CHAR(10) +N'PHÒNG BAN '+ CHAR(10) AS PB

¿

ALTER PROCEDURE [dbo].[rpt_MW_LIET_KE_CHUNG_TU_BanViet] @p_DATE VARCHAR(10), @p_BRANCH_ID VARCHAR(15) ='', @p_MAKER_ID VARCHAR(15) =NULL

AS

--LAY MA DON VI CUA HS NEU TIM THEO HS

IF @p_BRANCH_ID='HS' BEGIN

    SELECT @p_BRANCH_ID=BRANCH_ID FROM CM_BRANCH WHERE BRANCH_TYPE='HS';

END;

SELECT CC.STT, CHAR(10) + CC.TRN_ID + CHAR(10) AS TRN_ID, CHAR(10) + CC.ET_ID + CHAR(10) AS ET_ID, CC.DBRANCH_CODE, CC.CBRANCH_CODE, CC.CR_ACCT, CC.CR_BRN, CC.DR_ACCT, CC.DR_BRN, CHAR(10) +  REPLACE (FORMAT ((CC.AMT),'#,###'),',','.') +',00' + CHAR(10) AS AMT, 

CHAR(10) +  CC.TRN_DESC + CHAR(10) AS TRN_DESC ,

       CHAR(10) + CC.MAKER_ID + CHAR(10) AS MAKER_ID , CHAR(10) + CC.CHECKER_ID + CHAR(10) AS CHECKER_ID ,CHAR(10) + CC.CR_BRANCH_CODE + CHAR(10) AS CR_BRANCH_CODE , CHAR(10)+ CC.DR_BRANCH_CODE + CHAR(10) AS DR_BRANCH_CODE , CC.MAPHONGBAN, 

	   CHAR(10) + CC.CCY + CHAR(10) AS CCY, CHAR(10) + CC.REF_NO + CHAR(10) AS REF_NO , CHAR(10) +  FORMAT(CC.TRN_DATE,'dd/MM/yyyy') + CHAR(10) AS TRN_DATE

FROM(

    SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, MIN(A.TRN_DATE) AS TRN_DATE,A.TRN_ID,MAX(A.ET_ID) AS ET_ID,

	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,

    UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,'' AS CR_BRANCH_CODE,A.DR_ACCT+' '+D.BRANCH_CODE AS DR_BRANCH_CODE,

	'' AS MAPHONGBAN, 'VND' AS CCY,A.REF_NO as REF_NO

    FROM dbo.MW_ENTRIES_POST A

	     --LEFT JOIN dbo.MW_MAST_BAL B ON A.MAST_PRICE_ID=B.PRICE_ID

         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN

         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN

	WHERE 

	DATEDIFF(DAYOFYEAR, A.TRN_DATE, CONVERT(DATETIME, @p_DATE, 103))=0 AND A.DO_BRN=@p_BRANCH_ID

          AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')AND A.EXP_TO_CORE='Y'

		      GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE,A.REF_NO

    UNION ALL

    SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, 

	MIN(A.TRN_DATE) AS TRN_DATE,

	A.TRN_ID,

	MAX(A.ET_ID) AS ET_ID,

	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,

        --A.ASSET_ID,

        --A.TRN_TYPE,

        A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,

        --A.REF_NO,

        UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,

        --B.ASSET_CODE,

        --B.ASSET_NAME,

        A.CR_ACCT+' '+C.BRANCH_CODE AS CR_BRANCH_CODE,

        '' AS CR_BRANCH_CODE,

        '' AS MAPHONGBAN,  'VND' AS CCY,

			 A.REF_NO as REF_NO

       FROM dbo.MW_ENTRIES_POST A

        --LEFT JOIN dbo.MW_MAST_BAL B ON A.MAST_PRICE_ID=B.PRICE_ID

         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN

         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN

    --LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID=A.CR_BRN

    --LEFT JOIN CM_DEPARTMENT DD ON DD.DEP_ID=A.DR_BRN

		   WHERE DATEDIFF(DAYOFYEAR, A.TRN_DATE, CONVERT(DATETIME, @p_DATE, 103))=0 AND A.DO_BRN=@p_BRANCH_ID

			 AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')AND A.EXP_TO_CORE='Y'

		      GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE, A.REF_NO

    ) AS CC

ORDER BY cc.STT,CC.ET_ID, CC.CR_BRANCH_CODE, CC.TRN_DATE DESC;

SELECT N' Ngày lập phiếu: '+ FORMAT (GETDATE(),'dd/MM/yyyy H:mm:ss') AS NGAY_TAO, 'CN /PGD : '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_ID) AS DVKD,

FORMAT (GETDATE(),'dd/MM/yyyy') AS NGAY_LAP_PHIEU

SELECT CHAR(10) +'STT'+ CHAR(10) AS STT,

CHAR(10) +N'SỐ CHỨNG TỪ'+ CHAR(10) AS SO_CT,

CHAR(10) +N'SỐ GIAO DỊCH / REF NO'+ CHAR(10) AS SO_GD,

CHAR(10) +N'DIỄN GIẢI TRANSACTION'+ CHAR(10) AS CORE_NOTE,

CHAR(10) +N'THỜI GIAN '+ CHAR(10) AS NGAY_GD,

CHAR(10) +N'NỢ '+ CHAR(10) AS [NO],

CHAR(10) +N'CÓ '+ CHAR(10) AS [CO],

CHAR(10) +N'CCY '+ CHAR(10) AS CCY,

CHAR(10) +N'NGUYÊN TỆ '+ CHAR(10) AS NT,

CHAR(10) +N'QUY ĐỔI '+ CHAR(10) AS QD,

CHAR(10) +N'GDV '+ CHAR(10) AS GDV,

CHAR(10) +N'KSV '+ CHAR(10) AS KSV,

CHAR(10) +N'PHÒNG BAN '+ CHAR(10) AS PB

¿

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

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))

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, 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'

	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

¿

ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_ByRefId] 

@p_REF_ID VARCHAR(200) = NULL, 

@p_BRANCH_ID VARCHAR(50) = NULL, 

@RefNo VARCHAR(500) = NULL

AS

-- LUCTV 03062020 BO SUNG BANG MAP GL

DECLARE  @TABLE_MAP_GL TABLE (ACCT VARCHAR(100), TL_GL VARCHAR(100), GL_NAME VARCHAR(1000))

INSERT INTO @TABLE_MAP_GL SELECT ACC_NO, TK_GL, TK_GL_NAME FROM CM_ACCOUNT

--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, TK_GL, TK_GL_NAME FROM CM_ACCOUNT_PAY

--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, (SELECT ParaValue FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL'), (SELECT Description FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL') FROM CM_SUPPLIER

--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM_OUT, (SELECT ParaValue FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL_O'), (SELECT Description FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL_O') FROM CM_SUPPLIER

---

SELECT A.REF_NO, MAX(A.DR_ACCT) AS DR_ACCT, MAX(A.DR_ACCT_NAME) AS DR_ACCT_NAME,

MAX(A.DR_BRN) AS DR_BRN,MAX(A.DR_D) AS DR_D, MAX(A.ACCT) AS ACCT, MAX(A.REF_ID) AS REF_ID, MAX(A.DR_D) AS DR_D, MAX(A.ENTRY_PAIR) AS ENTRY_PAIR,

MAX(A.DRCR) AS DRCR,  REPLACE (FORMAT (SUM(A.AMT),'#,###'),',','.') +',00 VND' AS AMT

FROM

(

SELECT A.AMT, A.ACCT +' '+ BR.BRANCH_CODE AS DR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) AS DR_ACCT_NAME, '' DR_BRN, 

(CASE WHEN A.DRCR = 'D' THEN N'Nợ/Debit' ELSE N'Có/Credit' END) AS ACCT, '' AS REF_ID, ISNULL(DP.DEP_CODE,'') AS DR_D, A.ENTRY_PAIR,A.DRCR,A.REF_NO

FROM PAY_ENTRIES_POST A

LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID

LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID

WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '') 

AND LEN(A.ACCT) <= 9

--ORDER BY A.ENTRY_PAIR, A.DRCR DESC

UNION 

SELECT A.AMT, GL.TL_GL +' '+ BR.BRANCH_CODE  AS DR_ACCT, GL.GL_NAME AS DR_ACCT_NAME, '' DR_BRN, 

(CASE WHEN A.DRCR = 'D' THEN N'Nợ/Debit' ELSE N'Có/Credit' END) AS ACCT, '' AS REF_ID, ISNULL(DP.DEP_CODE,'') AS DR_D,A.ENTRY_PAIR,A.DRCR,A.REF_NO

FROM PAY_ENTRIES_POST A

INNER JOIN @TABLE_MAP_GL GL ON A.ACCT = GL.ACCT

LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID

LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID

WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '') 

AND LEN(A.ACCT) > 9

) A

--ORDER BY A.ENTRY_PAIR, A.DRCR,A.REF_NO

GROUP BY A.REF_NO, A.DR_ACCT, A.DRCR

ORDER BY A.DRCR DESC

--DECLARE @ENT_TEMP TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15))

--DECLARE @ENT_TEMP_ROOT TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15),DR_NAME NVARCHAR(500), CR_NAME NVARCHAR(500))

--DECLARE @TRN_DESC NVARCHAR(500) = '', @TRN_TYPE VARCHAR(50)

----------------------------LAY DANH SACH BUT TOAN -- LU VAO BANG DU LIEU GOC--------------------------

--INSERT INTO @ENT_TEMP_ROOT SELECT Row_number() over(order by A.CR_ACCT),'',A.CR_ACCT, B.BRANCH_CODE CR_BRN, A.DR_ACCT, C.BRANCH_CODE DR_BRN, SUM(A.AMT) AS AMT,@TRN_DESC, A.MAST_PRICE_ID AS ASSET_ID,NULL, NULL

--FROM MW_ENTRIES_POST A

--LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID

--LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID

--WHERE TRN_ID = @p_REF_ID AND (A.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 = '')

--GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID

----ORDER BY LEN(CR_ACCT)

----select * from @ENT_TEMP_ROOT

----DECLARE @COUNT INT = (SELECT COUNT(*) - COUNT(DISTINCT ASSET_ID) FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID)

----IF (SELECT COUNT(DISTINCT TRN_TYPE) FROM MW_ENTRIES_POST WHERE TRN_ID = @p_REF_ID) > 1

----SET @TRN_TYPE = 'ADD_NEW'

----ELSE

----SET @TRN_TYPE = (SELECT TOP 1 TRN_TYPE FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID )

------------------------------TACH 1 DONG DU LIEU THANH 2 DONG NAM LIEN KE NHAU DE BINDING WORD--------------------------

---------LAY TAI KHOAN GL TUONG UNG CASA TRONG CM_BRANCH

--UPDATE @ENT_TEMP_ROOT SET CR_ACCT = B.TEL, CR_NAME = B.PROVICE

--FROM CM_BRANCH B

--INNER JOIN @ENT_TEMP_ROOT C ON C.CR_ACCT = B.DAO_CODE AND LEN(C.CR_ACCT)>9

--UPDATE @ENT_TEMP_ROOT SET DR_ACCT = B.TEL, DR_NAME = B.PROVICE

--FROM CM_BRANCH B

--INNER JOIN @ENT_TEMP_ROOT C ON C.DR_ACCT = B.DAO_CODE AND LEN(C.DR_ACCT)>9

----IF @TRN_TYPE = 'ADD_NEW'  OR @TRN_TYPE = 'ASS_USE' OR @TRN_TYPE='ASS_TRANSFER' 

----BEGIN

--	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D

--	FROM

--	(

--		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, ISNULL(A.DR_NAME,[dbo].[FN_GET_ACC_NAME](A.DR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D

--		FROM @ENT_TEMP_ROOT A

--		--WHERE LEN(A.DR_ACCT) <= 9

--		UNION 

--		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, ISNULL(A.CR_NAME,[dbo].[FN_GET_ACC_NAME](A.CR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D

--		FROM @ENT_TEMP_ROOT A

--		--WHERE LEN(A.CR_ACCT) <= 9

--		--ORDER BY ID, ACCT DESC

--	) AG

--	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D

--	ORDER BY SUM(REF_ID), AG.ACCT DESC

----END

----ELSE

----BEGIN

----	--IF LEFT(@p_REF_ID,4) = 'ASSL'--THANH LY TAI SAN

----	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D

----	FROM

----	(

----		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.DR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D

----		FROM @ENT_TEMP_ROOT A

----		UNION 

----		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.CR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D

----		FROM @ENT_TEMP_ROOT A

----	--ORDER BY ID, ACCT DESC

----	) AG

----	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D

----	ORDER BY AG.DR_BRN, AG.ACCT DESC

----END

--DELETE @ENT_TEMP

--DELETE @ENT_TEMP_ROOT

--------------------------DONE--------------------------

¿

ALTER PROC [dbo].[CM_ACCOUNT_PAY_Upd]

@p_USER_ID VARCHAR(15) = NULL,

@p_ACC_TYPE VARCHAR(150) = NULL,

@p_ACC_NO VARCHAR(150) = NULL,

@p_ACC_NAME VARCHAR(150) = NULL,

@p_MAKER_ID VARCHAR(15)= NULL,

@p_TK_GL VARCHAR(100)= NULL,

@p_TK_GL_NAME VARCHAR(1000)= NULL,

@p_EDITOR_ID   VARCHAR(15)= NULL

AS

BEGIN TRANSACTION

	IF(@p_TK_GL ='' OR @p_TK_GL IS NULL)

	BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản GL không được phép để trống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_TK_GL_NAME ='' OR @p_TK_GL_NAME IS NULL)

	BEGIN

	ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' ACC_ID, N'Tên tài khoản GL không được phép để trống' ErrorDesc

			RETURN '-1'

	END

	IF(@p_ACC_NO ='' OR @p_ACC_NO IS NULL)

	BEGIN

	ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Số tài khoản không được phép để trống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_ACC_NAME ='' OR @p_ACC_NAME IS NULL)

	BEGIN

	ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tên tài khoản không được phép để trống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_ACC_TYPE ='ADV_PAY')

	BEGIN

		IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE REF_ID <> @p_USER_ID AND ACC_NUM =@p_ACC_NO))

		BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản tạm ứng ' + @p_ACC_NO + N' đã tồn tại trong hệ thống' ErrorDesc

		RETURN '-1'

		END

		IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_TYPE='I' AND RECEIVER_DEBIT =@p_ACC_NO))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' ACC_ID, N'Tài khoản tạm ứng ' + @p_ACC_NO + N' đã được sử dụng để tạm ứng (đã bắn bút toán tạm ứng vào core)' ErrorDesc

			RETURN '-1'

		END

		UPDATE CM_ACCOUNT_PAY SET ACC_NAME =@p_ACC_NAME, ACC_NUM =@p_ACC_NO,EDITOR_DT = GETDATE(), EDITOR_ID =@p_EDITOR_ID,AUTH_STATUS ='U' WHERE REF_ID =@p_USER_ID

		--- UPDATE TOAN BO CAC HACH TOAN CO SU DUNG TAI KHOAN TAM UNG

		--UPDATE TR_REQ_ADVANCE_PAYMENT SET RECEIVER_DEBIT =@p_ACC_NO WHERE REF_ID =@p_USER_ID AND REQ_TYPE ='I'

		--UPDATE TR_REQ_PAY_ENTRIES SET ACCT= @p_ACC_NO WHERE REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REF_ID =@p_USER_ID AND REQ_TYPE ='I')

	END

	ELSE

	BEGIN

		--KIEM TRA XEM DA TON TAI TAM UNG NOI BO NAO SU DUNG SO TAI KHOAN TAM UNG CUA NHAN VIEN NAY HAY KHONG

		IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE AUTH_STATUS_KT ='A'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' ACC_ID, N'Tài khoản ' + @p_ACC_NO + N' đã được sử dụng để hạch toán (đã bắn bút toán tạm ứng vào core)' ErrorDesc

			RETURN '-1'

		END

		ELSE

		BEGIN

			UPDATE CM_ACCOUNT SET ACC_NAME =@p_ACC_NAME,AUTH_STATUS='U', TK_GL =@p_TK_GL,TK_GL_NAME =@p_TK_GL_NAME WHERE ACC_NO =@p_ACC_NO

		END

	END

	-- UPDATE CHUNG

	UPDATE CM_ACCOUNT SET TK_GL =@p_TK_GL, TK_GL_NAME = @p_TK_GL_NAME WHERE ACC_NO =@p_ACC_NO

COMMIT TRANSACTION

SELECT '0' as Result, ''  ACC_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, '' ErrorDesc

		RETURN '-1'

END

¿

ALTER PROC [dbo].[CM_ACCOUNT_PAY_Ins]

@p_USER_ID VARCHAR(15) = NULL,

@p_ACC_TYPE VARCHAR(150) = NULL,

@p_ACC_NO VARCHAR(150) = NULL,

@p_ACC_NAME VARCHAR(150) = NULL,

@p_TK_GL VARCHAR(100) = NULL,

@p_TK_GL_NAME VARCHAR(1000) = NULL,

@p_MAKER_ID VARCHAR(15)= NULL

AS

BEGIN TRANSACTION

	IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM =@p_ACC_NO) AND @p_ACC_TYPE <> 'ENTRIES' )

	BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản tạm ứng của đã tồn tại trong hệ thống' ErrorDesc

		RETURN '-1'

	END

	IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_TYPE =@p_ACC_TYPE AND REF_ID =@p_USER_ID) AND @p_ACC_TYPE <> 'ENTRIES' )

	BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản tạm ứng của nhân viên ' +@p_USER_ID + N' đã tồn tại trong hệ thống' ErrorDesc

		RETURN '-1'

	END

	IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_TYPE =@p_ACC_TYPE AND REF_ID =@p_USER_ID) AND @p_ACC_TYPE = 'ENTRIES' )

	BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản hạch toán ' + @p_ACC_NO + N' đã tồn tại trong hệ thống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_TK_GL ='' OR @p_TK_GL IS NULL)

	BEGIN

	ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản GL không được phép để trống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_TK_GL_NAME ='' OR @p_TK_GL_NAME IS NULL)

	BEGIN

	ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tên tài khoản GL không được phép để trống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_ACC_NO ='' OR @p_ACC_NO IS NULL)

	BEGIN

	ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Số tài khoản không được phép để trống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_ACC_NAME ='' OR @p_ACC_NAME IS NULL)

	BEGIN

	ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, N'Tên tài khoản không được phép để trống' ErrorDesc

		RETURN '-1'

	END

	IF(@p_ACC_TYPE ='ADV_PAY')

	BEGIN

		INSERT INTO CM_ACCOUNT_PAY (REF_ID,ACC_TYPE,ACC_NUM,ACC_NAME,FUNTION,TK_GL,CREATE_DT,MAKER_ID,EDITOR_ID,EDITOR_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,TK_GL_NAME) 

		VALUES (@p_USER_ID,@p_ACC_TYPE,@p_ACC_NO,@p_ACC_NAME,'I',@p_TK_GL,GETDATE(),@p_MAKER_ID,NULL,NULL,'U',NULL,NULL,@p_TK_GL_NAME)

		-- THEM VAO BANG TAI KHOAN DUNG DE HACH TOAN

		IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@p_ACC_NO))

		BEGIN

			INSERT INTO CM_ACCOUNT(ACC_NO,ACC_NAME,TK_GL,TK_GL_NAME,MAKER_ID,CREATE_DT,AUTH_STATUS,EDITOR_ID,EDITOR_DT,CHECKER_ID,APPROVE_DT) 

			VALUES (@p_ACC_NO,@p_ACC_NAME,@p_TK_GL,@p_TK_GL_NAME,@p_MAKER_ID,GETDATE(),'U',NULL,NULL,NULL,NULL)

		END

		ELSE

		BEGIN

			UPDATE CM_ACCOUNT SET TK_GL =@p_TK_GL, TK_GL_NAME = @p_TK_GL_NAME WHERE ACC_NO =@p_ACC_NO

		END

	END

	ELSE

	BEGIN

		IF(EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@p_ACC_NO))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' ACC_ID, N'Tài khoản hạch toán đã tồn tại trong hệ thống' ErrorDesc

			RETURN '-1'

		END

		ELSE

		BEGIN

			INSERT INTO CM_ACCOUNT(ACC_NO,ACC_NAME,TK_GL,TK_GL_NAME,MAKER_ID,CREATE_DT,AUTH_STATUS,EDITOR_ID,EDITOR_DT,CHECKER_ID,APPROVE_DT) 

			VALUES (@p_ACC_NO,@p_ACC_NAME,@p_TK_GL,@p_TK_GL_NAME,@p_MAKER_ID,GETDATE(),'U',NULL,NULL,NULL,NULL)

		END

	END

COMMIT TRANSACTION

SELECT '0' as Result, ''  ACC_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ACC_ID, '' ErrorDesc

		RETURN '-1'

END

¿

ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_KT_Upd]

@p_REQ_PAY_ID	varchar(15)= NULL,

@p_REQ_PAY_CODE	varchar(50)	= NULL,

@p_REQ_DT VARCHAR(20)= NULL,

@p_BRANCH_ID	varchar(15)	= NULL,

@p_DEP_ID	varchar(15)	= NULL,

@p_REQ_REASON	nvarchar(MAX)	= NULL,

@p_REQ_TYPE	varchar(15)	= NULL,

@P_REQ_ENTRIES nvarchar(MAX)	= NULL,

@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,

@p_REF_ID	varchar(15)	= NULL,

@p_RECEIVER_PO	nvarchar(250)	= NULL,

@p_REQ_PAY_TYPE	varchar(15)	= NULL,

@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,

@p_REQ_AMT	decimal(18, 0)	= NULL,

@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,

@p_MAKER_ID	varchar(15)	= NULL,

@p_CREATE_DT	varchar(25)	= NULL,

@p_EDITOR_ID	varchar(15)	= NULL,

@p_AUTH_STATUS	varchar(1)	= NULL,

@p_CHECKER_ID	varchar(15)	= NULL,

@p_APPROVE_DT	varchar(25)	= NULL,

@p_CREATE_DT_KT	varchar(25)	= NULL,

@p_MAKER_ID_KT	varchar(15)	= NULL,

@p_AUTH_STATUS_KT	varchar(1)	= NULL,

@p_CHECKER_ID_KT	varchar(1)	= NULL,

@p_APPROVE_DT_KT  varchar(25)= null,

@p_CONFIRM_NOTE	nvarchar(500)	= NULL,

@p_BRANCH_CREATE	varchar(15)	= NULL,

@p_NOTES	varchar(15)	= NULL,

@p_RECORD_STATUS	varchar(1)	= NULL,

@p_TRANSFER_MAKER	nvarchar(50)	= NULL,

@p_TRANSFER_DT	varchar(25)	= NULL,

@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,

@p_PROCESS	varchar(15)	= NULL,

@p_PAY_PHASE VARCHAR(15) = NULL,

@p_XMP_TEMP XML = NULL,

@p_XMP_TEMP_2 XML = NULL

AS

--Validation is here

IF ((SELECT AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) ='R')

	BEGIN

		SELECT '-1' Result,'' REQ_PAY_ID,N'Phiếu đề nghị thanh toán đang được trả về đơn vị. Vui lòng chờ đơn vị cập nhật thông tin và duyệt lại!' ErrorDesc

		RETURN '-1'

	END

	DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(500), @AMT decimal(18,2),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),

		@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000),@GL_CODE VARCHAR(100)

	DECLARE @VAT decimal(18, 0) =NULL, @TRANS_NO nvarchar(50)=NULL, @TRANS_DT VARCHAR(20)=NULL,@INVOICE_SIGN nvarchar(50) = NULL,@INVOICE_NO_SIGN nvarchar(50)=null,

		@INVOICE_NO nvarchar(50) = NULL,@INVOICE_DT VARCHAR(20) = NULL,@SELLER nvarchar(250) = NULL,@TAX_NO nvarchar(15) = NULL,@GOODS_NAME nvarchar(250) = NULL,

		@PRICE decimal(18, 0) = NULL,@TAX decimal(18, 0) = NULL,@NOTES NVARCHAR(MAX) = NULL,@VAT_RATE decimal(18,2),

		@ACC_NO VARCHAR(25),@ACC_NAME NVARCHAR(250),@ISSUED_BY NVARCHAR(250),@ISSUED_DT VARCHAR(20),@RATE DECIMAL(18,2),@PRICE_KT DECIMAL(18,2) =0,@TOTAL_AMT_KT DECIMAL(18,2) =0,@VAT_KT DECIMAL(18,2) =0,

		@TYPE_VAT VARCHAR(15),@TYPE_FUNC VARCHAR(15)

DECLARE @hdoc INT;

	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;

	DECLARE XmlData CURSOR FOR

	SELECT *

	FROM

	OPENXML(@hdoc, '/Root/XmlData', 2)

	WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),

	BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000),GL_CODE VARCHAR(100))

	OPEN XmlData;

	---

	DECLARE XmlInvoice CURSOR FOR

	SELECT *

	FROM

	OPENXML(@hdoc, '/Root/XmlInvoice', 2)

	WITH(TRANS_NO nvarchar(50),TRANS_DT VARCHAR(20),INVOICE_SIGN nvarchar(50),INVOICE_NO_SIGN nvarchar(50),

		 INVOICE_NO nvarchar(50),INVOICE_DT VARCHAR(20) ,SELLER nvarchar(250),TAX_NO nvarchar(15),GOODS_NAME nvarchar(250) ,

		 PRICE decimal(18,2),TAX decimal(18, 2),VAT decimal(18,2),NOTE NVARCHAR(MAX),VAT_RATE decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),PRICE_KT DECIMAL(18,2),VAT_KT DECIMAL(18,2),TOTAL_AMT_KT DECIMAL(18,2),TYPE_VAT VARCHAR(15), TYPE_FUNC VARCHAR(15))

		OPEN XmlInvoice;

	DECLARE @INDEX INT  =0, @INDEX_IV INT =0

	BEGIN TRANSACTION

		-- KHAI BAO CAC BUOC DUYET- XAC NHAN

		DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT

		SET @LEVEL_JOB =(SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT AND STATUS_JOB='C')

		SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1

		----

		--IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND MAKER_ID_KT IS NULL))

		IF(@p_XMP_TEMP IS NOT NULL)

		BEGIN

			UPDATE TR_REQ_PAYMENT SET 

			AUTH_STATUS_KT ='U', CREATE_DT_KT = GETDATE(),MAKER_ID_KT =@p_MAKER_ID_KT,CHECKER_ID_KT=NULL,APPROVE_DT_KT = NULL, CONFIRM_NOTE=@p_CONFIRM_NOTE

			WHERE REQ_PAY_ID =@p_REQ_PAY_ID

			DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID

			DELETE FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID= @p_REQ_PAY_ID

		IF @@Error <> 0 GOTO ABORT

		DECLARE @DR_CR_NAME NVARCHAR(50),@ACC_NAME_FN NVARCHAR(500)

		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE

		WHILE @@fetch_status=0 BEGIN

			SET @INDEX = @INDEX +1

			SET @GL_CODE = RIGHT(@GL_CODE,9)

			IF(@DR_CR='D')

			BEGIN

				SET @DR_CR_NAME =N'Nợ'

			END

			ELSE

			BEGIN

				SET @DR_CR_NAME =N'Có'

			END

			SET @ACC_NAME_FN =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)

			IF((@ACCT_NAME IS NULL OR @ACCT_NAME ='' OR @ACC_NAME_FN IS NULL OR @ACC_NAME_FN ='' ) OR (ISNULL(@ACCT_NAME,'') <> ISNULL(@ACC_NAME_FN,'')))

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlData;

				DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc

				RETURN '-1'

			END

			IF(@DR_CR IS NULL OR @DR_CR ='')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlData;

				DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản nợ không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			IF(@BRANCH_ID IS NULL OR @BRANCH_ID ='')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlData;

				DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Đơn vị nhận không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			IF(@ACCT IS NULL OR @ACCT ='')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlData;

				DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản có không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			IF(@TRN_DESC IS NULL OR @TRN_DESC ='')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlData;

				DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Diễn giải hạch toán không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))

			BEGIN

				IF(@DR_CR ='C' AND ISNULL(@AMT,0) <> (SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))

				BEGIN

					ROLLBACK TRANSACTION

					CLOSE XmlData;

					DEALLOCATE XmlData;

					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Số tiền hạch toán có của tài khoản: '+@ACCT + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###')  ErrorDesc

					RETURN '-1'

				END

			END

			DECLARE @p_ET_ID VARCHAR(15);

			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @p_ET_ID OUT;

			IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;

			INSERT INTO TR_REQ_PAY_ENTRIES (REQ_PAY_DT_ID,REQ_PAY_ID,ENTRY_PAIR,DR_CR,DR_CR_NAME,ACCT,ACCT_NAME,AMT,CURRENCY,EXC_RATE,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT)

			VALUES (@p_ET_ID,@p_REQ_PAY_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,@TRN_DESC,GETDATE(),@p_MAKER_ID_KT)

			-- NEU CHUA CO TAI KHOAN THI THEM VO CM_ACCOUNT

			IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@ACCT))

			BEGIN

				INSERT INTO CM_ACCOUNT (ACC_NO, ACC_NAME, TK_GL, TK_GL_NAME, MAKER_ID, CHECKER_ID,CREATE_DT) VALUES

				(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin',GETDATE())

			END

			ELSE

			BEGIN

				IF(@GL_CODE IS NOT NULL AND @GL_CODE <> '')

					BEGIN

						UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE)

						WHERE ACC_NO=@ACCT

				END

				--UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE), CREATE_DT = GETDATE()

				--WHERE ACC_NO=@ACCT

			END

			---

			IF @@error<>0 GOTO ABORT;

		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE

		END;

	CLOSE XmlData;

	DEALLOCATE XmlData;

	-- UPDATE INVOICE

	--Insert XmlData

		FETCH NEXT FROM XmlInvoice  INTO @TRANS_NO , @TRANS_DT  ,@INVOICE_SIGN ,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,

		@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC

		WHILE @@fetch_status=0 BEGIN

			SET @INDEX_IV = @INDEX_IV+1

			IF(LEN(@INVOICE_NO) >7)

				BEGIN

					ROLLBACK TRANSACTION

					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' không được lớn hơn 7 kí tự' ErrorDesc

					RETURN '-1'

				END	

			  IF(LEN(@INVOICE_NO) <7)

				BEGIN

					ROLLBACK TRANSACTION

					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' phải đủ 7 kí tự' ErrorDesc

					RETURN '-1'

				END	

				IF(@TYPE_FUNC ='HC')

				BEGIN

					IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN AND INVOICE_SIGN =@INVOICE_SIGN AND 

					TAX_NO =@TAX_NO AND TAX =@TAX AND TYPE_FUNC ='HC' AND AUTH_STATUS <>'D' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'E')))

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID,N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc

						RETURN '-1'

					END	

				END

				IF(@TYPE_FUNC ='KT')

				BEGIN

					IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN 

											AND INVOICE_SIGN =@INVOICE_SIGN AND TAX_NO =@TAX_NO AND TAX =@TAX AND TYPE_FUNC ='KT' AND AUTH_STATUS <>'D' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'E')))

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID,N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc

						RETURN '-1'

					END	

				END

			DECLARE @p_REQ_INV_ID VARCHAR(15);

			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE', @p_REQ_INV_ID OUT;

			IF @p_REQ_INV_ID='' OR @p_REQ_INV_ID IS NULL GOTO ABORT;

			INSERT INTO TR_REQ_PAY_INVOICE(REQ_PAYDT_ID,REQ_PAY_ID,TRANS_NO,TRANS_DT,INVOICE_SIGN,INVOICE_NO,INVOICE_DT,SELLER,TAX_NO,GOODS_NAME,PRICE,TAX,VAT,NOTE,

		MAKER_ID,CREATE_DT,EDITOR_ID,AUTH_STATUS,CHECKER_ID,APPROVE_DT,

		CREATE_DT_KT,MAKER_ID_KT,AUTH_STATUS_KT,CHECKER_ID_KT,APPROVE_DT_KT,RECORD_STATUS,INVOICE_NO_SIGN,VAT_RATE,CURRENCY,RATE,PRICE_KT ,VAT_KT,TOTAL_AMT_KT,TYPE_VAT,TYPE_FUNC) 

		VALUES (@p_REQ_INV_ID,@p_REQ_PAY_ID , @TRANS_NO , CONVERT(DATE,@TRANS_DT,103)  ,@INVOICE_SIGN ,@INVOICE_NO ,CONVERT(DATE,@INVOICE_DT,103) ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,

		@p_MAKER_ID,GETDATE(),NULL,'U',NULL,NULL,GETDATE(),@p_MAKER_ID_KT,'U',NULL,NULL,'1',@INVOICE_NO_SIGN,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC)

		IF @@error<>0 GOTO ABORT;

		FETCH NEXT FROM XmlInvoice

		INTO @TRANS_NO , @TRANS_DT,@INVOICE_SIGN,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,

		@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC

		END;

		CLOSE XmlInvoice;

		DEALLOCATE XmlInvoice;

		--- Luu log chinh sua

		INSERT INTO TR_REQ_PAY_INVOICE_LOG SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID

		-----

	--- BAT DAU CAP NHAT XAC DINH CAC BUOC DUYET SAU KHI KE TOAN CAP NHAT THONG TIN

	END

	COMMIT TRANSACTION

	IF(@p_XMP_TEMP IS NULL)

			BEGIN

				IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'S'))

				BEGIN

					SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được được đề xuất từ chối! Vui lòng đợi KSV xử lý phiếu' ErrorDesc

					RETURN '-1'

				END

				IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'P'))

				BEGIN

					SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được gửi phê  duyệt thành công trước đó! Vui lòng đợi KSV xử lý phiếu' ErrorDesc

					RETURN '-1'

				END

				-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP

				IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

				BEGIN

					DECLARE  @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)

					SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C'),0)

					SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D'),0)

					IF(@SUM_CR <> @SUM_DR)

					BEGIN

					--ROLLBACK TRANSACTION

					--CLOSE XmlData;

					--DEALLOCATE XmlData;

						SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có' ErrorDesc

						RETURN '-1'

					END

				END

				UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT

				UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)

				-- CAP NHAT TINH TRANG DUYET KE TOAN LA DANG XU LY

				UPDATE TR_REQ_PAYMENT SET AUTH_STATUS_KT='P',CREATE_DT_KT = GETDATE() WHERE REQ_PAY_ID=@p_REQ_PAY_ID

				INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

				VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID_KT,GETDATE(), N'Giao dịch viên gửi phê duyệt',N'Giao dịch viên cập nhật thông tin')

				SELECT '4' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc

				RETURN '4'

END

SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		CLOSE XmlData;

		DEALLOCATE XmlData;

		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc

		RETURN '-1'

End

¿

ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_KT_Upd]

@p_REQ_PAY_ID	varchar(15)= NULL,

@p_REQ_PAY_CODE	varchar(50)	= NULL,

@p_REQ_DT VARCHAR(20)= NULL,

@p_BRANCH_ID	varchar(15)	= NULL,

@p_DEP_ID	varchar(15)	= NULL,

@p_REQ_REASON	nvarchar(MAX)	= NULL,

@p_REQ_TYPE	varchar(15)	= NULL,

@P_REQ_ENTRIES nvarchar(MAX)	= NULL,

@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,

@p_REF_ID	varchar(15)	= NULL,

@p_RECEIVER_PO	nvarchar(250)	= NULL,

@p_RECEIVER_DEBIT	nvarchar(250)	= NULL,

@p_REQ_PAY_TYPE	varchar(15)	= NULL,

@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,

@p_REQ_AMT	decimal(18, 0)	= NULL,

@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,

@p_MAKER_ID	varchar(15)	= NULL,

@p_CREATE_DT	varchar(25)	= NULL,

@p_EDITOR_ID	varchar(15)	= NULL,

@p_AUTH_STATUS	varchar(1)	= NULL,

@p_CHECKER_ID	varchar(15)	= NULL,

@p_APPROVE_DT	varchar(25)	= NULL,

@p_CREATE_DT_KT	varchar(25)	= NULL,

@p_MAKER_ID_KT	varchar(15)	= NULL,

@p_AUTH_STATUS_KT	varchar(1)	= NULL,

@p_CHECKER_ID_KT	varchar(1)	= NULL,

@p_APPROVE_DT_KT  varchar(25)= null,

@p_CONFIRM_NOTE	nvarchar(500)	= NULL,

@p_BRANCH_CREATE	varchar(15)	= NULL,

@p_NOTES	varchar(15)	= NULL,

@p_RECORD_STATUS	varchar(1)	= NULL,

@p_TRANSFER_MAKER	nvarchar(50)	= NULL,

@p_TRANSFER_DT	varchar(25)	= NULL,

@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,

@p_PROCESS	varchar(15)	= NULL,

@p_PAY_PHASE VARCHAR(15)= NULL,

@p_DVDM_ID VARCHAR(15) = NULL,

@p_RATE DECIMAL(18,0)= 0,

@p_XMP_TEMP XML = NULL

AS

--Validation is here

/*

END 

*/

DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(500), @AMT decimal(18,2),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),

	@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000),@GL_CODE VARCHAR(10)

DECLARE @hdoc INT;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;

DECLARE XmlData CURSOR FOR

SELECT *

FROM

	OPENXML(@hdoc, '/Root/XmlData', 2)

	WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),

	BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000), GL_CODE VARCHAR(10))

	OPEN XmlData;

	DECLARE @INDEX INT  =0

BEGIN TRANSACTION

		-- KHAI BAO CAC BUOC DUYET- XAC NHAN

		DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT

		SET @LEVEL_JOB =(SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT AND TYPE_JOB ='XL')

		SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1

		----

		--IF(EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND MAKER_ID_KT IS NULL))

		IF(@p_XMP_TEMP IS NOT NULL)

		BEGIN

			UPDATE TR_REQ_ADVANCE_PAYMENT SET 

			AUTH_STATUS_KT ='U', CREATE_DT_KT = GETDATE(),MAKER_ID_KT =@p_MAKER_ID_KT,CHECKER_ID_KT=NULL,APPROVE_DT_KT = NULL, CONFIRM_NOTES=@p_CONFIRM_NOTE

			WHERE REQ_PAY_ID =@p_REQ_PAY_ID

			DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID

		IF @@Error <> 0 GOTO ABORT

		DECLARE @DR_CR_NAME NVARCHAR(50)

		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE

		WHILE @@fetch_status=0 BEGIN

			SET @INDEX = @INDEX +1

			IF(@DR_CR='D')

			BEGIN

				SET @DR_CR_NAME =N'Nợ'

			END

			ELSE

			BEGIN

				SET @DR_CR_NAME =N'Có'

			END

			SET @ACCT_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)

			IF(@ACCT_NAME IS NULL OR @ACCT_NAME ='')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlData;

				DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc

				RETURN '-1'

			END

			IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))

			BEGIN

				IF(@DR_CR ='C' AND ISNULL(@AMT,0) <> (SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))

				BEGIN

					ROLLBACK TRANSACTION

					CLOSE XmlData;

					DEALLOCATE XmlData;

					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Số tiền hạch toán có của tài khoản: '+@ACCT + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###')  ErrorDesc

					RETURN '-1'

				END

			END

			--IF(@DR_ACCT IS NULL OR @DR_ACCT ='')

			--BEGIN

			--	ROLLBACK TRANSACTION

			--	CLOSE XmlData;

			--	DEALLOCATE XmlData;

			--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản có không được phép để trống' ErrorDesc

			--	RETURN '-1'

			--END

			--IF(@TRN_DESC IS NULL OR @TRN_DESC ='')

			--BEGIN

			--	ROLLBACK TRANSACTION

			--	CLOSE XmlData;

			--	DEALLOCATE XmlData;

			--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Diễn giải hạch toán không được phép để trống' ErrorDesc

			--	RETURN '-1'

			--END

			DECLARE @p_ET_ID VARCHAR(15);

			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @p_ET_ID OUT;

			IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;

			INSERT INTO TR_REQ_PAY_ENTRIES (REQ_PAY_DT_ID,REQ_PAY_ID,ENTRY_PAIR,DR_CR, DR_CR_NAME,ACCT,ACCT_NAME,AMT,CURRENCY,EXC_RATE,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT)

			VALUES (@p_ET_ID,@p_REQ_PAY_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,@TRN_DESC,GETDATE(),@p_MAKER_ID_KT)

			-- NEU CHUA CO TAI KHOAN THI THEM VO CM_ACCOUNT

			IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@ACCT))

			BEGIN

				INSERT INTO CM_ACCOUNT (ACC_NO, ACC_NAME, TK_GL, TK_GL_NAME, MAKER_ID, CHECKER_ID) VALUES

				(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin')

			END

			ELSE

			BEGIN

				IF(@GL_CODE IS NOT NULL AND @GL_CODE <> '')

					BEGIN

						UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE)

						WHERE ACC_NO=@ACCT

				END

			END

			IF @@error<>0 GOTO ABORT;

		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE

		END;

	CLOSE XmlData;

	DEALLOCATE XmlData;

	END

COMMIT TRANSACTION

--- BAT DAU CAP NHAT XAC DINH CAC BUOC DUYET SAU KHI KE TOAN CAP NHAT THONG TIN

	IF(@p_XMP_TEMP IS NULL)

	BEGIN

		-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP

		IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

		BEGIN

			DECLARE  @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)

			SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C'),0)

			SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D'),0)

			IF(@SUM_CR<>@SUM_DR)

			BEGIN

			--ROLLBACK TRANSACTION

			--CLOSE XmlData;

			--DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có' ErrorDesc

				RETURN '-1'

			END

		END

		UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT

		UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)

		-- CAP NHAT TINH TRANG VE DANG XU LY

		UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS_KT='P',CREATE_DT_KT =GETDATE() WHERE REQ_PAY_ID=@p_REQ_PAY_ID

		INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

				   VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID_KT,GETDATE(), N'Giao dịch viên gửi phê duyệt phiếu',N'Giao dịch viên cập nhật thông tin')

		SELECT '4' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N'đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc

		RETURN '4'

	END

SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		CLOSE XmlData;

		DEALLOCATE XmlData;

		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc

		RETURN '-1'

End

¿

ALTER PROCEDURE [dbo].[TR_REQ_PAY_TRANSFER_V1_Upd]

@p_TRANSFER_ID	varchar(15),

@p_TRANSFER_CODE varchar(15),

@p_REQ_PAY_ID	varchar(15)	,

@p_MAKER_ID	varchar(15)	,

@p_CREATE_DT	VARCHAR(25)	,

@p_AUTH_STATUS	varchar(15)	,

@p_CHECKER_ID	varchar(15)	,

@p_APPROVE_DT	datetime,	

@p_FR_USER	varchar(15)	,

@p_TO_USER	varchar(15)	,

@p_REASON	nvarchar(4000),	

@p_NOTES	nvarchar(4000)	,

@p_DESCRIPTION	nvarchar(4000),	

@p_CONFIRM_NOTES	nvarchar(4000)	,

@p_FR_ACC VARCHAR(50),

@p_TO_ACC VARCHAR(50),

@p_MAKER_ID_KT	varchar(15),

@P_LISTASSET XML,

@P_LISTASSET_v1 XML

AS

	IF(@p_FR_USER ='' or @p_FR_USER IS NULL)

	BEGIN

			--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Người cho phiếu không được để trống' AS ErrorDesc 

			RETURN '-1'

	END

	IF(@p_TO_USER ='' or @p_TO_USER IS NULL)

	BEGIN

			--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Người nhận phiếu không được để trống' AS ErrorDesc 

			RETURN '-1'

	END

	IF(@p_CONFIRM_NOTES ='' or @p_CONFIRM_NOTES IS NULL)

	BEGIN

			--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Lý do điều chuyển không được để trống' AS ErrorDesc 

			RETURN '-1'

	END

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET

	DECLARE TransferDetail CURSOR LOCAL FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/TransferDetail',2)

	WITH 

	(

		TRAN_DT_ID	varchar(15)				 ,

		TRANSFER_ID	varchar(15)				 ,

		REQ_PAY_ID	varchar(15)				 ,

		TOTAL_AMT	decimal(18, 0)			 ,

		TOAL_AMT_DO	decimal(18, 0)			 ,

		TOTAL_AMT_REMAIN	decimal(18, 0)	 ,

		TOTAL_AMT_REAL	decimal(18, 0)		 ,

		TOTAL_AMT_REMAIN_FN	decimal(18, 0)	 ,

		NOTES	nvarchar(4000)				 ,

		REASON	nvarchar(4000)				 

	)

	OPEN TransferDetail

	PRINT 'PASS KHOI TAO'

	BEGIN TRANSACTION

		--insert master

		--DECLARE @l_CONTRACT_ID VARCHAR(15)

		UPDATE TR_REQ_PAY_TRANSFER SET REASON =@p_REASON, CONFIRM_NOTES =@p_CONFIRM_NOTES WHERE TRANSFER_ID =@p_TRANSFER_ID

		IF @@Error <> 0 GOTO ABORT

		PRINT 'INSERT MASTER SUCCESS'

		Declare 

		@l_TRAN_DT_ID	varchar(15)				 ,

		@l_TRANSFER_ID	varchar(15)				 ,

		@l_REQ_PAY_ID	varchar(15)				 ,

		@l_TOTAL_AMT	decimal(18, 0)			 ,

		@l_TOAL_AMT_DO	decimal(18, 0)			 ,

		@l_TOTAL_AMT_REMAIN	decimal(18, 0)	 ,

		@l_TOTAL_AMT_REAL	decimal(18, 0)		 ,

		@l_TOTAL_AMT_REMAIN_FN	decimal(18, 0)	 ,

		@l_NOTES	nvarchar(4000)				 ,

		@l_REASON	nvarchar(4000)				 

		--Insert detail

		DELETE FROM TR_REQ_PAY_TRANSFER_DT WHERE TRANSFER_ID =@p_TRANSFER_ID OR TRANSFER_ID IS NULL OR TRANSFER_ID =''

		FETCH NEXT FROM TransferDetail INTO @l_TRAN_DT_ID,@l_TRANSFER_ID,@l_REQ_PAY_ID,@l_TOTAL_AMT,@l_TOAL_AMT_DO,@l_TOTAL_AMT_REMAIN,@l_TOTAL_AMT_REAL,@l_TOTAL_AMT_REMAIN_FN,@l_NOTES,@l_REASON

		-- Validate lưới chi tiết rỗng hay không

		IF (@@FETCH_STATUS < 0)

		BEGIN

			ROLLBACK TRANSACTION

			CLOSE TransferDetail;

			DEALLOCATE TransferDetail;

			SELECT '-1' as Result, '' AS TRANSFER_ID, N'Thông tin phiếu đề nghị tạm ứng không được để trống' ErrorDesc

			RETURN '-1'

		END

		DECLARE @IDX INT = 0

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			SET @IDX = @IDX + 1

			-- Kiểm tra nếu PĐN tạm ứng đang được điều chuyển ở một giao dịch khác thì không cho tạo

			IF EXISTS (SELECT A.REQ_PAY_ID FROM TR_REQ_PAY_TRANSFER_DT A

						LEFT JOIN TR_REQ_PAY_TRANSFER B ON A.TRANSFER_ID = B.TRANSFER_ID AND A.REQ_PAY_ID =@l_REQ_PAY_ID

						WHERE B.TRANSFER_ID <> @p_TRANSFER_ID AND B.AUTH_STATUS = 'U')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE TransferDetail;

				DEALLOCATE TransferDetail;

				SELECT '-1' as Result, '' AS TRANSFER_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@IDX) + N' Phiếu đề nghị tạm ứng đang được điều chuyển ở một giao dịch khác' ErrorDesc

				RETURN '-1'

			END

			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_TRANSFER_DT', @l_TRAN_DT_ID out

			IF @l_TRAN_DT_ID='' OR @l_TRAN_DT_ID IS NULL GOTO ABORT

			INSERT INTO TR_REQ_PAY_TRANSFER_DT(TRAN_DT_ID, TRANSFER_ID,REQ_PAY_ID,TOTAL_AMT, TOAL_AMT_DO, TOTAL_AMT_REMAIN, TOTAL_AMT_REAL,  TOTAL_AMT_REMAIN_FN, NOTES, REASON)

			VALUES ( @l_TRAN_DT_ID,@p_TRANSFER_ID,@l_REQ_PAY_ID,@l_TOTAL_AMT,@l_TOAL_AMT_DO,@l_TOTAL_AMT_REMAIN,@l_TOTAL_AMT_REAL,@l_TOTAL_AMT_REMAIN_FN,@l_NOTES,@l_REASON)

			IF @@Error <> 0 GOTO ABORT			

		-- next Group_Id

			FETCH NEXT FROM TransferDetail INTO @l_TRAN_DT_ID,@l_TRANSFER_ID,@l_REQ_PAY_ID,@l_TOTAL_AMT,@l_TOAL_AMT_DO,@l_TOTAL_AMT_REMAIN,@l_TOTAL_AMT_REAL,@l_TOTAL_AMT_REMAIN_FN,@l_NOTES,@l_REASON

		END

	DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(500), @AMT decimal(18,2),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),

	@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000), @GL_CODE VARCHAR(10)

	DECLARE @hdoc2 INT;

	EXEC sp_xml_preparedocument @hdoc2 OUTPUT, @P_LISTASSET_v1;

	DECLARE XmlData CURSOR LOCAL FOR

	SELECT *

	FROM

	OPENXML(@hdoc2, '/Root/XmlData', 2)

	WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),

	BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000),GL_CODE VARCHAR(10))

	OPEN XmlData;

		DECLARE @DR_CR_NAME NVARCHAR(50), @INDEX INT =0, @ACC_NAME_FN VARCHAR(500)

		--

		DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID

		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE

		WHILE @@fetch_status=0 BEGIN

			SET @INDEX= @INDEX+1

			IF(@DR_CR='D')

			BEGIN

				SET @DR_CR_NAME =N'Nợ'

			END

			ELSE

			BEGIN

				SET @DR_CR_NAME =N'Có'

			END

			IF(@DR_CR IS NULL OR @DR_CR ='')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlData;

				DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản nợ không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			IF(@ACCT IS NULL OR @ACCT ='')

			BEGIN

				ROLLBACK TRANSACTION

				--CLOSE XmlData;

				--DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản có không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			IF(@TRN_DESC IS NULL OR @TRN_DESC ='')

			BEGIN

				ROLLBACK TRANSACTION

				--CLOSE XmlData;

				--DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Diễn giải hạch toán không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			IF(@BRANCH_ID IS NULL OR @BRANCH_ID ='')

			BEGIN

				ROLLBACK TRANSACTION

				--CLOSE XmlData;

				--DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Đơn vị không được phép để trống' ErrorDesc

				RETURN '-1'

			END

			SET @ACC_NAME_FN =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)

			IF(@ACC_NAME_FN IS NULL OR @ACC_NAME_FN ='')

			BEGIN

					SET @ACC_NAME_FN =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT_PAY WHERE ACC_NUM =@ACCT)

			END

			IF((@ACCT_NAME IS NULL OR @ACCT_NAME ='' OR @ACC_NAME_FN IS NULL OR @ACC_NAME_FN ='' ) OR (ISNULL(@ACCT_NAME,'') <> ISNULL(@ACC_NAME_FN,'')))

			BEGIN

				ROLLBACK TRANSACTION

				--CLOSE XmlData;

				--DEALLOCATE XmlData;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc

				RETURN '-1'

			END

			DECLARE @p_ET_ID VARCHAR(15);

			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @p_ET_ID OUT;

			IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;

			INSERT INTO TR_REQ_PAY_ENTRIES (REQ_PAY_DT_ID,REQ_PAY_ID,ENTRY_PAIR,DR_CR,DR_CR_NAME,ACCT,ACCT_NAME,AMT,CURRENCY,EXC_RATE,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT)

			VALUES (@p_ET_ID,@p_TRANSFER_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,UPPER(@TRN_DESC),GETDATE(),@p_MAKER_ID_KT)

			-- NEU CHUA CO TAI KHOAN THI THEM VO CM_ACCOUNT

			IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@ACCT))

			BEGIN

				INSERT INTO CM_ACCOUNT (ACC_NO, ACC_NAME, TK_GL, TK_GL_NAME, MAKER_ID, CHECKER_ID) VALUES

				(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin')

			END

			ELSE

			BEGIN

				IF(@GL_CODE IS NOT NULL AND @GL_CODE <> '')

				BEGIN

					UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE)

					WHERE ACC_NO=@ACCT

				END

			END

			IF @@error<>0 GOTO ABORT;

		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE

		END;

	CLOSE XmlData;

	DEALLOCATE XmlData;	

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID))

			BEGIN

				DECLARE  @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)

				SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID AND DR_CR ='C'),0)

				SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID AND DR_CR ='D'),0)

				IF(@SUM_CR <> @SUM_DR)

				BEGIN

					ROLLBACK TRANSACTION

					SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có: ' + FORMAT(@SUM_DR,'#,###') ErrorDesc

					RETURN '-1'

				END

	END

COMMIT TRANSACTION

SELECT '0' as Result, @p_TRANSFER_ID  TRANSFER_ID, N'Giao dịch điều chuyển tạm ứng có ID: '+ @p_TRANSFER_ID +N' đã được tạo bản nháp thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		CLOSE AssetDetail

		DEALLOCATE AssetDetail

		CLOSE PaymentDetail

		DEALLOCATE PaymentDetail

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' TRANSFER_ID, '' ErrorDesc

		RETURN '-1'

End

¿

ALTER PROCEDURE [dbo].[TR_REQ_PAY_TRANSFER_Ins]

@p_TRANSFER_ID	varchar(15) = NULL,

@p_TRANSFER_CODE varchar(15),

@p_REQ_PAY_ID	varchar(15)	,

@p_MAKER_ID	varchar(15)	,

@p_CREATE_DT	VARCHAR(25)	,

@p_AUTH_STATUS	varchar(15)	,

@p_CHECKER_ID	varchar(15)	,

@p_APPROVE_DT	datetime,	

@p_FR_USER	varchar(15)	,

@p_TO_USER	varchar(15)	,

@p_REASON	nvarchar(4000),	

@p_NOTES	nvarchar(4000)	,

@p_DESCRIPTION	nvarchar(4000),	

@p_CONFIRM_NOTES	nvarchar(4000),

@p_FR_ACC VARCHAR(50),

@p_TO_ACC VARCHAR(50),

@p_MAKER_ID_KT	varchar(15),

@P_LISTASSET XML,

@P_LISTASSET_v1 XML

AS

	DECLARE @l_TRANSFER_ID_MASTER VARCHAR(15)

	IF(@p_FR_USER ='' or @p_FR_USER IS NULL)

	BEGIN

			--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Người cho phiếu không được để trống' AS ErrorDesc 

			RETURN '-1'

	END

	IF(@p_TO_USER ='' or @p_TO_USER IS NULL)

	BEGIN

			--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Người nhận phiếu không được để trống' AS ErrorDesc 

			RETURN '-1'

	END

	IF(@p_CONFIRM_NOTES ='' or @p_CONFIRM_NOTES IS NULL)

	BEGIN

			--ROLLBACK TRANSACTION

			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Lý do điều chuyển không được để trống' AS ErrorDesc 

			RETURN '-1'

	END

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET

	DECLARE TransferDetail CURSOR LOCAL FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/TransferDetail',2)

	WITH 

	(

		TRAN_DT_ID	varchar(15)				 ,

		TRANSFER_ID	varchar(15)				 ,

		REQ_PAY_ID	varchar(15)				 ,

		TOTAL_AMT	decimal(18, 0)			 ,

		TOAL_AMT_DO	decimal(18, 0)			 ,

		TOTAL_AMT_REMAIN	decimal(18, 0)	 ,

		TOTAL_AMT_REAL	decimal(18, 0)		 ,

		TOTAL_AMT_REMAIN_FN	decimal(18, 0)	 ,

		NOTES	nvarchar(4000)				 ,

		REASON	nvarchar(4000)				 

	)

	OPEN TransferDetail

	PRINT 'PASS KHOI TAO'

	BEGIN TRANSACTION

		--insert master

		EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_TRANSFER', @l_TRANSFER_ID_MASTER out

		IF @l_TRANSFER_ID_MASTER='' OR @l_TRANSFER_ID_MASTER IS NULL GOTO ABORT

		INSERT INTO TR_REQ_PAY_TRANSFER (TRANSFER_ID, TRANSFER_CODE,REQ_PAY_ID, MAKER_ID,CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, FR_USER, TO_USER, REASON, NOTES,[DESCRIPTION],CONFIRM_NOTES)

		VALUES (@l_TRANSFER_ID_MASTER, @p_TRANSFER_CODE,@p_REQ_PAY_ID, @p_MAKER_ID,GETDATE(), @p_AUTH_STATUS, @p_CHECKER_ID, NULL, @p_FR_USER, @p_TO_USER, @p_REASON, @p_NOTES,@p_DESCRIPTION,@p_CONFIRM_NOTES)

		IF @@Error <> 0 GOTO ABORT

		PRINT 'INSERT MASTER SUCCESS'

		Declare 

		@l_TRAN_DT_ID	varchar(15)				 ,

		@l_TRANSFER_ID	varchar(15)				 ,

		@l_REQ_PAY_ID	varchar(15)				 ,

		@l_TOTAL_AMT	decimal(18, 0)			 ,

		@l_TOAL_AMT_DO	decimal(18, 0)			 ,

		@l_TOTAL_AMT_REMAIN	decimal(18, 0)	 ,

		@l_TOTAL_AMT_REAL	decimal(18, 0)		 ,

		@l_TOTAL_AMT_REMAIN_FN	decimal(18, 0)	 ,

		@l_NOTES	nvarchar(4000)				 ,

		@l_REASON	nvarchar(4000)				 

		-- insert detail

		DECLARE @l_CD_ID VARCHAR(15)

		FETCH NEXT FROM TransferDetail INTO @l_TRAN_DT_ID,@p_TRANSFER_ID,@l_REQ_PAY_ID,@l_TOTAL_AMT,@l_TOAL_AMT_DO,@l_TOTAL_AMT_REMAIN,@l_TOTAL_AMT_REAL,@l_TOTAL_AMT_REMAIN_FN,@l_NOTES,@l_REASON

		-- Validate lưới chi tiết rỗng hay không

		IF (@@FETCH_STATUS < 0)

		BEGIN

			ROLLBACK TRANSACTION

			CLOSE TransferDetail;

			DEALLOCATE TransferDetail;

			SELECT '-1' as Result, '' AS TRANSFER_ID, N'Thông tin phiếu đề nghị tạm ứng không được để trống' ErrorDesc

			RETURN '-1'

		END

		DECLARE @IDX INT = 0

		WHILE @@FETCH_STATUS = 0	

		BEGIN

			SET @IDX = @IDX + 1

			-- Kiểm tra nếu PĐN tạm ứng đang được điều chuyển ở một giao dịch khác thì không cho tạo

		IF EXISTS (SELECT A.REQ_PAY_ID FROM TR_REQ_PAY_TRANSFER_DT A

						LEFT JOIN TR_REQ_PAY_TRANSFER B ON A.TRANSFER_ID = B.TRANSFER_ID AND A.REQ_PAY_ID =@l_REQ_PAY_ID

						WHERE B.AUTH_STATUS = 'U')

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE TransferDetail;

				DEALLOCATE TransferDetail;

				SELECT '-1' as Result, '' AS TRANSFER_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@IDX) + N' Phiếu đề nghị tạm ứng đang được điều chuyển ở một giao dịch khác' ErrorDesc

				RETURN '-1'

			END

			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_TRANSFER_DT', @l_TRAN_DT_ID out

			IF @l_TRAN_DT_ID='' OR @l_TRAN_DT_ID IS NULL GOTO ABORT

			INSERT INTO TR_REQ_PAY_TRANSFER_DT(TRAN_DT_ID, TRANSFER_ID,REQ_PAY_ID,TOTAL_AMT, TOAL_AMT_DO, TOTAL_AMT_REMAIN, TOTAL_AMT_REAL,  TOTAL_AMT_REMAIN_FN, NOTES, REASON)