IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_REUNI_CAD_FILIAL]'))
	DROP VIEW [DBO].[V_REUNI_CAD_FILIAL]
GO


CREATE  VIEW [DBO].[V_REUNI_CAD_FILIAL]

AS


	SELECT EMP.NM_FANT AS EMPRESA
	,CD_FILIAL
	,REPLACE(REPLACE(REPLACE(CGC,'.',''),'/',''),'-','') AS CNPJ
	,A.NM_FANT
	,END_FILILAL
	,BAIRRO
	,(SELECT DS_CID FROM GLB_CID Z WHERE Z.CD_CID = A.CD_CID) AS CIDADE
	,(SELECT UF FROM GLB_CID Z WHERE Z.CD_CID = A.CD_CID) AS ESTADO
	,REPLACE(REPLACE(A.CEP,'.',''),'-','') AS CEP
	FROM PRC_FILIAL A
	INNER JOIN PRC_EMP EMP ON EMP.CD_EMP = A.CD_EMP
	WHERE ESPECIE_FILIAL = 1 --SOMENTE LOJAS

		UNION

	SELECT EMP.NM_FANT AS EMPRESA
	,CD_FILIAL
	,REPLACE(REPLACE(REPLACE(CGC,'.',''),'/',''),'-','') AS CNPJ
	,A.NM_FANT
	,END_FILILAL
	,BAIRRO
	,(SELECT DS_CID FROM GLB_CID Z WHERE Z.CD_CID = A.CD_CID) AS CIDADE
	,(SELECT UF FROM GLB_CID Z WHERE Z.CD_CID = A.CD_CID) AS ESTADO
	,REPLACE(REPLACE(A.CEP,'.',''),'-','') AS CEP
	FROM PRC_FILIAL A
	INNER JOIN PRC_EMP EMP ON EMP.CD_EMP = A.CD_EMP
	WHERE A.FILIAL_MTZ = 1 --SOMENTE CD

GO