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

ALTER PROCEDURE dbo.ASS_GROUP_Ins

	@p_TYPE_ID		varchar(15)  = NULL,

	@p_GROUP_CODE	nvarchar(100)  = NULL,

	@p_GROUP_NAME	nvarchar(200)  = NULL,

	@p_PARENT_ID	varchar(15)  = NULL,

	@p_IS_LEAF		varchar(1)  = NULL,

	--@p_GROUP_LEVEL	int = NULL, --SP se tu dong tinh level

    @p_ASS_CAT	varchar(10) = NULL ,

	@p_AMORT_ACCTNO	varchar(50)  = NULL,

	@p_EXP_ACCTNO	varchar(50)  = NULL,

	@p_INC_ACCTNO	varchar(50)  = NULL,

	@p_LIQ_ACCTNO	varchar(50)  = NULL,

	@p_ASSET_ACCTNO	varchar(50)  = NULL,

	@p_ASSET_ACCTNO_NHNN VARCHAR(50) = NULL,

	@p_AMORT_MONTH	int = NULL,

	@p_AMORT_RATE	decimal(18,2)  = NULL,

	@p_NOTES		nvarchar(1000)  = NULL,

	@p_RECORD_STATUS	varchar(1)  = NULL,

	@p_AUTH_STATUS	varchar(1)  = NULL,

	@p_MAKER_ID		varchar(15)  = NULL,

	@p_MANAGER_ID	varchar(15)  = NULL,

	@p_CREATE_DT	VARCHAR(20) = NULL,

	@p_CHECKER_ID	varchar(15)  = NULL,

	@p_APPROVE_DT	VARCHAR(20) = NULL,

	@p_AMORT_MONTH_MIN int = NULL,

    @p_AMORT_MONTH_MAX int = NULL

AS

	DECLARE @l_PARENT_GROUP_LEVEL int = 0

	DECLARE @l_GROUP_LEVEL int

	DECLARE @l_GROUP_ID VARCHAR(15)

	DECLARE @sErrorCode VARCHAR(20) = ''

	DECLARE @l_AMORT_MONTH INT

	-- LUCTV: BO SUNG CONG THUC TU TINH

	SET @l_AMORT_MONTH = CASE WHEN @p_AMORT_RATE IS NULL OR @p_AMORT_RATE = 0 THEN 0 ELSE ROUND(12/@p_AMORT_RATE*100,0) END

	--Lay group level cua cha

	IF (@p_PARENT_ID IS NULL OR @p_PARENT_ID = '')

	BEGIN

		SET @l_GROUP_LEVEL = 1

	END

	ELSE

	BEGIN

		SELECT @l_PARENT_GROUP_LEVEL = ISNULL(GROUP_LEVEL,0) FROM ASS_GROUP A WHERE A.GROUP_ID = @p_PARENT_ID

		SET @l_GROUP_LEVEL = @l_PARENT_GROUP_LEVEL + 1

	END

	--Kiem tra parent_ID hop le

	IF (@p_PARENT_ID IS NOT NULL AND @p_PARENT_ID <> '')

	BEGIN

		IF NOT EXISTS(SELECT * FROM ASS_GROUP A WHERE A.GROUP_ID = @p_PARENT_ID)

		BEGIN

			SET @sErrorCode = 'ASS-00006'

		END

	END

	IF EXISTS(SELECT 1 FROM ASS_GROUP A WHERE A.GROUP_CODE = @p_GROUP_CODE AND A.TYPE_ID=@p_TYPE_ID)

	BEGIN

		SET @sErrorCode = 'ASS-000021'

	END

	--PHONGNT 19/9/2022 LUON MAC DINH GIA TRỊ MIN MAX

	SET @p_AMORT_MONTH_MIN=1

	SET @p_AMORT_MONTH_MAX=5000

	-- 19-04-2019 LUCTV: KIEM TRA MIN - MAX CUA NHOM TS KHONG DUOC =0

	IF (@p_AMORT_MONTH_MIN =0 OR @p_AMORT_MONTH_MIN IS NULL)

	BEGIN

		SELECT '-1' as Result, '' AMORT_MONTH, N'Số tháng khấu hao MIN phải lớn hơn 0' ErrorDesc

		RETURN '-1'		

	END

	IF (@p_AMORT_MONTH_MAX =0 OR @p_AMORT_MONTH_MAX IS NULL)

	BEGIN

		SELECT '-1' as Result, '' AMORT_MONTH, N'Số tháng khấu hao MAX phải lớn hơn 0' ErrorDesc

		RETURN '-1'		

	END

	IF (@p_AMORT_MONTH_MAX < @p_AMORT_MONTH_MIN)

	BEGIN

		SELECT '-1' as Result, '' AMORT_MONTH, N'Số tháng khấu hao MAX phải lớn hơn hoặc bằng số tháng khấu hao MIN' ErrorDesc

		RETURN '-1'		

	END

	IF (@l_AMORT_MONTH IS NULL OR @l_AMORT_MONTH >@p_AMORT_MONTH_MAX OR @l_AMORT_MONTH <@p_AMORT_MONTH_MIN)

	BEGIN

		SELECT '-1' as Result, '' AMORT_MONTH, N'Số tháng khấu hao phải nằm trong khoảng '+ CONVERT(VARCHAR(5),@p_AMORT_MONTH_MIN)+N' - ' +CONVERT(VARCHAR(5),@p_AMORT_MONTH_MAX)  ErrorDesc

		RETURN '-1'		

	END

	-- END LUCTV 19-04-2019	

	IF 	@sErrorCode <> ''

	BEGIN

		SELECT ErrorCode Result, '' AMORT_MONTH, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode

		RETURN 0

	END

