SGBD Oracle

CREATE TABLE TBTRACKSEARCH

(

 SEARCHID NUMBER NOT NULL,

 IDUSER INT NOT NULL,

 INSERTDATE DATE NOT NULL,

 FINISHDATE DATE,

 DOCUMENTKEY1 NUMBER NOT NULL,

 DOCUMENTKEY2 NUMBER NOT NULL,

 DOCUMENTKEY3 INT NOT NULL,

 XMLREQUESTREPLY VARCHAR(999),

 SEARCHSTATUS SMALLINT NOT NULL,

 OPENSTATUS CHAR NOT NULL,

 COLDIDSEARCHED NUMBER,

 DONTSHOW NUMBER(3)

 OBSERVATION VARCHAR(999),

 DOCUMENTTYPE NUMBER(3),

 PURCHASEORDER VARCHAR(999)

, CONSTRAINT TBTRACKSEARCH_PK PRIMARY KEY ( SEARCHID ) ENABLE);

CREATE SEQUENCE TBTRACKSEARCH_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER TBTRACKSEARCH_TRG

BEFORE INSERT ON TBTRACKSEARCH

FOR EACH ROW

BEGIN

 SELECT TBTRACKSEARCH_SEQ.NEXTVAL INTO :NEW.SEARCHID FROM DUAL;

END;

/

ALTER TRIGGER TBTRACKSEARCH_TRG ENABLE;

/

CREATE TABLE TBTRACKEVENTS

(

 EVENTID NUMBER NOT NULL,

 SEARCHID NUMBER NOT NULL,

 EVENTTYPE SMALLINT NOT NULL,

 EVENTDATE DATE NOT NULL,

 EVENTMESSAGE VARCHAR(300),

 ISSUERIDENTITYNUMBER VARCHAR(20),

 ISSUERNAME VARCHAR(150),

 EMISSIONDATE DATE,

 TOTALVALUE NUMBER(15,2),

 RECEIVERIDENTITYNUMBER VARCHAR(20),

 SECTOR SMALLINT,

 RECEIVERNAME VARCHAR(150),  

 USERIDENTIFICATION VARCHAR(100)

 ERPREGISTERDATE DATE,

 ERPREGISTERUSER VARCHAR(100)

, CONSTRAINT TBTRACKEVENTS_PK PRIMARY KEY ( EVENTID ) ENABLE);

ALTER TABLE TBTRACKEVENTS

ADD CONSTRAINT EVENTS_SEARCH_FK1 FOREIGN KEY ( SEARCHID )

REFERENCES TBTRACKSEARCH ( SEARCHID ) ENABLE;

CREATE SEQUENCE TBTRACKEVENTS_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE OR REPLACE TRIGGER TBTRACKEVENTS_TRG

BEFORE INSERT ON TBTRACKEVENTS

FOR EACH ROW

BEGIN

 SELECT TBTRACKEVENTS_SEQ.NEXTVAL INTO :NEW.EVENTID FROM DUAL;

END;

/

ALTER TRIGGER TBTRACKEVENTS_TRG ENABLE;

       CREATE TABLE  TBTRACKCUSTOMFIELDS(

 CUSTOMID NUMBER NOT NULL,

 EVENTID NUMBER NOT NULL,

 TYPE SMALLINT NOT NULL,

 VALUE VARCHAR(999),

       CONSTRAINT TBTRACKCUSTOMFIELDS_PK PRIMARY KEY ( CUSTOMID ) ENABLE);

       /

 ALTER TABLE TBTRACKCUSTOMFIELDS

 ADD CONSTRAINT CUSTOMFIELDS_EVENTS_FK1 FOREIGN KEY ( EVENTID )

 REFERENCES TBTRACKEVENTS ( EVENTID ) ENABLE;

       /        

   CREATE SEQUENCE TBTRACKCUSTOMFIELDS_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

       /

 CREATE OR REPLACE TRIGGER TBTRACKCUSTOMFIELDS_TRG

 BEFORE INSERT ON TBTRACKCUSTOMFIELDS

  FOR EACH ROW

 BEGIN

   SELECT TBTRACKCUSTOMFIELDS_SEQ.NEXTVAL INTO :NEW.CUSTOMID FROM DUAL;

       END;                

       /

       ALTER TRIGGER TBTRACKCUSTOMFIELDS_TRG ENABLE;

/

CREATE TABLE TBTRACKINSTANCE

(

 INSTANCEID SMALLINT NOT NULL,

 SERVERID SMALLINT,

 LASTUPDATE DATE

, CONSTRAINT TBTRACKINSTANCE_PK PRIMARY KEY ( INSTANCEID ) ENABLE);

CREATE SEQUENCE TBTRACKINSTANCE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER TBTRACKINSTANCE_TRG

BEFORE INSERT ON TBTRACKINSTANCE

