DROP VIEW [dbo].[CONTATOPESSOA]
#
CREATE VIEW  [dbo].[CONTATOPESSOA]  AS
SELECT PESSOA, 
       1 AS SEQUENCIA, 
       NULL AS NOME,
       TELRESIDENCIALCOR AS TELEFONERESIDENCIAL, 
       TELCOMERCIALCOR AS TELEFONECOMERCIAL,
       FAXCOR AS TELEFONEFAX,
       TELCELULARCOR AS TELEFONECELULAR,
       EMAILCOR AS EMAIL,
       HOMEPAGECOR AS HOMEPAGE,
       NULL AS REPRESENTANTELEGAL
FROM CGCM
#

DROP VIEW [dbo].[ENDERECO]
#
CREATE VIEW [dbo].[ENDERECO]
 AS 
SELECT E.PESSOA,
E.IDENDERECOTIPO AS TIPOENDERECO,
E.CEP,
C.DESCRICAO AS CIDADE,
C.CIDADE AS CODIGOCIDADE,
E.NUMERO,
L.TIPOLOGRADOURO,
TPL.DESCRICAO DESC_TIPOLOGRADOURO,
L.DESCRICAO AS LOGRADOURO,
B.DESCRICAO AS BAIRRO,
C.UNIDADEFEDERACAO,
E.COMPLEMENTO,
C.CODIGOIBGE
FROM (
SELECT
SCGCM.PESSOA,
SVE.IDENDERECOTIPO,
SVE.CEP,
SVE.NUMERO,
SVE.COMPLEMENTO,
SCGCM.CODIGO CGCM,
SVE.IDLOGRADOURO,
SVE.IDBAIRRO
FROM ENDERECOCADASTRO SVE
JOIN (
SELECT MAX(SSVE.IDENDERECOCADASTRO) IDENDERECOCADASTRO, SSVE.CODIGOCGCM, SSVE.IDENDERECOTIPO FROM (
SELECT SVE.IDENDERECOCADASTRO,
SCGCM.CODIGO CODIGOCGCM,
SVE.IDENDERECOTIPO
FROM CGCM SCGCM
JOIN ENDERECOPESSOA SVE ON SVE.PESSOA = SCGCM.PESSOA
WHERE SVE.IDENDERECOTIPO = 4
UNION ALL
SELECT SVE.IDENDERECOCADASTRO,
SCGCM.CODIGO CODIGOCGCM,
SVE.IDENDERECOTIPO
FROM CGCM SCGCM
JOIN ENDERECOPESSOA SVE ON SVE.PESSOA = SCGCM.PESSOA
WHERE SVE.IDENDERECOTIPO = 1 AND NOT EXISTS (SELECT * FROM ENDERECOPESSOA SSVE WHERE SSVE.PESSOA = SVE.PESSOA AND SSVE.IDENDERECOTIPO = 4)
) SSVE
GROUP BY SSVE.CODIGOCGCM, SSVE.IDENDERECOTIPO) SSSVE ON SSSVE.IDENDERECOCADASTRO = SVE.IDENDERECOCADASTRO
LEFT JOIN CGCM SCGCM ON SCGCM.CODIGO = SSSVE.CODIGOCGCM
) E
LEFT JOIN BAIRRO B ON B.IDBAIRRO = E.IDBAIRRO
LEFT JOIN LOGRADOURO L ON L.IDLOGRADOURO = E.IDLOGRADOURO
LEFT JOIN TIPOLOGRADOURO TPL ON TPL.TIPOLOGRADOURO = L.TIPOLOGRADOURO
LEFT JOIN CIDADE C ON C.CIDADE = L.CIDADE
#

