/****** Object:  UserDefinedFunction [dbo].[UFD_BUSCA_EST_PROD_TBL_DESC]    Script Date: 20/08/2014 16:28:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BUSCA_EST_PROD_TBL_DESC]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_BUSCA_EST_PROD_TBL_DESC]
GO 

--SELECT * FROM DBO.UFD_BUSCA_EST_PROD_TBL_DESC (1,2,81254)
CREATE  FUNCTION [dbo].[UFD_BUSCA_EST_PROD_TBL_DESC](@CD_EMP INT, @CD_FILIAL		INT,
											@CD_PROD		INT)
												 
RETURNS @RS_RETORNO TABLE (NIVEL INT,
						   PERC_DESC	MONEY,
						   PERC_DESC_MAX MONEY,
						   PERC_DESC_LIMITE MONEY,
						   CD_TBL INT,
						   DS_TBL_DESC VARCHAR(200))
							AS BEGIN
DECLARE @TBL_DESC TABLE (NIVEL			INT,
							CD_PROD		INT,
							CD_FILIAL	INT,
							CD_TBL		INT,
							PERC_DESC	MONEY,
							PERC_DESC_MAX MONEY,
							PERC_DESC_LIMITE MONEY,
							DS_TBL_DESC VARCHAR(200))

declare @Linha int,
	   @Categoria int,
	   @Fabricante int,
	   @Marca	 int,
	   @Familia int,
	   @Hoje date;

	   select
		  @Linha	    =A.CD_ARV_MERC_LINHA,
		  @Categoria  =A.CD_ARV_MERC_CATEG ,
		  @Fabricante =P.CD_FABRIC ,
		  @Marca	    =P.CD_MC,
		  @Familia    =A.CD_ARV_MERC_FAMILIA,
		  @Hoje=	getdatE()
	   FROM
		  EST_PROD P
		  JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON 
		  P.CD_EMP    = A.CD_EMP AND
		  P.CD_PROD   = A.CD_PROD
	   WHERE
		  P.CD_EMP=@CD_EMP
		  AND P.CD_PROD=@CD_PROD;

INSERT INTO @TBL_DESC
SELECT
	99 NIVEL,
	@CD_PROD,
	@CD_FILIAL,
	F.CD_TBL_DESC,
	F.PERC_DESC,
	F.PERC_DESC_MAX,
	F.PERC_DESC_LIMITE,
	D.DS_TBL_DESC
FROM 
	EST_PROD_TBL_DESC D 
	INNER JOIN EST_PROD_TBL_DESC_EST_PROD PD ON
	PD.CD_EMP = D.CD_EMP AND
	PD.CD_TBL_DESC = D.CD_TBL_DESC

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL F ON 
	D.CD_EMP		= F.CD_EMP AND
	D.CD_TBL_DESC	= F.CD_TBL_DESC 
 
WHERE 
	Pd.CD_PROD =@CD_PROD
	AND f.CD_FILIAL = @CD_FILIAL
	AND Pd.CD_EMP =@CD_EMP 
	AND D.CD_TP_DESCONTO in(1,18,23)
	AND @Hoje between D.DT_INI  AND D.DT_FIM ;


INSERT INTO @TBL_DESC
SELECT 
	6 NIVEL,
	@CD_PROD CD_PROD,
	CD_FILIAL,
	D.CD_TBL_DESC,
	D.PERC_DESC,
	D.PERC_DESC_MAX,
	D.PERC_DESC_LIMITE,
	Tb.DS_TBL_DESC
FROM  
	EST_PROD_TBL_DESC_EST_ARV_MERC_LINHA  D
	
	INNER JOIN  EST_PROD_TBL_DESC Tb ON 
	D.CD_EMP		= Tb.CD_EMP AND
	D.CD_TBL_DESC	= Tb.CD_TBL_DESC 

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL F ON 
	D.CD_EMP		= F.CD_EMP AND
	D.CD_TBL_DESC	= F.CD_TBL_DESC 
where
    d.cd_arv_merc_linha=@Linha

    AND f.CD_FILIAL = @CD_FILIAL
    AND tb.CD_EMP =@CD_EMP 
    AND tb.CD_TP_DESCONTO in(1,18,23)
    AND @Hoje between tb.DT_INI  AND tb.DT_FIM ;


INSERT INTO @TBL_DESC
SELECT
	5 NIVEL,
	@CD_PROD,
	@CD_FILIAL,
	D.CD_TBL_DESC,
	D.PERC_DESC,
	D.PERC_DESC_MAX,
	D.PERC_DESC_LIMITE,
	Tb.DS_TBL_DESC
FROM  
	EST_PROD_TBL_DESC_EST_ARV_MERC_CATEGORIA  D
	   
	INNER JOIN  EST_PROD_TBL_DESC Tb ON 
	D.CD_EMP		= Tb.CD_EMP AND
	D.CD_TBL_DESC	= Tb.CD_TBL_DESC 

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL F ON 
	D.CD_EMP		= F.CD_EMP AND
	D.CD_TBL_DESC	= F.CD_TBL_DESC 
where
    d.cd_arv_merc_categ =@Categoria
    AND f.CD_FILIAL = @CD_FILIAL
    AND tb.CD_EMP =@CD_EMP 
    AND tb.CD_TP_DESCONTO in(1,18,23)
    AND @Hoje between tb.DT_INI  AND tb.DT_FIM ;

INSERT INTO @TBL_DESC
SELECT 
	4 NIVEL,
	@CD_PROD,
	@CD_FILIAL,
	D.CD_TBL_DESC,
	D.PERC_DESC,
	D.PERC_DESC_MAX,
	D.PERC_DESC_LIMITE,
	Tb.DS_TBL_DESC
FROM  
	EST_PROD_TBL_DESC_EST_PROD_FABRIC  D
	
     INNER JOIN  EST_PROD_TBL_DESC Tb ON 
	D.CD_EMP		= Tb.CD_EMP AND
	D.CD_TBL_DESC	= Tb.CD_TBL_DESC 

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL F ON 
	D.CD_EMP		= F.CD_EMP AND
	D.CD_TBL_DESC	= F.CD_TBL_DESC 
where
    d.cd_fabric=@Fabricante
    AND f.CD_FILIAL = @CD_FILIAL
    AND tb.CD_EMP =@CD_EMP 
    AND tb.CD_TP_DESCONTO in(1,18,23)
    AND @Hoje between tb.DT_INI  AND tb.DT_FIM ;
	
INSERT INTO @TBL_DESC
SELECT 
	3 NIVEL,
	@CD_PROD,
	@CD_FILIAL,
	D.CD_TBL_DESC,
	D.PERC_DESC,
	D.PERC_DESC_MAX,
	D.PERC_DESC_LIMITE,
	Tb.DS_TBL_DESC
FROM  
	EST_PROD_TBL_DESC_EST_MC  D
	INNER JOIN  EST_PROD_TBL_DESC Tb ON 
	D.CD_EMP		= Tb.CD_EMP AND
	D.CD_TBL_DESC	= Tb.CD_TBL_DESC 

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL F ON 
	D.CD_EMP		= F.CD_EMP AND
	D.CD_TBL_DESC	= F.CD_TBL_DESC 
where
    d.cd_MC =@Marca
    AND f.CD_FILIAL = @CD_FILIAL
    AND tb.CD_EMP =@CD_EMP 
    AND tb.CD_TP_DESCONTO in(1,18,23)
    AND @Hoje between tb.DT_INI  AND tb.DT_FIM ;
	
INSERT INTO @TBL_DESC
SELECT 
    2 NIVEL,
    @CD_PROD,
    @CD_FILIAL,
    D.CD_TBL_DESC,
    D.PERC_DESC,
    D.PERC_DESC_MAX,
    D.PERC_DESC_LIMITE,
	Tb.DS_TBL_DESC
FROM  
    EST_PROD_TBL_DESC_EST_ARV_MERC_FAMILIA  D
    INNER JOIN  EST_PROD_TBL_DESC Tb ON 
    D.CD_EMP		= Tb.CD_EMP AND
    D.CD_TBL_DESC	= Tb.CD_TBL_DESC 

    INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL F ON 
    D.CD_EMP		= F.CD_EMP AND
    D.CD_TBL_DESC	= F.CD_TBL_DESC 
where
    d.CD_ARV_MERC_FAMILIA =@Familia
    AND f.CD_FILIAL = @CD_FILIAL
    AND tb.CD_EMP = @CD_EMP 
    AND tb.CD_TP_DESCONTO in(1,18,23)
    AND @Hoje between tb.DT_INI  AND tb.DT_FIM ;


INSERT INTO @TBL_DESC
SELECT 
	1 NIVEL,
	T.CD_PROD,
	T.CD_FILIAL,
	D.CD_TBL_DESC,
	D.PERC_DESC,
	D.PERC_DESC_MAX,
	D.PERC_DESC_LIMITE,
	T.DS_TBL_DESC 
FROM  
	EST_PROD_TBL_DESC_EST_PROD  D
	INNER JOIN  EST_PROD   P ON 
	D.CD_EMP		= P.CD_EMP AND
	D.CD_PROD = P.CD_PROD
	INNER JOIN @TBL_DESC T ON  
	P.CD_PROD		= T.CD_PROD AND
	D.CD_TBL_DESC	= T.CD_TBL
	AND T.NIVEL=99;
	
	INSERT INTO	@TBL_DESC
	SELECT
		7 AS NIVEL,
		TP.CD_PROD,
		TF.CD_FILIAL,
		T.CD_PRECO_PRG,
		TP.PERC_DESC,
		TP.PERC_DESC AS PERC_DESC_MAX,
		TP.PERC_DESC AS PERC_DESC_LIMITE,
		'DESCONTO PROGRAMADO'
	FROM
		EST_PROD_PRECO_DESC_PRG T
		INNER JOIN EST_PROD_PRECO_DESC_PRG_CPL TP ON
			T.CD_PRECO_PRG = TP.CD_PRECO_PRG
		INNER JOIN EST_PROD_PRECO_DESC_PRG_PRC_FILIAL TF ON
			T.CD_PRECO_PRG = TF.CD_PRECO_PRG

	WHERE
		@Hoje between T.DT_INI AND T.DT_VENCTO
		 AND TF.CD_FILIAL = @CD_FILIAL
		 AND TP.CD_PROD = @CD_PROD;
    /******************************************************************/
    --Validacao forma de pagamento a vista  
    /******************************************************************/
	   --Deleta formas de pgto a prazo
	   DELETE FROM T
	   FROM
		  @TBL_DESC T
		  JOIN EST_PROD_TBL_DESC_FORMA_PGTO P ON 
		  T.CD_TBL=P.CD_TBL_DESC
		  JOIN EST_FORMA_PGTO V ON
		  P.CD_EMP=V.CD_EMP AND
		  P.CD_FORMA_PGTO=V.CD_FRM_PGTO
	   where
		  v.TP_COND=1 --Prazo
		  AND P.CD_EMP=@CD_EMP
		  and not exists(select
						  cd_tbl
						  from 
							 @TBL_DESC
							 JOIN EST_PROD_TBL_DESC_FORMA_PGTO P ON 
							 T.CD_TBL=P.CD_TBL_DESC

							 JOIN EST_FORMA_PGTO V ON
							 P.CD_EMP=V.CD_EMP AND
							 P.CD_FORMA_PGTO=V.CD_FRM_PGTO
							 where v.TP_COND=0 --a vista
							 )
		  AND V.REGRA_DESCONTO <>3 --que nao seja melhor desconto;

    /******************************************************************/
	DECLARE @NIVEL INT
	DECLARE @CD_TBL INT
	SET @NIVEL =(SELECT MIN(NIVEL) FROM @TBL_DESC)
	
	SET @CD_TBL=(SELECT MAX(CD_TBL) CD_TBL
			FROM  @TBL_DESC WHERE NIVEL=@NIVEL)
	
	INSERT INTO @RS_RETORNO
	SELECT	 
		@NIVEL NIVEL,
		(SELECT MAX(PERC_DESC) PERC_DESC
			FROM @TBL_DESC WHERE NIVEL=@NIVEL) PERC_DESC, 
		(SELECT MAX(PERC_DESC_MAX) PERC_DESC_MAX
			FROM  @TBL_DESC WHERE NIVEL=@NIVEL) PERC_DESC_MAX,
		(SELECT MAX(PERC_DESC_LIMITE) PERC_DESC_LIMITE
			FROM  @TBL_DESC WHERE NIVEL=@NIVEL) PERC_DESC_LIMITE,
		(SELECT MAX(CD_TBL) CD_TBL
			FROM  @TBL_DESC WHERE NIVEL=@NIVEL) CD_TBL,
		(SELECT TOP 1 DS_TBL_DESC DS_TBL_DESC
			FROM  @TBL_DESC WHERE NIVEL=@NIVEL AND CD_TBL=@CD_TBL) DS_TBL_DESC
	RETURN;	 
END
