IF OBJECT_ID('P_EST_PROD_PRECIFICACAO_INDICADORES') IS NOT NULL
	DROP PROCEDURE [dbo].[P_EST_PROD_PRECIFICACAO_INDICADORES]

GO

CREATE PROCEDURE [dbo].[P_EST_PROD_PRECIFICACAO_INDICADORES] (	@CD_EMP            INT,
																@CD_FILIAL         VARCHAR(2500),
																@DT_INI            DATETIME,
																@DT_FIM            DATETIME,
																@TP_CUSTO          INT,																														
																@CD_ARV_MERC_LINHA VARCHAR(MAX),
																@TP_RELA           INT=0,
																@ST_COMPRADOR	   INT=0)
AS
	BEGIN
 
	/*
	DECLARE @CD_EMP            INT = 1
	DECLARE @CD_FILIAL         VARCHAR(2500) = '1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24,25,28,29,30,31,32,33,34,35,36'
	DECLARE @DT_INI            DATETIME = '10/01/2016'
	DECLARE @DT_FIM            DATETIME = '10/31/2016'
	DECLARE @TP_CUSTO          INT = 0
	DECLARE @CD_FABRIC         VARCHAR(MAX) = ''
	DECLARE @CD_ARV_MERC_LINHA VARCHAR(MAX) = ''
	DECLARE @TP_RELA		   INT = 0
	*/

SET NOCOUNT ON
 
DECLARE @MULTIPLO INT = -1

IF  OBJECT_ID('TEMPDB.DBO.#PRC_FILIAL') IS NOT NULL
	BEGIN
		DROP TABLE #PRC_FILIAL
	END


IF  OBJECT_ID('TEMPDB.DBO.#TBL_VENDA') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_VENDA
	END

IF  OBJECT_ID('TEMPDB.DBO.#TBL_AGENDA') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_AGENDA
	END

 IF  OBJECT_ID('TEMPDB.DBO.#EST_PROD_FABRIC') IS NOT NULL
	BEGIN
		DROP TABLE #EST_PROD_FABRIC
	END
 
IF  OBJECT_ID('TEMPDB.DBO.#EST_ARV_MERC_LINHA') IS NOT NULL
	BEGIN
		DROP TABLE #EST_ARV_MERC_LINHA
	END

IF  OBJECT_ID('TEMPDB.DBO.#TBL_ARV') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_ARV
	END

IF  OBJECT_ID('TEMPDB.DBO.#TBL_VENDA_CUPOM') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_VENDA_CUPOM
	END
 
IF  OBJECT_ID('TEMPDB.DBO.#TBL_PDV_VD') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_PDV_VD
	END


IF  OBJECT_ID('TEMPDB.DBO.#TBL_VD') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_VD
	END

IF  OBJECT_ID('TEMPDB.DBO.#TBL_PDV_VD_IT') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_PDV_VD_IT
	END
 
IF  OBJECT_ID('TEMPDB.DBO.#TBL_RESULT') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_RESULT
	END
 

IF  OBJECT_ID('TEMPDB.DBO.#EST_PRECIFICACAO_GRP_PRECO_FILIAL') IS NOT NULL
	BEGIN
		DROP TABLE #EST_PRECIFICACAO_GRP_PRECO_FILIAL
	END


CREATE TABLE #TBL_VENDA (CD_EMP			INT,
						 CD_FILIAL		INT,
						 CD_PROD		INT,						 
						 VLR_TABELA		MONEY,
						 VLR_CUSTO		MONEY,						 						 
						 MEDIAF			MONEY,
						 QT_IT			MONEY)

 
CREATE TABLE #TBL_AGENDA (CD_CTR_GRUPO		INT,
						 CD_COMPRADOR		INT,
						 NM_GRUPO			VARCHAR(100),
						 CD_EMP				INT,
						 CD_PROD			INT,
						 TP_GRUPO			VARCHAR(100))


CREATE TABLE #EST_PROD_FABRIC  (CD_EMP		INT,
								CD_FABRIC	INT,
								NM_FABRIC	VARCHAR(100))



CREATE TABLE #EST_ARV_MERC_LINHA	(CD_EMP				INT,
									 CD_ARV_MERC_LINHA	INT,
									 DS_ARV_MERC_LINHA	VARCHAR(100))


CREATE TABLE #PRC_FILIAL			(CD_EMP				INT,
									 CD_FILIAL			INT,
									 BAIRRO				VARCHAR(100))


CREATE TABLE #EST_PRECIFICACAO_GRP_PRECO_FILIAL			(CD_EMP				INT,
														 CD_FILIAL			INT,
														 CD_GRUPO			INT,
														 CD_ARV_MERC_DEPTO	INT,
														 PERC_MARGEM		MONEY)


INSERT INTO #PRC_FILIAL
	SELECT
		CD_EMP
		,CD_FILIAL
		,BAIRRO
	FROM
		PRC_FILIAL
	WHERE
		CD_EMP = @CD_EMP AND
		ESPECIE_FILIAL = 1 AND
		CD_FILIAL IN (SELECT
						ID
					  FROM
						UFD_PRM_IN_STRING(@CD_FILIAL));


