/****** Object:  UserDefinedFunction [dbo].[UFD_EST_PROD_TRIB_GERAR_IMPOSTO]    Script Date: 04/29/2014 23:12:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_TRIB_GERAR_IMPOSTO]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PROD_TRIB_GERAR_IMPOSTO]
GO 
GO
GO
/*
DECLARE @QT_PROD MONEY
DECLARE @VLR_IT MONEY
DECLARE @VLR_DESC_IT MONEY
DECLARE @VLR_FRETE MONEY
DECLARE @VLR_OUTROS MONEY
DECLARE @VLR_IPI MONEY
DECLARE @PERC_ICMS MONEY
DECLARE @PERC_ICMS_SUBST MONEY
DECLARE @VLR_PMC MONEY
DECLARE @PERC_IVA MONEY
DECLARE @PERC_REBU_ICMS MONEY
DECLARE @PERC_REDU_ICMS_SUBST MONEY
DECLARE @TP_SUBS INT
DECLARE @CD_TRIB INT
DECLARE @FLAG_GERAR_IMPOSTO_RETIDO INT
DECLARE @PRECO_BASE_SUBS_DESCONTO INT
DECLARE @PERC_FECOP MONEY
DECLARE @FLAG_PERC_DIFERIMENTO MONEY

SET @QT_PROD=1
SET @VLR_IT =125.86
SET @VLR_DESC_IT=0
SET @VLR_FRETE=0
SET @VLR_OUTROS =0
SET @VLR_IPI =0
SET @PERC_ICMS=18
SET @PERC_ICMS_SUBST=0
SET @VLR_PMC=0
SET @PERC_IVA=0
SET @PERC_REBU_ICMS=0
SET @PERC_REDU_ICMS_SUBST=0
SET @TP_SUBS=0
SET @PRECO_BASE_SUBS_DESCONTO=0
SET @FLAG_GERAR_IMPOSTO_RETIDO=0
SET @FLAG_PERC_DIFERIMENTO = 33.33 
SET @PERC_FECOP =  0
SET @CD_TRIB = 79

SELECT 	*  FROM  UFD_EST_PROD_TRIB_GERAR_IMPOSTO(@QT_PROD,@VLR_IT,@VLR_DESC_IT,@VLR_FRETE,@VLR_OUTROS,
									@VLR_IPI,@PERC_ICMS,@PERC_ICMS_SUBST,@VLR_PMC,@PERC_IVA,
									@PERC_REBU_ICMS,@PERC_REDU_ICMS_SUBST,@TP_SUBS,@CD_TRIB,
									@PRECO_BASE_SUBS_DESCONTO,@FLAG_GERAR_IMPOSTO_RETIDO,
									@PERC_FECOP,@FLAG_PERC_DIFERIMENTO)

*/
CREATE FUNCTION [dbo].[UFD_EST_PROD_TRIB_GERAR_IMPOSTO] (@QT_PROD money,
												@VLR_IT MONEY,
												@VLR_DESC_IT MONEY,
												@VLR_FRETE MONEY,
												@VLR_OUTROS MONEY,
												@VLR_IPI MONEY,
												@PERC_ICMS MONEY,
												@PERC_ICMS_SUBST MONEY,
												@VLR_PMC MONEY,
												@PERC_IVA  MONEY,
												@PERC_REBU_ICMS MONEY,
												@PERC_REDU_ICMS_SUBST MONEY,
												@TP_SUBS MONEY,
												@CD_TRIB MONEY,
												@PRECO_BASE_SUBS_DESCONTO INT,
												@FLAG_GERAR_IMPOSTO_RETIDO INT,
												@PERC_FECOP MONEY,
												@PERC_DIFERIMENTO MONEY)
RETURNS @RS_RETURN TABLE(QT_PROD INT,
						 VLR_IT MONEY,
						 VLR_DESC_IT MONEY,
						 VLR_FRETE MONEY,
						 VLR_OUTROS MONEY,
						 VLR_IPI MONEY,					     
						 VLR_BASE_ICMS MONEY,
                         VLR_ICMS MONEY,    
                         VLR_BASE_SUBS MONEY,    
                         VLR_ICMS_SUBS MONEY,
						 VLR_BASE_ICMS_OP MONEY,
						 VLR_ICMS_OP MONEY,
						 VLR_BASE_RET MONEY,
						 VLR_ICMS_RET MONEY,
                         VLR_CUSTO MONEY,
						 VLR_REA MONEY,
						 VLR_BASE_FCOP MONEY,
						 VLR_BASE_FCOP_SUBS MONEY,
						 VLR_BASE_FCOP_RET MONEY,
						 VLR_FCOP MONEY,
						 VLR_FCOP_SUBS MONEY,
						 VLR_FCOP_RET MONEY, 
						 VLR_DIFERIMENTO MONEY)
