ALTER TABLE ISSPRECADASTROCNAE ADD PRINCIPAL CHAR(1) DEFAULT 'N' NOT NULL
#
ALTER VIEW TRIBMOBILIARIOXCNAENACIONAL AS
SELECT 
  MOB.TIPOCADASTRO AS TIPO,
  MOB.CODIGO AS CADASTRO,
  MOB.CNAENACIONAL,
  '1' AS GERARLANCAMENTO,
  'S' AS PRINCIPAL,
  '1' AS HABILITADO,
  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,
  MCNAE.HABILITADO,
  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)
#