IF NOT OBJECT_ID('P_PRECIFICACAO_CONTROLE_ENTRADA') IS NULL
	BEGIN
			DROP PROCEDURE P_PRECIFICACAO_CONTROLE_ENTRADA
	END

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO 

--EXEC P_PRECIFICACAO_CONTROLE_ENTRADA 1,901,1,'12/01/2018','12/21/2018',0,1,1
CREATE PROCEDURE [dbo].[P_PRECIFICACAO_CONTROLE_ENTRADA](@CD_EMP INT,
														@CD_FILIAL INT,
														@CD_GRUPO  INT,
														@DT_INI    DATETIME,
														@DT_FIM    DATETIME,
														@FLAG_VLR_LISTA_MAIOR_ZERO INT=0,
														@FLAG_NAO_PRECIFICAR_PMC   INT=0,
														@CD_USU          INT,
														@DT_INI_PRC    DATETIME=null,
														@DT_FIM_PRC    DATETIME=null)
--WITH ENCRYPTION 
AS
	BEGIN
			SET NOCOUNT ON
			DECLARE @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO INT
			DECLARE @FLAG_USA_APENAS_ADICIONAL_MARKUP INT
			DECLARE @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO_COM_PIS_COFINS INT
			DECLARE @FLAG_TIPO_PRECO INT
			DECLARE @FLAG_FILIAL_OPTANTE_SIMPLES_NACIONAL INT
			DECLARE @ALIQUOTA_SIMPLES_NACIONAL MONEY
			
			SET @FLAG_USA_APENAS_ADICIONAL_MARKUP=0

			BEGIN TRY DROP TABLE #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA END TRY
			 BEGIN CATCH END CATCH;
			CREATE TABLE #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA
				(
					 CD_EMP              INT,
					 CD_GRUPO            INT,
					 CD_PROD             INT,
					 CD_ARV_MERC_FAMILIA INT,
					 VLR_PRC_CUSTO_MEDIA MONEY
				)

			CREATE TABLE #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT(
									   				     CD_EMP INT
														,CD_FILIAL INT
														,CD_NF INT
														,CD_PROD INT
														,CD_GRUPO INT
														,DT_ENT DATETIME
														,DS_PROD VARCHAR(200)
														,CTR_PMC INT
														,CD_FABRIC INT
														,PIS_COFINS INT
														,PERC_MARGEM_FIXA MONEY
														,VLR_PRECO_FIXO MONEY
														,DT_PRECO_FIXO DATETIME
														,CD_ARV_MERC_CATEG INT
														,CD_ARV_MERC_SEG INT
														,CD_ARV_MERC_PAPEL INT
														,CD_ARV_MERC_MIX INT
														,CD_ARV_MERC_CONSUMO INT
														,CD_ARV_MERC_LINHA INT
														,CD_ARV_MERC_MTZ_BCG INT
														,CD_MC INT
														,CD_ARV_MERC_FAMILIA INT
														,CD_ARV_MERC_EQUI INT
														,CD_APRES INT 
														,CD_GRP_EXPO INT
														,CD_ARV_MERC_CLASSIFICACAO INT
														,CD_ARV_MERC_FASE INT
														,CD_ARV_MERC_GENERO INT
														,CD_ARV_MERC_DEPTO INT
														,DS_ARV_MERC_LINHA VARCHAR (200)
														,FLAG_PARTIC_PRECIFICACAO INT
														,VLR_LISTA_COMPRA MONEY)

			CREATE TABLE #EST_PROD (CD_EMP INT,
								    CD_PROD INT,
									DS_PROD VARCHAR(200),
									CTR_PMC INT,
									CD_FABRIC INT,
									PIS_COFINS INT,
									PERC_MARGEM_FIXA MONEY,
									VLR_PRECO_FIXO MONEY,
									DT_PRECO_FIXO DATETIME,
									CD_ARV_MERC_CATEG INT,
									CD_ARV_MERC_SEG INT,
									CD_ARV_MERC_PAPEL INT,
									CD_ARV_MERC_MIX INT,
									CD_ARV_MERC_CONSUMO INT,
									CD_ARV_MERC_LINHA INT,
									CD_ARV_MERC_MTZ_BCG INT,
									CD_MC INT,
									CD_ARV_MERC_FAMILIA INT,
									CD_ARV_MERC_EQUI INT,
									CD_APRES INT,
									CD_GRP_EXPO INT,
									CD_ARV_MERC_CLASSIFICACAO INT,
									CD_ARV_MERC_FASE INT,
									CD_ARV_MERC_GENERO INT,
									CD_ARV_MERC_DEPTO INT,
									DS_ARV_MERC_LINHA VARCHAR (200),
									FLAG_PARTIC_PRECIFICACAO INT,
									VLR_LISTA_COMPRA MONEY)

			CREATE TABLE #EST_PROD_LISTA   (CD_EMP INT,
											CD_PROD INT,		
											VLR_FABRICA MONEY,
											VLR_LISTA_COMPRA MONEY)							
		

						INSERT INTO #EST_PROD
						SELECT
							PROD.CD_EMP,
							PROD.CD_PROD,
							PROD.DS_PROD,
							PROD.CTR_PMC,
							PROD.CD_FABRIC,
							ISNULL(PROD.TIPO_TRIBUTACAO_PIS_COFINS,0) AS PIS_COFINS,
							ISNULL(PROD.PERC_MARGEM_FIXA,0),
							ISNULL(PROD.VLR_PRECO_FIXO,0),
							PROD.DT_PRECO_FIXO,
							ARV.CD_ARV_MERC_CATEG,
							ARV.CD_ARV_MERC_SEG,ARV.CD_ARV_MERC_PAPEL
							,ARV.CD_ARV_MERC_MIX,ARV.CD_ARV_MERC_CONSUMO,
							ARV.CD_ARV_MERC_LINHA
							,ARV.CD_ARV_MERC_MTZ_BCG,
							ARV.CD_MC,ARV.CD_ARV_MERC_FAMILIA
							,ARV.CD_ARV_MERC_EQUI,
							ARV.CD_APRES,ARV.CD_GRP_EXPO
							,ARV.CD_ARV_MERC_CLASSIFICACAO,
							ARV.CD_ARV_MERC_FASE
							,ARV.CD_ARV_MERC_GENERO,
							CTG.CD_ARV_MERC_DEPTO
							,ARV_LINHA.DS_ARV_MERC_LINHA,
							ARV_LINHA.FLAG_PARTIC_PRECIFICACAO, 
							0 AS VLR_LISTA_COMPRA 
						FROM
							EST_PROD PROD 
							INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA AS ARV ON
							PROD.CD_EMP = ARV.CD_EMP AND
							PROD.CD_PROD = ARV.CD_PROD
							
							INNER JOIN dbo.EST_ARV_MERC_LINHA AS ARV_LINHA ON
							ARV.CD_EMP = ARV_LINHA.CD_EMP AND
							ARV.CD_ARV_MERC_LINHA = ARV_LINHA.CD_ARV_MERC_LINHA
							
							INNER JOIN dbo.EST_ARV_MERC_CATEGORIA AS CTG ON
							ARV.CD_EMP = CTG.CD_EMP AND
							ARV.CD_ARV_MERC_CATEG = CTG.CD_ARV_MERC_CATEG AND
							ARV_LINHA.FLAG_PARTIC_PRECIFICACAO = 1
							INNER JOIN GLB_ARV_MERC_FILTROS f ON
							PROD.CD_EMP = f.CD_EMP AND
							PROD.CD_PROD = f.CD_PROD AND
							@CD_USU = f.CD_USU
						WHERE
							PROD.STS_PROD <> 1
							AND PROD.CD_EMP=@CD_EMP 

			 CREATE UNIQUE NONCLUSTERED INDEX #ix_EST_PROD ON #EST_PROD (CD_PROD,CD_EMP);	
			 
			/**************************************************************************************************/				    
			--Aplicando margem fixas
			/**************************************************************************************************/
			UPDATE P
			SET
				PERC_MARGEM_FIXA=CASE WHEN ISNULL(F.PERC_MARGEM_FIXA,0)>0 THEN	 F.PERC_MARGEM_FIXA ELSE P.PERC_MARGEM_FIXA END,
				VLR_PRECO_FIXO= CASE WHEN ISNULL(F.VLR_PRECO_FIXO,0)>0 THEN	F.VLR_PRECO_FIXO ELSE P.VLR_PRECO_FIXO END,
				DT_PRECO_FIXO= f.DT_PRECO_FIXO
			FROM
				#EST_PROD P
				join EST_PROD_PRECIFICACAO F ON 
				P.CD_EMP    = F.CD_EMP AND
				P.CD_PROD   = F.CD_PROD
			where
				f.CD_GRUPO=@CD_GRUPO

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


			/**************************************************************************************************/				    
			--Calculando preco de custo da lista de compra
			/**************************************************************************************************/
		   INSERT INTO #EST_PROD_LISTA	
				SELECT 
					P.CD_EMP,
					P.CD_PROD,
					ROUND(ISNULL(F.VLR_FABRICA, 0) - (ISNULL(F.VLR_FABRICA, 0) * (ISNULL(F.PERC_DESC_FABRICA, 0) / 100)),2) AS VLR_FABRICA,
					0 AS VLR_LISTA_COMPRA
				FROM
					#EST_PROD P
						join EST_PROD_PRECIFICACAO_LISTA_PRECO_COMPRA F ON 
					P.CD_EMP    = F.CD_EMP AND
					P.CD_PROD   = F.CD_PROD
				WHERE
					F.DT_VALID 	>= CONVERT(VARCHAR(10),GETDATE(),101) 
		
			IF EXISTS (SELECT TOP 1 * FROM #EST_PROD_LISTA)
				BEGIN
					UPDATE L SET 
						VLR_LISTA_COMPRA = ROUND(UFD.VLR_CUSTO, 2)
					FROM #EST_PROD_LISTA L
						OUTER APPLY UFD_EST_PROD_TRIB(L.CD_EMP, @CD_FILIAL, 0, @CD_FILIAL, L.CD_PROD, 0, 0, 0, 1, L.VLR_FABRICA, 0,0,0,0,0,0) UFD


					UPDATE P SET	   				
						VLR_LISTA_COMPRA	= L.VLR_LISTA_COMPRA
					FROM
						#EST_PROD P
						join #EST_PROD_LISTA L ON 
						P.CD_EMP    = L.CD_EMP AND
						P.CD_PROD   = L.CD_PROD	   
		  		END
		  
			/**************************************************************************************************/

			 --Retirar produto com controle PMC
			 IF @FLAG_NAO_PRECIFICAR_PMC=1
			 BEGIN
				DELETE FROM #EST_PROD WHERE CTR_PMC=1
			 END

			SET @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO = ISNULL((SELECT ABS(DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO', 0, 0))), 0)
			SET @FLAG_TIPO_PRECO = ISNULL((SELECT ABS(DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'TIPO_PRECO_CUSTO_PRECIFICACAO', 0, 0))), 0)
			SET @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO_COM_PIS_COFINS = ISNULL((SELECT ABS(DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'NAO_ABATE_PIS_COFINS_PRECIFICACAO', 0, 0))), 0)
			SET @FLAG_FILIAL_OPTANTE_SIMPLES_NACIONAL = ISNULL((SELECT ABS(DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'FILIAL_OPTANTE_SIMPLES_NACIONAL', 1, @CD_FILIAL))), 0)
			SET @ALIQUOTA_SIMPLES_NACIONAL = ISNULL((SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'ALIQUOTA_SIMPLES_NACIONAL', 1, @CD_FILIAL)), '0')
			SET @FLAG_USA_APENAS_ADICIONAL_MARKUP=ISNULL((SELECT ABS(DBO.UFD_BUSCA_PARAM_SISTEMA(@CD_EMP, 'FLAG_USA_APENAS_ADICIONAL_MARKUP', 0, 0))), 0)

			IF @FLAG_TIPO_PRECO=1
			BEGIN
				SET @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO=1
			END

			IF ISNUMERIC(@ALIQUOTA_SIMPLES_NACIONAL) = 0
				BEGIN
					SET @ALIQUOTA_SIMPLES_NACIONAL = 0
				END
			-- Print convert(varchar,getdate(),108);
			  
			---------------------------------------------------------------------------------------------------------------------------------
			--DADOS DA ULTIMA ENTRADA
			---------------------------------------------------------------------------------------------------------------------------------
			INSERT INTO #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT
			SELECT
				ENT.CD_EMP
				,ENT.CD_FILIAL
				,ENT.CD_NF
				,ENT.CD_PROD
				,ENT.CD_GRUPO
				,NF_ENT.DT_ENT
				,PROD.DS_PROD
				,PROD.CTR_PMC
				,PROD.CD_FABRIC
				,ISNULL(PROD.PIS_COFINS,0)
				,PROD.PERC_MARGEM_FIXA
				,PROD.VLR_PRECO_FIXO
				,PROD.DT_PRECO_FIXO
				,PROD.CD_ARV_MERC_CATEG
				,PROD.CD_ARV_MERC_SEG
				,PROD.CD_ARV_MERC_PAPEL
				,PROD.CD_ARV_MERC_MIX
				,PROD.CD_ARV_MERC_CONSUMO
				,PROD.CD_ARV_MERC_LINHA
				,PROD.CD_ARV_MERC_MTZ_BCG
				,PROD.CD_MC
				,PROD.CD_ARV_MERC_FAMILIA
				,PROD.CD_ARV_MERC_EQUI
				,PROD.CD_APRES
				,PROD.CD_GRP_EXPO
				,PROD.CD_ARV_MERC_CLASSIFICACAO
				,PROD.CD_ARV_MERC_FASE
				,PROD.CD_ARV_MERC_GENERO
				,PROD.CD_ARV_MERC_DEPTO
				,PROD.DS_ARV_MERC_LINHA
				,PROD.FLAG_PARTIC_PRECIFICACAO
				,PROD.VLR_LISTA_COMPRA
			FROM
				EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT AS ENT
				INNER JOIN EST_NF_ENT NF_ENT ON
				ENT.CD_EMP = NF_ENT.CD_EMP AND
				ENT.CD_FILIAL = NF_ENT.CD_FILIAL AND
				ENT.CD_NF = NF_ENT.CD_NF
				INNER JOIN #EST_PROD PROD on
				ENT.CD_EMP = PROD.CD_EMP AND
				ENT.CD_PROD = PROD.CD_PROD
			WHERE
				NF_ENT.CD_EMP    = @CD_EMP AND
				NF_ENT.DT_ENT BETWEEN @DT_INI AND @DT_FIM AND
				ENT.CD_GRUPO  = @CD_GRUPO 
				
			  
