/****** Object:  StoredProcedure [dbo].[P_EST_NF_ENT_CORRIGE_NOTAS_ENTRADA]    Script Date: 11/25/2013 11:36:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_ENT_CORRIGE_NOTAS_ENTRADA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_ENT_CORRIGE_NOTAS_ENTRADA]
GO
--EXEC P_EST_NF_ENT_CORRIGE_NOTAS_ENTRADA 1, 999, 2216370,NULL,NULL
CREATE PROCEDURE [dbo].[P_EST_NF_ENT_CORRIGE_NOTAS_ENTRADA] @CD_EMP INT, 
														     @CD_FILIAL INT,
															 @CD_NF INT, 
															 @DT_INI DATETIME=NULL, 
															 @DT_FIM DATETIME=NULL AS
SET NOCOUNT ON  
CREATE TABLE #RsNotasCab  (CD_EMP INT,
					   CD_FILIAL INT,
					   CD_NF INT ,
					   VLR_NF MONEY,
					   VLR_TOT_PROD MONEY, 
					   VLR_DESC MONEY, 
					   VLR_BASE_ICMS MONEY,
					   VLR_ICMS MONEY,
					   VLR_IPI MONEY,
					   VLR_BASE_ICMS_SUBS MONEY,
					   VLR_ICMS_SUBS MONEY)  


CREATE TABLE #RsNotasItens (CD_EMP INT,
							CD_FILIAL INT,
							CD_NF INT,
							CD_PROD INT,
							SEQ_ITEM INT,
							VLR_NF MONEY,
							VLR_TOT_PROD MONEY,
							VLR_DESC MONEY,
							VLR_BASE_ICMS MONEY,
							VLR_ICMS MONEY,
							VLR_IPI MONEY,
							VLR_BASE_ICMS_SUBS MONEY,
							VLR_ICMS_SUBS MONEY,
							VLR_BASE_ICMS_RET MONEY,
							VLR_ICMS_RET MONEY)

DECLARE @CD_EMP_CURSOR INT
DECLARE @CD_FILIAL_CURSOR INT
DECLARE @CD_NF_CURSOR INT
DECLARE @NR_NF_CURSOR INT
DECLARE @DT_ENT_NF_CURSOR DATETIME

IF @CD_NF >0 
begin
	SELECT
		@DT_INI=DT_ENT,
		@DT_FIM=DT_ENT
	FROM
		EST_NF_ENT
	WHERE
		CD_EMP =@CD_EMP
		AND CD_FILIAL = @CD_FILIAL
		AND CD_NF = @CD_NF
end

--Analisando data dos itens
UPDATE I
SET
	I.DT_ENT=S.DT_ENT 
FROM 
	EST_NF_ENT S
	INNER JOIN EST_NF_ENT_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_ENT	<> ISNULL(I.DT_ENT,'19000101')
WHERE
	S.CD_EMP = @CD_EMP
	AND S.CD_FILIAL = CASE WHEN @CD_FILIAL=0 THEN S.CD_FILIAL ELSE @CD_FILIAL END
	AND S.DT_ENT BETWEEN @DT_INI AND @DT_FIM 
	AND S.CD_NF =CASE WHEN @CD_NF=0 THEN S.CD_NF ELSE @CD_NF END

Print 'Buscando dados da nota fiscal  ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
DECLARE cNotaFiscal CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT
	EST_NF_ENT.CD_EMP,
	EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT.CD_NF,
	EST_NF_ENT.NR_NF,
	EST_NF_ENT.DT_ENT
FROM
	EST_NF_ENT INNER JOIN EST_NF_ENT_NFE_PEND ON
	EST_NF_ENT.CD_EMP = EST_NF_ENT_NFE_PEND.CD_EMP AND
	EST_NF_ENT.CD_FILIAL = EST_NF_ENT_NFE_PEND.CD_FILIAL AND
	EST_NF_ENT.CD_NF = EST_NF_ENT_NFE_PEND.CD_NF
WHERE
	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.DT_ENT BETWEEN @DT_INI AND @DT_FIM 
	AND EST_NF_ENT.CD_NF =CASE WHEN @CD_NF=0 THEN EST_NF_ENT.CD_NF ELSE @CD_NF END	
	AND EST_NF_ENT_NFE_PEND.PEND_SOLUC=1
ORDER BY
	EST_NF_ENT.CD_FILIAL,
	EST_NF_ENT.NR_NF

OPEN cNotaFiscal
FETCH NEXT FROM cNotaFiscal INTO @CD_EMP_CURSOR, @CD_FILIAL_CURSOR,@CD_NF_CURSOR,@NR_NF_CURSOR,@DT_ENT_NF_CURSOR

WHILE @@FETCH_STATUS=0
BEGIN
Print 'Analisando dados nota fiscal '  + convert(varchar(10),@NR_NF_CURSOR) + ' emitida em ' + CONVERT(VARCHAR(10),@DT_ENT_NF_CURSOR, 103) + ' tempo processamento.: '  + CONVERT(VARCHAR(8), GETDATE(), 114) 
print 'Analisando percentual de desconto do itens tempo processamento.: '  + CONVERT(VARCHAR(8), GETDATE(), 114)
UPDATE
	EST_NF_ENT_IT
SET
	EST_NF_ENT_IT.PERC_DESC = ((EST_NF_ENT_IT.VLR_DESC / EST_NF_ENT_IT.QT_IT) / EST_NF_ENT_IT.VLR_IT) * 100.00
FROM        
	EST_NF_ENT_IT  
WHERE       
	EST_NF_ENT_IT.CD_EMP = @CD_EMP_CURSOR
	AND EST_NF_ENT_IT.CD_NF=@CD_NF_CURSOR
	AND EST_NF_ENT_IT.PERC_DESC = 0
	AND EST_NF_ENT_IT.VLR_DESC > 0
	AND EST_NF_ENT_IT.CD_FILIAL =@CD_FILIAL_CURSOR; 

Print 'Analisando dados dos itens  tempo processamento.: '  + CONVERT(VARCHAR(8), GETDATE(), 114) 
INSERT INTO #RsNotasItens
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	CD_PROD,
	SEQ_ITEM,
	SUM(ROUND((VLR_TOT_IT-VLR_DESC)+VLR_ICMS_SUBS+(CASE WHEN QT_IT=0 THEN 1 ELSE QT_IT END * VLR_IT) * (PERC_IPI/100.00),2)) AS VLR_NF,
	SUM(ROUND(VLR_TOT_IT,2)) AS VLR_TOT_PROD,
	SUM(ROUND(VLR_DESC,2)) AS VLR_DESC,
    SUM(ROUND(VLR_BASE_ICMS,2)) AS VLR_BASE_ICMS,
	SUM(ROUND(((VLR_BASE_ICMS * ALIQ_ICMS)/100),2)) AS VLR_ICMS,
	SUM(ROUND((CASE WHEN QT_IT=0 THEN 1 ELSE QT_IT END * VLR_IT) * (PERC_IPI/100.00),2)) AS VLR_IPI,
	SUM(ROUND(VLR_BASE_SUBS,2)) AS VLR_BASE_ICMS_SUBS,
	SUM(ROUND(VLR_ICMS_SUBS,2)) AS VLR_ICMS_SUBS,
	SUM(ROUND(VLR_BASE_ICMS_RET,2)) AS VLR_BASE_ICMS_RET,
	SUM(ROUND(VLR_ICMS_RET,2)) AS VLR_ICMS_RET
FROM	
	EST_NF_ENT_IT INNER JOIN  EST_TRIB ON
	EST_NF_ENT_IT.CD_TRIB = EST_TRIB.CD_TRIB
WHERE
	EST_NF_ENT_IT.CD_EMP = @CD_EMP_CURSOR
	AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL_CURSOR
	AND EST_NF_ENT_IT.CD_NF=@CD_NF_CURSOR
	AND EST_NF_ENT_IT.DT_ENT = @DT_ENT_NF_CURSOR
GROUP BY
	CD_EMP,
	CD_FILIAL,
	CD_NF,
	CD_PROD,
	SEQ_ITEM 

PRINT 'Corrigindo itens da nota fiscal ' + convert(varchar(10),@NR_NF_CURSOR) + ' ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
UPDATE
	it 
SET  
	it.VLR_TOT_IT=i.VLR_TOT_PROD,
	it.VLR_DESC=	i.VLR_DESC, 
	it.VLR_BASE_ICMS=i.VLR_BASE_ICMS, 
	it.VLR_ICMS_IT=i.VLR_ICMS, 
	IT.VLR_BASE_SUBS=i.VLR_BASE_ICMS_SUBS, 
	IT.VLR_ICMS_SUBS=i.VLR_ICMS_SUBS,
	IT.ALIQ_ICMS = IT.ALIQ_ICMS,
	IT.PERC_REDU_BASE_ICMS= CASE WHEN i.VLR_BASE_ICMS=0 THEN 0 ELSE IT.PERC_REDU_BASE_ICMS  END,
	IT.PERC_REDU_BASE_SUBS= CASE WHEN i.VLR_BASE_ICMS=0 THEN 0 ELSE IT.PERC_REDU_BASE_SUBS  END,
	IT.ALIQ_ICMS_INTERNO= CASE WHEN i.VLR_BASE_ICMS=0 THEN 0 ELSE IT.ALIQ_ICMS_INTERNO  END
from
	#RsNotasItens i INNER JOIN EST_NF_ENT_IT it ON
	i.CD_EMP = it.CD_EMP AND
	i.CD_FILIAL  = it.CD_FILIAL AND
	i.CD_NF = it.CD_NF AND
	i.CD_PROD = it.CD_PROD AND
	I.SEQ_ITEM = it.SEQ_ITEM 

Print 'Corrigindo dados do cabecalho ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
INSERT INTO #RsNotasCab
select
	i.CD_EMP,
	i.CD_FILIAL,
	i.CD_NF,
	SUM(VLR_NF),
	SUM(VLR_TOT_PROD),
	SUM(i.VLR_DESC),
	SUM(i.VLR_BASE_ICMS),
	SUM(i.VLR_ICMS),
	SUM(i.VLR_IPI),
	SUM(i.VLR_BASE_ICMS_SUBS),
	SUM(i.VLR_ICMS_SUBS)
from
	#RsNotasItens i 
group by
	i.CD_EMP,
	i.CD_FILIAL,
	i.CD_NF

UPDATE
	n
SET 
	n.VLR_NF=c.VLR_NF,
	n.VLR_TOT_PROD=c.VLR_TOT_PROD, 
	n.VLR_DESC=c.VLR_DESC, 
	n.VLR_BASE_ICMS=c.VLR_BASE_ICMS,
	n.VLR_ICMS=c.VLR_ICMS,
	n.VLR_IPI=c.VLR_IPI,
	n.VLR_BASE_ICMS_SUBS=c.VLR_BASE_ICMS_SUBS,
	n.VLR_ICMS_SUBS=c.VLR_ICMS_SUBS,
	n.CD_USU=1
FROM 
	EST_NF_ENT n inner join #RsNotasCab c ON
	n.CD_EMP = c.CD_EMP AND
	n.CD_FILIAL = c.CD_FILIAL AND
	n.CD_NF = c.CD_NF 

Print 'Liberando notas fiscais com erro para nova validacao ' + CONVERT(VARCHAR(8), GETDATE(), 114) 
UPDATE
	pend
SET 
	pend.PEND_SOLUC=0
FROM
	EST_NF_ENT_NFE_PEND pend INNER JOIN #RsNotasCab c ON
	pend.CD_EMP = c.CD_EMP AND
	pend.CD_FILIAL = c.CD_FILIAL AND
	pend.CD_NF = c.CD_NF 

FETCH NEXT FROM cNotaFiscal INTO @CD_EMP_CURSOR, @CD_FILIAL_CURSOR,@CD_NF_CURSOR,@NR_NF_CURSOR,@DT_ENT_NF_CURSOR
END
	
CLOSE cNotaFiscal
DEALLOCATE cNotaFiscal
	
DROP TABLE #RsNotasCab;	  
DROP TABLE #RsNotasItens;
