 /****** Object:  UserDefinedFunction [dbo].[UFD_EST_PROD_TRIB]    Script Date: 04/29/2014 23:12:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_CPL_POS_PERIODO]') 
			AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PROD_CPL_POS_PERIODO]
GO
GO 
/*
	Extrato do Produto mas
	conhecido como KARDEX

	EMPRESA: CORPORATE BUSINES
	DATA INICIO: 01/01/2004
	DATA TERMINO:01/01/2004 
	PROFISSIONAL:  Cristiano Nunes

	Alterado em 08/08/2006 por Leonardo: 
	na carga de @RS_SUBTRAI, trocado o select na view V_PDV_VD_IT pela juncao de PDV_VD com PDV_VD_IT.

*/
--SELECT * FROM UFD_EST_PROD_CPL_POS_PERIODO(1,16,81292,'2017-03-14',0)
CREATE FUNCTION [dbo].[UFD_EST_PROD_CPL_POS_PERIODO](
--CREATE FUNCTION UFD_EST_PROD_CPL_POS_PERIODO(
					      @CD_EMP		INT,
					      @CD_FILIAL			INT,
					      @CD_PROD				INT,
					      @DT_POSICAO				DATETIME,
						  @TP_EST				INT) 
RETURNS @RS_RETORNO TABLE (
			   CD_PROD 	INT, 
			   QT_IT 	MONEY) WITH ENCRYPTION  AS
BEGIN

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
DECLARE @DT_FECHA DATETIME
DECLARE @DT_INI DATETIME
DECLARE @DT_FIM DATETIME

SET @DT_INI=DBO.F_INICIO_MES(@DT_POSICAO)
SET @DT_FIM = @DT_POSICAO
SET @DT_FECHA = DBO.F_INICIO_MES(@DT_INI) -1

-------------------------------------------------------------------------------
IF @TP_EST = 0 --Estoque comercializacao  
Begin
INSERT INTO @RS_SOMA
SELECT       
	EST_FECHA.CD_PROD,   
	CASE WHEN @TP_EST= 0 THEN  
		EST_FECHA.QT_EST  
	WHEN @TP_EST=1 THEN  
		ISNULL(EST_FECHA.QT_EST_FLUT,0)  
	WHEN @TP_EST=2 THEN  
		ISNULL(EST_FECHA.QT_EST_EM_TERC,0)  
	WHEN @TP_EST=3 THEN  
		ISNULL(EST_FECHA.QT_EST_DE_TERC,0)  
	END
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)  
	AND EST_FECHA.MES_FECHA = MONTH(@DT_FECHA)   
	AND EST_FECHA.CD_PROD = @CD_PROD  
UNION ALL
	SELECT  
		EST_NF_ENT_IT.CD_PROD, 
		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 GLB_MOV_OP ON
		EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	WHERE   
		EST_NF_ENT_IT.CD_EMP = @CD_EMP 
		AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL
		AND EST_NF_ENT_IT.DT_ENT BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_ENT_IT.CD_PROD = @CD_PROD
		AND GLB_MOV_OP.ATU_EST = 1
		AND EST_NF_ENT.STS_NF = 0 
		AND GLB_MOV_OP.TP_ESTQ = 0
UNION ALL
	SELECT     
		CD_PROD, 
		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

	-------------------------------------------------------------------------------
	--Soma as quantidades que Subtrair�o ao extrato
	INSERT INTO @RS_SUBTRAI
	SELECT     
		@CD_PROD, 
		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 AND
		i.DT_VD = V.DT_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
	UNION ALL
	SELECT    	
		CD_PROD, 
		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 GLB_MOV_OP ON
		EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	WHERE   
		EST_NF_SAI_IT.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		AND EST_NF_SAI_IT.CD_EMP = @CD_EMP 
		AND EST_NF_SAI_IT.CD_FILIAL = @CD_FILIAL
		AND EST_NF_SAI_IT.CD_PROD = @CD_PROD
		AND GLB_MOV_OP.BX_EST = 1
		AND EST_NF_SAI.STS_NF = 0
		AND GLB_MOV_OP.TP_ESTQ = 0
		AND NOT EXISTS (SELECT 
					CD_NF 
				FROM 
					PDV_VD_EST_NF_SAI
				WHERE
					EST_NF_SAI.CD_EMP = PDV_VD_EST_NF_SAI.CD_EMP AND
					EST_NF_SAI.CD_FILIAL = PDV_VD_EST_NF_SAI.CD_FILIAL AND
					EST_NF_SAI.CD_NF = PDV_VD_EST_NF_SAI.CD_NF)
