ERP 2.50:QA test plan 2.50/Modularity/Room material tables (PostgreSQL)
CREATE TABLE RMAT_MATERIAL
(
RMAT_MATERIAL_ID character varying(32) NOT NULL,
AD_CLIENT_ID character varying(32) NOT NULL,
AD_ORG_ID character varying(32) NOT NULL,
isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
CREATED timestamp without time zone NOT NULL DEFAULT now(),
CREATEDBY character varying(32) NOT NULL,
UPDATED timestamp without time zone NOT NULL DEFAULT now(),
UPDATEDBY character varying(32) NOT NULL,
NAME character varying(60) NOT NULL,
MATERIALTYPE character(1)
);
CREATE UNIQUE INDEX RMAT_MATERIAL_KEY ON RMAT_MATERIAL
(RMAT_MATERIAL_ID);
ALTER TABLE RMAT_MATERIAL ADD CONSTRAINT RMAT_MATERIAL_ISACTIVE_CHECK CHECK (ISACTIVE IN ('Y', 'N'));
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);
CREATE TABLE RMAT_REQUIRED
(
RMAT_REQUIRED_ID character varying(32) NOT NULL,
AD_CLIENT_ID character varying(32) NOT NULL,
AD_ORG_ID character varying(32) NOT NULL,
ISACTIVE character(1) NOT NULL DEFAULT 'Y'::bpchar,
CREATED timestamp without time zone NOT NULL DEFAULT now(),
CREATEDBY character varying(32) NOT NULL,
UPDATED timestamp without time zone NOT NULL DEFAULT now(),
UPDATEDBY character varying(32) NOT NULL,
LINE numeric(10) NULL,
ROOMS_ROOMSCHEDULE_ID character varying(32) NOT NULL,
MATERIALUSED_ID character varying(32) NOT NULL
);
CREATE UNIQUE INDEX RMAT_REQUIRED_KEY ON RMAT_REQUIRED
(RMAT_REQUIRED_ID);
ALTER TABLE RMAT_REQUIRED ADD
CONSTRAINT RMAT_REQUIRED_ISACTIVE_CHECK
CHECK (ISACTIVE IN ('Y', 'N'));
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 ROOMS_ROOMSCHEDULE ADD ISREQUIRED character(1) NOT NULL DEFAULT 'Y'::bpchar;
ALTER TABLE ROOMS_ROOMSCHEDULE ADD ADDMATERIAL character(1) NOT NULL DEFAULT 'N'::bpchar;
ALTER TABLE RMAT_REQUIRED ADD
CONSTRAINT RSCHEDULE_RMAT_REQUIRED
FOREIGN KEY (ROOMS_ROOMSCHEDULE_ID)
REFERENCES ROOMS_ROOMSCHEDULE (ROOMS_ROOMSCHEDULE_ID);
ALTER TABLE RMAT_REQUIRED ADD
CONSTRAINT RMATUSED_RMAT_REQUIRED
FOREIGN KEY (MATERIALUSED_ID)
REFERENCES RMAT_MATERIAL (RMAT_MATERIAL_ID);