---------------------------------------------------------------------------------------------------------------------------------
--FAZENDO A MEDIA DO CUSTO DAS NOTA DO PERIODO 
---------------------------------------------------------------------------------------------------------------------------------			
	   --Insere a media da familia  
	   INSERT INTO #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA
	   SELECT
		  G.CD_EMP
		  ,G.CD_GRUPO  
		  , 0 CD_PROD
		  ,ARV.CD_ARV_MERC_FAMILIA
		  ,CASE WHEN @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO_COM_PIS_COFINS = 0 THEN 
				    CASE WHEN @FLAG_TIPO_PRECO=0 THEN
						  ( SUM(I.VLR_CUSTO_MD * I.QT_IT) / SUM(I.QT_IT) )
						WHEN @FLAG_TIPO_PRECO=1 THEN
						  CASE WHEN AVG(ISNULL(I.VLR_CUSTO_GER,0)) = 0 THEN
							( SUM(I.VLR_PRC_CUSTO * I.QT_IT) / SUM(I.QT_IT) )	
						  ELSE
							( SUM(I.VLR_CUSTO_GER * I.QT_IT) / SUM(I.QT_IT) )	
						  END
						WHEN @FLAG_TIPO_PRECO=2 THEN
						  (SUM(I.VLR_TOT_IT-I.VLR_DESC) / SUM(I.QT_IT))
				    END
			 ELSE ( SUM(I.VLR_PRC_CUSTO * I.QT_IT)
						  + SUM(ISNULL(VLR_PIS, 0))
						  + SUM(ISNULL(VLR_COFINS, 0)) ) / SUM(I.QT_IT)
			 END     AS VLR_PRC_CUSTO_MEDIA
	   FROM
		  EST_NF_ENT E
		  INNER JOIN EST_NF_ENT_IT I ON
				E.CD_EMP = I.CD_EMP AND
				E.CD_FILIAL = I.CD_FILIAL AND
				E.CD_NF = I.CD_NF AND
				E.DT_ENT = I.DT_ENT
		  INNER JOIN GLB_MOV_OP M ON
				E.CD_NT_MOV = M.CD_MOV_OP
		  INNER JOIN EST_PRECIFICACAO_GRP_PRECO_FILIAL G ON
				E.CD_EMP = G.CD_EMP AND
				E.CD_FILIAL = G.CD_FILIAL

		  LEFT JOIN PG_FORN F ON
				E.CD_EMP = F.CD_EMP AND
				E.CD_FORN = F.CD_FORN

		  INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
				I.CD_EMP = ARV.CD_EMP AND
				I.CD_PROD = ARV.CD_PROD 
	   WHERE
		  e.CD_EMP           = @CD_EMP AND
		  e.DT_ENT BETWEEN @DT_INI AND @DT_FIM AND
		  ( CASE WHEN M.DEST_ORIG = 1 THEN ISNULL(F.PROC_PRECIFICACAO, 0)
				ELSE 1
				END )               = 1 AND
		  M.TP_MOV_OP_TRANSA = 0 AND
		  M.TP_MOV           = 0 AND
		  G.CD_GRUPO         = @CD_GRUPO AND
		  I.QT_IT > 0
	   GROUP  BY
		  G.CD_EMP
		  ,G.CD_GRUPO	
		  ,ARV.CD_ARV_MERC_FAMILIA;    
		   
	   --Insere a media por produto
	   INSERT INTO #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA
	   SELECT
		  G.CD_EMP
		  ,G.CD_GRUPO
		  , i.CD_PROD
		  , ARV.CD_ARV_MERC_FAMILIA 
		  ,CASE WHEN @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO_COM_PIS_COFINS = 0 THEN 
				    CASE WHEN @FLAG_TIPO_PRECO=0 THEN
						  ( SUM(I.VLR_PRC_CUSTO * I.QT_IT) / SUM(I.QT_IT) )
						 WHEN @FLAG_TIPO_PRECO=1 THEN
							CASE WHEN AVG(ISNULL(I.VLR_CUSTO_GER,0)) = 0 THEN
								( SUM(I.VLR_PRC_CUSTO * I.QT_IT) / SUM(I.QT_IT) )	
							ELSE
								( SUM(I.VLR_CUSTO_GER * I.QT_IT) / SUM(I.QT_IT) )	
							END
						 WHEN @FLAG_TIPO_PRECO=2 THEN
							(SUM(I.VLR_TOT_IT-I.VLR_DESC) / SUM(I.QT_IT))
				    END
			 ELSE ( SUM(I.VLR_PRC_CUSTO * I.QT_IT)
						  + SUM(ISNULL(VLR_PIS, 0))
						  + SUM(ISNULL(VLR_COFINS, 0)) ) / SUM(I.QT_IT)
			 END     AS VLR_PRC_CUSTO_MEDIA
	   FROM
		  EST_NF_ENT E
		  INNER JOIN EST_NF_ENT_IT I ON
				E.CD_EMP = I.CD_EMP AND
				E.CD_FILIAL = I.CD_FILIAL AND
				E.CD_NF = I.CD_NF AND
				E.DT_ENT = I.DT_ENT
		  INNER JOIN GLB_MOV_OP M ON
				E.CD_NT_MOV = M.CD_MOV_OP
		  INNER JOIN EST_PRECIFICACAO_GRP_PRECO_FILIAL G ON
				E.CD_EMP = G.CD_EMP AND
				E.CD_FILIAL = G.CD_FILIAL
		  LEFT JOIN PG_FORN F ON
				E.CD_EMP = F.CD_EMP AND
				E.CD_FORN = F.CD_FORN
		  INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
				I.CD_EMP = ARV.CD_EMP AND
				I.CD_PROD = ARV.CD_PROD			 
	   WHERE
		  i.CD_EMP           = @CD_EMP AND
		  i.DT_ENT BETWEEN @DT_INI AND @DT_FIM AND
		  ( CASE WHEN M.DEST_ORIG = 1 THEN ISNULL(F.PROC_PRECIFICACAO, 0)
				ELSE 1
				END )               = 1 AND
		  M.TP_MOV_OP_TRANSA = 0 AND
		  M.TP_MOV           = 0 AND
		  G.CD_GRUPO         = @CD_GRUPO AND
		  I.QT_IT > 0
	   GROUP  BY
		  G.CD_EMP
		  ,G.CD_GRUPO
		  , i.CD_PROD
		  , ARV.CD_ARV_MERC_FAMILIA;

	   --Atualiza para a media de preco das familias 
	   UPDATE	
			B
	   SET
		  VLR_PRC_CUSTO_MEDIA= A.VLR_PRC_CUSTO_MEDIA
	   FROM 
		  #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA A
		  JOIN #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA  B ON 
		  A.CD_ARV_MERC_FAMILIA = B.CD_ARV_MERC_FAMILIA
	   WHERE
		  A.CD_ARV_MERC_FAMILIA > 0
		  AND A.CD_PROD = 0
		  AND B.CD_PROD > 0;

	   --Remove a media das familias temporarias
	   DELETE FROM #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA
	   WHERE CD_PROD = 0;
