


IF NOT OBJECT_ID('UFD_BUSCA_VLR_BASE_ICMS') IS NULL
	BEGIN 
		DROP FUNCTION UFD_BUSCA_VLR_BASE_ICMS
	END

GO

-- SELECT DBO.UFD_BUSCA_VLR_BASE_ICMS(1,1,11192380)

 

CREATE FUNCTION [dbo].UFD_BUSCA_VLR_BASE_ICMS(	@CD_EMP		    INT, 
													@CD_FILIAL	INT,
													@CD_VD		INT
													)
RETURNS VARCHAR(MAX) 
AS
BEGIN

	DECLARE @RETORNO MONEY
	DECLARE @RS_VALOR MONEY

	SET @RETORNO = 0

	DECLARE RS_IMPOSTO CURSOR FOR
 	  
      
	    SELECT 
			
			 CASE  WHEN  ISNULL(I.TIPO_IMPOSTO, 0) = 1 then
				0
			 ELSE 
							CASE WHEN PRC_FILIAL.REGIME_TRIBUTARIO IN (1,2) AND LTRIM(RIGHT(I.CD_TRIB_FC, 2))='500' THEN
								0  --  SELECT REGIME_TRIBUTARIO, * FROM PRC_FILIAL WHERE CD_FILIAL = 7
							ELSE
								CASE WHEN LTRIM(RIGHT(I.CD_TRIB_FC, 2)) IN ('00','20','02') THEN 
									SUM( I.TOT_IT - I.VLR_DESC_TOT_RAT ) 
								ELSE 0
								END
							END
    
			END AS VLR_BASE_ICMS 
    
	   
  
		   FROM PDV_VD
         INNER JOIN PDV_VD_IT I ON I.CD_EMP = PDV_VD.CD_EMP
                                   AND I.CD_FILIAL = PDV_VD.CD_FILIAL
                                   AND I.CD_VD = PDV_VD.CD_VD
         INNER JOIN PDV_CX ON PDV_VD.CD_EMP = PDV_CX.CD_EMP
                              AND PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL
                              AND PDV_VD.CD_CX = PDV_CX.CD_CX
		 INNER JOIN PRC_FILIAL ON PDV_VD.CD_EMP  = PRC_FILIAL.CD_EMP
		 AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
         LEFT JOIN PDV_MAPA_RESUMO MAPA ON MAPA.CD_EMP = PDV_VD.CD_EMP
                                           AND MAPA.CD_FILIAL = PDV_VD.CD_FILIAL
                                           AND MAPA.DT_ECF = PDV_VD.DT_VD
                                           AND MAPA.CD_CX = PDV_VD.CD_CX
	
		
		WHERE 
			PDV_VD.CD_EMP = @CD_EMP
			AND PDV_VD.CD_FILIAL = @CD_FILIAL
			AND PDV_VD.CD_VD = @CD_VD
			AND  I.ST_IT = 1


			GROUP BY PDV_VD.CD_EMP,I.TIPO_IMPOSTO,PRC_FILIAL.REGIME_TRIBUTARIO,
			I.CD_TRIB_FC,I.TX_ICMS_IT
	
			
		--Abrindo cursor
		OPEN RS_IMPOSTO
			
		--Atribuindo valores do select nas variáveis
		FETCH NEXT FROM RS_IMPOSTO INTO  @RS_VALOR
		
		SET @RETORNO = @RS_VALOR

		CLOSE RS_IMPOSTO
		DEALLOCATE RS_IMPOSTO
		
RETURN @RETORNO
 
END
