IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'UFD_EST_PROD_TRIB_GERAR_IMPOSTO') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION UFD_EST_PROD_TRIB_GERAR_IMPOSTO
GO 

/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20240322
'Job.................:  
'Nome................: UFD_EST_PROD_TRIB_GERAR_IMPOSTO
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 22/03/2024
'Dt Ult Alteracão....: --
'Criado por..........: 
'Versionado por......: Marcos Barros
'Alterado por........: Gerson Reis
'=======================================================================================
*/

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,
												@PERC_ICMS_SUBSTITUTO MONEY,
												@VLR_PAUTA_IVA_SUBSTITUTO MONEY,
												@FLAG_NAO_ABATER_FCP_OP INT = 0)
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,
						 VLR_ICMS_SUBSTITUTO 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
DECLARE @VLR_ICMS_SUBSTITUTO 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
SET @VLR_ICMS_SUBSTITUTO = 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) - CASE WHEN @FLAG_NAO_ABATER_FCP_OP = 1 THEN 0 ELSE @VLR_FCOP END
			 END
			 
		end
	end
	IF @GERA_SUBS_TRIB_ANT=1   -- SELECT * FROM EST_TRIB
	begin
		IF @PERC_IVA>0 OR @VLR_PAUTA_IVA_SUBSTITUTO >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_SUBSTITUTO =(@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_SUBSTITUTO
			SET @VLR_PROD_ITEM = ROUND(@VLR_PROD_TOTAL_LIQ/(1+(((@PERC_ICMS_SUBSTITUTO/100.00)*(@VLR_PAUTA_IVA_SUBSTITUTO/100.0000)))),2)
			SET @VLR_BASE_ICMS_OP =@VLR_PROD_ITEM
			SET @VLR_ICMS_SUBSTITUTO =(@VLR_BASE_ICMS_OP*@PERC_ICMS_SUBSTITUTO)/100.00
			 --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 * @QT_PROD)+@VLR_OUTROS))/@QT_PROD
			end
		else --Nao Gerou retira no custo
			begin
				SET @VLR_CUSTO = ((@VLR_PROD_TOTAL_LIQ+@VLR_IPI+(@VLR_FRETE * @QT_PROD)+@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= ROUND(@VLR_ICMS_OP,2) - ROUND(@VLR_DIFERIMENTO,2)
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,
					@VLR_ICMS_SUBSTITUTO
    RETURN 
end

GO


