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

ALTER PROCEDURE [dbo].[MW_ENTRIES_POST_CoreByRef]

	@p_REF_ID varchar(200) = NULL,

	@p_TRN_TYPE VARCHAR(50) = ''---AMORT - KHẤU HAO, ADDNEW - THEM MOI, USE - XUAT, TRANSF - DIEU CHUYEN, UPDATE - UPDATE, COLLECT - THU HOI, LIQ - THANH LY

AS

	DECLARE @l_MAKER_ID VARCHAR(15), @l_CHECKER_ID VARCHAR(15), @l_TRN_DESC NVARCHAR(MAX), @lTRN_DATE DATETIME

	SELECT TOP 1 @l_MAKER_ID = MAKER_ID,@l_CHECKER_ID = CHECKER_ID, @l_TRN_DESC = TRN_DESC, @lTRN_DATE = TRN_DATE

	FROM MW_ENTRIES_POST 

	WHERE TRN_ID = @p_REF_ID AND TRN_TYPE = @p_TRN_TYPE AND (REF_NO IS NULL OR REF_NO = '') AND CONVERT(DATE,TRN_DATE) >= CONVERt(DATE,GETDATE()) AND AMT > 0

	SELECT ACC.ET_ID,

			ACC.CR_ACCT,CRB.BRANCH_CODE AS CR_BRN,

			ACC.DR_ACCT,DRB.BRANCH_CODE AS DR_BRN, DP.DEP_CODE AS DR_DEP,

			ACC.AMT,

			'VND' AS CURRENCY, 1 AS EXCRATE, @l_MAKER_ID AS MAKER_ID, @l_CHECKER_ID AS CHECKER_ID, @l_TRN_DESC AS TRN_DESC

	FROM

	(

		SELECT  CR_BRN, CR_ACCT, 

		DR_BRN, DEPT_ID DR_DEP, DR_ACCT, 

		SUM(AMT) AS AMT, 

		STUFF(( SELECT ',' + C1.ET_ID

		FROM MW_ENTRIES_POST C1

		WHERE C1.AMT > 0 AND C2.CR_BRN=C1.CR_BRN AND C2.CR_ACCT=C1.CR_ACCT AND C2.DR_BRN=C1.DR_BRN AND C2.DR_ACCT=C1.DR_ACCT --AND C1.TRN_ID = @p_REF_ID

		AND  C2.DEPT_ID = C1.DEPT_ID

		AND TRN_ID = @p_REF_ID AND TRN_TYPE = @p_TRN_TYPE AND (REF_NO IS NULL OR REF_NO = '') AND CONVERT(DATE,TRN_DATE) >= CONVERt(DATE,GETDATE())

		FOR

		XML PATH('')

		), 1, 1, '') AS ET_ID

		--'VND' AS CURRENCY, 1.0 AS EXCRATE, @l_MAKER_ID AS MAKER_ID, @l_CHECKER_ID AS CHECKER_ID, @l_TRN_DESC AS TRN_DESC

		FROM MW_ENTRIES_POST  C2	

		WHERE C2.AMT > 0 AND TRN_ID = @p_REF_ID AND TRN_TYPE = @p_TRN_TYPE AND (REF_NO IS NULL OR REF_NO = '') AND CONVERT(DATE,TRN_DATE) >= CONVERt(DATE,GETDATE())

		GROUP BY C2.CR_BRN, C2.CR_ACCT, C2.DR_BRN, C2.DR_ACCT, C2.DEPT_ID

	) AS ACC

	LEFT JOIN CM_BRANCH CRB ON CRB.BRANCH_ID = ACC.CR_BRN

	LEFT JOIN CM_BRANCH DRB ON DRB.BRANCH_ID = ACC.DR_BRN

	LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = ACC.DR_DEP