


/****** Object:  UserDefinedFunction [dbo].[UFD_EST_PROD_CPL_POS_DIA]    Script Date: 07/23/2012 19:04:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_CPL_POS_DIA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PROD_CPL_POS_DIA]
GO
GO

-- Retornar o saldo inicial do dia
-- Ramam Freitas
-- 2013 09 27

CREATE FUNCTION [dbo].[UFD_EST_PROD_CPL_POS_DIA](
					      @CD_EMP				INT,
					      @CD_FILIAL			INT,
					      @CD_PROD				INT,
					      @DT_FIM				DATETIME,
					      @TP_EST				INT)

RETURNS @RS_RETORNO TABLE ( CD_FILIAL	INT,
							CD_PROD 	INT, 
							QT_IT 		MONEY) AS
BEGIN

DECLARE @DT_FECHA_EST DATETIME
DECLARE @DT_INI_FILIAL	DATETIME
DECLARE @DT_INI		    DATETIME


SET @DT_INI_FILIAL = (SELECT DT_INI_SISTEMA 
						FROM PRC_FILIAL 
						WHERE CD_EMP=@CD_EMP AND CD_FILIAL=@CD_FILIAL)

SET @DT_INI		  = dbo.F_INICIO_MES(@DT_FIM);
SET @DT_FECHA_EST = @DT_INI-1 ;


IF @DT_FIM <  @DT_INI_FILIAL 
BEGIN
	set @DT_FIM = @DT_INI_FILIAL
END
 
IF @DT_INI <  @DT_INI_FILIAL 
	BEGIN
		SET @DT_INI=@DT_INI_FILIAL
	END  
	
set @DT_FIM = @DT_FIM -1

DECLARE @RS_SOMA          TABLE (CD_PROD INT, QT_IT  MONEY)
DECLARE @RS_SUBTRAI       TABLE (CD_PROD INT, QT_IT  MONEY)
DECLARE @RS_TOT_SUBTRAI   TABLE (CD_PROD INT, QT_TOT MONEY)
DECLARE @QT MONEY
 
 
-------------------------------------------------------------------------------
IF @TP_EST = 0 --Estoque comercializacao  
Begin
	 
	INSERT INTO @RS_SOMA  
	SELECT    
		EST_FECHA.CD_PROD,   
		EST_FECHA.QT_EST   
	FROM           
		EST_FECHA   
	WHERE       
		EST_FECHA.CD_EMP = @CD_EMP   
		AND EST_FECHA.CD_FILIAL = @CD_FILIAL   
		AND EST_FECHA.ANO_FECHA = YEAR(@DT_FECHA_EST)  
		AND EST_FECHA.MES_FECHA = MONTH(@DT_FECHA_EST)   
		AND EST_FECHA.CD_PROD = @CD_PROD  
		  
	--Soma as quantidades que adicionar?o ao extrato
	INSERT INTO @RS_SOMA
	SELECT  
		CD_PROD, 
		SUM(QT_IT)
	FROM    
		V_EST_NF_ENT
	WHERE   
		CD_EMP = @CD_EMP 
		AND CD_FILIAL = @CD_FILIAL
		AND DT_ENT BETWEEN @DT_INI AND @DT_FIM  
		AND CD_PROD = @CD_PROD
		AND ATU_EST = 1
		AND STS_NF = 0
	GROUP BY 
		CD_PROD 
		 
	INSERT INTO @RS_SOMA
	SELECT     
		CD_PROD, 
		SUM(QT_IMP)
	FROM 
		EST_PROD_IMP
	WHERE     
		CD_EMP = @CD_EMP 
		AND CD_FILIAL = @CD_FILIAL 
		AND DT_CAD BETWEEN @DT_INI AND @DT_FIM
		AND CD_PROD = @CD_PROD
		AND TP_EST = 0
	GROUP BY 
		CD_PROD

	-------------------------------------------------------------------------------
	--Soma as quantidades que Subtrair?o ao extrato
	INSERT INTO @RS_SUBTRAI
	SELECT     
		@CD_PROD, 
		SUM(QT_IT)
	FROM    
		PDV_VD_IT i		
		INNER JOIN PDV_VD v ON 
		i.CD_EMP	=v.CD_EMP AND 
		i.CD_FILIAL	=v.CD_FILIAL AND 
		i.CD_VD		=v.CD_VD
	WHERE   
		v.CD_EMP = @CD_EMP
		AND v.CD_FILIAL = @CD_FILIAL
		AND v.DT_VD BETWEEN @DT_INI AND @DT_FIM
		AND i.CD_PROD = @CD_PROD
		and v.ST_VD = 0
		AND I.ST_IT = 1

	INSERT INTO @RS_SUBTRAI
	SELECT    	
		CD_PROD, 
		SUM(QT_IT)
	FROM    
		V_EST_NF_SAI
	WHERE   
		DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND CD_EMP = @CD_EMP 
		AND CD_FILIAL = @CD_FILIAL
		AND CD_PROD = @CD_PROD
		AND BX_EST = 1
		AND STS_NF = 0
		AND NOT EXISTS (SELECT 
					CD_NF 
				FROM 
					PDV_VD_EST_NF_SAI
				WHERE
					V_EST_NF_SAI.CD_EMP = PDV_VD_EST_NF_SAI.CD_EMP AND
					V_EST_NF_SAI.CD_FILIAL = PDV_VD_EST_NF_SAI.CD_FILIAL AND
					V_EST_NF_SAI.CD_NF = PDV_VD_EST_NF_SAI.CD_NF)
	GROUP BY CD_PROD 
End
---------------------------------------------------------------------------------------------
--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
IF @TP_EST = 2 --Estoque em terceiros  
Begin
	--Soma as quantidades que adicionar?o ao extrato
    --Quanto estoque em terceiro quanto sai entra no estoque
	INSERT INTO @RS_SOMA 
	SELECT    	
		CD_PROD, 
		SUM(QT_IT)
	 FROM      
	    V_EST_NF_SAI  
	 WHERE     
  DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM  
  AND CD_EMP = @CD_EMP   
  AND CD_FILIAL = @CD_FILIAL  
  AND CD_PROD = @CD_PROD  
  AND BX_EST = 1  
  AND TP_ESTQ = 0  
  AND STS_NF = 0  
  AND ENVIO_OPERADOR_LOG =1   
	GROUP BY 
		CD_PROD 

	INSERT INTO @RS_SOMA
	SELECT     
		CD_PROD, 
		SUM(QT_IMP)
	FROM 
		EST_PROD_IMP
    WHERE       
	    EST_PROD_IMP.CD_EMP = @CD_EMP   
	    AND EST_PROD_IMP.CD_FILIAL = @CD_FILIAL   
	    AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM  
	    AND EST_PROD_IMP.CD_PROD = @CD_PROD  
	    AND EST_PROD_IMP.TP_EST = 2  
	    AND TP_IMP < 4  
	GROUP BY 
			CD_PROD

	-------------------------------------------------------------------------------
    --Quanto estoque me terceiro quanto entra sai do estoque
	INSERT INTO @RS_SUBTRAI
		SELECT  
			CD_PROD, 
			SUM(QT_IT) 
		FROM      
			V_EST_NF_ENT  
		WHERE     
			CD_EMP = @CD_EMP   
			AND CD_FILIAL = @CD_FILIAL  
			AND DT_ENT BETWEEN @DT_INI AND @DT_FIM  
			AND CD_PROD = @CD_PROD  
			AND ATU_EST = 1  
			AND TP_ESTQ = 0  
			AND STS_NF = 0  
			AND FLAG_RETORNO_OPERADOR_LOG =1 
		GROUP BY 
			CD_PROD

End

--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
IF @TP_EST = 3 --Estoque de terceiros  
Begin
	--Soma as quantidades que adicionar?o ao extrato
	INSERT INTO @RS_SOMA 
		SELECT  
			CD_PROD, 
			SUM(QT_IT) 
		FROM      
			V_EST_NF_ENT  
		WHERE     
		   CD_EMP = @CD_EMP   
		   AND CD_FILIAL = @CD_FILIAL  
		   AND DT_ENT BETWEEN @DT_INI AND @DT_FIM  
		   AND CD_PROD = @CD_PROD  
		   AND ATU_EST = 1  
		   AND TP_ESTQ = 3  
		   AND STS_NF = 0  
		   AND FLAG_ENTRADA_OPERADOR_LOG =1 
		GROUP BY 
			CD_PROD


	INSERT INTO @RS_SOMA
	SELECT     
		CD_PROD, 
		SUM(QT_IMP)
	FROM 
		EST_PROD_IMP
    WHERE       
	  EST_PROD_IMP.CD_EMP = @CD_EMP   
	  AND EST_PROD_IMP.CD_FILIAL = @CD_FILIAL   
	  AND EST_PROD_IMP.DT_CAD BETWEEN @DT_INI AND @DT_FIM  
	  AND EST_PROD_IMP.CD_PROD = @CD_PROD  
	  AND EST_PROD_IMP.TP_EST = 3  
	  AND TP_IMP < 4  

	GROUP BY 
			CD_PROD

	-------------------------------------------------------------------------------
	INSERT INTO @RS_SUBTRAI
	SELECT    	
		CD_PROD, 
		SUM(QT_IT)
	 FROM      
	    V_EST_NF_SAI  
	 WHERE     
		  DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM  
		  AND CD_EMP = @CD_EMP   
		  AND CD_FILIAL = @CD_FILIAL  
		  AND CD_PROD = @CD_PROD  
		  AND BX_EST = 1  
		  AND TP_ESTQ = 3  
		  AND STS_NF = 0  
		  AND FLAG_RETORNO_OPERADOR_LOG =1   
	GROUP BY 
		CD_PROD 

End

--Somando os valores para subtra??o 

INSERT INTO @RS_TOT_SUBTRAI
            SELECT CD_PROD,
                   SUM(QT_IT)
            FROM @RS_SUBTRAI
            GROUP BY CD_PROD
 
---------------------------------------------------------------------------------------------
INSERT INTO @RS_RETORNO 
            SELECT 
                   @CD_FILIAL, CD_PROD, SUM(QT_IT)      
            FROM 
                   @RS_SOMA
            GROUP BY 
                   CD_PROD

-------------------------------
UPDATE @RS_RETORNO SET 
                         QT_IT = QT_IT - ISNULL(RS_TOT_SUBTRAI.QT_TOT,0)
                   FROM  
                         @RS_TOT_SUBTRAI RS_TOT_SUBTRAI,
                         @RS_RETORNO RS_RETORNO
                   WHERE 
                         RS_RETORNO.CD_PROD = RS_TOT_SUBTRAI.CD_PROD

-------------------------------
INSERT INTO @RS_RETORNO
            SELECT 
                  @CD_FILIAL, RS.CD_PROD, (RS.QT_TOT * -1)
            FROM 
                   @RS_TOT_SUBTRAI RS
            WHERE 
                   NOT EXISTS (SELECT 
                                     CD_PROD 
                               FROM 
                                     @RS_RETORNO
                               WHERE 
                                     CD_PROD = RS.CD_PROD)

RETURN
END


GO


