DROP VIEW TRIBMOBILIARIOXLISTA
#
CREATE VIEW TRIBMOBILIARIOXLISTA AS
SELECT 
  MOB.TIPOCADASTRO AS TIPO,
  MOB.CODIGO AS CADASTRO,
  MOB.CODSERVICO AS CODIGOLISTASERVICOS,
  '1' AS GERARLANCAMENTO,
  SRV.ALIQUOTA,
  SRV.BASECALCULO,
  'S' AS PRINCIPAL,
  CG.IDCADASTROGERALISS
FROM MOBILIARIO MOB 
JOIN LISTASERVICOS SRV ON SRV.CODIGO = MOB.CODSERVICO
JOIN CADASTROGERAL CG ON CG.Tipo = MOB.TipoCadastro and CG.Codigo = MOB.Codigo
WHERE NOT EXISTS(
  SELECT * 
  FROM MOBILIARIOXLISTA MXL 
  WHERE MXL.TIPO = MOB.TIPOCADASTRO 
  AND MXL.CADASTRO = MOB.CODIGO 
  AND MXL.CODIGOLISTASERVICOS = MOB.CODSERVICO
)
UNION
  SELECT 
  MXL.TIPO,
  MXL.CADASTRO,
  MXL.CODIGOLISTASERVICOS,
  MXL.GERARLANCAMENTO,
  MXL.ALIQUOTA,
  MXL.BASECALCULO,
  CASE WHEN EXISTS(
    SELECT * 
    FROM MOBILIARIO MOB 
    WHERE MOB.TIPOCADASTRO = MXL.TIPO 
    AND MOB.CODIGO = MXL.CADASTRO
    AND MOB.CODSERVICO = MXL.CODIGOLISTASERVICOS) THEN 'S' ELSE 'N' END AS PRINCIPAL,
  CG.IDCADASTROGERALISS
FROM MOBILIARIOXLISTA MXL
JOIN CADASTROGERAL CG ON CG.Tipo = MXL.Tipo and CG.Codigo = MXL.Cadastro
#
DROP VIEW TRIBMOBILIARIOXCNAENACIONAL
#
CREATE VIEW TRIBMOBILIARIOXCNAENACIONAL AS
SELECT 
  MOB.TIPOCADASTRO AS TIPO,
  MOB.CODIGO AS CADASTRO,
  MOB.CNAENACIONAL,
  '1' AS GERARLANCAMENTO,
  'S' AS PRINCIPAL,
  CG.IDCADASTROGERALISS
FROM MOBILIARIO MOB
JOIN CadastroGeral CG ON CG.Tipo = MOB.TipoCadastro AND CG.Codigo = MOB.Codigo
WHERE MOB.CNAENACIONAL IS NOT NULL
AND MOB.CNAENACIONAL <> ''
UNION
SELECT 
  MCNAE.TIPO, 
  MCNAE.CADASTRO, 
  MCNAE.CNAENACIONAL, 
  MCNAE.GERARLANCAMENTO,
  'N' AS PRINCIPAL,
  CG.IDCADASTROGERALISS
FROM MOBILIARIOXCNAENACIONAL MCNAE
JOIN CadastroGeral CG ON CG.Tipo = MCNAE.Tipo AND CG.Codigo = MCNAE.Cadastro
WHERE NOT EXISTS(
  SELECT * 
  FROM MOBILIARIO MOB 
  WHERE MOB.TIPOCADASTRO = MCNAE.TIPO 
  AND MOB.CODIGO = MCNAE.CADASTRO
  AND MOB.CNAENACIONAL = MCNAE.CNAENACIONAL)
#
DROP VIEW ENTIDADE
#
CREATE VIEW ENTIDADE
 AS 
SELECT CAST(A.CODIGO AS NUMERIC(15,0)) AS ENTIDADE,
A.PREFEITURA AS NOME,
NULL AS COMPLEMENTO,
NULL AS LOGRADOURO,
B.NUMERO AS NUMERO,
NULL AS BAIRRO,
L.CIDADE AS CIDADE,
A.CEP,
A.CGC AS CNPJ,
'S' AS ENTIDADEPRINCIPAL,
NULL AS TIPOENTIDADE,
A.BRASAO,
NULL AS TELEFONE,
NULL AS IDENTIFICACAOTCE,
NULL AS CODIGO,
NULL AS SECAO,
NULL AS CNAE,
NULL AS EMAIL,
A.UF AS UNIDADEFEDERACAO,
B.RUA AS DESCRICAOLOGRADOURO,
B.BAIRRO AS DESCRICAOBAIRRO,
A.CIDADE AS DESCRICAOCIDADE,
A.MODELO,
C.HOMEPAGECOR as HOMEPAGE
FROM EMPRESA A
LEFT OUTER JOIN EMPRESAAUX B
ON A.CODIGO = B.CODIGO
LEFT OUTER JOIN CGCM C
ON A.CODIGOCGCM = C.CODIGO
LEFT OUTER JOIN LOGRADOURO L
ON L.IDLOGRADOURO = B.IDLOGRADOURO
#
DROP VIEW TRIBCADASTROGERAL
#
DROP VIEW ENDERECOPESSOA
#
DROP VIEW ENTIDADE
#
DROP VIEW CONTATOPESSOA
#
DROP VIEW ENDERECO
#