
IF EXISTS (SELECT * 
             FROM SYS.objects 
			WHERE    object_id = OBJECT_ID(N'[dbo].UFD_EST_PROD_TBL_DESC_ESPECIAL_CLIENTE') )
BEGIN
     DROP FUNCTION UFD_EST_PROD_TBL_DESC_ESPECIAL_CLIENTE;
END 
GO 
-- SELECT * FROM UFD_EST_PROD_TBL_DESC_ESPECIAL_CLIENTE(1, 42, 15904,1, 0)

CREATE FUNCTION UFD_EST_PROD_TBL_DESC_ESPECIAL_CLIENTE (@CD_EMP  INT, @CD_FILIAL  INT, @CD_PROD  INT, @CD_CLI  INT , @TP_ORIG_VENDA INT , @USA_DESCONTO_PROPZ_IDENT INT = 0)


-------------------------------------------------------------------------------------------------------------------------------
--DECLARANDO TABELA TEMPORARIA DE RETORNO
-------------------------------------------------------------------------------------------------------------------------------

RETURNS @RETURN_REC TABLE( CD_TBL_DESC   INT, 
						   TP_DESC       INT, 
						   FLAG_DESC_FIX INT, 
						   PERC_DESC     MONEY, 
						   PERC_DESC_MAX MONEY, 
						   TP_NIVEL      INT 
						  )
AS


	BEGIN
	
DECLARE @TIPODESCONTOCLIENTE INT; 
			DECLARE @TEMP_RS_TAB_DESC_PROD TABLE
				(
					 CD_TBL_DESC   INT,
					 TP_DESC       INT,
					 FLAG_DESC_FIX INT,
					 PERC_DESC     MONEY,
					 PERC_DESC_MAX MONEY,
					 TP_NIVEL      INT,
					 DT_INI        DATETIME
				)
-------------------------------------------------------------------------------------------------------------------------------
--DECLARANDO TABELA QUE RECEBERA AS TBLS VIGENTES
-------------------------------------------------------------------------------------------------------------------------------
	
			DECLARE @RS_TAB_ATIVA_PROD TABLE
				(
					 CD_EMP      INT,
					 CD_TBL_DESC INT,
					 TP_DESC     INT
				)
