IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_SAI_EST_WMS_ENT_RET]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_SAI_EST_WMS_ENT_RET]
GO
CREATE PROCEDURE P_EST_NF_SAI_EST_WMS_ENT_RET AS
BEGIN
/*
***********************************************************************
	Este job esta sendo executado pela procedure P_INCLUI_PENDENCIA_WMS
***********************************************************************
*/

/*
=======================================================================================
'Job.................: Job - Inclui Pendencia WMS 
'Função..............: 
'Objetivo............: Alterar codigo do produto 
'Tabelas.............: 
'Dt Modificação......: 09/06/2021
'Modificação.........: Tag #A001 Evitar erro de PK 
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/
--VARIAVEIS PARA CONTROLAR OS PEDIDOS PENDENTES
DECLARE @CD_PED_WMS INT
DECLARE @CD_CTR		INT
DECLARE @CD_NF		INT
DECLARE @CD_MOV_OP	INT

--VARIAVEIS PARA CONTROLAR OS ITENS DOS PEDIDOS PENDENTES
DECLARE @CD_PROD		INT
DECLARE @QT_PROD		MONEY
DECLARE @QT_PROD_AUX	MONEY
DECLARE @QT_PROD_NF		MONEY
DECLARE @ROW_COUNT		INT

CREATE TABLE #RS_CHECK_IN (
	CD_EMP			INT,
	CD_FILIAL		INT,
	CD_MOV_OP		INT,
	CHECKIN_DIVER	INT,
	CD_NF			INT,
	CD_PROD			INT,
	QT_IT_NF		MONEY,
	QT_IT_CHECK		MONEY)

CREATE TABLE #RS_PEDIDOS_WMS_PEND_APAGAR (
	CD_PED_WMS		INT)

CREATE TABLE #RS_PRODUTO_SOBRA (
	CD_PROD			INT,
	QT_PROD			MONEY)


DECLARE 
	CURSOR_PEDIDOS 
CURSOR FOR
SELECT DISTINCT
	PL.CD_PED_WMS,
	PL.CD_CTR,
	L.CD_MOV_OP
FROM
	EST_WMS_ENT_RET_PEND PEND
	INNER JOIN EST_WMS_ENT_RET R ON
		PEND.CD_PED_WMS = R.CD_PED_WMS
		AND PEND.CD_IT = R.CD_IT
	INNER JOIN EST_NF_SAI_LOTE_ENVIO_WMS_EST_WMS_ENT PL ON
		PEND.CD_PED_WMS = PL.CD_PED_WMS
	INNER JOIN EST_NF_SAI_LOTE_ENVIO_WMS L ON
		PL.CD_CTR = L.CD_CTR

OPEN CURSOR_PEDIDOS

FETCH NEXT FROM CURSOR_PEDIDOS INTO @CD_PED_WMS, @CD_CTR, @CD_MOV_OP

WHILE @@FETCH_STATUS = 0
BEGIN
	
	DECLARE 
		CURSOR_PRODUTOS
	CURSOR FOR
		SELECT DISTINCT
			R.CD_PROD,
			SUM(R.QT_PROD) AS QT_PROD
		FROM
			EST_WMS_ENT_RET_PEND PEND
			INNER JOIN EST_WMS_ENT_RET R ON
				PEND.CD_PED_WMS = R.CD_PED_WMS
				AND PEND.CD_IT = R.CD_IT
			INNER JOIN EST_NF_SAI_LOTE_ENVIO_WMS_EST_WMS_ENT PL ON
				PEND.CD_PED_WMS = PL.CD_PED_WMS
		WHERE
			PL.CD_CTR = @CD_CTR
			AND PL.CD_PED_WMS = @CD_PED_WMS
		GROUP BY
			R.CD_PROD
		ORDER BY
			R.CD_PROD
		

		INSERT INTO
			#RS_CHECK_IN (CD_EMP, CD_FILIAL, CD_MOV_OP, CHECKIN_DIVER, CD_NF, CD_PROD, QT_IT_NF, QT_IT_CHECK)
		SELECT
			1,
			IT.CD_FILIAL,
			@CD_MOV_OP,
			0,
			IT.CD_NF,
			IT.CD_PROD,
			IT.QT_IT,
			0 AS QT_IT_CHECK
		FROM
			EST_NF_SAI_LOTE_ENVIO_WMS_CPL C
			INNER JOIN EST_NF_SAI_IT IT ON
				C.CD_EMP = IT.CD_EMP
				AND C.CD_FILIAL = IT.CD_FILIAL
				AND C.CD_NF = IT.CD_NF
		WHERE
			C.CD_CTR = @CD_CTR

		OPEN CURSOR_PRODUTOS

		FETCH NEXT FROM CURSOR_PRODUTOS INTO @CD_PROD, @QT_PROD

		WHILE @@FETCH_STATUS = 0
		BEGIN

			SET @QT_PROD_AUX = @QT_PROD

			SET @CD_NF = 0
			SET @ROW_COUNT = 1

			WHILE @QT_PROD_AUX > 0 AND @ROW_COUNT > 0
			BEGIN
				SELECT TOP 1
					@CD_NF = COALESCE(CD_NF, 0),
					@QT_PROD_NF = QT_IT_NF
				FROM
					#RS_CHECK_IN
				WHERE
					CD_EMP = 1
					AND CD_PROD = @CD_PROD
					AND QT_IT_NF > QT_IT_CHECK

				SET @ROW_COUNT = @@ROWCOUNT

				IF @ROW_COUNT = 0
				BEGIN
					INSERT INTO 
						#RS_PRODUTO_SOBRA (CD_PROD, QT_PROD)
					VALUES
						(@CD_PROD, @QT_PROD_AUX)
				END
				ELSE
				BEGIN
					UPDATE
						#RS_CHECK_IN
					SET
						QT_IT_CHECK = CASE WHEN @QT_PROD_AUX > @QT_PROD_NF THEN @QT_PROD_NF ELSE @QT_PROD_AUX END
					WHERE
						CD_NF = @CD_NF
						AND CD_PROD = @CD_PROD

					SET @QT_PROD_AUX = @QT_PROD_AUX - @QT_PROD_NF
				END
			END

			FETCH NEXT FROM CURSOR_PRODUTOS INTO @CD_PROD, @QT_PROD
		END

	IF EXISTS (SELECT * FROM #RS_PRODUTO_SOBRA)
	BEGIN
		DECLARE 
			CURSOR_SOBRA 
		CURSOR FOR
			SELECT
				CD_PROD,
				QT_PROD
			FROM
				#RS_PRODUTO_SOBRA

		OPEN CURSOR_SOBRA

		FETCH NEXT FROM CURSOR_SOBRA INTO @CD_PROD, @QT_PROD

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT TOP 1
				@CD_NF = CD_NF
			FROM
				#RS_CHECK_IN
			WHERE
				CD_PROD = @CD_PROD

			IF @@ROWCOUNT > 0
			BEGIN
				UPDATE
					#RS_CHECK_IN
				SET
					QT_IT_CHECK = QT_IT_CHECK + @QT_PROD
				WHERE
					CD_NF = @CD_NF
					AND CD_PROD = @CD_PROD
			END
				
			FETCH NEXT FROM CURSOR_SOBRA INTO @CD_PROD, @QT_PROD
		END

		CLOSE CURSOR_SOBRA
		DEALLOCATE CURSOR_SOBRA
	END

	TRUNCATE TABLE #RS_PRODUTO_SOBRA

	INSERT INTO	#RS_PEDIDOS_WMS_PEND_APAGAR (CD_PED_WMS) VALUES (@CD_PED_WMS)

	FETCH NEXT FROM CURSOR_PEDIDOS INTO @CD_PED_WMS, @CD_CTR, @CD_MOV_OP

	CLOSE CURSOR_PRODUTOS
	DEALLOCATE CURSOR_PRODUTOS
END

CLOSE CURSOR_PEDIDOS
DEALLOCATE CURSOR_PEDIDOS

IF EXISTS (SELECT * FROM #RS_CHECK_IN)
BEGIN

	UPDATE
		#RS_CHECK_IN
	SET
		CHECKIN_DIVER = 1
	WHERE
		QT_IT_NF <> QT_IT_CHECK

	INSERT INTO
		EST_NF_SAI_CHECKIN_ENTRADA(CD_EMP, CD_FILIAL, CD_NF, CD_USU, DT_CAD, CHECKIN_DIVERGENCIA, FLAG_ENTRADA_AUTOMATICA, CD_MOV_OP, OBSERVACAO,DT_ENT)
	SELECT DISTINCT
		C.CD_EMP,
		C.CD_FILIAL,
		C.CD_NF,
		1 AS CD_USU,
		CONVERT(DATE, GETDATE()),
		MAX(C.CHECKIN_DIVER),
		1,
		C.CD_MOV_OP,
		'' AS OBSERVACAO,
		CONVERT(DATE, GETDATE())
	FROM
		#RS_CHECK_IN C
		LEFT JOIN EST_NF_SAI_CHECKIN_ENTRADA CC ON
			C.CD_EMP = CC.CD_EMP
			AND C.CD_FILIAL = CC.CD_FILIAL
			AND C.CD_NF = CC.CD_NF
	WHERE
		CC.CD_EMP IS NULL
	GROUP BY
		C.CD_EMP,
		C.CD_FILIAL,
		C.CD_NF,
		C.CD_MOV_OP

-- Tag #A001
	INSERT INTO
		EST_NF_SAI_CHECKIN_ENTRADA_CPL (CD_EMP, CD_FILIAL, CD_NF, CD_PROD, QT_IT_CHECK)
	SELECT
		C.CD_EMP,
		C.CD_FILIAL,
		C.CD_NF,
		C.CD_PROD,
		SUM(C.QT_IT_CHECK)
	FROM
		#RS_CHECK_IN C
		LEFT JOIN EST_NF_SAI_CHECKIN_ENTRADA_CPL CC ON
			C.CD_EMP = CC.CD_EMP
			AND C.CD_FILIAL = CC.CD_FILIAL
			AND C.CD_NF = CC.CD_NF
			AND C.CD_PROD = CC.CD_PROD
	WHERE
		CC.CD_EMP IS NULL
	 GROUP BY C.CD_EMP,
			  C.CD_FILIAL,
			  C.CD_NF,
			  C.CD_PROD

	DELETE FROM
		EST_WMS_ENT_RET_PEND
	WHERE
		EXISTS (SELECT * FROM #RS_PEDIDOS_WMS_PEND_APAGAR WHERE CD_PED_WMS = EST_WMS_ENT_RET_PEND.CD_PED_WMS)

END

DROP TABLE #RS_CHECK_IN
DROP TABLE #RS_PRODUTO_SOBRA

END