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

/*

EXEC [ASS_AMORT_Do] '30/10/2018','admin','admin','ALL',NULL

*/

ALTER PROCEDURE dbo.ASS_AMORT_Do

	@p_EXECUTE_DATE varchar(20) = NULL,  --Mac dinh la ngay hien tai cua he thong, duoc truyen tu UI

	@p_MAKER_ID	varchar(15)  = NULL,

	@p_CHECKER_ID varchar(15)  = NULL,

	@p_ASSET_TYPE VARCHAR(15) = 'ALL',

	@p_BRANCH_ID_LST XML = NULL

AS

BEGIN TRY

	declare @l_AMORT_ID varchar(15), @l_AMORTDT_ID varchar(15)

	declare @l_LAST_AMORT_ID varchar(15) = NULL

	declare @l_AMORT_TERM nvarchar(50) = ''

	declare @l_ASSET_ID varchar(15)

	declare @l_AMORTIZED_AMT decimal(18,0)

	declare @l_AMORTIZED_MONTH int

	declare @l_AMORT_AMT decimal(18,0)

	declare @l_AMORT_MONTH decimal(18,2)

	declare @l_AMORT_END_DATE datetime, @l_AMORT_START_DATE datetime

	declare @l_FIRST_AMORT_AMT decimal(18,0), @l_MONTHLY_AMORT_AMT decimal(18,0)

	declare @l_BRANCH_ID varchar(15)

	declare @l_DEP_ID varchar(15)	

	declare @l_BRANCH_ID_DO varchar(15) = NULL, @l_BRANCH_TYPE_DO VARCHAR(15)

	declare @l_CURR_AMORT_AMT decimal(18,0)

	declare @l_TOTAL_AMT decimal(18,0)

	declare @l_CURR_AMORT_STATUS nvarchar(100)

	declare @l_DESC nvarchar(1000)

	DECLARE @ERRORSYS NVARCHAR(15) = '' 

	DECLARE @LST_BRANCH TABLE ( BRANCHID VARCHAR(15), BRANCHCODE VARCHAR(15))

	DECLARE @ErrCode nvarchar(10) --Phong added

