DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER PROCEDURE [dbo].[ASS_ENTRIES_POST_CoreByRef_Tool]

	@p_REF_ID varchar(200) = NULL,

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

AS

--DECLARE @l_SUM_AMT DECIMAL, @l_REF_NO VARCHAR(20), @l_ASSET_ID VARCHAR(20)

--SELECT TOP 1 @l_TRN_TYPE = TRN_TYPE FROM ASS_ENTRIES_POST A

--WHERE A.REF_ID = @p_REF_ID

--SELECT TOP 1 @l_REF_NO = REF_NO, @l_ASSET_ID = ASSET_ID FROM ASS_ENTRIES_POST WHERE REF_ID = @p_REF_ID

--dbo.ReadMoney(A.AMT) as stringMoney,

	--DECLARE @TEMP_AMT TABLE (CR_ACCT VARCHAR(50), AMOUNT DECIMAL)

	--INSERT INTO @TEMP_AMT select CR_ACCT,sum(AMT)

	--from ASS_ENTRIES_POST 

	--where REF_ID =@p_REF_ID

	--GROUP BY CR_ACCT

	--[dbo].[FN_GET_ACC_NAME](A.CR_ACCT) AS CR_ACCT_NAME,

	--SELECT A.ET_ID,

	--	A.CR_ACCT, CRB.BRANCH_CODE AS CR_BRN, 

	--	A.DR_ACCT, DRB.BRANCH_CODE AS DR_BRN, 

	--	A.AMT AS AMT, A.TRN_DESC AS TRN_DESC, 

	--	'VND' AS CURRENCY, 1.0 AS EXCRATE, A.MAKER_ID, A.CHECKER_ID

	--FROM ASS_ENTRIES_POST A

	----LEFT JOIN @TEMP_AMT B ON A.CR_ACCT= B.CR_ACCT

	----LEFT JOIN CM_BRANCH DOB ON DOB.BRANCH_ID = A.DO_BRN

	--LEFT JOIN CM_BRANCH CRB ON CRB.BRANCH_ID = A.CR_BRN

	--LEFT JOIN CM_BRANCH DRB ON DRB.BRANCH_ID = A.DR_BRN

	--WHERE A.REF_ID = @p_REF_ID

	----GROUP BY A.TRN_TYPE,A.REF_ID,A.CR_ACCT,A.CR_BRN,A.DR_ACCT,A.DR_BRN,A.TRN_DESC,B.AMOUNT

	DECLARE @ENTRIES_POST_TEM TABLE (ET_ID varchar(15), REF_NO varchar(20), ASSET_ID varchar(15), TRN_TYPE nvarchar(40), REF_ID varchar(15), DO_BRN varchar(15), CR_ACCT varchar(20), CR_BRN varchar(15), DR_ACCT varchar(20), DR_BRN varchar(15), AMT decimal(18, 0), EXP_TO_CORE varchar(1), TRN_DATE datetime, TRN_DESC nvarchar(1000), MAKER_ID varchar(15), CHECKER_ID varchar(15), TRN_ID varchar(15), CR_DEP VARCHAR(15), DR_DEP VARCHAR(15))

	INSERT INTO @ENTRIES_POST_TEM SELECT *,'','' FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID AND (REF_NO IS NULL OR REF_NO = '')

	UPDATE @ENTRIES_POST_TEM SET CR_DEP = A.DEP_ID, DR_DEP = A.DEP_ID

	FROM ASS_AMORT_DT A

	INNER JOIN @ENTRIES_POST_TEM B ON A.AMORTDT_ID = B.REF_ID

	UPDATE @ENTRIES_POST_TEM SET DR_DEP = A.DEPT_ID

	FROM (SELECT A.*, B.ASSET_ID FROM ASS_COST_ALLOC_DT A

		LEFT JOIN ASS_COST_ALLOCATION B ON A.COS_ID = B.COS_ID

		WHERE A.RECORD_STATUS = '1') A

	INNER JOIN @ENTRIES_POST_TEM B ON A.ASSET_ID = B.ASSET_ID

	--SELECT * FROM @ENTRIES_POST_TEM --WHERE CR_DEP <> DR_DEP

-------

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

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

	FROM ASS_ENTRIES_POST 

	WHERE TRN_ID = @p_REF_ID AND (REF_NO IS NULL OR REF_NO = '')

	---THIEUVQ THEM DO_BRANCH 121120

	SET @p_DO_BRN_CODE = (SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_DO_BRN)

	IF @p_TRN_TYPE = 'AMORT'

	BEGIN

		SET @l_TRN_DESC = N'KH/PB KY '+CONVERT(VARCHAR(5),MONTH(@lTRN_DATE))+' '+CONVERT(VARCHAR(5),YEAR(@lTRN_DATE))

	END

	SELECT ACC.ET_ID,

			ACC.CR_ACCT,CRB.BRANCH_CODE AS CR_BRN, ISNULL(DP.DEP_CODE,'') AS DR_DEP,

			ACC.DR_ACCT,DRB.BRANCH_CODE AS DR_BRN,

			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, @p_DO_BRN_CODE AS DO_BRN

	FROM

	(

		SELECT  CR_BRN, CR_DEP, CR_ACCT, 

		DR_BRN, DR_DEP, DR_ACCT, 

		SUM(AMT) AS AMT, 

		STUFF(( SELECT ',' + C1.ET_ID

		FROM @ENTRIES_POST_TEM C1

		WHERE 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.CR_DEP = C1.CR_DEP AND C2.DR_DEP = C1.DR_DEP

		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 @ENTRIES_POST_TEM  C2	

		--WHERE C2.TRN_ID = @p_REF_ID

		GROUP BY C2.CR_BRN, C2.CR_ACCT, C2.DR_BRN, C2.DR_ACCT, C2.CR_DEP, C2.DR_DEP

	) 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

	WHERE ACC.AMT > 0