IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES
			WHERE  TABLE_NAME = 'EST_PROD_SOMATORIA_MTZ' )
	BEGIN
		DROP TABLE EST_PROD_SOMATORIA_MTZ;
	END;
GO
GO

CREATE TABLE EST_PROD_SOMATORIA_MTZ
									(CD_EMP                INT
									, CD_FILIAL             INT
									, CD_PROD               INT
									, CD_SIGLA              INT
									, QTDE_MEDIAF           MONEY
									, QTDE_FINANCIADO       MONEY
									, QTDE_FACEAMENTO       MONEY
									, QTDE_LIMITADOR        MONEY
									, VALID_QTDE_FACEAMENTO DATETIME
									, QT_VD_ATUAL           MONEY
									, QT_VD_MES1            MONEY
									, QT_VD_MES2            MONEY
									, QT_VD_MES3            MONEY
									, LIMITP                MONEY
									, LIMITV                MONEY
									, CD_USU                INT
									, DT_CAD                DATETIME
									, DT_ULT_ALT            DATETIME
									, SAZ                   INT
									, DT_NASC               DATETIME
									, MEDIAF_TRANSF         MONEY
									, STS_PROD              INT
									 CONSTRAINT [PK_EST_PROD_SOMATORIA_MTZ] PRIMARY KEY CLUSTERED ( CD_PROD , CD_FILIAL, CD_EMP)
);
GO

 /****** Object:  StoredProcedure [dbo].[P_EST_PROD_SOMATORIA_MTZ]    Script Date: 02/10/2014 18:47:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_SOMATORIA_MTZ]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_SOMATORIA_MTZ]
GO 
/****** Object:  StoredProcedure [dbo].[P_EST_PROD_SOMATORIA_MTZ]    Script Date: 02/10/2014 18:47:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO 
--versao 2651
--EXEC P_EST_PROD_SOMATORIA_MTZ 999,0,1
CREATE PROCEDURE [dbo].[P_EST_PROD_SOMATORIA_MTZ] (@CD_FILIAL_DEPO INT, @CD_PROD_PARAM INT=0, @FLAG_DESATIVADO INT=0) AS 
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY HIGH;

SET CONTEXT_INFO 0x66666; --Evita disparar a trigger para est_prod_cpl

 
PRINT 'Criando tabela temporaria ' + convert(varchar,getdate(),108)
DELETE FROM EST_PROD_SOMATORIA_MTZ;

if @CD_PROD_PARAM > 0
begin
	
    INSERT INTO EST_PROD_SOMATORIA_MTZ (CD_EMP,CD_FILIAL,CD_PROD,CD_SIGLA,QTDE_MEDIAF,
									    QTDE_FINANCIADO,QTDE_FACEAMENTO,QTDE_LIMITADOR,VALID_QTDE_FACEAMENTO,
									    QT_VD_ATUAL,QT_VD_MES1,QT_VD_MES2,QT_VD_MES3,LIMITP,LIMITV,CD_USU,
									    DT_CAD,DT_ULT_ALT,SAZ,DT_NASC,MEDIAF_TRANSF,STS_PROD)
    SELECT     
	    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP, 
	    @CD_FILIAL_DEPO AS CD_FILIAL,
	    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_PROD, 
	    4 AS CD_CURVA,
	    SUM(V_EST_PROD_CPL_MEDIAF_MEDIAP.QTDE_MEDIA) AS QTDE_MEDIAF,
	    0 AS QTDE_FINANCIADO,
	    0 AS QTDE_FACEAMENTO,
	    0 AS QTDE_LIMITADOR,
	    NULL AS VALID_QTDE_FACEAMENTO,
	    SUM(QT_VD_ATUAL)	AS QT_VD_ATUAL,
	    SUM(QT_VD_MES1)		AS QT_VD_MES1,
	    SUM(QT_VD_MES2)		AS QT_VD_MES2,
	    SUM(QT_VD_MES3)		AS QT_VD_MES3,
	    0					AS LIMITP,
	    0					AS LIMITV,
	    1					AS CD_USU,
	    GETDATE() AS DT_CAD,
	    NULL							AS DT_ULT_ALT,
	    0								AS SAZ,
	    NULL							AS DT_NASC,
	    0	MEDIAF_TRANSF,
	    0	STS_PROD
    FROM         
	    V_EST_PROD_CPL_MEDIAF_MEDIAP 
	    INNER JOIN PRC_FILIAL_DEPOSITO_PRC_FILIAL ON
	    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP = PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_EMP AND 
	    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_FILIAL = PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_FILIAL 
	    INNER JOIN PRC_FILIAL_DEPOSITO ON
	    PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_CTR_DEPO = PRC_FILIAL_DEPOSITO.CD_CTR_DEPO
	    INNER JOIN PRC_FILIAL ON
	    PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_EMP = PRC_FILIAL.CD_EMP AND
	    PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
		AND NOT PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_FILIAL = @CD_FILIAL_DEPO
    WHERE     
	    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP = 1 
	    AND V_EST_PROD_CPL_MEDIAF_MEDIAP.STS_PROD_FILIAL=0
	    AND PRC_FILIAL_DEPOSITO.CD_FILIAL = @CD_FILIAL_DEPO
	    AND PRC_FILIAL.STS_FILIAL = 0
	    AND V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_PROD = @CD_PROD_PARAM 
	    AND EXISTS (SELECT 
					    * 
				    FROM  
					    EST_PROD_CPL 
				    WHERE
					    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP = EST_PROD_CPL.CD_EMP AND
						@CD_FILIAL_DEPO = EST_PROD_CPL.CD_FILIAL AND
					    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_PROD = EST_PROD_CPL.CD_PROD)
    GROUP BY 
	    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP, 
	    V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_PROD;


end 
else
begin
	IF @FLAG_DESATIVADO=0
	begin
	INSERT INTO EST_PROD_SOMATORIA_MTZ (CD_EMP,CD_FILIAL,CD_PROD,CD_SIGLA,QTDE_MEDIAF,
											QTDE_FINANCIADO,QTDE_FACEAMENTO,QTDE_LIMITADOR,VALID_QTDE_FACEAMENTO,
											QT_VD_ATUAL,QT_VD_MES1,QT_VD_MES2,QT_VD_MES3,LIMITP,LIMITV,CD_USU,
											DT_CAD,DT_ULT_ALT,SAZ,DT_NASC,MEDIAF_TRANSF,STS_PROD)
		SELECT     
			V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP, 
			@CD_FILIAL_DEPO AS CD_FILIAL,
			V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_PROD, 
			4 AS CD_CURVA,
			SUM(V_EST_PROD_CPL_MEDIAF_MEDIAP.QTDE_MEDIA) AS QTDE_MEDIAF,
			0 AS QTDE_FINANCIADO,
			0 AS QTDE_FACEAMENTO,
			0 AS QTDE_LIMITADOR,
			NULL AS VALID_QTDE_FACEAMENTO,
			SUM(QT_VD_ATUAL)	AS QT_VD_ATUAL,
			SUM(QT_VD_MES1)		AS QT_VD_MES1,
			SUM(QT_VD_MES2)		AS QT_VD_MES2,
			SUM(QT_VD_MES3)		AS QT_VD_MES3,
			0					AS LIMITP,
			0					AS LIMITV,
			1					AS CD_USU,
			GETDATE() AS DT_CAD,
			NULL							AS DT_ULT_ALT,
			0								AS SAZ,
			NULL							AS DT_NASC,
			0	MEDIAF_TRANSF,
			0	STS_PROD
		FROM         
			V_EST_PROD_CPL_MEDIAF_MEDIAP 
			INNER JOIN PRC_FILIAL_DEPOSITO_PRC_FILIAL ON
			V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP = PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_EMP AND 
			V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_FILIAL = PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_FILIAL 
			INNER JOIN PRC_FILIAL_DEPOSITO ON
			PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_CTR_DEPO = PRC_FILIAL_DEPOSITO.CD_CTR_DEPO
			INNER JOIN PRC_FILIAL ON
			PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_EMP = PRC_FILIAL.CD_EMP AND
			PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
			AND NOT PRC_FILIAL_DEPOSITO_PRC_FILIAL.CD_FILIAL = @CD_FILIAL_DEPO
		WHERE     
			V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP = 1 
			AND V_EST_PROD_CPL_MEDIAF_MEDIAP.STS_PROD_FILIAL=0
			AND PRC_FILIAL_DEPOSITO.CD_FILIAL = @CD_FILIAL_DEPO
			AND PRC_FILIAL.STS_FILIAL = 0
			AND EXISTS (SELECT 
							* 
						FROM  
							EST_PROD_CPL 
						WHERE
							V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP = EST_PROD_CPL.CD_EMP AND
							@CD_FILIAL_DEPO = EST_PROD_CPL.CD_FILIAL AND
							V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_PROD = EST_PROD_CPL.CD_PROD)
		GROUP BY 
			V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_EMP, 
			V_EST_PROD_CPL_MEDIAF_MEDIAP.CD_PROD;
	end
end
/**************************************************************************/
	