BEGIN TRANSACTION	

	--Thieuvq Them gia tri mac dinh

	--

	declare @l_AmortIdDefault varchar(100) = '',@l_AMORTTERMDefault varchar(100) = '',

	@l_TOTALAMTDefault decimal (18,2) = 0,@l_PROCESSSTATUSDefault varchar(100) = '',

	@l_ExecDateDefault datetime = null, @l_StatusNameDef varchar(100) = '', @l_NotesDef varchar(100) = ''

	--

	declare @sExecDT datetime = convert(datetime, @p_EXECUTE_DATE, 103)

	select @l_LAST_AMORT_ID = AMORT_ID

	FROM ASS_AMORT

	WHERE IS_LEAF = 'Y'

	SET @l_AMORT_TERM = convert(varchar(2), datepart(month, @sExecDT)) + '/' + convert(varchar(4), datepart(year, @sExecDT))

	--Put valodation here

	--THIEUVQ 11/07/2014

	/***********LAY BRANCH_ID CUA USER THUC HIEN CHAY KHAU HAO**************/

	SELECT @l_BRANCH_ID_DO = TLSUBBRID FROM TL_USER WHERE TLNANME = @p_MAKER_ID	

	IF @l_BRANCH_ID_DO = '' OR @l_BRANCH_ID_DO IS NULL

	BEGIN

		SET @ERRORSYS = 'ASS-AMR-0005'	

		GOTO THROW_ERR

	END

	--THIEUVQ 29092016 - BO DIEU KIEN KHONG CHO PGD CHAY PHAN BO

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

	--IF @l_BRANCH_TYPE_DO = 'PGD' OR @l_BRANCH_TYPE_DO IS NULL

	--BEGIN

	--	SET @ERRORSYS = 'ASS-AMR-1006'	

	--	GOTO THROW_ERR

	--END

	--11/07/2014

	----------------BO DUNG DANH SACH PGD DUOC CN PHAN BO GIUM THIEUVQ - 28092016------------------

			IF @p_BRANCH_ID_LST IS NOT NULL AND @l_BRANCH_TYPE_DO = 'CN'

			BEGIN				

				Declare @hdoc INT

				Exec sp_xml_preparedocument @hdoc Output, @p_BRANCH_ID_LST	

				INSERT INTO @LST_BRANCH

				SELECT *

				FROM OPENXML(@hDoc,'/Root/BRANCHLIST',2)

				WITH 

				(					

					BRANCH_ID	varchar(15),

					BRANCH_CODE	varchar(15)

				)				

			END

	-----------------------------------------------------------------------------------------------

	/******** kiem tra giao dich chua duyet **********************/

	/* --Phong commented starts

	IF @p_ASSET_TYPE = 'TSCD'

	BEGIN

		IF 1=0 AND EXISTS(SELECT 1 FROM ASS_PENDING_ITEM A WHERE A.AUTH_STATUS = 'U' AND TYPE_ID = 'TSCD')--THIEUVQ THEM DIEU KIEN KIEM TRA THEO DON VI

		BEGIN

			SET @ERRORSYS = 'ASS-AMR-0002'	

			GOTO THROW_ERR

		END

	END

	ELSE

	BEGIN

		IF EXISTS(SELECT 1 FROM ASS_PENDING_ITEM A WHERE A.AUTH_STATUS = 'U' AND TYPE_ID = 'CCLD' AND TRAN_TYPE NOT IN ('ASS_ADDNEW','ASS_USE')

		AND (BRANCH_CREATE = @l_BRANCH_ID_DO 

			OR (@l_BRANCH_TYPE_DO = 'CN' 

				--AND BRANCH_CREATE IN (SELECT * FROM [dbo].[CM_BRANCH_GETCHILDID](@l_BRANCH_ID_DO)))))--THIEUVQ THEM DIEU KIEN KIEM TRA THEO DON VI

				AND BRANCH_CREATE IN (SELECT BRANCHID FROM @LST_BRANCH))))--THIEUVQ 29092016 - SUA DIEU KIEN PGD THUOC DANH SACH DA CHON

		BEGIN

			SET @ERRORSYS = 'ASS-AMR-0002'	

			GOTO THROW_ERR

		END

	END

	*/

	--Phong commented ends

	--Phong added starts

	IF 1=0 AND EXISTS(SELECT 1 FROM ASS_PENDING_ITEM A WHERE A.AUTH_STATUS = 'U')

	BEGIN

		SET @ERRORSYS = 'ASS-AMR-0002'	

		GOTO THROW_ERR

	END

	--THIEUVQ  THEM DIEU KIEN KIEM TRA KY KHAU HAO DA DUOC TAO

	IF EXISTS(SELECT 1 FROM ASS_AMORT A WHERE MONTH(A.EXECUTE_DT) = MONTH(@sExecDT) AND YEAR(A.EXECUTE_DT) = YEAR(@sExecDT)

												  AND ASSET_TYPE = @p_ASSET_TYPE)	

		BEGIN

			SET @ERRORSYS = 'ASS-AMR-0001'

			GOTO THROW_ERR

		END

	/*******KIEM TRA KY KHAU HAO DA TRONG THANG DA DUOC TAO HAY CHUA******/