FOR EACH ROW

BEGIN

 SELECT TBTRACKINSTANCE_SEQ.NEXTVAL INTO :NEW.INSTANCEID FROM DUAL;

END;

/

ALTER TRIGGER TBTRACKINSTANCE_TRG ENABLE;

CREATE TABLE TBTRACKSEARCHWORK

(

 IDSEARCHWORK NUMBER NOT NULL,

 SEARCHID NUMBER NOT NULL,

 SERVERID SMALLINT,

 INSTANCEID SMALLINT

, CONSTRAINT TBTRACKSEARCHWORK_PK PRIMARY KEY (IDSEARCHWORK) ENABLE);

ALTER TABLE TBTRACKSEARCHWORK

ADD CONSTRAINT SEARCHWORK_TBTRACK_FK1 FOREIGN KEY (INSTANCEID)

REFERENCES TBTRACKINSTANCE (INSTANCEID) ENABLE;

ALTER TABLE TBTRACKSEARCHWORK

ADD CONSTRAINT SEARCHWORK_TBTRACK_FK2 FOREIGN KEY (SEARCHID)

REFERENCES TBTRACKSEARCH (SEARCHID) ENABLE;

CREATE SEQUENCE TBTRACKSEARCHWORK_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER TBTRACKSEARCHWORK_TRG

BEFORE INSERT ON TBTRACKSEARCHWORK

FOR EACH ROW

BEGIN

 SELECT TBTRACKSEARCHWORK_SEQ.NEXTVAL INTO :NEW.IDSEARCHWORK FROM DUAL;

END;

/

ALTER TRIGGER TBTRACKSEARCHWORK_TRG ENABLE;

/

CREATE TABLE TBTRACKLIST

(

 ID NUMBER NOT NULL,

 DOCUMENTNUMBER NUMBER(10,0) NOT NULL,

 EMISSIONDATE DATE NOT NULL,

 DOCUMENTKEY1 NUMBER(19,0) NOT NULL,

 DOCUMENTKEY2 NUMBER(19,0) NOT NULL,

 DOCUMENTKEY3 NUMBER(10,0) NOT NULL,

 COMPANYEMITTER NVARCHAR2(200) NOT NULL,

 TOTALVALUE NUMBER(18,2),

 STATUS NUMBER(3,0),

 USERID NUMBER(10,0),

 CONSTRAINT PK_TBTRACKLIST PRIMARY KEY (ID)

);

/

CREATE SEQUENCE TBTRACKLIST_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE OR REPLACE TRIGGER TBTRACKLIST_TRG

BEFORE INSERT ON TBTRACKLIST

FOR EACH ROW

BEGIN

 SELECT TBTRACKLIST_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

CREATE TABLE TBTRACKSECTOR

(ID NUMBER NOT NULL,

 SECTOR NUMBER,

 SECTORNAME VARCHAR(150) NOT NULL,

 CNPJ NUMBER NOT NULL,

 OWNERID VARCHAR(36) NOT NULL,

 CONSTRAINT PK_TBTRACKSECTOR PRIMARY KEY (ID)

)

/

CREATE SEQUENCE TBTRACKSECTOR_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

/

CREATE OR REPLACE TRIGGER TBTRACKSECTOR_TRG

BEFORE INSERT ON TBTRACKSECTOR

FOR EACH ROW

BEGIN

 SELECT TBTRACKSECTOR_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

/

ALTER TRIGGER TBTRACKSECTOR_TRG ENABLE;

/

--Executar uma instrução por vez

CREATE TABLE TBTRACKRECAP

(

 RECAPID NUMBER NOT NULL,

 DOCUMENTKEY1 NUMBER NOT NULL,

 DOCUMENTKEY2 NUMBER NOT NULL,

 DOCUMENTKEY3 INT NOT NULL,

 JOBKEY VARCHAR(60) ,

 STATUS SMALLINT NULL,

 KIND SMALLINT NULL,

 INSERTEDDATE DATE NOT NULL,

 SEARCHID NUMBER NOT NULL,

 SERVERID SMALLINT NULL,

 CONSTRAINT TBTRACKRECAP_PK PRIMARY KEY (RECAPID) ENABLE

);

/

--Executar uma instrução por vez

CREATE SEQUENCE TBTRACKRECAP_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

/

--Executar uma instrução por vez

CREATE OR REPLACE TRIGGER TBTRACKRECAP_TRG

BEFORE INSERT ON TBTRACKRECAP

FOR EACH ROW

BEGIN

 SELECT TBTRACKRECAP_SEQ.NEXTVAL INTO :NEW.RECAPID FROM DUAL;

END;

/

--Executar uma instrução por vez

ALTER TRIGGER TBTRACKRECAP_TRG ENABLE;

CREATE TABLE TBTRACKENTERPRISE

