create_irradiation_table.sql

/*
        To be run as CMS_MTD_CORE_COND
*/

INSERT INTO CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS (NAME, IS_RECORD_DELETED, EXTENSION_TABLE_NAME, COMMENT_DESCRIPTION) VALUES ('IRRADIATIONS', 'F', 'IRRADIATIONS', 'Irradiation data');
/*
	To be run as CMS_MTD_TMING_COND
*/

DROP TABLE CMS_MTD_TMING_COND.IRRADIATIONS CASCADE CONSTRAINTS;

CREATE TABLE CMS_MTD_TMING_COND.IRRADIATIONS
(
  RECORD_ID                NUMBER(38)	NOT NULL,
  CONDITION_DATA_SET_ID	   NUMBER(38)	NOT NULL,
  RADIATION_TYPE  	   CHAR(1)	NOT NULL
                           CONSTRAINT RADTYPE_CONSTRAINT CHECK (RADIATION_TYPE IN ('G', 'P', 'N', 'E')),
  DOSERATE		   NUMBER(38)	NOT NULL,
  DOSE			   NUMBER(38)	NOT NULL
)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE;

CREATE UNIQUE INDEX CMS_MTD_TMING_COND.RRDTNS_PK ON CMS_MTD_TMING_COND.IRRADIATIONS (RECORD_ID);

ALTER TABLE CMS_MTD_TMING_COND.IRRADIATIONS ADD (
  CONSTRAINT RRDTNS_PK
  PRIMARY KEY
  (RECORD_ID)
  USING INDEX CMS_MTD_TMING_COND.RRDTNS_PK
  ENABLE VALIDATE);


CREATE INDEX CMS_MTD_TMING_COND.RRDTNS_FK_I ON CMS_MTD_TMING_COND.IRRADIATIONS (CONDITION_DATA_SET_ID);

ALTER TABLE CMS_MTD_TMING_COND.IRRADIATIONS ADD (
  CONSTRAINT RRDTNS_FK 
  FOREIGN KEY (CONDITION_DATA_SET_ID) 
  REFERENCES CMS_MTD_CORE_COND.COND_DATA_SETS (CONDITION_DATA_SET_ID)
  ENABLE VALIDATE);

GRANT SELECT ON CMS_MTD_TMING_COND.IRRADIATIONS TO CMS_MTD_TMING_CONDITION_READER;

GRANT SELECT ON CMS_MTD_TMING_COND.IRRADIATIONS TO PUBLIC;

INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'singleBarCrystal' AND IS_RECORD_DELETED = 'F'),
	   (SELECT KIND_OF_CONDITION_ID FROM
	   CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'IRRADIATIONS' AND
	   NAME = 'IRRADIATIONS'), 'IRRADIATIONS for singleBarCrystals', 'F'); 

INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'LYSOMatrix #1' AND IS_RECORD_DELETED = 'F'),
	   (SELECT KIND_OF_CONDITION_ID FROM
	   CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'IRRADIATIONS' AND
	   NAME = 'IRRADIATIONS'), 'IRRADIATIONS for LYSOMatrix #1', 'F'); 

INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'LYSOMatrix #2' AND IS_RECORD_DELETED = 'F'),
	   (SELECT KIND_OF_CONDITION_ID FROM
	   CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'IRRADIATIONS' AND
	   NAME = 'IRRADIATIONS'), 'IRRADIATIONS for LYSOMatrix #2', 'F'); 

INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'LYSOMatrix #3' AND IS_RECORD_DELETED = 'F'),
	   (SELECT KIND_OF_CONDITION_ID FROM
	   CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'IRRADIATIONS' AND
	   NAME = 'IRRADIATIONS'), 'IRRADIATIONS for LYSOMatrix #3', 'F'); 

GRANT INSERT ON CMS_MTD_TMING_COND.IRRADIATIONS TO CMS_MTD_CORE_CONDITION_WRITER;
GRANT UPDATE ON CMS_MTD_TMING_COND.IRRADIATIONS TO CMS_MTD_CORE_CONDITION_WRITER;
GRANT INSERT ON CMS_MTD_TMING_COND.IRRADIATIONS TO CMS_MTD_PRTYP_TMING_WRT;
GRANT UPDATE ON CMS_MTD_TMING_COND.IRRADIATIONS TO CMS_MTD_PRTYP_TMING_WRT;