IF EXISTS (SELECT * FROM SYS.procedures WHERE NAME = 'P_EST_NF_ENT_GERA_DEVOLUCAO_PELA_SAIDA_ALL')
BEGIN
	DROP PROCEDURE P_EST_NF_ENT_GERA_DEVOLUCAO_PELA_SAIDA_ALL
END
GO
--P_EST_NF_ENT_GERA_DEVOLUCAO_PELA_SAIDA_ALL 1,900,101,'2016-03-07','2016-02-22',0
CREATE PROCEDURE P_EST_NF_ENT_GERA_DEVOLUCAO_PELA_SAIDA_ALL (@CD_EMP INT, 
															 @CD_FILIAL INT, 
															 @CD_NT_MOV INT,
															 @DT_ENT DATETIME,
															 @DT_PROC DATETIME,
															 @CD_NF_SAI_PROC INT=0)
AS
begin
DECLARE @CD_NF INT
DECLARE @NR_NF INT
DECLARE @CD_NF_SERIE INT
DECLARE @HIST VARCHAR(200)
DECLARE @HIST2 VARCHAR(200)
DECLARE @DT_EMI_NF DATETIME
DECLARE @CONTADOR INT

SET @HIST=''
SET @HIST2=''


DECLARE CnotasEntradaDevolucao CURSOR FAST_FORWARD FOR 
	SELECT  DISTINCT 
			NF.CD_NF,
			NF.NF_NF,
			NF.CD_NF_SERIE,
			NF.DT_EMI_NF
    FROM EST_NF_SAI NF INNER JOIN [0EST_NF_SAIDA_DEVOLVER] C ON
			NF.CD_EMP			= C.CD_EMP AND
			NF.CD_FILIAL	= C.CD_FILIAL AND
			NF.CD_NF		= C.CD_NF	
			LEFT OUTER JOIN EST_NF_SAI_NF_ENT d ON
			NF.CD_EMP = d.CD_EMP AND
			NF.CD_FILIAL = d.CD_FILIAL AND
			NF.CD_NF = d.CD_NF_SAI 
			LEFT OUTER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF t ON
			NF.CD_EMP = t.CD_EMP AND
			NF.CD_FILIAL = t.CD_FIL_ORIG AND
			NF.CD_NF = t.CD_NF_SAI 
	WHERE NF.CD_EMP = @CD_EMP
			AND NF.CD_FILIAL = @CD_FILIAL	
			AND NF.CD_NF = CASE WHEN @CD_NF_SAI_PROC= 0 THEN NF.CD_NF ELSE @CD_NF_SAI_PROC END
			AND NF.STS_NF=0	
			AND NF.DT_EMI_NF=@DT_PROC	
			AND D.CD_NF_SAI IS NULL
			AND T.CD_NF_SAI IS NULL
			AND NOT EXISTS (SELECT I.CD_EMP
							FROM EST_NF_SAI_EST_NF_NFE_INU I 
							WHERE   I.CD_EMP			 = NF.CD_EMP
									AND I.CD_FILIAL	 = NF.CD_FILIAL
									AND I.CD_NF		 = NF.CD_NF)
		ORDER BY
			NF_NF
SET @CONTADOR=1
OPEN CnotasEntradaDevolucao
FETCH NEXT FROM CnotasEntradaDevolucao INTO @CD_NF,@NR_NF,@CD_NF_SERIE,@DT_EMI_NF
	WHILE @@FETCH_STATUS=0
	  begin
	  PRINT 'Nota Fiscal ' + CONVERT(VARCHAR, @NR_NF) + ' ' + CONVERT(VARCHAR, @CONTADOR) + ' ' + CONVERT(VARCHAR, GETDATE (),108)
	    SET @HIST='NOTAL FISCAL DEVOLUCAO REFERENTE A NOTA FISCAL NR.: ' +  CONVERT(VARCHAR, @NR_NF) + ' EMITIDA EM ' + CONVERT(VARCHAR, @DT_EMI_NF,103)
		EXEC P_EST_NF_ENT_EFETUA_ENTRADA_NF_DEVO @CD_EMP,@CD_FILIAL,@CD_NT_MOV,@DT_ENT,@CD_NF,@CD_NF_SERIE,0,@HIST,@HIST2

		SET @CONTADOR=@CONTADOR+1
	  FETCH NEXT FROM CnotasEntradaDevolucao INTO @CD_NF,@NR_NF,@CD_NF_SERIE,@DT_EMI_NF
	end
PRINT 'Concluido.'
CLOSE CnotasEntradaDevolucao;
DEALLOCATE CnotasEntradaDevolucao;
end