PRINT 'Deletando registros para nova insercao ' + convert(varchar,getdate(),108)
if @CD_PROD_PARAM > 0
begin
    DELETE   FROM  EST_PROD_CPL_MEDIAF 
    WHERE 
    CD_EMP = 1 
    AND CD_FILIAL = @CD_FILIAL_DEPO
    AND CD_PROD = @CD_PROD_PARAM 
end
else
begin
    DELETE 
	FROM  
		EST_PROD_CPL_MEDIAF 
    WHERE 
		CD_EMP = 1 
		AND CD_FILIAL = @CD_FILIAL_DEPO
end 

print 'Gravando registros na tabela final. ' + convert(varchar,getdate(),108)
INSERT INTO EST_PROD_CPL_MEDIAF (CD_EMP,CD_FILIAL,CD_PROD,CD_SIGLA,QTDE_MEDIAF,
									QTDE_FINANCIADO,QTDE_FACEAMENTO,QTDE_LIMITADOR,VALID_QTDE_FACEAMENTO,
									QT_VD_ATUAL,QT_VD_MES1,QT_VD_MES2,QT_VD_MES3,LIMITP,LIMITV,CD_USU,
									DT_CAD,DT_ULT_ALT,SAZ,DT_NASC,MEDIAF_TRANSF,STS_PROD)
