

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

GO

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

 

CREATE FUNCTION [dbo].UFD_BUSCA_VLR_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 I.CD_TRIB_FC='500' THEN
						0
					ELSE
						CASE WHEN LTRIM(RIGHT(I.CD_TRIB_FC, 2)) IN ('00','20','02') THEN
							CASE WHEN LTRIM(RIGHT(I.CD_TRIB_FC, 2)) = '00' THEN 
							 SUM ( I.TOT_IT - I.VLR_DESC_TOT_RAT ) * ( I.TX_ICMS_IT / 100.0000 )
							ELSE 
							SUM ( ( I.TOT_IT - VLR_DESC_TOT_RAT ) * ( I.TX_RED_IT / 100.0000 ) ) * ( I.TX_ICMS_IT / 100.0000 )
						
							END
						ELSE 0 END
			END				 
					END  AS VLR_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