/*

	IF @p_ASSET_TYPE = 'TSCD'

	BEGIN

		IF EXISTS(SELECT 1 FROM ASS_AMORT A WHERE MONTH(A.EXECUTE_DT) = MONTH(@sExecDT) AND YEAR(A.EXECUTE_DT) = YEAR(@sExecDT)

												  AND ASSET_TYPE = 'TSCD')	

		BEGIN

			SET @ERRORSYS = 'ASS-AMR-0001'

			GOTO THROW_ERR

		END

	END

	ELSE

	BEGIN		

		IF EXISTS(SELECT 1 FROM ASS_AMORT A WHERE MONTH(A.EXECUTE_DT) = MONTH(@sExecDT) AND YEAR(A.EXECUTE_DT) = YEAR(@sExecDT)

												AND ASSET_TYPE = 'CCLD' AND BRANCH_ID = @l_BRANCH_ID_DO)--THIEUVQ THEM DIEU KIEN KIEM TRA THEO LOAI TAI SAN

		BEGIN

			SET @ERRORSYS = 'ASS-AMR-0006'

			GOTO THROW_ERR

		END

		----------------BO DUNG DANH SACH PGD DUOC CN PHAN BO GIUM THIEUVQ - 28092016------------------

		IF @l_BRANCH_TYPE_DO = 'CN'

		BEGIN

			DECLARE @LSTERROR VARCHAR(500) = ''

			SELECT @LSTERROR = @LSTERROR + B.BRANCHCODE + ';' 

			FROM ASS_AMORT A 

			LEFT JOIN @LST_BRANCH B ON B.BRANCHID = A.BRANCH_ID

			WHERE A.AMORT_TERM = @l_AMORT_TERM AND A.BRANCH_ID IN (SELECT BRANCHID FROM @LST_BRANCH)

			IF LEN(@LSTERROR) > 0	

			BEGIN

				SELECT '-1' Result, N'PGD sau đã tự chạy phân bổ: ' + @LSTERROR ErrorDesc, @l_AmortIdDefault as AMORT_ID, @l_AMORTTERMDefault as AMORT_TERM, 

					@l_TOTALAMTDefault as TOTAL_AMT, @l_PROCESSSTATUSDefault as PROCESS_STATUS ,@l_StatusNameDef as STATUS_NAME, 

					@l_ExecDateDefault AS EXECUTE_DT,@l_NotesDef AS NOTES

				IF @@TRANCOUNT>0 ROLLBACK TRANSACTION

				RETURN '0'

			END			

		END

		ELSE 

		BEGIN				

			IF @l_BRANCH_TYPE_DO = 'PGD'

			BEGIN			

				IF EXISTS(SELECT 1 FROM ASS_AMORT_BRN A WHERE A.AMORT_TERM = @l_AMORT_TERM AND A.BRANCH_ID = @l_BRANCH_ID_DO)

				BEGIN

					SET @ERRORSYS = 'ASS-AMR-0007'

					GOTO THROW_ERR

				END

			END

		END

	END

*/

	/***** Ngay thuc thi khau hao phai lon hon ngay chay cuoi cung da chay *****/

	--IF EXISTS(SELECT 1 FROM ASS_AMORT A WHERE convert(date,A.EXECUTE_DT) > convert(date,@sExecDT))

	--BEGIN

	--	SET @ERRORSYS = 'ASS-AMR-0004'

	--	GOTO THROW_ERR

	--END		

	/************ Kiem tra cac tai khoan hach toan trong bang ASS_GROUP ******/

	IF EXISTS(SELECT * 

				FROM ASS_GROUP A 

				WHERE 1=1

					AND A.IS_LEAF='Y' 

					AND ISNULL(A.ASSET_ACCTNO,'') = '' 

					AND ISNULL(A.AMORT_ACCTNO,'') = ''

					AND ISNULL(A.EXP_ACCTNO,'') = ''

					AND ISNULL(A.INC_ACCTNO,'') = ''

					AND ISNULL(A.LIQ_ACCTNO,'') = ''

					AND A.TYPE_ID = @p_ASSET_TYPE)

	BEGIN					

		SET @ERRORSYS = 'ASS-AMR-0003'

		GOTO THROW_ERR

	END

THROW_ERR:			

	IF @ERRORSYS <> '' 

	BEGIN

		IF @ERRORSYS = 'ASS-AMR-0001'

		BEGIN

			SELECT @l_AmortIdDefault = A.AMORT_ID, @l_PROCESSSTATUSDefault = A.PROCESS_STATUS, @l_ExecDateDefault = A.EXECUTE_DT,

			@l_AMORTTERMDefault = A.AMORT_TERM, @l_TOTALAMTDefault = A.TOTAL_AMT

			FROM ASS_AMORT A WHERE MONTH(A.EXECUTE_DT) = MONTH(@sExecDT) AND YEAR(A.EXECUTE_DT) = YEAR(@sExecDT)

												  AND ASSET_TYPE = @p_ASSET_TYPE 

			SELECT ErrorCode Result, ErrorDesc ErrorDesc, @l_AmortIdDefault as AMORT_ID, @l_AMORTTERMDefault as AMORT_TERM, 

						@l_TOTALAMTDefault as TOTAL_AMT, @l_PROCESSSTATUSDefault as PROCESS_STATUS,@l_StatusNameDef as STATUS_NAME, 

						@l_ExecDateDefault AS EXECUTE_DT,@l_NotesDef AS NOTES

			FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

		END

		ELSE

		SELECT ErrorCode Result, ErrorDesc ErrorDesc, @l_AmortIdDefault as AMORT_ID, @l_AMORTTERMDefault as AMORT_TERM, 

					@l_TOTALAMTDefault as TOTAL_AMT, @l_PROCESSSTATUSDefault as PROCESS_STATUS,@l_StatusNameDef as STATUS_NAME, 

					@l_ExecDateDefault AS EXECUTE_DT,@l_NotesDef AS NOTES

		FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

		IF @@TRANCOUNT>0 ROLLBACK TRANSACTION

		RETURN '0'

	END	

	--oepn cursor

	declare pCur cursor for

		 SELECT A.ASSET_ID, A.AMORTIZED_AMT, A.AMORTIZED_MONTH,

				A.AMORT_AMT,  A.AMORT_MONTH, A.AMORT_END_DATE, A.AMORT_START_DATE,

				A.FIRST_AMORT_AMT, A.MONTHLY_AMORT_AMT, A.BRANCH_ID, A.DEPT_ID

		 FROM ASS_MASTER A

		 WHERE A.AMORT_STATUS IN ('CKH','DKH')

			AND convert(date, A.AMORT_START_DATE) <= convert(date, @sExecDT)

			AND A.AMORTIZED_AMT < A.AMORT_AMT

			AND A.RECORD_STATUS = '1'

			AND A.AUTH_STATUS = 'A'

			AND (A.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE = 'ALL') --Phong

