IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_ENT_IT_RECALCULO_CUSTO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_ENT_IT_RECALCULO_CUSTO]
GO

CREATE PROCEDURE [dbo].[P_EST_NF_ENT_IT_RECALCULO_CUSTO] (@CD_EMP INT, 
														  @CD_FILIAL INT, 
														  @MES INT, 
														  @ANO INT, 
														  @ANALISE INT=0,
														  @DT_INI DATETIME=NULL,
														  @DT_FIM DATETIME=NULL,
														  @CD_PROD INT=0,
														  @CD_NF INT=0)  WITH ENCRYPTION AS  
	DECLARE @DTINI AS DATETIME, @DTFIM AS DATETIME, @DTATUAL AS DATETIME;
	SET @DTATUAL= CONVERT(CHAR(10),GETDATE(),112)
	
	DECLARE @FLAG_GERA_RESSARCIMENTO_ICMS_ST_RET INT, @FLAG_DEDUZ_ICMS_TRANSF INT , @ICMS_TRANSF VARCHAR(10)
	DECLARE @ALIQ_ICMS MONEY
SET NOCOUNT ON 	
	IF @MES > 0 
	begin
		SET @DTINI  =  CONVERT(VARCHAR, @ANO*100+@MES ) + '01';
		SET @DTFIM  =  dbo.F_FINAL_MES(@DTINI);
	end

	IF @MES = 0 
	begin
		SET @DTINI  =  @DT_INI;
		SET @DTFIM  =  @DT_FIM;
	end

	SET @FLAG_GERA_RESSARCIMENTO_ICMS_ST_RET = ISNULL((SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'FLAG_GERA_RESSARCIMENTO_ICMS_ST_RET', 1, @CD_FILIAL)),0)

	----------------------------------------------------------------------------------------------------
	--Valida se gera credito icms op. transferencias.

	SET @ICMS_TRANSF = (SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'FLAG_DEDUZ_ICMS_TRANSF_FILIAL_ENTRADA', 1, @CD_FILIAL));	 
	IF @ICMS_TRANSF  IS NULL OR LTRIM(RTRIM(@ICMS_TRANSF))=''
		SET @ICMS_TRANSF = ISNULL( (SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'FLAG_DEDUZ_ICMS_TRANSF_FILIAL_ENTRADA', 0, @CD_FILIAL)),0)
		  
	SET @FLAG_DEDUZ_ICMS_TRANSF =@ICMS_TRANSF;

	----------------------------------------------------------------------------------------------------
	SET @ALIQ_ICMS = ISNULL((SELECT TOP 1 ALIQ_ICMS
								FROM
								EST_TRIB_ALIQ_ICMS
								WHERE 
								UF_ORIG = UF_DEST
								AND UF_ORIG = (SELECT TOP 1 UF collate SQL_Latin1_General_CP1_CI_AS
												FROM   PRC_FILIAL F INNER JOIN GLB_CID C ON 
														F.CD_CID = C.CD_CID
														WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL)),0);
	
	IF  OBJECT_ID('TEMPDB.DBO.#TBL_RECALCULO') IS NOT NULL
	BEGIN
		 DROP TABLE #TBL_RECALCULO
	END
	CREATE TABLE #RsNota(CD_EMP INT,
						 CD_FILIAL INT,
						 CD_NF INT,
						 DT_ENT DATETIME,
						 FLAG_NAO_CREDITA_IMPOSTO INT,
						 CD_FORN INT,
						 NR_NF INT,
						 DEST_ORIG INT,
						 TP_MOV INT,
						 DS_MOV_OP_USUAL VARCHAR (200),
						 TP_MOV_OP_TRANSA INT,
						 FLAG_CUSTO_ZERO_ENT INT,
						 APLICATIVO VARCHAR(100),
						 FLAG_NAO_ABATER_ICMS_DESONERADO INT)
						 
	IF @CD_NF > 0
		BEGIN
			INSERT INTO #RsNota
				SELECT 
					E.CD_EMP,
					E.CD_FILIAL,
					E.CD_NF,
					E.DT_ENT,
					O.FLAG_NAO_CREDITA_IMPOSTO,
					E.CD_FORN,
					E.NR_NF,
					O.DEST_ORIG,
					O.TP_MOV,
					O.DS_MOV_OP_USUAL,
					O.TP_MOV_OP_TRANSA,
					O.FLAG_CUSTO_ZERO_ENT,
					E.APLICATIVO,
					case when O.DEST_ORIG = 1 THEN 
						ISNULL(F.FLAG_NAO_ABATER_ICMS_DESONERADO,1)  
						ELSE 1 END FLAG_NAO_ABATER_ICMS_DESONERADO
				FROM		
					EST_NF_ENT E INNER JOIN GLB_MOV_OP O ON 
					E.CD_NT_MOV = O.CD_MOV_OP
					LEFT JOIN PG_FORN F ON 
					E.CD_EMP = F.CD_EMP AND
					E.CD_FORN = F.CD_FORN
				WHERE	
					E.CD_EMP =@CD_EMP 
					AND E.CD_FILIAL = @CD_FILIAL 
					AND E.CD_NF = @CD_NF 
					AND o.tp_mov not in ( 52) -- adjudicacao de credito			
		END 
		ELSE
		BEGIN
			INSERT INTO #RsNota
				SELECT 
					E.CD_EMP,
					E.CD_FILIAL,
					E.CD_NF,
					E.DT_ENT,
					O.FLAG_NAO_CREDITA_IMPOSTO,
					E.CD_FORN,
					E.NR_NF,
					O.DEST_ORIG,
					O.TP_MOV,
					O.DS_MOV_OP_USUAL,
					O.TP_MOV_OP_TRANSA,
					O.FLAG_CUSTO_ZERO_ENT,
					E.APLICATIVO,
					case when O.DEST_ORIG = 1 THEN 
						ISNULL(F.FLAG_NAO_ABATER_ICMS_DESONERADO,1)  
						ELSE 1 END FLAG_NAO_ABATER_ICMS_DESONERADO
				FROM		
					EST_NF_ENT E INNER JOIN GLB_MOV_OP O ON 
					E.CD_NT_MOV = O.CD_MOV_OP
					LEFT JOIN PG_FORN F ON 
					E.CD_EMP = F.CD_EMP AND
					E.CD_FORN = F.CD_FORN
				WHERE	
					E.CD_EMP =@CD_EMP 
					AND E.CD_FILIAL = @CD_FILIAL 
					AND o.tp_mov not in ( 52) -- adjudicacao de credito			
					AND E.DT_ENT BETWEEN @DTINI AND @DTFIM		
		END
			
	PRINT 'Recalculando o custo nas entrada da filial ' + convert(varchar, @CD_FILIAL) + '  -  Periodo ' + convert(varchar, @DTINI,112) + ' a ' +  convert(varchar, @DTFIM,112) + ' | Time : ' + convert(varchar,getdate(),108)

	SELECT 
		E.CD_EMP
		,E.CD_NF
		,E.CD_FILIAL
		,E.NR_NF
		,E.CD_FORN
		,I.CD_PROD
		,I.QT_IT
		,I.VLR_IT 
		,I.VLR_DESC
		,I.VLR_IPI / I.QT_IT  AS VLR_IPI
		,I.VLR_ICMS_SUBS / I.QT_IT AS VLR_ICMS_SUBS
		,CASE WHEN ISNULL(E.FLAG_NAO_CREDITA_IMPOSTO,0) = 0 THEN I.VLR_BASE_ICMS ELSE 0 END AS VLR_BASE_ICMS
		,CASE WHEN ISNULL(E.FLAG_NAO_CREDITA_IMPOSTO,0) = 0 THEN I.VLR_ICMS_IT  ELSE 0 END AS VLR_ICMS_IT
		
		,(CASE WHEN ISNULL(I.VLR_FRETE_IT,0) > 0 THEN ISNULL(I.VLR_FRETE_IT ,0)
						ELSE ISNULL(I.VLR_PROD_FRETE,0) END)/ I.QT_IT AS VLR_FRETE_IT

		,I.VLR_OUTRAS_DESPESAS / I.QT_IT AS VLR_OUTRAS_DESPESAS
		,ISNULL(dr.VLR_ICMS_SUBS,0) AS VLR_ICMS_SUBS_DARE
		,I.VLR_PRC_CUSTO
		,ISNULL(I.VLR_CUSTO_SEM_ST,0) AS VLR_CUSTO_SEM_ST
		,CASE WHEN ISNULL(E.FLAG_CUSTO_ZERO_ENT,0)= 0 THEN	
			(
				(
					(
						(I.VLR_IT*I.QT_IT)
						- I.VLR_DESC
					)
						
					+ ISNULL(I.VLR_IPI,0) 
					+
					(--DEVOLUCOES DE VENDAS 
						CASE WHEN E.TP_MOV IN (11,31) THEN ISNULL(I.VLR_ICMS_SUBS,0)*-1
						ELSE ISNULL(I.VLR_ICMS_SUBS,0)  END
					)
					+ (CASE WHEN ISNULL(I.VLR_FRETE_IT,0) > 0 THEN ISNULL(I.VLR_FRETE_IT,0) 
						ELSE ISNULL(I.VLR_PROD_FRETE,0) END)
					+ ISNULL(I.VLR_OUTRAS_DESPESAS,0)
					+ ISNULL(dr.VLR_ICMS_SUBS,0)+ISNULL(I.VLR_FECOP_ST,0)
				)
			- 
				(
					
					(
						CASE WHEN (T.SUBS_TRIB + T.SUBS_TRIB_ANT) <> 0 OR ISNULL(dr.VLR_ICMS_SUBS,0) > 0 THEN 
							--DEVOLUCAO de vendas
							CASE WHEN E.TP_MOV IN (11,31) THEN 
								I.VLR_ICMS_IT
							ELSE
								0 
							END
						ELSE
							CASE WHEN E.TP_MOV IN (10,23,26) AND @FLAG_DEDUZ_ICMS_TRANSF=0 THEN 
								0 
							ELSE 
								CASE WHEN ISNULL(FLAG_TIPO_GERACAO_CUSTO_ICMS_CREDITO,0)=0 THEN
									CASE WHEN ISNULL(E.FLAG_NAO_CREDITA_IMPOSTO,0) = 0 THEN
										I.VLR_ICMS_IT +ISNULL(I.VLR_FECOP,0)
									ELSE
										0
									END
								ELSE
									0
								END
							END
						END
					)	
					+(
						CASE WHEN F.REGIME_TRIBUTARIO = 4 THEN
							(ISNULL(i.VLR_PIS,0) + ISNULL(i.VLR_COFINS,0)) 
						ELSE 0 END
					  )
					+ (
						CASE WHEN @FLAG_GERA_RESSARCIMENTO_ICMS_ST_RET = 1 AND ISNULL(P.FLAG_PERMITE_RESSARCIMENTO_ICMS,0) = 1 THEN
							CASE WHEN ISNULL(I.VLR_ICMS_RET,0) > 0 THEN I.VLR_ICMS_RET ELSE (ISNULL(I.VLR_BASE_ICMS_RET,0) * @ALIQ_ICMS / 100.0000) END
						ELSE
							0
						END 
					   )  
					+ CASE WHEN E.FLAG_NAO_ABATER_ICMS_DESONERADO = 1  THEN 0
						ELSE ISNULL(I.VLR_ICMS_DESONERADO,0) END

				)
			)	/I.QT_IT  
			ELSE
				0
			END AS   VLR_CUSTO_RECALC


			,CASE WHEN ISNULL(E.FLAG_CUSTO_ZERO_ENT,0)= 0 THEN	
			(
				(
					(
						(I.VLR_IT*I.QT_IT)
						- I.VLR_DESC
					)
						
					+ I.VLR_IPI 
					+ (CASE WHEN ISNULL(I.VLR_PROD_FRETE,0) > 0 THEN ISNULL(I.VLR_PROD_FRETE,0)
						ELSE ISNULL(I.VLR_PROD_FRETE,0) END)
					+ I.VLR_OUTRAS_DESPESAS+ISNULL(I.VLR_FECOP_ST,0) 
				)
			- 
				CASE WHEN  F.REGIME_TRIBUTARIO = 1 THEN 0 --Se for simples nao tem direito credito ICMS.
				ELSE(
					   (
						    CASE WHEN (T.SUBS_TRIB + T.SUBS_TRIB_ANT) <> 0 OR ISNULL(dr.VLR_ICMS_SUBS,0) > 0 THEN 
							    0 
						    ELSE
							    CASE WHEN E.TP_MOV IN (10,23,26) AND @FLAG_DEDUZ_ICMS_TRANSF=0THEN 
								    0 
							    ELSE 
								    CASE WHEN ISNULL(FLAG_TIPO_GERACAO_CUSTO_ICMS_CREDITO,0)=0  THEN
									    ISNULL(I.VLR_ICMS_IT,0) +ISNULL(I.VLR_FECOP,0)
								    ELSE
									    0
								    END
							    END
						    END
					    )	
					    +(
						    CASE WHEN F.REGIME_TRIBUTARIO = 4 THEN
							    (ISNULL(i.VLR_PIS,0) + ISNULL(i.VLR_COFINS,0)) 
						    ELSE 0 END
						 )
					    +(
						    CASE WHEN E.DEST_ORIG <> 2 THEN
							    ISNULL(i.VLR_ICMS_RET,0) 
						    ELSE 0 END
						 )
						 + CASE WHEN E.FLAG_NAO_ABATER_ICMS_DESONERADO = 1  THEN 0
						ELSE ISNULL(I.VLR_ICMS_DESONERADO,0) END
				    )
				    END

			)	/I.QT_IT  
			ELSE
				0
			END AS   VLR_CUSTO_SEM_ST_RECALC 
		,E.DS_MOV_OP_USUAL
		,E.TP_MOV_OP_TRANSA
		,E.TP_MOV
		,I.CD_TRIB
		,CASE WHEN ISNULL(I.VLR_DESC_FINAN,0) = 0 AND ISNULL(I.PERC_DESC_FINAN,0) > 0 THEN
			   CONVERT(MONEY, (I.VLR_TOT_IT * (I.PERC_DESC_FINAN/100.00))) 
			  ELSE  ISNULL(I.VLR_DESC_FINAN,0)  END VLR_DESC_FINAN
		,I.VLR_CUSTO_GER
		,T.ICMS	
		,T.SUBS_TRIB	
		,T.ICMS_RD	
		,T.SUBS_TRIB_ANT
		,T.FLAG_GERAR_ICMS_SUBS_DARE
		,e.APLICATIVO
		,ISNULL(i.VLR_PIS,0) AS VLR_PIS
	     ,ISNULL(i.VLR_COFINS,0) AS VLR_COFINS
		,ISNULL(E.FLAG_CUSTO_ZERO_ENT,0) AS FLAG_CUSTO_ZERO_ENT
		,ISNULL(FLAG_TIPO_GERACAO_CUSTO_ICMS_CREDITO,0) AS FLAG_TIPO_GERACAO_CUSTO_ICMS_CREDITO
		,F.REGIME_TRIBUTARIO
		,ISNULL(P.TIPO_TRIBUTACAO_PIS_COFINS_ENTRADA,0) TIPO_TRIBUTACAO_PIS_COFINS_ENTRADA
		,(((I.VLR_IT*I.QT_IT) - I.VLR_DESC)/I.QT_IT) VLR_IT_LIQ
		INTO #TBL_RECALCULO
	FROM 
		#RsNota E
		INNER JOIN EST_NF_ENT_IT I ON 
		E.CD_EMP		= I.CD_EMP AND 
		E.CD_FILIAL   = I.CD_FILIAL AND 
		E.CD_NF	    = I.CD_NF AND
		E.DT_ENT	    = i.DT_ENT
		INNER JOIN EST_TRIB T ON 
		I.CD_TRIB = T.CD_TRIB
		LEFT OUTER JOIN EST_NF_ENT_DAR_IT dr ON
		i.CD_EMP = dr.CD_EMP AND
		i.CD_FILIAL = dr.CD_FILIAL AND
		i.CD_NF = dr.CD_NF AND
		i.CD_PROD = dr.CD_PROD
		INNER JOIN PRC_FILIAL F ON
		E.CD_EMP = F.CD_EMP AND
		E.CD_FILIAL = F.CD_FILIAL
		INNER JOIN EST_PROD P ON 
		I.CD_EMP = P.CD_EMP AND 
		I.CD_PROD = P.CD_PROD
	WHERE
		I.CD_EMP =@CD_EMP
		AND I.CD_FILIAL = @CD_FILIAL
		and I.QT_IT > 0
		AND I.CD_PROD= CASE WHEN @CD_PROD>0 THEN @CD_PROD ELSE I.CD_PROD END

	DELETE FROM #TBL_RECALCULO WHERE VLR_CUSTO_RECALC < 0;
