/****** Object:  StoredProcedure [dbo].[P_EST_NF_ENT_ANALISE_VALOR_NOTA_FISCAL]    Script Date: 12/12/2014 10:45:45 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PED_ELTR_DEMANDA_CP_IT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_ENT_ANALISE_VALOR_NOTA_FISCAL]
GO
GO
--EXEC P_EST_NF_ENT_ANALISE_VALOR_NOTA_FISCAL '2019-08-01','2019-09-30',9,965137,0
CREATE PROCEDURE [dbo].[P_EST_NF_ENT_ANALISE_VALOR_NOTA_FISCAL] (@DT_INI AS DATETIME=NULL, 
                                                                 @DT_FIM DATETIME=NULL, 
																 @CD_FILIAL INT=0, 
																 @NR_NF INT=0, 
																 @ANALISE INT=0,
																 @CD_EMP INT=1) AS
set nocount on;
IF @DT_INI IS NULL
BEGIN
	SET @DT_INI = CONVERT(CHAR(10),GETDATE()-12,112)
	SET @DT_FIM = CONVERT(CHAR(10),GETDATE(),112)
END
CREATE TABLE #RsTotalItem (CD_EMP INT, 
						   CD_FILIAL INT, 
						   CD_NF INT, 
						   NR_NF INT,
						   VLR_TOTAL_NF MONEY,
						   VLR_TOTAL_PROD MONEY,
						   VLR_BASE_ICMS MONEY,
						   VLR_ICMS MONEY,
						   VLR_BASE_ICMS_SUBS MONEY,
						   VLR_ICMS_SUBS MONEY,
						   VLR_IPI MONEY,
						   VLR_DESC_IT MONEY,
						   VLR_ICMS_RET MONEY,
						   VLR_OUTRAS_DESPESAS MONEY,
						   VLR_FECOP_ST MONEY,
						   VLR_ICMS_DESONERADO MONEY,
						   FLAG_ICMS_SUBST_PAGO_ANTERIOR INT)
UPDATE
	EST_NF_ENT_IT
SET 
	 EST_NF_ENT_IT.VLR_TOT_IT=ROUND((QT_IT * VLR_IT),2)
FROM
	EST_NF_ENT_IT INNER JOIN EST_NF_ENT ON
	EST_NF_ENT_IT.CD_EMP = EST_NF_ENT.CD_EMP AND
	EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
	EST_NF_ENT_IT.CD_NF  = EST_NF_ENT.CD_NF 
WHERE
     ROUND((QT_IT * VLR_IT),2) <> EST_NF_ENT_IT.VLR_TOT_IT
	AND EST_NF_ENT.CD_EMP = @CD_EMP 
	AND EST_NF_ENT.CD_FILIAL=CASE WHEN @CD_FILIAL = 0 THEN EST_NF_ENT.CD_FILIAL ELSE @CD_FILIAL END 
	AND EST_NF_ENT.NR_NF=CASE WHEN @NR_NF = 0 THEN EST_NF_ENT.NR_NF ELSE @NR_NF END 
	AND EST_NF_ENT.DT_ENT BETWEEN  @DT_INI AND @DT_FIM  

INSERT INTO #RsTotalItem
SELECT     
	EST_NF_ENT.CD_EMP, 
	EST_NF_ENT.CD_FILIAL, 
	EST_NF_ENT.CD_NF, 
	EST_NF_ENT.NR_NF,
	CASE WHEN PG_FORN.FLAG_ICMS_SUBST_PAGO_ANTERIOR = 0 THEN
		SUM(round((EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT),4) - EST_NF_ENT_IT.VLR_DESC) + 
		ISNULL(SUM(EST_NF_ENT_IT.VLR_ICMS_SUBS),0) + 
		ISNULL(SUM(EST_NF_ENT_IT.VLR_IPI),0) + ISNULL(SUM(VLR_ICMS_RET),0)
    ELSE
		SUM(round((EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT),4) - EST_NF_ENT_IT.VLR_DESC) + 
		ISNULL(SUM(EST_NF_ENT_IT.VLR_ICMS_SUBS),0) + 
		ISNULL(SUM(EST_NF_ENT_IT.VLR_IPI),0)
	END,
	SUM(round(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT,4)),
    SUM(EST_NF_ENT_IT.VLR_BASE_ICMS) AS VLR_BASE_ICMS ,
    SUM(EST_NF_ENT_IT.VLR_ICMS_IT) AS VLR_ICMS,
    SUM(EST_NF_ENT_IT.VLR_BASE_SUBS) AS VLR_BASE_ICMS_SUBS,
    SUM(EST_NF_ENT_IT.VLR_ICMS_SUBS) AS VLR_ICMS_SUBS,
    SUM(EST_NF_ENT_IT.VLR_IPI) AS VLR_IPI,
	SUM(EST_NF_ENT_IT.VLR_DESC) AS VLR_DESC_IT,
	ISNULL(SUM(VLR_ICMS_RET),0) AS VLR_ICMS_RET,
	ISNULL(SUM(VLR_OUTRAS_DESPESAS),0) AS VLR_OUTRAS_DESPESAS,
	ISNULL(SUM(EST_NF_ENT_IT.VLR_FECOP_ST),0) AS VLR_FECOP_ST,
	ISNULL(SUM(EST_NF_ENT_IT.VLR_ICMS_DESONERADO),0) AS VLR_ICMS_DESONERADO,
	PG_FORN.FLAG_ICMS_SUBST_PAGO_ANTERIOR
FROM         
	EST_NF_ENT_IT INNER JOIN EST_NF_ENT ON 
	EST_NF_ENT_IT.CD_EMP = EST_NF_ENT.CD_EMP AND 
	EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND 
    EST_NF_ENT_IT.CD_NF = EST_NF_ENT.CD_NF
	INNER JOIN EST_NF_ENT_PG_FORN ON
	EST_NF_ENT.CD_EMP = EST_NF_ENT_PG_FORN.CD_EMP AND
	EST_NF_ENT.CD_FILIAL = EST_NF_ENT_PG_FORN.CD_FILIAL AND
	EST_NF_ENT.CD_NF = EST_NF_ENT_PG_FORN.CD_NF 
	INNER JOIN PG_FORN ON
	EST_NF_ENT_PG_FORN.CD_EMP = PG_FORN.CD_EMP AND
	EST_NF_ENT_PG_FORN.CD_FORN = PG_FORN.CD_FORN
WHERE     
	EST_NF_ENT.CD_EMP = @CD_EMP 
	AND EST_NF_ENT.DT_ENT BETWEEN  @DT_INI AND @DT_FIM 
	AND EST_NF_ENT.CD_FILIAL=CASE WHEN @CD_FILIAL = 0 THEN EST_NF_ENT.CD_FILIAL ELSE @CD_FILIAL END 
	AND EST_NF_ENT.NR_NF=CASE WHEN @NR_NF = 0 THEN EST_NF_ENT.NR_NF ELSE @NR_NF END 
GROUP BY 
	EST_NF_ENT.CD_EMP, 
	EST_NF_ENT.CD_FILIAL, 
	EST_NF_ENT.CD_NF,
	PG_FORN.FLAG_ICMS_SUBST_PAGO_ANTERIOR,
	EST_NF_ENT.NR_NF

IF @ANALISE=1 
begin
	SELECT
		e.CD_FILIAL,
		e.NR_NF,
		ROUND((i.VLR_TOTAL_NF + e.VLR_FRETE + CASE WHEN ISNULL(e.VLR_OUTROS,0)=0 THEN  
													i.VLR_OUTRAS_DESPESAS 
											  ELSE 
													e.VLR_OUTROS 
											  END) - e.VLR_DESC_CAB,2) AS VLR_NF_ITENS,
		VLR_NF AS VLR_NF_CAB ,
		ROUND(i.VLR_TOTAL_PROD,2) AS VLR_TOT_PROD_ITENS,
		e.VLR_TOT_PROD AS VLR_TOT_PROD_CAB,
		i.VLR_DESC_IT,
		ROUND(i.VLR_TOTAL_PROD,2) - i.VLR_DESC_IT AS VLR_TOT_PROD_LIQ,
		i.VLR_BASE_ICMS AS VLR_BASE_ICMS_ITENS,
		 e.VLR_BASE_ICMS AS VLR_BASE_ICMS_CAB,
		i.VLR_ICMS AS VLR_ICMS_ITENS,
		e.VLR_ICMS AS VLR_ICMS_CAB,
		i.VLR_BASE_ICMS_SUBS AS VLR_BASE_ICMS_SUBS,
		e.VLR_BASE_ICMS_SUBS AS VLR_BASE_ICMS_SUBS_CAB,
		i.VLR_ICMS_SUBS AS VLR_ICMS_SUBS,
		e.VLR_ICMS_SUBS AS VLR_ICMS_SUBS_CAB,
		i.VLR_ICMS_RET AS VLR_ICMS_RET_ITENS,
		i.VLR_IPI AS VLR_IPI_ITENS,
		e.VLR_IPI AS VLR_IPI_CAB,
		i.VLR_OUTRAS_DESPESAS AS VLR_OUTRAS_DESPESAS_ITENS,
	    e.VLR_OUTROS AS VLR_OUTRAS_DESPESAS_CAB,
		ROUND((i.VLR_TOTAL_NF + e.VLR_FRETE + CASE WHEN ISNULL(e.VLR_OUTROS,0)=0 THEN 
													i.VLR_OUTRAS_DESPESAS 
											   ELSE 
													e.VLR_OUTROS 
											   END ) - e.VLR_DESC_CAB,2) - VLR_NF AS VLR_DIF,
		I.FLAG_ICMS_SUBST_PAGO_ANTERIOR
	FROM
		#RsTotalItem i INNER JOIN EST_NF_ENT e ON
		i.CD_EMP = e.CD_EMP AND
		i.CD_FILIAL = e.CD_FILIAL AND
		i.CD_NF = e.CD_NF
	--where ((i.VLR_TOTAL_NF + e.VLR_FRETE + CASE WHEN ISNULL(e.VLR_OUTROS,0)=0 THEN  i.VLR_OUTRAS_DESPESAS ELSE e.VLR_OUTROS END) - e.VLR_DESC_CAB) <> e.VLR_NF 
	ORDER BY
			VLR_DIF
end
IF @ANALISE=0
begin
	UPDATE
		e
	SET
		e.VLR_NF = ROUND(((i.VLR_TOTAL_NF + e.VLR_FRETE + CASE WHEN ISNULL(e.VLR_OUTROS,0)=0 THEN 
															   i.VLR_OUTRAS_DESPESAS 
														   ELSE 
																e.VLR_OUTROS 
															END ) - e.VLR_DESC_CAB),2),
		e.VLR_TOT_PROD =   ROUND(i.VLR_TOTAL_PROD,2),
		e.VLR_BASE_ICMS  = i.VLR_BASE_ICMS,
		e.VLR_ICMS	= i.VLR_ICMS,
		e.VLR_BASE_ICMS_SUBS	= i.VLR_BASE_ICMS_SUBS,
		e.VLR_ICMS_SUBS	= i.VLR_ICMS_SUBS,
		e.VLR_IPI	= i.VLR_IPI,
		e.FLAG_NF_PROD_TOTAL_BRUTO=1
	FROM
		#RsTotalItem i INNER JOIN EST_NF_ENT e ON
		i.CD_EMP = e.CD_EMP AND
		i.CD_FILIAL = e.CD_FILIAL AND
		i.CD_NF = e.CD_NF
	WHERE
		(ROUND(((i.VLR_TOTAL_NF + e.VLR_FRETE + CASE WHEN ISNULL(e.VLR_OUTROS,0)=0 THEN 
													i.VLR_OUTRAS_DESPESAS 
												ELSE 
													e.VLR_OUTROS 
												END ) - e.VLR_DESC_CAB),2) <> VLR_NF
		OR ROUND(i.VLR_TOTAL_PROD,2) <> e.VLR_TOT_PROD
		OR ROUND(i.VLR_BASE_ICMS,2) <> e.VLR_BASE_ICMS
		OR ROUND(i.VLR_ICMS,2) <> e.VLR_ICMS
		OR ROUND(i.VLR_BASE_ICMS_SUBS,2)= e.VLR_BASE_ICMS_SUBS
		OR ROUND(i.VLR_ICMS_SUBS,2)	= e.VLR_ICMS_SUBS
		OR ROUND(i.VLR_IPI,2)	= e.VLR_IPI)
end
DROP TABLE #RsTotalItem