[firebase-br] Ajuda com SP por favor

[Fausto]

Agora que entendi..
O metadados
/******************************************************************************/
/***          Generated by IBExpert 2010.03.19 02/06/2012 
15:27:34          ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1252;

/******************************************************************************/
/***                                
Domains                                 ***/
/******************************************************************************/

CREATE DOMAIN DM_CEP AS
CHAR(8)
COLLATE PXW_INTL850;

CREATE DOMAIN DM_CNPJ AS
VARCHAR(14)
COLLATE PXW_INTL850;

CREATE DOMAIN DM_CODIGO AS
INTEGER
NOT NULL;

CREATE DOMAIN DM_DEBITOCREDITO AS
CHAR(1)
DEFAULT 'D'
NOT NULL
CHECK (VALUE IN ('D', 'C'))
COLLATE PXW_INTL850;

CREATE DOMAIN DM_DESCRICAO AS
VARCHAR(40)
NOT NULL
COLLATE PXW_INTL850;

CREATE DOMAIN DM_FONE AS
CHAR(10)
COLLATE PXW_INTL850;

CREATE DOMAIN DM_INSC AS
VARCHAR(20)
COLLATE PXW_INTL850;

CREATE DOMAIN DM_LOGICO AS
INTEGER
DEFAULT 1;

CREATE DOMAIN DM_NUMERO AS
NUMERIC(12,2)
DEFAULT 0.00;

CREATE DOMAIN DM_OBS AS
BLOB SUB_TYPE 1 SEGMENT SIZE 20;

CREATE DOMAIN DM_SIMNAO AS
CHAR(1)
NOT NULL
CHECK (Value in ('S','N'))
COLLATE PXW_INTL850;

CREATE DOMAIN DM_TAXA AS
NUMERIC(6,2)
DEFAULT 0.00;

CREATE DOMAIN DM_TEXTO AS
VARCHAR(40)
COLLATE PXW_INTL850;



/******************************************************************************/
/***                                 
Tables                                 ***/
/******************************************************************************/
CREATE TABLE CIDADES (
     CIDCOD  DM_CODIGO,
     CIDNOM  DM_DESCRICAO NOT NULL COLLATE PXW_INTL850,
     CIDEST  VARCHAR(2)
);



CREATE TABLE CLIENTES (
     CLICOD        DM_CODIGO,
     CLINOM        DM_DESCRICAO COLLATE PXW_INTL850,
     CLIFAN        VARCHAR(40) COLLATE PXW_INTL850,
     CLIEND        VARCHAR(40) COLLATE PXW_INTL850,
     CLICOMP       VARCHAR(20) COLLATE PXW_INTL850,
     CLIBAI        VARCHAR(25) COLLATE PXW_INTL850,
     CIDCOD        INTEGER,
     CLICEP        DM_CEP COLLATE PXW_INTL850,
     CLIFON        VARCHAR(10),
     CLIFAX        VARCHAR(10),
     CLIMAI        VARCHAR(60),
     CLIPES        CHAR(1),
     CLICGC        VARCHAR(14),
     CLIINS        VARCHAR(20),
     CLICRE        CHAR(1),
     CLILIM        DM_NUMERO,
     CLISIT        CHAR(1),
     CLICON        VARCHAR(25) COLLATE PXW_INTL850,
     REPCOD        INTEGER,
     TRACOD        INTEGER,
     CLIDAT        DATE,
     REDCOD        INTEGER,
     CPGCOD        INTEGER,
     CLIOBS        DM_OBS,
     CLIOBS1       DM_OBS,
     BANCOD        CHAR(3),
     CLIAGE        VARCHAR(10),
     CLICTA        VARCHAR(10),
     SALDOINICIAL  DM_NUMERO,
     SALDOATUAL    DM_NUMERO
);


CREATE TABLE FABRICA (
     FABCOD        DM_CODIGO,
     FABNOM        DM_DESCRICAO,
     FABFAN        VARCHAR(40) COLLATE PXW_INTL850,
     FABEND        VARCHAR(40) COLLATE PXW_INTL850,
     FABBAI        VARCHAR(25) COLLATE PXW_INTL850,
     FABCEP        DM_CEP,
     FABCOM        VARCHAR(20) COLLATE PXW_INTL850,
     FABCGC        DM_CNPJ,
     FABINS        DM_INSC,
     FABFON        DM_FONE,
     FABFAX        DM_FONE,
     FABCON        VARCHAR(25) COLLATE PXW_INTL850,
     CIDCOD        INTEGER,
     FABMAIL       VARCHAR(60),
     FABSITE       VARCHAR(60),
     FABSIT        DM_SIMNAO,
     FABPES        DM_SIMNAO,
     FABDAT        DATE,
     FABFAT        DM_TAXA,
     FABLIQ        DM_TAXA,
     REGCOD        INTEGER,
     FABOBS        DM_OBS,
     FABCAM        VARCHAR(60) COLLATE PXW_INTL850,
     REPCOD        INTEGER,
     SALDOINICIAL  DM_NUMERO,
     SALDOATUAL    DM_NUMERO
);


