IF  EXISTS(
	SELECT 
		1 
	FROM 
		SYS.OBJECTS 
	WHERE 
		NAME LIKE 'UFD_EST_PROD_COMIS_REGRESSAO' AND TYPE = 'TF' )
BEGIN
	DROP FUNCTION UFD_EST_PROD_COMIS_REGRESSAO
END
GO
--SELECT * FROM UFD_EST_PROD_COMIS_REGRESSAO (1,48,36846,0,20)
CREATE FUNCTION [dbo].[UFD_EST_PROD_COMIS_REGRESSAO]  (@CD_EMP   INT,    
          @CD_FILIAL  INT,    
          @CD_PROD  INT,    
          @CD_FUN  INT,
		  @PERC_DESC MONEY, 
		  @PERC_DESC_CAD MONEY)
 RETURNS @RS_RET TABLE (CD_PROD INT,
					     CD_TBL_COMIS_PERC INT,
					     TP_NIVEL_PERC INT,
					     PERC_COMIS MONEY,
					     DS_COMIS_PERC VARCHAR(100),
					     CD_TBL_COMIS_VLR INT,
					     TP_NIVEL_VLR INT,
					     VLR_COMIS MONEY,
					     DS_COMIS_VLR VARCHAR(100))

AS              
BEGIN 

	 DECLARE @RS_TAB_COMIS TABLE (CD_PROD INT,
							CD_TBL_COMIS_PERC INT,
							TP_NIVEL_PERC INT,
							PERC_COMIS MONEY,
							DS_COMIS_PERC VARCHAR(50),
							CD_TBL_COMIS_VLR INT,
							TP_NIVEL_VLR INT,
							VLR_COMIS MONEY,
							DS_COMIS_VLR 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' UNION
		SELECT 7, 'FILIAL'		

	INSERT INTO @RS_TAB_COMIS
		SELECT	A.CD_PROD,
				A.CD_TBL_COMIS_REGRESSAO,
				A.TP_NIVEL,
				A.PERC_COMIS,			
				'' AS DS_COMIS_PERC,
				0 AS CD_TBL_COMIS_VLR,
				0 AS TP_NIVEL_VLR,
				0 AS VLR_COMIS,
				'' AS DS_COMIS_VLR
		FROM UFD_EST_PROD_COMIS_REGRESSAO_VALORES(@CD_EMP,@CD_FILIAL, @CD_PROD, @CD_FUN,@PERC_DESC,@PERC_DESC_CAD) A

	
	INSERT INTO @RS_RET
	SELECT 
		MAX(CD_PROD) AS CD_PROD,
		MAX(CD_TBL_COMIS_PERC) AS CD_TBL_COMIS_PERC,
		MAX(TP_NIVEL_PERC) AS TP_NIVEL_PERC,
		MAX(PERC_COMIS)	 AS PERC_COMIS,		
			 (CASE WHEN MAX(ISNULL(PERC_COMIS,0)) = 0 AND MAX(CD_TBL_COMIS_PERC) > 0 THEN 
					'REGRESSAO DE COMISSAO ANULADA(%) PELO NIVEL - TAB: ' + CAST(MAX(CD_TBL_COMIS_PERC) AS VARCHAR) + ' NIVEL: ' + (SELECT DS_NIVEL FROM @TBL_NIVEL N WHERE N.TP_NIVEL = MAX(T.TP_NIVEL_PERC))
			 WHEN MAX(ISNULL(PERC_COMIS,0)) = 0 AND MAX(CD_TBL_COMIS_PERC) = 0 THEN 
					'SEM COMISSAO(%)'
			 WHEN MAX(ISNULL(PERC_COMIS,0)) > 0 AND MAX(CD_TBL_COMIS_PERC) > 0 THEN 
					'REGRESSAO DE COMISSAO(%) - TAB: ' + CAST(MAX(CD_TBL_COMIS_PERC) 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_COMIS_PERC,
		MAX(CD_TBL_COMIS_VLR)	AS CD_TBL_COMIS_VLR,
		MAX(TP_NIVEL_VLR)		AS TP_NIVEL_VLR,
		MAX(VLR_COMIS)			AS VLR_COMIS,
		(CASE WHEN MAX(ISNULL(VLR_COMIS,0)) = 0 AND MAX(CD_TBL_COMIS_VLR) > 0 THEN 
					'COMISSAO ANULADA(R$) PELO NÍVEL - TAB: ' + CAST(MAX(CD_TBL_COMIS_VLR) AS VARCHAR) + ' NIVEL: ' + (SELECT DS_NIVEL FROM @TBL_NIVEL N WHERE N.TP_NIVEL = MAX(T.TP_NIVEL_VLR))
			 WHEN MAX(ISNULL(VLR_COMIS,0)) = 0 AND MAX(CD_TBL_COMIS_VLR) = 0 THEN 
					'SEM COMISSAO(R$)'
			 WHEN MAX(ISNULL(VLR_COMIS,0)) > 0 AND MAX(CD_TBL_COMIS_VLR) > 0 THEN 
					'REGRESSAO DE COMISSAO(R$) - TAB: ' + CAST(MAX(CD_TBL_COMIS_VLR) AS VARCHAR) + ' NIVEL: ' + (SELECT DS_NIVEL FROM @TBL_NIVEL N WHERE N.TP_NIVEL = MAX(T.TP_NIVEL_VLR))
			 ELSE
				'NAO DEFINIDO'
			END)	AS DS_COMIS_VLR
	FROM @RS_TAB_COMIS T
	
	DELETE FROM @RS_RET WHERE CD_PROD IS NULL;

RETURN
END 