---------------------------------------------------------------------------------------------------------------------------------	
---------------------------------------------------------------------------------------------------------------------------------	
---------------------------------------------------------------------------------------------------------------------------------	

		  IF NOT @DT_FIM_PRC IS NULL
		  BEGIN
			 DELETE FROM ENT
			 FROM
				#EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT AS ENT
				INNER JOIN dbo.EST_PROD_PRECO AS PRC ON
				ENT.CD_EMP = PRC.CD_EMP AND
				ENT.CD_FILIAL = PRC.CD_FILIAL AND
				ENT.CD_PROD = PRC.CD_PROD
				AND PRC.DT_CAD between @DT_INI_PRC AND @DT_FIM_PRC
		  END

			SELECT DISTINCT
				ENT.CD_EMP
				,ENT.CD_GRUPO
				,IT.CD_PROD
				,ENT.DS_PROD
				,ENT.CD_FILIAL
				,NF.DT_ENT
				,CONVERT(VARCHAR, ENT.CD_FILIAL) + ' - ' + CONVERT(VARCHAR, NF.NR_NF) AS NR_NF
				,COALESCE (( CASE WHEN FRN.CD_FORN IS NULL THEN IT.VLR_TOT_IT / IT.QT_IT
										 ELSE OPL.VLR_ITEM
										 END ), 0)                                                     AS VLR_COMPRA
				,COALESCE (( CASE WHEN FRN.CD_FORN IS NULL THEN 
								0
								ELSE 
									CASE WHEN TRIB.ICMS = 1 THEN 
										OPL.VLR_ITEM + ( OPL.VLR_COMPRA * OPL.PERC_IPI / 100 ) - ( (OPL.VLR_ITEM * OPL.ALIQ_ICMS_COMPRA) / 100 )
									ELSE 
										OPL.VLR_ITEM + ( ( ( OPL.VLR_COMPRA + ( OPL.VLR_COMPRA * OPL.PERC_IPI / 100 ) + ( ( OPL.VLR_COMPRA + ( OPL.VLR_COMPRA * OPL.PERC_IPI / 100 ) ) * CPL.VLR_PAUTA_IVA / 100 ) ) * CPL.PERC_ICMS_EST / 100 ) - ( OPL.VLR_ITEM * OPL.ALIQ_ICMS_COMPRA / 100 ) )
									END
							END ), 0) AS SIMULADO_COF
				,( CASE WHEN @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO = 0 THEN
						 CASE WHEN @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO_COM_PIS_COFINS = 0 THEN 
							CASE WHEN @FLAG_TIPO_PRECO=0 THEN
									IT.VLR_PRC_CUSTO
							WHEN @FLAG_TIPO_PRECO=2 THEN
									(IT.VLR_TOT_IT-IT.VLR_DESC) / IT.QT_IT
							END
						 ELSE 
							IT.VLR_PRC_CUSTO + ( ( ISNULL(IT.VLR_PIS, 0) + ISNULL(IT.VLR_COFINS, 0) ) / IT.QT_IT )
						 END
					 ELSE
						 CASE WHEN @FLAG_UTILIZA_CUSTO_GERENCIAL_PRECIFICACAO_COM_PIS_COFINS = 0 THEN 
							isnull(IT.VLR_CUSTO_GER,IT.VLR_PRC_CUSTO)
						 ELSE 
							ISNULL(IT.VLR_CUSTO_GER, IT.VLR_PRC_CUSTO) + ( ( ISNULL(VLR_PIS, 0) + ISNULL(VLR_COFINS, 0) ) / IT.QT_IT )
						 END
					 END ) AS VLR_PRC_CUSTO
				,COALESCE (PRC.VLR_LISTA, 0) AS VLR_LISTA
				
				,COALESCE (VLR.VLR_TABELA,PRC.VLR_BASE,0)	AS VLR_BASE
				,CASE WHEN @FLAG_USA_APENAS_ADICIONAL_MARKUP=0 THEN
					COALESCE (( CASE WHEN ENT.PIS_COFINS = 1 THEN 0
					 ELSE 
						  CASE WHEN FLL.TP_REGIME_APURAC_PIS_COFINS=0 THEN
								(select 
								    sum(case when tipo_imposto in (1,2) then perc_imp 
										  else 0 end) perc_pis_cofins_nao_cumulativo 
								 from 
								    GLB_IMPOSTOS
								WHERE
    									ENT.PIS_COFINS               = 0 
									AND FLAG_NAO_COMPOR_PRECIFICACAO = 0)

						     WHEN FLL.TP_REGIME_APURAC_PIS_COFINS=1 THEN
								(select  
								    sum(case when tipo_imposto in (32,33) then perc_imp 
								    else 0 end) perc_pis_cofins_nao_cumulativo
								from 
								    GLB_IMPOSTOS
								WHERE
								    ENT.PIS_COFINS               = 0 
								    AND FLAG_NAO_COMPOR_PRECIFICACAO = 0)
							ELSE 0 
						  END
					 END ),0) 
					ELSE
						0
					END AS PIS_COFINS
				,( CASE WHEN @FLAG_USA_APENAS_ADICIONAL_MARKUP=0 THEN
						CASE WHEN @FLAG_FILIAL_OPTANTE_SIMPLES_NACIONAL = 0 THEN 
							ISNULL(CPL.PERC_ICMS_EST, 0)
						ELSE 
							@ALIQUOTA_SIMPLES_NACIONAL
						END
					ELSE
						0
					END) AS ICMS_VENDA
				,PPL.DS_SIGLA
				,ENT.CD_FABRIC
				,ENT.CD_ARV_MERC_FAMILIA
				,ENT.CD_ARV_MERC_CATEG
				,ENT.CD_ARV_MERC_DEPTO
				,ARV_FAMILIA.DS_ARV_MERC_FAMILIA
				,ISNULL(( CASE WHEN COALESCE(ENT.PERC_MARGEM_FIXA, 0) = 0 THEN MRG.PERC_MARGEM_MIN
									ELSE ENT.PERC_MARGEM_FIXA
									END ), 0)                                                        AS MMIN
				,ISNULL(( CASE WHEN COALESCE(ENT.PERC_MARGEM_FIXA, 0) = 0 THEN MRG.PERC_MARGEM_MAX
									ELSE ENT.PERC_MARGEM_FIXA
									END ), 0)                                                        AS MMAX
				,PPL.FORMULA_PRECIFICACAO
				,COALESCE (ENT.VLR_PRECO_FIXO, 0)                                          AS VLR_PRECO_FIXO
				,COALESCE (ENT.DT_PRECO_FIXO, 0)                                           AS DT_PRECO_FIXO
				,COALESCE ((SELECT
											SUM(A.VLR_VERBA) AS VERBA
										FROM
											dbo.EST_PROD_PRECIFICACAO_VERBAS_IT AS A
											INNER JOIN dbo.EST_PROD_PRECIFICACAO_VERBAS AS B ON
												A.CD_EMP = B.CD_EMP AND
												A.CD_VERBA = B.CD_VERBA
										WHERE
										 ( A.CD_EMP = ENT.CD_EMP ) AND
										 ( A.CD_PROD = ENT.CD_PROD ) AND
										 ( A.STS_VERBA = 0 ) AND
										 ( CONVERT(CHAR(10),GETDATE(),112) BETWEEN B.DT_INI_VERBA AND B.DT_VENC_VERBA )), 0) AS VERBA
				,(SELECT
						COUNT(A.CD_TABLOIDE) AS TABLOIDE
					FROM
						dbo.EST_PROD_TABLOIDE_CPL AS A
						INNER JOIN dbo.EST_PROD_TABLOIDE AS B ON
							A.CD_EMP = B.CD_EMP AND
							A.CD_TABLOIDE = B.CD_TABLOIDE
					WHERE
					 ( A.CD_EMP = ENT.CD_EMP ) AND
					 ( A.CD_PROD = ENT.CD_PROD ) AND
					 ( CONVERT(CHAR(10),GETDATE(),112) BETWEEN B.DT_INICIO AND B.DT_FIM ))  AS TABLOIDE
				,COALESCE (BLQ.CD_PROD, 0)                                                 AS BLOQUEIO
				,PRC.DT_PRECO_BASE
				,ISNULL(GRP_PRECO_MEDIA.VLR_PRC_CUSTO_MEDIA, 0)                            AS VLR_PRC_CUSTO_MEDIA
				,ISNULL(TBL_ADIC_MARKUP.PERC_ADIC_MARKUP, 0)                               AS PERC_ADIC_MARKUP
				,ISNULL(ENT.VLR_LISTA_COMPRA, 0)                               			   AS VLR_LISTA_COMPRA

				,CASE WHEN ROUND(ISNULL(VLR_LISTA, 0), 2)> 0 THEN
					CASE WHEN VLR_TABELA = 0 THEN
						0
					ELSE
						CASE WHEN ISNULL(VLR.VLR_TABELA,0) + ISNULL(PRC.VLR_BASE,0)>0 THEN
							(COALESCE (VLR.VLR_TABELA,PRC.VLR_BASE,0)-ROUND(ISNULL(VLR_LISTA, 0), 2))/ROUND(COALESCE (VLR.VLR_TABELA,PRC.VLR_BASE,0), 2) * 100
						ELSE
							0
						END
					END
				ELSE
					0
				END as PERC_MARGEM_ATUAL
				,PPR.DS_ARV_MERC_PAPEL
				,DS_ARV_MERC_DEPTO
				,DS_ARV_MERC_CATEG
				,NF.CD_NF
			FROM
				#EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT AS ENT
				INNER JOIN dbo.EST_NF_ENT AS NF ON
					ENT.CD_EMP = NF.CD_EMP AND
					ENT.CD_FILIAL = NF.CD_FILIAL AND
					ENT.CD_NF = NF.CD_NF
				INNER JOIN dbo.EST_ARV_MERC_FAMILIA AS ARV_FAMILIA ON
					ENT.CD_EMP = ARV_FAMILIA.CD_EMP AND
					ENT.CD_ARV_MERC_FAMILIA = ARV_FAMILIA.CD_ARV_MERC_FAMILIA
				INNER JOIN dbo.EST_PROD_PRECO AS VLR ON
					ENT.CD_EMP = VLR.CD_EMP AND
					VLR.CD_FILIAL =@CD_FILIAL AND
					ENT.CD_PROD = VLR.CD_PROD
				LEFT JOIN dbo.EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS AS MRG ON
					ENT.CD_EMP = MRG.CD_EMP AND
					ENT.CD_GRUPO = MRG.CD_GRUPO AND
					ENT.CD_ARV_MERC_CATEG = MRG.CD_ARV_MERC_CATEG
				INNER JOIN dbo.EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA AS PPL ON
					ENT.CD_EMP = PPL.CD_EMP AND
					ENT.CD_ARV_MERC_PAPEL = PPL.CD_ARV_MERC_PAPEL
				INNER JOIN dbo.EST_ARV_MERC_PAPEL AS PPR ON
					ENT.CD_EMP = PPR.CD_EMP AND
					ENT.CD_ARV_MERC_PAPEL = PPR.CD_ARV_MERC_PAPEL

				INNER JOIN dbo.EST_ARV_MERC_CATEGORIA AS CAT ON
					ENT.CD_EMP = CAT.CD_EMP AND
					ENT.CD_ARV_MERC_CATEG = CAT.CD_ARV_MERC_CATEG

				INNER JOIN dbo.EST_ARV_MERC_DEPTO AS DP ON
					ENT.CD_EMP = DP.CD_EMP AND
					ENT.CD_ARV_MERC_DEPTO = DP.CD_ARV_MERC_DEPTO

				LEFT OUTER JOIN dbo.EST_PROD_PRECIFICACAO AS PRF ON
					ENT.CD_EMP = PRF.CD_EMP AND
					ENT.CD_GRUPO = PRF.CD_GRUPO AND
					ENT.CD_PROD = PRF.CD_PROD
				LEFT OUTER JOIN dbo.EST_PROD_PRECIFICACAO_PRECO AS PRC ON
					ENT.CD_EMP = PRC.CD_EMP AND
					ENT.CD_GRUPO = PRC.CD_GRUPO AND
					ENT.CD_PROD = PRC.CD_PROD
				INNER JOIN dbo.EST_NF_ENT_IT AS IT ON
					NF.CD_EMP = IT.CD_EMP AND
					NF.CD_FILIAL = IT.CD_FILIAL AND
					NF.CD_NF = IT.CD_NF AND
					NF.DT_ENT = IT.DT_ENT AND
					ENT.CD_PROD = IT.CD_PROD
				INNER JOIN dbo.EST_TRIB AS TRIB ON
					IT.CD_TRIB = TRIB.CD_TRIB
				LEFT OUTER JOIN (SELECT
													 A.CD_EMP
													 ,A.CD_FILIAL
													 ,A.CD_FORN
												 FROM
													 dbo.PRC_FILIAL_PG_FORN AS A
													 INNER JOIN dbo.PRC_FILIAL AS B ON
														 A.CD_EMP = B.CD_EMP AND
														 A.CD_FILIAL = B.CD_FILIAL
												 WHERE
													( B.FLAG_OPERADOR_LOG = 1 )) AS FRN ON
					NF.CD_EMP = FRN.CD_EMP AND
					NF.CD_FORN = FRN.CD_FORN
				LEFT OUTER JOIN (SELECT
													 A.CD_EMP
													 ,A.CD_FILIAL
													 ,A.CD_PROD
													 ,C.VLR_IT               AS VLR_COMPRA
													 ,C.VLR_TOT_IT / C.QT_IT AS VLR_ITEM
													 ,C.ALIQ_ICMS + COALESCE(PERC_FECOP, 0) AS ALIQ_ICMS_COMPRA
													 ,C.PERC_IPI
												 FROM
													 dbo.EST_PROD_ENT_OPERADOR_LOG AS A
													 INNER JOIN dbo.EST_NF_ENT AS B ON
														 A.CD_EMP = B.CD_EMP AND
														 A.CD_FILIAL = B.CD_FILIAL AND
														 A.CD_NF = B.CD_NF
													 INNER JOIN dbo.EST_NF_ENT_IT AS C ON
														 A.CD_EMP = C.CD_EMP AND
														 A.CD_FILIAL = C.CD_FILIAL AND
														 A.CD_NF = C.CD_NF AND
														 A.CD_PROD = C.CD_PROD
												 WHERE
													B.CD_EMP = @CD_EMP AND
													B.DT_ENT BETWEEN @DT_INI AND @DT_FIM
													AND c.QT_IT > 0 ) AS OPL ON
					FRN.CD_EMP = OPL.CD_EMP AND
					FRN.CD_FILIAL = OPL.CD_FILIAL AND
					ENT.CD_PROD = OPL.CD_PROD
				LEFT OUTER JOIN (SELECT	
									A.CD_EMP
									,A.CD_FILIAL
									,A.CD_PROD
									,MAX(CASE WHEN A.TP_TRANSA = 0 THEN 
											A.VLR_PAUTA_IVA
										ELSE 
											0
										END) AS VLR_PAUTA_IVA
										,MAX(CASE WHEN A.TP_TRANSA = 1 THEN 
											( CASE WHEN ( ISNULL(B.SUBS_TRIB_ANT, 0)
												+ ISNULL(B.SUBS_TRIB, 0) + ISNULL(B.ISENTO, 0) ) > 0 THEN 0
												ELSE 
													CONVERT(MONEY, (A.PERC_ICMS_EST + COALESCE(A.PERC_FECOP, 0)) * ( CASE WHEN A.PERC_RED > 0 THEN ( A.PERC_RED / 100.0000 )
												ELSE 
													1
												END ))
											END )
											ELSE 
												0
											END) AS PERC_ICMS_EST
											FROM
												EST_PROD_CPL_TRIB_EST AS A
												INNER JOIN EST_TRIB AS B ON
												A.CD_TRIB = B.CD_TRIB
											WHERE
												A.TP_MOV = 0
											GROUP  BY
													A.CD_EMP
													,A.CD_FILIAL
													,A.CD_PROD) AS CPL ON
					IT.CD_EMP = CPL.CD_EMP AND
					IT.CD_FILIAL = CPL.CD_FILIAL AND
					IT.CD_PROD = CPL.CD_PROD
				LEFT OUTER JOIN dbo.EST_PROD_PRECIFICACAO_BLOQUEIOS AS BLQ ON
					ENT.CD_EMP = BLQ.CD_EMP AND
					ENT.CD_GRUPO = BLQ.CD_GRUPO AND
					ENT.CD_PROD = BLQ.CD_PROD
				INNER JOIN PRC_FILIAL AS FLL ON
					ENT.CD_EMP = FLL.CD_EMP AND
					ENT.CD_FILIAL = FLL.CD_FILIAL
				LEFT OUTER JOIN (SELECT
													 CD_EMP
													 ,VALOR
												 FROM
													 PRC_EMP_CONFIG
												 WHERE
													CD_CHAVE = 'PRECIFICACAO_SOMENTE_MTZ_OL') AS CFG ON
					ENT.CD_EMP = CFG.CD_EMP
				INNER JOIN #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA GRP_PRECO_MEDIA ON
					ENT.CD_EMP	= GRP_PRECO_MEDIA.CD_EMP AND
					ENT.CD_GRUPO	= GRP_PRECO_MEDIA.CD_GRUPO AND
					ENT.CD_PROD	= GRP_PRECO_MEDIA.CD_PROD
				LEFT JOIN V_EST_PRECIFICACAO_GRP_PRECO_ADIC_MARKUP TBL_ADIC_MARKUP ON
					ENT.CD_EMP = TBL_ADIC_MARKUP.CD_EMP AND
					ENT.CD_GRUPO = TBL_ADIC_MARKUP.CD_GRUPO
			WHERE
				NOT ( FLL.FILIAL_MTZ               = 0 AND
							FLL.FLAG_OPERADOR_LOG        = 0 AND
							COALESCE(CFG.VALOR, 0)        = 1 ) AND
				ROUND(ISNULL(VLR_LISTA, 0), 2) > ( CASE WHEN @FLAG_VLR_LISTA_MAIOR_ZERO = 1 THEN 0
																					 ELSE -1
																					 END ) 
				AND IT.QT_IT > 0
				
				
				
	DROP TABLE #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT
	DROP TABLE #EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT_MEDIA
	DROP TABLE #EST_PROD
	DROP TABLE #EST_PROD_LISTA
	END