/****** Object:  StoredProcedure [dbo].[P_SNGPC_ANALISE_PENDENCIA_NOTA]    Script Date: 05/17/2013 14:49:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_SNGPC_ANALISE_PENDENCIA_NOTA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_SNGPC_ANALISE_PENDENCIA_NOTA]
GO

/****** Object:  StoredProcedure [dbo].[P_SNGPC_ANALISE_PENDENCIA_NOTA]    Script Date: 05/17/2013 14:49:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[P_SNGPC_ANALISE_PENDENCIA_NOTA] (@CD_EMP INT, @CD_FILIAL INT)
AS
BEGIN
	/*
	********************************************************************************
	********************************************************************************

							N  O  T  A  S     F  I  S  C  A  I  S
			
			--ENVIAR SAIDAS NÃO REGISTRADAS E NÃO PENDENTES, PARA A PENDÊNCIA--

	********************************************************************************
	********************************************************************************
	*/

	/* LIMPAR AS TABELAS TEMPORARIAS DE USO, CASO NECESSARIO */
	BEGIN TRY
			DROP TABLE #TEMP_DIVERGENCIAS_NOTAS;
	END TRY
	BEGIN CATCH
	END CATCH

	SELECT
		I.CD_EMP
		, I.CD_FILIAL
		, I.CD_NF
		, I.CD_PROD
		, I.CD_IT
		, C.NF_NF
		, S.DS_DCB
		, I.DT_EMI_NF
		, P.FLAG_PRODUTO_CONTROLADO CONTROLADO
		, P.ANTIBIOTICO
	INTO #TEMP_DIVERGENCIAS_NOTAS	
	FROM EST_NF_SAI_IT I
	INNER JOIN EST_NF_SAI C ON 
			I.CD_EMP = C.CD_EMP 
		AND I.CD_FILIAL = C.CD_FILIAL 
		AND I.CD_NF = C.CD_NF
	INNER JOIN EST_PROD_EST_SNGPC S ON 
			I.CD_EMP = S.CD_EMP 
		AND I.CD_PROD = S.CD_PROD 
	INNER JOIN EST_PROD P ON 
			I.CD_EMP = P.CD_EMP
		AND I.CD_PROD = P.CD_PROD
	WHERE 
			CONVERT(CHAR(10), I.DT_EMI_NF, 112) >= (
						SELECT
							TOP 1 CONVERT(CHAR(10), RI.DT_REINICIO, 112)
						FROM EST_SNGPC_REINICIO_INVENTARIO RI
						WHERE
								RI.CD_EMP = I.CD_EMP
							AND RI.CD_FILIAL = I.CD_FILIAL
						ORDER BY
								CD_REINICIO DESC
						)
		AND I.CD_EMP = @cd_emp
		AND ((I.CD_FILIAL = @cd_filial) OR (@cd_filial = 0))
		AND NOT EXISTS (
			SELECT CD_EMP 
			FROM EST_NF_SAI_IT_EST_SNGPC_PENDENTE T 
			WHERE 
					T.CD_EMP = I.CD_EMP 
				AND T.CD_FILIAL = I.CD_FILIAL 
				AND T.CD_NF = I.CD_NF
				AND T.CD_IT = I.CD_IT
			)
		AND NOT EXISTS (
			SELECT CD_EMP 
			FROM EST_NF_SAI_IT_EST_SNGPC_SAIDA T 
			WHERE 
					T.CD_EMP = I.CD_EMP 
				AND T.CD_FILIAL = I.CD_FILIAL 
				AND T.CD_NF = I.CD_NF
				AND T.CD_IT = I.CD_IT 
			)
	ORDER BY I.CD_PROD;

	--SELECT * FROM #TEMP_DIVERGENCIAS_NOTAS;

	INSERT INTO EST_NF_SAI_IT_EST_SNGPC_PENDENTE
	SELECT CD_EMP, CD_FILIAL, CD_NF, CD_PROD, CD_IT, GETDATE() FROM #TEMP_DIVERGENCIAS_NOTAS;

	BEGIN TRY
			DROP TABLE #TEMP_DIVERGENCIAS_NOTAS;
	END TRY
	BEGIN CATCH
	END CATCH

END
GO


