/****** Object:  UserDefinedFunction [dbo].[BUSCA_EST_NF_ENT_IT_QT_DEV]    Script Date: 08/01/2014 01:38:27 ******/
IF EXISTS( SELECT * FROM sys.objects
			WHERE  object_id = OBJECT_ID(N'[dbo].[BUSCA_EST_NF_ENT_IT_QT_DEV]')
					AND type IN(N'FN', N'IF', N'TF', N'FS', N'FT'))
	BEGIN
		DROP FUNCTION [dbo].[BUSCA_EST_NF_ENT_IT_QT_DEV];
	END;
GO

--Versao 2489
--SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1,90,84084,84516)

CREATE FUNCTION [dbo].[BUSCA_EST_NF_ENT_IT_QT_DEV](@CD_EMP INT, @CD_FILIAL INT, @CD_NF_ENT INT, @CD_PROD   INT)
RETURNS MONEY

AS
BEGIN
	DECLARE @QT_DEV MONEY;
	DECLARE @QT_ENTRADA MONEY;
	DECLARE @QT_DEV_CANCEL MONEY;
	DECLARE @QT_DEV_CALC MONEY;

----------------------------------------------------------------------------------------------------------
SET @QT_DEV = (SELECT SUM(QT_IT_DEV) 
				FROM
					V_EST_NF_ENT_NF_SAI_IT
				WHERE  
					V_EST_NF_ENT_NF_SAI_IT.CD_EMP	 = @CD_EMP
				AND V_EST_NF_ENT_NF_SAI_IT.CD_FILIAL = @CD_FILIAL
				AND V_EST_NF_ENT_NF_SAI_IT.CD_NF_ENT = @CD_NF_ENT
				AND V_EST_NF_ENT_NF_SAI_IT.CD_PROD	 = @CD_PROD
				AND V_EST_NF_ENT_NF_SAI_IT.STS_NF	 = 0);

----------------------------------------------------------------------------------------------------------

SET @QT_ENTRADA = (SELECT SUM(EST_NF_ENT_IT.QT_IT)
						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
					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_ENT
						AND EST_NF_ENT.STS_NF = 0
						AND EST_NF_ENT_IT.CD_PROD = @CD_PROD
						);
		 
----------------------------------------------------------------------------------------------------------		 
SET @QT_DEV_CANCEL =(SELECT 
							SUM(EST_NF_SAI_IT.QT_IT)
						FROM
							EST_NF_SAI
							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

							INNER JOIN EST_NF_ENT_NF_SAI ON 
								EST_NF_SAI_IT.CD_EMP	= EST_NF_ENT_NF_SAI.CD_EMP
							AND EST_NF_SAI_IT.CD_FILIAL = EST_NF_ENT_NF_SAI.CD_FILIAL
							AND EST_NF_SAI_IT.CD_NF		= EST_NF_ENT_NF_SAI.CD_NF_SAI

							INNER JOIN EST_NF_SAI_NF_ENT ON 
								EST_NF_SAI_IT.CD_EMP	= EST_NF_SAI_NF_ENT.CD_EMP
							AND EST_NF_SAI_IT.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL
							AND EST_NF_SAI_IT.CD_NF		= EST_NF_SAI_NF_ENT.CD_NF_SAI
						WHERE  
							EST_NF_ENT_NF_SAI.CD_EMP = @CD_EMP
						AND EST_NF_ENT_NF_SAI.CD_FILIAL = @CD_FILIAL
						AND EST_NF_ENT_NF_SAI.CD_NF_ENT = @CD_NF_ENT
						AND EST_NF_SAI.STS_NF = 0
						AND EST_NF_SAI_IT.CD_PROD = @CD_PROD);
----------------------------------------------------------------------------------------------------------						
		SET @QT_DEV_CALC = 0;

		IF ISNULL(@QT_DEV, 0) > 0
		BEGIN
			IF ISNULL(@QT_DEV_CANCEL, 0) < ISNULL(@QT_DEV, 0)
				BEGIN
					SET @QT_DEV_CALC = (ISNULL(@QT_DEV, 0) - (ISNULL(@QT_ENTRADA, 0) + ISNULL(@QT_DEV_CANCEL, 0)));
				END;
		END;
		 
		IF @QT_DEV_CALC < 0
			BEGIN
				SET @QT_DEV_CALC = 0;
			END;

		 RETURN @QT_DEV_CALC;


	 END;


