IF  EXISTS (
 SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_CPL_ARV_MERCADOLOGICA]'))
DROP VIEW [dbo].[V_EST_PROD_CPL_ARV_MERCADOLOGICA]
GO
GO
GO

CREATE VIEW [dbo].[V_EST_PROD_CPL_ARV_MERCADOLOGICA]
AS
SELECT    
	P.CD_EMP, 
	CPL.CD_FILIAL, 
	P.CD_PROD, 
	P.DS_PROD, 
	FAB.CD_FABRIC, 
	FAB.NM_FABRIC, 
	CAT.CD_ARV_MERC_CATEG, 
	CAT.DS_ARV_MERC_CATEG, 
    D.CD_ARV_MERC_DEPTO, 
	D.DS_ARV_MERC_DEPTO, 
	AA.CD_ARV_MERC_AREA_ATEND, 
	AA.DS_ARV_MERC_AREA_ATEND, 
	S.CD_ARV_MERC_SEG, 
    S.DS_ARV_MERC_SEG, 
	L.CD_ARV_MERC_LINHA, 
	L.DS_ARV_MERC_LINHA, 
	L.DS_SIGLA_LINHA, 
	C.CD_ARV_MERC_CONSUMO, 
    C.DS_ARV_MERC_CONSUMO, 
	M.CD_ARV_MERC_MIX, 
	M.DS_ARV_MERC_MIX, 
	PA.CD_ARV_MERC_PAPEL, 
	PA.DS_ARV_MERC_PAPEL, 
    F.CD_ARV_MERC_FAMILIA, 
	F.DS_ARV_MERC_FAMILIA, 
	MTZ.CD_ARV_MERC_MTZ_BCG, 
	MTZ.DS_ARV_MERC_MTZ_BCG, 
	P.CD_UN_VD, 
	P.STS_PROD, 
    P.LISTA_PROD, 
	CASE P.LISTA_PROD WHEN 0 THEN 'POSITIVA' WHEN 1 THEN 'NEGATIVA' WHEN 2 THEN 'NEUTRA' END AS LISTA, 
	P.CTR_PMC, 
    P.COMPRIMENTO, 
	P.LARGURA, 
	P.ALTURA, 
	P.PERC_DESC_FIDEL DESC_FIDEL_PROD, 
	P.PERC_DESC_FIDEL_MAX DESC_FIDEL_MAX_PROD, 
    CASE WHEN P.COMPRA_POR_SUPRIMENTO = 0 THEN 'RESSUPRIMENTO' ELSE 'SUPRIMENTO' END AS TIPO_COMPRA, 
	P.VLR_COMPRA_GRP, 
    P.PROD_USO_CONT,
	CPL.QT_EST,
	CPL.MEDIAF,
	CPL.EMAX,
	CPL.ESEG,
	CPL.PERC_DESC_FIDEL,
	CPL.PERC_DESC_FIDEL_MAX,
	CONVERT(MONEY,(CPL.MEDIAF/30.) * CPL.EMAX) EMAX_QTDE,
	CONVERT(MONEY,(CPL.MEDIAF/30.) * CPL.ESEG) ESEG_QTDE,
	CPL.QTDE_FACEAMENTO,
	CPL.QTDE_FINANCIADO,
	CPL.QTDE_LIMITADOR,
	CPL.VLR_CUSTO ,
	CURVA.DS_SIGLA,
	CPL.PRAT,
	CPL.GAV,
	P.QTDE_CAIXA,
	P.PERC_DESC_FIX,
	CPL.QT_EST_EM_TERC,
	CPL.CD_SIGLA,
	CPL.TP_PROD,
	ISNULL(PRC.VLR_TABELA,0)VLR_TABELA,
	CPL.QT_EST_DEPOSITOS,
	CASE WHEN FAB.PARAM_EST <=0 THEN 10 ELSE FAB.PARAM_EST END  PARAM_EST,
	CPL.STS_PROD STS_PROD_LJ,
	CPL.STS_PROD STS_PROD_CPL,
	MC.CD_MC, 
	MC.DS_MC, 
	EQ.CD_ARV_MERC_EQUI,
	EQ.DS_ARV_MERC_EQUI,
	AP.CD_APRES,
	AP.DS_APRES,
	CPL.QT_EST_DE_TERC,
    ISNULL(EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS.PERC_MARGEM_MAX,0) AS PERC_MARGEM_MAX, 
    ISNULL(EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS.PERC_MARGEM_MIN,0) AS PERC_MARGEM_MIN,
	ISNULL(EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA.TX_VARIACAO,0) AS  TX_VARIACAO,
    ISNULL(EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA.FORMULA_PRECIFICACAO,'') AS FORMULA_PRECIFICACAO,
	p.VLR_CUSTO_GRP,
	p.PERC_DESC,
	PRC.VLR_OFERTA,
	CPL.VLR_CUSTO_MD