DROP VIEW [dbo].[ENDERECOPESSOA]
#
CREATE VIEW [dbo].[ENDERECOPESSOA] AS
	SELECT SVE.IDENDERECOCADASTRO,
		SCGCM.CODIGO CODIGOCGCM,
		SVE.IDENDERECOTIPO,
		SCGCM.PESSOA
	FROM ENDERECOCADASTRO SVE
		JOIN CADASTROGERAL SCG ON SVE.TIPOCADASTRO = SCG.TIPO AND SVE.CADASTROGERAL = SCG.CODIGO
		JOIN MOBILIARIO SM ON SM.TIPOCADASTRO = SCG.TIPO AND SM.CODIGO = SCG.CODIGO
		JOIN CGCM SCGCM ON SCG.CODIGOCGCM = SCGCM.CODIGO
	UNION ALL
	SELECT SVE.IDENDERECOCADASTRO,
		SCGCM.CODIGO CODIGOCGCM,
		SVE.IDENDERECOTIPO,
		SCGCM.PESSOA
	FROM ENDERECOCADASTRO SVE
		JOIN CGCM SCGCM ON SVE.CODIGOCGCM = SCGCM.CODIGO
#
DROP VIEW [dbo].[ENTIDADE]
#
CREATE VIEW [dbo].[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 [dbo].[PESSOA]
#
create view [dbo].[PESSOA] as
select   a.pessoa,  a.nome, a.datanascimento, a.tipopessoa, a.nomefantasia, a.cgccpf as cnpj_cpf, 
         (select b.identificacaoprofissional from identificacaoprofissional b
          where b.codcgcm = a.codigo and b.codorgaoemissor = 1
          ) as rg,
          (select c.sigla from identificacaoprofissional b join orgaoemissor c on c.codigo = b.codorgaoemissor
          where b.codcgcm = a.codigo and b.codorgaoemissor = 1
          ) as orgaoemissor, 
          (select b.dataemissao from identificacaoprofissional b
          where b.codcgcm = a.codigo and b.codorgaoemissor = 1
          ) as dataemissao,
          (select b.uf from identificacaoprofissional b
          where b.codcgcm = a.codigo and b.codorgaoemissor = 1
          ) as estadoemissor, 
         a.inscricaoestadual, 
         null as sequenciaendereco, null as sequenciacontato, 
         null as sequenciabanco, 
         null as contribuinte, null   as fornecedor,
         null as pessoal, 
         null as autonomo, 
         null as datainclusao,
         null as observacao,
         null as usuarioinclusao,
         null as pais, 
         null as microempresa, 
         null as estrangeiro, 
         null as dataalteracao, 
         null as atividadeprincipal, 
         null as inscricaoissqn, 
         null as dataissqn, 
         null as fornecedorsiscop, 
         null as codigosistemaanterior,
         'n' as inativo,
		 a.codigo as cgcm,
         (select max(b.pessoa) from endereco b where a.pessoa = b.pessoa ) as id_endereco
from cgcm a
#
DROP VIEW [dbo].[TRIBCADASTROGERAL]
#
CREATE VIEW [dbo].[TRIBCADASTROGERAL] AS
SELECT CAST (M.TIPOCADASTRO AS NUMERIC(10,0)) AS TIPOCADASTRO,
CAST (M.CODIGO AS NUMERIC(10,0)) AS CADASTROGERAL,
M.DATAABERTURA,
M.DATAENCERRAMENTO,
M.ISENCAO,
M.IDREGIMEFISCAL,
M.IDNATUREZAJURIDICA,
M.SUBSTITUTOTRIBUTARIO,
C.PESSOA,
CG.IDCADASTROGERALISS,
M.MICROEMPRESA,
M.SUPERSIMPLES,
M.ISSISENTO,
M.ISSIMUNE,
M.DATAINICIOISS
FROM MOBILIARIO M
LEFT OUTER JOIN CADASTROGERAL CG ON
M.TIPOCADASTRO = CG.TIPO AND
M.CODIGO = CG.CODIGO
LEFT OUTER JOIN CGCM C ON
CG.CODIGOCGCM = C.CODIGO
#
DROP VIEW [dbo].[TRIBMOBILIARIOXCNAENACIONAL]
#
CREATE VIEW [dbo].[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 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
#
ALTER TABLE REGIMEFISCAL ADD ALIQZERO CHAR(1) NOT NULL DEFAULT 'N'
#
UPDATE REGIMEFISCAL SET ALIQZERO = 'S' WHERE IDREGIMEFISCAL IN (5,100);
#
ALTER TABLE REGIMEFISCAL ADD CONSTRAINT CHK_ALIQZERO CHECK (ALIQZERO IN ('S', 'N'));
#
ALTER TABLE ISSNOTAFISCALITEM ALTER COLUMN VALORUNITARIO NUMERIC(14,5)
#
ALTER TABLE ISSRPS ADD IDCADASTRO NUMERIC(10,0) NOT NULL,
CONSTRAINT FK_CADASTRO_RPS FOREIGN KEY (IDCADASTRO) 
  REFERENCES DBO.CADASTROGERAL (IDCADASTROGERALISS)
#
UPDATE ISSRPS SET IDCADASTRO = (
	SELECT CG.IDCADASTROGERALISS 
    FROM CADASTROGERAL CG 
    WHERE CG.TIPO = 2 
    AND CG.CODIGO = ISSRPS.CADASTROGERAL)
#
ALTER TABLE ISSRPS DROP CONSTRAINT UK_ISSRPS1,
	COLUMN CADASTROGERAL
#
ALTER TABLE ISSRPS ADD
CONSTRAINT UK_ISSRPS1 UNIQUE (IDCADASTRO, NUMERO, SERIE, TIPO, HOMOLOGACAO);
#
ALTER TABLE ISSRPSLOTE ADD IDCADASTRO NUMERIC(10,0) NOT NULL,
	CONSTRAINT FK_CADASTRO_RPSLOTE FOREIGN KEY (IDCADASTRO) 
    REFERENCES CADASTROGERAL (IDCADASTROGERALISS),
    CONSTRAINT UK_ISSRPSLOTE1 UNIQUE (NUMERO, IDCADASTRO, HOMOLOGACAO)
#
UPDATE ISSRPSLOTE SET IDCADASTRO = (
		SELECT CG.IDCADASTROGERALISS
        FROM CADASTROGERAL CG
        WHERE CG.Tipo = 2
        AND CG.CODIGO = ISSRPSLOTE.CADASTROGERAL)
#
ALTER TABLE ISSRPSLOTE DROP CONSTRAINT UK_ISSRPSLOTE,
	COLUMN CADASTROGERAL
#
ALTER TABLE ISSRPS ADD IDCADASTROTOMADOR NUMERIC(10,0) NOT NULL,
	CONSTRAINT FK_CADASTROTOMADOR_RPSLOTE FOREIGN KEY (IDCADASTROTOMADOR) 
    REFERENCES CADASTROGERAL (IDCADASTROGERALISS)
#
UPDATE ISSRPS SET IDCADASTROTOMADOR = (
		SELECT CG.IDCADASTROGERALISS
        FROM CADASTROGERAL CG
        WHERE CG.TIPO = 2
        AND CG.CODIGO = ISSRPS.TOMADORCADASTROGERAL)
#
ALTER TABLE ISSRPS DROP
	COLUMN TOMADORCADASTROGERAL
#
ALTER TABLE ISSRPS ADD IDCADASTROINTERMEDIARIO NUMERIC(10,0) NOT NULL,
	CONSTRAINT FK_CADASTROINTERMEDIARIO_RPSLOTE FOREIGN KEY (IDCADASTROINTERMEDIARIO) 
    REFERENCES CADASTROGERAL (IDCADASTROGERALISS)
#
UPDATE ISSRPS SET IDCADASTROINTERMEDIARIO = (
		SELECT CG.IDCADASTROGERALISS
        FROM CADASTROGERAL CG
        WHERE CG.TIPO = 2
        AND CG.CODIGO = ISSRPS.INTERMEDIARIOCADASTROGERAL)
#
ALTER TABLE ISSRPS DROP
	COLUMN INTERMEDIARIOCADASTROGERAL
#