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

--exec P_EST_PROD_PENDENCIA_ENTRADA_FILIAL 1,5
CREATE PROCEDURE P_EST_PROD_PENDENCIA_ENTRADA_FILIAL (@CD_EMP INT, 
													  @CD_FILIAL INT) 
WITH ENCRYPTION AS
/*
	Esta procedure e processada dentro da procedure de pendencia de entrada
	"P_EST_PROD_PENDENCIA_ENTRADA"
*/
SET NOCOUNT ON
declare @dtf date, @DtAux date;
CREATE TABLE #RsProcPendencia (CD_EMP INT,
							   CD_FILIAL INT,
							   DT_CAD DATETIME)

	set @dtf = getdate() 
	set @DtAux =dateadd(day,-1,@dtf)

	Print 'Analisando saldo dos itens de pedido de compras ' + convert(varchar, @cd_filial) + ' ' + convert(varchar,getdate(),108);
	EXEC dbo.P_EST_PED_CP_IT_SLD @CD_EMP,@CD_FILIAL
	--Analisando volume de ressuprimento
	EXEC P_EST_PROD_TRANSF_CORRIGIR_VOLUME
	
	--Analisando saldo dos pedidos
	Print 'Analisando pedidos de compras ' + convert(varchar, @cd_filial) + ' ' + convert(varchar,getdate(),108);
	EXEC dbo.P_EST_PED_CP_ATUALIZA_STS_PED @CD_EMP,@CD_FILIAL
	 
	Print 'Pendencia entrada filial ' + convert(varchar, @cd_filial) + ' ' + convert(varchar,getdate(),108);

	INSERT INTO  #RsProcPendencia
				(CD_EMP,
				CD_FILIAL,
				DT_CAD)
	SELECT
		CD_EMP,
		CD_FILIAL,
		DT_CAD
	FROM
		PRC_FILIAL_PENDENCIA
/**************************************************************************/
--
/**************************************************************************/

    DELETE FROM B
    FROM 
	   EST_PED_CP a
	   join EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP b on 
	   a.CD_EMP = b.CD_EMP  and 
	   a.CD_FILIAL= b.CD_FILIAL  and 
	   a.CD_PED= b.CD_PED
	   LEFT join EST_PED_CP_IT i on 
	   a.CD_EMP = I.CD_EMP  and 
	   a.CD_FILIAL= I.CD_FILIAL  and 
	   a.CD_PED= I.CD_PED
    WHERE
	   I.CD_EMP IS NULL 
	   AND a.CD_EMP=@CD_EMP
	   AND a.CD_FILIAL=@CD_FILIAL;

/**************************************************************************/
Print 'Gerando tabela temporaria de pendencia de entrada EST_PROD_PENDENCIA_ENTRADA_FILIAL ' + convert(varchar,getdate(),108);	  
--Analisando pedido pendentes que nao estao na tabela de pendencia de entrada
--Fim
	--TRUNCATE TABLE EST_PROD_PENDENCIA_ENTRADA_FILIAL;
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('#TMP_EST_PROD_PEND_ENT_FILIAL') AND type in (N'U'))
	begin
		DROP TABLE #TMP_EST_PROD_PEND_ENT_FILIAL
	end

	CREATE TABLE #TMP_EST_PROD_PEND_ENT_FILIAL(
		[CD_EMP] [tinyint] NOT NULL,
		[CD_FILIAL] [int] NOT NULL,
		[CD_PROD] [int] NOT NULL,
		[QT_EST_PEND_ENT] [money] NULL,
		[QT_EST_PEND_SAI] [money] NULL
	)

	--Analisando pendencias
	--INSERT INTO EST_PROD_PENDENCIA_ENTRADA_FILIAL 
	INSERT INTO #TMP_EST_PROD_PEND_ENT_FILIAL 
	SELECT	a.CD_EMP, 
			a.CD_FILIAL_DEST,	
			a.CD_PROD,
			a.QT_PEND_ENT,
			a.QT_PEND_SAI 
		FROM 
