IF EXISTS(SELECT * FROM SYS.VIEWS
	WHERE  NAME = 'V_DW_EST_PRECIFICACAO' )
	BEGIN
		DROP VIEW
			 V_DW_EST_PRECIFICACAO;
	END;
GO

CREATE VIEW [dbo].[V_DW_EST_PRECIFICACAO]
AS
	 SELECT
			CD_EMP
		  , CD_GRUPO
		  , DS_GRUPO
		  , CD_PROD
		  , DS_PROD
		  , CD_FABRIC
		  , NM_FABRIC
		  , VLR_TABELA
		  , VLR_TABELA AS VLR_VENDA
		  , ICMS_VENDA
		  , VALOR_VENDA
		  , CD_ARV_MERC_PAPEL
		  , DS_ARV_MERC_PAPEL
		  , DS_SIGLA
		  , DS_ARV_MERC_MTZ_BCG
		  , CD_ARV_MERC_CATEG
		  , DS_ARV_MERC_CATEG
		  , DS_ARV_MERC_MIX
		  , CD_ARV_MERC_LINHA
		  , DS_ARV_MERC_LINHA
		  , CD_ARV_MERC_FAMILIA
		  , DS_ARV_MERC_FAMILIA
		  , CD_ARV_MERC_DEPTO
		  , DS_ARV_MERC_DEPTO
		  , DS_ARV_MERC_AREA_ATEND
		  , DS_ARV_MERC_CONSUMO
		  , CTR_PMC
		  , VLR_CUSTO_GRP
		  , QTDE_MEDIAF
		  , QT_VD_ATUAL
		  , QT_VD_MES1
		  , QT_VD_MES2
		  , QT_VD_MES3
		  , QT_MEDIA_TRIMESTRE
		  , PERC_IPI_COMPRA
		  , ALIQ_ICMS_COMPRA
		  , VLR_PROD_LIQ_COMPRA
		  , VLR_PROD_BRUTO_COMPRA
		  , PERC_IVA_COMPRA
		  , SUBST
		  , VLR_BASE_ICMS_COMPRA
		  , VLR_ICMS_IT_COMPRA
		  , VLR_BASE_SUBS_COMPRA
		  , VLR_ICMS_SUBS_COMPRA
		  , VLR_DESC_COMPRA
		  , QT_IT_COMPRA
		  , NR_NF
		  , CD_FORN
		  , FORNECEDOR
		  , DAR
		  , VLR_VERBA
		  , CUSTO_ATUAL
		  , MARG_APLIC
		  , VLR_PRECO_FIXO
		  , DT_PRECO_FIXO
		  , TX_VARIACAO
		  , MARG_MIN
		  , MARG_MAX
		  , ((VLR_LISTA * MARG_MIN) / 100) AS MMIN
		  , ((VLR_LISTA * MARG_MAX) / 100) AS MMAX
		  , (CUSTO_ATUAL * QT_VD_ATUAL) AS CUSTO_SICMS_ATUAL
		  , (CUSTO_ATUAL * QT_MEDIA_TRIMESTRE) AS CUSTO_SICMS_TRIM
		  , (VLR_TABELA * QT_MEDIA_TRIMESTRE) AS VENDA_CICMS_TRIM
		  , (VALOR_VENDA * QT_MEDIA_TRIMESTRE) AS VENDA_SICMS_TRIM
		  , (VLR_TABELA * QT_VD_ATUAL) AS VENDA_CICMS_ATUAL
		  , (VALOR_VENDA * QT_VD_ATUAL) AS VENDA_SICMS_ATUAL
		  , ((VALOR_VENDA * QT_VD_ATUAL) - (CUSTO_ATUAL * QT_VD_ATUAL)) AS MB_CICMS_ATUAL
		  , ((VALOR_VENDA * QT_MEDIA_TRIMESTRE) - (CUSTO_ATUAL * QT_MEDIA_TRIMESTRE)) AS MB_CICMS_TRIM
		  , PRECO_FAMILIA
		  , STS_PRECO_UNICO
		  , FORMULA_PRECIFICACAO
		  , BLOQUEADO
		  , VENCIMENTO_BLOQUEIO
		  , QT_TABLOIDE
		  , VLR_LISTA
		  , VLR_BASE
		  , DT_PRECO_BASE
	 FROM
