Log in / create account
Openbravo.com
Partner Portal
Issues
Blogs
Forge
Exchange
University
Downloads
View source | Discuss page | Page history | Printable version   
ADVERTISEMENT
Accounting eLearning Courses
Partnerships
SourceForge.net Logo
Openbravo ERP at SourceForge

SourceForge.net Logo
Openbravo POS at SourceForge

Open Solution Alliance Logo
Openbravo at Open Solutions Alliance

Upgraders Testing/Create tables (Oracle)

Script for 2.40/2.3x 

/************************C_ROOM TABLE******************************/


CREATE TABLE C_ROOM
(
  C_ROOM_ID          NUMBER(10)                 NOT NULL,
  AD_CLIENT_ID       NUMBER(10)                 NOT NULL,
  AD_ORG_ID          NUMBER(10)                 NOT NULL,
  ISACTIVE           CHAR(1 BYTE)               DEFAULT 'Y'                    NOT NULL,
  CREATED            DATE                       DEFAULT SYSDATE                NOT NULL,
  CREATEDBY          NUMBER(10)                 NOT NULL,
  UPDATED            DATE                       DEFAULT SYSDATE               NOT NULL,
  UPDATEDBY          NUMBER(10)                 NOT NULL,
  NAME               NVARCHAR2(60)              NOT NULL,
  DESCRIPTION        NVARCHAR2(255),
  ROOMTYPE           CHAR(2 BYTE),
  LOCATION           NVARCHAR2(255)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX C_ROOM_KEY ON C_ROOM
(C_ROOM_ID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



ALTER TABLE C_ROOM ADD (
  CONSTRAINT C_ROOM_ISACTIVE_CHECK
 CHECK (ISACTIVE IN ('Y', 'N')));

ALTER TABLE C_ROOM ADD (
  CONSTRAINT C_ROOM_KEY
 PRIMARY KEY
 (C_ROOM_ID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));


ALTER TABLE C_ROOM ADD (
  CONSTRAINT C_ROOM_ORG 
 FOREIGN KEY (AD_ORG_ID) 
 REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE C_ROOM ADD (
  CONSTRAINT ADCLIENT_C_ROOM 
 FOREIGN KEY (AD_CLIENT_ID) 
 REFERENCES AD_CLIENT (AD_CLIENT_ID));

/*********************************************C_ROOMSCHEDULE***************************/ 

CREATE TABLE C_ROOMSCHEDULE
(
  C_ROOMSCHEDULE_ID  NUMBER(10)                 NOT NULL,
  AD_CLIENT_ID       NUMBER(10)                 NOT NULL,
  AD_ORG_ID          NUMBER(10)                 NOT NULL,
  ISACTIVE           CHAR(1 BYTE)               DEFAULT 'Y'                     NOT NULL,
  CREATED            DATE                       DEFAULT SYSDATE                NOT NULL,
  CREATEDBY          NUMBER(10)                 NOT NULL,
  UPDATED            DATE                       DEFAULT SYSDATE                NOT NULL,
  UPDATEDBY          NUMBER(10)                 NOT NULL,
  C_ROOM_ID          NUMBER(10)                 NOT NULL,
  TOPIC              NVARCHAR2(255),
  RESERVATIONDATE    DATE                       NOT NULL,
  STARTTIME          DATE                       NOT NULL,
  ENDTIME            DATE                       NOT NULL,
  AD_USER_ID         NUMBER(10),
  FREQUENCY          CHAR(2 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX C_ROOMSCHEDULE_KEY ON C_ROOMSCHEDULE
(C_ROOMSCHEDULE_ID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;




ALTER TABLE C_ROOMSCHEDULE ADD (
  CONSTRAINT C_ROOMSCHEDULE_ISACTIVE_CHECK
 CHECK (ISACTIVE IN ('Y', 'N')));

ALTER TABLE C_ROOMSCHEDULE ADD (
  CONSTRAINT C_ROOMSCHEDULE_KEY
 PRIMARY KEY
 (C_ROOMSCHEDULE_ID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));


ALTER TABLE C_ROOMSCHEDULE ADD (
  CONSTRAINT C_ROOMSCHEDULE_ORG 
 FOREIGN KEY (AD_ORG_ID) 
 REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE C_ROOMSCHEDULE ADD (
  CONSTRAINT ADCLIENT_C_ROOMSCHEDULE 
 FOREIGN KEY (AD_CLIENT_ID) 
 REFERENCES AD_CLIENT (AD_CLIENT_ID));

ALTER TABLE C_ROOMSCHEDULE ADD (
  CONSTRAINT C_ROOMSCHEDULE_USER
 FOREIGN KEY (AD_USER_ID) 
 REFERENCES AD_USER (AD_USER_ID));

ALTER TABLE C_ROOMSCHEDULE ADD (
  CONSTRAINT C_ROOMSCHEDULE_ROOM
 FOREIGN KEY (C_ROOM_ID) 
 REFERENCES C_ROOM (C_ROOM_ID));


/***********************************************C_ROOMATTEND*********************************/

CREATE TABLE C_ROOMATTEND
(
  C_ROOMATTEND_ID           NUMBER(10)          NOT NULL,
  AD_CLIENT_ID              NUMBER(10)          NOT NULL,
  AD_ORG_ID                 NUMBER(10)          NOT NULL,
  ISACTIVE                  CHAR(1 BYTE)        DEFAULT 'Y'                     NOT NULL,
  CREATED                   DATE                DEFAULT SYSDATE               NOT NULL,
  CREATEDBY                 NUMBER(10)          NOT NULL,
  UPDATED                   DATE                DEFAULT SYSDATE                NOT NULL,
  UPDATEDBY                 NUMBER(10)          NOT NULL,
  C_ROOMSCHEDULE_ID            NUMBER(10)               NOT NULL,
  MEETINGROLE               NVARCHAR2(255),
  TYPEATTENDER              CHAR(2 BYTE),
  DEPARTMENT                CHAR(2 BYTE),
  AD_USER_ID                NUMBER(10),
  C_BPARTNER_ID             NUMBER(10)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX C_ROOMATTEND_KEY ON C_ROOMATTEND
(C_ROOMATTEND_ID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE C_ROOMATTEND ADD (
  CONSTRAINT C_ROOMATTEND_ISACTIVE_CHECK
 CHECK (ISACTIVE IN ('Y', 'N')));

ALTER TABLE C_ROOMATTEND ADD (
  CONSTRAINT C_ROOMATTEND_KEY
 PRIMARY KEY
 (C_ROOMATTEND_ID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));


ALTER TABLE C_ROOMATTEND ADD (
  CONSTRAINT ADCLIENT_C_ROOMATTEND 
 FOREIGN KEY (AD_CLIENT_ID) 
 REFERENCES AD_CLIENT (AD_CLIENT_ID));

ALTER TABLE C_ROOMATTEND ADD (
  CONSTRAINT AUSER_C_ROOMATTEND
 FOREIGN KEY (AD_USER_ID) 
 REFERENCES AD_USER (AD_USER_ID));

ALTER TABLE C_ROOMATTEND ADD (
  CONSTRAINT ADORG_C_ROOMATTEND
 FOREIGN KEY (AD_ORG_ID) 
 REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE C_ROOMATTEND ADD (
  CONSTRAINT BPARTNER_C_ROOMATTEND
 FOREIGN KEY (C_BPARTNER_ID) 
 REFERENCES C_BPARTNER (C_BPARTNER_ID));

Script for 2.50 or higher 

/************************C_ROOM TABLE******************************/

CREATE TABLE C_ROOM
(
 C_ROOM_ID  	     VARCHAR2(32)                 NOT NULL,
 AD_CLIENT_ID       VARCHAR2(32)                 NOT NULL,
 AD_ORG_ID          VARCHAR2(32)                 NOT NULL,
 ISACTIVE           CHAR(1 BYTE)               DEFAULT 'Y'                   NOT NULL,
 CREATED            DATE                       DEFAULT SYSDATE               NOT NULL,
 CREATEDBY          VARCHAR2(32)                 NOT NULL,
 UPDATED            DATE                       DEFAULT SYSDATE               NOT NULL,
 UPDATEDBY          VARCHAR2(32)                 NOT NULL,
 NAME               NVARCHAR2(60)              NOT NULL,
 DESCRIPTION        NVARCHAR2(255),
 ROOMTYPE	     CHAR(2 BYTE),
 LOCATION	     NVARCHAR2(255)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           MINEXTENTS       1
           MAXEXTENTS       2147483645
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX C_ROOM_KEY ON C_ROOM
(C_ROOM_ID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           MINEXTENTS       1
           MAXEXTENTS       2147483645
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
NOPARALLEL;



ALTER TABLE C_ROOM ADD (
 CONSTRAINT C_ROOM_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));

ALTER TABLE C_ROOM ADD (
 CONSTRAINT C_ROOM_KEY
PRIMARY KEY
(C_ROOM_ID)
   USING INDEX 
   TABLESPACE USERS
   PCTFREE    10
   INITRANS   2
   MAXTRANS   255
   STORAGE    (
               INITIAL          64K
               MINEXTENTS       1
               MAXEXTENTS       2147483645
               PCTINCREASE      0
              ));


ALTER TABLE C_ROOM ADD (
 CONSTRAINT C_ROOM_ORG 
FOREIGN KEY (AD_ORG_ID) 
REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE C_ROOM ADD (
 CONSTRAINT ADCLIENT_C_ROOM 
FOREIGN KEY (AD_CLIENT_ID) 
REFERENCES AD_CLIENT (AD_CLIENT_ID));


/*********************************************C_ROOMSCHEDULE***************************/

CREATE TABLE C_ROOMSCHEDULE
(
 C_ROOMSCHEDULE_ID  VARCHAR2(32)                 NOT NULL,
 AD_CLIENT_ID       VARCHAR2(32)                 NOT NULL,
 AD_ORG_ID          VARCHAR2(32)                 NOT NULL,
 ISACTIVE           CHAR(1 BYTE)               DEFAULT 'Y'                   NOT NULL,
 CREATED            DATE                       DEFAULT SYSDATE               NOT NULL,
 CREATEDBY          VARCHAR2(32)                 NOT NULL,
 UPDATED            DATE                       DEFAULT SYSDATE               NOT NULL,
 UPDATEDBY          VARCHAR2(32)                 NOT NULL,
 C_ROOM_ID	     VARCHAR2(32)			NOT NULL,
 TOPIC        	     NVARCHAR2(255),
 RESERVATIONDATE    DATE			NOT NULL,
 STARTTIME          DATE			NOT NULL,
 ENDTIME            DATE			NOT NULL,
 AD_USER_ID         VARCHAR2(32),
 FREQUENCY          CHAR(2 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           MINEXTENTS       1
           MAXEXTENTS       2147483645
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX C_ROOMSCHEDULE_KEY ON C_ROOMSCHEDULE
(C_ROOMSCHEDULE_ID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           MINEXTENTS       1
           MAXEXTENTS       2147483645
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
NOPARALLEL;




ALTER TABLE C_ROOMSCHEDULE ADD (
 CONSTRAINT C_ROOMSCHEDULE_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));

ALTER TABLE C_ROOMSCHEDULE ADD (
 CONSTRAINT C_ROOMSCHEDULE_KEY
PRIMARY KEY
(C_ROOMSCHEDULE_ID)
   USING INDEX 
   TABLESPACE USERS
   PCTFREE    10
   INITRANS   2
   MAXTRANS   255
   STORAGE    (
               INITIAL          64K
               MINEXTENTS       1
               MAXEXTENTS       2147483645
               PCTINCREASE      0
              ));


ALTER TABLE C_ROOMSCHEDULE ADD (
 CONSTRAINT C_ROOMSCHEDULE_ORG 
FOREIGN KEY (AD_ORG_ID) 
REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE C_ROOMSCHEDULE ADD (
 CONSTRAINT ADCLIENT_C_ROOMSCHEDULE 
FOREIGN KEY (AD_CLIENT_ID) 
REFERENCES AD_CLIENT (AD_CLIENT_ID));

ALTER TABLE C_ROOMSCHEDULE ADD (
 CONSTRAINT C_ROOMSCHEDULE_USER
FOREIGN KEY (AD_USER_ID) 
REFERENCES AD_USER (AD_USER_ID));

ALTER TABLE C_ROOMSCHEDULE ADD (
 CONSTRAINT C_ROOMSCHEDULE_ROOM
FOREIGN KEY (C_ROOM_ID) 
REFERENCES C_ROOM (C_ROOM_ID));


/***********************************************C_ROOMATTEND*********************************/

CREATE TABLE C_ROOMATTEND
(
 C_ROOMATTEND_ID           VARCHAR2(32)          NOT NULL,
 AD_CLIENT_ID              VARCHAR2(32)          NOT NULL,
 AD_ORG_ID                 VARCHAR2(32)          NOT NULL,
 ISACTIVE                  CHAR(1 BYTE)        DEFAULT 'Y'                   NOT NULL,
 CREATED                   DATE                DEFAULT SYSDATE               NOT NULL,
 CREATEDBY                 VARCHAR2(32)          NOT NULL,
 UPDATED                   DATE                DEFAULT SYSDATE               NOT NULL,
 UPDATEDBY                 VARCHAR2(32)          NOT NULL,
 C_ROOMSCHEDULE_ID            VARCHAR2(32)		NOT NULL,
 MEETINGROLE               NVARCHAR2(255),
 TYPEATTENDER              CHAR(2 BYTE),
 DEPARTMENT                CHAR(2 BYTE),
 AD_USER_ID                VARCHAR2(32),
 C_BPARTNER_ID             VARCHAR2(32)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           MINEXTENTS       1
           MAXEXTENTS       2147483645
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX C_ROOMATTEND_KEY ON C_ROOMATTEND
(C_ROOMATTEND_ID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
           INITIAL          64K
           MINEXTENTS       1
           MAXEXTENTS       2147483645
           PCTINCREASE      0
           BUFFER_POOL      DEFAULT
          )
NOPARALLEL;


ALTER TABLE C_ROOMATTEND ADD (
 CONSTRAINT C_ROOMATTEND_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));

ALTER TABLE C_ROOMATTEND ADD (
 CONSTRAINT C_ROOMATTEND_KEY
PRIMARY KEY
(C_ROOMATTEND_ID)
   USING INDEX 
   TABLESPACE USERS
   PCTFREE    10
   INITRANS   2
   MAXTRANS   255
   STORAGE    (
               INITIAL          64K
               MINEXTENTS       1
               MAXEXTENTS       2147483645
               PCTINCREASE      0
              ));


ALTER TABLE C_ROOMATTEND ADD (
 CONSTRAINT ADCLIENT_C_ROOMATTEND 
FOREIGN KEY (AD_CLIENT_ID) 
REFERENCES AD_CLIENT (AD_CLIENT_ID));

ALTER TABLE C_ROOMATTEND ADD (
 CONSTRAINT AUSER_C_ROOMATTEND
FOREIGN KEY (AD_USER_ID) 
REFERENCES AD_USER (AD_USER_ID));

ALTER TABLE C_ROOMATTEND ADD (
 CONSTRAINT ADORG_C_ROOMATTEND
FOREIGN KEY (AD_ORG_ID) 
REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE C_ROOMATTEND ADD (
 CONSTRAINT BPARTNER_C_ROOMATTEND
FOREIGN KEY (C_BPARTNER_ID) 
REFERENCES C_BPARTNER (C_BPARTNER_ID));

Retrieved from "http://wiki.openbravo.com/wiki/Upgraders_Testing/Create_tables_%28Oracle%29"

This page has been accessed 1,502 times. This page was last modified 10:39, 12 March 2009. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.


Category: Upgraders Testing ERP