ERP 2.50:QA test plan 2.50/Modularity/Room material tables (Oracle)
/***RMAT_MATERIAL TABLE******************************/
CREATE TABLE RMAT_MATERIAL
(
RMAT_MATERIAL_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,
MATERIALTYPE CHAR(1 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 RMAT_MATERIAL_KEY ON RMAT_MATERIAL
(RMAT_MATERIAL_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 RMAT_MATERIAL ADD (
CONSTRAINT RMAT_MATERIAL_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));
ALTER TABLE RMAT_MATERIAL ADD (
CONSTRAINT RMAT_MATERIAL_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 RMAT_MATERIAL ADD (
CONSTRAINT ORG_RMAT_MATERIAL
FOREIGN KEY (AD_ORG_ID)
REFERENCES AD_ORG (AD_ORG_ID));
ALTER TABLE RMAT_MATERIAL ADD (
CONSTRAINT ADCLIENT_RMAT_MATERIAL
FOREIGN KEY (AD_CLIENT_ID)
REFERENCES AD_CLIENT (AD_CLIENT_ID));
ALTER TABLE RMAT_MATERIAL ADD (
CONSTRAINT CREATEDBY_RMAT_MATERIAL
FOREIGN KEY (CREATEDBY)
REFERENCES AD_USER (AD_USER_ID));
ALTER TABLE RMAT_MATERIAL ADD (
CONSTRAINT UPDATEDBY_RMAT_MATERIAL
FOREIGN KEY (UPDATEDBY)
REFERENCES AD_USER (AD_USER_ID))
/***RMAT_REQUIRED TABLE******************************/
CREATE TABLE RMAT_REQUIRED
(
RMAT_REQUIRED_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,
LINE NUMBER(10) NULL,
ROOMS_ROOMSCHEDULE_ID VARCHAR2(32) NOT NULL,
MATERIALUSED_ID VARCHAR2(32) NOT NULL
)
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 RMAT_REQUIRED_KEY ON RMAT_REQUIRED
(RMAT_REQUIRED_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 RMAT_REQUIRED ADD (
CONSTRAINT RMAT_REQUIRED_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N')));
ALTER TABLE RMAT_REQUIRED ADD (
CONSTRAINT RMAT_REQUIRED_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 RMAT_REQUIRED ADD (
CONSTRAINT ORG_RMAT_REQUIRED
FOREIGN KEY (AD_ORG_ID)
REFERENCES AD_ORG (AD_ORG_ID));
ALTER TABLE RMAT_REQUIRED ADD (
CONSTRAINT ADCLIENT_RMAT_REQUIRED
FOREIGN KEY (AD_CLIENT_ID)
REFERENCES AD_CLIENT (AD_CLIENT_ID));
ALTER TABLE RMAT_REQUIRED ADD (
CONSTRAINT CREATEDBY_RMAT_REQUIRED
FOREIGN KEY (CREATEDBY)
REFERENCES AD_USER (AD_USER_ID));
ALTER TABLE RMAT_MATERIAL ADD (
CONSTRAINT UPDATEDBY_RMAT_REQUIRED
FOREIGN KEY (UPDATEDBY)
REFERENCES AD_USER (AD_USER_ID));
ALTER TABLE RMAT_MATERIAL ADD (
CONSTRAINT RSCHEDULE_RMAT_REQUIRED
FOREIGN KEY (ROOMS_ROOMSCHEDULE_ID)
REFERENCES ROOMS_ROOMSCHEDULE (ROOMS_ROOMSCHEDULE_ID));
ALTER TABLE RMAT_MATERIAL ADD (
CONSTRAINT RMATUSED_RMAT_REQUIRED
FOREIGN KEY (MATERIALUSED_ID)
REFERENCES RMAT_MATERIAL (RMAT_MATERIAL_ID));