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));