IF  EXISTS(
	SELECT 
		1 
	FROM 
		SYS.OBJECTS 
	WHERE 
		NAME LIKE 'UFD_EST_PROD_CPL_DEMANDA_PARAMETRO' AND TYPE = 'TF' )
BEGIN
	DROP FUNCTION UFD_EST_PROD_CPL_DEMANDA_PARAMETRO
END
GO
--SELECT * FROM UFD_EST_PROD_CPL_DEMANDA_PARAMETRO (1,2,7)
CREATE FUNCTION [dbo].[UFD_EST_PROD_CPL_DEMANDA_PARAMETRO]  (@CD_EMP   INT,    
          @CD_FILIAL  INT,    
          @CD_PROD  INT)
 RETURNS @RS_RET TABLE (CD_PROD INT,
					     CD_CTR_DEMANDA_PARAMETRO INT,
					     TP_NIVEL_PERC INT,
					     PERC_VARIACAO MONEY,
					     DS_PERC_VARIACAO VARCHAR(100))

AS              
BEGIN 

	 DECLARE @RS_TAB_COMIS TABLE (CD_PROD INT,
							CD_CTR_DEMANDA_PARAMETRO INT,
							TP_NIVEL_PERC INT,
							PERC_VARIACAO MONEY,
							DS_PERC_VARIACAO VARCHAR(50))
	
	DECLARE @TBL_NIVEL TABLE ( TP_NIVEL INT,
						 DS_NIVEL VARCHAR(20))
	INSERT INTO @TBL_NIVEL 
		SELECT 1, 'PRODUTO' UNION
		SELECT 2, 'FAMILIA' UNION
		SELECT 3, 'MARCA' UNION
		SELECT 4, 'FABRICANTE' UNION
		SELECT 5, 'CATEGORIA' UNION
		SELECT 6, 'LINHA'	

	INSERT INTO @RS_TAB_COMIS
		SELECT	A.CD_PROD,
				A.CD_CTR_DEMANDA_PARAMETRO,
				A.TP_NIVEL,
				A.PERC_VARIACAO,			
				'' AS DS_PERC_VARIACAO
		FROM UFD_EST_PROD_CPL_DEMANDA_PARAMETRO_VALORES(@CD_EMP,@CD_FILIAL, @CD_PROD) A


	INSERT INTO @RS_RET
	SELECT 
		MAX(CD_PROD) AS CD_PROD,
		MAX(CD_CTR_DEMANDA_PARAMETRO) AS CD_CTR_DEMANDA_PARAMETRO,
		MAX(TP_NIVEL_PERC) AS TP_NIVEL_PERC,
		MAX(PERC_VARIACAO)	 AS PERC_VARIACAO,		
			 (CASE WHEN MAX(ISNULL(PERC_VARIACAO,0)) = 0 AND MAX(CD_CTR_DEMANDA_PARAMETRO) > 0 THEN 
					'PARAMETRO DEMANA ANULADA(%) PELO NIVEL - TAB: ' + CAST(MAX(CD_CTR_DEMANDA_PARAMETRO) AS VARCHAR) + ' NIVEL: ' + (SELECT DS_NIVEL FROM @TBL_NIVEL N WHERE N.TP_NIVEL = MAX(T.TP_NIVEL_PERC))
			 WHEN MAX(ISNULL(PERC_VARIACAO,0)) = 0 AND MAX(CD_CTR_DEMANDA_PARAMETRO) = 0 THEN 
					'SEM COMISSAO(%)'
			 WHEN MAX(ISNULL(PERC_VARIACAO,0)) > 0 AND MAX(CD_CTR_DEMANDA_PARAMETRO) > 0 THEN 
					'PARAMETRO DEMANA(%) - TAB: ' + CAST(MAX(CD_CTR_DEMANDA_PARAMETRO) AS VARCHAR) + ' NIVEL: ' + (SELECT DS_NIVEL FROM @TBL_NIVEL N WHERE N.TP_NIVEL = MAX(T.TP_NIVEL_PERC))
			 ELSE
				'NAO DEFINIDO'
			END) AS DS_PERC_VARIACAO
	FROM @RS_TAB_COMIS T

	DELETE FROM @RS_RET WHERE CD_PROD IS NULL

RETURN
END 