/*			AND A.TYPE_ID = @p_ASSET_TYPE--THIEUVQ	

			AND (A.BRANCH_ID = @l_BRANCH_ID_DO 

				OR (@p_ASSET_TYPE = 'TSCD' or @p_ASSET_TYPE = 'CCLD')

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

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

					AND A.BRANCH_ID IN (SELECT BRANCHID FROM @LST_BRANCH))) --THIEUVQ CHINH SUA 28092016

*/

	open pCur

	--Update ky khau hao truoc khong con la nut la

	IF @l_LAST_AMORT_ID IS NOT NULL 

	BEGIN

		UPDATE ASS_AMORT 

		SET IS_LEAF = 'N'

		WHERE AMORT_ID = @l_LAST_AMORT_ID

	END	

	--set dien giai

	IF @p_ASSET_TYPE = 'TSCD'

		SET @l_DESC = N'Khấu hao kỳ ' + @l_AMORT_TERM

	ELSE

		SET @l_DESC = N'Phân bổ kỳ ' + @l_AMORT_TERM

	--Insert ky khau hao moi

	EXEC SYS_CodeMasters_Gen 'ASS_AMORT', @l_AMORT_ID out

	INSERT INTO ASS_AMORT

	(

		AMORT_ID,

		AMORT_TERM,

		EXECUTE_DT,

		TOTAL_AMT,

		IS_LEAF,

		PARENT_ID,

		NOTES,

		PROCESS_STATUS,

		[RECORD_STATUS],

		[MAKER_ID],

		[CREATE_DT],

		[AUTH_STATUS],

		[CHECKER_ID],

		[APPROVE_DT],

		[BRANCH_ID],

		[ASSET_TYPE]

	)

	VALUES

	(

		@l_AMORT_ID,

		@l_AMORT_TERM,

		CONVERT(datetime, @p_EXECUTE_DATE, 103),

		0,

		'Y',

		@l_LAST_AMORT_ID,

		@l_DESC,--N'Khấu hao kỳ ' + @l_AMORT_TERM,

		'P', --Pending cho chay khau hao

		'1',

		@p_MAKER_ID,

		GETDATE(),

		'A',

		@p_CHECKER_ID,

		GETDATE(),

		@l_BRANCH_ID_DO,

		@p_ASSET_TYPE

	)	

	----------------BO DUNG DANH SACH PGD DUOC CN PHAN BO GIUM THIEUVQ - 28092016------------------

	IF @l_BRANCH_TYPE_DO = 'CN'

	BEGIN

		INSERT INTO ASS_AMORT_BRN SELECT @l_AMORT_ID, @l_AMORT_TERM, BRANCHID FROM @LST_BRANCH

	END

	-----------------------------------------------------------------------------------------------

	SET @l_TOTAL_AMT = 0	

	DECLARE @SOKY INT = 1

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

	--SELECT @BeginDate

	--Tinh khau hao cho tung tai san

	FETCH NEXT FROM pCur INTO  @l_ASSET_ID, @l_AMORTIZED_AMT, @l_AMORTIZED_MONTH,

				@l_AMORT_AMT,  @l_AMORT_MONTH, @l_AMORT_END_DATE, @l_AMORT_START_DATE,

				@l_FIRST_AMORT_AMT, @l_MONTHLY_AMORT_AMT, @l_BRANCH_ID, @l_DEP_ID

	WHILE @@FETCH_STATUS = 0	

	BEGIN

		--SET @l_DESC = N'Khấu hao kỳ ' + @l_AMORT_TERM

		SET @SOKY = 1

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

		BEGIN

			SET @l_CURR_AMORT_AMT = @l_FIRST_AMORT_AMT

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

				SET @l_CURR_AMORT_AMT = @l_FIRST_AMORT_AMT + @l_MONTHLY_AMORT_AMT*@SOKY

				SET @SOKY = @SOKY + 1

				IF @SOKY > = @l_AMORT_MONTH ---KHAU HAO LUI VOI SOKY LON HON HOAC BANG SO THANG THI KHAU HAO HET

				BEGIN

					SET @SOKY = @l_AMORT_MONTH

					SET @l_CURR_AMORT_AMT = @l_AMORT_AMT

				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'	

			print 'last amort'	+ @l_ASSET_ID

		END

		ELSE --tai san dang khau hao binh thuong

		BEGIN

			SET @l_CURR_AMORT_AMT = @l_MONTHLY_AMORT_AMT

			--SET @l_CURR_AMORT_STATUS = 'DKH'			

		END

		--THIEUVQ THEM DK KHONG KHAU HAO AM 13112017

			IF (ISNULL(@l_AMORTIZED_AMT,0) + @l_CURR_AMORT_AMT) > @l_AMORT_AMT

				SET @l_CURR_AMORT_AMT = @l_AMORT_AMT - ISNULL(@l_AMORTIZED_AMT,0)

		--Insert to bang ASS_AMORT_DT

		EXEC SYS_CodeMasters_Gen 'ASS_AMORT_DT', @l_AMORTDT_ID out

		INSERT INTO ASS_AMORT_DT

		(

			AMORTDT_ID,

			AMORT_ID,

			AMORT_DT,

			CRDR,

			BRANCH_ID,

			DEP_ID,

			ASSET_ID,

			AMORT_AMT,

			GROUP_ID,

			EXECUTED,

			TRN_TYPE,

			TRN_DESC

		)

		VALUES

		(

			@l_AMORTDT_ID,

			@l_AMORT_ID,

			@sExecDT,

			'C',

			@l_BRANCH_ID,

			@l_DEP_ID,

			@l_ASSET_ID,

			@l_CURR_AMORT_AMT,

			NULL, --Khong chay khau hao theo group

			'0',

			'AMORT',

			@l_DESC

		)

		SET @l_TOTAL_AMT = @l_TOTAL_AMT + @l_CURR_AMORT_AMT

		--Phong added for cost allocate starts -- 20-OCT-2018

		IF EXISTS(SELECT 1 FROM ASS_COST_ALLOCATION WHERE ASSET_ID = @l_ASSET_ID)

		BEGIN

			EXEC ASS_AMORT_COST_Do

				@l_AMORT_ID,

				@p_EXECUTE_DATE,

				@l_AMORTDT_ID,

				@l_ASSET_ID,

				@l_CURR_AMORT_AMT,

				@SOKY,

				@ErrCode OUT

			IF (@ErrCode<> '0') 

				THROW 99001, 'Cost Allocation failed', 1;

		END

		--Phong added for cost allocate End -- 20-OCT-2018

		FETCH NEXT FROM pCur INTO  @l_ASSET_ID, @l_AMORTIZED_AMT, @l_AMORTIZED_MONTH,

					@l_AMORT_AMT,  @l_AMORT_MONTH, @l_AMORT_END_DATE, @l_AMORT_START_DATE,

					@l_FIRST_AMORT_AMT, @l_MONTHLY_AMORT_AMT, @l_BRANCH_ID, @l_DEP_ID

	END

	UPDATE ASS_AMORT

	SET TOTAL_AMT = @l_TOTAL_AMT

	WHERE AMORT_ID = @l_AMORT_ID