AS
begin
DECLARE @VLR_CUSTO MONEY
DECLARE @VLR_PROD_TOTAL MONEY
DECLARE @VLR_PROD_TOTAL_LIQ MONEY
DECLARE @GERA_ICMS INT
DECLARE @GERA_SUBS_TRIB INT
DECLARE @GERA_SUBS_TRIB_ANT INT
DECLARE @GERA_ISENTO INT
DECLARE @GERA_FLAG_OUTROS INT
DECLARE @GERA_FLAG_DIFERIDO INT

DECLARE @VLR_REA MONEY
DECLARE @VLR_BASE_ICMS MONEY
DECLARE @VLR_ICMS MONEY
DECLARE @VLR_BASE_ICMS_SUBST MONEY
DECLARE @VLR_ICMS_SUBST MONEY
DECLARE @VLR_BASE_ICMS_OP MONEY
DECLARE @VLR_ICMS_OP MONEY
DECLARE @VLR_BASE_RET MONEY
DECLARE @VLR_ICMS_RET MONEY
DECLARE @VLR_PROD_ITEM MONEY

DECLARE @VLR_FCOP MONEY
DECLARE @VLR_FCOP_SUBS MONEY
DECLARE @VLR_FCOP_RET MONEY
DECLARE @VLR_BASE_FCOP MONEY
DECLARE @VLR_BASE_FCOP_SUBS MONEY
DECLARE @VLR_BASE_FCOP_RET MONEY
DECLARE @VLR_DIFERIMENTO  MONEY

SELECT
	@GERA_ICMS=ICMS,
	@GERA_SUBS_TRIB=SUBS_TRIB,
	@GERA_SUBS_TRIB_ANT=SUBS_TRIB_ANT,
	@GERA_ISENTO=ISENTO,
	@GERA_FLAG_OUTROS=FLAG_OUTROS,
	@GERA_FLAG_DIFERIDO = ICMS_DIFERIDO   --  SELECT * 
FROM
	EST_TRIB
WHERE
	CD_TRIB =   @CD_TRIB

--Zerando variaveis
SET @VLR_PROD_TOTAL=0
SET @VLR_PROD_TOTAL_LIQ =0
SET @VLR_BASE_ICMS =0
SET @VLR_ICMS =0
SET @VLR_BASE_ICMS_SUBST =0
SET @VLR_ICMS_SUBST =0
SET @VLR_BASE_ICMS_OP =0
SET @VLR_ICMS_OP =0
SET @VLR_BASE_RET =0
SET @VLR_ICMS_RET =0
SET @VLR_PROD_ITEM =0
SET @VLR_REA=0
SET @VLR_FCOP=0
SET @VLR_FCOP_SUBS=0
SET @VLR_FCOP_RET=0
SET @VLR_BASE_FCOP=0
SET @VLR_BASE_FCOP_SUBS=0
SET @VLR_BASE_FCOP_RET=0
SET @VLR_DIFERIMENTO = 0 

--Fim

--Totalizando produto
SET @VLR_PROD_TOTAL=@QT_PROD *@VLR_IT

--Gerando valor total liquido
SET @VLR_PROD_TOTAL_LIQ=(@VLR_PROD_TOTAL - (@QT_PROD*@VLR_DESC_IT)) + (@VLR_FRETE*@QT_PROD)

