IF  EXISTS (SELECT 1 FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BUSCA_VENDAS_MENSAL_FILIAIS]') 
            AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[UFD_BUSCA_VENDAS_MENSAL_FILIAIS]
GO

CREATE  FUNCTION [dbo].[UFD_BUSCA_VENDAS_MENSAL_FILIAIS](@CD_EMP		INT , --Codigo da Empresa
												 @CD_FILIAL_DEP INT, --CÓDIGO FILIAL DEPÓSITO
												 @CD_FILIAL     VARCHAR(8000), --Codigo da Filiais
												 @DT_ATUAL		DATETIME,     
												 @QTD_MESES		INT,
												 @CD_PROD		INT )

RETURNS @RsRetorno TABLE (SEQ INT IDENTITY (1, 1),								
						ANO						INT,
						MES						INT,
						CD_PROD					INT,
						QTD_VENDIDO				MONEY,
						VLR_VENDIDO				MONEY
						) 
AS
BEGIN

	DECLARE @RsTemp TABLE (SEQ INT IDENTITY (1, 1),								
							ANO						INT,
							MES						INT,
							CD_PROD					INT,
							QTD_VENDIDO				MONEY,
							VLR_VENDIDO				MONEY
							) 

	DECLARE @MES INT
	DECLARE @ANO INT

	DECLARE @RsAno TABLE (ANO INT, MES INT, ANO_MES INT)
	DECLARE @QTD_ATUAL INT
	DECLARE @DATA_ATUAL DATETIME	
	
	SET @QTD_ATUAL = 1;

	WHILE (@QTD_ATUAL - @QTD_MESES) <= 0
		BEGIN
			SELECT @DATA_ATUAL = DATEADD ( mm , (-1 * @QTD_ATUAL), @DT_ATUAL) 
			SELECT @MES = MONTH(@DATA_ATUAL)
			SELECT @ANO = YEAR(@DATA_ATUAL)

			INSERT INTO @RsAno (ANO,MES,ANO_MES)
			SELECT 
				@ANO,
				@MES,
				@ANO*100+@MES
		SELECT @QTD_ATUAL = @QTD_ATUAL + 1
	END

	DECLARE @RsProdMovimento TABLE (CD_EMP INT,
							  CD_PROD INT,
							  ANO INT,
							  MES INT,
							  QT_VD MONEY,
							  VLR_VD MONEY)

	IF EXISTS (SELECT
					CD_EMP
			   FROM
					PRC_FILIAL_DEPOSITO
				WHERE
					CD_EMP =@CD_EMP
					AND CD_FILIAL = @CD_FILIAL_DEP)
	begin --Soma das filiais
		INSERT INTO @RsProdMovimento
		SELECT
			CD_EMP,
			CD_PROD,
			V.ANO,
			V.MES,
			QT_VD AS QT_VD_PROD,
			VLR_VD AS VLR_VD_PROD
		FROM
			EST_PROD_VD V INNER JOIN @RsAno a ON
			V.ANO_MES=a.ANO_MES
	end
	else --Filiais
	begin
		INSERT INTO @RsProdMovimento
		SELECT
			CD_EMP,
			CD_PROD,
			V.ANO,
			V.MES,
			QT_VD AS QT_VD_PROD,
			VLR_VD AS VLR_VD_PROD
		FROM
			EST_PROD_CPL_VD V INNER JOIN @RsAno a ON
			V.ANO_MES=a.ANO_MES
		WHERE
			V.CD_EMP =@CD_EMP
			AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
	end

	INSERT INTO @RsTemp (
					ANO,
					MES,
					CD_PROD,
					QTD_VENDIDO,
					VLR_VENDIDO
					)
	SELECT
		ANO,
		MES,
		@CD_PROD,
		SUM(QT_VD_PROD) AS QT_VD_PROD,
		SUM(VLR_VD_PROD) AS VLR_VD_PROD
	FROM
	(
		SELECT
			V.ANO,
			V.MES,
			SUM(QT_VD) AS QT_VD_PROD,
			SUM(VLR_VD) AS VLR_VD_PROD
		FROM
			@RsProdMovimento V 
		WHERE
			CD_EMP =@CD_EMP
			AND CD_PROD =@CD_PROD	
		GROUP BY
			V.ANO,
			V.MES
	) RS 
	GROUP BY
		ANO,
		MES

	INSERT INTO @RsTemp (
					ANO,
					MES,
					CD_PROD,
					QTD_VENDIDO,
					VLR_VENDIDO
					)
	SELECT
		a.ANO,
		a.MES,
		@CD_PROD,
		0 AS QT_VD_PROD,
		0 AS VLR_VD_PROD
	FROM
		@RsAno a LEFT OUTER JOIN @RsTemp t ON
		a.ANO = t.ANO AND
		a.MES = t.MES
	WHERE
		t.ANO IS NULL

	INSERT INTO @RsRetorno
	SELECT
		rs.ANO,
		rs.MES,
		rs.CD_PROD,
		rs.QTD_VENDIDO,
		rs.VLR_VENDIDO
	FROM
		@RsTemp rs 

	RETURN		
END