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

--

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254405'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254406'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254407'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254408'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254329'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254330'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254331'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254332'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254333'

UPDATE ASS_ADDNEW SET APPROVE_DT_KT ='2021-02-25' WHERE ADDNEW_ID ='ASA000000254334'

---

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254405') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254406') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254407') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254408') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254329') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254330') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254331') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254332') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254333') AND TRN_TYPE ='ASS_ADDNEW'

UPDATE ASS_VALUES SET TRN_DT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254334') AND TRN_TYPE ='ASS_ADDNEW'

--

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254405') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254406') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254407') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254408') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254329') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254330') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254331') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254332') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254333') 

UPDATE ASS_MASTER SET BUY_DATE_KT ='2021-02-25' WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254334') 

--

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254405'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254406'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254407'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254408'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254329'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254330'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254331'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254332'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254333'

UPDATE ASS_TRANSACTIONS SET TRN_DATE ='2021-02-25' WHERE TRN_ID ='ASA000000254334'

--

DECLARE @REQ_ID VARCHAR(15), @DATE DATETIME

SET @DATE ='2020-01-26 12:00:00 AM'

SET @REQ_ID =(SELECT REQ_ID FROM TR_REQUEST_DOC WHERE REQ_CODE ='PUR/2021/000127')

UPDATE PL_PROCESS SET APPROVE_DT =@DATE WHERE REQ_ID =@REQ_ID

UPDATE TR_REQUEST_DOC SET REQ_DT =@DATE, APPROVE_DT =@DATE WHERE REQ_ID =@REQ_ID

SET @DATE ='2020-01-19 12:00:00 AM'

SET @REQ_ID =(SELECT REQ_ID FROM TR_REQUEST_DOC WHERE REQ_CODE ='PUR/2021/000095')

UPDATE PL_PROCESS SET APPROVE_DT =@DATE WHERE REQ_ID =@REQ_ID

UPDATE TR_REQUEST_DOC SET REQ_DT =@DATE, APPROVE_DT =@DATE WHERE REQ_ID =@REQ_ID

--

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254405') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254406') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254407') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254408') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254329') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254330') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254331') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254332') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254333') 

SELECT * FROM ASS_AMORT_DT WHERE ASSET_ID IN (SELECT ASSET_ID FROM ASS_TRANSACTIONS WHERE TRN_ID ='ASA000000254334') 

¿

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.TRN_ID 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

    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.TRN_ID 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

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

		      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'

		      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

WHERE CC.AMT >0

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