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

GO

CREATE VIEW
	V_PRC_FILIAL_IBOPE
WITH ENCRYPTION
AS
SELECT
    F.CD_FILIAL,
    F.CGC,
    CASE WHEN NM_FANT LIKE 'FMLF%' THEN
        'FARMALIFE'
    ELSE
        COALESCE(B.NM_BANDEIRA, 'NAO DEFINIDO')
    END AS BANDEIRA,
    F.NM_FANT,
    F.DT_CAD AS DT_ABERTURA,
    'NULL' AS DT_FECHAMENTO,
    LEFT(F.END_FILILAL, CHARINDEX(' ', F.END_FILILAL)) AS TP_LOGRA,
    SUBSTRING(F.END_FILILAL, CHARINDEX(' ', F.END_FILILAL, CHARINDEX(' ', F.END_FILILAL)), LEN(F.END_FILILAL)) AS LOGRADOURO,
    F.END_FILILAL,
    '0' AS NUMERO,
    F.COMPLEMENTO,
    F.BAIRRO,
    GLB_CID.DS_CID,
    F.CEP,
    GLB_CID.UF,
    '' AS POSICIONAMENTO
FROM
    PRC_FILIAL F
    INNER JOIN GLB_CID ON
        F.CD_CID = GLB_CID.CD_CID
    LEFT JOIN PRC_BANDEIRA_PRC_FILIAL FB ON
        F.CD_EMP = FB.CD_EMP
        AND F.CD_FILIAL = FB.CD_FILIAL
    LEFT JOIN PRC_BANDEIRA B ON
        FB.CD_BANDEIRA = B.CD_BANDEIRA
    LEFT JOIN CAD_UNICO_DEPARAS D ON
        F.CD_FILIAL = D.CD_CTR_PK
        AND TABELA = 'PRC_FILIAL'
        AND FB.CD_BANDEIRA = D.BANDEIRA
WHERE
	FB.CD_BANDEIRA = 1