IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_WMS_SAI_RET_PEND_PROCESSAR]') 
					AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [DBO].[P_EST_WMS_SAI_RET_PEND_PROCESSAR]
END
GO
--P_EST_WMS_SAI_RET_PEND_PROCESSAR 519520
/*
***********************************************************************
	Este job esta sendo executado pela procedure P_INCLUI_PENDENCIA_WMS
***********************************************************************
*/
CREATE PROCEDURE P_EST_WMS_SAI_RET_PEND_PROCESSAR @CD_PED_WMS_PAR INT=0, @CD_EMP_PAR INT=1 AS
DECLARE @CD_EMP INT
DECLARE @CD_FILIAL INT
DECLARE @CD_CTR INT
DECLARE @CD_CTR_FASES INT
DECLARE @CD_PED_WMS INT
DECLARE @CD_CONTROLE INT
DECLARE @CD_LACRE INT

SET NOCOUNT ON;
PRINT 'Inicio da rotina - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
DECLARE ProcWMSSai CURSOR FAST_FORWARD
FOR SELECT
		PEND.CD_PED_WMS, 
		TRANSF.CD_EMP,
		TRANSF.CD_FILIAL,
		TRANSF.CD_CTR
	FROM
		EST_WMS_SAI_RET_PEND PEND INNER JOIN EST_PROD_TRANSF_EST_WMS_SAI TRANSF ON
		PEND.CD_PED_WMS = TRANSF.CD_PED_WMS
		INNER JOIN EST_PROD_TRANSF ON
		TRANSF.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
		TRANSF.CD_FILIAL = EST_PROD_TRANSF.CD_FILIAL AND 
		TRANSF.CD_CTR = EST_PROD_TRANSF.CD_CTR  
	WHERE
		PEND.CD_PED_WMS= CASE WHEN @CD_PED_WMS_PAR=0 THEN  PEND.CD_PED_WMS ELSE @CD_PED_WMS_PAR END 

OPEN ProcWMSSai;
FETCH NEXT FROM ProcWMSSai INTO @CD_PED_WMS,
								@CD_EMP,
								@CD_FILIAL,
								@CD_CTR;