CREATE TABLE PEDIDOS (
     PEDCOD     DM_CODIGO,
     PEDCLI     VARCHAR(15) COLLATE PXW_INTL850,
     CLICOD     INTEGER,
     REPCOD     INTEGER,
     REDCOD     INTEGER,
     FABCOD     INTEGER,
     PEDEMI     DATE,
     PEDENT     DATE,
     PEDPRV     DATE,
     PEDPR1     DATE,
     PEDDES     DM_TAXA
);


CREATE TABLE PEDITEM (
     PEDCOD    INTEGER NOT NULL,
     PEDITE    INTEGER NOT NULL,
     MODPRE    DM_NUMERO,
     ITENOT    VARCHAR(6),
     ITEDFT    DATE,
     ITEDCN    DATE,
     ITEQDE    INTEGER,
     ITEEMB    INTEGER
);


CREATE TABLE REDES (
     REDCOD  DM_CODIGO,
     REDNOM  DM_DESCRICAO
);




/******************************************************************************/
/***                              Primary 
Keys                              ***/
/******************************************************************************/

ALTER TABLE CIDADES ADD CONSTRAINT PK_CIDADES PRIMARY KEY (CIDCOD);
ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES PRIMARY KEY (CLICOD);
ALTER TABLE FABRICA ADD CONSTRAINT PK_FABRICA PRIMARY KEY (FABCOD);
ALTER TABLE PEDIDOS ADD CONSTRAINT PK_PEDIDOS PRIMARY KEY (PEDCOD);
ALTER TABLE PEDITEM ADD CONSTRAINT PK_PEDITEM PRIMARY KEY (PEDCOD, PEDITE);
ALTER TABLE REDES ADD CONSTRAINT PK_REDES PRIMARY KEY (REDCOD);

/******************************************************************************/
/***                              Foreign 
Keys                              ***/
/******************************************************************************/

ALTER TABLE CIDADES ADD CONSTRAINT FK_CIDADES_1 FOREIGN KEY (CIDEST) 
REFERENCES ESTADOS (ESTCOD);
ALTER TABLE CLIENTES ADD CONSTRAINT FK_CLIENTES_CIDADES FOREIGN KEY 
(CIDCOD) REFERENCES CIDADES (CIDCOD);
ALTER TABLE CLIENTES ADD CONSTRAINT FK_CLIENTES_REDES FOREIGN KEY 
(REDCOD) REFERENCES REDES (REDCOD);
ALTER TABLE FABRICA ADD CONSTRAINT FK_FABRICA_1 FOREIGN KEY (REGCOD) 
REFERENCES CIDADES (CIDCOD);
ALTER TABLE FABRICA ADD CONSTRAINT FK_FABRICA_CIDADE FOREIGN KEY 
(CIDCOD) REFERENCES CIDADES (CIDCOD);
ALTER TABLE PEDIDOS ADD CONSTRAINT FK_PEDIDOS_CLI FOREIGN KEY (CLICOD) 
REFERENCES CLIENTES (CLICOD);
ALTER TABLE PEDIDOS ADD CONSTRAINT FK_PEDIDOS_CPG FOREIGN KEY (CPGCOD) 
REFERENCES CONDPGTO (CPGCOD);
ALTER TABLE PEDIDOS ADD CONSTRAINT FK_PEDIDOS_FAB FOREIGN KEY (FABCOD) 
REFERENCES FABRICA (FABCOD);
ALTER TABLE PEDIDOS ADD CONSTRAINT FK_PEDIDOS_REP FOREIGN KEY (REPCOD) 
REFERENCES VENDEDORES (REPCOD);
ALTER TABLE PEDIDOS ADD CONSTRAINT FK_PEDIDOS_VEN FOREIGN KEY (VENCOD) 
REFERENCES TIPOVENDA (VENCOD);
ALTER TABLE PEDITEM ADD CONSTRAINT FK_PEDITEM_CAB FOREIGN KEY (PEDCOD) 
REFERENCES PEDIDOS (PEDCOD) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PEDITEM ADD CONSTRAINT FK_PEDITEM_LINHA FOREIGN KEY (LINCOD) 
REFERENCES LINHAS (LINCOD);
ALTER TABLE PEDITEM ADD CONSTRAINT FK_PEDITEM_NEGOCIACAO FOREIGN KEY 
(MOVLAN) REFERENCES NEGOCIACAO (MOVLAN);
ALTER TABLE REDES ADD CONSTRAINT FK_REDES_CID FOREIGN KEY (CIDCOD) 
REFERENCES CIDADES (CIDCOD);



/******************************************************************************/
/***                                
Indices                                 ***/
/******************************************************************************/

CREATE INDEX IDX_PEDCLI ON PEDIDOS (PEDCLI);
CREATE INDEX IDX_PEDEMI ON PEDIDOS (PEDEMI);
CREATE INDEX PEDITEM_DTFT ON PEDITEM (ITEDFT);
CREATE INDEX PEDITEM_NEG ON PEDITEM (MOVLAN);


-- 
Fausto Alves
Skype:faustoalves
msn:faustoalves em com4.com.br
Franca-SP
Enviado pelo Mozilla Thunderbird
http://br.mozdev.org/thunderbird