IF EXISTS ( SELECT * FROM SYS.OBJECTS 
	WHERE NAME = 'P_CONTROLE_SEQUENCIA')
BEGIN 
	DROP PROCEDURE P_CONTROLE_SEQUENCIA
END
GO

CREATE PROCEDURE [dbo].[P_CONTROLE_SEQUENCIA]   (@CDEMP			INT
												,@Tabela		VARCHAR(100)
												,@Chave			VARCHAR(100)
												,@Criterio		VARCHAR(MAX))

AS
	BEGIN

		SET DEADLOCK_PRIORITY HIGH;
		SET NOCOUNT ON;

		IF OBJECT_ID(N'tempdb..#TmpControleSeq') IS NOT NULL
			BEGIN
				DROP TABLE #TmpControleSeq
			END
 
		CREATE TABLE #TmpControleSeq
		(
		 ControleV0 BIGINT,
		 ControleV1 BIGINT,
		 ControleV2 BIGINT
		)

		IF OBJECT_ID(N'tempdb..#TmpRetorno') IS NOT NULL
			BEGIN
				DROP TABLE #TmpRetorno
			END
 
		CREATE TABLE #TmpRetorno(VALOR BIGINT)

		DECLARE		 @Sequencia01	BIGINT
					,@Sequencia02	BIGINT
					,@Sequencia00	BIGINT
					,@Resultado		BIGINT
					,@ScriptExec	VARCHAR(MAX)
					
		
		SET @Criterio = dbo.[F_PADRONIZA_AGRUPAMENTO](@Criterio);
					

		SET @Sequencia00	= 0
		SET @Sequencia01	= 0
		SET @Sequencia02	= 0

		SET @ScriptExec		= 'INSERT INTO #TmpControleSeq (ControleV0,ControleV1) SELECT ';
		SET @ScriptExec		= @ScriptExec + ' ISNULL((SELECT ISNULL(SEQUENCIA,0) AS ID FROM EST_NUM_SEQ_TABELA WHERE CD_EMP = ' + CONVERT(VARCHAR,@CDEMP) + ' AND TABELA = ''' + @Tabela + ''' AND CHAVE = ''' + @Chave + ''' AND AGRUPAMENTO = ''' + @Criterio + '''),0) AS CONTROLE';
		SET @ScriptExec		= @ScriptExec + ',ISNULL((SELECT ISNULL(SEQUENCIA,0) AS ID FROM EST_NUM_SEQ_TABELA WHERE CD_EMP = ' + CONVERT(VARCHAR,@CDEMP) + ' AND TABELA = ''' + @Tabela + ''' AND CHAVE = ''' + @Chave + ''' AND AGRUPAMENTO = ''' + @Criterio + '''),0) AS VALOR';
		
		EXEC(@ScriptExec)

		IF @Criterio = ''
			BEGIN
				SET @ScriptExec		= 'UPDATE #TmpControleSeq SET ControleV2 = ISNULL((SELECT MAX(' + @Chave + ') AS ID FROM ' + @Tabela + '),0)';
			END
		ELSE
			BEGIN
				SET @ScriptExec		= 'UPDATE #TmpControleSeq SET ControleV2 = ISNULL((SELECT MAX(' + @Chave + ') AS ID FROM ' + @Tabela + ' WHERE ' + replace(@Criterio,'>','''') + '),0)';
			END
			
		EXEC(@ScriptExec)

		SET @Sequencia00	= ISNULL((SELECT ControleV0 from #TmpControleSeq),0)
		SET @Sequencia01	= ISNULL((SELECT ControleV1 from #TmpControleSeq),0)
		SET @Sequencia02	= ISNULL((SELECT ControleV2 from #TmpControleSeq),0)

		IF @Sequencia02 > @Sequencia01
			BEGIN
				SET @Resultado = (@Sequencia02 + 1)

				IF @Sequencia00 > 0
					BEGIN				
						IF @Criterio = ''
							BEGIN
								UPDATE EST_NUM_SEQ_TABELA SET SEQUENCIA = @Resultado WHERE CD_EMP = @CDEMP AND TABELA = @Tabela AND CHAVE = @Chave
							END
						ELSE
							BEGIN
								UPDATE EST_NUM_SEQ_TABELA SET SEQUENCIA = @Resultado WHERE CD_EMP = @CDEMP AND TABELA = @Tabela AND CHAVE = @Chave AND AGRUPAMENTO = @Criterio
							END				
					END
				ELSE
					BEGIN
						INSERT INTO EST_NUM_SEQ_TABELA (CD_EMP,SEQUENCIA,TABELA,CHAVE,AGRUPAMENTO) VALUES (@CDEMP,@Resultado,@Tabela,@Chave,@Criterio)
					END

			END
		ELSE
			BEGIN
				SET @Resultado = (@Sequencia01 + 1)

				IF @Criterio = ''
					BEGIN
						UPDATE EST_NUM_SEQ_TABELA SET SEQUENCIA = @Resultado WHERE CD_EMP = @CDEMP AND TABELA = @Tabela AND CHAVE = @Chave
					END
				ELSE
					BEGIN
						UPDATE EST_NUM_SEQ_TABELA SET SEQUENCIA = @Resultado WHERE CD_EMP = @CDEMP AND TABELA = @Tabela AND CHAVE = @Chave AND AGRUPAMENTO = @Criterio
					END	
			END

		INSERT INTO #TmpRetorno (Valor) VALUES (@Resultado)
	
		SELECT Valor FROM #TmpRetorno;
	
		RETURN;					 	
	
	END

	GO

