ALTER PROCEDURE dbo.CM_APPROVE_GROUP_Upd
	@p_REQ_ID [varchar](50) NULL,
	@p_GROUP_APPROVES NVARCHAR(MAX)=NULL,
	@p_CURRENT_STEP INT,
	@p_DONE BIT OUT,
	@p_ERROR_MESS NVARCHAR(MAX) OUT
AS
--Validation is here

	DECLARE @ERRORSYS NVARCHAR(15) = '' 

	IF( @ERRORSYS <> '' )
	BEGIN
		SELECT @p_DONE = 0, @p_ERROR_MESS =  @ERRORSYS
		RETURN '-1'
	END

--BEGIN TRANSACTION
	declare @approveTlb table(STEP_LEVEL INT,USERNAME nvarchar(50),PROCESS BIT)
	declare @listApproveTlb table(STEP_LEVEL INT,GROUP_APPROVE nvarchar(max))

	--SLIP LIST
		insert into @listApproveTlb (STEP_LEVEL, GROUP_APPROVE)
		select  row_number() over(order by (select 1)), [value]
		FROM WSISPLIT(@p_GROUP_APPROVES,',')
		WHERE [value] <>'' 
		--DELETE @listApproveTlb WHERE GROUP_APPROVE=''OR GROUP_APPROVE IS NULL


	--	LIST TO TABLE
		insert into @approveTlb (STEP_LEVEL,USERNAME,PROCESS)
		select A.STEP_LEVEL,B.value	,0
		from @listApproveTlb A
		CROSS APPLY (select [value] FROM WSISPLIT(A.GROUP_APPROVE,';') ) B
	--CHECK NHÓM DUYỆT
	IF(EXISTS(SELECT * FROM @approveTlb GROUP BY USERNAME HAVING COUNT(USERNAME)>1))
	BEGIN
		--ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, N'DANH SÁCH NHÓM DUYỆT KHÔNG HỢP LỆ. DANH SÁCH NGƯỜI DUYỆT BỊ TRÙNG. VUI LÒNG KIỂM TRA LẠI.' ErrorDesc
		RETURN '-1'
	END
	--END CHECK NHÓM DUYỆT		
	DECLARE @TOT_APPORVE INT = (SELECT COUNT(*) from @approveTlb)
	DECLARE @index_approve INT,@l_APPROVE_GROUP_ID nvarchar(50)
	--RESET CM_APPROVE_GROUP
		DELETE CM_APPROVE_GROUP WHERE REQ_ID=@p_REQ_ID
		EXEC SYS_CodeMasters_Gen_Mult 'CM_APPROVE_GROUP',@TOT_APPORVE, @l_APPROVE_GROUP_ID out, @index_approve out
		INSERT INTO [dbo].[CM_APPROVE_GROUP]
			   ([APPROVE_GROUP_ID]
			   ,[APPROVE_USERNAME]
			   ,[STEP_LEVEL]
			   ,PROCESS_STATUS
			   ,DONE
			   ,CREATE_DT
			   ,[AUTH_STATUS]
			   ,[REQ_ID]
			   ,[TYPE])
		SELECT ('CAG' + right('0000000000000'+convert(varchar(15),(@index_approve -1 + row_number() over(order by (select 1)))),10)),
			USERNAME
			,STEP_LEVEL
			,0
			,0
			,GETDATE()
			,'U'
			,@p_REQ_ID
			,'APP'
		FROM @approveTlb
	--RESET CM_APPROVE_GROUP
	
	IF @@Error <> 0 GOTO ABORT
	--
--COMMIT TRANSACTION
SELECT @p_DONE = 1, @p_ERROR_MESS =  @ERRORSYS
RETURN '0'
ABORT:
BEGIN
		--ROLLBACK TRANSACTION
		SELECT @p_DONE = 0, @p_ERROR_MESS =  N'CẬP NHẬT NHÓM DUYỆT THẤT BẠI'
		RETURN '-1'
End