WHILE @@FETCH_STATUS = 0
	BEGIN
	PRINT 'Buscando controle de fases - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);

		SET @CD_CONTROLE=0
		SET @CD_CTR_FASES=ISNULL((SELECT 
									MAX(CD_CTR_FASES) 
								FROM
									EST_PROD_TRANSF_CONF_FASES 
								WHERE 
									CD_EMP=@CD_EMP),0)+1
		
		PRINT 'Gravancado cabecalho do controle de fases - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
		--Controle de fase do checkOut
		INSERT INTO EST_PROD_TRANSF_CONF_FASES
		(CD_EMP,
		CD_CTR_FASES,
		CD_FILIAL,
		CD_CTR,
		CD_USU,
		DT_CONF_FASES,
		HR_INI,
		HR_FIM)
		SELECT
			@CD_EMP,
			@CD_CTR_FASES,
			@CD_FILIAL,
			@CD_CTR,
			1,
			CONVERT(CHAR(10),GETDATE(),112),
			NULL,
			NULL
		
		SET @CD_CONTROLE=SCOPE_IDENTITY()

		PRINT 'Gravancado itens do controle de fases - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
		--Controle do CheckOut do ressuprimento dos itens
		INSERT INTO EST_PROD_TRANSF_CONF_FASES_ITENS
		(CD_EMP,
		CD_CTR_FASES,
		CD_PROD,
		QTDE_PROD_CONF,
		CD_CONTROLE)
		SELECT
			EST_PROD_TRANSF.CD_EMP,
			@CD_CTR_FASES,
			RET.CD_PROD,
			SUM(RET.QT_PROD) AS QT_PROD,
			@CD_CONTROLE
		FROM
			EST_WMS_SAI_RET RET INNER JOIN EST_PROD_TRANSF_EST_WMS_SAI TRANSF ON
			RET.CD_PED_WMS = TRANSF.CD_PED_WMS
			INNER JOIN EST_PROD_TRANSF ON
			TRANSF.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
			TRANSF.CD_FILIAL = EST_PROD_TRANSF.CD_FILIAL AND 
			TRANSF.CD_CTR = EST_PROD_TRANSF.CD_CTR  
		WHERE
			RET.QT_PROD >0
			AND RET.CD_PED_WMS = @CD_PED_WMS
		GROUP BY 
			EST_PROD_TRANSF.CD_EMP,
			RET.CD_PROD
			
			
	 PRINT 'Gravando os lotes de produtos no ressuprimento CD_PED_WMS: ' + convert(varchar, @CD_PED_WMS ) + ' - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);

     INSERT INTO EST_PROD_TRANSF_IT_LOTE(
		CD_EMP
		,CD_FILIAL
		,CD_CTR
		,CD_PROD
		,NR_LOTE
		,DT_VALID
		,QTDE_PROD
		,DT_FABR
		,NR_LOTE_NOVO)		
		 
		SELECT
			EST_PROD_TRANSF.CD_EMP,
			EST_PROD_TRANSF.CD_FILIAL,
			EST_PROD_TRANSF.CD_CTR,
			RET.CD_PROD,
			RET.LOTE,
			RET.DT_VENCTO,
			RET.QT_PROD,
			RET.DT_FABR,
			RET.LOTE
		FROM
			EST_WMS_SAI_RET RET 
			INNER JOIN EST_PROD_TRANSF_EST_WMS_SAI TRANSF ON
			RET.CD_PED_WMS = TRANSF.CD_PED_WMS
			INNER JOIN EST_PROD_TRANSF ON
			TRANSF.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
			TRANSF.CD_FILIAL = EST_PROD_TRANSF.CD_FILIAL AND 
			TRANSF.CD_CTR = EST_PROD_TRANSF.CD_CTR  
			LEFT OUTER JOIN EST_PROD_TRANSF_IT_LOTE ON
			TRANSF.CD_EMP = EST_PROD_TRANSF_IT_LOTE.CD_EMP AND 
			TRANSF.CD_FILIAL = EST_PROD_TRANSF_IT_LOTE.CD_FILIAL AND 
			TRANSF.CD_CTR = EST_PROD_TRANSF_IT_LOTE.CD_CTR  AND 
			RET.CD_PROD = EST_PROD_TRANSF_IT_LOTE.CD_PROD AND 
			RET.LOTE = EST_PROD_TRANSF_IT_LOTE.NR_LOTE
		WHERE
			RET.CD_PED_WMS = @CD_PED_WMS
			AND EST_PROD_TRANSF_IT_LOTE.NR_LOTE IS NULL 
			
		PRINT 'Zerando itens do ressuprimento - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
	  --Zerando quantidade atendida do ressuprimento
	  UPDATE
		EST_PROD_TRANSF_IT
	  SET 
		QT_AT=0
	 WHERE
		CD_EMP=@CD_EMP
		AND CD_FILIAL = @CD_FILIAL
		AND CD_CTR=@CD_CTR
		
		PRINT 'Gravando quantidade itens atendido do ressuprimento - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
	  --Quantidade atendida do ressuprimento
	  UPDATE
		IT
	  SET 
		IT.QT_AT=EST_PROD_TRANSF_CONF_FASES_ITENS.QTDE_PROD_CONF
	  FROM
		 EST_PROD_TRANSF_IT IT INNER JOIN EST_PROD_TRANSF_CONF_FASES_ITENS ON
		 IT.CD_EMP = EST_PROD_TRANSF_CONF_FASES_ITENS.CD_EMP AND
		 IT.CD_FILIAL = @CD_FILIAL AND
		 IT.CD_CTR = @CD_CTR AND
		 IT.CD_PROD = EST_PROD_TRANSF_CONF_FASES_ITENS.CD_PROD 
	  WHERE
		IT.CD_EMP=@CD_EMP
		AND IT.CD_FILIAL=@CD_FILIAL
		AND IT.CD_CTR=@CD_CTR
		AND EST_PROD_TRANSF_CONF_FASES_ITENS.CD_CONTROLE=@CD_CONTROLE

	  PRINT 'Mudando status do ressuprimento - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
	  --Mudando status do ressuprimento para checkout finalizado
	  UPDATE
		EST_PROD_TRANSF
	  SET 
		FLAG_CHECKOUT_INICIADO=0, 
		STS_TRANSF=3
	 WHERE
		CD_EMP=@CD_EMP
		AND CD_FILIAL = @CD_FILIAL
		AND CD_CTR=@CD_CTR

	PRINT 'Confirmando fase para faturamento - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
	--Inserindo confirmacao para faturamento
	INSERT INTO EST_PROD_TRANSF_CONF
	(CD_EMP,
	CD_CTR_FASES,
	CD_USU,
	DT_CONF,
	HR_INI,
	HR_FIM,
	CD_CONTROLE)
	SELECT
		@CD_EMP,
		@CD_CTR_FASES,
		1,
		CONVERT(CHAR(10),GETDATE(),112),
		NULL,
		NULL,
		@CD_CONTROLE

	PRINT 'Deletando pendencias de retorno - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
	DELETE FROM EST_WMS_SAI_RET_PEND WHERE CD_PED_WMS = @CD_PED_WMS 

FETCH NEXT FROM ProcWMSSai INTO @CD_PED_WMS,
								@CD_EMP,
								@CD_FILIAL,
								@CD_CTR;
END;
PRINT 'Concluido.';
CLOSE ProcWMSSai;
DEALLOCATE ProcWMSSai;

PRINT 'Cancelando retorno do WMS 100% zerado - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
UPDATE
	EST_PROD_TRANSF
SET 
	EST_PROD_TRANSF.STS_TRANSF=4,
	OBSERVACAO='CANCELAMENTO POR CHECKOUT ZERADO - RETORNO WMS'
FROM
EST_PROD_TRANSF LEFT OUTER JOIN (SELECT
									CD_EMP,
									CD_FILIAL,
									CD_CTR
								 FROM
									EST_PROD_TRANSF_IT 
								 GROUP BY
									CD_EMP,
									CD_FILIAL,
									CD_CTR									
								 HAVING  
									SUM(QT_AT) > 0)	EST_PROD_TRANSF_IT
									ON
EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_IT.CD_EMP AND
EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_IT.CD_FILIAL AND
EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_IT.CD_CTR

INNER JOIN EST_PROD_TRANSF_CONF_FASES ON
EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_CONF_FASES.CD_EMP AND
EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_CONF_FASES.CD_FILIAL AND
EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_CONF_FASES.CD_CTR

INNER JOIN EST_PROD_TRANSF_CONF ON
EST_PROD_TRANSF_CONF_FASES.CD_EMP = EST_PROD_TRANSF_CONF.CD_EMP AND
EST_PROD_TRANSF_CONF_FASES.CD_CTR_FASES = EST_PROD_TRANSF_CONF.CD_CTR_FASES AND
EST_PROD_TRANSF_CONF_FASES.CD_CONTROLE = EST_PROD_TRANSF_CONF.CD_CONTROLE

INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR
WHERE
EST_PROD_TRANSF.CD_EMP=@CD_EMP_PAR
AND EST_PROD_TRANSF_IT.CD_CTR IS NULL