/****** OBJECT:  STOREDPROCEDURE [DBO].[P_EST_NF_ENT_NUMERACAO_REFAZER]    SCRIPT DATE: 12/19/2013 17:34:34 ******/
IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_EST_NF_ENT_NUMERACAO_REFAZER]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_EST_NF_ENT_NUMERACAO_REFAZER]
GO
--EXEC P_EST_NF_ENT_NUMERACAO_REFAZER 1, 4, 9428
CREATE PROCEDURE P_EST_NF_ENT_NUMERACAO_REFAZER (@CD_EMP INT, @CD_FILIAL INT, @CD_NF INT, @CD_NF_SERIE INT=0)
AS
DECLARE @DT_EMI_NF DATETIME
DECLARE @NR_NF INT
SET NOCOUNT ON 
CREATE TABLE #NOTAFISCAIS (CD_EMP INT, CD_FILIAL INT, CD_NF INT, NR_NF_NOVO INT)

IF @CD_NF_SERIE=0
begin
SELECT
	@CD_NF_SERIE=CD_NF_SERIE
FROM
	EST_NF_ENT 
WHERE
	CD_EMP =@CD_EMP
	AND CD_FILIAL = @CD_FILIAL
	AND CD_NF = @CD_NF
end

SET @NR_NF=(SELECT
			ISNULL(MAX(NR_NF),0) 
			FROM
				EF_NF_CTR_NR
			WHERE
				CD_EMP =@CD_EMP
				AND CD_FILIAL = @CD_FILIAL
				AND CD_NF_SERIE=@CD_NF_SERIE)

PRINT 'Novo numero.: ' + convert(varchar(20),@NR_NF)

--Inserindo notas fiscais para tratamento
INSERT INTO #NOTAFISCAIS
SELECT
	EST_NF_ENT.CD_EMP,
	EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT.CD_NF,
	@NR_NF + ROW_NUMBER() OVER (PARTITION BY EST_NF_ENT.CD_EMP, 
									EST_NF_ENT.CD_FILIAL
									ORDER BY EST_NF_ENT.CD_EMP, 
									EST_NF_ENT.CD_FILIAL) AS CD_IT
FROM
	EST_NF_ENT 
WHERE
	EST_NF_ENT.CD_EMP=@CD_EMP 
	AND EST_NF_ENT.CD_FILIAL=@CD_FILIAL
	AND EST_NF_ENT.CD_NF=@CD_NF

--Controle de numeracao
INSERT INTO EF_NF_CTR_NR (CD_EMP,CD_FILIAL,NR_NF,CD_NF_SERIE,NR_SELO_SAIDA,DT_CAD)
SELECT
	CD_EMP,
	CD_FILIAL,
	NR_NF_NOVO,
	@CD_NF_SERIE,
	0,
	GETDATE()
FROM
	#NOTAFISCAIS

--Update novo numero
UPDATE
	e
SET 
	NR_NF = NR_NF_NOVO,
	STS_NF=0,
	DT_ENT=CONVERT(CHAR(10),GETDATE(),112),
	DT_EMI_NF=CONVERT(CHAR(10),GETDATE(),112),
	HR_EMI_NF=CONVERT(CHAR(8),GETDATE(),108)
FROM
	EST_NF_ENT e INNER JOIN #NOTAFISCAIS N	ON
	e.CD_EMP = N.CD_EMP AND
	e.CD_FILIAL = N.CD_FILIAL AND
	e.CD_NF = N.CD_NF 

UPDATE
	e
SET 
	DT_ENT=CONVERT(CHAR(10),GETDATE(),112)
FROM
	EST_NF_ENT_IT e INNER JOIN #NOTAFISCAIS N	ON
	e.CD_EMP = N.CD_EMP AND
	e.CD_FILIAL = N.CD_FILIAL AND
	e.CD_NF = N.CD_NF 

--Deletando vinculo com a numeracao anterior
DELETE
	EST_NF_ENT_EF_NF_CTR_NF
WHERE
	CD_EMP=@CD_EMP 
	AND CD_FILIAL=@CD_FILIAL
	AND CD_NF_ENT=@CD_NF

--Vinculando nota fiscal com o controle de numeracao	
INSERT INTO EST_NF_ENT_EF_NF_CTR_NF(CD_EMP,CD_FILIAL,CD_NF_ENT,NR_NF,CD_NF_SERIE,FLAG_NOTA_INTEGRACAO)
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	NR_NF_NOVO,
	@CD_NF_SERIE,
	0
FROM
	#NOTAFISCAIS

--Deletando vinculo com inutilizacao
DELETE
	EST_NF_ENT_EST_NF_NFE_INU
WHERE
	CD_EMP=@CD_EMP 
	AND CD_FILIAL=@CD_FILIAL
	AND CD_NF=@CD_NF

--Deletando pendencia
DELETE
	EST_NF_ENT_NFE_PEND
WHERE
	CD_EMP=@CD_EMP 
	AND CD_FILIAL=@CD_FILIAL
	AND CD_NF=@CD_NF

--Inserindo pendencia	
INSERT INTO EST_NF_ENT_NFE_PEND(CD_EMP,CD_FILIAL,CD_NF,DT_CAD,PEND_SOLUC)
SELECT
	@CD_EMP,
	@CD_FILIAL,
	@CD_NF,
	GETDATE(),
	0

DROP TABLE #NOTAFISCAIS