(

 ID NUMBER NOT NULL,

 IDENTITYNUMBER NUMBER NOT NULL,

 OWNERID VARCHAR(36) NOT NULL,

 CONSTRAINT PK_TBTRACKENTERPRISE PRIMARY KEY (ID)

)

/

CREATE SEQUENCE TBTRACKENTERPRISE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

/

CREATE OR REPLACE TRIGGER TBTRACKENTERPRISE_TRG

BEFORE INSERT ON TBTRACKENTERPRISE

FOR EACH ROW

BEGIN

 SELECT TBTRACKENTERPRISE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

/

ALTER TRIGGER"TBTRACKENTERPRISE_TRG" ENABLE;

/

--Executar uma instrução por vez

CREATE TABLE TBTRACKCOLDCONTROL(

 ID NUMBER NOT NULL,

 COLDID NUMBER,

 LAST_ID NUMBER

);

/

CREATE SEQUENCE TBTRACKCOLDCONTROL_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER TBTRACKCOLDCONTROL_TRG

BEFORE INSERT ON TBTRACKCOLDCONTROL

FOR EACH ROW

BEGIN

 SELECT TBTRACKCOLDCONTROL_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

/

ALTER TRIGGER TBTRACKCOLDCONTROL_TRG ENABLE;

/

--------------------------------------------------------

--  DDL for Table TBDOCSEARCHPROCESS

--------------------------------------------------------

declare lcnt number;

BEGIN

     SELECT

       COUNT(table_name)

       INTO

         lcnt

       FROM

         user_tables

       where

         table_name= 'TBDOCSEARCHPROCESS';

     IF( lcnt = 0 ) THEN

         execute immediate 'CREATE TABLE "TBDOCSEARCHPROCESS"

                           ("PROCESSID" NUMBER(20,0),

              "DOCKEY1" NUMBER(20,0),

              "DOCKEY2" NUMBER(20,0),

              "DOCKEY3" NUMBER(20,0),

              "PROTOCOL" VARCHAR2(20))';

     END IF;

END;

/

--------------------------------------------------------

--  Constraints for Table TBDOCSEARCHPROCESS

--------------------------------------------------------

declare lcnt number;

BEGIN

     SELECT

       COUNT(table_name)

       INTO

         lcnt

       FROM

         user_tables

       where

         table_name= 'TBDOCSEARCHPROCESS';

     IF( lcnt = 0 ) THEN

         execute immediate 'ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("PROCESSID" NUMBER(20,0) NOT NULL ENABLE);

                            ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY1" NUMBER(20,0) NOT NULL ENABLE);

                            ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY2" NUMBER(20,0) NOT NULL ENABLE);

                            ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY3" NUMBER(20,0) NOT NULL ENABLE);

              ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("PROTOCOL" VARCHAR2(20) NOT NULL ENABLE);

                            ALTER TABLE "TBDOCSEARCHPROCESS" ADD CONSTRAINT "TBDOCSEARCHPROCESS_PK" PRIMARY KEY ("PROCESSID") ENABLE;';

     END IF;

END;

/

CREATE TABLE  TBTRACKCUSTOMFIELDS(

 CUSTOMID NUMBER NOT NULL,

 EVENTID NUMBER NOT NULL,

 TYPE SMALLINT NOT NULL,

 VALUE VARCHAR(999),

CONSTRAINT TBTRACKCUSTOMFIELDS_PK PRIMARY KEY ( CUSTOMID ) ENABLE);

 /

 ALTER TABLE TBTRACKCUSTOMFIELDS

 ADD CONSTRAINT CUSTOMFIELDS_EVENTS_FK1 FOREIGN KEY ( EVENTID )

 REFERENCES TBTRACKEVENTS ( EVENTID ) ENABLE;

/        

   CREATE SEQUENCE TBTRACKCUSTOMFIELDS_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

/

 CREATE OR REPLACE TRIGGER TBTRACKCUSTOMFIELDS_TRG

 BEFORE INSERT ON TBTRACKCUSTOMFIELDS

  FOR EACH ROW

 BEGIN

   SELECT TBTRACKCUSTOMFIELDS_SEQ.NEXTVAL INTO :NEW.CUSTOMID FROM DUAL;

       END;                

/

ALTER TRIGGER TBTRACKCUSTOMFIELDS_TRG ENABLE;

CREATE TABLE TBTRACKCOLDCONTROL(

 ID NUMBER NOT NULL,

 COLDID NUMBER,

 LAST_ID NUMBER

);

CREATE SEQUENCE TBTRACKCOLDCONTROL_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER TBTRACKCOLDCONTROL_TRG

BEFORE INSERT ON TBTRACKCOLDCONTROL

FOR EACH ROW

BEGIN

 SELECT TBTRACKCOLDCONTROL_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

/

ALTER TRIGGER TBTRACKCOLDCONTROL_TRG ENABLE;

/

/