--Nao gerar imposto retido e nem REA - CE
IF @FLAG_GERAR_IMPOSTO_RETIDO=0
	begin
	IF (@GERA_ICMS+@GERA_SUBS_TRIB)>0 
	begin
			--***************************ICMS**************************************
			--Gerando Base de ICMS
			SET @VLR_BASE_ICMS=@VLR_PROD_TOTAL_LIQ

			--Tem reducao?
			IF @PERC_REBU_ICMS >0
			begin
				SET @VLR_BASE_ICMS=@VLR_BASE_ICMS * (@PERC_REBU_ICMS/100.00)
			end

			 --Gerando valor do icms
			 SET @VLR_ICMS=(@VLR_BASE_ICMS*@PERC_ICMS)/100.00

			 --GERANDO FCOP ICMS
			 IF  @PERC_FECOP>0
			 BEGIN
				SET @VLR_BASE_FCOP=@VLR_BASE_ICMS	  		  
				SET @VLR_FCOP=ROUND(((@PERC_FECOP * @VLR_BASE_FCOP) /100),2)
			 END

			--***************************ICMS SUBST**************************************

		if @GERA_SUBS_TRIB=1
		begin
			--Gerando base de icms substituicao
			IF @TP_SUBS=0 --MVA
			begin
				--Valor bruto da base de subs
				IF @PRECO_BASE_SUBS_DESCONTO=1
					begin
						SET @VLR_BASE_ICMS_SUBST =(@VLR_PROD_TOTAL+@VLR_IPI)*(1+(@PERC_IVA/100.00))
					end
				ELSE --Valor liquido da base de subs
					begin
						SET @VLR_BASE_ICMS_SUBST =(@VLR_PROD_TOTAL_LIQ+@VLR_IPI)*(1+(@PERC_IVA/100.00))				
					end
			end


			IF @TP_SUBS=1 --Pauta ICMS
			begin
				SET @VLR_BASE_ICMS_SUBST =@QT_PROD*@PERC_IVA
			end

			IF @TP_SUBS=3 OR @TP_SUBS=4 --3-PMC , 4-PMPF  -  O campo @VLR_PMC foi alicmentado com o valor do PMPF
			begin
				SET @VLR_BASE_ICMS_SUBST =(@VLR_PMC*@QT_PROD)
			
				IF @PERC_REBU_ICMS >0
				begin
					SET @VLR_BASE_ICMS=@VLR_BASE_ICMS * (@PERC_REBU_ICMS/100.00)
				end			
			end

			--Tem reducao?
			IF @PERC_REDU_ICMS_SUBST >0
			begin
				SET @VLR_BASE_ICMS_SUBST=@VLR_BASE_ICMS_SUBST * (@PERC_REDU_ICMS_SUBST/100.00)
			end

			SET @VLR_ICMS_SUBST=((@VLR_BASE_ICMS_SUBST*@PERC_ICMS_SUBST)/100.00) - @VLR_ICMS

			IF @TP_SUBS=2 --Carga tributaria, ela possui calculo do icms substituicao final diferente das demais
			begin
				SET @VLR_ICMS_SUBST =((@VLR_PROD_TOTAL_LIQ+@VLR_IPI)*@PERC_IVA)/100.00
				SET @VLR_BASE_ICMS_SUBST=((@VLR_ICMS_SUBST+@VLR_ICMS)/@PERC_ICMS_SUBST)*100.00
			end

			 --GERANDO FCOP SUBS
			 IF  @PERC_FECOP>0
			 BEGIN
				SET @VLR_BASE_FCOP_SUBS=@VLR_BASE_ICMS_SUBST
				SET @VLR_FCOP_SUBS=ROUND(((@PERC_FECOP * @VLR_BASE_FCOP_SUBS) /100),2) - @VLR_FCOP
			 END
		end
	end

	IF @GERA_SUBS_TRIB_ANT=1
	begin
		IF @PERC_IVA>0
		begin
			SET @VLR_PROD_ITEM=ROUND(@VLR_PROD_TOTAL_LIQ/(1+(((@PERC_ICMS/100.00)*(@PERC_IVA/100.0000)))),2)
			SET @VLR_BASE_ICMS_OP =@VLR_PROD_ITEM
			SET @VLR_ICMS_OP=(@VLR_BASE_ICMS_OP*@PERC_ICMS)/100.00

			SET @VLR_BASE_RET =(@VLR_PROD_ITEM)*(1+(@PERC_IVA/100.00))

			SET @VLR_ICMS_RET=((@VLR_BASE_RET*@PERC_ICMS_SUBST)/100.00) - @VLR_ICMS_OP

			 --GERANDO FCOP ICMS RETIDO
			 IF  @PERC_FECOP>0
			 BEGIN
				SET @VLR_BASE_FCOP_RET=@VLR_BASE_RET	 
				SET @VLR_FCOP_RET=ROUND(((@PERC_FECOP * @VLR_BASE_FCOP_RET) /100),2)
			 END
		end
	end
