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

ALTER PROCEDURE [dbo].[ASS_AMORT_Start]

	@p_AMORT_ID	varchar(15)  = NULL

AS

	--declare @l_CURR_AMORT_AMT decimal(18,0)

	declare @l_CURR_AMORT_STATUS nvarchar(100)

	declare @l_EXP_GL varchar(50)

	DECLARE @l_AMORT_GL varchar(50)

	declare @l_GROUP_ID varchar(15)

	declare @l_ASSET_ID varchar(15)

	declare @l_AMORT_AMT decimal(18,0)

	declare @l_BRANCH_ID varchar(15)

	declare @l_AMORTIZED_MONTH DECIMAL(18,2)

	declare @l_ET_ID varchar(15)

	declare @l_AMORTDT_ID varchar(15)

	declare @l_AMORT_END_DATE datetime

	declare @sToday varchar(10) = convert(varchar(10), getdate(), 103)

	declare @sExecDT datetime

	declare @l_TRN_REF_NO varchar(20) = NULL

	declare @l_DO_BRANCH_ID varchar(15)= NULL

	DECLARE @l_TYPE_ID	varchar(15)  = NULL

	DECLARE @l_DR_BRN_ID varchar(15), @l_CR_BRN_ID varchar(15)		

	DECLARE @l_HO_BRN_ID varchar(15)	

	DECLARE @l_DEP_ID varchar(15)

	DECLARE @l_MAKER_ID varchar(15), @l_CHECKER_ID varchar(15), @l_BRANCH_ID_DO varchar(20), @l_ASSET_TYPE_DO varchar(15),

	@l_BRANCH_TYPE_DO varchar(15)

	DECLARE @l_ASSET_TYPE VARCHAR(15), @l_DESC_CORE nvarchar(200),@l_AMORT_TERM varchar(20) --thieuvq them loai tai san de dien giai hach toan

	--Put valodation here	

	--Phong added for cost allocate starts :: 1/11/2018	

	declare @l_AMORTCOSTDT_ID varchar(15)

	declare @l_Cost decimal(18,0)

	declare @l_COSTDT_ID varchar(15)

	declare @l_COST_EXP_GL varchar(50)

	declare @l_COST_BRANCH_ID varchar(15),@l_AMORT_START_DATE datetime

	--Phong added for cost allocate ends :: 1/11/2018	

	--get data

	select @sExecDT = EXECUTE_DT, @l_MAKER_ID = MAKER_ID, @l_CHECKER_ID = CHECKER_ID, @l_AMORT_TERM = AMORT_TERM,

	--

	@l_BRANCH_ID_DO = BRANCH_ID, @l_ASSET_TYPE_DO = ASSET_TYPE--thieuvq them 01/08/2014

	--

	FROM ASS_AMORT 

	where AMORT_ID = @p_AMORT_ID

	SET @l_AMORT_TERM = REPLACE(@l_AMORT_TERM,'/',' ')

	--Lay branch_id cua user duyet

	select @l_DO_BRANCH_ID = a.TLSUBBRID from TL_USER a where a.TLNANME = (SELECT MAKER_ID FROM ASS_AMORT WHERE AMORT_ID = @p_AMORT_ID)	

	--BRN_ID HOI SO

	SELECT @l_HO_BRN_ID = BRANCH_ID FROM CM_BRANCH A WHERE A.BRANCH_TYPE = 'HS'	

	--lay branch type cua don vi thuc hien

	SELECT @l_BRANCH_TYPE_DO = BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @l_BRANCH_ID_DO

	--oepn cursor

	declare pCur cursor for

		 SELECT A.AMORTDT_ID, A.ASSET_ID, A.BRANCH_ID, A.DEP_ID, A.AMORT_AMT, 

				AM.GROUP_ID, AM.AMORTIZED_MONTH, AM.AMORT_END_DATE, AM.[TYPE_ID], AM.AMORT_START_DATE

		 FROM ASS_AMORT_DT  A

		 LEFT JOIN ASS_MASTER AM ON AM.ASSET_ID = A.ASSET_ID

		 WHERE AMORT_ID = @p_AMORT_ID AND A.EXECUTED = '0'

	open pCur

	DECLARE @SOKY INT = 1

	DECLARE @BeginDate DATE = CONVERT(DATE,(CONVERT(VARCHAR(10),YEAR(@sExecDT))+'-'+CONVERT(VARCHAR(4),MONTH(@sExecDT)) +'-1'))

	--SELECT @BeginDate

