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

-- SELECT * FROM UFD_EST_PROD_TBL_DESC_PRE_VENCIDOS(1, 26, '0000009000000') 
CREATE FUNCTION [dbo].[UFD_EST_PROD_TBL_DESC_PRE_VENCIDOS] (@CD_EMP INT, @CD_FILIAL INT, @EAN varchar(13) ) --, @CD_FRM_PGTO INT)
RETURNS  @RETURN_REC 
TABLE(
	CD_TBL_DESC 		INT,
	PERC_DESC			MONEY,
	PERC_DESC_MAX		MONEY,
	TP_NIVEL			INT
)
AS 
BEGIN

-- VERSÃO 14/05/2020

	-------------------------------------------------------------------------------------------------------------------------------    
    --verifica o se o produto e pre-vencido
    ------------------------------------------------------------------------------------------------------------------------------- 
	DECLARE @VENCIDO INT
	DECLARE @TRABALHACOMDESCONTOFORMADEPAGAMENTO INT = 0 
	
		SET @TRABALHACOMDESCONTOFORMADEPAGAMENTO = ISNULL (( SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_CHAVE = 'USA_DESCONTO_PGTO' AND CD_FILIAL = @CD_FILIAL), 0)
		SET @VENCIDO =ISNULL((SELECT FLAG_PRE_VENCIDOS FROM EST_PROD_CD_BARRA WHERE CD_EMP=@CD_EMP AND CD_BARRA = @EAN),0);
		
		IF @VENCIDO=0 
			RETURN;

	
	-------------------------------------------------------------------------------------------------------------------------------    
	DECLARE @RS_TMP_DESC TABLE( CD_TBL_DESC 		INT,
							 PERC_DESC			MONEY,
							 PERC_DESC_MAX		MONEY,
							 TP_NIVEL			INT);

    -------------------------------------------------------------------------------------------------------------------------------    
    -------------------------------------------------------------------------------------------------------------------------------    
	


    INSERT INTO @RS_TMP_DESC     
    SELECT DISTINCT   
	   C.CD_TBL_DESC AS CD_TBL_DESC,        
	   C.VLR_DESC AS PERC_DESC,    
	   C.VLR_DESC AS PERC_DESC_MAX,   
	   1 AS TP_NIVEL      
    FROM 
	   EST_PROD_CPL_LOTE A
	   JOIN EST_PROD_REM_PRE_VENC_IT_LOTE B ON 
	   A.CD_EMP	 = B.CD_EMP AND 
	   A.CD_FILIAL = B.CD_FILIAL AND 
	   A.CD_LOTE	 = B.CD_LOTE  
	   JOIN EST_PROD_TBL_DESC_PRE_VENCIDOS_CPL C ON 
	   B.CD_EMP	 = C.CD_EMP AND 
	   B.CD_TBL_DESC = C.CD_TBL_DESC  
    WHERE
        DATEDIFF(DAY,GETDATE(), DT_VALID) BETWEEN C.DIAS_FIM AND C.DIAS_INI  
	   AND  B.EAN = @EAN
	   AND A.CD_FILIAL = @CD_FILIAL
	   AND A.CD_EMP = @CD_EMP;

    -------------------------------------------------------------------------------------------------------------------------------    
    -------------------------------------------------------------------------------------------------------------------------------    
    
    INSERT INTO @RS_TMP_DESC     
    SELECT DISTINCT   
	   C.CD_TBL_DESC AS CD_TBL_DESC,        
	   C.VLR_DESC AS PERC_DESC,    
	   C.VLR_DESC AS PERC_DESC_MAX,    
	   1 AS TP_NIVEL      
    FROM 
	   EST_PROD_CPL_LOTE A
	   JOIN EST_PROD_REM_PRE_VENC_IT_LOTE B ON 
	   A.CD_EMP	 = B.CD_EMP AND 
	   A.CD_FILIAL = B.CD_FILIAL AND 
	   A.CD_LOTE	 = B.CD_LOTE  
	   JOIN EST_PROD_TBL_DESC_PRE_VENCIDOS_CPL C ON 
	   B.CD_EMP	 = C.CD_EMP AND 
	   B.CD_TBL_DESC = C.CD_TBL_DESC  
    WHERE
        A.QTDE_PROD BETWEEN C.QTDE_FIM   and C.QTDE_INI
	   AND  B.EAN = @EAN
	   AND A.CD_FILIAL = @CD_FILIAL
	   AND A.CD_EMP = @CD_EMP

/*
			IF @TRABALHACOMDESCONTOFORMADEPAGAMENTO = 1  
			BEGIN   
				DELETE FROM RS_TMP_DESC    
				  from @RS_TMP_DESC RS_TMP_DESC
				 WHERE NOT EXISTS (SELECT B.CD_EMP
									 FROM EST_PROD_TBL_DESC_FORMA_PGTO B  
									WHERE B.CD_TBL_DESC   = RS_TMP_DESC.CD_TBL_DESC   
									  AND B.CD_FORMA_PGTO = @CD_FRM_PGTO  
										) ;
				
			END 
*/

    -------------------------------------------------------------------------------------------------------------------------------    
    --Resultado final
    ------------------------------------------------------------------------------------------------------------------------------- 
	INSERT INTO @RETURN_REC
	SELECT TOP 
		  1 
		  CD_TBL_DESC,
		  PERC_DESC, 
		  PERC_DESC_MAX, 
		  TP_NIVEL
	FROM @RS_TMP_DESC 
	ORDER BY 
	   PERC_DESC DESC

	RETURN
END

	
GO


