/****** Object:  StoredProcedure [dbo].[P_EST_NF_ENT_IT_IMPORTA_DESCONTO_FINANCEIRO]    Script Date: 16/07/2014 09:55:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_ENT_IT_IMPORTA_DESCONTO_FINANCEIRO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_ENT_IT_IMPORTA_DESCONTO_FINANCEIRO]
GO

--EXEC P_EST_NF_ENT_IT_IMPORTA_DESCONTO_FINANCEIRO '45453214002600','','42225938/0030-94',1,93677,'04/01/2014',679975, 0, 18.98,1
CREATE PROCEDURE [dbo].[P_EST_NF_ENT_IT_IMPORTA_DESCONTO_FINANCEIRO](@CGC_CPF_FORN AS VARCHAR(20),
															 @CD_FIL_FORN AS VARCHAR(20)='',
															 @CGC_CPF_FILIAL AS VARCHAR(20)='',
															 @TIPO_CD_FIL_FORN	AS INT=0,
															 @NR_NF		  AS INT,
															 @DT_EMI_NF    AS DATETIME,
															 @CD_PROD_FORN  AS VARCHAR(40), 
															 @PERC_DESC_FINAN AS MONEY,
															 @VLR_DESC_FINAN AS MONEY,
															 @CONSULTA INT =0)

WITH ENCRYPTION AS
BEGIN
DECLARE @CD_PROD INT
DECLARE @CD_FILIAL INT
DECLARE @CD_FORN AS INT
DECLARE @CGC_CPF_SO_NUMEROS AS VARCHAR
DECLARE @mensagemErro AS varchar(500)
SET @CGC_CPF_SO_NUMEROS=(SELECT DBO.F_LIMPA_NUMEROS(@CGC_CPF_FORN))

--Buscando codigo do fornecedor
SET @CD_FORN =ISNULL((SELECT
				TOP 1 CD_FORN
			   FROM
				PG_FORN
			   WHERE
				(CGC_CPF = @CGC_CPF_FORN OR  CGC_CPF = @CGC_CPF_SO_NUMEROS)),0)


--Buscando pelo codigo do fornecedor
IF @TIPO_CD_FIL_FORN=0
begin
SET @CD_FILIAL = ISNULL((SELECT 
					TOP 1 CD_FILIAL
				 FROM
					PG_FORN_CD_FIL_FORN
				 WHERE
					CD_EMP = 1 
					AND CD_FORN = @CD_FORN 
					AND CD_FIL_FORN = @CD_FIL_FORN),0)
End

--Buscando pelo CGC da filial
IF @TIPO_CD_FIL_FORN=1
begin
	SET @CD_FILIAL = ISNULL((SELECT 
						TOP 1 CD_FILIAL
					 FROM
						PRC_FILIAL
					 WHERE
						CD_EMP = 1 
						AND PRC_FILIAL.CGC = @CGC_CPF_FILIAL),0)
End

--Buscando codigo do produto junto ao fornecedor
SET @CD_PROD = ISNULL((SELECT 
					TOP 1 CD_PROD
				 FROM
					EST_PROD_FORN
				 WHERE
					CD_EMP = 1 
					AND CD_FORN = @CD_FORN
					AND CD_PROD_FORN = @CD_PROD_FORN),0)


if @CD_FORN = 0
begin
	   	set @mensagemErro ='Fornecedor nao encontrado'  
		RAISERROR (@mensagemErro, 16,0);
		RETURN
end

if @CD_FILIAL = 0
begin
	   	set @mensagemErro ='Filial informada nao encontrada no cadastro fornecedor - ' + convert(varchar, @CD_FORN) 
		RAISERROR (@mensagemErro, 16,0);
		RETURN
end

if @CD_PROD = 0
begin
	   	set @mensagemErro ='Produto informado ' + convert(varchar, @CD_PROD_FORN) + ' nao encontrado no cadastro fornecedor - ' + convert(varchar, @CD_FORN)
		RAISERROR (@mensagemErro, 16,0);
		RETURN
end

IF @CONSULTA=0
begin
UPDATE
	EST_NF_ENT_IT
SET 
	PERC_DESC_FINAN=CASE WHEN @PERC_DESC_FINAN= 0 THEN 
						CASE WHEN ISNULL(@VLR_DESC_FINAN,0)>0 THEN
							(@VLR_DESC_FINAN*100)/((ISNULL(I.VLR_IT,0) + ISNULL(I.VLR_FRETE_IT,0) - ISNULL(I.VLR_DESC,0)/I.QT_IT))
							ELSE
							0
						END					
					ELSE 
						@PERC_DESC_FINAN 
					END,
	VLR_DESC_FINAN=CASE WHEN @VLR_DESC_FINAN= 0 THEN 
						CASE WHEN ISNULL(@PERC_DESC_FINAN,0)>0 THEN
							  ((ISNULL(I.VLR_TOT_IT,0) + ISNULL(I.VLR_FRETE_IT,0) - I.VLR_DESC)/I.QT_IT) * (ISNULL(@PERC_DESC_FINAN,0)/100)
						ELSE
							0
						END					
					ELSE 
						@VLR_DESC_FINAN 
					END
FROM
	EST_NF_ENT_IT i INNER JOIN EST_NF_ENT e ON
	i.CD_EMP = e.CD_EMP AND
	i.CD_FILIAL = e.CD_FILIAL AND
	i.CD_NF = e.CD_NF 
where
	e.CD_EMP = 1
    AND e.CD_FILIAL = @CD_FILIAL
    AND e.CD_FORN = @CD_FORN
    AND e.NR_NF =@NR_NF
    AND e.DT_EMI_NF =@DT_EMI_NF
	AND i.CD_PROD = @CD_PROD
end

IF @CONSULTA=1
begin
SELECT
    e.CD_FILIAL,
	e.CD_NF,
	e.NR_NF,
	e.CD_FORN,
	e.DT_EMI_NF,
	i.PERC_DESC_FINAN,
	i.VLR_DESC_FINAN
FROM
	EST_NF_ENT_IT i INNER JOIN EST_NF_ENT e ON
	i.CD_EMP = e.CD_EMP AND
	i.CD_FILIAL = e.CD_FILIAL AND
	i.CD_NF = e.CD_NF 
where
	e.CD_EMP = 1
    AND e.CD_FILIAL = @CD_FILIAL
    AND e.CD_FORN = @CD_FORN
    AND e.NR_NF =@NR_NF
    AND e.DT_EMI_NF =@DT_EMI_NF
	AND i.CD_PROD = @CD_PROD
end
END

GO