End
---------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
IF @TP_EST = 1 --Estoque flutuante
Begin
INSERT INTO @RS_SOMA
SELECT       
	EST_FECHA.CD_PROD,   
	CASE WHEN @TP_EST= 0 THEN  
		EST_FECHA.QT_EST  
	WHEN @TP_EST=1 THEN  
		ISNULL(EST_FECHA.QT_EST_FLUT,0)  
	WHEN @TP_EST=2 THEN  
		ISNULL(EST_FECHA.QT_EST_EM_TERC,0)  
	WHEN @TP_EST=3 THEN  
		ISNULL(EST_FECHA.QT_EST_DE_TERC,0)  
	END
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)  
	AND EST_FECHA.MES_FECHA = MONTH(@DT_FECHA)   
	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 
		AND TP_ESTQ = 1
	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 = 1
	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 STS_NF = 0
		AND TP_ESTQ = 1
		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
INSERT INTO @RS_SOMA
SELECT       
	EST_FECHA.CD_PROD,   
	CASE WHEN @TP_EST= 0 THEN  
		EST_FECHA.QT_EST  
	WHEN @TP_EST=1 THEN  
		ISNULL(EST_FECHA.QT_EST_FLUT,0)  
	WHEN @TP_EST=2 THEN  
		ISNULL(EST_FECHA.QT_EST_EM_TERC,0)  
	WHEN @TP_EST=3 THEN  
		ISNULL(EST_FECHA.QT_EST_DE_TERC,0)  
	END
FROM           
	EST_FECHA 
	INNER JOIN EST_PROD_CPL ON   
	EST_FECHA.CD_EMP = EST_PROD_CPL.CD_EMP AND   
	EST_FECHA.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND   
	EST_FECHA.CD_PROD = EST_PROD_CPL.CD_PROD   
WHERE       
	EST_FECHA.CD_EMP = @CD_EMP   
	AND EST_FECHA.CD_FILIAL = @CD_FILIAL   
	AND EST_FECHA.ANO_FECHA = YEAR(@DT_FECHA)  
	AND EST_FECHA.MES_FECHA = MONTH(@DT_FECHA)   
	AND EST_FECHA.CD_PROD = @CD_PROD  

	--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
INSERT INTO @RS_SOMA
SELECT       
	EST_FECHA.CD_PROD,   
	CASE WHEN @TP_EST= 0 THEN  
		EST_FECHA.QT_EST  
	WHEN @TP_EST=1 THEN  
		ISNULL(EST_FECHA.QT_EST_FLUT,0)  
	WHEN @TP_EST=2 THEN  
		ISNULL(EST_FECHA.QT_EST_EM_TERC,0)  
	WHEN @TP_EST=3 THEN  
		ISNULL(EST_FECHA.QT_EST_DE_TERC,0)  
	END
FROM           
	EST_FECHA 
	INNER JOIN EST_PROD_CPL ON   
	EST_FECHA.CD_EMP = EST_PROD_CPL.CD_EMP AND   
	EST_FECHA.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND   
	EST_FECHA.CD_PROD = EST_PROD_CPL.CD_PROD   
WHERE       
	EST_FECHA.CD_EMP = @CD_EMP   
	AND EST_FECHA.CD_FILIAL = @CD_FILIAL   
	AND EST_FECHA.ANO_FECHA = YEAR(@DT_FECHA)  
	AND EST_FECHA.MES_FECHA = MONTH(@DT_FECHA)   
	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 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_PROD, SUM(QT_IT)      
            FROM 
                   @RS_SOMA
            GROUP BY 
                   CD_PROD

-------------------------------
UPDATE @RS_RETORNO SET 
                         RS_RETORNO.QT_IT = RS_RETORNO.QT_IT - ISNULL(RS_TOT_SUBTRAI.QT_TOT,0)
                   FROM  
                         @RS_RETORNO RS_RETORNO INNER JOIN  @RS_TOT_SUBTRAI RS_TOT_SUBTRAI ON
						 RS_RETORNO.CD_PROD = RS_TOT_SUBTRAI.CD_PROD
-------------------------------
INSERT INTO @RS_RETORNO
            SELECT 
                   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