INSERT INTO #EST_PROD_FABRIC
	SELECT
		CD_EMP
		,CD_FABRIC
		,NM_FABRIC
	FROM
		EST_PROD_FABRIC


INSERT INTO #EST_ARV_MERC_LINHA
	SELECT
		CD_EMP
		,CD_ARV_MERC_LINHA
		,DS_ARV_MERC_LINHA
	FROM
		EST_ARV_MERC_LINHA
	WHERE
		CD_EMP = @CD_EMP 
		AND FLAG_PARTIC_PRECIFICACAO = 1
		AND CD_ARV_MERC_LINHA IN (SELECT
									ID
								  FROM
									UFD_PRM_IN_STRING(@CD_ARV_MERC_LINHA));		


INSERT INTO #EST_PRECIFICACAO_GRP_PRECO_FILIAL 
	SELECT  GRP_FIL.CD_EMP,
			GRP_FIL.CD_FILIAL,
			GRP_FIL.CD_GRUPO,
			MARGEM.CD_ARV_MERC_DEPTO,
			MARGEM.PERC_MARGEM
	FROM 
		EST_PRECIFICACAO_GRP_PRECO_FILIAL GRP_FIL
		INNER JOIN EST_PRECIFICACAO_GRP_PRECO GRP ON 
			GRP.CD_EMP = GRP_FIL.CD_EMP AND
			GRP.CD_GRUPO = GRP_FIL.CD_GRUPO	
		LEFT JOIN EST_PROD_PRECIFICACAO_META_MARGEM_EST_ARV_MERC_DEPTO MARGEM ON 
			GRP.CD_EMP = MARGEM.CD_EMP AND
			GRP.CD_GRUPO = MARGEM.CD_GRUPO	
	WHERE 
		GRP.TIPO_GRUPO = 0


