IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_CPL_ACERTO_CUSTO_CORRECAO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_CPL_ACERTO_CUSTO_CORRECAO]
GO
--EXEC P_EST_PROD_CPL_ACERTO_CUSTO_CORRECAO 1,1,'2016-09-25',0,0,1,1
CREATE PROCEDURE [dbo].[P_EST_PROD_CPL_ACERTO_CUSTO_CORRECAO] (@CD_EMP INT, 
													   @CD_FILIAL INT, 
													   @DT_PROC_INICIAL DATETIME, 
													   @CD_PROD_PAR INT=0, 
													   @VLR_CUSTO MONEY=0,
													   @CD_USU INT,
													   @FLAG_ANALISE INT=0)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CD_PROD INT;
DECLARE @DT_ENT DATETIME;
DECLARE @VLR_PRECO MONEY;
declare @IsGrossUP tinyint;

CREATE TABLE #RsProdNotaEntrada (CD_EMP INT,
								 CD_FILIAL INT,
								 CD_PROD INT,
								 DT_MOV DATETIME,
								 VLR_CUSTO_NOVO MONEY,
								 VLR_CUSTO_ATUAL MONEY,
								 VLR_TABELA MONEY,
								 DS_LINHA VARCHAR(150),
								 TIPO_PRECO VARCHAR(60))
INSERT INTO #RsProdNotaEntrada
SELECT
	EST_PROD_CPL.CD_EMP,
	EST_PROD_CPL.CD_FILIAL,
	EST_PROD_CPL.CD_PROD,
	@DT_PROC_INICIAL AS DT_ENT,
	CASE WHEN @VLR_CUSTO=0 THEN
		CASE WHEN  ISNULL(EST_PROD.VLR_COMPRA_GRP,0) = 0 THEN
			(CASE WHEN ISNULL(V_EST_PROD_PRECO_TBL.VLR_FORN,0)>0 THEN
				V_EST_PROD_PRECO_TBL.VLR_FORN * (ISNULL(PERC_DESCONTO,0)/100.00)
			ELSE
				ISNULL(EST_PROD_PRECO.VLR_TABELA,0)
			END) * (PERC_CMV_PREVISTO/100.00)
		ELSE
			ISNULL(EST_PROD.VLR_COMPRA_GRP,0)
		END 
	ELSE
		@VLR_CUSTO
	END,
	EST_PROD_CPL.VLR_CUSTO_MD,
	EST_PROD_PRECO.VLR_TABELA,
	EST_ARV_MERC_LINHA.DS_ARV_MERC_LINHA,
	'CUSTO MOVIMENTACAO ENTRADA'
FROM
	EST_PROD_CPL  LEFT OUTER JOIN EST_PROD_PRECO ON  
	EST_PROD_CPL.CD_EMP = EST_PROD_PRECO.CD_EMP AND
	EST_PROD_CPL.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL AND
	EST_PROD_CPL.CD_PROD = EST_PROD_PRECO.CD_PROD 
	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON
	EST_PROD_CPL.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
	EST_PROD_CPL.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
	INNER JOIN EST_ARV_MERC_LINHA ON
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP AND
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA
	INNER JOIN EST_PROD ON
	EST_PROD_CPL.CD_EMP = EST_PROD.CD_EMP AND
	EST_PROD_CPL.CD_PROD = EST_PROD.CD_PROD 
	LEFT OUTER JOIN V_EST_PROD_PRECO_TBL ON
	EST_PROD_CPL.CD_EMP = V_EST_PROD_PRECO_TBL.CD_EMP AND 
	EST_PROD_CPL.CD_FILIAL = V_EST_PROD_PRECO_TBL.CD_FILIAL AND
	EST_PROD_CPL.CD_PROD = V_EST_PROD_PRECO_TBL.CD_PROD 
WHERE
	EST_PROD_CPL.CD_EMP=@CD_EMP
	AND EST_PROD_CPL.CD_FILIAL=@CD_FILIAL
	AND EST_PROD_CPL.CD_PROD = CASE WHEN @CD_PROD_PAR=0 THEN EST_PROD_CPL.CD_PROD ELSE @CD_PROD_PAR END