(
	SELECT
		   PROD.CD_EMP
		 , PROD.CD_PROD
		 , PROD.DS_PROD
		 , (CASE
				WHEN PROD.CTR_PMC > 0 THEN 'SIM'
				ELSE 'NAO'
			END) AS CTR_PMC
		 , PROD.VLR_CUSTO_GRP
		 , CTG.CD_ARV_MERC_CATEG
		 , CTG.DS_ARV_MERC_CATEG
		 , DPT.CD_ARV_MERC_DEPTO
		 , DPT.DS_ARV_MERC_DEPTO
		 , FML.CD_ARV_MERC_FAMILIA
		 , FML.DS_ARV_MERC_FAMILIA
		 , PRCU.VLR_UNICO AS PRECO_FAMILIA
		 , PRCU.STS_PRECO_UNICO
		 , PPL.CD_ARV_MERC_PAPEL
		 , PPL.DS_ARV_MERC_PAPEL
		 , FRM.DS_SIGLA
		 , FRM.TX_VARIACAO
		 , FRM.FORMULA_PRECIFICACAO
		 , ATD.DS_ARV_MERC_AREA_ATEND
		 , LNH.CD_ARV_MERC_LINHA
		 , LNH.DS_ARV_MERC_LINHA
		 , CSM.DS_ARV_MERC_CONSUMO
		 , MIX.DS_ARV_MERC_MIX
		 , BCG.DS_ARV_MERC_MTZ_BCG
		 , FBR.CD_FABRIC
		 , FBR.NM_FABRIC
		 , COALESCE(NF.NR_NF, 0) AS NR_NF
		 , COALESCE(NF.CD_FORN, 0) AS CD_FORN
		 , COALESCE(FORN.NM_FANT, '') AS FORNECEDOR
		 , COALESCE(IT.PERC_IPI, 0) AS PERC_IPI_COMPRA
		 , COALESCE(IT.ALIQ_ICMS, 0) AS ALIQ_ICMS_COMPRA
		 , COALESCE(IT.VLR_TOT_IT / IT.QT_IT, 0) AS VLR_PROD_LIQ_COMPRA
		 , COALESCE(IT.VLR_IT, 0) AS VLR_PROD_BRUTO_COMPRA
		 , COALESCE(IT.VLR_BASE_ICMS, 0) AS VLR_BASE_ICMS_COMPRA
		 , COALESCE(IT.VLR_ICMS_IT, 0) AS VLR_ICMS_IT_COMPRA
		 , COALESCE(IT.VLR_DESC, 0) AS VLR_DESC_COMPRA
		 , COALESCE(IT.QT_IT, 0) AS QT_IT_COMPRA
		 , (CASE
				WHEN COALESCE(IT.VLR_BASE_ICMS, 0) > 0 THEN 'NAO'
				ELSE 'SIM'
			END) AS SUBST
		 , (CASE
				WHEN COALESCE(DAR.PERC_IVA, 0) > 0 THEN DAR.PERC_IVA
				ELSE COALESCE(IT.PERC_IVA, 0)
			END) AS PERC_IVA_COMPRA
		 , (CASE
				WHEN COALESCE(DAR.VLR_BASE_SUBS, 0) > 0 THEN DAR.VLR_BASE_SUBS
				ELSE COALESCE(IT.VLR_BASE_SUBS, 0)
			END) AS VLR_BASE_SUBS_COMPRA
		 , (CASE
				WHEN COALESCE(DAR.VLR_ICMS_SUBS, 0) > 0 THEN DAR.VLR_ICMS_SUBS
				ELSE COALESCE(IT.VLR_ICMS_SUBS, 0)
			END) AS VLR_ICMS_SUBS_COMPRA
		 , (CASE
				WHEN COALESCE(DAR.VLR_BASE_SUBS, 0) > 0 THEN 'SIM'
				ELSE 'NAO'
			END) AS DAR
		 , VRB.VLR_VERBA
		 , PROD.VLR_CUSTO_GRP - COALESCE(VRB.VLR_VERBA, 0) AS CUSTO_ATUAL
		 , TRIB.ICMS_VENDA
		 , SUM(MDF.QTDE_MEDIAF) AS QTDE_MEDIAF
		 , SUM(MDF.QT_VD_MES1) AS QT_VD_MES1
		 , SUM(MDF.QT_VD_MES2) AS QT_VD_MES2
		 , SUM(MDF.QT_VD_MES3) AS QT_VD_MES3
		 , SUM((MDF.QT_VD_MES1 + MDF.QT_VD_MES2 + MDF.QT_VD_MES3) / 3) AS QT_MEDIA_TRIMESTRE
		 , GRP.CD_GRUPO
		 , GRP.DS_GRUPO
		 , MRG.PERC_MARGEM_MIN AS MARG_MIN
		 , MRG.PERC_MARGEM_MAX AS MARG_MAX
		 , PRC.VLR_BASE AS VLR_TABELA
		 , PRC.VLR_LISTA
		 , PRC.VLR_BASE
		 , PRC.DT_PRECO_BASE
		 , (CASE
				WHEN COALESCE(DAR.VLR_BASE_SUBS, 0) > 0 THEN PRC.VLR_BASE
				ELSE PRC.VLR_BASE - (PRC.VLR_BASE * (TRIB.ICMS_VENDA / 100))
			END) AS VALOR_VENDA
		 , PRF.PERC_MARGEM_FIXA AS MARG_APLIC
		 , PRF.VLR_PRECO_FIXO
		 , PRF.DT_PRECO_FIXO
		 , (CASE
				WHEN COALESCE(BLQ.CD_PROD, 0) > 0 THEN 'S'
				ELSE 'N'
			END) AS BLOQUEADO
		 , BLQ.VENCIMENTO_BLOQUEIO
		 , ( SELECT
						COUNT(I.CD_PROD) AS EXPR1
				FROM
					DBO.EST_PROD_TABLOIDE_CPL AS I
					INNER JOIN DBO.EST_PROD_TABLOIDE AS T ON I.CD_EMP = T.CD_EMP
																AND I.CD_TABLOIDE = T.CD_TABLOIDE
				WHERE  (T.DT_FIM <= GETDATE())
						AND (I.CD_EMP = PROD.CD_EMP)
						AND (I.CD_PROD = PROD.CD_PROD)
			) AS QT_TABLOIDE
		 , SUM(COALESCE(VD.QT_VD, 0)) AS QT_VD_ATUAL
	FROM
		EST_PROD AS 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 EST_ARV_MERC_CATEGORIA AS CTG ON ARV.CD_EMP = CTG.CD_EMP
													AND ARV.CD_ARV_MERC_CATEG = CTG.CD_ARV_MERC_CATEG
		INNER JOIN EST_ARV_MERC_DEPTO AS DPT ON CTG.CD_EMP = DPT.CD_EMP
												AND CTG.CD_ARV_MERC_DEPTO = DPT.CD_ARV_MERC_DEPTO
		INNER JOIN EST_ARV_MERC_FAMILIA AS FML ON ARV.CD_EMP = FML.CD_EMP
												  AND ARV.CD_ARV_MERC_FAMILIA = FML.CD_ARV_MERC_FAMILIA
		LEFT OUTER JOIN EST_ARV_MERC_FAMILIA_PRECIFICACAO_PRECO AS PRCU ON ARV.CD_EMP = PRCU.CD_EMP
																		   AND ARV.CD_ARV_MERC_FAMILIA = PRCU.CD_ARV_MERC_FAMILIA
		INNER JOIN EST_ARV_MERC_PAPEL AS PPL ON ARV.CD_EMP = PPL.CD_EMP
												AND ARV.CD_ARV_MERC_PAPEL = PPL.CD_ARV_MERC_PAPEL
		INNER JOIN EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA AS FRM ON ARV.CD_EMP = FRM.CD_EMP
																	 AND ARV.CD_ARV_MERC_PAPEL = FRM.CD_ARV_MERC_PAPEL
		INNER JOIN EST_ARV_MERC_AREA_ATENDIMENTO AS ATD ON DPT.CD_EMP = ATD.CD_EMP
														   AND DPT.CD_ARV_MERC_AREA_ATEND = ATD.CD_ARV_MERC_AREA_ATEND
		INNER JOIN EST_ARV_MERC_LINHA AS LNH ON ARV.CD_EMP = LNH.CD_EMP
												AND ARV.CD_ARV_MERC_LINHA = LNH.CD_ARV_MERC_LINHA
		INNER JOIN EST_ARV_MERC_CONSUMO AS CSM ON ARV.CD_EMP = CSM.CD_EMP
												  AND ARV.CD_ARV_MERC_CONSUMO = CSM.CD_ARV_MERC_CONSUMO
		INNER JOIN EST_ARV_MERC_MIX AS MIX ON ARV.CD_EMP = MIX.CD_EMP
											  AND ARV.CD_ARV_MERC_MIX = MIX.CD_ARV_MERC_MIX
		INNER JOIN EST_ARV_MERC_MTZ_BCG AS BCG ON ARV.CD_EMP = BCG.CD_EMP
												  AND ARV.CD_ARV_MERC_MTZ_BCG = BCG.CD_ARV_MERC_MTZ_BCG
		INNER JOIN EST_PROD_FABRIC AS FBR ON PROD.CD_FABRIC = FBR.CD_FABRIC    
        
    
  /*----------------------------------------------------------------------------*/
               
		LEFT OUTER JOIN
		(
			SELECT
				   IT.CD_EMP
				 , IT.CD_PROD
				 , SUM(IT.VLR_VERBA) AS VLR_VERBA
			FROM
				EST_PROD_PRECIFICACAO_VERBAS_IT AS IT
				INNER JOIN EST_PROD_PRECIFICACAO_VERBAS AS VRB ON IT.CD_EMP = VRB.CD_EMP
																  AND IT.CD_VERBA = VRB.CD_VERBA
			WHERE  IT.STS_VERBA = 0
				   AND VRB.DT_VENC_VERBA >= GETDATE()
			GROUP BY
					 IT.CD_EMP
				   , IT.CD_PROD
		) AS VRB ON PROD.CD_EMP = VRB.CD_EMP
					AND PROD.CD_PROD = VRB.CD_PROD    
    
  /*----------------------------------------------------------------------------*/
               
		INNER JOIN
		(
			SELECT
				   A.CD_EMP
				 , A.CD_PROD
				 , 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 A.PERC_ICMS_EST
													 END)
						   ELSE 0
					   END) AS ICMS_VENDA
			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
				   AND A.CD_FILIAL =
		(
			SELECT
				   CD_FILIAL
			FROM
				PRC_FILIAL
			WHERE  FILIAL_MTZ = 1
		)
			GROUP BY
					 A.CD_EMP
				   , A.CD_PROD
		) AS TRIB ON PROD.CD_EMP = TRIB.CD_EMP
					 AND PROD.CD_PROD = TRIB.CD_PROD    
    
  /*----------------------------------------------------------------------------*/
               
		INNER JOIN EST_PROD_CPL AS CPL ON PROD.CD_EMP = CPL.CD_EMP
										  AND PROD.CD_PROD = CPL.CD_PROD
										  AND CPL.STS_PROD = 0    

	
		--		 AND CPL.CD_FILIAL = ENT.CD_FIL_GRP
  /*----------------------------------------------------------------------------*/