BEGIN TRANSACTION

	--chay khau hao

	FETCH NEXT FROM pCur INTO  @l_AMORTDT_ID, @l_ASSET_ID, @l_BRANCH_ID, @l_DEP_ID, @l_AMORT_AMT, 

				@l_GROUP_ID, @l_AMORTIZED_MONTH, @l_AMORT_END_DATE, @l_ASSET_TYPE, @l_AMORT_START_DATE

	WHILE @@FETCH_STATUS = 0	

	BEGIN

		--lay thong tin tai khoan

		SELECT @l_EXP_GL = G.EXP_ACCTNO, @l_AMORT_GL = G.AMORT_ACCTNO,@l_Type_ID = G.[TYPE_ID]

		FROM ASS_GROUP G

		WHERE G.GROUP_ID = @l_GROUP_ID

		IF @@ERROR<>0 GOTO ABORT

		SET @SOKY = 1

		IF (@l_AMORTIZED_MONTH = 0) --Khau hao thang dau tien

		BEGIN			

			SET @l_CURR_AMORT_STATUS = 'DKH'

			--------THIEUVQ KHAU HAO LUI 15012019-----------------

			--------BEGIN

			IF @l_AMORT_START_DATE < @BeginDate

			BEGIN

				--TINH SO KY KHAO HAO

				--SELECT DATEDIFF(MONTH,'2018-11-10','2019-1-1')

				SET @SOKY = DATEDIFF(MONTH, @l_AMORT_START_DATE, @BeginDate) + 1

				IF dbo.FN_GetLastDayOfMonth(@sExecDT) >= dbo.FN_GetLastDayOfMonth(@l_AMORT_END_DATE) --Ky khau hao cuoi cung

				BEGIN

					--SET @l_CURR_AMORT_AMT = @l_AMORT_AMT - @l_AMORTIZED_AMT

					SET @l_CURR_AMORT_STATUS = 'KHX'		

				END				

			END

			--------END

		END

		ELSE IF dbo.FN_GetLastDayOfMonth(@sExecDT) = dbo.FN_GetLastDayOfMonth(@l_AMORT_END_DATE) --Ky khau hao cuoi cung

		BEGIN

			--SET @l_CURR_AMORT_AMT = @l_AMORT_AMT - @l_AMORTIZED_AMT

			SET @l_CURR_AMORT_STATUS = 'KHX'		

		END

		ELSE --tai san dang khau hao binh thuong

		BEGIN			

			SET @l_CURR_AMORT_STATUS = 'DKH'

		END

		--Insert vao bang dbo.ASS_ENTRIES_POST

		EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST', @l_ET_ID out

		IF @l_ET_ID='' OR @l_ET_ID IS NULL GOTO ABORT	

		--ref_no de ben core tra ve thieuvq - 071118

		--EXEC ASS_TRN_REF_NO_Gen @l_Type_ID, @sToday, @l_TRN_REF_NO out	

		--IF @l_BRANCH_ID = @l_HO_BRN_ID 

		--BEGIN

		--	SET @l_DR_BRN_ID = @l_DEP_ID

		--END

		--ELSE

		--BEGIN

			SET @l_DR_BRN_ID = @l_BRANCH_ID

		--END

		--thieuvq dien giai hach toan khau hao

		IF @l_ASSET_TYPE = 'TSCD'

		BEGIN

			SET @l_DESC_CORE = 'KHAU HAO TSCD KY ' + @l_AMORT_TERM

			SET @l_CR_BRN_ID = @l_BRANCH_ID--@l_HO_BRN_ID

		END

		ELSE

		BEGIN

			SET @l_DESC_CORE = 'PHAN BO CHI PHI CCLD KY ' + @l_AMORT_TERM

			SET @l_CR_BRN_ID = @l_BRANCH_ID

		END

		INSERT INTO ASS_ENTRIES_POST

		(

			ET_ID,REF_NO, ASSET_ID, TRN_TYPE, REF_ID,DO_BRN, CR_ACCT, CR_BRN, DR_ACCT, DR_BRN, AMT,EXP_TO_CORE, TRN_DATE, TRN_DESC, MAKER_ID, CHECKER_ID, TRN_ID

		)

		VALUES

		(

			@l_ET_ID,@l_TRN_REF_NO, @l_ASSET_ID, 'AMORT', @l_AMORTDT_ID,@l_DO_BRANCH_ID, dbo.fn_replace_acctno(@l_AMORT_GL,@l_CR_BRN_ID), @l_CR_BRN_ID, @l_EXP_GL, @l_DR_BRN_ID, @l_AMORT_AMT,'Y',

			@sExecDT, @l_DESC_CORE, @l_MAKER_ID, @l_CHECKER_ID,@p_AMORT_ID

		)

		IF @@Error <> 0 GOTO ABORT	

		--- Phong added for cost allocate starts :: 1/11/2018

		IF EXISTS(SELECT 1 FROM ASS_AMORT_COST_DT A WHERE A.AMORTDT_ID = @l_AMORTDT_ID)

		BEGIN

			--update lai chi phi phan bo cho don vi chinh

			IF EXISTS(SELECT 1 FROM ASS_AMORT_COST_DT

						WHERE AMORTDT_ID = @l_AMORTDT_ID

						AND BRANCH_ID = @l_BRANCH_ID/*THIEUVQ THEM DK IS NULL NAY 140519*/AND COSTDT_ID IS NULL

						AND EXECUTED = '0')

			BEGIN

				SELECT @l_Cost = ISNULL(SUM(COST_AMT),0)

				FROM ASS_AMORT_COST_DT

				WHERE AMORTDT_ID = @l_AMORTDT_ID

				AND BRANCH_ID = @l_BRANCH_ID/*THIEUVQ THEM DK IS NULL NAY 140519*/AND COSTDT_ID IS NULL

				AND EXECUTED = '0'

			END

			ELSE

			BEGIN

				SET @l_Cost = 0

			END

			UPDATE ASS_ENTRIES_POST

			SET AMT = @l_Cost

			WHERE ET_ID = @l_ET_ID

			IF @@Error <> 0 GOTO ABORT	

			--hach toan Phan bo chi phi

			declare pCurCost cursor for

				SELECT a.AMORTCOSTDT_ID, a.COSTDT_ID, A.COST_AMT, A.BRANCH_ID, B.EXP_ACCTNO

				FROM ASS_AMORT_COST_DT  A

				INNER JOIN ASS_COST_ALLOC_DT B ON A.COSTDT_ID = B.COSTDT_ID

				WHERE AMORTDT_ID = @l_AMORTDT_ID

				/*THIEUVQ SUA DIEU KIEN 140519*/AND A.COSTDT_ID IS NOT NULL--AND A.BRANCH_ID <> @l_BRANCH_ID

				AND EXECUTED = '0'

			open pCurCost

			FETCH NEXT FROM pCurCost INTO @l_AMORTCOSTDT_ID, @l_COSTDT_ID, @l_Cost, @l_COST_BRANCH_ID, @l_COST_EXP_GL

			WHILE @@FETCH_STATUS = 0	

			BEGIN

				EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST', @l_ET_ID out

				IF @l_ET_ID='' OR @l_ET_ID IS NULL GOTO ABORT	

				INSERT INTO ASS_ENTRIES_POST

				(

					ET_ID,REF_NO, ASSET_ID, TRN_TYPE, REF_ID,DO_BRN, CR_ACCT, CR_BRN, DR_ACCT, DR_BRN, AMT,EXP_TO_CORE, TRN_DATE, TRN_DESC, MAKER_ID, CHECKER_ID, TRN_ID

				)

				VALUES

				(

					@l_ET_ID,@l_TRN_REF_NO, @l_ASSET_ID, 'AMORT', @l_AMORTDT_ID,@l_DO_BRANCH_ID, dbo.fn_replace_acctno(@l_AMORT_GL,@l_CR_BRN_ID), @l_CR_BRN_ID, @l_COST_EXP_GL, /*@l_DR_BRN_ID*/ @l_COST_BRANCH_ID,@l_Cost/* @l_AMORT_AMT*/,'Y',

					@sExecDT, @l_DESC_CORE, @l_MAKER_ID, @l_CHECKER_ID,@p_AMORT_ID

				)

				IF @@Error <> 0 GOTO ABORT	

				UPDATE ASS_COST_ALLOC_DT

				SET COSTED_MONTH = ISNULL(COSTED_MONTH,0) + @SOKY,

					COSTED_AMT = ISNULL(COSTED_AMT,0) + @l_Cost

				WHERE COSTDT_ID = @l_COSTDT_ID

				UPDATE ASS_AMORT_COST_DT

				SET EXECUTED = '1'

				WHERE AMORTCOSTDT_ID = @l_AMORTCOSTDT_ID

				FETCH NEXT FROM pCurCost INTO  @l_AMORTCOSTDT_ID, @l_COSTDT_ID, @l_Cost, @l_COST_BRANCH_ID, @l_COST_EXP_GL

			END

			CLOSE pCurCost

			DEALLOCATE pCurCost		

		END

		--- Phong added for cost allocate ends :: 1/11/2018

		--UPDATE BANG ASS_MASTER

		UPDATE ASS_MASTER 

		SET AMORTIZED_AMT = isnull(AMORTIZED_AMT, 0) + @l_AMORT_AMT,

			AMORTIZED_MONTH = isnull(AMORTIZED_MONTH, 0) + @SOKY,

			AMORT_STATUS = @l_CURR_AMORT_STATUS

		WHERE ASSET_ID = @l_ASSET_ID

		IF @@Error <> 0 GOTO ABORT	

		--update da hach toan

		UPDATE ASS_AMORT_DT

		SET EXECUTED = '1'

		WHERE AMORTDT_ID = @l_AMORTDT_ID

		IF @@Error <> 0 GOTO ABORT					

		FETCH NEXT FROM pCur INTO  @l_AMORTDT_ID, @l_ASSET_ID, @l_BRANCH_ID, @l_DEP_ID, @l_AMORT_AMT, 

				@l_GROUP_ID, @l_AMORTIZED_MONTH, @l_AMORT_END_DATE, @l_ASSET_TYPE, @l_AMORT_START_DATE

	END

	--UPDATE STATUS CHO BANG ASS_AMORT

	UPDATE ASS_AMORT

	SET PROCESS_STATUS = 'S'

	WHERE AMORT_ID = @p_AMORT_ID

	IF @@Error <> 0 GOTO ABORT		

	/*****THIEUVQ bo doan nay vi BAN VIET khong co dung

	/****CAP NHAT CCLD TINH TRANG: DPB(Đã phân bổ chi phí khi xuất sử dụng) VE DANG KHAU HAO***/

	--IF @l_ASSET_TYPE_DO = 'CCLD'

	--BEGIN

	--	 UPDATE ASS_MASTER SET AMORT_STATUS = 'DKH'

	--	 WHERE AMORT_STATUS IN ('DPB')

	--		--AND convert(date, AMORT_START_DATE) <= convert(date, @sExecDT)

	--		AND convert(date, AMORT_START_DATE) <= convert(date, dbo.FN_GetLastDayOfMonth(@sExecDT))			

	--		AND AMORTIZED_AMT < AMORT_AMT

	--		AND RECORD_STATUS = '1'

	--		AND AUTH_STATUS = 'A'		

	--		AND TYPE_ID = 'CCLD'	

	--		AND (BRANCH_ID = @l_BRANCH_ID_DO 				

	--			OR (@l_BRANCH_TYPE_DO = 'CN' --NEU LA CN THI PHAN BO LUON CHO PGD THUOC CHI NHANH DO

	--				--AND BRANCH_ID IN (SELECT * FROM [dbo].[CM_BRANCH_GETCHILDID](@l_BRANCH_ID_DO)))) --THIEUVQ

	--				AND BRANCH_ID IN (SELECT BR.BRANCH_ID FROM ASS_AMORT_BRN BR WHERE BR.AMORT_ID  = @p_AMORT_ID))) --THIEUVQ  28092016

	--END

	******/

	/*******************THIEUVQ - HUY CAC GIAO DICH CHUA DUYET - 251218 - BEGIN:*****************************/

	UPDATE ASS_ADDNEW SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' 

	WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)

	UPDATE ASS_USE_MULTI_DT SET AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE USER_MASTER_ID IN (SELECT A.USER_MASTER_ID FROM ASS_USE_MULTI_MASTER A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL))

	UPDATE ASS_USE_MULTI_MASTER SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)

	UPDATE ASS_LIQUIDATION_DT SET AUTH_STATUS = 'D', MAKER_ID = 'system' WHERE LIQ_ID IN (SELECT A.LIQ_ID FROM ASS_LIQUIDATION A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL))	

	UPDATE ASS_LIQUIDATION SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)

	UPDATE ASS_COLLECT_MULTI_DT SET AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE COL_MULTI_MASTER_ID IN (SELECT A.COL_MULTI_MASTER_ID FROM ASS_COLLECT_MULTI_MASTER A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL))

	UPDATE ASS_COLLECT_MULTI_MASTER SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)

	UPDATE ASS_TRANSFER_MULTI_DT SET AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE TRANS_MULTI_MASTER_ID IN (SELECT A.TRANS_MULTI_MASTER_ID FROM ASS_TRANSFER_MULTI_MASTER A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL))

	UPDATE ASS_TRANSFER_MULTI_MASTER SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)

	UPDATE ASS_UPDATE SET AUTH_STATUS = 'D', CHECKER_ID = 'system' WHERE AUTH_STATUS = 'U'

	/*********************END:***************************/

	CLOSE pCur

	DEALLOCATE pCur

COMMIT TRANSACTION

SELECT '0' as Result, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		CLOSE pCur

		DEALLOCATE pCur

		CLOSE pCurCost

		DEALLOCATE pCurCost	

		ROLLBACK TRANSACTION

		SELECT '-1' as Result,  ERROR_MESSAGE() ErrorDesc

		RETURN '-1'

End