--Order 20010648

ALTER TABLE STERILIZATIONRUN
	ADD (STERILIZATIONTIMESTAMP DATE NULL);


ALTER TABLE STERILIZATIONRUN
	ADD (REJECTTIMESTAMP DATE NULL);

ALTER TABLE STERILIZATIONRUN
	ADD (REJECTEMPLOYEE_ID NUMBER NULL);

ALTER TABLE STERILIZATIONRUN
	ADD (RECALLTIMESTAMP DATE NULL);


ALTER TABLE STERILIZATIONRUN
	ADD (RECALLEMPLOYEE_ID NUMBER NULL);

ALTER TABLE STERILIZATIONRUN
	ADD (RECALLREASONCODE_ID NUMBER NULL);

ALTER TABLE STERILIZATIONRUN
	ADD (RECALLREPORTPRINTED VARCHAR2(1) DEFAULT 'N' NOT NULL 
		CONSTRAINT  YN_ValidationField26 CHECK (RECALLREPORTPRINTED IN ('Y', 'N'))
		);

ALTER TABLE STERILIZATIONRUN
	ADD (STATUS NUMBER NULL CONSTRAINT Status_valid2 CHECK (STATUS IN (1, 2, 3)));

COMMENT ON COLUMN STERILIZATIONRUN.STATUS IS '1 = Sterilized
2 = Rejected
3 = Recalled';


CREATE  INDEX XIF6STERILIZATIONRUN ON STERILIZATIONRUN
(REJECTEMPLOYEE_ID   ASC)
TABLESPACE INDX;



CREATE  INDEX XIF7STERILIZATIONRUN ON STERILIZATIONRUN
(RECALLEMPLOYEE_ID   ASC)
TABLESPACE INDX;



CREATE  INDEX XIF8STERILIZATIONRUN ON STERILIZATIONRUN
(RECALLREASONCODE_ID   ASC)
TABLESPACE INDX;



ALTER TABLE STERILIZATIONRUN
ADD CONSTRAINT STERILIZATIONRUN_REJECTEMPLOYE FOREIGN KEY (REJECTEMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID) ;



ALTER TABLE STERILIZATIONRUN
ADD CONSTRAINT STERILIZATIONRUN_RECALLEMPLOYE FOREIGN KEY (RECALLEMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID) ;



ALTER TABLE STERILIZATIONRUN
ADD CONSTRAINT STERILIZATIONRUN_RECALLREASONC FOREIGN KEY (RECALLREASONCODE_ID) REFERENCES REASONCODE (REASONCODE_ID) ;

update sterilizationrun s set s.status = 1 where s.rejectreasoncode_id is null;
commit;
update sterilizationrun s set s.status = 2 where s.rejectreasoncode_id is not null;
commit;

ALTER TABLE STERILIZATIONRUN MODIFY (STATUS NOT NULL);

