AMS - VB

ALTER PROCEDURE dbo.rpt_LIET_KE_CHUNG_TU_VietBank 

@p_DATE VARCHAR(25), 

@p_BRANCH_ID VARCHAR(15) = '', 

@p_MAKER_ID VARCHAR(200) =NULL

AS

BEGIN

--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) + CC.DRCR_IND + CHAR(10) AS DRCR_IND,

       CHAR(10) + CC.ET_ID + CHAR(10) AS REF_NO,

       CHAR(10) + CC.ETP_ID + CHAR(10) AS RNO,

       CHAR(10) + CC.RELATED_REFERENCE + CHAR(10) AS ASSET_CODE,

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

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

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

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

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

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

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

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

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

       CHAR(10) + CC.CHECKER_ID + CHAR(10) AS CHECKER_ID

FROM(

        SELECT 

    	(CONVERT(DATE, A.TRN_DT, 103)) AS TRN_DATE, A.TRN_ID,A.ETP_ID,

        SUM(A.FCY_AMOUNT) AS AMT, UPPER(A.DESC_TRANS) AS TRN_DESC,

        D.BRANCH_NAME AS BRANCH_NAME,

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

        CASE WHEN A.DRCR_IND = 'D' THEN ISNULL(A.AC_NO, '') +' '+ D.BRANCH_CODE ELSE '' END AS DR_BRANCH_CODE,

        CASE WHEN A.DRCR_IND = 'C' THEN ISNULL(A.AC_NO, '') +' '+ D.BRANCH_CODE ELSE '' END AS CR_BRANCH_CODE,

        'VND' AS CCY, A.TRN_NO, A.ET_ID, A.RELATED_REFERENCE, A.DRCR_IND

        FROM ASS_ENTRIES_POST_SYNC A

         LEFT JOIN ASS_ENTRIES_POST AEP ON A.ET_ID = AEP.ET_ID

         LEFT JOIN ASS_MASTER B ON A.RELATED_REFERENCE=B.ASSET_CODE

         LEFT JOIN CM_BRANCH D ON D.BRANCH_CODE=A.AC_BRANCH

	    WHERE DATEDIFF(DAYOFYEAR, A.TRN_DT, CONVERT(DATETIME, @p_DATE, 103))=0

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

          AND AEP.TRN_TYPE <> 'AMORT'

		GROUP BY A.TRN_ID, A.AC_NO, A.AC_BRANCH, A.DESC_TRANS,  CONVERT(DATE, A.TRN_DT, 103), D.BRANCH_NAME

                ,A.USER_ID, A.AUTH_ID, D.BRANCH_CODE,A.TRN_NO, A.ET_ID, A.RELATED_REFERENCE, A.DRCR_IND,A.ETP_ID

    ) AS CC

ORDER BY cc.ET_ID ASC

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, @p_DATE AS NGAY_LAP_PHIEU

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

CHAR(10) +'REF'+ CHAR(10) AS REF,

CHAR(10) +N'MÃ TÀI SẢN'+ CHAR(10) AS ASSET_CODE,

CHAR(10) +N'SỐ CHỨNG TỪ / REF AMS'+ 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) +CHAR(10)  +N'TÀI KHOẢN NỢ '+ CHAR(10) AS [NO],

CHAR(10) +N'TÀI KHOẢ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

END