IF  EXISTS( SELECT  * FROM  SYS.OBJECTS 
	WHERE  NAME LIKE 'P_EST_PED_CP_BAIXA_PBM' AND TYPE = 'P' )
BEGIN
	DROP PROCEDURE P_EST_PED_CP_BAIXA_PBM 
END

GO

--EXEC P_EST_PED_CP_BAIXA_PBM 
CREATE PROCEDURE P_EST_PED_CP_BAIXA_PBM 
WITH ENCRYPTION AS

SET NOCOUNT ON;

DECLARE @CD_EMP INT
DECLARE @CD_FILIAL INT
DECLARE @CD_PROD INT
DECLARE @QT_IT_NF MONEY
DECLARE @QT_IT_NF_SLD MONEY

DECLARE @CD_PED INT
DECLARE @QT_IT_PED MONEY

DECLARE @QTDE_CANCELAR MONEY

SET CONTEXT_INFO 0x99999 

DECLARE 
	CURSOR_NOTAS
CURSOR FOR
SELECT
	IT.CD_EMP,
	IT.CD_FILIAL,
	IT.CD_PROD,
	IT.QT_IT
FROM
	EST_NF_ENT_DANFE_PROVISORIO D
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT V ON
		D.CD_CTR = V.CD_CTR
	INNER JOIN EST_NF_ENT E ON
		V.CD_EMP = E.CD_EMP
		AND V.CD_FILIAL = E.CD_FILIAL
		AND V.CD_NF = E.CD_NF
	INNER JOIN EST_NF_ENT_IT IT ON
		E.CD_EMP = IT.CD_EMP
		AND E.CD_FILIAL = IT.CD_FILIAL
		AND E.CD_NF = IT.CD_NF
	LEFT JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP DP ON
		D.CD_CTR = DP.CD_CTR
WHERE
	E.CD_EMP = 1
	AND E.DT_ENT = DATEADD(DAY, -1, CONVERT(DATE, GETDATE()))

	AND D.FLAG_REPOSICAO_PBM > 0
	AND DP.CD_CTR IS NULL

OPEN CURSOR_NOTAS

FETCH NEXT FROM CURSOR_NOTAS INTO @CD_EMP, @CD_FILIAL, @CD_PROD, @QT_IT_NF

WHILE @@FETCH_STATUS = 0
BEGIN
	
	DECLARE
		CURSOR_PEDIDOS
	CURSOR FOR

	SELECT
		IT.CD_PED,
		IT.QT_IT_SLD
	FROM
		EST_PED_CP CP
		INNER JOIN EST_PED_CP_IT IT ON
			CP.CD_EMP = IT.CD_EMP
			AND CP.CD_FILIAL = IT.CD_FILIAL
			AND CP.CD_PED = IT.CD_PED
		INNER JOIN EST_PED_CP_PBM PBM ON
			CP.CD_EMP = PBM.CD_EMP
			AND CP.CD_FILIAL = PBM.CD_FILIAL
			AND CP.CD_PED = PBM.CD_PED
		INNER JOIN EST_PED_CP_PENDENCIA PEND ON
			CP.CD_EMP = PEND.CD_EMP
			AND CP.CD_FILIAL = PEND.CD_FILIAL
			AND CP.CD_PED = PEND.CD_PED
	WHERE
		CP.CD_EMP = @CD_EMP
		AND CP.CD_FILIAL = @CD_FILIAL
		AND IT.CD_PROD = @CD_PROD
		AND CP.STS_PED <> 2
		AND IT.QT_IT_SLD > 0
	ORDER BY
		CP.DT_EMI

	OPEN CURSOR_PEDIDOS

	FETCH NEXT FROM CURSOR_PEDIDOS INTO @CD_PED, @QT_IT_PED

	SET @QT_IT_NF_SLD = @QT_IT_NF;

	WHILE @@FETCH_STATUS = 0 AND @QT_IT_NF_SLD > 0
	BEGIN

		SET @QTDE_CANCELAR = CASE WHEN @QT_IT_NF_SLD > @QT_IT_PED THEN @QT_IT_PED ELSE @QT_IT_NF_SLD END;

		PRINT 'FILIAL: ' + CAST(@CD_FILIAL AS VARCHAR(MAX)) + ' - PEDIDO: ' + CAST(@CD_PED AS VARCHAR(MAX)) + ' - PRODUTO: ' + CAST(@CD_PROD AS VARCHAR(MAX)) + ' - QTDE: ' + CAST(@QTDE_CANCELAR AS VARCHAR(MAX));

		IF NOT EXISTS (SELECT * FROM EST_PED_CP_IT_CANCEL_SLD WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED AND CD_PROD = @CD_PROD AND CD_USU = 1)
		BEGIN
			INSERT INTO
				EST_PED_CP_IT_CANCEL_SLD (CD_EMP, CD_FILIAL, CD_PED, CD_PROD, CD_USU, QT_PROD, DT_CANCEL)
			VALUES
				(@CD_EMP, @CD_FILIAL, @CD_PED, @CD_PROD, 1, @QTDE_CANCELAR, GETDATE())
		END


		UPDATE
			EST_PED_CP_IT
		SET
			QT_IT_SLD = QT_IT_SLD - @QTDE_CANCELAR
		WHERE
			CD_EMP = @CD_EMP
			AND CD_FILIAL = @CD_FILIAL
			AND CD_PED = @CD_PED
			AND CD_PROD = @CD_PROD


		IF NOT (SELECT SUM(QT_IT_SLD) FROM EST_PED_CP_IT WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED) > 0
		BEGIN
			UPDATE EST_PED_CP SET STS_PED = 2 WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED;
			DELETE FROM EST_PED_CP_PENDENCIA WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL AND CD_PED = @CD_PED;
		END

		SET @QT_IT_NF_SLD = @QT_IT_NF_SLD - @QTDE_CANCELAR;


		FETCH NEXT FROM CURSOR_PEDIDOS INTO @CD_PED, @QT_IT_PED
	END

	CLOSE CURSOR_PEDIDOS
	DEALLOCATE CURSOR_PEDIDOS


	FETCH NEXT FROM CURSOR_NOTAS INTO @CD_EMP, @CD_FILIAL, @CD_PROD, @QT_IT_NF
END


CLOSE CURSOR_NOTAS
DEALLOCATE CURSOR_NOTAS