(SELECT
		CD_EMP, 
		CD_FILIAL_DEST,	
		CD_PROD,
		SUM(QT_PEND_ENT) QT_PEND_ENT,
		SUM(QT_PEND_SAI) QT_PEND_SAI 
	FROM
		(
		SELECT
			P.CD_EMP, 
			P.CD_FILIAL_DEST,
			P.CD_PROD,
			P.QT_IT AS QT_PEND_ENT,
			0 AS QT_PEND_SAI 
		FROM 
			V_EST_PROD_CPL_PENDENTE_ENTRADA_SAIDA P
		WHERE
			P.CD_EMP=@CD_EMP
			AND p.CD_FILIAL_DEST =@CD_FILIAL
			AND TP <> 11
		UNION ALL
		SELECT
			P.CD_EMP, 
			P.CD_FILIAL,
			P.CD_PROD,
			0 AS QT_PEND_ENT,
			P.QT_IT AS QT_PEND_SAI 
		FROM 
			V_EST_PROD_CPL_PENDENTE_ENTRADA_SAIDA P
		WHERE	
			TP IN (0,2,6,7,11)
			AND CD_EMP=@CD_EMP
			AND p.CD_FILIAL =@CD_FILIAL) R
		GROUP BY
			CD_EMP, 
			CD_FILIAL_DEST,
			--CD_PROD) AS A LEFT OUTER JOIN EST_PROD_PENDENCIA_ENTRADA_FILIAL B ON A.CD_EMP = B.CD_EMP 
			CD_PROD) AS A LEFT OUTER JOIN #TMP_EST_PROD_PEND_ENT_FILIAL B ON A.CD_EMP = B.CD_EMP 
			AND A.CD_FILIAL_DEST = B.CD_FILIAL
			AND A.CD_PROD = B.CD_PROD
		WHERE B.CD_EMP IS NULL;
	
	Print ' Zerando pendencia de entada de produtos que nao possui mais documentos pendentes ' + convert(varchar,getdate(),108);	  
	--update zerando pendencia
	UPDATE 
		p
	SET
		p.QT_PEND_ENT = 0,
		p.QT_PEND_SAI = 0 
	FROM
		EST_PROD_CPL_PENDENTE p 
		--LEFT OUTER JOIN EST_PROD_PENDENCIA_ENTRADA_FILIAL r ON
		LEFT OUTER JOIN #TMP_EST_PROD_PEND_ENT_FILIAL r ON
		p.CD_EMP = r.CD_EMP AND
		p.CD_FILIAL = r.CD_FILIAL AND
		p.CD_PROD = r.CD_PROD	
	WHERE
		p.CD_EMP= @CD_EMP
		AND p.CD_FILIAL=@CD_FILIAL
		AND r.CD_PROD IS NULL; 
	Print 'Alterando quantidade produto pendencia de entrada ' + convert(varchar,getdate(),108);	  
	--update pendencia
	UPDATE 
		p
	SET
		p.QT_PEND_ENT = ISNULL(r.QT_EST_PEND_ENT,0),
		p.QT_PEND_SAI = ISNULL(r.QT_EST_PEND_SAI,0),
		p.QT_PEND_FLUT = 0
	FROM
		-- EST_PROD_PENDENCIA_ENTRADA_FILIAL r 
		#TMP_EST_PROD_PEND_ENT_FILIAL r
		INNER JOIN EST_PROD_CPL_PENDENTE p ON
		r.CD_EMP = p.CD_EMP AND
		r.CD_FILIAL = p.CD_FILIAL AND
		r.CD_PROD = p.CD_PROD
	WHERE
		p.CD_EMP= @CD_EMP
		AND p.CD_FILIAL=@CD_FILIAL
		AND (p.QT_PEND_ENT <> r.QT_EST_PEND_ENT 
			 or p.QT_PEND_SAI <> r.QT_EST_PEND_SAI) ;
	
	Print 'Incluindo produtos que nao estavam no controle de pendencia de entrada ' + convert(varchar,getdate(),108);	  

	--Inserindo pendencia
	INSERT INTO EST_PROD_CPL_PENDENTE(CD_EMP,CD_FILIAL,CD_PROD,QT_PEND_ENT,QT_PEND_SAI,QT_PEND_FLUT)
	SELECT
		CD_EMP,
		CD_FILIAL,
		CD_PROD,
		ISNULL(QT_EST_PEND_ENT,0), 
		ISNULL(QT_EST_PEND_SAI,0)  ,
		0 QT_PEND_FLUT
	FROM
		--EST_PROD_PENDENCIA_ENTRADA_FILIAL r
		#TMP_EST_PROD_PEND_ENT_FILIAL r
	WHERE
		r.CD_EMP= @CD_EMP
		AND r.CD_FILIAL=@CD_FILIAL	
		AND NOT EXISTS (SELECT
						*
					FROM
						EST_PROD_CPL_PENDENTE p
					WHERE
						r.CD_EMP = p.CD_EMP AND
						r.CD_FILIAL =p.CD_FILIAL AND
						r.CD_PROD = p.CD_PROD)
	Print ' Fim ' + convert(varchar,getdate(),108);	  

Print 'Deletando pendencia de geracao de pendencia de entrada e saidas ' + convert(varchar,getdate(),108);	  
DELETE
	PRC_FILIAL_PENDENCIA
FROM
	PRC_FILIAL_PENDENCIA INNER JOIN #RsProcPendencia P ON
	PRC_FILIAL_PENDENCIA.CD_EMP = p.CD_EMP AND
	PRC_FILIAL_PENDENCIA.CD_FILIAL = p.CD_FILIAL AND
	PRC_FILIAL_PENDENCIA.DT_CAD = p.DT_CAD

DROP TABLE #TMP_EST_PROD_PEND_ENT_FILIAL
