| DDL Code |
CREATE TABLE EXPRESS.EXPT_RSRC(
EXPT_RSRC_ID VARCHAR2(50) NOT NULL,
STAT_ID VARCHAR2(20) NOT NULL,
EMS_RSRC_ID NUMBER(38, 0),
RSRC_NM VARCHAR2(120) NOT NULL,
EXPT_RSRC_TYPE_ID VARCHAR2(50) NOT NULL,
EXPT_RSRC_SUBTYPE_ID VARCHAR2(50),
NEXT_AVAIL_DTE DATE DEFAULT sysdate,
URL VARCHAR2(2048),
RESTORE_TIME NUMBER(2, 0),
RESTORE_TIME_UOM VARCHAR2(3),
SETUP_TIME NUMBER(2, 0),
SETUP_TIME_UOM VARCHAR2(3),
BRKDWN_TIME NUMBER(2, 0),
BRKDWN_TIME_UOM VARCHAR2(3),
AVAIL VARCHAR2(50),
MAX_ALLOC_PER NUMBER(3, 0),
TEMP_MIN VARCHAR2(5),
TEMP_MAX VARCHAR2(5),
TEMP_UOM VARCHAR2(3),
PRESS_MIN VARCHAR2(5),
PRESS_MAX VARCHAR2(5),
PRESS_UOM VARCHAR2(3),
CELL_SIZE_HT VARCHAR2(10),
CELL_SIZE_DIA VARCHAR2(50),
CELL_SIZE_UOM VARCHAR2(3),
SMPL_SPACE_HT VARCHAR2(50),
SMPL_SPACE_DIA VARCHAR2(20),
SMPL_SPACE_UOM VARCHAR2(3),
BORE_SIZE_HT VARCHAR2(50),
BORE_SIZE_DIA VARCHAR2(50),
BORE_SIZE_UOM VARCHAR2(3),
INTERFACE_CONN VARCHAR2(255),
DIST_INT_BEAM_CENTER VARCHAR2(255),
DIST_INT_BEAM_CENTER_ADJ VARCHAR2(25),
DIST_INT_BEAM_CENTER_UOM VARCHAR2(3),
DIST_STICK_BEAM_CENTER VARCHAR2(255),
DIST_STICK_BEAM_CENTER_UOM VARCHAR2(3),
TAIL_DIST_BEAM_CENTER VARCHAR2(50),
TAIL_DIST_CENTER_UOM VARCHAR2(3),
BEAM_CENTER_SMPL_SP_BTM VARCHAR2(10),
BEAM_CENTER_BTM_UOM VARCHAR2(3),
CMNT VARCHAR2(2000),
MANUF_ID VARCHAR2(30),
Model RAW(1),
MANFU_SERIAL_NBR VARCHAR2(50),
COST NUMBER(12, 2),
COMM_DTE DATE DEFAULT sysdate NOT NULL,
REV_DTE DATE DEFAULT sysdate NOT NULL,
VIEW_CD CHAR(1) DEFAULT 'N' NOT NULL,
DS_ASSET_ID VARCHAR2(30),
MEL_ID VARCHAR2(75),
ORNL_PROP_NBR VARCHAR2(10),
FACIL_ID VARCHAR2(10) NOT NULL,
LOC_ID VARCHAR2(50),
QTY NUMBER(38, 0) DEFAULT 1,
APPRV_IND CHAR(1) DEFAULT 'N' NOT NULL,
DRAW_NBR VARCHAR2(50),
CAT_IND CHAR(1) DEFAULT 'N' NOT NULL,
ELEC_CERT_ID VARCHAR2(25),
WEB_DESC VARCHAR2(2000),
CONSTRAINT PK_EXPT_RSRC PRIMARY KEY (EXPT_RSRC_ID),
CONSTRAINT FK_ERS_TO_RXPT_RSRC FOREIGN KEY (STAT_ID)
REFERENCES EXPRESS.EXPT_RSRC_STAT(STAT_ID),
CONSTRAINT fk_expt_rsrc_to_manuf FOREIGN KEY (MANUF_ID)
REFERENCES EXPRESS.EXPT_RSRC_MANUF(MANUF_ID),
CONSTRAINT fk_er_to_expt_rsrc_facil FOREIGN KEY (FACIL_ID)
REFERENCES EXPRESS.EXPT_RSRC_FACIL(FACIL_ID),
CONSTRAINT fk_setup_uom_to_uom FOREIGN KEY (SETUP_TIME_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_temp_uom_to_uom FOREIGN KEY (TEMP_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_press_uom_to_uom FOREIGN KEY (PRESS_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_cell_uom_to_uom FOREIGN KEY (CELL_SIZE_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_bore_uom_to_uom FOREIGN KEY (BORE_SIZE_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_smpl_uom_to_uom FOREIGN KEY (SMPL_SPACE_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_dibc_uom_to_uom FOREIGN KEY (DIST_INT_BEAM_CENTER_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_dsbc_uom_to_uom FOREIGN KEY (DIST_STICK_BEAM_CENTER_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_brkdwn_uom_to_uom FOREIGN KEY (BRKDWN_TIME_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_restore_uom_to_uom FOREIGN KEY (RESTORE_TIME_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_tail_uom_to_uom FOREIGN KEY (TAIL_DIST_CENTER_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_expt_rsrc_to_expt_rsrc_loc FOREIGN KEY (FACIL_ID, LOC_ID)
REFERENCES EXPRESS.EXPT_RSRC_LOC(FACIL_ID, LOC_ID),
CONSTRAINT FK_EXPT_RSRC_TO_ELEC_CERT FOREIGN KEY (ELEC_CERT_ID)
REFERENCES EXPRESS.ELEC_CERT(ELEC_CERT_ID),
CONSTRAINT RefUOM4241 FOREIGN KEY (BEAM_CENTER_BTM_UOM)
REFERENCES EXPRESS.UOM(UNIT_ID),
CONSTRAINT fk_expt_rsrc_to_expt_rsrc_type FOREIGN KEY (EXPT_RSRC_TYPE_ID, FACIL_ID)
REFERENCES EXPRESS.EXPT_RSRC_TYPE(EXPT_RSRC_TYPE_ID, FACIL_ID),
CONSTRAINT fk_er_to_expt_rsrc_subtype FOREIGN KEY (EXPT_RSRC_TYPE_ID, EXPT_RSRC_SUBTYPE_ID, FACIL_ID)
REFERENCES EXPRESS.EXPT_RSRC_SUBTYPE(EXPT_RSRC_TYPE_ID, EXPT_RSRC_SUBTYPE_ID, FACIL_ID)
)
NOCOMPRESS
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.RSRC_NM IS 'Name: A long description name usually accompanied by an abbreviated code.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.NEXT_AVAIL_DTE IS 'Next Available Date: Time when the equipment is expected to be available for a new commitment.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.URL IS 'Universal Resource Locator: The global address of documents and other resources on the web. In this case the URL to the specifications of the equipment.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.RESTORE_TIME IS 'Restore Time: The typical amount of time in hours it takes to restore the equipment back to its base configuration.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.RESTORE_TIME_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.SETUP_TIME IS 'Setup Time: The typical amount of time in hours it takes to setup the equipment.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.SETUP_TIME_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.BRKDWN_TIME_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.AVAIL IS 'Operating Schedule: The normal operating schedule in day and hours formatted as follows.
M T W T F | 8:00 - 16:00'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.TEMP_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.PRESS_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.CELL_SIZE_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.SMPL_SPACE_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.BORE_SIZE_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.INTERFACE_CONN IS 'Description: Often used in validation tables accompanying an abbreviated identifer.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.DIST_INT_BEAM_CENTER IS 'Description: Often used in validation tables accompanying an abbreviated identifer.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.DIST_INT_BEAM_CENTER_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.DIST_STICK_BEAM_CENTER IS 'Description: Often used in validation tables accompanying an abbreviated identifer.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.DIST_STICK_BEAM_CENTER_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.TAIL_DIST_CENTER_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.BEAM_CENTER_BTM_UOM IS 'Length Unit Identifier: Abbreviated unit of length.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.CMNT IS 'Comment: General comments.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.COMM_DTE IS 'Date: Date and time to seconds.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.REV_DTE IS 'Date: Date and time to seconds.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.MEL_ID IS 'Master Equipment List Identifier: The id assigned to the resource in HFIR''s Master Equipment List system.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.FACIL_ID IS 'Facility Identifer: A unique abbreviated identifier for the facilty in which the proposed experiment is to take place.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.APPRV_IND IS 'Approval Indicator: Indicates if the resource needs approval to be scheduled.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.DRAW_NBR IS 'Drawing Number: NScD drawing number.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.CAT_IND IS 'Catalog Indicator: Indicates the resource should be treated as a catalog item. Catalog items are low support required, generally available and easily replenishable.'
;
COMMENT ON COLUMN EXPRESS.EXPT_RSRC.WEB_DESC IS 'Website Description: Text describing the experiment resource which will display on the public website.'
;
COMMENT ON TABLE EXPRESS.EXPT_RSRC IS 'Experiment Resource: Pool of resources used in the operation of a beam line or laboratory and for the execution of an approved experiment proposal.'
;
|
