/****** Object:  StoredProcedure [dbo].[P_EST_PROD_REAVALIAR_CURVA_FILIAL]    Script Date: 10/07/2012 18:09:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_REAVALIAR_CURVA_FILIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_REAVALIAR_CURVA_FILIAL]
GO
GO
  
--EXECUTE P_EST_PROD_REAVALIAR_CURVA_FILIAL 1,90;
CREATE PROCEDURE [dbo].[P_EST_PROD_REAVALIAR_CURVA_FILIAL] (@CD_EMP INT = 1, @CD_FILIAL INT=0, @CD_PROD_PAR INT=0)

 WITH 
 ENCRYPTION 
AS
SET NOCOUNT ON  
SET CONTEXT_INFO 0x66666;


DECLARE @AGORA  DATETIME, @SQLComand varchar(max);
SELECT	@AGORA = CONVERT(CHAR(10),GETDATE(),120) 

	PRINT 'Inicio da rotina - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
	IF NOT EXISTS(SELECT TOP 1 1 FROM 
						INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='EST_PROD_REAVALIAR_CURVA_FILIAL_TMP')
	BEGIN
		CREATE TABLE EST_PROD_REAVALIAR_CURVA_FILIAL_TMP (CD_EMP		INT,
							CD_FILIAL	INT,
							CD_PROD		INT, 
							CD_SIGLA	INT,
							MEDIA		MONEY,
							CD_ARV_MERC_LINHA INT
							CONSTRAINT [PK_EST_PROD_REAVALIAR_CURVA_FILIAL_TMP] PRIMARY KEY CLUSTERED 
					 		(CD_PROD, CD_SIGLA,CD_FILIAL,CD_EMP)
							)
	END
	BEGIN
		TRUNCATE TABLE EST_PROD_REAVALIAR_CURVA_FILIAL_TMP
	END

	DECLARE @CD_CURVA_PD INT 
	SET @CD_CURVA_PD =(SELECT CD_CURVA_PD FROM PRC_EMP WHERE CD_EMP = @CD_EMP);

	INSERT INTO EST_PROD_REAVALIAR_CURVA_FILIAL_TMP 
	SELECT 
		F.CD_EMP,
		F.CD_FILIAL,
		F.CD_PROD ,		 
		0 CD_SIGLA,
		CASE WHEN F.QTDE_MEDIAF > 0 THEN  F.QTDE_MEDIAF ELSE P.QTDE_MEDIAP  END MEDIA,
		0 CD_ARV_MERC_LINHA
	FROM
		EST_PROD_CPL_MEDIAF F  
		LEFT JOIN EST_PROD_CPL_MEDIAP P ON
		F.CD_EMP	= P.CD_EMP AND
		F.CD_PROD	= P.CD_PROD AND
		F.CD_FILIAL = P.CD_FILIAL
	WHERE 
		F.CD_EMP=@CD_EMP 
		AND F.CD_FILIAL = @CD_FILIAL
		AND F.CD_PROD = CASE WHEN @CD_PROD_PAR=0 THEN F.CD_PROD ELSE @CD_PROD_PAR END  ;
		 
	
	PRINT 'Inseriu a tabela base para altercao - Hora : '+ CONVERT(VARCHAR, GETDATE(),108); 
 
		UPDATE T 
		SET CD_SIGLA = Cv.Curva
		FROM
			EST_PROD_REAVALIAR_CURVA_FILIAL_TMP T
			CROSS APPLY (SELECT ISNULL ((SELECT TOP (1) CD_SIGLA FROM         
							V_EST_ARV_MD_F_GRP_PARAM_CURVA_PRC_FILIAL a
							WHERE   
									a.CD_EMP 			= T.CD_EMP
								AND a.VLR_PARAMETRO 	<= MEDIA 
								AND a.CD_FILIAL 		= T.CD_FILIAL
								AND a.CD_PROD 		= T.CD_PROD 
								ORDER BY VLR_PARAMETRO DESC),@CD_CURVA_PD) Curva) Cv 
		WHERE
			T.CD_EMP=@CD_EMP 
			AND T.CD_FILIAL =@CD_FILIAL;
 
	PRINT 'Atualizou as curvas a tabela base para altercao - Hora : '+ CONVERT(VARCHAR, GETDATE(),108); 
	
	-- Aplica as alteracoes na tabela de mediaf.. 

	SET @SQLComand = 'DELETE FROM T
	FROM 
		EST_PROD_CPL_MEDIAF F 
		INNER JOIN EST_PROD_REAVALIAR_CURVA_FILIAL_TMP T ON 
		F.CD_EMP	=T.CD_EMP AND
		F.CD_PROD	=T.CD_PROD AND
		F.CD_FILIAL =T.CD_FILIAL
	WHERE F.CD_SIGLA = T.CD_SIGLA 
		and f.CD_EMP=' + convert(varchar, @CD_EMP ) + ' AND f.CD_FILIAL = ' + convert(varchar, @CD_FILIAL)

	exec (@SQLComand);


	
	PRINT 'Exclui as curvas que nao sofreram altereção - Hora : '+ CONVERT(VARCHAR, GETDATE(),108);
	
	
	SET @SQLComand = 'UPDATE F SET 
		F.CD_SIGLA =T.CD_SIGLA
	FROM
		EST_PROD_CPL_MEDIAF F 
		INNER JOIN EST_PROD_REAVALIAR_CURVA_FILIAL_TMP T ON 
		F.CD_EMP	=T.CD_EMP AND
		F.CD_PROD	=T.CD_PROD AND
		F.CD_FILIAL =T.CD_FILIAL  
	WHERE 
		f.CD_EMP=' + convert(varchar, @CD_EMP ) + ' AND f.CD_FILIAL = ' + convert(varchar, @CD_FILIAL)

	exec (@SQLComand); 
	PRINT 'Alterou a tabela  EST_PROD_CPL_MEDIAF - Hora : '+ CONVERT(VARCHAR, GETDATE(),108); 
	
	TRUNCATE TABLE EST_PROD_REAVALIAR_CURVA_FILIAL_TMP;
	
	PRINT 'Fim da rotina - Hora : '+ CONVERT(VARCHAR, GETDATE(),108); 


GO


