IF  EXISTS(
	SELECT 
		1 
	FROM 
		SYS.OBJECTS 
	WHERE 
		NAME LIKE 'UFD_EST_PROD_COMIS_QTDE' AND TYPE = 'TF' )
BEGIN
	DROP FUNCTION UFD_EST_PROD_COMIS_QTDE
END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--SELECT * FROM UFD_EST_PROD_COMIS_QTDE (1,2,23014,1,3)
CREATE FUNCTION [dbo].[UFD_EST_PROD_COMIS_QTDE]  (@CD_EMP   INT,    
          @CD_FILIAL  INT,    
          @CD_PROD  INT,    
          @CD_FUN  INT,
		  @QTDE_PROD MONEY)
 RETURNS @RS_RET TABLE (CD_PROD INT,
					     CD_TBL_COMIS_PERC INT,
					     TP_NIVEL_PERC INT,
					     PERC_COMIS MONEY,
					     DS_COMIS_PERC NVARCHAR(100),
					     FLAG_DESC_OUTR_TBL INT,
					     CD_TBL_COMIS_VLR INT,
					     TP_NIVEL_VLR INT,
					     VLR_COMIS MONEY,
					     DS_COMIS_VLR NVARCHAR(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 NVARCHAR(50),
							FLAG_DESC_OUTR_TBL INT,
							CD_TBL_COMIS_VLR INT,
							TP_NIVEL_VLR INT,
							VLR_COMIS MONEY,
							DS_COMIS_VLR NVARCHAR(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	B.CD_PROD,
				0 AS CD_TBL_COMIS_PERC,
				0 AS TP_NIVEL_PERC,
				0 AS PERC_COMIS,			
				'' AS DS_COMIS_PERC,
				B.FLAG_DESC_OUTR_TBL,
				B.CD_TBL_COMIS,
				B.TP_NIVEL,
				B.PERC_COMIS,
				'' AS DS_COMIS_VLR
		FROM UFD_EST_PROD_COMIS_QTDE_VALORES(@CD_EMP,@CD_FILIAL, @CD_PROD, @CD_FUN, 1,@QTDE_PROD) B


	INSERT INTO @RS_TAB_COMIS
		SELECT	A.CD_PROD,
				A.CD_TBL_COMIS,
				A.TP_NIVEL,
				A.PERC_COMIS,			
				'' AS DS_COMIS_PERC,
				0 AS FLAG_DESC_OUTR_TBL,
				0 AS CD_TBL_COMIS_VLR,
				0 AS TP_NIVEL_VLR,
				0 AS VLR_COMIS,
				'' AS DS_COMIS_VLR
		FROM UFD_EST_PROD_COMIS_QTDE_VALORES(@CD_EMP,@CD_FILIAL, @CD_PROD, @CD_FUN, 0,@QTDE_PROD) A

	
	INSERT INTO @RS_RET
	SELECT 
		MAX(CD_PROD)			AS CD_PROD,
		(CASE WHEN MAX(ISNULL(FLAG_DESC_OUTR_TBL,0)) = 0 THEN MAX(CD_TBL_COMIS_PERC)	ELSE 0 END) AS CD_TBL_COMIS_PERC,
		(CASE WHEN MAX(ISNULL(FLAG_DESC_OUTR_TBL,0)) = 0 THEN MAX(TP_NIVEL_PERC)		ELSE 0 END) AS TP_NIVEL_PERC,
		(CASE WHEN MAX(ISNULL(FLAG_DESC_OUTR_TBL,0)) = 0 THEN MAX(PERC_COMIS)			ELSE 0 END) AS PERC_COMIS,		
		(CASE WHEN MAX(ISNULL(FLAG_DESC_OUTR_TBL,0)) = 1 THEN 
					'COMISSAO QTDE ANULADA(%) PELO DESC. VALOR - 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(PERC_COMIS,0)) = 0 AND MAX(CD_TBL_COMIS_PERC) > 0 THEN 
					'COMISSAO QTDE 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 
					'MELHOR QTDE 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(FLAG_DESC_OUTR_TBL)	AS FLAG_DESC_OUTR_TBL,
		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 QTDE 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 
					'MELHOR QTDE 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 