IF @ST_COMPRADOR = 1
	BEGIN
		INSERT INTO #TBL_AGENDA
			select
				G.CD_CTR_GRUPO, G.CD_COMPRADOR, G.NM_GRUPO, P.CD_EMP, P.CD_PROD, 'GRUPO DE COMPRA - FABRICANTE' AS TP_GRUPO 
			from 
				EST_PROD P 
					INNER JOIN EST_PROD_FABRIC_GRUPO_EST_PROD_FABRIC GF ON 
						P.CD_FABRIC = GF.CD_FABRIC
					INNER JOIN EST_PROD_FABRIC_GRUPO G ON 		  
						GF.CD_CTR_GRUPO = G.CD_CTR_GRUPO 
			WHERE 
				G.INDICADOR_SUPRIMENTO = 1
				AND G.STS_GRP_FABRIC = 0
				AND P.COMPRA_POR_SUPRIMENTO IN (1,2)
				AND G.FLAG_GRUPO_POSSUI_PRODUTOS = 0

			UNION 

			select
				G.CD_CTR_GRUPO, G.CD_COMPRADOR, G.NM_GRUPO, P.CD_EMP, P.CD_PROD, 'GRUPO DE COMPRA - PRODUTOS' AS TP_GRUPO
			from 
				EST_PROD P 
					INNER JOIN EST_PROD_FABRIC_GRUPO_EST_PROD GP ON 
						P.CD_EMP = GP.CD_EMP
						AND P.CD_PROD = GP.CD_PROD
					INNER JOIN EST_PROD_FABRIC_GRUPO G ON 		  
						GP.CD_CTR_GRUPO = G.CD_CTR_GRUPO 
			WHERE 
				G.INDICADOR_SUPRIMENTO = 1
				AND G.STS_GRP_FABRIC = 0
				AND P.COMPRA_POR_SUPRIMENTO IN (1,2)
				AND G.FLAG_GRUPO_POSSUI_PRODUTOS = 1

			UNION 

			select
				G.CD_FABRIC, G.CD_COMPRADOR, G.NM_FABRIC, P.CD_EMP, P.CD_PROD, 'FABRICANTE' AS TP_GRUPO
			from 
				EST_PROD P 
					INNER JOIN EST_PROD_FABRIC G ON 
						P.CD_FABRIC = G.CD_FABRIC 
			WHERE 
				G.INDICADOR_SUPRIMENTO = 1
				--AND G.STS_FABRIC <> 1
				AND P.COMPRA_POR_SUPRIMENTO IN (1,2)
				

	END	


	SELECT
		P.CD_EMP
		,P.CD_PROD
		,P.DS_PROD
		,P.FLAG_PRODUTO_GENERICO
		,(CASE P.PIS_COFINS WHEN 0 THEN 'NÃO' ELSE 'SIM' END) AS PIS_COFINS
		,P.FLAG_PRODUTO_CONTROLADO
		,(CASE P.COMPRA_POR_SUPRIMENTO WHEN 0 THEN 'RESSUPRIMENTO' WHEN 1 THEN 'SUPRIMENTO' WHEN 2 THEN 'SUP-RESSUP' END) AS TIPO_COMPRA
		,A.CD_ARV_MERC_LINHA
		,DS_ARV_MERC_CATEG
		,DS_ARV_MERC_DEPTO
		,DS_ARV_MERC_AREA_ATEND
		,DS_ARV_MERC_LINHA
		,DS_ARV_MERC_FAMILIA
		,DS_ARV_MERC_MIX
		,DS_ARV_MERC_MTZ_BCG
		,DS_ARV_MERC_PAPEL
		,DS_ARV_MERC_EQUI
		,DS_ARV_MERC_CONSUMO
		,DS_ARV_MERC_CLASSIFICACAO
		,DS_APRES
		,DS_MC
		,NM_FABRIC
		,(CASE P.LISTA_PROD WHEN 0 THEN 'POSITIVA' WHEN 1 THEN 'NEGATIVA' WHEN 2 THEN 'NEUTRA' ELSE 'CORRELATO' END) AS LISTA_PROD
		,P.PERC_COMIS
		,p.NR_NCM
		,EST_ARV_MERC_DEPTO.CD_ARV_MERC_DEPTO
	INTO   #TBL_ARV
	FROM
		EST_PROD P
		INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON
			P.CD_EMP = A.CD_EMP AND
			P.CD_PROD = A.CD_PROD
		INNER JOIN #EST_ARV_MERC_LINHA L ON
			A.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA AND
			A.CD_EMP = L.CD_EMP
		INNER JOIN EST_ARV_MERC_FAMILIA ON
			A.CD_ARV_MERC_FAMILIA = EST_ARV_MERC_FAMILIA.CD_ARV_MERC_FAMILIA AND
			A.CD_EMP = EST_ARV_MERC_FAMILIA.CD_EMP
		INNER JOIN EST_ARV_MERC_MIX ON
			A.CD_ARV_MERC_MIX = EST_ARV_MERC_MIX.CD_ARV_MERC_MIX AND
			A.CD_EMP = EST_ARV_MERC_MIX.CD_EMP
		INNER JOIN EST_ARV_MERC_MTZ_BCG ON
			A.CD_ARV_MERC_MTZ_BCG = EST_ARV_MERC_MTZ_BCG.CD_ARV_MERC_MTZ_BCG AND
			A.CD_EMP = EST_ARV_MERC_MTZ_BCG.CD_EMP
		INNER JOIN EST_ARV_MERC_PAPEL ON
			A.CD_ARV_MERC_PAPEL = EST_ARV_MERC_PAPEL.CD_ARV_MERC_PAPEL AND
			A.CD_EMP = EST_ARV_MERC_PAPEL.CD_EMP
		INNER JOIN #EST_PROD_FABRIC F ON
			P.CD_FABRIC = F.CD_FABRIC
		INNER JOIN EST_ARV_MERC_CATEGORIA ON
			A.CD_EMP = EST_ARV_MERC_CATEGORIA.CD_EMP AND
			A.CD_ARV_MERC_CATEG = EST_ARV_MERC_CATEGORIA.CD_ARV_MERC_CATEG
		INNER JOIN EST_ARV_MERC_DEPTO ON
			EST_ARV_MERC_CATEGORIA.CD_EMP = EST_ARV_MERC_DEPTO.CD_EMP AND
			EST_ARV_MERC_CATEGORIA.CD_ARV_MERC_DEPTO = EST_ARV_MERC_DEPTO.CD_ARV_MERC_DEPTO
		INNER JOIN EST_ARV_MERC_AREA_ATENDIMENTO ON
			EST_ARV_MERC_DEPTO.CD_EMP = EST_ARV_MERC_AREA_ATENDIMENTO.CD_EMP AND
			EST_ARV_MERC_DEPTO.CD_ARV_MERC_AREA_ATEND = EST_ARV_MERC_AREA_ATENDIMENTO.CD_ARV_MERC_AREA_ATEND
		INNER JOIN EST_ARV_MERC_EQUIVALENTE ON
			A.CD_EMP = EST_ARV_MERC_EQUIVALENTE.CD_EMP AND
			A.CD_ARV_MERC_EQUI = EST_ARV_MERC_EQUIVALENTE.CD_ARV_MERC_EQUI
		INNER JOIN dbo.EST_ARV_MERC_APRESENTACAO ON
			A.CD_EMP = EST_ARV_MERC_APRESENTACAO.CD_EMP AND
			A.CD_APRES = EST_ARV_MERC_APRESENTACAO.CD_APRES
		INNER JOIN dbo.EST_MC ON
			A.CD_EMP = EST_MC.CD_EMP AND
			A.CD_MC = EST_MC.CD_MC
		INNER JOIN dbo.EST_ARV_MERC_CONSUMO ON
			A.CD_EMP = EST_ARV_MERC_CONSUMO.CD_EMP AND
			A.CD_ARV_MERC_CONSUMO = EST_ARV_MERC_CONSUMO.CD_ARV_MERC_CONSUMO
		INNER JOIN dbo.EST_ARV_MERC_CLASSIFICACAO ON
			A.CD_EMP = EST_ARV_MERC_CLASSIFICACAO.CD_EMP AND
			A.CD_ARV_MERC_CLASSIFICACAO = EST_ARV_MERC_CLASSIFICACAO.CD_ARV_MERC_CLASSIFICACAO
	WHERE 
		P.CTR_PMC <> 1;
		

	IF @TP_RELA = 0
		BEGIN
			INSERT INTO #TBL_VENDA 
				SELECT 
					P.CD_EMP, P.CD_FILIAL, P.CD_PROD, P.VLR_TABELA, 
					(CASE WHEN @TP_CUSTO = 1 THEN COALESCE(CPL.VLR_CUSTO_MD, CPL.VLR_CUSTO) ELSE CPL.VLR_CUSTO END) AS VLR_CUSTO, 
					CPL.MEDIAF, ROUND(CPL.MEDIAF,0) AS QT_IT	
				FROM 
					EST_PROD_PRECO P					
					INNER JOIN #PRC_FILIAL PRC_FILIAL ON 
						P.CD_EMP = PRC_FILIAL.CD_EMP  
						AND P.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
					INNER JOIN EST_PROD_CPL CPL ON 
						P.CD_EMP = CPL.CD_EMP
						AND P.CD_PROD = CPL.CD_PROD
						AND P.CD_FILIAL = CPL.CD_FILIAL
				WHERE 
					ROUND(CPL.MEDIAF,0) > 0

			SELECT 
				TBL_ARV.CD_EMP
				,TBL_ARV.CD_PROD
				,TBL_ARV.DS_PROD
				,TBL_ARV.FLAG_PRODUTO_GENERICO
				,TBL_ARV.PIS_COFINS
				,TBL_ARV.FLAG_PRODUTO_CONTROLADO
				,TBL_ARV.TIPO_COMPRA
				,TBL_ARV.CD_ARV_MERC_LINHA
				,TBL_ARV.DS_ARV_MERC_CATEG
				,TBL_ARV.DS_ARV_MERC_DEPTO
				,TBL_ARV.DS_ARV_MERC_AREA_ATEND
				,TBL_ARV.DS_ARV_MERC_LINHA
				,TBL_ARV.DS_ARV_MERC_FAMILIA
				,TBL_ARV.DS_ARV_MERC_MIX
				,TBL_ARV.DS_ARV_MERC_MTZ_BCG
				,TBL_ARV.DS_ARV_MERC_PAPEL
				,TBL_ARV.DS_ARV_MERC_EQUI
				,TBL_ARV.DS_ARV_MERC_CONSUMO
				,TBL_ARV.DS_ARV_MERC_CLASSIFICACAO
				,TBL_ARV.DS_APRES
				,TBL_ARV.DS_MC
				,TBL_ARV.NM_FABRIC
				,TBL_ARV.LISTA_PROD
				,TBL_ARV.PERC_COMIS
				,TBL_ARV.NR_NCM
				,TBL_VENDA.CD_FILIAL						 				
				,(TBL_VENDA.QT_IT * TBL_VENDA.VLR_TABELA) AS VLR_TABELA
				,(TBL_VENDA.QT_IT * TBL_VENDA.VLR_CUSTO) AS VLR_CUSTO	
				,(TBL_VENDA.QT_IT * TBL_VENDA.VLR_TABELA) - (TBL_VENDA.QT_IT * TBL_VENDA.VLR_CUSTO)	AS VLR_MARGEM_BRUTA						
				,TBL_VENDA.MEDIAF
				,TBL_VENDA.QT_IT
				,EST_COMPRADOR.NM_COMPRADOR 
				,TBL_AGENDA.CD_CTR_GRUPO 
				,TBL_AGENDA.NM_GRUPO 
				,TBL_AGENDA.TP_GRUPO
				,ISNULL((TBL_VENDA.QT_IT * TBL_VENDA.VLR_TABELA) * GRP_FIL.PERC_MARGEM / 100,0) AS VLR_META_MARGEM
			FROM #TBL_VENDA TBL_VENDA	
				INNER JOIN #TBL_ARV TBL_ARV ON 
					TBL_VENDA.CD_EMP = TBL_ARV.CD_EMP
					AND TBL_VENDA.CD_PROD = TBL_ARV.CD_PROD
				LEFT JOIN #EST_PRECIFICACAO_GRP_PRECO_FILIAL GRP_FIL ON 
					TBL_VENDA.CD_EMP = GRP_FIL.CD_EMP AND
					TBL_VENDA.CD_FILIAL = GRP_FIL.CD_FILIAL	AND 
					TBL_ARV.CD_ARV_MERC_DEPTO = GRP_FIL.CD_ARV_MERC_DEPTO							
				LEFT JOIN #TBL_AGENDA TBL_AGENDA ON 
					TBL_VENDA.CD_EMP = TBL_AGENDA.CD_EMP
					AND TBL_VENDA.CD_PROD = TBL_AGENDA.CD_PROD 
				LEFT JOIN EST_COMPRADOR ON 
					TBL_AGENDA.CD_COMPRADOR = EST_COMPRADOR.CD_COMPRADOR 

		END

	IF @TP_RELA = 1
		BEGIN
		
			SELECT 
				PDV_VD.CD_EMP,
				PDV_VD.CD_FILIAL,
				PDV_VD.CD_VD
				INTO #TBL_PDV_VD
			FROM PDV_VD
				INNER JOIN #PRC_FILIAL PRC_FILIAL ON 
					PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
					AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
			WHERE			
				PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM AND
				PDV_VD.ST_VD = 0		
		 
			SELECT 
				PDV_VD_IT.*
				INTO #TBL_PDV_VD_IT
			FROM PDV_VD_IT
				INNER JOIN #TBL_PDV_VD PDV_VD ON 
					PDV_VD_IT.CD_EMP = PDV_VD.CD_EMP AND 
					PDV_VD_IT.CD_FILIAL = PDV_VD.CD_FILIAL AND
					PDV_VD_IT.CD_VD = PDV_VD.CD_VD

			DELETE T
			FROM #TBL_PDV_VD_IT T
			WHERE T.ST_IT <> 1	
	

			DELETE T
			FROM #TBL_PDV_VD_IT T
			WHERE NOT EXISTS (SELECT A.CD_EMP 
							  FROM #TBL_ARV A 																		
							  WHERE 
								  T.CD_EMP = A.CD_EMP AND 
									T.CD_PROD = A.CD_PROD)


			SELECT
				PDV_VD_IT.CD_EMP
				,PDV_VD_IT.CD_FILIAL
				,PDV_VD_IT.CD_PROD				
				,PDV_VD_IT.DT_VD											
				,CAST('' AS VARCHAR(50)) AS TIPO_PRODUTO
				,CAST(0 AS MONEY)                                          AS VLR_PMC
				,ROUND(PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT * PDV_VD_IT.TX_DESC / 100, 2) - ISNULL(PDV_VD_IT.VLR_DESC_VERBA, 0)  AS VLR_DESC_ITENS
				,PDV_VD_IT.VLR_DESC_TOT_RAT                              AS VLR_DESC
				,PDV_VD_IT.QT_IT                                         AS QT_IT
				,(CASE WHEN (PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) > 0 THEN 		
								PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT
						ELSE
								0
						END) AS VLR_LIQ_VD
				,PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT                        AS VLR_VD
				,(CASE WHEN ISNULL(PDV_VD_IT.VLR_PMC,0) > PDV_VD_IT.VLR_IT THEN  
							(PDV_VD_IT.VLR_PMC - PDV_VD_IT.VLR_IT) * PDV_VD_IT.QT_IT   
					ELSE  
							0  
					END)													AS VLR_DESC_OFERTA
				,ISNULL(PDV_VD_IT.VLR_DESC_VERBA, 0) AS VLR_DESC_VERBA
				,PDV_VD_IT.TIPO_DESCONTO 
				,(CASE WHEN PDV_VD_IT.TIPO_DESCONTO = 0 THEN   
						'SEM DESCONTO'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 1 THEN   
						'DESCONTO CADASTRO PRODUTO'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 2 THEN   
						'DESCONTO LOJA'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 3 THEN   
						'DESCONTO CONVENIO'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 4 THEN   
						'DESCONTO FIDELIDADE'  
				WHEN PDV_VD_IT.TIPO_DESCONTO = 5 THEN   
						'DESCONTO PBM'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 6 THEN   
						'DESCONTO OFERTA'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 7 THEN   
						'DESCONTO VALOR FIDELIDADE'  
				WHEN PDV_VD_IT.TIPO_DESCONTO = 8 THEN   
						'DESCONTO CUPONAGEM'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 9 THEN   
						'DESCONTO CRM'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 10 THEN   
						'DESCONTO POR TROCA DE PONTOS'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 11 THEN   
						'DESCONTO ANIVERSARI0'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 12 THEN   
						'DESCONTO POR CLASSIFICACAO DE CLIENTE'  
				WHEN PDV_VD_IT.TIPO_DESCONTO = 13 THEN   
						'DESCONTO PRECO DE FABRICA'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 14 THEN   
						'DESCONTO POR QUANTIDADE'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 15 THEN   
						'DESCONTO PROMOCAO NO TELEVENDAS'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 16 THEN   
						'DESCONTO CONVENIO COM RECEITA'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 17 THEN   
						'DESCONTO BRINDE'   
				WHEN PDV_VD_IT.TIPO_DESCONTO = 18 THEN   
						'DESCONTO FORMA DE PAGAMENTO'  
				WHEN PDV_VD_IT.TIPO_DESCONTO = 19 THEN   
								'DESCONTO CREDIARIO'  
				WHEN PDV_VD_IT.TIPO_DESCONTO = 20 THEN   
								'DESCONTO CAMPANHA'  
				ELSE   
						'ERRO SEM CLASSIFICACAO'  
				END) AS DS_TIPO_DESCONTO
				,ISNULL(PDV_VD_IT.VLR_CUSTO, 0)                          AS VLR_CUSTO			
				,CAST('NORMAL' AS VARCHAR(50))                                                 SIT_VD
				,(CASE WHEN (PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) > 0 THEN 		
								PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT
						ELSE
								0
						END) AS TOT_IT
				,ISNULL(PDV_VD_IT.VLR_PMC, 0) * PDV_VD_IT.QT_IT			   AS VLR_PMC_TOTAL
				,ISNULL(PDV_VD_IT.VLR_CUSTO_MEDIO, 0)                    AS VLR_CUSTO_MEDIO				
				,ISNULL(PDV_VD_IT.VLR_CUSTO_GER, 0)                      AS VLR_CUSTO_GER
				,CAST(0 AS MONEY) AS VLR_MARGEM_BRUTA
				INTO   #TBL_VD
			FROM
				#TBL_PDV_VD_IT PDV_VD_IT 		 				


			CREATE INDEX #IX__#TBL_VD_CD_PROD
				ON #TBL_VD (CD_PROD, CD_FILIAL, CD_EMP);

			UPDATE V
				SET    TIPO_PRODUTO =  'SERVICO',
							 VLR_PMC =    V.VLR_PMC_TOTAL,
												 
							 VLR_DESC_OFERTA = V.VLR_DESC_OFERTA,
																  						 
							 VLR_CUSTO = CASE WHEN @TP_CUSTO = 0 THEN
														 V.QT_IT * V.VLR_CUSTO
														 
													 WHEN @TP_CUSTO = 1 THEN
														 V.QT_IT * V.VLR_CUSTO_MEDIO
														 
													 WHEN @TP_CUSTO = 2 THEN
														 V.QT_IT * V.VLR_CUSTO_GER
														 
													 ELSE 0
													 END,
							 VLR_MARGEM_BRUTA = CASE WHEN @TP_CUSTO = 0 THEN
																		V.TOT_IT - ( V.QT_IT * V.VLR_CUSTO )
																		
																	WHEN @TP_CUSTO = 1 THEN
																		V.TOT_IT - ( V.QT_IT * V.VLR_CUSTO_MEDIO )
																		
																	WHEN @TP_CUSTO = 2 THEN
																		V.TOT_IT - ( V.QT_IT * V.VLR_CUSTO_GER )																		
																	ELSE 0
																	END
			FROM   #TBL_VD V						
			

			SELECT
				V.CD_EMP 
				,V.CD_FILIAL							
				,DT_VD
				,TIPO_PRODUTO				
				,V.CD_PROD								
				,VLR_PMC
				,VLR_DESC_ITENS
				,VLR_DESC
				,QT_IT
				,VLR_LIQ_VD
				,VLR_VD
				,VLR_DESC_OFERTA				
				,VLR_DESC_VERBA				
				,DS_TIPO_DESCONTO										
				,VLR_CUSTO
				,VLR_MARGEM_BRUTA				
				,SIT_VD				
				INTO  #TBL_VENDA_CUPOM
			FROM
				#TBL_VD V

			INSERT INTO #TBL_VENDA_CUPOM				
				SELECT
					PDV_VD_DEV_EST_PROD_IMP.CD_EMP 
					,PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL							
					,PDV_VD_DEV_EST_PROD_IMP.DT_DEVO                                                                                                    AS DT_VD
					,CASE WHEN EST_PROD_CPL.TP_PROD = 10 THEN 'SERVICO'
					 ELSE 'REVENDA'
					 END                                                                                                                                AS TIPO_PRODUTO				
					,PDV_VD_DEV_EST_PROD_IMP.CD_PROD				
					,( PDV_VD_DEV_EST_PROD_IMP.VLR_PMC_IT * PDV_VD_DEV_EST_PROD_IMP.QT_IT ) * @MULTIPLO                                                 AS VLR_PMC				
					,( (( PDV_VD_DEV_EST_PROD_IMP.VLR_DESC_IT * PDV_VD_DEV_EST_PROD_IMP.QT_IT )) - PDV_VD_DEV_EST_PROD_IMP.VLR_DESC_VERBA ) * @MULTIPLO AS VLR_DESC_ITENS
					,CAST(0 AS MONEY)                                                                                                                   AS VLR_DESC
					,PDV_VD_DEV_EST_PROD_IMP.QT_IT * @MULTIPLO                                                                                          AS QT_IT
					,( PDV_VD_DEV_EST_PROD_IMP.VLR_IT * PDV_VD_DEV_EST_PROD_IMP.QT_IT ) * @MULTIPLO                                                     AS VLR_LIQ_VD
					,( PDV_VD_DEV_EST_PROD_IMP.VLR_IT_BRUTO * PDV_VD_DEV_EST_PROD_IMP.QT_IT ) * @MULTIPLO                                               AS VLR_VD
					,( CASE WHEN PDV_VD_DEV_EST_PROD_IMP.VLR_PMC_IT > PDV_VD_DEV_EST_PROD_IMP.VLR_IT_BRUTO THEN ( PDV_VD_DEV_EST_PROD_IMP.VLR_PMC_IT - PDV_VD_DEV_EST_PROD_IMP.VLR_IT_BRUTO ) * PDV_VD_DEV_EST_PROD_IMP.QT_IT
						 ELSE 0
						 END ) * @MULTIPLO                                                                                                              AS VLR_DESC_OFERTA
					,ISNULL(PDV_VD_DEV_EST_PROD_IMP.VLR_DESC_VERBA, 0) * @MULTIPLO                                                                      AS VLR_DESC_VERBA
					,CONVERT(VARCHAR(25), CASE WHEN PDV_VD_DEV_EST_PROD_IMP.CD_ORIG_VENDA = 0 THEN 'DEV. DIRETO P.D.V'
																WHEN PDV_VD_DEV_EST_PROD_IMP.CD_ORIG_VENDA = 1 THEN 'DEV. BALCAO'
																WHEN PDV_VD_DEV_EST_PROD_IMP.CD_ORIG_VENDA = 2 THEN 'DEV. TELEVENDAS'
																ELSE 'NAO DEFINIDO'
																END)                                                                                                          AS DS_TIPO_DESCONTO				
					,( PDV_VD_DEV_EST_PROD_IMP.QT_IT * CASE WHEN @TP_CUSTO = 0 THEN PDV_VD_DEV_EST_PROD_IMP.VLR_CUSTO
																						 ELSE PDV_VD_DEV_EST_PROD_IMP.VLR_CUSTO_MEDIO
																						 END ) * @MULTIPLO                                                                                AS VLR_CUSTO
					,( CASE WHEN EST_PROD_CPL.TP_PROD IN ( 10, 11 ) THEN ( ( PDV_VD_DEV_EST_PROD_IMP.VLR_IT * PDV_VD_DEV_EST_PROD_IMP.QT_IT ) * ( EST_PROD_CPL.PERC_COMISSAO_SERVICO / 100 ) )
						 ELSE ( ( PDV_VD_DEV_EST_PROD_IMP.VLR_IT * PDV_VD_DEV_EST_PROD_IMP.QT_IT ) - ( PDV_VD_DEV_EST_PROD_IMP.QT_IT * CASE WHEN @TP_CUSTO = 0 THEN PDV_VD_DEV_EST_PROD_IMP.VLR_CUSTO
																																																													 ELSE PDV_VD_DEV_EST_PROD_IMP.VLR_CUSTO_MEDIO
																																																													 END ) )
						 END ) * @MULTIPLO                                                                                                                AS VLR_MARGEM_BRUTA				
					,CONVERT(VARCHAR(25), CASE WHEN PDV_VD_DEV_EST_PROD_IMP.CD_ORIG_VENDA = 0 THEN 'DEV. DIRETO P.D.V'
																WHEN PDV_VD_DEV_EST_PROD_IMP.CD_ORIG_VENDA = 1 THEN 'DEV. BALCAO'
																WHEN PDV_VD_DEV_EST_PROD_IMP.CD_ORIG_VENDA = 2 THEN 'DEV. TELEVENDAS'
																ELSE 'NAO DEFINIDO'
																END)                                                                                                          AS SIT_VD			
				FROM
					PDV_VD_DEV_EST_PROD_IMP
					INNER JOIN PDV_VD ON
						PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP AND
						PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
						PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD				
					INNER JOIN #PRC_FILIAL F ON
						PDV_VD.CD_EMP = F.CD_EMP AND
						PDV_VD.CD_FILIAL = F.CD_FILIAL
					INNER JOIN EST_PROD_CPL ON
						PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_PROD_CPL.CD_EMP AND
						PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND
						PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_PROD_CPL.CD_PROD

				WHERE
					PDV_VD_DEV_EST_PROD_IMP.CD_EMP = @CD_EMP AND
					PDV_VD_DEV_EST_PROD_IMP.DT_DEVO BETWEEN @DT_INI AND @DT_FIM;
									

			SELECT 	
				TBL_VENDA.CD_EMP
				,TBL_VENDA.CD_PROD						
				,TBL_VENDA.CD_FILIAL
				,TBL_VENDA.DT_VD
				,TBL_VENDA.TIPO_PRODUTO							
				,SUM(TBL_VENDA.VLR_PMC) AS VLR_PMC
				,SUM(TBL_VENDA.VLR_DESC_ITENS) AS VLR_DESC_ITENS
				,SUM(TBL_VENDA.VLR_DESC) AS VLR_DESC
				,SUM(TBL_VENDA.QT_IT) AS QT_IT
				,SUM(TBL_VENDA.VLR_LIQ_VD) AS VLR_LIQ_VD
				,SUM(TBL_VENDA.VLR_VD) AS VLR_VD
				,SUM(TBL_VENDA.VLR_DESC_OFERTA) AS VLR_DESC_OFERTA				
				,SUM(TBL_VENDA.VLR_DESC_VERBA) AS VLR_DESC_VERBA				
				,TBL_VENDA.DS_TIPO_DESCONTO										
				,SUM(TBL_VENDA.VLR_CUSTO) AS VLR_CUSTO
				,SUM(TBL_VENDA.VLR_MARGEM_BRUTA) AS VLR_MARGEM_BRUTA				
				,TBL_VENDA.SIT_VD										
				INTO #TBL_RESULT
			FROM	
				#TBL_VENDA_CUPOM TBL_VENDA	
			
			GROUP BY 					
				TBL_VENDA.CD_EMP
				,TBL_VENDA.CD_PROD
				,TBL_VENDA.CD_FILIAL
				,TBL_VENDA.DT_VD
				,TBL_VENDA.TIPO_PRODUTO										
				,TBL_VENDA.DS_TIPO_DESCONTO													
				,TBL_VENDA.SIT_VD										
			

			SELECT 
				TBL_ARV.CD_EMP
				,TBL_ARV.CD_PROD
				,TBL_ARV.DS_PROD
				,TBL_ARV.FLAG_PRODUTO_GENERICO
				,TBL_ARV.PIS_COFINS
				,TBL_ARV.FLAG_PRODUTO_CONTROLADO
				,TBL_ARV.TIPO_COMPRA
				,TBL_ARV.CD_ARV_MERC_LINHA
				,TBL_ARV.DS_ARV_MERC_CATEG
				,TBL_ARV.DS_ARV_MERC_DEPTO
				,TBL_ARV.DS_ARV_MERC_AREA_ATEND
				,TBL_ARV.DS_ARV_MERC_LINHA
				,TBL_ARV.DS_ARV_MERC_FAMILIA
				,TBL_ARV.DS_ARV_MERC_MIX
				,TBL_ARV.DS_ARV_MERC_MTZ_BCG
				,TBL_ARV.DS_ARV_MERC_PAPEL
				,TBL_ARV.DS_ARV_MERC_EQUI
				,TBL_ARV.DS_ARV_MERC_CONSUMO
				,TBL_ARV.DS_ARV_MERC_CLASSIFICACAO
				,TBL_ARV.DS_APRES
				,TBL_ARV.DS_MC
				,TBL_ARV.NM_FABRIC
				,TBL_ARV.LISTA_PROD
				,TBL_ARV.PERC_COMIS
				,TBL_ARV.NR_NCM	
				,TBL_VENDA.CD_FILIAL								
				,TBL_VENDA.DT_VD
				,TBL_VENDA.TIPO_PRODUTO							
				,TBL_VENDA.VLR_PMC AS VLR_PMC
				,TBL_VENDA.VLR_DESC_ITENS AS VLR_DESC_ITENS
				,TBL_VENDA.VLR_DESC AS VLR_DESC
				,TBL_VENDA.QT_IT AS QT_IT
				,TBL_VENDA.VLR_LIQ_VD AS VLR_LIQ_VD
				,TBL_VENDA.VLR_VD AS VLR_VD
				,TBL_VENDA.VLR_DESC_OFERTA AS VLR_DESC_OFERTA				
				,TBL_VENDA.VLR_DESC_VERBA AS VLR_DESC_VERBA				
				,TBL_VENDA.DS_TIPO_DESCONTO										
				,TBL_VENDA.VLR_CUSTO AS VLR_CUSTO
				,TBL_VENDA.VLR_MARGEM_BRUTA AS VLR_MARGEM_BRUTA				
				,TBL_VENDA.SIT_VD										
				,EST_COMPRADOR.NM_COMPRADOR 
				,TBL_AGENDA.CD_CTR_GRUPO 
				,TBL_AGENDA.NM_GRUPO 
				,TBL_AGENDA.TP_GRUPO 	
				,(TBL_VENDA.VLR_LIQ_VD * ISNULL(GRP_FIL.PERC_MARGEM,0)) /100 AS VLR_META_MARGEM			
			FROM #TBL_RESULT TBL_VENDA	
				INNER JOIN #TBL_ARV TBL_ARV ON 
					TBL_VENDA.CD_EMP = TBL_ARV.CD_EMP
					AND TBL_VENDA.CD_PROD = TBL_ARV.CD_PROD
				LEFT JOIN #EST_PRECIFICACAO_GRP_PRECO_FILIAL GRP_FIL ON 
					TBL_VENDA.CD_EMP = GRP_FIL.CD_EMP AND
					TBL_VENDA.CD_FILIAL = GRP_FIL.CD_FILIAL	AND 
					TBL_ARV.CD_ARV_MERC_DEPTO = GRP_FIL.CD_ARV_MERC_DEPTO
				LEFT JOIN #TBL_AGENDA TBL_AGENDA ON 
					TBL_VENDA.CD_EMP = TBL_AGENDA.CD_EMP
					AND TBL_VENDA.CD_PROD = TBL_AGENDA.CD_PROD 
				LEFT JOIN EST_COMPRADOR ON 
					TBL_AGENDA.CD_COMPRADOR = EST_COMPRADOR.CD_COMPRADOR 
				 
	END		 

	IF  OBJECT_ID('TEMPDB.DBO.#PRC_FILIAL') IS NOT NULL
		BEGIN
			DROP TABLE #PRC_FILIAL
		END


	IF  OBJECT_ID('TEMPDB.DBO.#TBL_VENDA') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_VENDA
		END

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_AGENDA') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_AGENDA
		END

	 IF  OBJECT_ID('TEMPDB.DBO.#EST_PROD_FABRIC') IS NOT NULL
		BEGIN
			DROP TABLE #EST_PROD_FABRIC
		END
 
	IF  OBJECT_ID('TEMPDB.DBO.#EST_ARV_MERC_LINHA') IS NOT NULL
		BEGIN
			DROP TABLE #EST_ARV_MERC_LINHA
		END

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_ARV') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_ARV
		END

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_VENDA_CUPOM') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_VENDA_CUPOM
		END
 
	IF  OBJECT_ID('TEMPDB.DBO.#TBL_PDV_VD') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_PDV_VD
		END


	IF  OBJECT_ID('TEMPDB.DBO.#TBL_VD') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_VD
		END

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_PDV_VD_IT') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_PDV_VD_IT
		END
 
	IF  OBJECT_ID('TEMPDB.DBO.#TBL_RESULT') IS NOT NULL
		BEGIN
			DROP TABLE #TBL_RESULT
		END

END

