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 CHAR(10) +  CC.STT + 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, 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 H:mm:ss') + 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,
@p_DATE 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