/*****************************************************************************************/
--Abatimento PIS COFINS Custo
-- Lojas BA Indiana
/*****************************************************************************************/
	
	IF EXISTS (SELECT A.CD_EMP
				FROM 
					   #TBL_RECALCULO A
					   JOIN GLB_CONFIG_PRECO_RESSUP_PRC_FILIAL B ON  
					   A.CD_FILIAL = B.CD_FILIAL
				    WHERE
					   a.CD_FILIAL = @CD_FILIAL)
	BEGIN

	   IF EXISTS(SELECT A.CD_FILIAL FROM
				    #TBL_RECALCULO  a
				WHERE
				    a.CD_FORN IN (SELECT CD_FILIAL FROM V_PRC_FILIAL_RELACAO_DEPOSITOS)
								)
	   BEGIN
		  
		  declare @Aliq money=0.0925 --(9.25%) -- SELECT sum(PERC_IMP) 
										  -- FROM GLB_IMPOSTOS WHERE TIPO_IMPOSTO IN (1,2)
		  update a
		  set				-- Abate credito de pis cofins do custo
			 a.VLR_CUSTO_RECALC=a.VLR_CUSTO_RECALC-(a.VLR_IT_LIQ*0.0925)
		  from
			 #TBL_RECALCULO a
			 JOIN GLB_CONFIG_PRECO_RESSUP_PRC_FILIAL c ON  
			 A.CD_FILIAL = c.CD_FILIAL
			 JOIN V_PRC_FILIAL_RELACAO_DEPOSITOS  b on
			 a.cd_forn=b.cd_filial --Transferencia do CD
		  WHERE 
			 A.TIPO_TRIBUTACAO_PIS_COFINS_ENTRADA=0--Credito 
			 AND a.CD_FILIAL = @CD_FILIAL; 
	   END
	END


