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

CREATE  VIEW [dbo].[V_PRC_FILIAL]
AS
SELECT     
	A.CD_EMP,
	A.CD_FILIAL , 
	A.RZ_FILIAL, 
	A.NM_FANT,
	A.NM_FILIAL_LISTA,
	A.END_FILILAL, 
	A.INSC_EST , 
	A.TEL , 
	A.BAIRRO , 
    A.CD_CID , 
	B.DS_CID,
	A.CGC,
	A.CEP,
	B.UF,
	ISNULL(D.CD_REGIAO_FISCAL,0) CD_REGIAO_FISCAL,
	ISNULL(D.NM_REGIAO_FISCAL,'NAO DEFINIDO') NM_REGIAO_FISCAL,
	ISNULL(F.CD_GRP_ECON,0) CD_GRP_ECON,
	ISNULL(F.NM_GRP_ECON,'NAO DEFINIDO') NM_GRP_ECON,
	ISNULL(H.CD_REGIAO_FILIAL,0) CD_REGIAO_FILIAL,
	ISNULL(H.NM_REGIAO_FILIAL,'NAO DEFINIDO') NM_REGIAO_FILIAL,
	ISNULL(H.STS_REGIAO,0) STS_REGIAO,
	ISNULL(I.CD_USU,1) CD_USU,
	ISNULL(I.NM_USU,'NAO DEFINIDO') NM_USU,
	ISNULL(L.CD_BANDEIRA,0) CD_BANDEIRA,
	ISNULL(L.NM_BANDEIRA,'NAO DEFINIDO') NM_BANDEIRA,
	ISNULL(A.ALIQ_ICMS,0) ALIQ_ICMS,
	ISNULL(A.STS_FILIAL,0) STS_FILIAL
FROM
        dbo.PRC_FILIAL A 
		LEFT JOIN GLB_CID B ON
		A.CD_CID = B.CD_CID
		LEFT JOIN PRC_REGIAO_FISCAL_PRC_FILIAL C ON
		A.CD_EMP = C.CD_EMP
		AND A.CD_FILIAL = C.CD_FILIAL
		LEFT JOIN PRC_REGIAO_FISCAL D ON
		C.CD_REGIAO_FISCAL = D.CD_REGIAO_FISCAL
		LEFT JOIN PRC_GRP_ECON_PRC_FILIAL E ON
		A.CD_EMP = E.CD_EMP
		AND A.CD_FILIAL = E.CD_FILIAL
		LEFT JOIN PRC_GRP_ECON F ON
		E.CD_GRP_ECON = F.CD_GRP_ECON
		LEFT JOIN PRC_REGIAO_FILIAL_PRC_FILIAL G ON
		A.CD_EMP = G.CD_EMP
		AND A.CD_FILIAL = G.CD_FILIAL
		LEFT JOIN PRC_REGIAO_FILIAL H ON
		G.CD_REGIAO_FILIAL = H.CD_REGIAO_FILIAL
		LEFT JOIN GLB_USU I ON
		H.CD_USU_SUPERVISOR = I.CD_USU
		LEFT JOIN PRC_BANDEIRA_PRC_FILIAL J ON
		A.CD_EMP = J.CD_EMP
		AND A.CD_FILIAL = J.CD_FILIAL
		LEFT JOIN PRC_BANDEIRA L ON
		J.CD_BANDEIRA = L.CD_BANDEIRA
WHERE
	A.STS_FILIAL=0;