/****** Object:  StoredProcedure [dbo].[P_EST_NF_ENT_CORRECAO_NUMERACAO_DUPLICADA]    Script Date: 01/17/2014 14:10:46 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_ENT_CORRECAO_NUMERACAO_DUPLICADA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_ENT_CORRECAO_NUMERACAO_DUPLICADA]
GO
GO
GO
CREATE PROCEDURE [dbo].[P_EST_NF_ENT_CORRECAO_NUMERACAO_DUPLICADA] AS
CREATE TABLE #NFEnt(CD_EMP INT,
					CD_FILIAL INT,
					NR_NF INT,
					CD_FORN INT,
					CD_NF_SERIE INT,
					DT_EMI_NF DATETIME)
INSERT INTO #NFENT
SELECT
	CD_EMP,
	CD_FILIAL,
	NR_NF,
	CD_FORN,
	CD_NF_SERIE,
	DT_EMI_NF
FROM
	EST_NF_ENT
WHERE
	CD_EMP =1
GROUP BY
	NR_NF,
	DT_EMI_NF,
	CD_FORN,
	CD_FILIAL,
	CD_EMP,
	CD_NF_SERIE
HAVING
	COUNT(NR_NF) > 1
ORDER BY
	NR_NF,
	CD_FILIAL,
	DT_EMI_NF


DECLARE @CD_EMP INT,
		@CD_FILIAL INT,
		@NR_NF INT,
		@CD_NF INT,
		@CONTADOR INT
	
DECLARE cNFCorrigirNumeracao CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT
	EST_NF_ENT.CD_EMP,
	EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT.CD_NF,
	EST_NF_ENT.NR_NF 
FROM
	EST_NF_ENT inner join #NFENT e on
	EST_NF_ENT.CD_EMP = e.CD_EMP AND 
	EST_NF_ENT.CD_FILIAL = e.CD_FILIAL AND 
	EST_NF_ENT.NR_NF = e.NR_NF AND
	EST_NF_ENT.CD_FORN = e.CD_FORN AND 
	EST_NF_ENT.CD_NF_SERIE = e.CD_NF_SERIE AND
	EST_NF_ENT.DT_EMI_NF = e.DT_EMI_NF

		
SET @CONTADOR= 0
OPEN cNFCorrigirNumeracao
FETCH NEXT FROM cNFCorrigirNumeracao INTO @CD_EMP,@CD_FILIAL,@CD_NF,@NR_NF
WHILE @@FETCH_STATUS=0
BEGIN 
	PRINT 'Codigo filial ' + CONVERT(VARCHAR, @CD_FILIAL) + ' ' + CONVERT(VARCHAR, GETDATE (),108)
		
	update
		EST_NF_ENT 
	set 
		  NR_NF = NR_NF + @CONTADOR
	WHERE
		CD_EMP = @CD_EMP
		AND CD_FILIAL = @CD_FILIAL
		AND CD_NF = @CD_NF
		
	SET @CONTADOR= @CONTADOR + 1
	FETCH NEXT FROM cNFCorrigirNumeracao INTO @CD_EMP,@CD_FILIAL,@CD_NF,@NR_NF
		
END
CLOSE cNFCorrigirNumeracao
DEALLOCATE cNFCorrigirNumeracao 
print 'Fim de atualizacao da correção - Time : ' +convert(varchar,getdate(),108) 

DROP TABLE #NFEnt	
GO


