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_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, 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.TRN_ID 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' AND A.AMT >0

		      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

    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.TRN_ID 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' AND A.AMT >0

		      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

    ) AS CC

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