/****** Object:  StoredProcedure [dbo].[P_EST_NF_SAI_IT_RECALCULO_CUSTO]    Script Date: 11/28/2013 09:35:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_SAI_IT_RECALCULO_CUSTO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_SAI_IT_RECALCULO_CUSTO]
GO
GO
GO
GO
--versao 2926
-- P_EST_NF_SAI_IT_RECALCULO_CUSTO 1,16,5,2015,1
CREATE PROCEDURE [dbo].[P_EST_NF_SAI_IT_RECALCULO_CUSTO] (@CD_EMP INT, 
														  @CD_FILIAL INT, 
														  @MES INT, 
														  @ANO INT, 
														  @ANALISE INT=0,
														  @DT_INI DATETIME=NULL,
														  @DT_FIM DATETIME=NULL, 
														  @NR_NF INT =0,
														  @CD_PROD INT=0)  WITH ENCRYPTION AS  
	DECLARE @DTINI AS DATETIME, @DTFIM AS DATETIME, @NaoConsideraImpostoRecupCustoSaida INT;
	DECLARE @FLAG_GERA_RESSARCIMENTO_ICMS_ST_RET INT;

	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)
	set @NaoConsideraImpostoRecupCustoSaida = ISNULL((select VALOR from PRC_EMP_CONFIG 
																WHERE CD_CHAVE ='NAO_CONSIDERA_IMPOSTO_RECUP_CUSTO_SAIDA'),0);
	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

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_RECALCULO') IS NOT NULL
	BEGIN
		 DROP TABLE #TBL_RECALCULO
	END
	
	PRINT 'Recalculando o custo de saida da filial ' + convert(varchar, @CD_FILIAL) + '  -  Periodo ' + convert(varchar, @DTINI,112) + ' a ' +  convert(varchar, @DTFIM,112) + ' | Time : ' + convert(varchar,getdate(),108)
 
	SELECT 
		S.CD_EMP
		,S.CD_NF
		,S.CD_FILIAL
		,S.NF_NF AS NR_NF
		,S.CD_CLI
		,i.DT_EMI_NF
		,I.CD_PROD
		,I.QT_IT
		,I.VLR_IT 
		,I.VLR_DESC_IT
		--,I.VLR_IPI 
		,I.VLR_ICMS_SUBS
		,I.VLR_ICMS_IT
		,ISNULL(I.VLR_PROD_FRETE,0) AS VLR_PROD_FRETE_IT
		,I.VLR_OUTROS_DESPESAS
		,ISNULL(i.VLR_PIS,0) AS VLR_PIS
		,ISNULL(i.VLR_COFINS,0) AS VLR_COFINS
		,I.VLR_CUSTO,	
		ROUND((
				(
					(
						(I.VLR_IT*I.QT_IT)
						- I.VLR_DESC_IT )

					+  (I.PERC_IPI * ((I.VLR_IT*I.QT_IT)- I.VLR_DESC_IT))/100 

					+ (CASE WHEN @FLAG_GERA_RESSARCIMENTO_ICMS_ST_RET = 1 
							AND ISNULL(P.FLAG_PERMITE_RESSARCIMENTO_ICMS,0) = 1 and O.DEST_ORIG=2 THEN
							0
							ELSE I.VLR_ICMS_SUBS  END)
					+ ISNULL(I.VLR_PROD_FRETE,0) 
					+ I.VLR_OUTROS_DESPESAS
				)
			+ 
				(
					(CASE WHEN @FLAG_GERA_RESSARCIMENTO_ICMS_ST_RET = 1 
								AND ISNULL(P.FLAG_PERMITE_RESSARCIMENTO_ICMS,0) = 1 and O.DEST_ORIG=2 THEN --Apenas nas transf.
						(I.VLR_ICMS_IT *-1)
					ELSE 
						((CASE WHEN ISNULL(I.VLR_ICMS_SUBS,0)=0 THEN
								I.VLR_ICMS_IT 
							ELSE 0
						END)	
						+
						(ISNULL(i.VLR_PIS,0) + ISNULL(i.VLR_COFINS,0))) 
					
						* (CASE WHEN @NaoConsideraImpostoRecupCustoSaida = 1 then -1	
								else 1 end) 
					END)

				)
			)/I.QT_IT,4)  AS   VLR_CUSTO_RECALC 


		,O.DS_MOV_OP_USUAL
		,O.TP_MOV_OP_TRANSA
		,O.TP_MOV
		,I.CD_TRIB
		
		,T.ICMS	
		,T.SUBS_TRIB	
		,T.ICMS_RD	
		,T.SUBS_TRIB_ANT
		,T.FLAG_GERAR_ICMS_SUBS_DARE
		,S.APLICATIVO
		INTO #TBL_RECALCULO
	FROM 
		EST_NF_SAI S
		INNER JOIN EST_NF_SAI_IT I ON 
		S.CD_EMP		= I.CD_EMP AND 
		S.CD_FILIAL		= I.CD_FILIAL AND 
		S.CD_NF			= I.CD_NF AND
		S.DT_EMI_NF			= I.DT_EMI_NF

		INNER JOIN EST_PROD P  ON 
		I.CD_EMP		= P.CD_EMP AND 
		I.CD_PROD		= P.CD_PROD

		INNER JOIN GLB_MOV_OP O ON 
		S.CD_NT_MOV = O.CD_MOV_OP
		
		INNER JOIN EST_TRIB T ON 
		I.CD_TRIB = T.CD_TRIB
		INNER JOIN PRC_FILIAL F ON
		S.CD_EMP = F.CD_EMP AND
		S.CD_FILIAL = F.CD_FILIAL
	WHERE
		i.CD_EMP =@CD_EMP
		AND I.QT_IT > 0
		AND i.CD_FILIAL = @CD_FILIAL
		AND i.DT_EMI_NF BETWEEN @DTINI AND @DTFIM
		AND (S.NOTA_FISCAL_LIVRE=0 OR S.NOTA_FISCAL_LIVRE IS NULL)
		AND I.CD_PROD= CASE WHEN @CD_PROD > 0 THEN @CD_PROD ELSE I.CD_PROD END

	IF @ANALISE =0
		BEGIN
		
			SELECT 
				*
				,VLR_CUSTO - VLR_CUSTO_RECALC DIFERENCA
			FROM #TBL_RECALCULO
			WHERE
				VLR_CUSTO - VLR_CUSTO_RECALC  <> 0
				AND NR_NF = CASE WHEN @NR_NF > 0 THEN @NR_NF ELSE NR_NF END
			ORDER BY 
				DIFERENCA 
			DESC
		END
	ELSE
		BEGIN
			
			DELETE FROM #TBL_RECALCULO WHERE VLR_CUSTO_RECALC < 0;
			PRINT 'Atualiza o custo de saida da filial ' + convert(varchar, @CD_FILIAL) + '  -  Periodo ' + convert(varchar, @DTINI,112) + ' a ' +  convert(varchar, @DTFIM,112) + ' | Time : ' + convert(varchar,getdate(),108)
 

			UPDATE 
				I
			SET
				I.VLR_CUSTO  = R.VLR_CUSTO_RECALC
			FROM
				EST_NF_SAI_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.DT_EMI_NF	= R.DT_EMI_NF AND
				I.CD_PROD	= R.CD_PROD
				
			WHERE
				I.CD_EMP =@CD_EMP
				AND R.CD_FILIAL =@CD_FILIAL			
				AND R.QT_IT > 0	
				AND R.VLR_CUSTO - R.VLR_CUSTO_RECALC  <> 0;
		END