/****** Object:  UserDefinedFunction [dbo].[UFD_EST_PROD_GRP_PRECO]    Script Date: 10/09/2012 14:18:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_GRP_PRECO]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PROD_GRP_PRECO]
GO  
CREATE FUNCTION [dbo].[UFD_EST_PROD_GRP_PRECO](@CD_EMP INT, @CD_GRUPO INT, @CD_PROD VARCHAR(MAX), @CD_FILIAL INT)
RETURNS @RS_RET TABLE(  CD_EMP INT, 
						CD_PROD INT, 
						CD_FILIAL INT,
						DS_PROD VARCHAR(100), 
						QTDE_MEDIAF MONEY, 
						VLR_CUSTO MONEY, 
						VLR_VENDA MONEY, 
						VLR_TABELA MONEY,
						CTR_PMC		INT)
AS
BEGIN 	
	
	DECLARE @PRC_FILIAL_GRP TABLE ( CD_GRUPO	INT,
								    CD_EMP		INT,
									CD_FILIAL	INT)
	
	DECLARE @EST_PROD TABLE (CD_EMP					INT,
							CD_PROD					INT,
							DS_PROD					VARCHAR(100),
							CD_ARV_MERC_FAMILIA		INT,
							CD_PROD_FAMILIA			INT,
							CTR_PMC					INT)
	
	
	DECLARE @RS_PRC_CUSTO TABLE (CD_EMP					INT,
								 CD_PROD				INT,
								 VLR_PRC_CUSTO			MONEY,
								 ID						INT)
	

	DECLARE @RS_DESCONTOS_PROD TABLE 	(CD_EMP			INT,        
									     CD_PROD		INT,        
									     CD_FILIAL		INT,            
									     VLR_TABELA		MONEY,          
									     VLR_OFERTA		MONEY,            
									     PERC_DESC_FIX	MONEY,        
									     PERC_DESC		MONEY) 
		
	
	INSERT INTO @PRC_FILIAL_GRP
		SELECT G.CD_GRUPO, G.CD_EMP, G.CD_FILIAL	
		FROM EST_PRECIFICACAO_GRP_PRECO_FILIAL G 
			INNER JOIN PRC_FILIAL F ON 
				G.CD_EMP = F.CD_EMP
				AND G.CD_FILIAL = F.CD_FILIAL
		WHERE 
			F.ESPECIE_FILIAL =1
			AND G.CD_GRUPO = (CASE WHEN @CD_GRUPO = 0 THEN G.CD_GRUPO ELSE @CD_GRUPO END)
	

	INSERT INTO @EST_PROD
		SELECT P.CD_EMP, P.CD_PROD, P.DS_PROD, ARV.CD_ARV_MERC_FAMILIA, P.CD_PROD, P.CTR_PMC	
		FROM EST_PROD P
			INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON 			
				P.CD_EMP = ARV.CD_EMP	
				AND P.CD_PROD = ARV.CD_PROD
		WHERE P.CD_PROD IN (SELECT ID FROM DBO.UFD_PRM_IN_STRING(@CD_PROD))
		
	INSERT INTO @EST_PROD
		SELECT P.CD_EMP, P.CD_PROD, P.DS_PROD, ARV.CD_ARV_MERC_FAMILIA, ARV.CD_PROD, P.CTR_PMC 	
		FROM @EST_PROD P
			INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON 			
				P.CD_EMP = ARV.CD_EMP	
				AND P.CD_ARV_MERC_FAMILIA = ARV.CD_ARV_MERC_FAMILIA
		WHERE 
			ARV.CD_ARV_MERC_FAMILIA > 0
			AND ARV.CD_PROD <> P.CD_PROD
	
	INSERT INTO @RS_RET
		SELECT  P.CD_EMP, 
				P.CD_PROD, 
				@CD_FILIAL AS CD_FILIAL, 
				P.DS_PROD, 
				SUM(CPL.MEDIAF) AS QTDE_MEDIAF, 
				ISNULL(MAX(CASE WHEN CPL.CD_FILIAL = @CD_FILIAL THEN CPL.VLR_CUSTO ELSE 0 END),0) AS VLR_CUSTO, 
				0 AS VLR_VENDA, 
				0 AS VLR_TABELA,
				P.CTR_PMC 
		FROM 
			@EST_PROD P
			INNER JOIN @PRC_FILIAL_GRP G ON 
				P.CD_EMP = G.CD_EMP		
			INNER JOIN EST_PROD_CPL CPL ON
				G.CD_EMP = CPL.CD_EMP
				AND G.CD_FILIAL = CPL.CD_FILIAL
				AND P.CD_PROD_FAMILIA = CPL.CD_PROD
		GROUP BY 
			P.CD_EMP, P.CD_PROD, P.DS_PROD, P.CTR_PMC



	INSERT INTO @RS_PRC_CUSTO
		SELECT  P.CD_EMP,
				P.CD_PROD,
				IT.VLR_PRC_CUSTO,
				ROW_NUMBER() OVER (PARTITION BY P.CD_PROD ORDER BY P.CD_PROD, IT.VLR_PRC_CUSTO DESC) AS ID
		FROM 
			@EST_PROD P 			
			INNER JOIN EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT ENT ON 
				ENT.CD_EMP			= P.CD_EMP
				AND ENT.CD_GRUPO	= (CASE WHEN @CD_GRUPO = 0 THEN ENT.CD_GRUPO ELSE @CD_GRUPO END)
				AND ENT.CD_PROD		= P.CD_PROD_FAMILIA
			INNER JOIN EST_NF_ENT AS NF ON					
				NF.CD_EMP			= ENT.CD_EMP    
				AND    NF.CD_FILIAL = ENT.CD_FILIAL    
				AND    NF.CD_NF     = ENT.CD_NF    			
			INNER JOIN EST_NF_ENT_IT AS IT ON					
				NF.CD_EMP			= IT.CD_EMP    
				AND    NF.CD_FILIAL = IT.CD_FILIAL    
				AND    NF.CD_NF     = IT.CD_NF    
				AND    NF.DT_ENT    = IT.DT_ENT
				AND    ENT.CD_PROD  = IT.CD_PROD		
			 
	DELETE FROM @RS_PRC_CUSTO
	WHERE ID > 1

	UPDATE R SET 
		VLR_CUSTO = CUSTO.VLR_PRC_CUSTO
	FROM @RS_RET R
		INNER JOIN @RS_PRC_CUSTO CUSTO ON 
			R.CD_EMP = CUSTO.CD_EMP
			AND R.CD_PROD = CUSTO.CD_PROD

	INSERT INTO @RS_DESCONTOS_PROD
		SELECT         
		   PROD.CD_EMP,        
		   PROD.CD_PROD,        
		   PRECO.CD_FILIAL,            
		   PRECO.VLR_TABELA,          
		   PRECO.VLR_OFERTA,            
		   PROD.PERC_DESC_FIX,        
		   PROD.PERC_DESC  		
		FROM   
		   @EST_PROD P	
		   INNER JOIN 
		       EST_PROD AS PROD 
			   ON P.CD_EMP = PROD.CD_EMP AND 
			   P.CD_PROD = PROD.CD_PROD
		   INNER JOIN         
			   EST_PROD_PRECO AS PRECO        
			   ON PROD.CD_EMP = PRECO.CD_EMP AND         
			   PROD.CD_PROD = PRECO.CD_PROD AND  
			   @CD_FILIAL = PRECO.CD_FILIAL
				   
		   INNER JOIN EST_PROD_CPL        
			   ON PRECO.CD_EMP = EST_PROD_CPL.CD_EMP AND        
			   PRECO.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND        
			   PRECO.CD_PROD = EST_PROD_CPL.CD_PROD        
       

	UPDATE  D    
		SET      
			PERC_DESC_FIX= EST_PROD_PRECO_DESC_PRG.PERC_DESC_FIX,      
			PERC_DESC= (CASE WHEN ISNULL(EST_PROD_PRECO_DESC_PRG.FLAG_PERC_DESC_LIMITE,0) = 0 THEN      
							EST_PROD_PRECO_DESC_PRG_CPL.PERC_DESC       
						ELSE      
							0      
						END)
	FROM               
		EST_PROD_PRECO_DESC_PRG 
		INNER JOIN EST_PROD_PRECO_DESC_PRG_CPL ON       
			EST_PROD_PRECO_DESC_PRG.CD_PRECO_PRG = EST_PROD_PRECO_DESC_PRG_CPL.CD_PRECO_PRG     
			AND EST_PROD_PRECO_DESC_PRG_CPL.CD_EMP = @CD_EMP 
		INNER JOIN EST_PROD_PRECO_DESC_PRG_PRC_FILIAL ON       
			EST_PROD_PRECO_DESC_PRG.CD_PRECO_PRG = EST_PROD_PRECO_DESC_PRG_PRC_FILIAL.CD_PRECO_PRG  
			AND EST_PROD_PRECO_DESC_PRG_PRC_FILIAL.CD_EMP = @CD_EMP  
			AND EST_PROD_PRECO_DESC_PRG_PRC_FILIAL.CD_FILIAL = @CD_FILIAL
		INNER JOIN @RS_DESCONTOS_PROD d ON       
			EST_PROD_PRECO_DESC_PRG_CPL.CD_PROD = d.CD_PROD        			
			AND EST_PROD_PRECO_DESC_PRG_CPL.CD_EMP = d.CD_EMP
	WHERE           
		EST_PROD_PRECO_DESC_PRG.DT_INI <= CONVERT(CHAR(10),GETDATE(),112)       
		AND EST_PROD_PRECO_DESC_PRG.DT_VENCTO >= CONVERT(CHAR(10),GETDATE(),112)      
		AND EST_PROD_PRECO_DESC_PRG.TP_CLIENTE IN (0)   

	UPDATE D SET 
		PERC_DESC = ISNULL(UFD.PERC_DESC,0)
	FROM @RS_DESCONTOS_PROD D
		CROSS APPLY dbo.UFD_BUSCA_EST_PROD_TBL_DESC (D.CD_EMP, D.CD_FILIAL, D.CD_PROD ) UFD
    WHERE 
		ISNULL(UFD.PERC_DESC,0) > 0
			
	UPDATE R SET 
		VLR_TABELA = (CASE WHEN R.CTR_PMC = 1 THEN D.VLR_TABELA ELSE 0 END),
		VLR_VENDA = (CASE WHEN D.VLR_OFERTA > 0 THEN 
						D.VLR_OFERTA 
					 ELSE
						ROUND(D.VLR_TABELA - ((D.PERC_DESC * D.VLR_TABELA) / 100),2)
					 END)
	FROM 
		@RS_RET R
		INNER JOIN 
			@RS_DESCONTOS_PROD D
			ON  R.CD_EMP = D.CD_EMP AND
				R.CD_PROD = D.CD_PROD  
		
	RETURN 

END




	 
GO


