View source | Discuss this page | Page history | Printable version   

ERP 2.50:QA test plan 2.50/Modularity/Room tables (Oracle)

/***ROOMS_ROOM TABLE******************************/

CREATE TABLE ROOMS_ROOM
(
 ROOMS_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 ROOMS_ROOM_KEY ON ROOMS_ROOM
(ROOMS_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 ROOMS_ROOM ADD (
 CONSTRAINT ROOMS_ROOM_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));

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


ALTER TABLE ROOMS_ROOM ADD (
 CONSTRAINT ROOMS_ROOM_ORG 
FOREIGN KEY (AD_ORG_ID) 
REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE ROOMS_ROOM ADD (
 CONSTRAINT ADCLIENT_ROOMS_ROOM 
FOREIGN KEY (AD_CLIENT_ID) 
REFERENCES AD_CLIENT (AD_CLIENT_ID));


/***ROOMS_ROOMSCHEDULE***************************/

CREATE TABLE ROOMS_ROOMSCHEDULE
(
 ROOMS_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,
 ROOMS_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 ROOMS_ROOMSCHEDULE_KEY ON ROOMS_ROOMSCHEDULE
(ROOMS_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 ROOMS_ROOMSCHEDULE ADD (
 CONSTRAINT ROOMS_SCHEDULE_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));

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


ALTER TABLE ROOMS_ROOMSCHEDULE ADD (
 CONSTRAINT ROOMS_ROOMSCHEDULE_ORG 
FOREIGN KEY (AD_ORG_ID) 
REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE ROOMS_ROOMSCHEDULE ADD (
 CONSTRAINT ADCLIENT_ROOMS_ROOMSCHEDULE 
FOREIGN KEY (AD_CLIENT_ID) 
REFERENCES AD_CLIENT (AD_CLIENT_ID));

ALTER TABLE ROOMS_ROOMSCHEDULE ADD (
 CONSTRAINT ROOMS_ROOMSCHEDULE_USER
FOREIGN KEY (AD_USER_ID) 
REFERENCES AD_USER (AD_USER_ID));

ALTER TABLE ROOMS_ROOMSCHEDULE ADD (
 CONSTRAINT ROOMS_ROOMSCHEDULE_ROOM
FOREIGN KEY (ROOMS_ROOM_ID) 
REFERENCES ROOMS_ROOM (ROOMS_ROOM_ID));


/***ROOMS_ROOMATTEND*********************************/

CREATE TABLE ROOMS_ROOMATTEND
(
 ROOMS_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,
 ROOMS_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 ROOMS_ROOMATTEND_KEY ON ROOMS_ROOMATTEND
(ROOMS_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 ROOMS_ROOMATTEND ADD (
 CONSTRAINT ROOMS_ATTEND_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));

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


ALTER TABLE ROOMS_ROOMATTEND ADD (
 CONSTRAINT ADCLIENT_ROOMS_ROOMATTEND 
FOREIGN KEY (AD_CLIENT_ID) 
REFERENCES AD_CLIENT (AD_CLIENT_ID));

ALTER TABLE ROOMS_ROOMATTEND ADD (
 CONSTRAINT AUSER_ROOMS_ROOMATTEND
FOREIGN KEY (AD_USER_ID) 
REFERENCES AD_USER (AD_USER_ID));

ALTER TABLE ROOMS_ROOMATTEND ADD (
 CONSTRAINT ADORG_ROOMS_ROOMATTEND
FOREIGN KEY (AD_ORG_ID) 
REFERENCES AD_ORG (AD_ORG_ID));

ALTER TABLE ROOMS_ROOMATTEND ADD (
 CONSTRAINT BPARTNER_ROOMS_ROOMATTEND
FOREIGN KEY (C_BPARTNER_ID) 
REFERENCES C_BPARTNER (C_BPARTNER_ID));

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:QA_test_plan_2.50/Modularity/Room_tables_(Oracle)"

This page has been accessed 4,789 times. This page was last modified on 3 April 2012, at 11:00. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.