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