BEGIN TRANSACTION

		EXEC SYS_CodeMasters_Gen 'ASS_GROUP', @l_GROUP_ID out

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

		--TINH SO KY KHAU HAO

		--SET @p_AMORT_RATE = (CASE WHEN @p_AMORT_RATE IS NULL OR @p_AMORT_RATE = 0 then 0 ELSE (12/@p_AMORT_RATE)*100 END)

		SET @l_AMORT_MONTH = CASE WHEN @p_AMORT_RATE IS NULL OR @p_AMORT_RATE = 0 THEN 0 ELSE ROUND(12/@p_AMORT_RATE*100,0) END

		--Insert bang ASS_GROUP

		INSERT INTO ASS_GROUP

		(

			[GROUP_ID],

			[TYPE_ID],

			[GROUP_CODE],

			[GROUP_NAME],

			[PARENT_ID],

			[IS_LEAF],			

			[GROUP_LEVEL],

			[ASS_CAT],

			[AMORT_ACCTNO],

			[EXP_ACCTNO],

			[INC_ACCTNO],

			[LIQ_ACCTNO],

			[ASSET_ACCTNO],

			[ASSET_ACCTNO_NHNN],

			[AMORT_MONTH],

			[AMORT_RATE],

			[NOTES],

			[RECORD_STATUS],

			[AUTH_STATUS],

			[MAKER_ID],

			[CREATE_DT],

			[CHECKER_ID],

			[APPROVE_DT],

			[AMORT_MONTH_MIN],

			[AMORT_MONTH_MAX],

			[MANAGER_ID]

		)

		VALUES

		(

			@l_GROUP_ID,

			@p_TYPE_ID,

			@p_GROUP_CODE, 

			@p_GROUP_NAME,

			@p_PARENT_ID,

			--@p_IS_LEAF,

			'Y', --Luon luon la leaf

			@l_GROUP_LEVEL,

			@p_ASS_CAT,

			@p_AMORT_ACCTNO,

			@p_EXP_ACCTNO,

			@p_INC_ACCTNO,

			@p_LIQ_ACCTNO,

			@p_ASSET_ACCTNO,

			@p_ASSET_ACCTNO_NHNN,

			@l_AMORT_MONTH,--sua

			@p_AMORT_RATE,

			@p_NOTES,

			@p_RECORD_STATUS,

			@p_AUTH_STATUS,

			@p_MAKER_ID,

			CONVERT(DATETIME, @p_CREATE_DT, 103),

			@p_CHECKER_ID,

			(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END),

			@p_AMORT_MONTH_MIN,

			@p_AMORT_MONTH_MAX,

			@p_MANAGER_ID

		)

		IF @@Error <> 0 GOTO ABORT

		--UPDATE IS_LEAF = 'N' cho record cha

		IF (@p_PARENT_ID IS NOT NULL AND @p_PARENT_ID<>'')

		BEGIN

			UPDATE ASS_GROUP

			SET IS_LEAF = 'N'

			WHERE GROUP_ID = @p_PARENT_ID

			IF @@Error <> 0 GOTO ABORT

		END

COMMIT TRANSACTION

SELECT '0' as Result, CAST(@l_AMORT_MONTH as varchar(100)) AMORT_MONTH, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' AMORT_MONTH, '' ErrorDesc

		RETURN '-1'

End