--ABS-133019 ABS-137572

--CLEANROOMCUSTOMERTYPE
CREATE SEQUENCE SEQ_CLEANROOMCUSTOMERTYPE 
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMCUSTOMERTYPE
(
  CLEANROOMCUSTOMERTYPE_ID NUMBER NOT NULL,
  CODE                     VARCHAR2(12) NOT NULL,
  DESCRIPTION              VARCHAR2(30) NOT NULL ,
  ACTIVE                   VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_CLEANROOMCUSTYPE_ACTIVE CHECK (ACTIVE IN ('Y','N')),
  STERILE                  VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_CLEANROOMCUSTYPE_STERILE CHECK (STERILE IN ('Y','N')),
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
);

ALTER TABLE CLEANROOMCUSTOMERTYPE
  ADD CONSTRAINT XPKCLEANROOMCUSTOMERTYPE PRIMARY KEY (CLEANROOMCUSTOMERTYPE_ID);  

CREATE UNIQUE INDEX XAK1CLEANROOMCUSTOMERTYPE ON CLEANROOMCUSTOMERTYPE
(CODE ASC);
  
--CUSTOMEREXTRAINFO
ALTER TABLE CUSTOMEREXTRAINFO 
ADD (CLEANROOMCUSTOMERTYPE_ID NUMBER NULL,
     STERILIZATIONEXPIRATIONPERIOD NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CUSTEXTRAINFO_STERILEXPER CHECK (STERILIZATIONEXPIRATIONPERIOD IN (0, 1, 2)));
 
COMMENT ON COLUMN CUSTOMEREXTRAINFO.STERILIZATIONEXPIRATIONPERIOD IS '0 = Not Applicable
                                                                      1 = 6 Months
                                                                      2 = 12 Months';

ALTER TABLE CUSTOMEREXTRAINFO
ADD CONSTRAINT CUSTOMEREXTRAINFO_CLEANROOMCUSTTYPE FOREIGN KEY (CLEANROOMCUSTOMERTYPE_ID) REFERENCES CLEANROOMCUSTOMERTYPE(CLEANROOMCUSTOMERTYPE_ID);
  
CREATE INDEX XIF2CUSTOMEREXTRAINFO ON CUSTOMEREXTRAINFO(CLEANROOMCUSTOMERTYPE_ID);
  
 
