IF EXISTS (select * from INFORMATION_SCHEMA.ROUTINES
				WHERE ROUTINE_NAME='TST_EST_NF_ENT_STATUS' 
						AND ROUTINE_TYPE= 'FUNCTION')
drop FUNCTION TST_EST_NF_ENT_STATUS
GO

/*
	Testa Status da Nota Fiscal de Entrada
	EMPRESA: CORPORATE BUSINES
	DATA INICIO:  15/12/2003
	DATA TERMINO: 15/12/2003 
	PROFISSIONAL: Cristiano Nunes
*/
--SELECT DBO.TST_EST_NF_ENT_STATUS(1,1,5)
CREATE FUNCTION [dbo].[TST_EST_NF_ENT_STATUS](@CD_EMP    INT
										   , @CD_FILIAL INT
										   , @CD_NF     INT)
RETURNS NVARCHAR(30)
AS
	 BEGIN
		 DECLARE @QT_ENT MONEY;
		 DECLARE @QT_DEV MONEY;
		 DECLARE @QT_DEV_CANCEL MONEY;
		 DECLARE @STS_NF NVARCHAR(30);


		 SET @QT_ENT =( SELECT
							SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT_MOV
						FROM
							EST_NF_ENT
							INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP
														AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL
														AND EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF
														AND EST_NF_ENT.DT_ENT = EST_NF_ENT_IT.DT_ENT 
						WHERE  EST_NF_ENT_IT.CD_EMP = @CD_EMP
							   AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL
							   AND EST_NF_ENT_IT.CD_NF = @CD_NF);


		SET @QT_DEV =( SELECT
					SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT_MOV
					FROM
					EST_NF_ENT_NF_SAI
					INNER JOIN EST_NF_SAI ON EST_NF_ENT_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP
												AND EST_NF_ENT_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL
												AND EST_NF_ENT_NF_SAI.CD_NF_SAI = EST_NF_SAI.CD_NF
					INNER JOIN EST_NF_SAI_IT ON EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP
												AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
												AND EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF
												AND EST_NF_SAI.DT_EMI_NF = EST_NF_SAI_IT.DT_EMI_NF
				WHERE	   EST_NF_SAI_IT.CD_EMP = @CD_EMP
						AND EST_NF_SAI_IT.CD_FILIAL = @CD_FILIAL
						AND EST_NF_ENT_NF_SAI.CD_NF_ENT = @CD_NF
						AND EST_NF_SAI.STS_NF = 0);

					   
		 SET @QT_DEV_CANCEL =( SELECT SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT_MOV_CANCEL
								FROM
									EST_NF_ENT
									INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT.CD_EMP		 = EST_NF_ENT_IT.CD_EMP
																AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL
																AND EST_NF_ENT.CD_NF	 = EST_NF_ENT_IT.CD_NF
																AND EST_NF_ENT.DT_ENT	 = EST_NF_ENT_IT.DT_ENT

									INNER JOIN EST_NF_SAI_NF_ENT ON EST_NF_ENT.CD_EMP = EST_NF_SAI_NF_ENT.CD_EMP
																	AND EST_NF_ENT.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL
																	AND EST_NF_ENT.CD_NF = EST_NF_SAI_NF_ENT.CD_NF_ENT

									INNER JOIN EST_NF_ENT_NF_SAI ON EST_NF_SAI_NF_ENT.CD_EMP = EST_NF_ENT_NF_SAI.CD_EMP
																	AND EST_NF_SAI_NF_ENT.CD_FILIAL = EST_NF_ENT_NF_SAI.CD_FILIAL
																	AND EST_NF_SAI_NF_ENT.CD_NF_SAI = EST_NF_ENT_NF_SAI.CD_NF_SAI
								WHERE		EST_NF_ENT_IT.CD_EMP	= @CD_EMP
										AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL
										AND EST_NF_ENT_IT.CD_NF		= @CD_NF
										AND EST_NF_ENT.STS_NF = 0);


		 SET @QT_DEV = ISNULL(@QT_DEV,0) - ISNULL(@QT_DEV_CANCEL,0);
		 
		 SET @STS_NF = CASE
						   WHEN @QT_DEV = 0 THEN 'NORMAL'
						   WHEN ISNULL(@QT_ENT,0) - @QT_DEV = 0 THEN 'DEVOLUÇÃO TOTAL'
						   ELSE 'DEVOLUÇÃO PARCIAL'
					   END;


		 RETURN(@STS_NF);
	 END;
GO