END TRY

BEGIN CATCH	

		CLOSE pCur

		DEALLOCATE pCur

	IF @@TRANCOUNT>0 ROLLBACK TRANSACTION		

		--SELECT '-1' as Result, (convert(nvarchar(100),ERROR_NUMBER()) + N' - ' + ERROR_MESSAGE()) ErrorDesc, 

		--	'' AMORT_ID, '' AMORT_TERM, 0 TOTAL_AMT, GETDATE() EXECUTE_DT,'' AS PROCESS_STATUS,

		--	N'' STATUS_NAME, N'' NOTES

		SELECT top 1 '-1' as Result, (convert(nvarchar(100),ERROR_NUMBER()) + N' - ' + ERROR_MESSAGE()) ErrorDesc, AMORT_ID, AMORT_TERM, TOTAL_AMT, EXECUTE_DT,'' AS PROCESS_STATUS,

		N'' STATUS_NAME, NOTES

		FROM ASS_AMORT 

	RETURN '-1'	

END CATCH;

	CLOSE pCur

	DEALLOCATE pCur

IF @@TRANCOUNT>0 COMMIT TRANSACTION

SELECT '0' as Result, '' ErrorDesc, AMORT_ID, AMORT_TERM, TOTAL_AMT, EXECUTE_DT,'' AS PROCESS_STATUS,

		CASE WHEN PROCESS_STATUS='P' THEN N'Chờ thực thi' ELSE N'Đã thực thi' END STATUS_NAME, NOTES

		FROM ASS_AMORT WHERE AMORT_ID = @l_AMORT_ID

RETURN '0'	

GO