GO

ALTER PROCEDURE dbo.CM_APPROVE_GROUP_Ins
	@p_REQ_ID [varchar](50) NULL,
	@p_GROUP_APPROVES NVARCHAR(max)=NULL,
	@p_DONE BIT OUT,
	@p_ERROR_MESS NVARCHAR(MAX) OUT
AS
--Validation is here

	DECLARE @ERRORSYS NVARCHAR(15) = '' 

	IF( @ERRORSYS <> '' )
	BEGIN
		SELECT @p_DONE = 0, @p_ERROR_MESS =  @ERRORSYS
		RETURN '-1'
	END

	--BEGIN TRANSACTION
	declare @listApproveTlb table(STEP_LEVEL INT,GROUP_APPROVE nvarchar(max))

	declare @approveTlb table(STEP_LEVEL INT,USERNAME nvarchar(50))
	--SLIP LIST
		insert into @listApproveTlb (STEP_LEVEL, GROUP_APPROVE)
		select  row_number() over(order by (select 1)), [value]
		FROM WSISPLIT(@p_GROUP_APPROVES,',')
		WHERE [value] <>'' 
		--DELETE @listApproveTlb WHERE GROUP_APPROVE=''OR GROUP_APPROVE IS NULL
		
	--	LIST TO TABLE
		insert into @approveTlb (STEP_LEVEL,USERNAME)
		select A.STEP_LEVEL,B.value	 
		from @listApproveTlb A
		CROSS APPLY (select [value] FROM WSISPLIT(A.GROUP_APPROVE,';') ) B

	--CHECK NHÓM DUYỆT
		IF(EXISTS(SELECT * FROM @approveTlb GROUP BY USERNAME HAVING COUNT(USERNAME)>1))
		BEGIN
			--ROLLBACK TRANSACTION
			SELECT @p_DONE = 0, @p_ERROR_MESS=N'DANH SÁCH NHÓM DUYỆT KHÔNG HỢP LỆ. DANH SÁCH NGƯỜI DUYỆT BỊ TRÙNG. VUI LÒNG KIỂM TRA LẠI.'
			print 7
			RETURN 0
		END
	--END CHECK NHÓM DUYỆT	
	--START CHECK THƯ KÍ
	/*insert into @approveTlb
	SELECT STEP_LEVEL From @approveTlb
	WHERE dbo.FN_CHECK_ROLE_USER(USERNAME,'TGD')=1*/
	--END CHECK THƯ KÍ
	--INSERT CM_APPROVE_GROUP
	DECLARE @TOT_APPORVE INT = (SELECT COUNT(*) from @approveTlb)
	DECLARE @index_approve INT,@l_APPROVE_GROUP_ID nvarchar(50)
	EXEC SYS_CodeMasters_Gen_Mult 'CM_APPROVE_GROUP',@TOT_APPORVE, @l_APPROVE_GROUP_ID out, @index_approve out

	INSERT INTO [dbo].[CM_APPROVE_GROUP]
           ([APPROVE_GROUP_ID]
           ,[APPROVE_USERNAME]
           ,[STEP_LEVEL]
		   ,PROCESS_STATUS
		   ,DONE
           ,[AUTH_STATUS]
		   ,CREATE_DT
           ,[REQ_ID]
		   ,[TYPE])
	SELECT ('CAG' + right('000000000000000'+convert(varchar(15),(@index_approve -1 + row_number() over(order by (select 1)))),10)),
		USERNAME
		,STEP_LEVEL
		,0
		,0
		,'U'
		,GETDATE()
		,@p_REQ_ID
		,'APP'
	FROM @approveTlb
	IF @@Error <> 0 GOTO ABORT
	--

--COMMIT TRANSACTION
SELECT @p_DONE = 1, @p_ERROR_MESS =  N''
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT @p_DONE = 0, @p_ERROR_MESS =  N'THÊM NHÓM DUYỆT THẤT BẠI'
		RETURN '-1'
End