IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_EST_PED_CP_IT_SLD]') AND TYPE IN (N'P', N'PC'))
	DROP PROCEDURE [DBO].[P_EST_PED_CP_IT_SLD]  
GO 


CREATE PROCEDURE [dbo].[P_EST_PED_CP_IT_SLD](
												  @CD_EMP		INT=1,
												  @CD_FILIAL 	INT,
												  @FLAG_ANALISE INT=0,
												  @CD_PED INT=0)
AS
BEGIN
SET NOCOUNT ON; 

DECLARE @CD_EMP_INTERNA		INT= @CD_EMP,
@CD_FILIAL_INTERNA 	INT = @CD_FILIAL,
@FLAG_ANALISE_INTERNA INT= @FLAG_ANALISE,
@CD_PED_INTERNA INT= @CD_PED

Print 'começando ' + convert(varchar,getdate(),108);

CREATE TABLE #RsPedidoAnalise (CD_EMP INT, 
							   CD_FILIAL INT,
							   CD_PED INT,
							   CD_PROD INT,
							   QT_PED_SLD_ATUAL MONEY,
							   QT_PED MONEY,
							   QT_NF_ENTRADA MONEY, 
							   QT_PED_SLD_CANCEL MONEY, 
							   QT_PED_SLD MONEY )
							   
CREATE CLUSTERED INDEX PK_RsPedidoAnalise
    ON #RsPedidoAnalise (CD_EMP,CD_FILIAL,CD_PED,CD_PROD);   
							   
INSERT INTO #RsPedidoAnalise
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_PED,
	CD_PROD,
	SUM(QT_PED_SLD_ATUAL) AS QT_PED_SLD_ATUAL,
	SUM(QT_PED) AS QT_PED,
	SUM(QT_NF_ENTRADA) AS QT_NF_ENTRADA,
	SUM(QT_PED_SLD_CANCEL) AS QT_PED_SLD_CANCEL,
	CASE WHEN SUM(QT_PED) - (SUM(QT_NF_ENTRADA) + SUM(QT_PED_SLD_CANCEL)) <0 THEN
		0
	ELSE
		SUM(QT_PED) - (SUM(QT_NF_ENTRADA) + SUM(QT_PED_SLD_CANCEL))
	END AS QT_PED_SLD
FROM
(SELECT --Buscando dados do pedido de compras
	EST_PED_CP_IT.CD_EMP,
	EST_PED_CP_IT.CD_FILIAL,
	EST_PED_CP_IT.CD_PED,
	EST_PED_CP_IT.CD_PROD,
	ISNULL(QT_IT_SLD,0) AS QT_PED_SLD_ATUAL, --Ja esta convertido a embalagem
	(EST_PED_CP_IT.QT_IT * CASE WHEN EST_PED_CP_IT.QT_EMB=0 THEN 1 ELSE EST_PED_CP_IT.QT_EMB END) AS QT_PED,
	0 AS QT_NF_ENTRADA,
	0 AS QT_PED_SLD_CANCEL
FROM         
	EST_PED_CP_IT INNER JOIN EST_PED_CP_PENDENCIA p ON
	EST_PED_CP_IT.CD_EMP = p.CD_EMP AND
	EST_PED_CP_IT.CD_FILIAL = p.CD_FILIAL AND
	EST_PED_CP_IT.CD_PED = p.CD_PED
WHERE
	EST_PED_CP_IT.CD_EMP		= @CD_EMP_INTERNA
	AND EST_PED_CP_IT.CD_FILIAL = @CD_FILIAL_INTERNA
	AND EST_PED_CP_IT.CD_PED=CASE WHEN @CD_PED_INTERNA=0 THEN EST_PED_CP_IT.CD_PED ELSE @CD_PED_INTERNA END 
UNION ALL
SELECT     --Notas fiscais do pedido ja efetuadas
	EST_NF_ENT_PED_CP.CD_EMP,
	EST_NF_ENT_PED_CP.CD_FILIAL,
	EST_NF_ENT_PED_CP.CD_PED,
	EST_NF_ENT_IT.CD_PROD,
	0 AS QT_PED_SLD_ATUAL,
	0 AS QT_PED,
	EST_NF_ENT_IT.QT_IT AS QT_NF_ENTRADA,
	0 AS QT_PED_SLD_CANCEL
FROM         
	EST_NF_ENT_IT 
	INNER JOIN EST_NF_ENT_PED_CP ON 
	EST_NF_ENT_IT.CD_EMP	= EST_NF_ENT_PED_CP.CD_EMP AND 
	EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT_PED_CP.CD_FILIAL AND 
	EST_NF_ENT_IT.CD_NF		= EST_NF_ENT_PED_CP.CD_NF
	INNER JOIN EST_PED_CP_PENDENCIA p ON
	EST_NF_ENT_PED_CP.CD_EMP = p.CD_EMP AND
	EST_NF_ENT_PED_CP.CD_FILIAL = p.CD_FILIAL AND
	EST_NF_ENT_PED_CP.CD_PED = p.CD_PED
WHERE     
	EST_NF_ENT_IT.CD_EMP		= @CD_EMP_INTERNA
	AND EST_NF_ENT_IT.CD_FILIAL	= @CD_FILIAL_INTERNA
	AND p.CD_PED=CASE WHEN @CD_PED_INTERNA=0 THEN p.CD_PED ELSE @CD_PED_INTERNA END 
UNION ALL
SELECT  --Pedido saldo cancelamento
	c.CD_EMP,
	c.CD_FILIAL,
	c.CD_PED,
	c.CD_PROD,   
	0 AS QT_PED_SLD_ATUAL,
	0 AS QT_PED,
	0 AS QT_NF_ENTRADA,
	C.QT_PROD*i.QT_EMB AS QT_PED_SLD_CANCEL
FROM         
	EST_PED_CP_IT_CANCEL_SLD c 
	INNER JOIN EST_PED_CP_IT i ON
	c.CD_EMP	= i.CD_EMP AND
	c.CD_FILIAL = i.CD_FILIAL AND
	c.CD_PED	= i.CD_PED AND
	c.CD_PROD	= i.CD_PROD 
	INNER JOIN EST_PED_CP_PENDENCIA p ON
	C.CD_EMP = p.CD_EMP AND
	C.CD_FILIAL = p.CD_FILIAL AND
	C.CD_PED = p.CD_PED
WHERE     
	c.CD_EMP		= @CD_EMP_INTERNA
	AND c.CD_FILIAL = @CD_FILIAL_INTERNA
	AND p.CD_PED=CASE WHEN @CD_PED_INTERNA=0 THEN p.CD_PED ELSE @CD_PED_INTERNA END) Rs
