ALTER TABLE MOBILIARIO ADD ISSISENTO VARCHAR(1) # ALTER TABLE MOBILIARIO ADD ISSIMUNE VARCHAR(1) # DROP VIEW 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.IDREGIMEFISCAL, M.IDNATUREZAJURIDICA, M.SUBSTITUTOTRIBUTARIO, C.PESSOA, M.MICROEMPRESA, M.SUPERSIMPLES, M.ISSISENTO, M.ISSIMUNE 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 ENDERECO # CREATE VIEW [dbo].[ENDERECO] AS SELECT DISTINCT P.PESSOA, 1 AS TIPOENDERECO, P.CEP, P.CIDADE, COALESCE((SELECT MAX(LG.CIDADE) FROM CADASTROGERAL CG, LOGRADOURO LG WHERE CG.CODIGOCGCM = P.CODIGO AND LG.IDLOGRADOURO = CG.IDLOGRADOURO ),4143) AS CODIGOCIDADE, P.NUMERO, CASE WHEN P.CODIGOLOGRADOURO IS NULL THEN P.TIPOLOGRADOURO ELSE L.TIPOLOGRADOURO END AS TIPOLOGRADOURO, CASE WHEN P.CODIGOLOGRADOURO IS NULL THEN P.NOMELOGRADOURO ELSE L.DESCRICAO END AS LOGRADOURO, CASE WHEN P.CODBAIRRO IS NULL THEN P.NOMEBAIRRO ELSE B.DESCRICAO END AS BAIRRO, P.UF AS UNIDADEFEDERACAO, P.COMPLEMENTO FROM CGCM P LEFT OUTER JOIN LOGRADOUROS L ON P.CODIGOLOGRADOURO = L.CODIGO LEFT OUTER JOIN BAIRROS B ON P.CODBAIRRO = B.CODIGO # CREATE TABLE ISSTEMPLATEEMAIL ( IDTEMPLATEEMAIL NUMERIC(10,0) NOT NULL, TIPOEMAIL INT NOT NULL, ASSUNTO VARCHAR(50) NOT NULL, TEMPLATE TEXT, CONSTRAINT PK_ISSTEMPLATESEMAIL PRIMARY KEY(IDTEMPLATEEMAIL), CONSTRAINT UK_ISSTEMPLATESEMAIL_TIPOEMAIL UNIQUE(TIPOEMAIL) ) # CREATE TABLE ISSTEMPLATESEMAILCAMPOS ( IDTEMPLATESEMAILCAMPOS NUMERIC(10, 0), CAMPO VARCHAR(64), CONSTRAINT PK_ISSTEMPLATESEMAILCAMPOS PRIMARY KEY(IDTEMPLATESEMAILCAMPOS), CONSTRAINT UK_ISSTEMPLATESEMAILCAMPOS UNIQUE(CAMPO) ) # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (1, '[%nome_razaosocial_tomador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (2, '[%email_tomador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (3, '[%cnpj_tomador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (4, '[%insc_municipal_tomador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (5, '[%telefone_tomador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (6, '[%nome_razaosocial_prestador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (7, '[%email_prestador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (8, '[%cnpj_prestador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (9, '[%insc_municipal_prestador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (10, '[%telefone_prestador%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (11, '[%nr_doc_fiscal%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (12, '[%valor_doc_fiscal%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (13, '[%dt_emissao%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (14, '[%nr_doc_fiscal_cancelado%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (15, '[%valor_doc_fiscal_cancelado%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (16, '[%dt_emissao_cancelado%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (17, '[%to%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (18, '[%cc%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (19, '[%cco%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (20, '[%entidade%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (21, '[%senha_gerada%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (22, '[%cpf_usuario%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (23, '[%nome_usuario%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (24, '[%dt_nascimento%]') # INSERT INTO ISSTEMPLATESEMAILCAMPOS (IDTEMPLATESEMAILCAMPOS, CAMPO) VALUES (25, '[%portal_acesso%]') # INSERT INTO ISSTEMPLATEEMAIL (IDTEMPLATEEMAIL, TIPOEMAIL, ASSUNTO, TEMPLATE) VALUES (1, 0, '', '') # INSERT INTO ISSTEMPLATEEMAIL (IDTEMPLATEEMAIL, TIPOEMAIL, ASSUNTO, TEMPLATE) VALUES (2, 1, '', '') # INSERT INTO ISSTEMPLATEEMAIL (IDTEMPLATEEMAIL, TIPOEMAIL, ASSUNTO, TEMPLATE) VALUES (3, 2, '', '') # INSERT INTO ISSTEMPLATEEMAIL (IDTEMPLATEEMAIL, TIPOEMAIL, ASSUNTO, TEMPLATE) VALUES (4, 3, '', '') # INSERT INTO ISSTEMPLATEEMAIL (IDTEMPLATEEMAIL, TIPOEMAIL, ASSUNTO, TEMPLATE) VALUES (5, 4, '', '') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'REMETENTENOME', 'Nome do remetente de email', '') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'REMETENTEEMAIL', 'Email do remetente de email', '') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'SERVIDORSMTP', 'Servidor SMTP', '') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'PORTASMTP', 'Porta do servidor SMTP', '') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'REQUERQUTENTICACAO', 'Indica se o servidor SMTP requer autenticação', '') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'SSLSMTP', 'Indica se a conexão usa SSL') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'USUARIOSMTP', 'Usuário SMTP', '') # INSERT INTO PARAMETROGERAL (MODULO, PARAMETRO, DESCRICAO, VALOR) values ('05', 'SENHASMTP', 'Senha SMTP', '') # INSERT INTO REGIMEFISCAL (IDREGIMEFISCAL, DESCRICAO) VALUES (100, 'Simples Nacional') # CREATE TABLE ISSDMS ( IDDMS NUMERIC(10) NOT NULL, TIPOCADASTRO NUMERIC(10), CADASTROGERAL NUMERIC(10), ANOCOMPETENCIA NUMERIC(10), MESCOMPETENCIA NUMERIC(10), TIPODECLARACAO CHAR(1), TIPOMOVIMENTO CHAR(1), VALORMOVIMENTO NUMERIC(14,2), VALORISS NUMERIC(14,2), STATUS CHAR(1), DMSRETIFICADA NUMERIC(10) ) # ALTER TABLE ISSDMS ADD CONSTRAINT PK_ISSDMS PRIMARY KEY (IDDMS) # CREATE TABLE ISSDMSNOTAFISCAL ( IDDMSNOTAFISCAL NUMERIC(10) NOT NULL, IDDMS NUMERIC(10), IDNOTAFISCAL NUMERIC(10), VALORSERVICOS NUMERIC(14,2), VALORDEDUCOES NUMERIC(14,2), ALIQUOTAISS NUMERIC(5,2), BASECALCULO NUMERIC(14,2), VALORISS NUMERIC(14,2), VALORISSRETIDO NUMERIC(14,2) ) # ALTER TABLE ISSDMSNOTAFISCAL ADD CONSTRAINT PK_ISSDMSNOTAFISCAL PRIMARY KEY (IDDMSNOTAFISCAL) # ALTER TABLE ISSDMSNOTAFISCAL ADD CONSTRAINT FK_ISSDMSNOTAFISCAL_ISSDMS FOREIGN KEY (IDDMS) REFERENCES ISSDMS (IDDMS) # ALTER TABLE ISSDMSNOTAFISCAL ADD CONSTRAINT FK_ISSDMSNOTAFISCAL_ISSNOTAFISCAL FOREIGN KEY (IDNOTAFISCAL) REFERENCES ISSNOTAFISCAL (IDNOTAFISCAL) # INSERT INTO PARAMETROGERAL( MODULO, PARAMETRO,DESCRICAO,VALOR) VALUES ('05','AJUSTEISSCALCULADO','Percentual máximo para alterar o valor do iss ',10) # INSERT INTO PARAMETROGERAL( MODULO, PARAMETRO,DESCRICAO,VALOR) VALUES ('05','ALIQUOTAMINIMA','Valor Mínimo da alíquota do ISS',2) # INSERT INTO PARAMETROGERAL( MODULO, PARAMETRO,DESCRICAO,VALOR) VALUES ('05','ALIQUOTAMAXIMA','Valor Máximo da alíquota do ISS',5) # ALTER TABLE ISSNOTAFISCALPRESTADOR ADD CIDADEEXTERIOR VARCHAR(60) # ALTER TABLE ISSNOTAFISCALPRESTADOR ADD IDPAIS NUMERIC(10,0) # ALTER TABLE ISSNOTAFISCAL ADD EDITAVEL VARCHAR(1) # ALTER TABLE ISSNOTAFISCAL ADD SEQUENCIAEXCLUSAOLOGICA NUMERIC(10,0) # UPDATE CNAENACIONAL SET PERMITESERVICO = 0 WHERE PERMITESERVICO IS NULL # UPDATE CNAENACIONAL SET PERMITESERVICO = 1 WHERE CODIGO IN (161001, 7830200,6438701,5022001,4120400,161002, 7911200,6438799,5022002,4211101,161003, 7912100,6440900,5030101,4211102,162801, 7990200,6450600,5030102,4212000,162802, 8011101,6461100,5091201,4213800,162803, 8011102,6462000,5091202,4221901,210101, 8012900,6470101,5099801,4221902,210102, 8020000,6470102,5099899,4221903,210103, 8030700,6470103,5111100,4221904,210104, 8121400,6491300,5112901,4221905,210105, 8122200,6493000,5112999,4222701,210106, 8129000,6499901,5120000,4222702,210107, 8130300,6499902,5130700,4223500,220901, 8211300,6511101,5211702,4291000,220906, 8219901,6511102,5212500,4292801,1340599, 8219999,6512000,5221400,4292802,1412602, 8220200,6520100,5222200,4299501,1531902, 8230001,6530800,5223100,4299599,1811301, 8230002,6611804,5229001,4311801,1811302, 8291100,6612601,5229002,4311802,1812100, 8292000,6612602,5229099,4312600,1813001, 8299701,6612603,5231101,4313400,1813099, 8299702,6612604,5231102,4319300,1821100, 8299703,6612605,5232000,4321500,1822901, 8299704,6613400,5239700,4322301,1822999, 8299705,6619301,5240101,4322302,1830001, 8299706,6619302,5240199,4322303,1830002, 8299707,6619303,5250801,4329101,1830003, 8299799,6619304,5250802,4329102,2212900, 8412400,6619305,5250803,4329103,2330305, 8413200,6619399,5250804,4329104,2391501, 8511200,6621501,5250805,4329105,2391503, 8512100,6621502,5310501,4329199,2399101, 8513900,6622300,5310502,4330401,2442300, 8520100,6629100,5320201,4330402,2443100, 8531700,6630400,5320202,4330403,2449199, 8532500,6810201,5510801,4330404,2513600, 8533300,6821801,5510802,4330405,2539001, 8541400,6821802,5510803,4330499,2539002, 8542200,6822600,5590601,4391600,2599301, 8550301,6911701,5590602,4399101,2599302, 8550302,6911702,5590603,4399102,2950600, 8591100,6912500,5590699,4399103,3011301,8720401, 8592901,6920601,5620102,4399104,3011302,8720499, 8592902,6920602,5811500,4399105,3012100,8730101, 8592903,7020400,5812300,4399199,3211601,8730102, 8592999,7111100,5813100,4512901,3250706,8730199, 8593700,7112000,5819100,4520001,3250709,8800600, 8599601,7119701,5821200,4520002,3311200,9001901, 8599602,7119702,5822100,4520003,3312102,9001902, 8599603,7119703,5823900,4520004,3312103,9001903, 8599604,7119704,5829800,4520005,3312104,9001904, 8599605,7119799,5911101,4520006,3313901,9001905, 8599699,7120100,5911102,4520007,3313902,9001906, 8610101,7210000,5911199,4520008,3313999,9001999, 8610102,7220700,5912001,4530706,3314701,9002701, 8621601,7311400,5912002,4542101,3314702,9002702, 8621602,7312200,5912099,4543900,3314703,9003500, 8622400,7319001,5913800,4611700,3314704,9101500, 8630501,7319002,5914600,4612500,3314705,9102301, 8630502,7319003,5920100,4613300,3314706,9102302, 8630503,7319004,6010100,4614100,3314707,9103100, 8630504,7319099,6021700,4615000,3314708,9311500, 8630506,7320300,6022501,4616800,3314709,9312300, 8630507,7410201,6022502,4617600,3314710,9313100, 8630599,7410202,6110801,4618401,3314711,9319101, 8640201,7420001,6110802,4618402,3314712,9319199, 8640202,7420002,6110803,4618403,3314713,9321200, 8640203,7420003,6110899,4618499,3314714,9329801, 8640204,7420004,6120501,4619200,3314715,9329802, 8640205,7420005,6120502,4751202,3314716,9329803, 8640206,7490101,6120599,4911600,3314717,9329804, 8640207,7490102,6130200,4912401,3314718,9329899, 8640208,7490103,6190601,4912402,3314719,9511800, 8640209,7490104,6190602,4912403,3314720,9512600, 8640210,7490105,6190699,4921301,3314721,9521500, 8640211,7490199,6201500,4921302,3314722,9529101, 8640212,7500100,6202300,4922101,3314799,9529102, 8640213,7711000,6203100,4922102,3315500,9529103, 8640214,7719501,6204000,4922103,3316301,9529104, 8640299,7719502,6209100,4923001,3316302,9529105, 8650001,7719599,6311900,4923002,3317101,9529106, 8650002,7721700,6319400,4924800,3317102,9529199, 8650003,7722500,6391700,4929901,3319800,9601701, 8650004,7723300,6421200,4929902,3321000,9601702, 8650005,7729201,6422100,4929903,3329501,9601703, 8650006,7729202,6423900,4929904,3329599,9602501, 8650007,7729203,6424701,4929999,3512300,9602502, 8650099,7729299,6424702,4930201,3514000,9603301, 8660700,7731400,6424703,4930202,3600601,9603302, 8690901,7732201,6424704,4930203,3600602,9603303, 8690902,7732202,6431000,4930204,3701100,9603304, 8690903,7733100,6432800,4940000,3702900,9603305, 8690904,7739001,6433600,4950700,3811400,9603399, 8690999,7739002,6434400,5011401,3812200,9609202, 8711501,7739003,6435201,5011402,3821100,9609203, 8711502,7739099,6435202,5012201,3822000,9609205, 8711503,7740300,6435203,5012202,3839499,9609206, 8711504,7810800,6436100,5021101,3900500,9609299, 8712300,7820500,6437900,5021102,4110700,9700500) #