PRINT '> NFE - SCRIPT #0015';

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

CREATE PROCEDURE [dbo].[P_NFE_PENDENCIAS_ENTRADA] (@REINSERIR_PENDENCIAS INT = 0, @TEMPO_MIN INT = 30)
AS

	-- ################################################################################
	-- SELEÇÃO DAS NOTAS NÃO_VALIDADAS - ENTRADA
	-- ################################################################################
	
	BEGIN TRY DROP TABLE #PENDENCIAS_ENTRADA; END TRY BEGIN CATCH END CATCH;

	WITH
		TMP_NOTAS_ENTRADA_PENDENTES AS (
			SELECT
				EST_NFE_LOTE_ENT.CD_EMP
				, EST_NFE_LOTE_ENT.CD_FILIAL
				, EST_NFE_LOTE_ENT.CD_NF
				, MAX(EST_NFE_LOTE_ENT.CD_NFE_LOTE) AS CD_NFE_LOTE
			FROM EST_NFE_LOTE_ENT 
			INNER JOIN EST_NF_ENT ON 
				 EST_NFE_LOTE_ENT.CD_EMP = EST_NF_ENT.CD_EMP AND  
				 EST_NFE_LOTE_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND 
				 EST_NFE_LOTE_ENT.CD_NF = EST_NF_ENT.CD_NF  
			INNER JOIN EST_NFE_LOTE ON  
				 EST_NFE_LOTE_ENT.CD_NFE_LOTE = EST_NFE_LOTE.CD_NFE_LOTE 
			LEFT OUTER JOIN EST_NF_ENT_NFE_PEND ON 
				 EST_NFE_LOTE_ENT.CD_EMP = EST_NF_ENT_NFE_PEND.CD_EMP AND 
				 EST_NFE_LOTE_ENT.CD_FILIAL = EST_NF_ENT_NFE_PEND.CD_FILIAL AND 
				 EST_NFE_LOTE_ENT.CD_NF = EST_NF_ENT_NFE_PEND.CD_NF 
			LEFT OUTER JOIN EST_NF_ENT_NFE ON
				 EST_NFE_LOTE_ENT.CD_EMP = EST_NF_ENT_NFE.CD_EMP AND
				 EST_NFE_LOTE_ENT.CD_FILIAL = EST_NF_ENT_NFE.CD_FILIAL AND
				 EST_NFE_LOTE_ENT.CD_NF = EST_NF_ENT_NFE.CD_NF 
			WHERE 
				 EST_NFE_LOTE_ENT.CD_EMP = 1
				 AND EST_NF_ENT.DT_EMI_NF >= CONVERT(CHAR(10), GETDATE() -7, 112)
				 AND EST_NF_ENT_NFE_PEND.CD_NF IS NULL
				 AND EST_NF_ENT_NFE.CD_NF IS NULL
				 AND EST_NF_ENT.STS_NF = 0
			GROUP BY
				EST_NFE_LOTE_ENT.CD_EMP
				, EST_NFE_LOTE_ENT.CD_FILIAL
				, EST_NFE_LOTE_ENT.CD_NF
		)
		, NOTAS_ENTRADA_PENDENTES AS (
			SELECT
				P.*
				, DATEDIFF(MINUTE, (CONVERT(CHAR(10), EST_NFE_LOTE.DT_CAD, 120) + ' ' + CONVERT(CHAR(08), EST_NFE_LOTE.HR_CAD, 108)), GETDATE()) TEMPO_PENDENTE
			FROM TMP_NOTAS_ENTRADA_PENDENTES P
			INNER JOIN EST_NF_ENT ON 
				 P.CD_EMP = EST_NF_ENT.CD_EMP AND  
				 P.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND 
				 P.CD_NF = EST_NF_ENT.CD_NF  
			INNER JOIN EST_NFE_LOTE ON  
				 EST_NFE_LOTE.CD_NFE_LOTE = P.CD_NFE_LOTE
		)
		SELECT 
			*
		INTO #PENDENCIAS_ENTRADA
		FROM NOTAS_ENTRADA_PENDENTES;
		
	IF (@TEMPO_MIN > 0)
		DELETE FROM #PENDENCIAS_ENTRADA WHERE TEMPO_PENDENTE < @TEMPO_MIN;

	IF (@REINSERIR_PENDENCIAS = 1) AND ( (SELECT ISNULL(COUNT(*), 0) FROM #PENDENCIAS_ENTRADA) > 0)
	BEGIN
		INSERT INTO EST_NF_ENT_NFE_PEND
		SELECT DISTINCT CD_EMP, CD_FILIAL, CD_NF, 0 AS PEND_SOLUC, 0 AS DT_CAD FROM #PENDENCIAS_ENTRADA;
	END
	ELSE
	BEGIN
		SELECT * FROM #PENDENCIAS_ENTRADA;
		RETURN;
	END
GO

PRINT '    < [OK]';