IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'EST_NF_SAI_DIF')
BEGIN 
	CREATE TABLE EST_NF_SAI_DIF (
		CD_CTR      INT IDENTITY (1,1),
		CD_EMP		INT, 
		CD_FILIAL	INT,
		CD_NF		INT,
		VLR_NF_OLD    MONEY,
		VLR_NF_NEW    MONEY
		)
END 
GO
IF EXISTS (SELECT *FROM SYS.PROCEDURES WHERE NAME= 'P_EST_NF_SAI_CORRIGE_VLR_NF')
BEGIN
    DROP PROCEDURE  P_EST_NF_SAI_CORRIGE_VLR_NF 
END 

GO

CREATE PROCEDURE   P_EST_NF_SAI_CORRIGE_VLR_NF 
AS 
 SET NOCOUNT ON;
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
	CREATE TABLE #tmp_corrigir (CD_EMP		int, 
								CD_FILIAL	int, 
								CD_NF		int, 
								VLR_NF		Money,
								VLR_NF_NEW Money)

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
 
	INSERT INTO #tmp_corrigir
	SELECT 
		EST_NF_SAI.CD_EMP, 
		EST_NF_SAI.CD_FILIAL, 
		EST_NF_SAI.CD_NF, 
		EST_NF_SAI.VLR_NF,
		VLR_TOT_IT
	FROM 
		EST_NF_SAI  
		JOIN ( SELECT CD_EMP , 
						CD_FILIAL , 
						CD_NF,  
							SUM( 	(VLR_TOT_IT + ISNULL(VLR_IPI_IT,0) 
											+ ISNULL(VLR_ICMS_SUBS,0) 
											+ ISNULL(VLR_OUTROS_DESPESAS,0) 
											+ ISNULL(VLR_PROD_FRETE,0) 
											+ ISNULL(VLR_FECOP_ST,0) )
								) - 
							SUM((VLR_SUFRAMA + VLR_DESC_IT)) VLR_TOT_IT

				FROM EST_NF_SAI_IT  
				GROUP BY CD_EMP , DT_EMI_NF,
						CD_FILIAL , 
						CD_NF
				) IT  ON IT.CD_EMP = EST_NF_SAI.CD_EMP 
						AND IT.CD_FILIAL = EST_NF_SAI.CD_FILIAL 
						AND IT.CD_NF = EST_NF_SAI.CD_NF  
						
	WHERE 
		EST_NF_SAI.CD_EMP=1
		AND EST_NF_SAI.DT_EMI_NF BETWEEN CONVERT(DATE,GETDATE()-7) AND  CONVERT(DATE,GETDATE())
		AND EST_NF_SAI.VLR_NF <> ROUND(VLR_TOT_IT,2);
	
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- Salva vlr_nf anterior	
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
	INSERT INTO EST_NF_SAI_DIF
	SELECT CD_EMP
		  ,CD_FILIAL
		  ,CD_NF
		  ,VLR_NF
		  ,VLR_NF_NEW
	from 
		#tmp_corrigir
 
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::		  
 	
	UPDATE s
		SET VLR_NF = VLR_NF_NEW	
	FROM 
		EST_NF_SAI  s
		Join #tmp_corrigir t ON s.CD_EMP	= t.CD_EMP 
							AND s.CD_FILIAL = t.CD_FILIAL 
							AND s.CD_NF		= t.CD_NF  
	where
		s.CD_EMP=1
   

 