IF OBJECT_ID('V_EST_PROD_IBOPE') IS NOT NULL
BEGIN
	DROP VIEW V_EST_PROD_IBOPE
END

GO

CREATE VIEW
	V_EST_PROD_IBOPE
WITH ENCRYPTION
AS
SELECT 
    A.CD_PROD
    ,'DROGASMIL' AS BANDEIRA
    ,COALESCE((SELECT MAX(CD_BARRA) FROM EST_PROD_CD_BARRA WHERE CD_PROD = A.CD_PROD), 'NULL') AS CD_BARRA
    ,A.DS_PROD
    ,A.DS_USUAL
    ,D.CD_ARV_MERC_CATEG
    ,E.DS_ARV_MERC_CATEG
    ,E.CD_ARV_MERC_DEPTO
    ,F.DS_ARV_MERC_DEPTO
    ,D.CD_ARV_MERC_LINHA
    ,G.DS_ARV_MERC_LINHA
    ,D.CD_ARV_MERC_FAMILIA
    ,H.DS_ARV_MERC_FAMILIA
    ,D.CD_ARV_MERC_PAPEL
    ,I.DS_ARV_MERC_PAPEL
    ,A.CD_FABRIC
    ,J.NM_FABRIC
    ,COALESCE(MC.CD_MC, 0) AS CD_MC
    ,COALESCE(MC.DS_MC, 'NAO DEFINIDO') AS DS_MC
FROM            
    EST_PROD                       A
    INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA D ON 
        A.CD_EMP = D.CD_EMP
        AND A.CD_PROD = D.CD_PROD
    INNER JOIN EST_ARV_MERC_CATEGORIA         E ON 
        D.CD_EMP = E.CD_EMP
        AND D.CD_ARV_MERC_CATEG = E.CD_ARV_MERC_CATEG
    INNER JOIN EST_ARV_MERC_DEPTO             F ON 
        E.CD_EMP = F.CD_EMP
        AND E.CD_ARV_MERC_DEPTO = F.CD_ARV_MERC_DEPTO
    INNER JOIN EST_ARV_MERC_LINHA             G ON 
        D.CD_EMP = G.CD_EMP
        AND D.CD_ARV_MERC_LINHA = G.CD_ARV_MERC_LINHA
    INNER JOIN EST_ARV_MERC_FAMILIA           H ON 
        D.CD_EMP = H.CD_EMP
        AND D.CD_ARV_MERC_FAMILIA = H.CD_ARV_MERC_FAMILIA 
    INNER JOIN EST_ARV_MERC_PAPEL             I ON 
        D.CD_EMP = I.CD_EMP
        AND D.CD_ARV_MERC_PAPEL = I.CD_ARV_MERC_PAPEL
    INNER JOIN EST_PROD_FABRIC                J ON 
        A.CD_FABRIC           = J.CD_FABRIC
    LEFT JOIN EST_MC MC ON
        D.CD_EMP = MC.CD_EMP
        AND D.CD_MC = MC.CD_MC