-------------------------------------------------------------------------------------------------------------------------------    
--DECLARANDO TABELA DE ARVORE MERCADOLOGICA DO PRODUTO    
-------------------------------------------------------------------------------------------------------------------------------    
			DECLARE @RS_EST_PROD_ARV_MERC TABLE
				(
					 CD_EMP              INT,
					 CD_PROD             INT,
					 CD_ARV_MERC_CATEG   INT,
					 CD_ARV_MERC_LINHA   INT,
					 CD_MC               INT,
					 CD_ARV_MERC_FAMILIA INT,
					 CD_FABRIC           INT
				)


	SET @TIPODESCONTOCLIENTE = ISNULL((SELECT VALOR FROM  PRC_EMP_CONFIG WHERE CD_CHAVE = 'TIPO_DESCONTO_LISTA_DE_PRODUTOS_CLIENTE'), 0) 				
	

    -- lista especifica 
 	if (@TIPODESCONTOCLIENTE = 0) 
	begin 
		INSERT INTO @TEMP_RS_TAB_DESC_PROD     
			 SELECT    
					0             AS CD_TBL_DESC,    
					28            AS TP_DESC,    
					0             AS FLAG_DESC_FIX,    
					PERC_DESC     AS PERC_DESC,    
					PERC_DESC     AS PERC_DESC_MAX,    
					1             AS TP_NIVEL,
			 		GETDATE()  AS DT_INI      
			   FROM RC_CLI_EST_PROD A 
					JOIN RC_CLI B ON A.CD_EMP = B.CD_EMP 
				  				 AND A.CD_CLI = B.CD_CLI 
							
			WHERE @CD_EMP    = A.CD_EMP 
			  AND @CD_PROD   = A.CD_PROD
			  AND @CD_CLI    = A.CD_CLI;
	END; 	   	  
	   	  

 	-- lista por tabela de desconto 
	if ( @TIPODESCONTOCLIENTE = 1 ) 
	begin 
	
	-------------------------------------------------------------------------------------------------------------------------------
	--buscando arvore merc. do produto
	-------------------------------------------------------------------------------------------------------------------------------	
		
	INSERT INTO @RS_EST_PROD_ARV_MERC		
	SELECT 
		   ARV.CD_EMP					,
		   ARV.CD_PROD					,
		   ARV.CD_ARV_MERC_CATEG		,
		   ARV.CD_ARV_MERC_LINHA		,
		   ARV.CD_MC					,
		   ARV.CD_ARV_MERC_FAMILIA		,												
		   P.CD_FABRIC 
	  FROM EST_PROD_EST_ARV_MERCADOLOGICA ARV 
	       INNER JOIN EST_PROD P ON ARV.CD_EMP		= P.CD_EMP
								AND ARV.CD_PROD = P.CD_PROD
	 WHERE ARV.CD_EMP  = @CD_EMP   
	   AND ARV.CD_PROD = @CD_PROD;
	  
	  
	 IF EXISTS (SELECT 1 FROM @RS_EST_PROD_ARV_MERC) 
	 BEGIN 
		 

	   INSERT INTO @RS_TAB_ATIVA_PROD    
			SELECT  A.CD_EMP,    
					A.CD_TBL_DESC,    
					A.CD_TP_DESCONTO
			  FROM EST_PROD_TBL_DESC A 
				   INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL FIL ON A.CD_EMP      = FIL.CD_EMP 
															  AND A.CD_TBL_DESC = FIL.CD_TBL_DESC
				   INNER JOIN EST_PROD_TBL_DESC_RC_CLI C ON A.CD_TBL_DESC = C.CD_TBL_DESC
											            AND A.CD_EMP      = C.CD_EMP
	
			  WHERE	@CD_EMP       = A.CD_EMP 
			   	AND GETDATE() BETWEEN A.DT_INI AND A.DT_FIM 
				AND 1  = A.STS_TBL_DESC 
				AND 28 = A.CD_TP_DESCONTO 
				AND @CD_CLI = C.CD_CLI
				AND A.ORIG_VENDA IN ( 0 , @TP_ORIG_VENDA )
				AND	(
				  ((@USA_DESCONTO_PROPZ_IDENT = 1) AND (A.FLAG_TP_DESCONTO_PROPZ IN (0,1))) OR  
				  ((@USA_DESCONTO_PROPZ_IDENT = 0) AND (A.FLAG_TP_DESCONTO_PROPZ =0))
				);  

				 
		IF EXISTS (SELECT 1 FROM @RS_TAB_ATIVA_PROD) 
		BEGIN
		
			-------------------------------------------------------------------------------------------------------------------------------    
			--consultando as tbls de desconto para verificar se ha algum desconto no nivel 1 - produtos    
			-------------------------------------------------------------------------------------------------------------------------------    
			INSERT INTO @TEMP_RS_TAB_DESC_PROD     
				SELECT    
						A.CD_TBL_DESC AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,    
						A.FLAG_DESC_FIX,    
						B.PERC_DESC AS PERC_DESC,    
						B.PERC_DESC_MAX AS PERC_DESC_MAX,    
						1 AS TP_NIVEL,
					    A.DT_INI      
				FROM EST_PROD_TBL_DESC A    
					 INNER JOIN EST_PROD_TBL_DESC_RC_CLI B ON A.CD_EMP      = B.CD_EMP 
												          AND A.CD_TBL_DESC = B.CD_TBL_DESC    
					 INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL FIL ON B.CD_EMP      = FIL.CD_EMP 
																AND B.CD_TBL_DESC = FIL.CD_TBL_DESC    
					 INNER JOIN @RS_TAB_ATIVA_PROD RS_TAB_ATIVA_PROD ON B.CD_EMP       = RS_TAB_ATIVA_PROD.CD_EMP 
																   AND B.CD_TBL_DESC = RS_TAB_ATIVA_PROD.CD_TBL_DESC     
					 INNER JOIN @RS_EST_PROD_ARV_MERC ARV ON B.CD_EMP  = ARV.CD_EMP 
														AND B.CD_PROD = ARV.CD_PROD
																												
			   WHERE @CD_PROD   = B.CD_PROD
			     AND @CD_CLI    = B.CD_CLI;
		 END;
	       
	   	   
	  end  ; 
	   	   
	 end  ;	  
INSERT INTO @RETURN_REC
				SELECT TOP 1
					   CD_TBL_DESC
					  ,TP_DESC
					  ,FLAG_DESC_FIX
					  ,PERC_DESC
					  ,PERC_DESC_MAX
					  ,TP_NIVEL
				  FROM @TEMP_RS_TAB_DESC_PROD
				 ORDER  BY
					   DT_INI    DESC
					  ,TP_NIVEL  ASC
				   	  ,PERC_DESC DESC;

			RETURN
	END 