FROM          
	DBO.EST_PROD AS P 

	INNER JOIN DBO.EST_PROD_CPL AS CPL ON 
	P.CD_EMP = CPL.CD_EMP AND 
	P.CD_PROD = CPL.CD_PROD 

	INNER JOIN DBO.EST_PROD_CURVA_SIGLA AS CURVA ON 
	CPL.CD_EMP = CURVA.CD_EMP AND 
	CPL.CD_SIGLA = CURVA.CD_SIGLA

	INNER JOIN DBO.EST_PROD_FABRIC AS FAB ON 
	P.CD_FABRIC = FAB.CD_FABRIC 

	INNER JOIN DBO.EST_PROD_EST_ARV_MERCADOLOGICA AS ARV ON 
	P.CD_EMP = ARV.CD_EMP AND 
	P.CD_PROD = ARV.CD_PROD 

	INNER JOIN DBO.EST_ARV_MERC_CATEGORIA AS CAT ON 
	ARV.CD_EMP = CAT.CD_EMP AND 
	ARV.CD_ARV_MERC_CATEG = CAT.CD_ARV_MERC_CATEG 

	INNER JOIN DBO.EST_ARV_MERC_DEPTO AS D ON 
	CAT.CD_EMP = D.CD_EMP AND 
	CAT.CD_ARV_MERC_DEPTO = D.CD_ARV_MERC_DEPTO 

	INNER JOIN DBO.EST_ARV_MERC_AREA_ATENDIMENTO AS AA ON 
	D.CD_EMP = AA.CD_EMP AND 
    D.CD_ARV_MERC_AREA_ATEND = AA.CD_ARV_MERC_AREA_ATEND 

	LEFT OUTER JOIN DBO.EST_ARV_MERC_SEGMENTO AS S ON 
	ARV.CD_EMP = S.CD_EMP AND 
	ARV.CD_ARV_MERC_SEG = S.CD_ARV_MERC_SEG 

	INNER JOIN DBO.EST_ARV_MERC_LINHA AS L ON 
	ARV.CD_EMP = L.CD_EMP AND 
	ARV.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA 

	INNER JOIN DBO.EST_ARV_MERC_CONSUMO AS C ON 
	ARV.CD_EMP = CAT.CD_EMP AND 
    ARV.CD_ARV_MERC_CONSUMO = C.CD_ARV_MERC_CONSUMO 

	INNER JOIN DBO.EST_ARV_MERC_MIX AS M ON 
	ARV.CD_EMP = M.CD_EMP AND 
	ARV.CD_ARV_MERC_MIX = M.CD_ARV_MERC_MIX 

	INNER JOIN DBO.EST_ARV_MERC_PAPEL AS PA ON 
	ARV.CD_EMP = PA.CD_EMP AND 
	ARV.CD_ARV_MERC_PAPEL = PA.CD_ARV_MERC_PAPEL 

	INNER JOIN DBO.EST_ARV_MERC_FAMILIA AS F ON 
	ARV.CD_EMP = F.CD_EMP AND 
	ARV.CD_ARV_MERC_FAMILIA = F.CD_ARV_MERC_FAMILIA 

	INNER JOIN DBO.EST_ARV_MERC_MTZ_BCG AS MTZ ON 
	ARV.CD_EMP = MTZ.CD_EMP AND 
	ARV.CD_ARV_MERC_MTZ_BCG = MTZ.CD_ARV_MERC_MTZ_BCG

	LEFT JOIN DBO.EST_PROD_PRECO AS PRC ON 
	CPL.CD_EMP = PRC.CD_EMP AND
	CPL.CD_FILIAL = PRC.CD_FILIAL AND
	CPL.CD_PROD = PRC.CD_PROD

	INNER JOIN	dbo.EST_ARV_MERC_EQUIVALENTE AS EQ ON 
	ARV.CD_EMP = EQ.CD_EMP AND 
	ARV.CD_ARV_MERC_EQUI = EQ.CD_ARV_MERC_EQUI

	INNER JOIN	dbo.EST_ARV_MERC_APRESENTACAO AS AP ON 
	ARV.CD_EMP = AP.CD_EMP AND 
	ARV.CD_APRES = AP.CD_APRES

	INNER JOIN	dbo.EST_MC AS MC ON 
	ARV.CD_EMP = MC.CD_EMP AND 
	ARV.CD_MC = MC.CD_MC

	LEFT JOIN ( SELECT B.CD_EMP, 
					B.CD_FILIAL, 
					B.CD_GRUPO 
					FROM 
					EST_PRECIFICACAO_GRP_PRECO A
					JOIN EST_PRECIFICACAO_GRP_PRECO_FILIAL B ON
					A.CD_EMP	  = B.CD_EMP AND 
					A.CD_GRUPO	  = B.CD_GRUPO
					and ISNULL(A.TIPO_GRUPO,1) = 0)GPF ON 
	CPL.CD_EMP				= GPF.CD_EMP AND 
    CPL.CD_FILIAL	     	= GPF.CD_FILIAL 
	
	LEFT JOIN EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS ON 
	CAT.CD_EMP				= EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS.CD_EMP AND 
    CAT.CD_ARV_MERC_CATEG	= EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS.CD_ARV_MERC_CATEG AND
	GPF.CD_GRUPO = EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS.CD_GRUPO
	
	LEFT JOIN EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA ON 
	pa.CD_EMP = EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA.CD_EMP AND 
    pa.CD_ARV_MERC_PAPEL = EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA.CD_ARV_MERC_PAPEL
GO


--SELECT * FROM EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS