 |  LoginProducts  SalesSupportDownloadsAbout |
| Home » Technical Support » ElevateDB Technical Support » Incident Reports » Incident Reports Addressed for Version 2.05 » View Incident Report |
View Incident Report Serious |
| Reported By: Fernando Dias Reported On: 4/7/2011 For: Version 2.05 Build 5 |
I'm getting an error message trying to create a database from a script generated by the Reverse-Engineering tool.
ElevateDB Error #300 An error occurred with the statement at line 3345 and column 19
(Cannot lock the table Paises in the schema Default for exclusive access)
/************************************************************
*
* ElevateDB Reverse-Engineered script for the
* AGV2Afm2011 database
*
* Generated on 07-04-2011 19:34:03
* By the user Administrator
*
************************************************************/
SCRIPT
BEGIN
/************************************************************
* Tables
************************************************************/
EXECUTE IMMEDIATE 'CREATE TABLE "Actividades"
(
"CodActividade" VARCHAR(3) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(30) COLLATE "PTG"
)
DESCRIPTION ''Actividades dos clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Bancos"
(
"CodEntidade" VARCHAR(5) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(40) COLLATE "PTG"
)
DESCRIPTION ''Bancos e Caixas''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Chaves"
(
"Id" VARCHAR(30) COLLATE "PTG" NOT NULL,
"Valor" INTEGER
)
DESCRIPTION ''Chaves de Identificação''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Clientes"
(
"NumCliente" INTEGER NOT NULL,
"Nome" VARCHAR(60) COLLATE "PTG_CI_AI",
"IdFiscal" VARCHAR(9) COLLATE "PTG",
"CodMoeda" VARCHAR(3) COLLATE "PTG",
"Morada" VARCHAR(50) COLLATE "PTG",
"Localidade" VARCHAR(30) COLLATE "PTG",
"CodPostal" VARCHAR(39) COLLATE "PTG",
"Telefone" VARCHAR(30) COLLATE "PTG",
"Telemovel" VARCHAR(30) COLLATE "PTG",
"Fax" VARCHAR(30) COLLATE "PTG",
"Email" VARCHAR(50) COLLATE "PTG",
"CodActividade" VARCHAR(3) COLLATE "PTG",
"Contactos" VARCHAR(50) COLLATE "PTG",
"Grupo" VARCHAR(10) COLLATE "PTG",
"CodCondPaga" VARCHAR(3) COLLATE "PTG",
"CodVendedor" VARCHAR(3) COLLATE "PTG",
"Obs" VARCHAR(50) COLLATE "PTG",
"CodFornecedor" INTEGER,
"PaginaWWW" VARCHAR(50) COLLATE "PTG",
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI",
"DataAlterado" DATE,
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"DataCriacao" DATE,
"ObsIdFiscal" VARCHAR(10) COLLATE "PTG",
"Situacao" VARCHAR(8) COLLATE "PTG",
"PctDesconto" DECIMAL(19,4),
"LimiteCred" DECIMAL(19,2),
"Saldo" DECIMAL(19,2),
"CodPais" VARCHAR(2) COLLATE "PTG",
"PrefIdFiscal" VARCHAR(2) COLLATE "PTG"
)
DESCRIPTION ''Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "CliZL"
(
"NumCliente" INTEGER NOT NULL,
"Zona" VARCHAR(20) COLLATE "PTG" NOT NULL,
"Local" VARCHAR(20) COLLATE "PTG" NOT NULL
)
DESCRIPTION ''Locais de Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "CondPaga"
(
"CodCondPaga" VARCHAR(3) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(20) COLLATE "PTG",
"Dias" INTEGER DEFAULT 0 NOT NULL
)
DESCRIPTION ''Condições de Pagamento''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "DadosPessoais"
(
"NumCliente" INTEGER NOT NULL,
"Nome" VARCHAR(60) COLLATE "PTG_CI_AI" NOT NULL,
"NomeAV" VARCHAR(40) COLLATE "PTG",
"Pref" VARCHAR(6) COLLATE "PTG",
"DataNascimento" DATE,
"Telefone" VARCHAR(30) COLLATE "PTG",
"Telemovel" VARCHAR(30) COLLATE "PTG",
"Fax" VARCHAR(30) COLLATE "PTG",
"EMail" VARCHAR(40) COLLATE "PTG",
"Morada" VARCHAR(50) COLLATE "PTG",
"Localidade" VARCHAR(30) COLLATE "PTG",
"CodPostal" VARCHAR(38) COLLATE "PTG",
"IdFiscal" VARCHAR(9) COLLATE "PTG",
"NumBI" VARCHAR(12) COLLATE "PTG",
"ObsIdFiscal" VARCHAR(10) COLLATE "PTG",
"NumPFreq1" VARCHAR(16) COLLATE "PTG",
"NumPFreq2" VARCHAR(16) COLLATE "PTG",
"NumPFreq3" VARCHAR(16) COLLATE "PTG",
"Delegacao" INTEGER,
"Zona" VARCHAR(20) COLLATE "PTG",
"Local" VARCHAR(20) COLLATE "PTG",
"Obs" VARCHAR(45) COLLATE "PTG",
"Obs2" VARCHAR(45) COLLATE "PTG",
"CodPais" VARCHAR(2) COLLATE "PTG",
"PrefIdFiscal" VARCHAR(2) COLLATE "PTG"
)
DESCRIPTION ''Contactos e Dados Pessoais''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Delegacoes"
(
"CodDelegacao" INTEGER NOT NULL,
"NomeDelegacao" VARCHAR(40) COLLATE "PTG",
"NumClienteInicial" INTEGER,
"NumClienteFinal" INTEGER
)
DESCRIPTION ''Delegações''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Diarios"
(
"TipoDiario" VARCHAR(1) COLLATE "PTG" NOT NULL,
"NumDiario" INTEGER NOT NULL,
"Descricao" VARCHAR(30) COLLATE "PTG",
"TiposMovimento" VARCHAR(50) COLLATE "PTG",
"DebitoCredito" BOOLEAN,
"Fixo" BOOLEAN
)
DESCRIPTION ''Diários''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "DocsAbrCli"
(
"AnoDoc" INTEGER NOT NULL,
"TipoDoc" VARCHAR(3) COLLATE "PTG" NOT NULL,
"SerieDoc" VARCHAR(1) COLLATE "PTG" NOT NULL,
"NumDoc" VARCHAR(12) COLLATE "PTG" NOT NULL,
"NumCliente" INTEGER NOT NULL,
"DataDoc" DATE,
"DataVencimento" DATE,
"ValorAberto" DECIMAL(19,2),
"ValorInicial" DECIMAL(19,2)
)
DESCRIPTION ''Documentos em Aberto de Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "FacCab"
(
"TipoDoc" VARCHAR(3) COLLATE "PTG" NOT NULL,
"SerieDoc" VARCHAR(1) COLLATE "PTG" NOT NULL,
"NumDoc" INTEGER NOT NULL,
"DataDoc" DATE NOT NULL,
"NumCliente" INTEGER,
"Nome" VARCHAR(60) COLLATE "PTG",
"IdFiscal" VARCHAR(9) COLLATE "PTG",
"CodMoeda" VARCHAR(3) COLLATE "PTG",
"Cambio" FLOAT,
"Morada" VARCHAR(50) COLLATE "PTG",
"Localidade" VARCHAR(30) COLLATE "PTG",
"CodPostal" VARCHAR(39) COLLATE "PTG",
"CodCondPaga" VARCHAR(3) COLLATE "PTG",
"CodVendedor" VARCHAR(3) COLLATE "PTG",
"PctDesconto" FLOAT,
"Obs" VARCHAR(40) COLLATE "PTG",
"RefDoc" VARCHAR(12) COLLATE "PTG",
"FormaPaga" VARCHAR(3) COLLATE "PTG",
"Estado" VARCHAR(3) COLLATE "PTG",
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI",
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"DataCriacao" TIMESTAMP,
"DataAlteracao" TIMESTAMP,
"PctIva1" DECIMAL(19,2),
"PctIva2" DECIMAL(19,2),
"PctIva3" DECIMAL(19,2),
"Incidencia0" DECIMAL(19,2),
"Incidencia1" DECIMAL(19,2),
"Incidencia2" DECIMAL(19,2),
"Incidencia3" DECIMAL(19,2),
"ValorIva1" DECIMAL(19,2),
"ValorIva2" DECIMAL(19,2),
"ValorIva3" DECIMAL(19,2),
"ValorCusto" DECIMAL(19,2),
"ValorMargem" DECIMAL(19,2),
"ValorSemIva" DECIMAL(19,2),
"ValorComIva" DECIMAL(19,2),
"Taxas" DECIMAL(19,2),
"TaxaXP" DECIMAL(19,2),
"Penalizacao" DECIMAL(19,2) DEFAULT 0 ,
"ValorTotal" DECIMAL(19,2),
"ValorDescontos" DECIMAL(19,2),
"Total" DECIMAL(19,2),
"CodPais" VARCHAR(2) COLLATE "PTG",
"PrefIdFiscal" VARCHAR(2) COLLATE "PTG",
"DataVencimento" DATE,
"Assinatura" VARCHAR(200) COLLATE "ANSI" DEFAULT ''0'',
"VersaoChave" INTEGER DEFAULT NULL
)
DESCRIPTION ''Documentos de Venda''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "FacLin"
(
"TipoDoc" VARCHAR(3) COLLATE "PTG" NOT NULL,
"SerieDoc" VARCHAR(1) COLLATE "PTG" NOT NULL,
"NumDoc" INTEGER NOT NULL,
"Linha" INTEGER NOT NULL,
"Anexo" INTEGER DEFAULT 0 NOT NULL,
"CodFornecedor" INTEGER,
"CodServico" VARCHAR(5) COLLATE "PTG",
"CodTServico" VARCHAR(8) COLLATE "PTG",
"Descricao" VARCHAR(50) COLLATE "PTG",
"Descricao2" VARCHAR(50) COLLATE "PTG",
"DataServico" DATE,
"NumBilhete" VARCHAR(20) COLLATE "PTG",
"Qt" INTEGER,
"PctMargemLiq" FLOAT,
"PctMargemTotal" FLOAT,
"CodIVA" VARCHAR(3) COLLATE "PTG",
"MetodoCalculo" INTEGER,
"PedeVComIVA" BOOLEAN,
"MsgIVA" VARCHAR(40) COLLATE "PTG",
"PVP" DECIMAL(19,4),
"LegislacaoIVA" VARCHAR(40) COLLATE "PTG",
"Valor" DECIMAL(19,4),
"PCusto" DECIMAL(19,4),
"Margem" DECIMAL(19,4),
"PVenda" DECIMAL(19,4),
"PctIvaPCusto" DECIMAL(19,2),
"PctIvaMargem" DECIMAL(19,2),
"PctIvaComissao" DECIMAL(19,2),
"Taxas" DECIMAL(19,4),
"TaxaXP" DECIMAL(19,4),
"DataEmissaoTkt" DATE,
"Penalizacao" DECIMAL(19,2) DEFAULT 0
)
DESCRIPTION ''Linhas dos Documentos de Venda''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Fornecedores"
(
"CodFornecedor" INTEGER NOT NULL,
"Nome" VARCHAR(60) COLLATE "PTG_CI_AI",
"Categoria" VARCHAR(5) COLLATE "PTG",
"IdFiscal" VARCHAR(9) COLLATE "PTG",
"CodMoeda" VARCHAR(3) COLLATE "PTG",
"Morada" VARCHAR(50) COLLATE "PTG",
"Localidade" VARCHAR(30) COLLATE "PTG",
"CodPostal" VARCHAR(39) COLLATE "PTG",
"Telefone" VARCHAR(30) COLLATE "PTG",
"TeleMovel" VARCHAR(30) COLLATE "PTG",
"Fax" VARCHAR(30) COLLATE "PTG",
"Email" VARCHAR(50) COLLATE "PTG",
"Contactos" VARCHAR(50) COLLATE "PTG",
"Grupo" VARCHAR(10) COLLATE "PTG",
"CodCondPaga" VARCHAR(3) COLLATE "PTG",
"Obs" VARCHAR(50) COLLATE "PTG",
"NumCliente" INTEGER,
"PaginaWWW" VARCHAR(50) COLLATE "PTG",
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI",
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"DataCriacao" DATE,
"DataAlterado" DATE,
"ObsIdFiscal" VARCHAR(10) COLLATE "PTG",
"ComissaoCC" DECIMAL(19,4),
"DadosBanco" VARCHAR(40) COLLATE "PTG",
"Saldo" DECIMAL(19,2),
"CodPais" VARCHAR(2) COLLATE "PTG",
"PrefIdFiscal" VARCHAR(2) COLLATE "PTG",
"AvisoTP" BOOLEAN,
"DiasAvisoTP" INTEGER
)
DESCRIPTION ''Fornecedores''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "FornServ"
(
"CodFornecedor" INTEGER NOT NULL,
"CodServico" VARCHAR(5) COLLATE "PTG" NOT NULL,
"Notas" CLOB COLLATE "PTG"
)
DESCRIPTION ''Serviços dos Fornecedores''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "FornZL"
(
"CodFornecedor" INTEGER NOT NULL,
"Zona" VARCHAR(20) COLLATE "PTG" NOT NULL,
"Local" VARCHAR(20) COLLATE "PTG" NOT NULL
)
DESCRIPTION ''Locais de Fornecedores''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Locais"
(
"Zona" VARCHAR(20) COLLATE "PTG" NOT NULL,
"Local" VARCHAR(20) COLLATE "PTG" NOT NULL
)
DESCRIPTION ''Locais''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "MCalc"
(
"MetodoDeCalculo" INTEGER NOT NULL,
"Descricao" VARCHAR(50) COLLATE "PTG"
)
DESCRIPTION ''Métodos de Cálculo''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Moedas"
(
"CodMoeda" VARCHAR(3) COLLATE "PTG" NOT NULL,
"Moeda" VARCHAR(30) COLLATE "PTG",
"SubMoeda" VARCHAR(30) COLLATE "PTG",
"Cambio" FLOAT DEFAULT 1 NOT NULL,
"DataCambio" DATE,
"Decimais" INTEGER DEFAULT 2 NOT NULL
)
DESCRIPTION ''Moedas e Cambios''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "MovCli"
(
"Diario" INTEGER NOT NULL,
"NumLanc" INTEGER NOT NULL,
"NumCliente" INTEGER,
"DataMov" DATE,
"Descricao" VARCHAR(30) COLLATE "PTG",
"AbrFecho" VARCHAR(1) COLLATE "PTG",
"AnoDoc" INTEGER,
"TipoDoc" VARCHAR(3) COLLATE "PTG",
"SerieDoc" VARCHAR(1) COLLATE "PTG",
"NumDoc" VARCHAR(12) COLLATE "PTG",
"DataDoc" DATE,
"DataVencDoc" DATE,
"ModoLanc" VARCHAR(1) COLLATE "PTG",
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI",
"DataCriado" DATE,
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"DataAlterado" DATE,
"ValorMov" DECIMAL(19,2)
)
DESCRIPTION ''Movimentos de Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "MovCliL"
(
"Diario" INTEGER NOT NULL,
"NumLanc" INTEGER NOT NULL,
"Linha" INTEGER NOT NULL,
"TipoLanc" VARCHAR(1) COLLATE "PTG",
"CodEntidade" VARCHAR(5) COLLATE "PTG",
"AnoDoc" INTEGER,
"TipoDoc" VARCHAR(3) COLLATE "PTG",
"SerieDoc" VARCHAR(1) COLLATE "PTG",
"NumDoc" VARCHAR(12) COLLATE "PTG",
"DataDoc" DATE,
"Valor" DECIMAL(19,2)
)
DESCRIPTION ''Fechos de Documentos de Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "NotasCli"
(
"NumCliente" INTEGER NOT NULL,
"Data" DATE NOT NULL,
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI" NOT NULL,
"Assunto" VARCHAR(50) COLLATE "PTG",
"Cor" INTEGER,
"Arquivar" BOOLEAN,
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"CriadoEm" TIMESTAMP,
"AlteradoEm" TIMESTAMP,
"Texto" CLOB COLLATE "PTG",
"Agendar" BOOLEAN,
"AgData" DATE,
"AgHora" TIME,
"AgConcluido" BOOLEAN,
"AgTodos" BOOLEAN,
"Delegacao" INTEGER DEFAULT 0 NOT NULL
)
DESCRIPTION ''Anotações Sobre Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "NotasFor"
(
"CodFornecedor" INTEGER NOT NULL,
"Data" DATE NOT NULL,
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI" NOT NULL,
"Assunto" VARCHAR(50) COLLATE "PTG",
"Cor" INTEGER,
"Arquivar" BOOLEAN,
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"CriadoEm" TIMESTAMP,
"AlteradoEm" TIMESTAMP,
"Texto" CLOB COLLATE "PTG",
"Agendar" BOOLEAN,
"AgData" DATE,
"AgHora" TIME,
"AgConcluido" BOOLEAN,
"AgTodos" BOOLEAN,
"Delegacao" INTEGER DEFAULT 0 NOT NULL
)
DESCRIPTION ''Anotações sobre Fornecedores''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Paises"
(
"CodPais" VARCHAR(2) COLLATE "PTG" NOT NULL,
"CodPais3A" VARCHAR(3) COLLATE "ANSI",
"CodPais3N" VARCHAR(3) COLLATE "ANSI",
"UE" BOOLEAN,
"NomeP" VARCHAR(60) COLLATE "PTG",
"NomeI" VARCHAR(60) COLLATE "PTG",
"NomeF" VARCHAR(60) COLLATE "PTG"
)
DESCRIPTION ''Países''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "PassFac"
(
"TipoDoc" VARCHAR(3) COLLATE "PTG" NOT NULL,
"SerieDoc" VARCHAR(1) COLLATE "PTG" NOT NULL,
"NumDoc" INTEGER NOT NULL,
"NumPassageiro" INTEGER NOT NULL,
"Nome" VARCHAR(50) COLLATE "PTG"
)
DESCRIPTION ''Passageiros/Utilizadores de Docs. de Venda''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "RecCab"
(
"SerieRecibo" VARCHAR(1) COLLATE "PTG" NOT NULL,
"NumRecibo" INTEGER NOT NULL,
"DataRecibo" DATE NOT NULL,
"NumCliente" INTEGER,
"Nome" VARCHAR(55) COLLATE "PTG",
"IdFiscal" VARCHAR(9) COLLATE "PTG",
"CodMoeda" VARCHAR(3) COLLATE "PTG",
"Cambio" FLOAT,
"Morada" VARCHAR(50) COLLATE "PTG",
"Localidade" VARCHAR(30) COLLATE "PTG",
"CodPostal" VARCHAR(39) COLLATE "PTG",
"Obs" VARCHAR(80) COLLATE "PTG",
"FormaPaga" VARCHAR(3) COLLATE "PTG",
"Estado" VARCHAR(3) COLLATE "PTG",
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI",
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"DataCriacao" TIMESTAMP,
"DataAlteracao" TIMESTAMP,
"Valor" DECIMAL(19,2),
"CodPais" VARCHAR(2) COLLATE "PTG",
"PrefIdFiscal" VARCHAR(2) COLLATE "PTG"
)
DESCRIPTION ''Recibos de Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "RecLin"
(
"SerieRecibo" VARCHAR(1) COLLATE "PTG" NOT NULL,
"NumRecibo" INTEGER NOT NULL,
"Linha" INTEGER NOT NULL,
"TipoLanc" VARCHAR(1) COLLATE "PTG",
"CodEntidade" VARCHAR(5) COLLATE "PTG",
"AnoDoc" INTEGER,
"TipoDoc" VARCHAR(3) COLLATE "PTG",
"SerieDoc" VARCHAR(1) COLLATE "PTG",
"NumDoc" VARCHAR(12) COLLATE "PTG",
"DataDoc" DATE,
"Valor" DECIMAL(19,2)
)
DESCRIPTION ''Linhas dos Recibos de Clientes''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "ResCont"
(
"Pagina" INTEGER NOT NULL,
"Linha" INTEGER NOT NULL,
"Desc1" VARCHAR(40) COLLATE "PTG",
"Desc2" VARCHAR(40) COLLATE "PTG",
"IdVar" VARCHAR(16) COLLATE "PTG_CI",
"Separador" BOOLEAN,
"FSize" INTEGER,
"Bold" BOOLEAN,
"Valor" DECIMAL(19,2)
)
DESCRIPTION ''Resumo Contabilistico''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "RptForms"
(
"RptId" VARCHAR(5) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(60) COLLATE "PTG",
"Rpt" BLOB
)
DESCRIPTION ''Formulários de Impressão''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Series"
(
"Serie" VARCHAR(1) COLLATE "PTG" NOT NULL,
"Delegacao" INTEGER,
"Descricao" VARCHAR(30) COLLATE "PTG"
)
DESCRIPTION ''Séries de Numeração''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Servicos"
(
"CodServico" VARCHAR(5) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(40) COLLATE "PTG",
"CodIva" VARCHAR(3) COLLATE "PTG",
"CodIvaCom" VARCHAR(3) COLLATE "PTG",
"MCalc" INTEGER,
"Imagem" INTEGER,
"Ordem" INTEGER,
"ColunaA" VARCHAR(30) COLLATE "PTG",
"ColunaB" VARCHAR(30) COLLATE "PTG",
"ColunaC" VARCHAR(30) COLLATE "PTG",
"ColunaD" VARCHAR(30) COLLATE "PTG"
)
DESCRIPTION ''Serviços''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "SitCli"
(
"Situacao" VARCHAR(8) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(30) COLLATE "PTG"
)
DESCRIPTION ''Situações da Conta Corrente''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "TabPre"
(
"CodServico" VARCHAR(5) COLLATE "PTG" NOT NULL,
"CodFornecedor" INTEGER NOT NULL,
"NumTabela" INTEGER NOT NULL,
"Titulo" VARCHAR(60) COLLATE "PTG",
"TipoTarifa" VARCHAR(12) COLLATE "PTG",
"Grupos" BOOLEAN,
"Individuais" BOOLEAN,
"Zona" VARCHAR(20) COLLATE "PTG",
"Local" VARCHAR(20) COLLATE "PTG",
"ColunaA" VARCHAR(30) COLLATE "PTG",
"ColunaB" VARCHAR(30) COLLATE "PTG",
"ColunaC" VARCHAR(30) COLLATE "PTG",
"ImprimeABC" VARCHAR(30) COLLATE "PTG",
"Margem" DECIMAL(19,4),
"TMargem" VARCHAR(1) COLLATE "PTG",
"Notas" CLOB COLLATE "PTG",
"ColunaD" VARCHAR(30) COLLATE "PTG",
"PVPIgualBalcao" BOOLEAN
)
DESCRIPTION ''Tabelas de Preços''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "TabPreCol"
(
"CodServico" VARCHAR(5) COLLATE "PTG" NOT NULL,
"CodFornecedor" INTEGER NOT NULL,
"NumTabela" INTEGER NOT NULL,
"NumCol" INTEGER NOT NULL,
"Inicio" DATE,
"Fim" DATE,
"Titulo" VARCHAR(30) COLLATE "PTG",
"OrdemCol" INTEGER,
"Inicio2" DATE,
"Fim2" DATE,
"Inicio3" DATE,
"Fim3" DATE
)
DESCRIPTION ''Colunas de Tabelas de Preços''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "TabPreLn"
(
"CodServico" VARCHAR(5) COLLATE "PTG" NOT NULL,
"CodFornecedor" INTEGER NOT NULL,
"NumTabela" INTEGER NOT NULL,
"NumCol" INTEGER NOT NULL,
"CodTServico" VARCHAR(8) COLLATE "PTG" NOT NULL,
"Linha" INTEGER NOT NULL,
"PVP" DECIMAL(19,4),
"PrecoC" DECIMAL(19,4),
"Margem" DECIMAL(19,4),
"TMargem" VARCHAR(1) COLLATE "PTG",
"Taxas" DECIMAL(19,4),
"TaxaXP" DECIMAL(19,4),
"Obs" VARCHAR(30) COLLATE "PTG",
"PBalcao" DECIMAL(19,4)
)
DESCRIPTION ''Linhas de Tabelas de Preços''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Taxas"
(
"CodTaxa" VARCHAR(3) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(50) COLLATE "PTG",
"PctCusto" DECIMAL(19,2),
"PctMargem" DECIMAL(19,2),
"PctComissao" DECIMAL(19,2),
"DescricaoCurta" VARCHAR(20) COLLATE "PTG",
"DataFimTaxa" DATE DEFAULT DATE ''9999-12-31'' NOT NULL,
"Legislacao" VARCHAR(40) COLLATE "PTG"
)
DESCRIPTION ''Taxas de IVA''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "TiposDoc"
(
"TipoDoc" VARCHAR(3) COLLATE "PTG" NOT NULL,
"Descricao" VARCHAR(30) COLLATE "PTG",
"DescricaoPlural" VARCHAR(30) COLLATE "PTG",
"CliFor" VARCHAR(1) COLLATE "PTG",
"BCaixas" BOOLEAN,
"CrDeb" VARCHAR(1) COLLATE "PTG",
"Movimento" INTEGER DEFAULT 1 NOT NULL DESCRIPTION ''Por enquanto é apenas usado nas compras.'',
"AFecho" VARCHAR(1) COLLATE "PTG",
"Fixo" BOOLEAN,
"Vias" INTEGER,
"RptId" VARCHAR(5) COLLATE "PTG"
)
DESCRIPTION ''Tipos de Documento''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "TiposServ"
(
"CodServico" VARCHAR(5) COLLATE "PTG" NOT NULL,
"CodTServico" VARCHAR(8) COLLATE "PTG" NOT NULL,
"DescricaoTab" VARCHAR(60) COLLATE "PTG",
"DescricaoFac" VARCHAR(60) COLLATE "PTG",
"DescricaoInt" VARCHAR(60) COLLATE "PTG",
"Ordem" INTEGER,
"CodIva" VARCHAR(3) COLLATE "PTG",
"CodIvaCom" VARCHAR(3) COLLATE "PTG",
"MCalc" INTEGER,
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI",
"CriadoEm" TIMESTAMP,
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"AlteradoEm" TIMESTAMP
)
DESCRIPTION ''Tipos de Serviço''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "TiposTarifa"
(
"TipoTarifa" VARCHAR(12) COLLATE "PTG" NOT NULL
)
DESCRIPTION ''Tipos de Tarifa''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Vendedores"
(
"CodVendedor" VARCHAR(3) COLLATE "PTG" NOT NULL,
"NomeVendedor" VARCHAR(30) COLLATE "PTG"
)
DESCRIPTION ''Vendedores''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "Zonas"
(
"Zona" VARCHAR(20) COLLATE "PTG" NOT NULL
)
DESCRIPTION ''Zonas''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "AppLog"
(
"ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Data" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
"Evento" VARCHAR(40) COLLATE "PTG_CI",
"Utilizador" VARCHAR(16) COLLATE "PTG_CI" DEFAULT CURRENT_USER NOT NULL,
"IdBd" VARCHAR(16) COLLATE "PTG_CI",
"Msg" CLOB COLLATE "PTG" DEFAULT '''' NOT NULL
)
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "ComCab"
(
"TipoDoc" VARCHAR(3) COLLATE "PTG" NOT NULL,
"NumReg" INTEGER NOT NULL,
"CodFornecedor" INTEGER,
"NumDoc" VARCHAR(16) COLLATE "PTG_CI" NOT NULL,
"DataDoc" DATE NOT NULL,
"Nome" VARCHAR(60) COLLATE "PTG_CI_AI",
"CodPais" VARCHAR(2) COLLATE "PTG" DEFAULT ''PT'' NOT NULL,
"PrefIdFiscal" VARCHAR(2) COLLATE "PTG",
"IdFiscal" VARCHAR(9) COLLATE "PTG",
"Obs" VARCHAR(60) COLLATE "PTG",
"CodCondPaga" VARCHAR(3) COLLATE "PTG",
"DataVencimento" DATE,
"ValorTotal" DECIMAL(19,2) DEFAULT 0 NOT NULL,
"TotalTaxas" DECIMAL(19,2) DEFAULT 0 NOT NULL,
"DescontoFin" DECIMAL(19,2) DEFAULT 0 NOT NULL,
"Total" DECIMAL(19,2) GENERATED ALWAYS AS ValorTotal + TotalTaxas - DescontoFin,
"CodMoeda" VARCHAR(3) COLLATE "PTG" DEFAULT ''EUR'' NOT NULL,
"Cambio" FLOAT DEFAULT 1 NOT NULL,
"Voucher" VARCHAR(16) COLLATE "PTG",
"TemTaxas" BOOLEAN DEFAULT False NOT NULL,
"Movimento" INTEGER,
"CrDeb" VARCHAR(1) COLLATE "ANSI",
"CriadoPor" VARCHAR(16) COLLATE "PTG_CI" DEFAULT CURRENT_USER NOT NULL,
"AlteradoPor" VARCHAR(16) COLLATE "PTG_CI",
"CriadoEm" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
"AlteradoEm" TIMESTAMP
)
DESCRIPTION ''Documentos de Compra''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
EXECUTE IMMEDIATE 'CREATE TABLE "ComLin"
(
"TipoDoc" VARCHAR(3) COLLATE "PTG" NOT NULL,
"NumReg" INTEGER NOT NULL,
"Linha" INTEGER NOT NULL,
"CodServico" VARCHAR(5) COLLATE "PTG",
"CodTServico" VARCHAR(8) COLLATE "PTG",
"Descricao" VARCHAR(60) COLLATE "PTG",
"DataServico" DATE,
"Qt" INTEGER,
"Preco" DECIMAL(19,4),
"CodIva" VARCHAR(3) COLLATE "PTG",
"PctIva" DECIMAL(19,2),
"Valor" DECIMAL(19,4),
"Taxas" DECIMAL(19,4),
"TotalTaxas" DECIMAL(19,4) COMPUTED ALWAYS AS Qt * Taxas ,
"Incidencia" DECIMAL(19,4) COMPUTED ALWAYS AS COALESCE(ROUND((Valor * 100) / (PctIva + 100), 3), 0) ,
"IVA" DECIMAL(19,4) COMPUTED ALWAYS AS ROUND((Incidencia * PctIva)/100, 3)
,
"Voucher" VARCHAR(16) COLLATE "PTG"
)
DESCRIPTION ''Linhas de Documentos de Compra''
VERSION 2.1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';
/************************************************************
* Table Rows
************************************************************/
/************************************************************
* Views
************************************************************/
EXECUTE IMMEDIATE 'CREATE VIEW "ComIva" AS
SELECT
TipoDoc, NumReg, CodIva, PctIva, DescIva, ValorTotal,
ROUND( (ValorTotal * 100)/(100+PctIva), 2 ) AS IncidenciaTotal,
ROUND( ((ROUND( (ValorTotal * 100)/(100+PctIva), 2)) * PctIva)/100, 2 ) AS IvaTotal
FROM
(
SELECT
TipoDoc, NumReg, CodIva, PctIva,
(SELECT DescricaoCurta FROM Taxas WHERE CodTaxa = CL.CodIva) AS DescIva,
SUM(Valor) As ValorTotal
FROM
ComLin CL
WHERE
COALESCE(Valor, 0) <> 0
GROUP BY
TipoDoc, NumReg, CodIva, PctIva
) As Tmp__ComIva
VERSION 2.04';
/************************************************************
* Functions
************************************************************/
EXECUTE IMMEDIATE 'CREATE FUNCTION "NovaChave" (IN "IdChave" VARCHAR COLLATE PTG)
RETURNS INTEGER
BEGIN
DECLARE Result INTEGER;
DECLARE Cur1 SENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
DECLARE Chave VARCHAR;
BEGIN
SET Chave = UPPER(TRIM(BOTH '' '' FROM IdChave));
PREPARE Sql1 FROM ''SELECT Id, Valor FROM "Chaves" WHERE Id = ?'';
OPEN Cur1 USING Chave ;
IF ROWCOUNT(Cur1)=0 THEN
INSERT INTO Cur1 VALUES(Chave, 1);
SET Result = 1;
ELSE
FETCH FIRST FROM Cur1(''Valor'') INTO Result;
SET Result = Result + 1;
UPDATE Cur1 SET ''Valor'' = Result;
END IF;
EXCEPTION
SET Result = NULL;
END;
CLOSE Cur1;
RETURN Result;
END
VERSION 2.04';
EXECUTE IMMEDIATE 'CREATE FUNCTION "GetDBParam" (IN "DBName" VARCHAR COLLATE ANSI, IN "ParamName" VARCHAR COLLATE ANSI)
RETURNS VARCHAR COLLATE PTG
BEGIN
DECLARE I,N INTEGER;
DECLARE IsaValueChar BOOLEAN;
DECLARE Params, ParamValue, C VARCHAR;
DECLARE Cur1 SENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
PREPARE Sql1 FROM ''SELECT Description FROM Configuration.Databases WHERE Name = ?'' ;
SET ParamValue = '''';
OPEN Cur1 USING DBName;
IF ROWCOUNT(Cur1) > 0 THEN
FETCH FIRST FROM Cur1 INTO Params;
SET I = POS(UPPER(ParamName) IN UPPER(Params));
IF I > 0 THEN
SET IsaValueChar = False ;
SET N = LENGTH(Params);
WHILE I <= N DO
SET C = SUBSTRING(Params,I,1);
IF C = #13 OR C=#10 THEN LEAVE ; END IF;
IF IsaValueChar THEN SET ParamValue = ParamValue + C; END IF;
IF C = ''='' THEN SET IsaValueChar = True ; END IF;
SET I = I+1;
END WHILE;
ELSE
SET ParamValue = '''';
END IF;
ELSE
SET ParamValue = '''';
END IF;
UNPREPARE Sql1;
RETURN ParamValue;
END
DESCRIPTION ''Devolve o valor correspondente ao nome indicado, na lista de pares <Valor>=<Nome>[#13<Valor>=<Nome>] da descrição da base de dados''
VERSION 1';
/************************************************************
* Procedures
************************************************************/
EXECUTE IMMEDIATE 'CREATE PROCEDURE "PrecosServ" (IN "CodServico" VARCHAR(5) COLLATE PTG, IN "CodTServico" VARCHAR(8) COLLATE PTG, IN "CodFornecedor" INTEGER, IN "DataServico" DATE)
BEGIN
DECLARE Cursor1 CURSOR WITH RETURN FOR Sql1;
DECLARE st VARCHAR;
SET st = ''
SELECT
CodFornecedor, CodServico, CodTServico, NumTabela, TipoTarifa, Individuais, Grupos,
NumCol, Titulo, Inicio, Fim, Inicio2, Fim2, Inicio3, Fim3, Linha,
PVP, Taxas, TaxaXp, Margem, PrecoC, TMargem
FROM
TabPreLn L
JOIN TabPreCol C ON C.CodServico=L.CodServico AND C.CodFornecedor=L.CodFornecedor AND C.NumTabela=L.NumTabela AND C.NumCol=L.NumCol
JOIN TabPre T ON T.CodServico=C.CodServico AND T.CodFornecedor=C.CodFornecedor AND C.NumTabela=T.NumTabela
WHERE
CodServico= ? AND CodTServico= ? '';
IF CodFornecedor IS NOT NULL THEN
SET st = st + ''AND CodFornecedor= ? '';
END IF;
IF DataServico IS NOT NULL THEN
SET st = st +
''AND (
? BETWEEN Inicio AND Fim OR
? BETWEEN Inicio2 AND Fim2 OR
? BETWEEN Inicio3 AND Fim3 OR
(Inicio IS NULL AND Fim IS NULL AND Inicio2 IS NULL AND Fim2 IS NULL AND Inicio3 IS NULL AND Fim3 IS NULL)
)'';
END IF;
PREPARE Sql1 FROM st;
IF CodFornecedor IS NULL THEN
IF DataServico IS NULL THEN
OPEN Cursor1 USING CodServico, CodTServico;
ELSE
OPEN Cursor1 USING CodServico, CodTServico, DataServico, DataServico, DataServico;
END IF;
ELSE
IF DataServico IS NULL THEN
OPEN Cursor1 USING CodServico, CodTServico, CodFornecedor;
ELSE
OPEN Cursor1 USING CodServico, CodTServico, CodFornecedor, DataServico, DataServico, DataServico;
END IF;
END IF;
END
DESCRIPTION ''Devolve uma lista de preços, de acordo com os argumentos:
- CodServico
- CodTServico
- CodFornecedor : se NULL, selecciona preços de todos os fornecedores
- DataServico: se NULL selecciona preços de todas as datas ;
os preços sem datas associadas são sempre seleccionados independentemente do valor de DataServico''
VERSION 1';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "NewLogEvent" (OUT "ID" INTEGER, IN "Evento" VARCHAR(40) COLLATE PTG, IN "IdBd" VARCHAR(16) COLLATE PTG_CI)
BEGIN
DECLARE Sql1 STATEMENT;
PREPARE Sql1 FROM ''INSERT INTO "AppLog"(ID, Evento, IdBd) VALUES(?,?,?)'';
EXECUTE Sql1 USING Id, Evento, IdBd;
END
DESCRIPTION ''Cria um novo evento na tabela "AppLog" ''
VERSION 1';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "AddLogEvent" (IN "Id" INTEGER, IN "NewMsg" VARCHAR COLLATE PTG)
BEGIN
DECLARE Cur1 SENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
DECLARE St CLOB;
IF NewMsg IS NOT NULL THEN
PREPARE Sql1 FROM ''SELECT Msg FROM "AppLog" WHERE Id = ?'';
OPEN Cur1 USING Id;
IF ROWCOUNT(Cur1)<>0 THEN
FETCH FIRST FROM Cur1(''Msg'') INTO st;
IF St <> '''' THEN SET St = St + #13 + #10 ; END IF;
UPDATE Cur1 SET ''Msg'' = St + NewMsg;
END IF;
UNPREPARE Sql1;
END IF;
END
DESCRIPTION ''Acrescenta texto a um evento já existente na tabela "AppLog"''
VERSION 2.04';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "VerificarLCC" (IN "LogId" INTEGER, OUT "Verificados" INTEGER, OUT "ErrosResolvidos" INTEGER, OUT "ErrosNaoResolvidos" INTEGER)
BEGIN
-- Verifica a ligação da facturação às contas correntes de clientes
DECLARE Msg VARCHAR;
DECLARE N, P, NumDocSize INTEGER;
DECLARE IdDoc, St VARCHAR;
DECLARE Ano, NumLanc INTEGER;
DECLARE NumDoc, TipoLanc VARCHAR;
DECLARE Proc CHAR;
DECLARE tValor DECIMAL(19,4);
DECLARE cTipoDoc VarChar ;
DECLARE cSerieDoc VarChar ;
DECLARE cNumDoc Integer ;
DECLARE cDataDoc Date ;
DECLARE cNumCliente Integer ;
DECLARE cEstado VarChar ;
DECLARE cTotal Decimal(19, 2) ;
DECLARE cDataVencimento Date ;
DECLARE cCrDeb, cCliFor VARCHAR;
DECLARE mDiario Integer ;
DECLARE mNumLanc Integer ;
DECLARE mNumCliente Integer ;
DECLARE mDataMov Date ;
DECLARE mDescricao VarChar ;
DECLARE mAbrFecho VarChar ;
DECLARE mAnoDoc Integer ;
DECLARE mTipoDoc VarChar ;
DECLARE mSerieDoc VarChar ;
DECLARE mNumDoc VarChar ;
DECLARE mDataDoc Date ;
DECLARE mDataVencDoc Date ;
DECLARE mModoLanc VarChar ;
DECLARE mCriadoPor VarChar ;
DECLARE mDataCriado Date ;
DECLARE mAlteradoPor VarChar ;
DECLARE mDataAlterado Date ;
DECLARE mValorMov Decimal(19, 2) ;
DECLARE Cur1 INSENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
DECLARE Fac INSENSITIVE CURSOR WITHOUT RETURN FOR SqlFac;
DECLARE Mov SENSITIVE CURSOR WITHOUT RETURN FOR SqlMov;
DECLARE SqlDelMov STATEMENT ;
SET Verificados = 0;
SET ErrosResolvidos = 0;
SET ErrosNaoResolvidos = 0;
SET Msg = #13+#10+''2.Facturação: Verificação dos lançamentos nas Contas Correntes'';
SET PROGRESS TO 0;
PREPARE Sql1 FROM ''SELECT "Length" FROM Information.TableColumns WHERE TableName = ? AND Name = ?'';
OPEN Cur1 USING ''MovCli'', ''NumDoc'';
FETCH FIRST FROM Cur1 INTO NumDocSize;
CLOSE Cur1;
UNPREPARE Sql1;
PREPARE SqlFac FROM ''
SELECT
TipoDoc, SerieDoc, NumDoc, DataDoc, NumCliente, Estado, Total, DataVencimento, T.CrDeb, T.CliFor
FROM
FacCab C
LEFT OUTER JOIN TiposDoc T ON T.TipoDoc = C.TipoDoc
ORDER BY
TipoDoc, SerieDoc, NumDoc
'';
PREPARE SqlDelMov FROM ''DELETE FROM MovCli WHERE AnoDoc = ? AND TipoDoc = ? AND SerieDoc = ? AND NumDoc = ?'';
PREPARE SqlMov FROM ''
SELECT
Diario, NumLanc, NumCliente, DataMov, Descricao, AbrFecho, AnoDoc, TipoDoc, SerieDoc, NumDoc, DataDoc,
DataVencDoc, ModoLanc, CriadoPor, DataCriado, AlteradoPor, DataAlterado, ValorMov
FROM
MovCli
WHERE
AnoDoc = ? AND TipoDoc = ? AND SerieDoc = ? AND NumDoc = ? AND AbrFecho = ?
'';
OPEN Fac;
SET N = ROWCOUNT(Fac);
SET P = 2+TRUNC(N/30);
FETCH FIRST FROM Fac INTO cTipoDoc, cSerieDoc, cNumDoc, cDataDoc, cNumCliente, cEstado, cTotal, cDataVencimento, cCrDeb, cCliFor ;
WHILE NOT EOF(Fac) DO
SET IdDoc = '' ''+cTipoDoc + ''-'' + cSerieDoc + CAST(cNumDoc AS VARCHAR);
SET St = CAST(cNumDoc as VARCHAR);
SET NumDoc = REPEAT('' '' FOR NumDocSize - CHAR_LENGTH(St)) + St ;
SET Ano = EXTRACT(YEAR FROM cDataDoc);
IF (cEstado = ''ANU'') OR (cCliFor<>''C'')THEN
SET Proc = ''A'';
SET TipoLanc = '''';
ELSE
CASE cCrDeb
WHEN ''D'' THEN
SET Proc = ''L'';
SET TipoLanc = ''D'';
WHEN ''C'' THEN
SET Proc = ''L'';
SET TipoLanc = ''C'';
ELSE
SET Proc = ''A'';
SET TipoLanc = '''';
END CASE;
END IF;
IF TipoLanc = ''C'' THEN SET tValor = -cTotal ; ELSE SET tValor = cTotal ; END IF;
CASE Proc
WHEN ''A'' THEN
OPEN Mov USING Ano, cTipoDoc, cSerieDoc, NumDoc, ''A'';
IF ROWCOUNT(Mov)>0 THEN
BEGIN
FETCH FIRST FROM Mov;
DELETE FROM Mov;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado na conta corrente: Abatido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado na c.corrente, mas tem movimentos associados!'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
WHEN ''L'' THEN
OPEN Mov USING Ano, cTipoDoc, cSerieDoc, NumDoc, ''F'';
IF ROWCOUNT(Mov)>0 THEN
FETCH FIRST FROM Mov;
BEGIN
DELETE FROM Mov;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado como mov. de fecho: Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado como mov. de fecho, mas tem movimentos associados!'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
OPEN Mov USING Ano, cTipoDoc, cSerieDoc, NumDoc, ''A'';
IF ROWCOUNT(Mov) <> 0 THEN
FETCH FIRST FROM Mov INTO
mDiario, mNumLanc, mNumCliente, mDataMov, mDescricao, mAbrFecho, mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc, mDataDoc,
mDataVencDoc, mModoLanc, mCriadoPor, mDataCriado, mAlteradoPor, mDataAlterado, mValorMov ;
-- valor errado
IF tValor <> mValorMov THEN
BEGIN
UPDATE Mov SET ''ValorMov'' = tValor;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' tinha valor errado na conta corrente. Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' tinha valor errado na conta corrente. NÃO Corrigido.'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
-- cliente errado
IF cNumCliente <> mNumCliente then
BEGIN
UPDATE Mov SET ''NumCliente'' = cNumCliente;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' lançado em conta errada. Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' lançado em conta errada. NÃO Corrigido.'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
-- Data Documento/Vencimento errada
IF (cDataDoc <> mDataMov) OR (cDataDoc <> mDataDoc) OR (cDataVencimento <> mDataVencDoc) THEN
BEGIN
UPDATE Mov SET ''DataMov'' = cDataDoc, ''DataDoc''= cDataDoc, ''DataVencDoc'' = cDataVencimento;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' lançado com data errada. Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' lançado com data errada. NÃO Corrigido.'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
ELSE
-- Criar Lançamento
BEGIN
SET mNumLanc = NovaChave(''LCC99'');
IF mNumLanc IS NOT NULL THEN
INSERT
INTO Mov(
''Diario'', ''NumLanc'', ''NumCliente'', ''DataMov'', ''Descricao'', ''AbrFecho'', ''AnoDoc'', ''TipoDoc'', ''SerieDoc'', ''NumDoc'',
''DataDoc'', ''DataVencDoc'', ''ModoLanc'', ''CriadoPor'', ''DataCriado'', ''AlteradoPor'', ''DataAlterado'', ''ValorMov'')
VALUES
(99, mNumLanc, cNumCliente, cDataDoc, '''', ''A'', Ano, cTipoDoc, cSerieDoc, NumDoc,
cDataDoc, cDataVencimento, ''A'', CURRENT_USER, CURRENT_DATE, CURRENT_USER, CURRENT_DATE, tValor ) ;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' não lançado na conta corrente. Corrigido.'' ;
ELSE
RAISE ERROR CODE 10000 MESSAGE '''';
END IF;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' não lançado na conta corrente. NÃO CORRIGIDO!'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
END CASE;
SET Verificados = Verificados + 1;
IF Verificados MOD P = 0 THEN
SET PROGRESS TO TRUNC(100*Verificados/N);
END IF;
FETCH NEXT FROM Fac INTO cTipoDoc, cSerieDoc, cNumDoc, cDataDoc, cNumCliente, cEstado, cTotal, cDataVencimento, cCrDeb, cCliFor ;
END WHILE;
CLOSE Fac;
CLOSE Mov;
SET PROGRESS TO 100;
SET st =
''-> Documentos verificados:''+CAST(Verificados AS VARCHAR)+#13+#10+
''-> Erros encontrados.....:''+CAST(ErrosResolvidos+ErrosNaoResolvidos AS VARCHAR)+#13+#10+
''-> Erros não resolvidos..:''+CAST(ErrosNaoResolvidos AS VARCHAR)+#13+#10;
SET Msg = Msg +#13+#10+#13+#10+ st;
IF LogId IS NOT NULL THEN
CALL AddLogEvent(LogId, Msg);
END IF;
END
DESCRIPTION ''Verifica a ligação da facturação às contas correntes de clientes ''
VERSION 2.04';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "VerificarFac" (IN "LogID" INTEGER, OUT "Verificados" INTEGER, OUT "ErrosResolvidos" INTEGER, OUT "ErrosNaoResolvidos" INTEGER)
BEGIN
-- Verifica os valores totais das linhas e os totais dos documentos
-- Não altera valores - apenas verifica
DECLARE Msg VARCHAR;
DECLARE N, P INTEGER;
DECLARE sValorCusto DECIMAL(19,4);
DECLARE sValorMargem DECIMAL(19,4);
DECLARE sValorSemIVA DECIMAL(19,4);
DECLARE sValorComIVA DECIMAL(19,4);
DECLARE sValorIsento DECIMAL(19,4);
DECLARE sValorIncidencia DECIMAL(19,4);
DECLARE sValorIVA DECIMAL(19,4);
DECLARE sTaxas, sTaxaXP DECIMAL(19,4);
DECLARE sPenalizacao DECIMAL(19,4);
DECLARE sPenalizacaoSemIva DECIMAL(19,4);
DECLARE v1, v2 DECIMAL(19,4);
DECLARE IdDoc, st VARCHAR;
DECLARE cTipoDoc VarChar ;
DECLARE cSerieDoc VarChar ;
DECLARE cNumDoc Integer ;
DECLARE cCodMoeda VarChar ;
DECLARE cDecimais Integer ;
DECLARE cPctIva1 Decimal(19,4) ;
DECLARE cPctIva2 Decimal(19,4) ;
DECLARE cPctIva3 Decimal(19,4) ;
DECLARE cIncidencia0 Decimal(19,4) ;
DECLARE cIncidencia1 Decimal(19,4) ;
DECLARE cIncidencia2 Decimal(19,4) ;
DECLARE cIncidencia3 Decimal(19,4) ;
DECLARE cValorIva1 Decimal(19,4) ;
DECLARE cValorIva2 Decimal(19,4) ;
DECLARE cValorIva3 Decimal(19,4) ;
DECLARE cValorCusto Decimal(19,4) ;
DECLARE cValorMargem Decimal(19,4) ;
DECLARE cValorSemIva Decimal(19,4) ;
DECLARE cValorComIva Decimal(19,4) ;
DECLARE cTaxas Decimal(19,4) ;
DECLARE cTaxaXP Decimal(19,4) ;
DECLARE cPenalizacao Decimal(19,4) ;
DECLARE cValorTotal Decimal(19,4) ;
DECLARE cValorDescontos Decimal(19,4) ;
DECLARE cTotal Decimal(19,4) ;
DECLARE lLinha Integer ;
DECLARE lAnexo Integer ;
DECLARE lQt Integer ;
DECLARE lPVP Decimal(19,4) ;
DECLARE lValor Decimal(19,4) ;
DECLARE lPCusto Decimal(19,4) ;
DECLARE lMargem Decimal(19,4) ;
DECLARE lPVenda Decimal(19,4) ;
DECLARE lPctIvaPCusto Decimal(19,4) ;
DECLARE lPctIvaMargem Decimal(19,4) ;
DECLARE lTaxas Decimal(19,4) ;
DECLARE lTaxaXP Decimal(19,4) ;
DECLARE lPenalizacao Decimal(19,4) ;
DECLARE lPenalizacaoSemIva Decimal(19,4) ;
DECLARE Fac INSENSITIVE CURSOR WITHOUT RETURN FOR SqlFac;
DECLARE FacL SENSITIVE CURSOR WITHOUT RETURN FOR SqlFacL;
SET Verificados = 0;
SET ErrosResolvidos = 0;
SET ErrosNaoResolvidos = 0;
SET Msg = #13+#10+''1.Facturação: Verificação dos totais dos documentos'';
SET PROGRESS TO 0;
PREPARE SqlFac FROM ''
SELECT
TipoDoc, SerieDoc, NumDoc, C.CodMoeda, M.Decimais,
PctIva1, PctIva2, PctIva3, Incidencia0, Incidencia1, Incidencia2, Incidencia3, ValorIva1, ValorIva2, ValorIva3,
ValorCusto, ValorMargem, ValorSemIva, ValorComIva, Taxas, TaxaXP, Penalizacao, ValorTotal, Total
FROM
FacCab C
LEFT OUTER JOIN Moedas M ON M.CodMoeda = C.CodMoeda
ORDER BY
TipoDoc, SerieDoc, NumDoc
'';
PREPARE SqlFacL FROM ''
SELECT
Linha, Anexo, Qt, PVP, Valor, PCusto, Margem, PVenda, PctIvaPCusto, PctIvaMargem, Taxas, TaxaXP, Penalizacao
FROM
FacLin
WHERE
TipoDoc = ? AND SerieDoc = ? AND NumDoc = ?
ORDER BY
Linha
'';
OPEN Fac;
SET N = ROWCOUNT(Fac);
SET P = 2+TRUNC(N/30);
FETCH FIRST FROM Fac INTO
cTipoDoc, cSerieDoc, cNumDoc, cCodMoeda, cDecimais, cPctIva1, cPctIva2, cPctIva3, cIncidencia0, cIncidencia1, cIncidencia2, cIncidencia3,
cValorIva1, cValorIva2, cValorIva3, cValorCusto, cValorMargem, cValorSemIva, cValorComIva, cTaxas, cTaxaXP, cPenalizacao, cValorTotal, cTotal ;
WHILE NOT EOF(Fac) DO
SET IdDoc = '' ''+cTipoDoc + ''-'' + cSerieDoc + CAST(cNumDoc AS VARCHAR);
-- Soma das linhas
SET sValorCusto = 0;
SET sValorMargem = 0;
SET sValorSemIVA = 0;
SET sValorComIVA = 0;
SET sTaxas = 0;
SET sTaxaXP = 0;
SET sPenalizacao = 0;
SET sPenalizacaoSemIva = 0;
SET sValorIsento = 0;
SET sValorIncidencia = 0;
SET sValorIva = 0;
OPEN FacL USING cTipoDoc, cSerieDoc, cNumDoc;
FETCH FIRST FROM FacL INTO lLinha, lAnexo, lQt, lPVP, lValor, lPCusto, lMargem, lPVenda, lPctIvaPCusto, lPctIvaMargem, lTaxas, lTaxaXP, lPenalizacao;
WHILE NOT EOF(FacL) DO
IF COALESCE(lQt,0) = 0 THEN SET lQt = 1 ; END IF;
IF lPctIvaPCusto = 0 THEN
SET lPenalizacaoSemIva = lPenalizacao ;
SET sValorIsento = sValorIsento + (lQt * (lPCusto - lPenalizacao));
ELSE
SET lPenalizacaoSemIva = ROUND((100*lPenalizacao)/(100+lPctIvaPCusto), 2) ;
SET sValorIncidencia = sValorIncidencia + lQt * (lPCusto - lPenalizacaoSemIva) ;
SET sValorIva = sValorIva + (lQt*(lPCusto - lPenalizacaoSemIva)*lPctIvaPCusto)/100;
END IF;
IF lPctIvaMargem = 0 THEN
SET sValorIsento = sValorIsento + (lQt * lMargem);
ELSE
SET sValorIncidencia = sValorIncidencia + (lQt * lMargem);
SET sValorIva = sValorIva + (lQt*lMargem*lPctIvaMargem)/100;
END IF;
SET sValorCusto = sValorCusto + lPCusto * lQt ;
SET sValorMargem = sValorMargem + lMargem * lQt ;
SET sValorSemIVA = sValorSemIVA + (lPVenda - lPenalizacaoSemIva) * lQt;
SET sValorComIVA = sValorComIVA + lValor - lPenalizacao * lQt;
SET sTaxas = sTaxas + lTaxas * lQt;
SET sTaxaXP = sTaxaXP + lTaxaXP * lQt;
SET sPenalizacao = sPenalizacao + lPenalizacao * lQt ;
SET sPenalizacaoSemIva = sPenalizacaoSemIva + lPenalizacaoSemIva * lQt ;
IF ABS(lPVP * lQt - lValor) >= 0.005 THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total da linha ''+CAST(lLinha AS VARCHAR);
IF lAnexo<>0 THEN
SET st = st + ''.'' +CAST(lAnexo AS VARCHAR);
END IF;
SET st = st + '' errado: Não Resolvido (Dif=''+CAST(lPVP*lQt - lValor AS VARCHAR)+'')'';
SET Msg = Msg +#13+#10+ st;
END IF;
SET v1 = (lPCusto*(100+lPctIvaPcusto) + lMargem*(100+lPctIvaMargem))/100;
SET v2 = lPVP;
IF ABS(v1-v2)>0.01 THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Diferença de valores na linha '' + CAST(lLinha AS VARCHAR);
IF lAnexo<>0 THEN
SET st = st + ''.'' +CAST(lAnexo AS VARCHAR);
END IF;
SET st = st +'' errado: Não Resolvido (Dif=''+CAST(V1 - V2 AS VARCHAR)+'')'';
SET Msg = Msg +#13+#10+ st;
END IF;
FETCH NEXT FROM FacL INTO lLinha, lAnexo, lQt, lPVP, lValor, lPCusto, lMargem, lPVenda, lPctIvaPCusto, lPctIvaMargem, lTaxas, lTaxaXP, lPenalizacao;
END WHILE;
SET sValorSemIVA = ROUND(sValorSemIVA, 2);
SET sValorComIva = ROUND(sValorComIVA, cDecimais);
SET sTaxas = ROUND(sTaxas,cDecimais);
SET sTaxaXP = ROUND(sTaxaXP,cDecimais);
SET sPenalizacao = ROUND(sPenalizacao,cDecimais);
IF cValorCusto <> (sValorCusto - sPenalizacaoSemIva) THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total do Custo Errado (Diferença=''+CAST(cValorCusto-(sValorCusto - sPenalizacaoSemIva) AS VARCHAR)+''): Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
IF cValorComIVA <> sValorComIVA THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total do Valor C/IVA Errado : Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
IF cValorMargem <> sValorMargem THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total da Margem Errado : Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
IF cTaxas <> sTaxas THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total das Taxas Errado : Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
IF cTaxaXP <> sTaxaXP THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total das Taxas XP Errado (Diferença=''+CAST(cTaxaXP-sTaxaXP AS VARCHAR)+''): Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
IF cPenalizacao <> sPenalizacao THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total das Penalizações Errado : Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
SET v1 =cValorTotal;
SET v2 =sValorComIVA+sTaxas+sTaxaXP;
IF v1<>v2 THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Valor Total Errado: Não Resolvido. Diferença=''+CAST(v1 - v2 AS VARCHAR) ;
SET Msg = Msg +#13+#10+ st;
END IF;
IF cTotal <> (sValorComIVA+sTaxas+sTaxaXP-cValorDescontos) THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Desconto mal calculado: Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
IF cValorSemIva <> sValorSemIVA THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-Total do Valor S/IVA Errado : Não Resolvido'';
SET Msg = Msg +#13+#10+ st;
END IF;
IF cIncidencia0 <> sValorIsento THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''--Total Isento de IVA Errado : Não Resolvido. Diferença=''+CAST(cIncidencia0 - sValorIsento AS VARCHAR) ;
SET Msg = Msg +#13+#10+ st;
END IF;
SET v2 = cIncidencia1 + cIncidencia2 + cIncidencia3 ;
IF v2 <> sValorIncidencia THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-IVA:Valor de Incidencia Errado : Não Resolvido. Diferença=''+CAST(v2 - sValorIncidencia AS VARCHAR) ;
SET Msg = Msg +#13+#10+ st;
END IF;
SET v1 = sValorIva ;
SET v2 = cValorIva1 + cValorIva2 + cValorIva3 ;
IF ABS(v1 - v2) > 0.01 THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-IVA: Valor Errado : Não Resolvido . Diferença=''+CAST(v2 - v1 AS VARCHAR) ;
SET Msg = Msg +#13+#10+ st;
END IF;
SET v1 = ROUND(cValorIva1 + cValorIva2 + cValorIva3, 2);
SET v2 = sValorComIva-SValorSemIva;
IF ABS(v1-v2) > 0.1 THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+''-IVA: Total Errado : Não Resolvido. Diferença=''+CAST(v2 - v1 AS VARCHAR) ;
SET Msg = Msg +#13+#10+ st;
END IF;
SET Verificados = Verificados + 1;
IF Verificados MOD P = 0 THEN
SET PROGRESS TO TRUNC(100*Verificados/N);
END IF;
FETCH NEXT FROM Fac INTO
cTipoDoc, cSerieDoc, cNumDoc, cCodMoeda, cDecimais, cPctIva1, cPctIva2, cPctIva3, cIncidencia0, cIncidencia1, cIncidencia2, cIncidencia3,
cValorIva1, cValorIva2, cValorIva3, cValorCusto, cValorMargem, cValorSemIva, cValorComIva, cTaxas, cTaxaXP, cPenalizacao, cValorTotal, cTotal ;
END WHILE;
CLOSE Fac;
CLOSE FacL;
SET PROGRESS TO 100;
SET st =
''-> Documentos verificados:''+CAST(Verificados AS VARCHAR)+#13+#10+
''-> Erros encontrados.....:''+CAST(ErrosResolvidos+ErrosNaoResolvidos AS VARCHAR)+#13+#10+
''-> Erros não resolvidos..:''+CAST(ErrosNaoResolvidos AS VARCHAR)+#13+#10
;
SET Msg = Msg +#13+#10+#13+#10+ st;
IF LogId IS NOT NULL THEN
CALL AddLogEvent(LogId, Msg);
END IF;
END
DESCRIPTION ''Verifica os valores totais de linha e cabeçalho dos documentos de facturação. Não altera valores - apenas verifica.
-> Nas Linhas, o valor das penalizações não está incluído nos valores/preços de custo nem de vendas
-> No Cabeçalho, as penalizações estão já somadas em todos os totais
(Valores de incidência, Iva, Totais com e sem Iva, Total do documento)
''
VERSION 2.09';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "VerificarRec" (IN "LogId" INTEGER, OUT "Verificados" INTEGER, OUT "ErrosResolvidos" INTEGER, OUT "ErrosNaoResolvidos" INTEGER)
BEGIN
-- Verificação dos recibos
DECLARE Msg VARCHAR;
DECLARE N, P, NumDocSize INTEGER;
DECLARE IdDoc, St VARCHAR;
DECLARE Ano, NumLanc INTEGER;
DECLARE NumDoc VARCHAR;
DECLARE Proc CHAR;
DECLARE tValor DECIMAL(19,4);
DECLARE cSerieRecibo VarChar ;
DECLARE cNumRecibo Integer ;
DECLARE cDataRecibo Date ;
DECLARE cNumCliente Integer ;
DECLARE cCodMoeda VarChar ;
DECLARE cFormaPaga VarChar ;
DECLARE cEstado VarChar ;
DECLARE cValor Decimal(19, 2) ;
DECLARE mDiario Integer ;
DECLARE mNumLanc Integer ;
DECLARE mNumCliente Integer ;
DECLARE mDataMov Date ;
DECLARE mDescricao VarChar ;
DECLARE mAbrFecho VarChar ;
DECLARE mAnoDoc Integer ;
DECLARE mTipoDoc VarChar ;
DECLARE mSerieDoc VarChar ;
DECLARE mNumDoc VarChar ;
DECLARE mDataDoc Date ;
DECLARE mDataVencDoc Date ;
DECLARE mModoLanc VarChar ;
DECLARE mCriadoPor VarChar ;
DECLARE mDataCriado Date ;
DECLARE mAlteradoPor VarChar ;
DECLARE mDataAlterado Date ;
DECLARE mValorMov Decimal(19, 2) ;
DECLARE Cur1 INSENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
DECLARE Rec INSENSITIVE CURSOR WITHOUT RETURN FOR SqlRec;
DECLARE Mov SENSITIVE CURSOR WITHOUT RETURN FOR SqlMov;
DECLARE SqlDelMovL STATEMENT ;
SET Verificados = 0;
SET ErrosResolvidos = 0;
SET ErrosNaoResolvidos = 0;
SET Msg = #13+#10+''3.Clientes: Verificação dos recibos'';
SET PROGRESS TO 0;
-- Obter o numero de caracteres usado para guardar o numero de documento
PREPARE Sql1 FROM ''SELECT "Length" FROM Information.TableColumns WHERE TableName = ? AND Name = ?'';
OPEN Cur1 USING ''MovCli'', ''NumDoc'';
FETCH FIRST FROM Cur1 INTO NumDocSize;
CLOSE Cur1;
UNPREPARE Sql1;
PREPARE SqlRec FROM ''
SELECT
SerieRecibo, NumRecibo, DataRecibo, NumCliente, CodMoeda, Valor, Estado, FormaPaga
FROM
RecCab
ORDER BY
SerieRecibo, NumRecibo
'';
PREPARE SqlMov FROM ''
SELECT
Diario, NumLanc, NumCliente, DataMov, Descricao, AbrFecho, AnoDoc, TipoDoc, SerieDoc, NumDoc, DataDoc,
DataVencDoc, ModoLanc, CriadoPor, DataCriado, AlteradoPor, DataAlterado, ValorMov
FROM
MovCli
WHERE
AnoDoc = ? AND TipoDoc = ? AND SerieDoc = ? AND NumDoc = ? AND AbrFecho = ?
'';
PREPARE SqlDelMovL FROM ''DELETE FROM MovCliL WHERE Diario = ? AND NumLanc = ? '';
start transaction;
OPEN Rec;
SET N = ROWCOUNT(Rec);
SET P = 2+TRUNC(N/30);
FETCH FIRST FROM Rec INTO cSerieRecibo, cNumRecibo, cDataRecibo, cNumCliente, cCodMoeda, cValor, cEstado, cFormaPaga ;
WHILE NOT EOF(Rec) DO
SET IdDoc = '' NRE-'' + cSerieRecibo + CAST(cNumRecibo AS VARCHAR);
SET St = CAST(cNumRecibo as VARCHAR);
SET NumDoc = REPEAT('' '' FOR NumDocSize - CHAR_LENGTH(St)) + St ;
SET Ano = EXTRACT(YEAR FROM cDataRecibo);
IF (cEstado = ''ANU'') THEN
SET Proc = ''A'';
ELSE
SET Proc = ''L'';
END IF;
SET tValor = COALESCE(-cValor,0) ;
CASE Proc
WHEN ''A'' THEN
-- Recibo anulado
OPEN Mov USING Ano, ''NRE'', cSerieRecibo, NumDoc, ''F'';
IF ROWCOUNT(Mov)>0 THEN
BEGIN
FETCH FIRST FROM Mov(''Diario'', ''NumLanc'') INTO mDiario, mNumLanc;
EXECUTE SqlDelMovL USING mDiario, mNumLanc;
DELETE FROM Mov;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado na conta corrente: Abatido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado na c.corrente, mas não foi possível apagar!'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
WHEN ''L'' THEN
-- Recibo não pode ser mov. de abertura
OPEN Mov USING Ano, ''NRE'', cSerieRecibo, NumDoc, ''A'';
IF ROWCOUNT(Mov)>0 THEN
FETCH FIRST FROM Mov(''Diario'', ''NumLanc'') INTO mDiario, mNumLanc;
BEGIN
EXECUTE SqlDelMovL USING mDiario, mNumLanc;
DELETE FROM Mov;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado como mov. de abertura: Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' indevidamente lançado como mov. de abertura, mas não foi possível apagar!'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
OPEN Mov USING Ano, ''NRE'', cSerieRecibo, NumDoc, ''F'';
IF ROWCOUNT(Mov) <> 0 THEN
FETCH FIRST FROM Mov INTO
mDiario, mNumLanc, mNumCliente, mDataMov, mDescricao, mAbrFecho, mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc, mDataDoc,
mDataVencDoc, mModoLanc, mCriadoPor, mDataCriado, mAlteradoPor, mDataAlterado, mValorMov ;
-- valor errado
IF mValorMov IS NULL OR (tValor <> mValorMov) THEN
BEGIN
UPDATE Mov SET ''ValorMov'' = tValor;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' tinha valor errado na conta corrente. Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' tinha valor errado na conta corrente. NÃO Corrigido.'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
-- falta cliente
IF cNumCliente IS NULL THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' não tem nº de cliente. NÃO RESOLVIDO!'' ;
SET Msg = Msg +#13+#10+ st;
END IF;
-- cliente errado
IF mNumCliente IS NULL OR (cNumCliente <> mNumCliente) then
BEGIN
UPDATE Mov SET ''NumCliente'' = cNumCliente;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' lançado em conta errada. Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' lançado em conta errada. NÃO Corrigido.'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
-- Data Documento/Vencimento errada
IF mDataMov IS NULL OR mDataDoc IS NULL OR mDataVencDoc IS NULL OR
(cDataRecibo <> mDataMov) OR (cDataRecibo <> mDataDoc) OR (cDataRecibo <> mDataVencDoc) THEN
BEGIN
UPDATE Mov SET ''DataMov'' = cDataRecibo, ''DataDoc'' = cDataRecibo, ''DataVencDoc'' = cDataRecibo ;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' lançado com data errada. Corrigido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' lançado com data errada. NÃO Corrigido.'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
ELSE
-- Criar Lançamento
BEGIN
SET mDiario = NULL;
SET mNumLanc = NovaChave(''LCC98'');
IF mNumLanc IS NOT NULL THEN
INSERT
INTO Mov(
''Diario'', ''NumLanc'', ''NumCliente'', ''DataMov'', ''Descricao'', ''AbrFecho'', ''AnoDoc'', ''TipoDoc'', ''SerieDoc'', ''NumDoc'',
''DataDoc'', ''DataVencDoc'', ''ModoLanc'', ''CriadoPor'', ''DataCriado'', ''AlteradoPor'', ''DataAlterado'', ''ValorMov'')
VALUES
(98, mNumLanc, cNumCliente, cDataRecibo, '''', ''F'', Ano, ''NRE'', cSerieRecibo, NumDoc,
cDataRecibo, cDataRecibo, ''A'', CURRENT_USER, CURRENT_DATE, CURRENT_USER, CURRENT_DATE, tValor ) ;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' não lançado na conta corrente. Corrigido.'' ;
SET mDiario = 98;
ELSE
RAISE ERROR CODE 10000 MESSAGE '''';
END IF;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' não lançado na conta corrente. NÃO CORRIGIDO!'' ;
END;
SET Msg = Msg +#13+#10+ st;
END IF;
-- apagar e recriar todas as linhas
IF mDiario IS NOT NULL THEN
EXECUTE SqlDelMovL USING mDiario, mNumLanc;
EXECUTE IMMEDIATE ''
INSERT INTO
MovCliL (Diario, NumLanc, Linha, TipoLanc, CodEntidade, AnoDoc, TipoDoc, SerieDoc, NumDoc, DataDoc, Valor )
SELECT
98, ''+CAST(mNumlanc AS VARCHAR)+'', Linha, TipoLanc, CodEntidade, AnoDoc, TipoDoc, SerieDoc, NumDoc, DataDoc, Valor
FROM RecLin
WHERE SerieRecibo = ''''''+cSerieRecibo+'''''' AND NumRecibo = ''+CAST(cNumRecibo AS VARCHAR);
END IF;
END CASE;
SET Verificados = Verificados + 1;
IF Verificados MOD P = 0 THEN
SET PROGRESS TO TRUNC(100*Verificados/N);
END IF;
FETCH NEXT FROM Rec INTO cSerieRecibo, cNumRecibo, cDataRecibo, cNumCliente, cCodMoeda, cValor, cEstado, cFormaPaga ;
END WHILE;
CLOSE Rec;
CLOSE Mov;
commit;
SET PROGRESS TO 100;
SET st =
''-> Recibos verificados...:''+CAST(Verificados AS VARCHAR)+#13+#10+
''-> Erros encontrados.....:''+CAST(ErrosResolvidos+ErrosNaoResolvidos AS VARCHAR)+#13+#10+
''-> Erros não resolvidos..:''+CAST(ErrosNaoResolvidos AS VARCHAR)+#13+#10;
SET Msg = Msg +#13+#10+#13+#10+ st;
IF LogId IS NOT NULL THEN
CALL AddLogEvent(LogId, Msg);
END IF;
END
DESCRIPTION ''Verifica a ligação dos recibos às contas correntes de clientes ''
VERSION 2.04';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "VerificarSC" (IN "LogId" INTEGER, OUT "Verificados" INTEGER, OUT "ErrosResolvidos" INTEGER, OUT "ErrosNaoResolvidos" INTEGER)
BEGIN
-- Verifica os saldos de clientes
DECLARE Msg VARCHAR;
DECLARE N, P INTEGER;
DECLARE IdDoc, St VARCHAR;
DECLARE cNumCliente Integer ;
DECLARE cSaldo Decimal(19, 2) ;
DECLARE mSaldo Decimal(19, 2) ;
DECLARE Cli SENSITIVE CURSOR WITHOUT RETURN FOR SqlCli;
DECLARE Mov INSENSITIVE CURSOR WITHOUT RETURN FOR SqlMov;
SET Verificados = 0;
SET ErrosResolvidos = 0;
SET ErrosNaoResolvidos = 0;
SET Msg = #13+#10+''4.Clientes: Verificação dos saldos'';
SET PROGRESS TO 0;
PREPARE SqlCli FROM ''SELECT NumCliente, Saldo FROM Clientes ORDER BY NumCliente'';
PREPARE SqlMov FROM ''SELECT SUM(ValorMov) AS Saldo FROM MovCli WHERE NumCliente = ? '';
OPEN Cli;
SET N = ROWCOUNT(Cli);
SET P = 2+TRUNC(N/30);
FETCH FIRST FROM Cli INTO cNumCliente, cSaldo ;
WHILE NOT EOF(Cli) DO
SET IdDoc = '' Cliente '' + CAST(cNumCliente AS VARCHAR)+'' :'' ;
OPEN Mov USING cNumCliente;
IF ROWCOUNT(Mov)<>0 THEN
FETCH FIRST FROM Mov INTO mSaldo ;
END IF;
SET mSaldo = COALESCE(mSaldo, 0);
IF cSaldo IS NULL OR (cSaldo <> mSaldo) THEN
BEGIN
UPDATE Cli SET ''Saldo'' = mSaldo ;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' Saldo Errado: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' Saldo Errado: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
END IF;
SET Verificados = Verificados + 1;
IF Verificados MOD P = 0 THEN
SET PROGRESS TO TRUNC(100*Verificados/N);
END IF;
FETCH NEXT FROM Cli INTO cNumCliente, cSaldo ;
END WHILE;
CLOSE Cli;
CLOSE Mov;
SET PROGRESS TO 100;
SET st =
''-> Clientes verificados..:''+CAST(Verificados AS VARCHAR)+#13+#10+
''-> Erros encontrados.....:''+CAST(ErrosResolvidos+ErrosNaoResolvidos AS VARCHAR)+#13+#10+
''-> Erros não resolvidos..:''+CAST(ErrosNaoResolvidos AS VARCHAR)+#13+#10 ;
SET Msg = Msg +#13+#10+#13+#10+ st;
IF LogId IS NOT NULL THEN
CALL AddLogEvent(LogId, Msg);
END IF;
END
DESCRIPTION ''Verifica os saldos de clientes ''
VERSION 2.04';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "VerificarDAC" (IN "LogId" INTEGER, OUT "Verificados" INTEGER, OUT "ErrosResolvidos" INTEGER, OUT "ErrosNaoResolvidos" INTEGER)
BEGIN
-- Verifica os documentos em aberto de clientes
DECLARE Msg VARCHAR;
DECLARE N, P INTEGER;
DECLARE IdDoc, St VARCHAR;
DECLARE VFechos Decimal(19, 2) ;
DECLARE VAberto Decimal(19, 2) ;
DECLARE aAnoDoc Integer ;
DECLARE aTipoDoc VarChar ;
DECLARE aSerieDoc VarChar ;
DECLARE aNumDoc VarChar ;
DECLARE aNumCliente Integer ;
DECLARE aDataDoc Date ;
DECLARE aDataVencimento Date ;
DECLARE aValorAberto Decimal(19, 2) ;
DECLARE aValorInicial Decimal(19, 2) ;
DECLARE mNumCliente Integer ;
DECLARE mDataMov Date ;
DECLARE mAbrFecho VarChar ;
DECLARE mAnoDoc Integer ;
DECLARE mTipoDoc VarChar ;
DECLARE mSerieDoc VarChar ;
DECLARE mNumDoc VarChar ;
DECLARE mDataDoc Date ;
DECLARE mDataVencDoc Date ;
DECLARE mValorMov Decimal(19, 2) ;
DECLARE DAC SENSITIVE CURSOR WITHOUT RETURN FOR SqlDAC;
DECLARE QDAC SENSITIVE CURSOR WITHOUT RETURN FOR SqlQDAC;
DECLARE Mov INSENSITIVE CURSOR WITHOUT RETURN FOR SqlMov;
DECLARE MovL INSENSITIVE CURSOR WITHOUT RETURN FOR SqlMovL;
DECLARE SqlDelDAC STATEMENT ;
SET Verificados = 0;
SET ErrosResolvidos = 0;
SET ErrosNaoResolvidos = 0;
SET Msg = #13+#10+''5.Clientes: Verificação dos documentos em aberto'';
SET PROGRESS TO 0;
PREPARE SqlDAC FROM ''
SELECT
AnoDoc, TipoDoc, SerieDoc, NumDoc
FROM
DocsAbrCli
ORDER BY
AnoDoc, TipoDoc, SerieDoc, NumDoc
'';
PREPARE SqlDelDAC FROM ''DELETE FROM DocsAbrCli WHERE AnoDoc = ? AND TipoDoc = ? AND SerieDoc = ? AND NumDoc = ?'';
PREPARE SqlQDAC FROM ''
SELECT
AnoDoc, TipoDoc, SerieDoc, NumDoc, NumCliente, DataDoc, DataVencimento, ValorAberto, ValorInicial
FROM
DocsAbrCli
WHERE
AnoDoc = ? AND TipoDoc = ? AND SerieDoc = ? AND NumDoc = ?
ORDER BY
AnoDoc, TipoDoc, SerieDoc, NumDoc
'';
PREPARE SqlMov FROM ''
SELECT
NumCliente, DataMov, AbrFecho, AnoDoc, TipoDoc, SerieDoc, NumDoc, DataDoc, DataVencDoc, ValorMov
FROM
MovCli
WHERE
AbrFecho = ''''A''''
ORDER BY
AnoDoc, TipoDoc, SerieDoc, NumDoc
'';
PREPARE SqlMovL FROM ''
SELECT SUM(Valor) AS Fechos FROM MovCliL WHERE AnoDoc = ? AND TipoDoc = ? AND SerieDoc = ? AND NumDoc = ? AND TipoLanc = ''''F''''
'';
OPEN Mov;
OPEN DAC;
SET N = ROWCOUNT(Mov) + ROWCOUNT(DAC);
SET P = 2+TRUNC(N/30);
FETCH FIRST
FROM Mov(''NumCliente'', ''DataMov'', ''AbrFecho'', ''AnoDoc'', ''TipoDoc'', ''SerieDoc'', ''NumDoc'', ''DataDoc'', ''DataVencDoc'', ''ValorMov'')
INTO mNumCliente, mDataMov, mAbrFecho, mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc, mDataDoc, mDataVencDoc, mValorMov ;
WHILE NOT EOF(Mov) DO
SET IdDoc = '' ''+mTipoDoc + ''-'' + mSerieDoc +TRIM(BOTH '' '' FROM mNumDoc);
OPEN MovL USING mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc;
IF ROWCOUNT(MovL)<>0 THEN
FETCH FIRST FROM MovL INTO VFechos ;
END IF;
SET VFechos = COALESCE(VFechos, 0);
SET VAberto = COALESCE(mValorMov, 0) - VFechos ;
IF ABS(VAberto) < 0.01 THEN SET VAberto = 0 ; END IF;
IF VAberto = 0 THEN
BEGIN
EXECUTE SqlDelDAC USING mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc;
IF ROWSAFFECTED(SqlDelDAC)>0 THEN
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' Mal Lançado em Doc.Aberto: Abatido'' ;
SET Msg = Msg + #13+#10 + st;
END IF;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' Mal Lançado em Doc.Aberto: NÃO Resolvido!'' ;
SET Msg = Msg + #13+#10 + st;
END;
ELSE
OPEN QDAC USING mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc;
IF ROWCOUNT(QDAC) = 0 THEN
BEGIN
INSERT INTO QDAC VALUES (mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc, mNumCliente, mDataDoc, mDataVencDoc, VAberto, mValorMov) ;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' Não lançado em Doc.Aberto: Corrigido'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' Não lançado em Doc.Aberto: NÃO Corrigido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
ELSE
FETCH FIRST
FROM QDAC(''NumCliente'', ''DataDoc'', ''DataVencimento'', ''ValorAberto'', ''ValorInicial'')
INTO aNumCliente, aDataDoc, aDataVencimento, aValorAberto, aValorInicial;
IF aNumCliente<>mNumCliente OR aDataDoc<>mDataDoc OR aDataVencimento<>mDataVencDoc OR aValorAberto<>VAberto OR aValorInicial<>mValorMov OR
aNumCliente IS NULL OR aDataDoc IS NULL OR aValorAberto IS NULL OR aDataVencimento IS NULL OR aValorInicial IS NULL THEN
BEGIN
UPDATE QDAC SET ''NumCliente'' = mNumCliente, ''DataDoc'' = mDataDoc, ''DataVencimento'' = mDataVencDoc, ''ValorAberto'' = VAberto, ''ValorInicial'' = mValorMov ;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' Valores errados em Doc.Aberto: Corrigido'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' Valores errados em Doc.Aberto: NÃO Corrigido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
END IF;
END IF;
END IF;
SET Verificados = Verificados + 1;
IF Verificados MOD P = 0 THEN
SET PROGRESS TO TRUNC(100*Verificados/N);
END IF;
FETCH NEXT
FROM Mov(''NumCliente'', ''DataMov'', ''AbrFecho'', ''AnoDoc'', ''TipoDoc'', ''SerieDoc'', ''NumDoc'', ''DataDoc'', ''DataVencDoc'', ''ValorMov'')
INTO mNumCliente, mDataMov, mAbrFecho, mAnoDoc, mTipoDoc, mSerieDoc, mNumDoc, mDataDoc, mDataVencDoc, mValorMov ;
END WHILE;
CLOSE QDAC;
CLOSE Mov;
UNPREPARE SqlMov;
PREPARE SqlMov FROM ''
SELECT
NumCliente, DataMov, AbrFecho, AnoDoc, TipoDoc, SerieDoc, NumDoc, DataDoc, DataVencDoc, ValorMov
FROM
MovCli
WHERE
AnoDoc = ? AND TipoDoc = ? AND SerieDoc = ? AND NumDoc = ? AND AbrFecho = ''''A''''
ORDER BY
AnoDoc, TipoDoc, SerieDoc, NumDoc
'';
FETCH FIRST FROM DAC INTO aAnoDoc, aTipoDoc, aSerieDoc, aNumDoc ;
WHILE NOT EOF(DAC) DO
SET IdDoc = aTipoDoc + ''-'' + aSerieDoc +TRIM(BOTH '' '' FROM aNumDoc);
OPEN Mov USING aAnoDoc, aTipoDoc, aSerieDoc, aNumDoc;
IF ROWCOUNT(Mov)=0 THEN
BEGIN
DELETE FROM DAC;
FETCH RELATIVE 0 FROM DAC INTO aAnoDoc, aTipoDoc, aSerieDoc, aNumDoc ;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = IdDoc+'' não tem movimento de abertura: Abatido'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = IdDoc+'' não tem movimento de abertura: NÃO Resolvido!'' ;
FETCH NEXT FROM DAC INTO aAnoDoc, aTipoDoc, aSerieDoc, aNumDoc ;
END;
SET Msg = Msg + #13+#10 + st;
ELSE
FETCH NEXT FROM DAC INTO aAnoDoc, aTipoDoc, aSerieDoc, aNumDoc ;
END IF;
SET Verificados = Verificados + 1;
IF Verificados MOD P = 0 THEN
SET PROGRESS TO TRUNC(100*Verificados/N);
END IF;
END WHILE;
CLOSE DAC;
CLOSE Mov;
SET PROGRESS TO 100;
SET st =
''-> Registos verificados..:''+CAST(Verificados AS VARCHAR)+#13+#10+
''-> Erros encontrados.....:''+CAST(ErrosResolvidos+ErrosNaoResolvidos AS VARCHAR)+#13+#10+
''-> Erros não resolvidos..:''+CAST(ErrosNaoResolvidos AS VARCHAR)+#13+#10;
SET Msg = Msg +#13+#10+#13+#10+ st;
IF LogId IS NOT NULL THEN
CALL AddLogEvent(LogId, Msg);
END IF;
END
DESCRIPTION ''Verifica os documento em aberto de clientes''
VERSION 2.04';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "VerificarSN" (IN "LogId" INTEGER, OUT "Verificados" INTEGER, OUT "ErrosResolvidos" INTEGER, OUT "ErrosNaoResolvidos" INTEGER)
BEGIN
-- Verifica as sequências de numeração
DECLARE Msg VARCHAR;
DECLARE N INTEGER; -- Nº total de sequencias
DECLARE Delegacao INTEGER;
DECLARE Series VARCHAR;
DECLARE NumCli1, NumCli2, MaxNum, Num INTEGER;
DECLARE St VARCHAR;
DECLARE TDoc, SDoc, StDiario VARCHAR;
DECLARE I,J,Diario INTEGER;
DECLARE TDocV VARCHAR DEFAULT ''NFA,NFP,NNC,NND,NNR,NVD,NCI'';
DECLARE TDocC VARCHAR DEFAULT ''VFA,VNC,VND,VNR,VVD'';
DECLARE Diarios VARCHAR DEFAULT ''00,01,98,99'';
DECLARE cId Varchar ;
DECLARE cValor Integer ;
DECLARE Chaves SENSITIVE CURSOR WITHOUT RETURN FOR SqlChaves;
DECLARE Cur1 INSENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
SET Verificados = 0;
SET ErrosResolvidos = 0;
SET ErrosNaoResolvidos = 0;
SET Msg = #13+#10+''6.Sequências de numeração'';
SET PROGRESS TO 0;
SET St = GetDbParam(CURRENT_DATABASE, ''Delegacao'');
IF St = '''' THEN SET St=''0''; END IF;
SET Delegacao = CAST(St AS INTEGER);
SET Series= '''' ;
SET NumCli1 = 2;
SET NumCli2 = 999999;
SET N = 58;
PREPARE SqlChaves FROM ''SELECT Id, Valor FROM Chaves WHERE Id = ? ORDER BY Id'';
PREPARE Sql1 FROM ''SELECT Serie FROM Series ORDER BY Serie'';
OPEN Cur1;
FETCH FIRST FROM Cur1 INTO St;
WHILE NOT EOF(Cur1) DO
SET Series = Series + St;
FETCH NEXT FROM Cur1 INTO St;
END WHILE;
UNPREPARE Sql1;
IF Series='''' THEN SET Series='' ''; END IF;
-- Clientes
PREPARE Sql1 FROM ''SELECT NumClienteInicial, NumClienteFinal FROM Delegacoes WHERE CodDelegacao = ?'';
OPEN Cur1 USING Delegacao;
IF ROWCOUNT(Cur1) > 0 THEN
FETCH FIRST FROM Cur1 INTO NumCli1, NumCli2;
SET NumCli1 = COALESCE(NumCli1, 2);
SET NumCli2 = COALESCE(NumCli2, 999999);
END IF;
UNPREPARE Sql1;
PREPARE Sql1 FROM ''SELECT Max(NumCliente) AS MaxCli FROM Clientes WHERE NumCliente BETWEEN ? AND ?'';
OPEN Cur1 USING NumCli1, NumCli2;
IF ROWCOUNT(Cur1) > 0 THEN
FETCH FIRST FROM Cur1 INTO MaxNum;
SET MaxNum = COALESCE(MaxNum, 0);
ELSE
SET MaxNum = NumCli1 - 1;
END IF;
UNPREPARE Sql1;
OPEN Chaves USING ''CLI'';
IF ROWCOUNT(Chaves) = 0 THEN
BEGIN
INSERT INTO Chaves(''Id'', ''Valor'') VALUES (''CLI'',MaxNum);
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = ''Clientes - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = ''Clientes - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
ELSE
FETCH FIRST FROM Chaves(''Valor'') INTO cValor;
SET MaxNum = COALESCE(MaxNum, 0);
IF cValor IS NULL OR (cValor <> MaxNum) THEN
BEGIN
UPDATE Chaves SET ''Valor'' = MaxNum;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = ''Clientes - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = ''Clientes - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
END IF;
END IF;
SET Verificados = Verificados + 1;
SET PROGRESS TO TRUNC(100*Verificados/N);
-- Documentos de facturação - verificação da numeração
PREPARE Sql1 FROM ''SELECT NumDoc FROM FacCab WHERE TipoDoc = ? AND SerieDoc = ? ORDER BY NumDoc'';
SET I=1 ;
WHILE I < LENGTH(TDocV) DO
SET TDoc = SUBSTRING(TDocV,I,3) ;
SET J=1 ;
WHILE J <= LENGTH(Series) DO
SET SDoc = TRIM(BOTH '' '' FROM SUBSTRING(Series,J,1)) ;
-- Verificar continuidade da numeração e determinar maior numero
OPEN Cur1 USING TDoc, SDoc;
FETCH FIRST FROM Cur1 INTO Num;
SET MaxNum = COALESCE(Num, 1) - 1;
WHILE NOT EOF(Cur1) DO
IF Num <> (MaxNum+1) THEN
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = TDoc+SDoc+CAST(Num AS VARCHAR)+'' - Detectado Salto de Numeração: NÃO Resolvido!'' ;
SET Msg = Msg + #13+#10 + st;
END IF ;
SET MaxNum = Num ;
FETCH NEXT FROM Cur1 INTO Num;
END WHILE ;
CLOSE Cur1 ;
-- Verificar proxima chave
OPEN Chaves USING TDoc+SDoc ;
IF ROWCOUNT(Chaves) = 0 THEN
BEGIN
INSERT INTO Chaves(''Id'', ''Valor'') VALUES (TDoc+SDoc, 0);
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
ELSE
FETCH FIRST FROM Chaves(''Valor'') INTO cValor;
IF cValor IS NULL OR (cValor <> MaxNum) THEN
BEGIN
UPDATE Chaves SET ''Valor'' = MaxNum;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
END IF;
END IF;
SET Verificados = Verificados + 1;
SET PROGRESS TO TRUNC(100*Verificados/N);
SET J = J+1;
END WHILE;
SET I = I+4;
END WHILE;
UNPREPARE Sql1;
-- Recibos
PREPARE Sql1 FROM ''SELECT Max(NumRecibo) AS MaxNum FROM RecCab WHERE SerieRecibo = ?'';
SET TDoc = ''NRE'';
SET J=1 ;
WHILE J <= LENGTH(Series) DO
SET SDoc = TRIM(BOTH '' '' FROM SUBSTRING(Series,J,1)) ;
OPEN Chaves USING TDoc+SDoc ;
IF ROWCOUNT(Chaves) = 0 THEN
BEGIN
INSERT INTO Chaves(''Id'', ''Valor'') VALUES (TDoc+SDoc, 0);
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
ELSE
OPEN Cur1 USING SDoc;
IF ROWCOUNT(Cur1) > 0 THEN
FETCH FIRST FROM Cur1 INTO MaxNum;
ELSE
SET MaxNum = 0;
END IF;
SET MaxNum = COALESCE(MaxNum, 0);
FETCH FIRST FROM Chaves(''Valor'') INTO cValor;
IF cValor IS NULL OR (cValor <> MaxNum) THEN
BEGIN
UPDATE Chaves SET ''Valor'' = MaxNum;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = TDoc+SDoc+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
END IF;
END IF;
SET Verificados = Verificados + 1;
SET PROGRESS TO TRUNC(100*Verificados/N);
SET J = J+1;
END WHILE;
UNPREPARE Sql1;
-- Contas correntes de clientes
PREPARE Sql1 FROM ''SELECT Max(NumLanc) AS MaxNum FROM MovCli WHERE Diario = ?'';
SET J=1 ;
WHILE J < LENGTH(Diarios) DO
SET StDiario = SUBSTRING(Diarios,J,2);
SET Diario = CAST(StDiario AS INTEGER) ;
OPEN Chaves USING ''LCC''+StDiario ;
IF ROWCOUNT(Chaves) = 0 THEN
BEGIN
INSERT INTO Chaves(''Id'', ''Valor'') VALUES (''LCC''+StDiario, 0);
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = ''C.C.Clientes, Diario ''+StDiario+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = ''C.C.Clientes, Diario ''+StDiario+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
ELSE
OPEN Cur1 USING Diario;
IF ROWCOUNT(Cur1) > 0 THEN
FETCH FIRST FROM Cur1 INTO MaxNum;
ELSE
SET MaxNum = 0;
END IF;
SET MaxNum = COALESCE(MaxNum, 0);
FETCH FIRST FROM Chaves(''Valor'') INTO cValor;
IF cValor IS NULL OR (cValor <> MaxNum) THEN
BEGIN
UPDATE Chaves SET ''Valor'' = MaxNum;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = ''C.C.Clientes, Diario ''+StDiario+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = ''C.C.Clientes, Diario ''+StDiario+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
END IF;
END IF;
SET Verificados = Verificados + 1;
SET PROGRESS TO TRUNC(100*Verificados/N);
SET J = J+3;
END WHILE;
UNPREPARE Sql1;
-- Documentos de compra
PREPARE Sql1 FROM ''SELECT Max(NumReg) AS MaxNum FROM ComCab WHERE TipoDoc = ?'';
SET I=1 ;
WHILE I < LENGTH(TDocC) DO
SET TDoc = SUBSTRING(TDocC,I,3) ;
OPEN Chaves USING TDoc ;
IF ROWCOUNT(Chaves) = 0 THEN
BEGIN
INSERT INTO Chaves(''Id'', ''Valor'') VALUES (TDoc, 0);
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = TDoc+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = TDoc+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
ELSE
OPEN Cur1 USING TDoc, SDoc;
IF ROWCOUNT(Cur1) > 0 THEN
FETCH FIRST FROM Cur1 INTO MaxNum;
ELSE
SET MaxNum = 0;
END IF;
SET MaxNum = COALESCE(MaxNum, 0);
FETCH FIRST FROM Chaves(''Valor'') INTO cValor;
IF cValor IS NULL OR (cValor <> MaxNum) THEN
BEGIN
UPDATE Chaves SET ''Valor'' = MaxNum;
SET ErrosResolvidos = ErrosResolvidos + 1;
SET st = TDoc+'' - Numeração errada: Resolvido.'' ;
EXCEPTION
SET ErrosNaoResolvidos = ErrosNaoResolvidos + 1;
SET st = TDoc+'' - Numeração errada: NÃO Resolvido!'' ;
END;
SET Msg = Msg + #13+#10 + st;
END IF;
END IF;
SET Verificados = Verificados + 1;
SET PROGRESS TO TRUNC(100*Verificados/N);
SET I = I+4;
END WHILE;
UNPREPARE Sql1;
SET PROGRESS TO 100;
SET st =
''-> Sequencias verificadas:''+CAST(Verificados AS VARCHAR)+ #13+#10 +
''-> Erros encontrados.....:''+CAST(ErrosResolvidos+ErrosNaoResolvidos AS VARCHAR)+ #13+#10 +
''-> Erros não resolvidos..:''+CAST(ErrosNaoResolvidos AS VARCHAR)+#13+#10;
SET Msg = Msg +#13+#10+#13+#10+ st;
IF LogId IS NOT NULL THEN
CALL AddLogEvent(LogId, Msg);
END IF;
END
DESCRIPTION ''Verifica e corrige as sequências de numeração''
VERSION 2.06';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "Diagnostico" ()
BEGIN
DECLARE Verificados INTEGER;
DECLARE ErrosResolvidos INTEGER;
DECLARE ErrosNaoResolvidos INTEGER;
DECLARE NVer, NERes, NENaoRes INTEGER;
DECLARE EventID INTEGER;
DECLARE IdBd, Msg VARCHAR;
SET IdBd = CURRENT_DATABASE ;
SET PROGRESS TO 0 ;
CALL NewLogEvent(EventID, ''Diagnóstico'', IdBd);
SET Msg =
''* DIAGNÓSTICO DA BASE DE DADOS "''+IdBd+''"''+#13+#10+
''* INICIADO EM: ''+CAST(CURRENT_TIMESTAMP AS CHAR(19))+#13+#10 ;
CALL AddLogEvent(EventID, Msg);
SET Verificados = 0;
SET ErrosResolvidos = 0;
SET ErrosNaoResolvidos =0;
SET PROGRESS TO 10 ;
CALL VerificarFac(EventID, NVer, NERes, NENaoRes);
SET Verificados = Verificados + NVer;
SET ErrosResolvidos = ErrosResolvidos + NERes;
SET ErrosNaoResolvidos = ErrosNaoResolvidos + NENaoRes;
SET PROGRESS TO 20 ;
CALL VerificarLCC(EventID, NVer, NERes, NENaoRes);
SET Verificados = Verificados + NVer;
SET ErrosResolvidos = ErrosResolvidos + NERes;
SET ErrosNaoResolvidos = ErrosNaoResolvidos + NENaoRes;
SET PROGRESS TO 30 ;
CALL VerificarRec(EventID, NVer, NERes, NENaoRes);
SET Verificados = Verificados + NVer;
SET ErrosResolvidos = ErrosResolvidos + NERes;
SET ErrosNaoResolvidos = ErrosNaoResolvidos + NENaoRes;
SET PROGRESS TO 50 ;
CALL VerificarSC(EventID, NVer, NERes, NENaoRes);
SET Verificados = Verificados + NVer;
SET ErrosResolvidos = ErrosResolvidos + NERes;
SET ErrosNaoResolvidos = ErrosNaoResolvidos + NENaoRes;
SET PROGRESS TO 60 ;
CALL VerificarDAC(EventID, NVer, NERes, NENaoRes);
SET Verificados = Verificados + NVer;
SET ErrosResolvidos = ErrosResolvidos + NERes;
SET ErrosNaoResolvidos = ErrosNaoResolvidos + NENaoRes;
SET PROGRESS TO 80 ;
CALL VerificarSN(EventID, NVer, NERes, NENaoRes);
SET Verificados = Verificados + NVer;
SET ErrosResolvidos = ErrosResolvidos + NERes;
SET ErrosNaoResolvidos = ErrosNaoResolvidos + NENaoRes;
SET PROGRESS TO 90 ;
SET Msg =
#13+#10+
''* FIM DO DIAGNÓSTICO ----------------------------''+#13+#10+
''* Total de erros........:''+CAST(ErrosNaoResolvidos+ErrosResolvidos AS VARCHAR)+#13+#10+
''* Erros não resolvidos..:''+CAST(ErrosNaoResolvidos AS VARCHAR)+#13+#10+
''* DIAGNÓSTICO TERMINADO EM ''+CAST(CURRENT_TIMESTAMP AS CHAR(19))+#13+#10 ;
CALL AddLogEvent(EventID, Msg);
SET PROGRESS TO 100 ;
END
DESCRIPTION ''Faz o diagnóstico completo da base de dados.
Gera um relatório na tabela "AppLog"
Utiliza:
-AddLogEvent
-NewLogEvent
-VerificarFac
-VerificarLLC
-VerificarRec
-VerificarSC
-VerificarDAC
-VerificarSN''
VERSION 1';
EXECUTE IMMEDIATE 'CREATE PROCEDURE "VerificarBD" ()
BEGIN
-- Verifica todas as tabelas da base de dados
DECLARE TblName VARCHAR;
DECLARE N INTEGER;
DECLARE I INTEGER;
DECLARE Tbls INSENSITIVE CURSOR WITHOUT RETURN FOR SqlTbls ;
PREPARE SqlTbls FROM ''
SELECT Name FROM Information.Tables ORDER BY Name
'' ;
SET PROGRESS TO 0 ;
OPEN Tbls ;
SET N = ROWCOUNT(Tbls) ;
SET I = 0;
FETCH FIRST FROM Tbls INTO TblName ;
WHILE NOT EOF(Tbls) DO
EXECUTE IMMEDIATE ''VERIFY TABLE "''+ TblName +''"'' ;
SET I = I + 1;
SET PROGRESS TO TRUNC(100*I/N);
FETCH NEXT FROM Tbls INTO TblName ;
END WHILE ;
CLOSE Tbls;
END
DESCRIPTION ''Faz a verificação de todas as tabelas da base de dados''
VERSION 1';
/************************************************************
* Table triggers, indexes, and constraints
************************************************************/
-- Creating indexes for Clientes table
EXECUTE IMMEDIATE 'CREATE INDEX "Nome" ON "Clientes" ("Nome" COLLATE "PTG_CI_AI")';
-- Creating indexes for DadosPessoais table
EXECUTE IMMEDIATE 'CREATE INDEX "IdFiscal" ON "DadosPessoais" ("PrefIdFiscal" COLLATE "PTG", "IdFiscal" COLLATE "PTG")';
EXECUTE IMMEDIATE 'CREATE INDEX "Nome" ON "DadosPessoais" ("Nome" COLLATE "PTG_CI_AI")';
EXECUTE IMMEDIATE 'CREATE INDEX "NomeAV" ON "DadosPessoais" ("NomeAV" COLLATE "PTG")';
-- Creating indexes for DocsAbrCli table
EXECUTE IMMEDIATE 'CREATE INDEX "NumCliente" ON "DocsAbrCli" ("NumCliente", "DataVencimento")';
EXECUTE IMMEDIATE 'CREATE INDEX "NumDoc" ON "DocsAbrCli" ("NumDoc" COLLATE "PTG")';
-- Creating indexes for FacCab table
EXECUTE IMMEDIATE 'CREATE INDEX "NumDoc" ON "FacCab" ("NumDoc")';
EXECUTE IMMEDIATE 'CREATE INDEX "NumCliente" ON "FacCab" ("NumCliente", "TipoDoc" COLLATE "PTG", "SerieDoc" COLLATE "PTG", "NumDoc")';
-- Creating indexes for FacLin table
EXECUTE IMMEDIATE 'CREATE INDEX "NumDoc" ON "FacLin" ("NumDoc")';
-- Creating indexes for Fornecedores table
EXECUTE IMMEDIATE 'CREATE INDEX "Nome" ON "Fornecedores" ("Nome" COLLATE "PTG_CI_AI")';
-- Creating indexes for Locais table
EXECUTE IMMEDIATE 'CREATE INDEX "Local" ON "Locais" ("Local" COLLATE "PTG")';
-- Creating indexes for MovCli table
EXECUTE IMMEDIATE 'CREATE INDEX "NumCliente" ON "MovCli" ("NumCliente", "DataMov", "Diario", "NumLanc")';
EXECUTE IMMEDIATE 'CREATE INDEX "NumDoc" ON "MovCli" ("NumDoc" COLLATE "PTG")';
-- Creating indexes for MovCliL table
EXECUTE IMMEDIATE 'CREATE INDEX "Doc" ON "MovCliL" ("AnoDoc", "TipoDoc" COLLATE "PTG", "SerieDoc" COLLATE "PTG", "NumDoc" COLLATE "PTG")';
EXECUTE IMMEDIATE 'CREATE INDEX "NumLanc" ON "MovCliL" ("NumLanc")';
EXECUTE IMMEDIATE 'CREATE INDEX "NumDoc" ON "MovCliL" ("NumDoc" COLLATE "PTG")';
-- Creating indexes for Paises table
EXECUTE IMMEDIATE 'CREATE INDEX "NomeP" ON "Paises" ("NomeP" COLLATE "PTG")';
-- Creating indexes for ResCont table
EXECUTE IMMEDIATE 'CREATE INDEX "IdVar" ON "ResCont" ("IdVar" COLLATE "PTG_CI")';
-- Creating indexes for TabPreCol table
EXECUTE IMMEDIATE 'CREATE INDEX "CodServico" ON "TabPreCol" ("CodServico" COLLATE "PTG", "CodFornecedor", "NumTabela", "Inicio", "OrdemCol")';
-- Creating indexes for TabPreLn table
EXECUTE IMMEDIATE 'CREATE INDEX "CodServico" ON "TabPreLn" ("CodServico" COLLATE "PTG", "CodFornecedor", "NumTabela", "NumCol", "Linha")';
EXECUTE IMMEDIATE 'CREATE INDEX "CodFornecedor" ON "TabPreLn" ("CodFornecedor")';
-- Creating indexes for TiposServ table
EXECUTE IMMEDIATE 'CREATE INDEX "Ordem" ON "TiposServ" ("CodServico" COLLATE "PTG", "Ordem", "CodTServico" COLLATE "PTG")';
-- Creating triggers for ComCab table
EXECUTE IMMEDIATE 'CREATE TRIGGER "ActTotal" BEFORE UPDATE ON "ComCab"
BEGIN
-- Totais das linhas
DECLARE vTotal, vTaxas DECIMAL(19,2);
DECLARE OutTransaction BOOLEAN ;
DECLARE Cur1 INSENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
-- Movimento e CrDeb
DECLARE Movimento Integer; -- Movimento em Compras (-1/0/1)
DECLARE TipoDoc, CrDeb VARCHAR; -- Movimento nas C.C. de fornecedores (C/D/ )
DECLARE Cur2 SENSITIVE CURSOR FOR Sql2;
-- Totais das linhas
PREPARE Sql1 FROM ''SELECT SUM(Valor), SUM(TotalTaxas) FROM ComLin WHERE TipoDoc = ? AND NumReg = ? '';
SET OutTransaction = INTRANSACTION ;
IF NOT OutTransaction THEN START TRANSACTION ON TABLES ''ComCab'', ''ComLin'' ; END IF;
BEGIN
OPEN Cur1 USING NEWROW.TipoDoc, NEWROW.NumReg;
FETCH FIRST FROM Cur1 INTO vTotal, vTaxas;
UNPREPARE Sql1;
SET NEWROW.ValorTotal = COALESCE(vTotal, 0);
SET NEWROW.TotalTaxas = COALESCE(vTaxas, 0);
IF NOT OutTransaction THEN COMMIT; END IF;
EXCEPTION
IF NOT OutTransaction THEN ROLLBACK; END IF;
RAISE;
END;
set log message to ''1'' ;
-- Actualização "Movimento" e "CrDeb" se forem nulos
IF OLDROW.Movimento IS NULL OR OLDROW.CrDeb IS NULL THEN
set log message to ''2'' ;
PREPARE Sql2 FROM ''SELECT CrDeb, Movimento FROM TiposDoc WHERE TipoDoc=?'';
OPEN Cur2 USING NEWROW.TipoDoc ;
IF ROWCOUNT(Cur2) = 1 THEN
FETCH FIRST FROM Cur2 INTO CrDeb, Movimento ;
SET NEWROW.Movimento = Movimento ;
SET NEWROW.CrDeb = CrDeb ;
END IF;
CLOSE Cur2;
UNPREPARE Sql2;
END IF ;
END
';
EXECUTE IMMEDIATE 'CREATE TRIGGER "NovoDoc" BEFORE INSERT ON "ComCab"
BEGIN
DECLARE Movimento Integer; -- Movimento em Compras (-1/0/1)
DECLARE TipoDoc, CrDeb VARCHAR; -- Movimento nas C.C. de fornecedores (C/D/ )
DECLARE Cur1 SENSITIVE CURSOR FOR Sql1;
IF NEWROW.TipoDoc IS NOT NULL THEN
PREPARE Sql1 FROM ''SELECT CrDeb, Movimento FROM TiposDoc WHERE TipoDoc=?'';
OPEN Cur1 USING NEWROW.TipoDoc ;
IF ROWCOUNT(Cur1) = 1 THEN
FETCH FIRST FROM Cur1 INTO CrDeb, Movimento ;
SET NEWROW.Movimento = Movimento ;
SET NEWROW.CrDeb = CrDeb ;
END IF;
CLOSE Cur1;
UNPREPARE Sql1;
END IF ;
END
';
-- Creating indexes for ComCab table
EXECUTE IMMEDIATE 'CREATE INDEX "DataDoc" ON "ComCab" ("DataDoc", "CodFornecedor", "NumReg")';
EXECUTE IMMEDIATE 'CREATE INDEX "CF_NumDoc" ON "ComCab" ("CodFornecedor", "TipoDoc" COLLATE "PTG", "DataDoc")';
EXECUTE IMMEDIATE 'CREATE INDEX "NumDoc" ON "ComCab" ("NumDoc" COLLATE "PTG_CI", "CodFornecedor")';
-- Creating triggers for ComLin table
EXECUTE IMMEDIATE 'CREATE TRIGGER "NovaLinha" BEFORE INSERT ON "ComLin"
BEGIN
DECLARE OutTransaction BOOLEAN ;
DECLARE MaxLinha INTEGER;
DECLARE Cur1 INSENSITIVE CURSOR WITHOUT RETURN FOR Sql1;
IF NEWROW.Linha IS NULL THEN
PREPARE Sql1 FROM ''SELECT MAX(Linha) FROM ComLin WHERE TipoDoc = ? AND NumReg = ? '';
SET OutTransaction = INTRANSACTION ;
IF NOT OutTransaction THEN START TRANSACTION ON TABLES ''ComLin'' ; END IF;
BEGIN
OPEN Cur1 USING NEWROW.TipoDoc, NEWROW.NumReg;
FETCH FIRST FROM Cur1 INTO MaxLinha;
UNPREPARE Sql1;
SET NEWROW.Linha = COALESCE(MaxLinha, 0) + 1;
IF NOT OutTransaction THEN COMMIT; END IF;
EXCEPTION
IF NOT OutTransaction THEN ROLLBACK; END IF;
RAISE;
END;
END IF;
END
';
-- Creating non-foreign key constraints for Actividades table
EXECUTE IMMEDIATE 'ALTER TABLE "Actividades"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodActividade")';
-- Creating non-foreign key constraints for Bancos table
EXECUTE IMMEDIATE 'ALTER TABLE "Bancos"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodEntidade")';
-- Creating non-foreign key constraints for Chaves table
EXECUTE IMMEDIATE 'ALTER TABLE "Chaves"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Id")';
-- Creating non-foreign key constraints for Clientes table
EXECUTE IMMEDIATE 'ALTER TABLE "Clientes"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("NumCliente")';
-- Creating non-foreign key constraints for CliZL table
EXECUTE IMMEDIATE 'ALTER TABLE "CliZL"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("NumCliente", "Zona", "Local")';
-- Creating non-foreign key constraints for CondPaga table
EXECUTE IMMEDIATE 'ALTER TABLE "CondPaga"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodCondPaga")';
-- Creating non-foreign key constraints for DadosPessoais table
EXECUTE IMMEDIATE 'ALTER TABLE "DadosPessoais"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("NumCliente", "Nome")';
-- Creating non-foreign key constraints for Delegacoes table
EXECUTE IMMEDIATE 'ALTER TABLE "Delegacoes"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodDelegacao")';
-- Creating non-foreign key constraints for Diarios table
EXECUTE IMMEDIATE 'ALTER TABLE "Diarios"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoDiario", "NumDiario")';
-- Creating non-foreign key constraints for DocsAbrCli table
EXECUTE IMMEDIATE 'ALTER TABLE "DocsAbrCli"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("AnoDoc", "TipoDoc", "SerieDoc", "NumDoc")';
-- Creating non-foreign key constraints for FacCab table
EXECUTE IMMEDIATE 'ALTER TABLE "FacCab"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoDoc", "SerieDoc", "NumDoc")';
-- Creating non-foreign key constraints for FacLin table
EXECUTE IMMEDIATE 'ALTER TABLE "FacLin"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoDoc", "SerieDoc", "NumDoc", "Linha", "Anexo") ERROR CODE 15002 MESSAGE ''Linha Duplicada.'',
ADD CONSTRAINT "CHLinha" CHECK (Linha >= 0) ERROR CODE 15001 MESSAGE ''Número da linha não pode ser nagativo.''';
-- Creating non-foreign key constraints for Fornecedores table
EXECUTE IMMEDIATE 'ALTER TABLE "Fornecedores"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodFornecedor")';
-- Creating non-foreign key constraints for FornServ table
EXECUTE IMMEDIATE 'ALTER TABLE "FornServ"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodFornecedor", "CodServico"),
ADD CONSTRAINT "CodServico" UNIQUE ("CodServico", "CodFornecedor")';
-- Creating non-foreign key constraints for FornZL table
EXECUTE IMMEDIATE 'ALTER TABLE "FornZL"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodFornecedor", "Zona", "Local")';
-- Creating non-foreign key constraints for Locais table
EXECUTE IMMEDIATE 'ALTER TABLE "Locais"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Zona", "Local")';
-- Creating non-foreign key constraints for MCalc table
EXECUTE IMMEDIATE 'ALTER TABLE "MCalc"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("MetodoDeCalculo")';
-- Creating non-foreign key constraints for Moedas table
EXECUTE IMMEDIATE 'ALTER TABLE "Moedas"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodMoeda")';
-- Creating non-foreign key constraints for MovCli table
EXECUTE IMMEDIATE 'ALTER TABLE "MovCli"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Diario", "NumLanc"),
ADD CONSTRAINT "UKAnoDoc" UNIQUE ("AnoDoc", "TipoDoc", "SerieDoc", "NumDoc", "AbrFecho")';
-- Creating non-foreign key constraints for MovCliL table
EXECUTE IMMEDIATE 'ALTER TABLE "MovCliL"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Diario", "NumLanc", "Linha")';
-- Creating non-foreign key constraints for NotasCli table
EXECUTE IMMEDIATE 'ALTER TABLE "NotasCli"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("NumCliente", "Data", "CriadoPor")';
-- Creating non-foreign key constraints for NotasFor table
EXECUTE IMMEDIATE 'ALTER TABLE "NotasFor"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodFornecedor", "Data", "CriadoPor")';
-- Creating non-foreign key constraints for Paises table
EXECUTE IMMEDIATE 'ALTER TABLE "Paises"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodPais")';
-- Creating non-foreign key constraints for PassFac table
EXECUTE IMMEDIATE 'ALTER TABLE "PassFac"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoDoc", "SerieDoc", "NumDoc", "NumPassageiro")';
-- Creating non-foreign key constraints for RecCab table
EXECUTE IMMEDIATE 'ALTER TABLE "RecCab"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("SerieRecibo", "NumRecibo")';
-- Creating non-foreign key constraints for RecLin table
EXECUTE IMMEDIATE 'ALTER TABLE "RecLin"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("SerieRecibo", "NumRecibo", "Linha")';
-- Creating non-foreign key constraints for ResCont table
EXECUTE IMMEDIATE 'ALTER TABLE "ResCont"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Pagina", "Linha")';
-- Creating non-foreign key constraints for RptForms table
EXECUTE IMMEDIATE 'ALTER TABLE "RptForms"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("RptId")';
-- Creating non-foreign key constraints for Series table
EXECUTE IMMEDIATE 'ALTER TABLE "Series"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Serie")';
-- Creating non-foreign key constraints for Servicos table
EXECUTE IMMEDIATE 'ALTER TABLE "Servicos"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodServico")';
-- Creating non-foreign key constraints for SitCli table
EXECUTE IMMEDIATE 'ALTER TABLE "SitCli"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Situacao")';
-- Creating non-foreign key constraints for TabPre table
EXECUTE IMMEDIATE 'ALTER TABLE "TabPre"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodServico", "CodFornecedor", "NumTabela"),
ADD CONSTRAINT "CodFornecedor" UNIQUE ("CodFornecedor", "CodServico", "NumTabela"),
ADD CONSTRAINT "TipoTarifa" UNIQUE ("TipoTarifa", "CodFornecedor", "CodServico", "NumTabela")';
-- Creating non-foreign key constraints for TabPreCol table
EXECUTE IMMEDIATE 'ALTER TABLE "TabPreCol"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodServico", "CodFornecedor", "NumTabela", "NumCol")';
-- Creating non-foreign key constraints for TabPreLn table
EXECUTE IMMEDIATE 'ALTER TABLE "TabPreLn"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodServico", "CodFornecedor", "NumTabela", "NumCol", "CodTServico")';
-- Creating non-foreign key constraints for Taxas table
EXECUTE IMMEDIATE 'ALTER TABLE "Taxas"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodTaxa")';
-- Creating non-foreign key constraints for TiposDoc table
EXECUTE IMMEDIATE 'ALTER TABLE "TiposDoc"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoDoc")';
-- Creating non-foreign key constraints for TiposServ table
EXECUTE IMMEDIATE 'ALTER TABLE "TiposServ"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodServico", "CodTServico")';
-- Creating non-foreign key constraints for TiposTarifa table
EXECUTE IMMEDIATE 'ALTER TABLE "TiposTarifa"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoTarifa")';
-- Creating non-foreign key constraints for Vendedores table
EXECUTE IMMEDIATE 'ALTER TABLE "Vendedores"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("CodVendedor")';
-- Creating non-foreign key constraints for Zonas table
EXECUTE IMMEDIATE 'ALTER TABLE "Zonas"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("Zona")';
-- Creating non-foreign key constraints for AppLog table
EXECUTE IMMEDIATE 'ALTER TABLE "AppLog"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID")';
-- Creating non-foreign key constraints for ComCab table
EXECUTE IMMEDIATE 'ALTER TABLE "ComCab"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoDoc", "NumReg")';
-- Creating non-foreign key constraints for ComLin table
EXECUTE IMMEDIATE 'ALTER TABLE "ComLin"
ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("TipoDoc", "NumReg", "Linha"),
ADD CONSTRAINT "Chk_NullValues" CHECK ((CodServico IS NULL AND CodTServico IS NULL) OR (CodServico IS NOT NULL AND CodTServico IS NOT NULL))
';
-- Creating foreign key constraints for Clientes table
EXECUTE IMMEDIATE 'ALTER TABLE "Clientes"
ADD CONSTRAINT "FKActividades" FOREIGN KEY ("CodActividade")
REFERENCES "Actividades" ("CodActividade")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKMoedas" FOREIGN KEY ("CodMoeda")
REFERENCES "Moedas" ("CodMoeda")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKCondPaga" FOREIGN KEY ("CodCondPaga")
REFERENCES "CondPaga" ("CodCondPaga")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKVendedores" FOREIGN KEY ("CodVendedor")
REFERENCES "Vendedores" ("CodVendedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKSitCli" FOREIGN KEY ("Situacao")
REFERENCES "SitCli" ("Situacao")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKPaises" FOREIGN KEY ("CodPais")
REFERENCES "Paises" ("CodPais")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKFornecedores" FOREIGN KEY ("CodFornecedor")
REFERENCES "Fornecedores" ("CodFornecedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for CliZL table
EXECUTE IMMEDIATE 'ALTER TABLE "CliZL"
ADD CONSTRAINT "FKClientes" FOREIGN KEY ("NumCliente")
REFERENCES "Clientes" ("NumCliente")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKLocais" FOREIGN KEY ("Zona", "Local")
REFERENCES "Locais" ("Zona", "Local")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for DadosPessoais table
EXECUTE IMMEDIATE 'ALTER TABLE "DadosPessoais"
ADD CONSTRAINT "FKClientes" FOREIGN KEY ("NumCliente")
REFERENCES "Clientes" ("NumCliente")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKLocais" FOREIGN KEY ("Zona", "Local")
REFERENCES "Locais" ("Zona", "Local")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKPaises" FOREIGN KEY ("CodPais")
REFERENCES "Paises" ("CodPais")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for DocsAbrCli table
EXECUTE IMMEDIATE 'ALTER TABLE "DocsAbrCli"
ADD CONSTRAINT "FKClientes" FOREIGN KEY ("NumCliente")
REFERENCES "Clientes" ("NumCliente")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for FacCab table
EXECUTE IMMEDIATE 'ALTER TABLE "FacCab"
ADD CONSTRAINT "FKTiposDoc" FOREIGN KEY ("TipoDoc")
REFERENCES "TiposDoc" ("TipoDoc")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKSeries" FOREIGN KEY ("SerieDoc")
REFERENCES "Series" ("Serie")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKMoedas" FOREIGN KEY ("CodMoeda")
REFERENCES "Moedas" ("CodMoeda")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKCondPaga" FOREIGN KEY ("CodCondPaga")
REFERENCES "CondPaga" ("CodCondPaga")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKVendedores" FOREIGN KEY ("CodVendedor")
REFERENCES "Vendedores" ("CodVendedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKPaises" FOREIGN KEY ("CodPais")
REFERENCES "Paises" ("CodPais")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for FacLin table
EXECUTE IMMEDIATE 'ALTER TABLE "FacLin"
ADD CONSTRAINT "FKFacCab" FOREIGN KEY ("TipoDoc", "SerieDoc", "NumDoc")
REFERENCES "FacCab" ("TipoDoc", "SerieDoc", "NumDoc")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKTiposServ" FOREIGN KEY ("CodServico", "CodTServico")
REFERENCES "TiposServ" ("CodServico", "CodTServico")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKFornecedores" FOREIGN KEY ("CodFornecedor")
REFERENCES "Fornecedores" ("CodFornecedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKTaxas" FOREIGN KEY ("CodIVA")
REFERENCES "Taxas" ("CodTaxa")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for Fornecedores table
EXECUTE IMMEDIATE 'ALTER TABLE "Fornecedores"
ADD CONSTRAINT "FKMoedas" FOREIGN KEY ("CodMoeda")
REFERENCES "Moedas" ("CodMoeda")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKCondPaga" FOREIGN KEY ("CodCondPaga")
REFERENCES "CondPaga" ("CodCondPaga")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKPaises" FOREIGN KEY ("CodPais")
REFERENCES "Paises" ("CodPais")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for FornServ table
EXECUTE IMMEDIATE 'ALTER TABLE "FornServ"
ADD CONSTRAINT "FKServicos" FOREIGN KEY ("CodServico")
REFERENCES "Servicos" ("CodServico")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKFornecedores" FOREIGN KEY ("CodFornecedor")
REFERENCES "Fornecedores" ("CodFornecedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for FornZL table
EXECUTE IMMEDIATE 'ALTER TABLE "FornZL"
ADD CONSTRAINT "FKFornecedores" FOREIGN KEY ("CodFornecedor")
REFERENCES "Fornecedores" ("CodFornecedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKLocais" FOREIGN KEY ("Zona", "Local")
REFERENCES "Locais" ("Zona", "Local")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for MovCli table
EXECUTE IMMEDIATE 'ALTER TABLE "MovCli"
ADD CONSTRAINT "FKClientes" FOREIGN KEY ("NumCliente")
REFERENCES "Clientes" ("NumCliente")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for NotasCli table
EXECUTE IMMEDIATE 'ALTER TABLE "NotasCli"
ADD CONSTRAINT "FKClientes" FOREIGN KEY ("NumCliente")
REFERENCES "Clientes" ("NumCliente")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKDelegacoes" FOREIGN KEY ("Delegacao")
REFERENCES "Delegacoes" ("CodDelegacao")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for NotasFor table
EXECUTE IMMEDIATE 'ALTER TABLE "NotasFor"
ADD CONSTRAINT "FKFornecedores" FOREIGN KEY ("CodFornecedor")
REFERENCES "Fornecedores" ("CodFornecedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKDelegacoes" FOREIGN KEY ("Delegacao")
REFERENCES "Delegacoes" ("CodDelegacao")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for PassFac table
EXECUTE IMMEDIATE 'ALTER TABLE "PassFac"
ADD CONSTRAINT "FKFacCab" FOREIGN KEY ("TipoDoc", "SerieDoc", "NumDoc")
REFERENCES "FacCab" ("TipoDoc", "SerieDoc", "NumDoc")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for RecCab table
EXECUTE IMMEDIATE 'ALTER TABLE "RecCab"
ADD CONSTRAINT "FKClientes" FOREIGN KEY ("NumCliente")
REFERENCES "Clientes" ("NumCliente")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKSeries" FOREIGN KEY ("SerieRecibo")
REFERENCES "Series" ("Serie")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKMoedas" FOREIGN KEY ("CodMoeda")
REFERENCES "Moedas" ("CodMoeda")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKPaises" FOREIGN KEY ("CodPais")
REFERENCES "Paises" ("CodPais")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for RecLin table
EXECUTE IMMEDIATE 'ALTER TABLE "RecLin"
ADD CONSTRAINT "FKRecCab" FOREIGN KEY ("SerieRecibo", "NumRecibo")
REFERENCES "RecCab" ("SerieRecibo", "NumRecibo")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for Series table
EXECUTE IMMEDIATE 'ALTER TABLE "Series"
ADD CONSTRAINT "FKDelegacoes" FOREIGN KEY ("Delegacao")
REFERENCES "Delegacoes" ("CodDelegacao")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for Servicos table
EXECUTE IMMEDIATE 'ALTER TABLE "Servicos"
ADD CONSTRAINT "FKTaxas" FOREIGN KEY ("CodIva")
REFERENCES "Taxas" ("CodTaxa")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for TabPre table
EXECUTE IMMEDIATE 'ALTER TABLE "TabPre"
ADD CONSTRAINT "FKFornecedores" FOREIGN KEY ("CodFornecedor")
REFERENCES "Fornecedores" ("CodFornecedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKServicos" FOREIGN KEY ("CodServico")
REFERENCES "Servicos" ("CodServico")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKTiposTarifas" FOREIGN KEY ("TipoTarifa")
REFERENCES "TiposTarifa" ("TipoTarifa")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKLocais" FOREIGN KEY ("Zona", "Local")
REFERENCES "Locais" ("Zona", "Local")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for TabPreCol table
EXECUTE IMMEDIATE 'ALTER TABLE "TabPreCol"
ADD CONSTRAINT "FKTabPre" FOREIGN KEY ("CodServico", "CodFornecedor", "NumTabela")
REFERENCES "TabPre" ("CodServico", "CodFornecedor", "NumTabela")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for TabPreLn table
EXECUTE IMMEDIATE 'ALTER TABLE "TabPreLn"
ADD CONSTRAINT "FKTabPreCol" FOREIGN KEY ("CodServico", "CodFornecedor", "NumTabela", "NumCol")
REFERENCES "TabPreCol" ("CodServico", "CodFornecedor", "NumTabela", "NumCol")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKTiposServ" FOREIGN KEY ("CodServico", "CodTServico")
REFERENCES "TiposServ" ("CodServico", "CodTServico")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for TiposDoc table
EXECUTE IMMEDIATE 'ALTER TABLE "TiposDoc"
ADD CONSTRAINT "FKRptForms" FOREIGN KEY ("RptId")
REFERENCES "RptForms" ("RptId")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for TiposServ table
EXECUTE IMMEDIATE 'ALTER TABLE "TiposServ"
ADD CONSTRAINT "FKServicos" FOREIGN KEY ("CodServico")
REFERENCES "Servicos" ("CodServico")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKTaxas" FOREIGN KEY ("CodIva")
REFERENCES "Taxas" ("CodTaxa")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for ComCab table
EXECUTE IMMEDIATE 'ALTER TABLE "ComCab"
ADD CONSTRAINT "FKTiposDoc" FOREIGN KEY ("TipoDoc")
REFERENCES "TiposDoc" ("TipoDoc")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKFornecedores" FOREIGN KEY ("CodFornecedor")
REFERENCES "Fornecedores" ("CodFornecedor")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKPaises" FOREIGN KEY ("CodPais")
REFERENCES "Paises" ("CodPais")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKCondPaga" FOREIGN KEY ("CodCondPaga")
REFERENCES "CondPaga" ("CodCondPaga")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKMoedas" FOREIGN KEY ("CodMoeda")
REFERENCES "Moedas" ("CodMoeda")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
-- Creating foreign key constraints for ComLin table
EXECUTE IMMEDIATE 'ALTER TABLE "ComLin"
ADD CONSTRAINT "FKComCab" FOREIGN KEY ("TipoDoc", "NumReg")
REFERENCES "ComCab" ("TipoDoc", "NumReg")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "FKTiposServ" FOREIGN KEY ("CodServico", "CodTServico")
REFERENCES "TiposServ" ("CodServico", "CodTServico")
ON UPDATE NO ACTION
ON DELETE NO ACTION';
END
/************************************************************
* End of generated SQL
************************************************************/
CommentsThe problem was caused by the changes introduced for the fix to incident report #3422.
ResolutionFixed Problem on 4/8/2011 in version 2.05 build 6
Products AffectedElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial
This web page was last updated on Wednesday, March 20, 2024 at 07:22 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ?  E-mail us at info@elevatesoft.com |