UNION ALL
SELECT
	EST_PROD_CPL.CD_EMP,
	EST_PROD_CPL.CD_FILIAL,
	EST_PROD_CPL.CD_PROD,
	EST_NF_ENT_IT.DT_ENT,
	CASE WHEN @VLR_CUSTO=0 THEN
		CASE WHEN EST_NF_ENT_IT.VLR_PRC_CUSTO= 0 THEN 
			CASE WHEN  ISNULL(EST_PROD.VLR_COMPRA_GRP,0) = 0 THEN
				(CASE WHEN ISNULL(V_EST_PROD_PRECO_TBL.VLR_FORN,0)>0 THEN
					V_EST_PROD_PRECO_TBL.VLR_FORN * (ISNULL(PERC_DESCONTO,0)/100.00)
				ELSE
					ISNULL(EST_PROD_PRECO.VLR_TABELA,0)
				END) * (PERC_CMV_PREVISTO/100.00)
			ELSE
				ISNULL(EST_PROD.VLR_COMPRA_GRP,0)
			END 
		ELSE
			EST_NF_ENT_IT.VLR_PRC_CUSTO
		END
	ELSE
		@VLR_CUSTO
	END,
	EST_PROD_CPL.VLR_CUSTO_MD,
	EST_PROD_PRECO.VLR_TABELA,
	EST_ARV_MERC_LINHA.DS_ARV_MERC_LINHA,
	'CUSTO MOVIMENTACAO ENTRADA'
FROM
	EST_PROD_CPL LEFT OUTER JOIN EST_PROD_PRECO ON  
	EST_PROD_CPL.CD_EMP = EST_PROD_PRECO.CD_EMP AND
	EST_PROD_CPL.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL AND
	EST_PROD_CPL.CD_PROD = EST_PROD_PRECO.CD_PROD 
	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON
	EST_PROD_CPL.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
	EST_PROD_CPL.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
	INNER JOIN EST_ARV_MERC_LINHA ON
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP AND
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA
	INNER JOIN EST_PROD ON
	EST_PROD_CPL.CD_EMP = EST_PROD.CD_EMP AND
	EST_PROD_CPL.CD_PROD = EST_PROD.CD_PROD 
	INNER JOIN EST_NF_ENT_IT ON
	EST_PROD_CPL.CD_EMP = EST_NF_ENT_IT.CD_EMP AND
	EST_PROD_CPL.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND
	EST_PROD_CPL.CD_PROD = EST_NF_ENT_IT.CD_PROD 
	LEFT OUTER JOIN V_EST_PROD_PRECO_TBL ON
	EST_PROD_CPL.CD_EMP = V_EST_PROD_PRECO_TBL.CD_EMP AND 
	EST_PROD_CPL.CD_FILIAL = V_EST_PROD_PRECO_TBL.CD_FILIAL AND
	EST_PROD_CPL.CD_PROD = V_EST_PROD_PRECO_TBL.CD_PROD 
WHERE
	EST_PROD_CPL.CD_EMP=@CD_EMP
	AND EST_PROD_CPL.CD_FILIAL=@CD_FILIAL
	AND EST_NF_ENT_IT.DT_ENT >= @DT_PROC_INICIAL
	AND EST_PROD_CPL.CD_PROD = CASE WHEN @CD_PROD_PAR=0 THEN EST_PROD_CPL.CD_PROD ELSE @CD_PROD_PAR END
UNION ALL
 SELECT
   EST_PROD_IMP.CD_EMP,
   EST_PROD_IMP.CD_FILIAL,
   EST_PROD_IMP.CD_PROD,
   EST_PROD_IMP.DT_CAD AS DT_ENT,
   EST_PROD_REQ_TRANSF_LIB_ITENS.VLR_CUSTO AS VLR_CUSTO,
   EST_PROD_CPL.VLR_CUSTO_MD,
   EST_PROD_PRECO.VLR_TABELA,
   EST_ARV_MERC_LINHA.DS_ARV_MERC_LINHA,
   'REQUISICAO DE ENTRADA'
 FROM 
   EST_PROD_IMP INNER JOIN EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP ON
   EST_PROD_IMP.CD_EMP = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_EMP AND
   EST_PROD_IMP.CD_FILIAL = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_FILIAL_DEST AND
   EST_PROD_IMP.CD_IMP = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_IMP AND
   EST_PROD_IMP.CD_PROD = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_PROD
   INNER JOIN EST_PROD_REQ_TRANSF_LIB_ITENS ON
   EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_EMP  = EST_PROD_REQ_TRANSF_LIB_ITENS.CD_EMP AND
   EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_FILIAL_ORIG = EST_PROD_REQ_TRANSF_LIB_ITENS.CD_FILIAL AND
   EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_REQ = EST_PROD_REQ_TRANSF_LIB_ITENS.CD_REQ AND
   EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_PROD = EST_PROD_REQ_TRANSF_LIB_ITENS.CD_PROD
   INNER JOIN EST_PROD_CPL ON
   EST_PROD_IMP.CD_EMP = EST_PROD_CPL.CD_EMP AND
   EST_PROD_IMP.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND
   EST_PROD_IMP.CD_PROD = EST_PROD_CPL.CD_PROD
   INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON
   EST_PROD_CPL.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
   EST_PROD_CPL.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
   INNER JOIN EST_ARV_MERC_LINHA ON
   EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP AND
   EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA
   LEFT OUTER JOIN EST_PROD_PRECO ON  
   EST_PROD_CPL.CD_EMP = EST_PROD_PRECO.CD_EMP AND
   EST_PROD_CPL.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL AND
   EST_PROD_CPL.CD_PROD = EST_PROD_PRECO.CD_PROD 
 WHERE 
  EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_EMP =  @CD_EMP
  AND EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_FILIAL_DEST =  @CD_FILIAL
  AND EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_PROD = CASE WHEN @CD_PROD_PAR=0 THEN EST_PROD_CPL.CD_PROD ELSE @CD_PROD_PAR END
  AND EST_PROD_IMP.DT_CAD >= @DT_PROC_INICIAL
ORDER BY
	CD_PROD,
	DT_ENT;
	
/***********************************************************************************************************/
-- Se a data do inicio da filial for maior que a data de processamento do custo..
/***********************************************************************************************************/
	update p
	set
		DT_MOV = PRC_FILIAL.DT_INI_SISTEMA
	FROM
		#RsProdNotaEntrada P 
		INNER JOIN PRC_FILIAL ON
		p.CD_EMP = PRC_FILIAL.CD_EMP AND
		p.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
	where
		 p.DT_MOV < PRC_FILIAL.DT_INI_SISTEMA;

/***********************************************************************************************************/
-- APLICA GROSSUP NO CUSTO AJUSTADO.
/***********************************************************************************************************/
Set @IsGrossUP = 0
--IF EXISTS (SELECT * FROM PRC_FILIAL_DEPOSITO 
--				WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL)
--    BEGIN
--        IF ISNULL((SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE = 'FLAG_TP_PRECO_CUSTO_RESSUP'), '0') = '16' --TipoPrecoGrossUP
--            BEGIN 
--				Set @IsGrossUP = 1
--                UPDATE #RsProdNotaEntrada
--                  SET
--                      VLR_CUSTO_NOVO = DBO.UFD_BUSCA_PRECO_PRODUTO_GROSS_UP(CD_EMP, @CD_FILIAL, CD_PROD, 4, 2, CD_FILIAL, VLR_CUSTO_NOVO)
--                FROM #RsProdNotaEntrada;
				 
--            END;
--    END; 
/***********************************************************************************************************/

/***********************************************************************************************************/

IF @FLAG_ANALISE=0
begin

    /***********************************************************************************************************/

    /***********************************************************************************************************/
    DECLARE CustoProduto CURSOR FAST_FORWARD FOR 
    SELECT
	    CD_EMP,
	    CD_FILIAL,
	    CD_PROD,
	    DT_MOV,
	    VLR_CUSTO_NOVO
    FROM
	    #RsProdNotaEntrada
    ORDER BY
	    CD_PROD,
	    DT_MOV

    OPEN CustoProduto;
    FETCH NEXT FROM CustoProduto INTO @CD_EMP,@CD_FILIAL,@CD_PROD,@DT_ENT,@VLR_PRECO;
    WHILE @@FETCH_STATUS = 0
	   BEGIN
		  PRINT 'Processando filial.: '+ CONVERT(VARCHAR(10),@CD_FILIAL) + ' produto.: '+ CONVERT(VARCHAR(10),@CD_PROD) + ' data.: ' +CONVERT(VARCHAR, @DT_ENT, 112) + ' ' + CONVERT(VARCHAR, GETDATE (),108);
		    IF @VLR_PRECO>0
		    begin
			    EXEC P_EST_PROD_CUSTO_ACERTO_REPLICACAO @CD_EMP,@CD_FILIAL,@CD_PROD,@DT_ENT,@VLR_PRECO,@CD_USU,0,0,0,@IsGrossUP;
				 
		    end
		    FETCH NEXT FROM CustoProduto INTO @CD_EMP,@CD_FILIAL,@CD_PROD,@DT_ENT,@VLR_PRECO;
	   END;
    CLOSE CustoProduto;
    DEALLOCATE CustoProduto;
    /***********************************************************************************************************/


end
else
begin
	SELECT 
		CD_EMP,
		CD_FILIAL,
		CD_PROD,
		DT_MOV,
		VLR_CUSTO_NOVO,
		VLR_CUSTO_ATUAL,
		VLR_TABELA,
		(VLR_TABELA-VLR_CUSTO_NOVO) AS VLR_MARGEM,
		CASE WHEN (VLR_TABELA) > 0 THEN
			((VLR_TABELA-VLR_CUSTO_NOVO)/VLR_TABELA)*100
		ELSE
			0			
		END AS PERC_MARGEM,
		CASE WHEN ((VLR_TABELA-VLR_CUSTO_NOVO)) > 0 THEN
			(VLR_CUSTO_NOVO/VLR_TABELA)*100
		ELSE
			0			
		END AS PERC_CMV,
		DS_LINHA,
		TIPO_PRECO
	FROM 
		#RsProdNotaEntrada
	ORDER BY
		CD_PROD,
		DT_MOV
end
PRINT 'Concluido.';
DROP TABLE #RsProdNotaEntrada;
end


