IF  EXISTS (SELECT * FROM sys.objects 
			 WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BUSCA_EST_PROD_TBL_DESC_PADRAO]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_BUSCA_EST_PROD_TBL_DESC_PADRAO]
GO 


/*

=======================================================================================
'Job.................:  
'Função..............: 
'Objetivo............: Retornar percentual de desconto 
'Tabelas.............: 
'Dt Modificação......: 21/10/2020
'Modificação.........: Tag #A001 - Adicionado o 6º Parametro na chamada da função UFD_EST_PROD_TBL_DESC_CLASSIFICACAO
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 

*/
--SELECT * FROM DBO.UFD_BUSCA_EST_PROD_TBL_DESC_PADRAO (1,99,2030,0,1)
CREATE  FUNCTION [dbo].UFD_BUSCA_EST_PROD_TBL_DESC_PADRAO(@CD_EMP INT, @CD_FILIAL		INT,
														  @CD_PROD		INT,
														  @QT_PROD		MONEY,
														  @CD_CLI INT ) 
RETURNS @RS_RETORNO TABLE (TIPO INT,
						   PERC_DESC	MONEY,
						   PERC_DESC_MAX MONEY,
						   PERC_DESC_LIMITE MONEY )

AS BEGIN

DECLARE @TBL_DESC TABLE (TIPO			INT, 
							PERC_DESC	MONEY,
							PERC_DESC_MAX MONEY,
							PERC_DESC_LIMITE MONEY )
    
    INSERT INTO @TBL_DESC (TIPO,PERC_DESC,PERC_DESC_MAX,PERC_DESC_LIMITE)
    SELECT 
	    NIVEL	
	   ,PERC_DESC	
	   ,PERC_DESC_MAX	
	   ,PERC_DESC_LIMITE
    FROM 
	   dbo.UFD_BUSCA_EST_PROD_TBL_DESC (@CD_EMP,@CD_FILIAL,@CD_PROD)	;
    
    --Busca desconto por classificacao de cliente.
    IF @CD_CLI > 0 
    BEGIN
-- Tag #A001	
	   INSERT INTO @TBL_DESC (TIPO,PERC_DESC,PERC_DESC_MAX,PERC_DESC_LIMITE)
	   SELECT  
		  TP_DESC TIPO	
		  ,PERC_DESC	
		  ,PERC_DESC_MAX	
		  ,PERC_DESC_MAX	PERC_DESC_LIMITE
	   FROM UFD_EST_PROD_TBL_DESC_CLASSIFICACAO(@CD_EMP,@CD_FILIAL,@CD_PROD,@CD_CLI,0,0,0);
    END

    INSERT INTO @RS_RETORNO
    SELECT TOP 1
	    TIPO	
	   ,PERC_DESC	
	   ,PERC_DESC_MAX	
	   ,PERC_DESC_LIMITE
    FROM 
	   @TBL_DESC
    ORDER BY PERC_DESC DESC;

    RETURN
END			 