GROUP BY
	CD_EMP,
	CD_FILIAL,
	CD_PED,
	CD_PROD

Print 'parte 1 ' + convert(varchar,getdate(),108);

--Retirando nulo se houver
UPDATE
	i
SET 
	i.QT_IT_SLD = 0
FROM
	#RsPedidoAnalise s INNER JOIN EST_PED_CP_IT i ON
	s.CD_EMP = i.CD_EMP AND
	s.CD_FILIAL = i.CD_FILIAL AND
	s.CD_PED = i.CD_PED AND
	s.CD_PROD = i.CD_PROD 
WHERE
	i.QT_IT_SLD IS NULL

	Print 'parte 2' + convert(varchar,getdate(),108);

IF @FLAG_ANALISE_INTERNA=0
begin
--Aplicando resultado analise

Print 'parte 3 ' + convert(varchar,getdate(),108);

--Sinalizando que ha necessidade de revisao de saldo pedido
  UPDATE
	p
  SET 
	p.FLAG_ANALISAR_STS=1
  FROM
	EST_PED_CP_PENDENCIA p
  WHERE
	 EXISTS (SELECT
				s.CD_EMP 
			 FROM
					#RsPedidoAnalise s INNER JOIN EST_PED_CP_IT i ON
					s.CD_EMP = i.CD_EMP AND
					s.CD_FILIAL = i.CD_FILIAL AND
					s.CD_PED = i.CD_PED AND
					s.CD_PROD = i.CD_PROD AND
					s.QT_PED_SLD <> i.QT_IT_SLD
				WHERE
					p.CD_EMP = i.CD_EMP AND
					p.CD_FILIAL = i.CD_FILIAL AND
					p.CD_PED = i.CD_PED )

UPDATE
	i
SET 
	i.QT_IT_SLD = s.QT_PED_SLD
FROM
	#RsPedidoAnalise s INNER JOIN EST_PED_CP_IT i ON
	s.CD_EMP = i.CD_EMP AND
	s.CD_FILIAL = i.CD_FILIAL AND
	s.CD_PED = i.CD_PED AND
	s.CD_PROD = i.CD_PROD AND
	s.QT_PED_SLD <> i.QT_IT_SLD

Print 'parte 4 ' + convert(varchar,getdate(),108);


					Print 'parte 5 ' + convert(varchar,getdate(),108);

end
else
begin
	SELECT * FROM #RsPedidoAnalise WHERE CD_PED = CASE WHEN @CD_PED_INTERNA=0 THEN CD_PED ELSE @CD_PED_INTERNA END
end
DROP TABLE #RsPedidoAnalise

Print 'parte 6 ' + convert(varchar,getdate(),108);

end