-- Ult entrada grupo


		LEFT OUTER JOIN
		(
			SELECT
				   a.CD_EMP
				 , a.CD_FILIAL
				 , a.CD_NF
				 , a.CD_PROD
				 , a.DT_HR_CAD_ENTRADA
				 , c.cd_filial CD_FIL_GRP
			FROM
				EST_PROD_ENT_GRP_ECON_EST_NF_ENT_IT a
				JOIN PRC_GRP_ECON_PRC_FILIAL B ON A.CD_EMP = B.CD_EMP
												  AND A.CD_FILIAL = B.CD_FILIAL
	 			JOIN (SELECT
						   F.CD_EMP
						 , B.CD_GRP_ECON
						 , F.CD_FILIAL
					FROM
						 PRC_FILIAL F
						 JOIN PRC_GRP_ECON_PRC_FILIAL B ON F.CD_EMP = B.CD_EMP
														   AND F.CD_FILIAL = B.CD_FILIAL
					WHERE F.FILIAL_MTZ_GRP_ECON = 1) c ON b.CD_EMP = c.CD_EMP  AND 
														  b.CD_GRP_ECON = c.CD_GRP_ECON

	
		) ENT ON	 PROD.CD_EMP   = ENT.CD_EMP
				 AND PROD.CD_PROD  = ENT.CD_PROD


		LEFT OUTER JOIN 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

		LEFT OUTER JOIN PG_FORN AS FORN ON NF.CD_EMP = FORN.CD_EMP
										   AND NF.CD_FORN = FORN.CD_FORN
		LEFT OUTER JOIN EST_NF_ENT_IT AS IT ON ENT.CD_EMP = IT.CD_EMP
											   AND ENT.CD_FILIAL = IT.CD_FILIAL
											   AND ENT.CD_NF = IT.CD_NF
											   AND ENT.CD_PROD = IT.CD_PROD
		LEFT OUTER JOIN EST_NF_ENT_DAR_IT AS DAR ON IT.CD_EMP = DAR.CD_EMP
													AND IT.CD_FILIAL = DAR.CD_FILIAL
													AND IT.CD_NF = DAR.CD_NF
													AND IT.CD_PROD = DAR.CD_PROD 
	
  /*----------------------------------------------------------------------------*/ 

    
		INNER JOIN EST_PROD_CPL_MEDIAF AS MDF ON CPL.CD_EMP = MDF.CD_EMP
												 AND CPL.CD_FILIAL = MDF.CD_FILIAL
												 AND CPL.CD_PROD = MDF.CD_PROD
		INNER JOIN EST_PRECIFICACAO_GRP_PRECO_FILIAL AS GRPF ON CPL.CD_EMP = GRPF.CD_EMP
																AND CPL.CD_FILIAL = GRPF.CD_FILIAL
		INNER JOIN EST_PRECIFICACAO_GRP_PRECO AS GRP ON GRPF.CD_EMP = GRP.CD_EMP
														AND GRPF.CD_GRUPO = GRP.CD_GRUPO
		LEFT OUTER JOIN EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS AS MRG ON ARV.CD_EMP = MRG.CD_EMP
																			  AND ARV.CD_ARV_MERC_CATEG = MRG.CD_ARV_MERC_CATEG
																			  AND MRG.CD_GRUPO = GRP.CD_GRUPO
		LEFT OUTER JOIN EST_PROD_PRECIFICACAO_PRECO AS PRC ON PROD.CD_EMP = PRC.CD_EMP
															  AND PROD.CD_PROD = PRC.CD_PROD
															  AND PRC.CD_GRUPO = GRP.CD_GRUPO
		LEFT OUTER JOIN EST_PROD_PRECIFICACAO AS PRF ON PROD.CD_EMP = PRF.CD_EMP
														AND PROD.CD_PROD = PRF.CD_PROD
														AND PRF.CD_GRUPO = GRP.CD_GRUPO
		LEFT OUTER JOIN EST_PROD_PRECIFICACAO_BLOQUEIOS AS BLQ ON PROD.CD_EMP = BLQ.CD_EMP
																  AND PROD.CD_PROD = BLQ.CD_PROD
																  AND BLQ.CD_GRUPO = GRP.CD_GRUPO    
    
  /*----------------------------------------------------------------------------*/
             
		LEFT OUTER JOIN
		( SELECT
				   IT.CD_EMP
				 , IT.CD_FILIAL
				 , IT.CD_PROD
				 , SUM(IT.QT_IT) AS QT_VD
			FROM
				PDV_VD AS VD
				INNER   JOIN PDV_VD_IT AS IT ON VD.CD_VD = IT.CD_VD
												   AND VD.CD_FILIAL = IT.CD_FILIAL
												   AND VD.CD_EMP	= IT.CD_EMP
												   AND VD.DT_VD		= IT.DT_VD
			WHERE  
				IT.CD_EMP = 1
				AND IT.DT_VD BETWEEN CONVERT(DATE, DATEADD(DAY, DATEPART(DAY,GETDATE()-1)*-1,GETDATE())) AND CONVERT(DATE, GETDATE())
				AND VD.ST_VD =0 --retira as vendas cancelada    
				AND IT.ST_IT =1 --retira as vendas cancelada    
			GROUP BY
					 IT.CD_EMP
				   , IT.CD_FILIAL
				   , IT.CD_PROD
		) AS VD ON CPL.CD_EMP = VD.CD_EMP
				   AND CPL.CD_FILIAL = VD.CD_FILIAL
				   AND CPL.CD_PROD = VD.CD_PROD    
    
  /*----------------------------------------------------------------------------*/
     
	WHERE PROD.CD_EMP = 1
		  AND PROD.STS_PROD = 0
	GROUP BY
			 PROD.CD_EMP
		   , PROD.CD_PROD
		   , PROD.DS_PROD
		   , PROD.CTR_PMC
		   , PROD.VLR_CUSTO_GRP
		   , CTG.CD_ARV_MERC_CATEG
		   , CTG.DS_ARV_MERC_CATEG
		   , DPT.CD_ARV_MERC_DEPTO
		   , DPT.DS_ARV_MERC_DEPTO
		   , FML.CD_ARV_MERC_FAMILIA
		   , FML.DS_ARV_MERC_FAMILIA
		   , PRCU.VLR_UNICO
		   , PRCU.STS_PRECO_UNICO
		   , PPL.CD_ARV_MERC_PAPEL
		   , PPL.DS_ARV_MERC_PAPEL
		   , FRM.DS_SIGLA
		   , FRM.TX_VARIACAO
		   , FRM.FORMULA_PRECIFICACAO
		   , ATD.DS_ARV_MERC_AREA_ATEND
		   , LNH.CD_ARV_MERC_LINHA
		   , LNH.DS_ARV_MERC_LINHA
		   , CSM.DS_ARV_MERC_CONSUMO
		   , MIX.DS_ARV_MERC_MIX
		   , BCG.DS_ARV_MERC_MTZ_BCG
		   , FBR.CD_FABRIC
		   , FBR.NM_FABRIC
		   , NF.NR_NF
		   , NF.CD_FORN
		   , FORN.NM_FANT
		   , IT.PERC_IPI
		   , IT.ALIQ_ICMS
		   , IT.VLR_TOT_IT
		   , IT.QT_IT
		   , IT.VLR_IT
		   , IT.VLR_BASE_ICMS
		   , IT.VLR_ICMS_IT
		   , IT.VLR_DESC
		   , IT.QT_IT
		   , IT.VLR_BASE_ICMS
		   , DAR.PERC_IVA
		   , IT.PERC_IVA
		   , DAR.VLR_BASE_SUBS
		   , IT.VLR_BASE_SUBS
		   , DAR.VLR_ICMS_SUBS
		   , IT.VLR_ICMS_SUBS
		   , DAR.VLR_BASE_SUBS
		   , VRB.VLR_VERBA
		   , TRIB.ICMS_VENDA
		   , GRP.CD_GRUPO
		   , GRP.DS_GRUPO
		   , MRG.PERC_MARGEM_MIN
		   , MRG.PERC_MARGEM_MAX
		   , PRC.VLR_BASE
		   , PRC.VLR_LISTA
		   , PRC.VLR_BASE
		   , PRC.DT_PRECO_BASE
		   , PRF.PERC_MARGEM_FIXA
		   , PRF.VLR_PRECO_FIXO
		   , PRF.DT_PRECO_FIXO
		   , BLQ.CD_PROD
		   , BLQ.VENCIMENTO_BLOQUEIO    
   --, VD.QT_VD    
) AS TMP;
GO