/*****************************************************************************************/
	IF @ANALISE =0
		BEGIN
		
			SELECT 
				*
				,r.VLR_PRC_CUSTO - r.VLR_CUSTO_RECALC DIFERENCA
			FROM #TBL_RECALCULO r
						
			ORDER BY CD_FILIAL,CD_NF DESC
		END
	ELSE
		BEGIN
			
			INSERT INTO EST_NF_ENT_IT_HIST_CUSTO (CD_EMP,CD_FILIAL,CD_NF,CD_PROD,
													VLR_CUSTO_NOVO, VLR_CUSTO_ANT, DT_CAD)
			SELECT
				R.CD_EMP,
				R.CD_FILIAL,
				R.CD_NF,
				R.CD_PROD,
				ISNULL(R.VLR_CUSTO_RECALC,0)	VLR_CUSTO_NOVO,
				ISNULL(R.VLR_PRC_CUSTO,0) VLR_CUSTO_ANT,
				GETDATE() DT_CAD
			FROM
				EST_NF_ENT_IT I 
				INNER JOIN #TBL_RECALCULO R ON 
				I.CD_EMP	= R.CD_EMP	AND
				I.CD_FILIAL = R.CD_FILIAL AND
				I.CD_NF		= R.CD_NF AND
				I.CD_PROD	= R.CD_PROD
				
			WHERE
				I.CD_EMP =@CD_EMP
				AND I.CD_FILIAL =@CD_FILIAL			
				AND I.QT_IT > 0	
				AND round(R.VLR_PRC_CUSTO,4) - round(R.VLR_CUSTO_RECALC,4)  <> 0 ;

			UPDATE 
				I
			SET
				VLR_PRC_CUSTO			= ISNULL(R.VLR_CUSTO_RECALC,0),
				VLR_CUSTO_ANT_RECALC	= ISNULL(R.VLR_PRC_CUSTO,0),
				VLR_DESC_FINAN			= R.VLR_DESC_FINAN,
				VLR_CUSTO_GER			= R.VLR_CUSTO_RECALC - (ISNULL(R.VLR_DESC_FINAN,0)/i.QT_IT),
				VLR_CUSTO_SEM_ST    	= R.VLR_CUSTO_SEM_ST_RECALC,
				VLR_BASE_ICMS			= R.VLR_BASE_ICMS,
				VLR_ICMS_IT  			= R.VLR_ICMS_IT  
			FROM
				EST_NF_ENT_IT I 
				INNER JOIN #TBL_RECALCULO R ON 
				I.CD_EMP	= R.CD_EMP	AND
				I.CD_FILIAL = R.CD_FILIAL AND
				I.CD_NF		= R.CD_NF AND
				I.CD_PROD	= R.CD_PROD
				
			WHERE
				I.CD_EMP =@CD_EMP
				AND I.CD_FILIAL =@CD_FILIAL			
				AND I.QT_IT > 0	
				AND (	(round(R.VLR_PRC_CUSTO,4) - round(R.VLR_CUSTO_RECALC,4)  <> 0)
						OR (I.VLR_CUSTO_GER <> (round(R.VLR_CUSTO_RECALC,4) - (ISNULL(R.VLR_DESC_FINAN,0)/i.QT_IT)))
						OR (round(R.VLR_CUSTO_SEM_ST,4) - round(R.VLR_CUSTO_SEM_ST_RECALC,4) <> 0)
						) ;

	END
 DROP TABLE #TBL_RECALCULO
 DROP TABLE #RsNota