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