SELECT     
	CD_EMP, 
	CD_FILIAL,
	CD_PROD, 
	CD_SIGLA,
	QTDE_MEDIAF,
	QTDE_FINANCIADO,
	QTDE_FACEAMENTO,
	QTDE_LIMITADOR,
	VALID_QTDE_FACEAMENTO,
	QT_VD_ATUAL,
	QT_VD_MES1,
	QT_VD_MES2,
	QT_VD_MES3,
	LIMITP,
	LIMITV,
	CD_USU,
	DT_CAD,
	DT_ULT_ALT,
	SAZ,
	DT_NASC,
	MEDIAF_TRANSF,
	STS_PROD
FROM         
	EST_PROD_SOMATORIA_MTZ

IF @CD_PROD_PARAM =0
BEGIN
PRINT 'Gravando registros que nao possuir mediaF mas tem na CPL ' + convert(varchar,getdate(),108)
    INSERT INTO EST_PROD_CPL_MEDIAF (CD_EMP,CD_FILIAL,CD_PROD,CD_SIGLA,QTDE_MEDIAF,QTDE_FINANCIADO,
									    QTDE_FACEAMENTO,QTDE_LIMITADOR,VALID_QTDE_FACEAMENTO,
									    QT_VD_ATUAL,QT_VD_MES1,QT_VD_MES2,QT_VD_MES3,LIMITP,LIMITV,CD_USU,
									    DT_CAD,DT_ULT_ALT,SAZ,DT_NASC,MEDIAF_TRANSF,STS_PROD) 	
	SELECT     
	    c.CD_EMP, 
	    @CD_FILIAL_DEPO AS CD_FILIAL,
	    c.CD_PROD, 
	    4 AS CD_CURVA,
	    0 AS QTDE_MEDIAF,
	    0 AS QTDE_FINANCIADO,
	    0 AS QTDE_FACEAMENTO,
	    0 AS QTDE_LIMITADOR,
	    NULL AS VALID_QTDE_FACEAMENTO,
	    0 AS QT_VD_ATUAL,
	    0 AS QT_VD_MES1,
	    0 AS QT_VD_MES2,
	    0 AS QT_VD_MES3,
	    0 AS LIMITP,
	    0 AS LIMITV,
	    1 AS CD_USU,
	    CONVERT(CHAR(10),GETDATE(),120) AS DT_CAD,
	    NULL AS DT_ULT_ALT,
	    0	 AS SAZ,
	    NULL AS DT_NASC,
	    0	MEDIAF_TRANSF,
	    0	STS_PROD  
    FROM
	    EST_PROD_CPL c 
	    left join EST_PROD_CPL_MEDIAF  m on 
	    c.cd_emp	= m.cd_emp and 
	    c.cd_prod   = m.cd_prod and 
	    c.cd_filial = m.cd_filial
    WHERE 
	    c.CD_EMP = 1 
	    AND c.CD_FILIAL = @CD_FILIAL_DEPO
	    and m.cd_emp is null;
END