end


--Gerar imposto pelo REA - CE
IF @FLAG_GERAR_IMPOSTO_RETIDO=2
begin
	--Valor bruto da base de subs
	IF @PRECO_BASE_SUBS_DESCONTO=1
		begin
			SET @VLR_BASE_ICMS_SUBST =(@VLR_PROD_TOTAL+@VLR_IPI)*(1+(@PERC_IVA/100.00))
		end
	ELSE --Valor liquido da base de subs
		begin
			SET @VLR_BASE_ICMS_SUBST =(@VLR_PROD_TOTAL_LIQ+@VLR_IPI)*(1+(@PERC_IVA/100.00))				
		end	

	--Tem reducao?
	IF @PERC_REDU_ICMS_SUBST >0
	begin
		SET @VLR_BASE_ICMS_SUBST=@VLR_BASE_ICMS_SUBST * (@PERC_REDU_ICMS_SUBST/100.00)
	end

	SET @VLR_ICMS_SUBST=((@VLR_BASE_ICMS_SUBST*@PERC_ICMS)/100.00)
	SET @VLR_REA=@VLR_ICMS_SUBST/@QT_PROD
	SET @VLR_ICMS_SUBST=0
	SET @VLR_BASE_ICMS_SUBST=0
end


IF @QT_PROD>0
begin
	--Nao gerar imposto retido e nem REA - CE
	IF @FLAG_GERAR_IMPOSTO_RETIDO=0
	begin	
		--Gerou Substituicao acrescenta no custo
		if @GERA_SUBS_TRIB=1
			begin
				SET @VLR_CUSTO = ((@VLR_PROD_TOTAL_LIQ+@VLR_IPI+@VLR_ICMS_SUBST+@VLR_FRETE+@VLR_OUTROS))/@QT_PROD
			end
		else --Nao Gerou retira no custo
			begin
				SET @VLR_CUSTO = ((@VLR_PROD_TOTAL_LIQ+@VLR_IPI+@VLR_FRETE+@VLR_OUTROS) - @VLR_ICMS)/@QT_PROD		
			end
	end

	--Nao gerar imposto retido e nem REA - CE
	IF @FLAG_GERAR_IMPOSTO_RETIDO=2
	begin	
		begin
			SET @VLR_CUSTO = (((@VLR_PROD_TOTAL_LIQ+@VLR_IPI+@VLR_FRETE+@VLR_OUTROS))/@QT_PROD)+ @VLR_REA
		end
	end
end


IF @GERA_FLAG_DIFERIDO = 1
	BEGIN
	SET @VLR_ICMS_OP = (@VLR_BASE_ICMS*@PERC_ICMS)/100.00
	SET @VLR_DIFERIMENTO =  @VLR_BASE_ICMS * ( @PERC_DIFERIMENTO/ 100.00)   * @PERC_ICMS/100.00 
    SET @VLR_ICMS= @VLR_ICMS_OP - @VLR_DIFERIMENTO
END

IF isnull (@VLR_CUSTO,0) <=0
BEGIN
	SET @VLR_CUSTO=0
END

INSERT INTO @RS_RETURN
			SELECT  @QT_PROD,
				    @VLR_IT,
				    @VLR_DESC_IT,
				    @VLR_FRETE,
				    @VLR_OUTROS,
				    @VLR_IPI,
			        @VLR_BASE_ICMS,
					@VLR_ICMS,
					@VLR_BASE_ICMS_SUBST,
					@VLR_ICMS_SUBST,
					@VLR_BASE_ICMS_OP,
					@VLR_ICMS_OP,
				    @VLR_BASE_RET,
					@VLR_ICMS_RET,
					@VLR_CUSTO,
					@VLR_REA,
					@VLR_BASE_FCOP,
					@VLR_BASE_FCOP_SUBS,
					@VLR_BASE_FCOP_RET,
					@VLR_FCOP,
					@VLR_FCOP_SUBS,
					@VLR_FCOP_RET,
					@VLR_DIFERIMENTO
    RETURN 
end
go