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