BEGIN TRY 
    DROP FUNCTION [dbo].[BUSCA_EST_PROD_MES_ANO_QT_SAIDA]  
END TRY BEGIN CATCH END CATCH
/****** Object:  UserDefinedFunction [dbo].[BUSCA_EST_PROD_MES_ANO_QT_SAIDA]    Script Date: 15/03/2017 12:49:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	BUSCA A QUANTIDA MOVIMENTADA DO PRODUTO EM UM DETERMINADO MES / ANO

	EMPRESA: CORPORATE BUSINES
	DATA INICIO: 30/10/2003
	DATA TERMINO:30/10/2003 
	PROFISSIONAL:  Cristiano Nunes
*/
--SELECT DBO.BUSCA_EST_PROD_MES_ANO_QT_SAIDA(1,99,7,2007,6504)
CREATE FUNCTION [dbo].[BUSCA_EST_PROD_MES_ANO_QT_SAIDA] (
--CREATE FUNCTION BUSCA_EST_PROD_MES_ANO_QT_SAIDA (
								 @CD_EMP			INT,
								 @CD_FILIAL 		INT,
								 @MES			INT,
								 @ANO			INT,
								 @CD_PROD			INT	
								)
RETURNS MONEY AS
BEGIN
DECLARE @QUANT_SAIDA MONEY
DECLARE @TP_FIL_MTZ  INT
DECLARE @RS_PROD TABLE(QT_PROD MONEY)

SET @TP_FIL_MTZ = (SELECT FILIAL_MTZ FROM PRC_FILIAL WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL)

IF @TP_FIL_MTZ = 0 
BEGIN
	--Buscando venda no pdv
	INSERT INTO @RS_PROD
	   SELECT     							
		  SUM(v.QT_VD) AS QT_SAI
	   FROM 
		  EST_PROD_CPL_VD v
	   WHERE						 	
		  v.CD_EMP = @CD_EMP
		  AND v.CD_FILIAL = @CD_FILIAL
		  AND v.MES= @MES
		  AND V.ANO = @ANO
		  AND V.CD_PROD = @CD_PROD
	
	--Buscando venda com nota fiscal
	INSERT INTO @RS_PROD
					SELECT     
						SUM(EST_NF_SAI_IT.QT_IT) AS QT_SAI
					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 
						INNER JOIN GLB_MOV_OP ON
						EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
					WHERE 
						EST_NF_SAI.CD_EMP = @CD_PROD
						AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
						AND EST_NF_SAI.STS_NF = 0
						AND MONTH(EST_NF_SAI.DT_EMI_NF) = @MES
						AND YEAR(EST_NF_SAI.DT_EMI_NF) = @ANO
						AND EST_NF_SAI_IT.CD_PROD = @CD_PROD
						AND GLB_MOV_OP.BX_EST = 1
						AND GLB_MOV_OP.TP_PRECO = 0
						AND NOT EXISTS (SELECT 
									CD_NF 
								  FROM 
										PDV_VD_EST_NF_SAI
								 WHERE
										CD_EMP = @CD_EMP) 
END 
IF @TP_FIL_MTZ = 1 
BEGIN
	--Buscando venda no pdv
	INSERT INTO @RS_PROD
	   SELECT     							
		  SUM(C.QT_VD) AS QT_SAI
	   FROM 
		  EST_PROD_CPL_VD C							
	   WHERE						 	
		  C.CD_EMP = @CD_EMP
		  AND C.MES = @MES
		  AND C.ANO = @ANO
		  AND C.CD_PROD = @CD_PROD
	
	--Buscando venda com nota fiscal
	INSERT INTO @RS_PROD
    SELECT     
	   EST_NF_SAI_IT.QT_IT AS QT_SAI
    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 
	   INNER JOIN GLB_MOV_OP ON
	   EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
    WHERE 						
	   EST_NF_SAI.CD_EMP = @CD_PROD
	   AND EST_NF_SAI.STS_NF = 0
	   AND MONTH(EST_NF_SAI.DT_EMI_NF) = @MES
	   AND YEAR(EST_NF_SAI.DT_EMI_NF) = @ANO
	   AND EST_NF_SAI_IT.CD_PROD = @CD_PROD
	   AND GLB_MOV_OP.BX_EST = 1
	   AND GLB_MOV_OP.TP_PRECO = 0
	   AND NOT EXISTS (SELECT 
				    CD_NF 
				FROM 
					   PDV_VD_EST_NF_SAI
				WHERE
					   CD_EMP =@CD_EMP) 
END 
SET @QUANT_SAIDA = (SELECT 
								ISNULL(SUM(QT_PROD),0) AS QT_PROD
						  FROM	
								@RS_PROD)
RETURN(@QUANT_SAIDA)
END
GO


