-- File:	sdb_tables.sql
-- Purpose:	Create silicon database tables.
-- Created:	May  9, 2003 Stan K.
--                           Based on Atlas sct tables v5_9
--                           Original tables removed:
--				TEST_OCBER1
--				TEST_OCBER2
--				TEST_OCDC
--				TEST_WAFER_E_PARAM
--				TSTABCD
--				TSTABCDCHS
--				TSTDETIVS
--				TSTDETOPTS
--				TSTHYBRC
--				TSTMODXY
--				TSTMODZ
-- Modified:
--	      May 12, 2003 Stan K.
--                              Adapted table TSTDETMFRS for D0.
--                              Replaced 11 '*SER_NO NUMBER(14,0)' by '*GLO_ID VARCHAR2(35)'.
--                              Added PROD_TYPE column to ITEMS table.
--            May 13, 2003 Stan K.
--                              Added TSTDETIVS and TSTDETCVS tables.
--            May 15, 2003 Stan K.
--                              Added 1 or 2 columns into tables:
--                              DISASSMLOGS
--                              ITEM_IMAGES
--                              TEST_IMAGES
--                              TEST_RAWDATA
--                              - according to Atlas designer file of 12 May 2003.
--            May 20, 2003 Stan K.
--                              Added BATCH_LIST table to store legitimate batch names
--                              to be used in ITEMS table, for item types which are
--                              produced in batches, like sensors.
--                              Added columns: BATCH_NAME     to table ITEMS
--                                             BATCH_REQUIRED to table ITEM_DESCR
--            May 22, 2003 Stan K.
--                              Corrected column DATA VARCHAR2(1) to DATA LONG RAW,
--                              in ITEM_IMAGES and TEST_IMAGES tables.
--                              COMMENT for columns added on May 15.
--            May 23, 2003 Stan K
--                              Commented out TR_FILENAME column in TEST_RAWDATA table.
--                              Replaced LONG RAW by BLOB, in 3 tables.
--                              Commented out SUBSYST column in all tables.
--            May 28, 2003 Stan K
--                              Merged TSTDETIVS and TSTDETCVS tables into TSTDETKEY table.
--            Jun  3, 2003 Stan K
--                              Added Atlas tables LAST_MOD and THIS_LOCN, although do not
--                              know what they are for ...
--                              Cleanup - removed commented out statements.
--            Jul 18, 2003 Stan K
--                              Added TSTSENLEAK table.
--            Jul 21, 2003 Stan K
--                              Added TSTSENDIAG table.
--                              Added TSTSENMECH table.
--                              Added TSTSENIRRAD table.
--            Jul 23, 2003 Stan K
--                              Added columns VISUAL_INSP, IS_STRUCTEST to TSTDETMFRS table.
--                              Added columns R_BIAS_AVE, R_BIAS_RMS to TSTSENDIAG table.
--            Jul 29, 2003 Stan K
--                              Changed LOCN_NAME VARCHAR2(50) to LOCN_NAME VARCHAR2(60)
--            Aug  5, 2003 Stan K
--                              For TSTDETKEY table changed all I_LEAK_xxx NUMBER(10,2)
--                              to                              I_LEAK_xxx NUMBER(12,10)
--            Aug 21, 2003 Stan K
--                              For TSTSENDIAG table changed 12 columns storing
--                              'Strip Test' results from NUMBER(10,2) to NUMBER(10,6);
--                              also allowed NULLs for them.
--                              
--
--
PROMPT Creating Table 'MAIL_LISTS'
DROP TABLE MAIL_LISTS
/
CREATE TABLE MAIL_LISTS
 (NAME VARCHAR2(64) NOT NULL
 )
/

PROMPT Creating Table 'TEST_CMNTS'
DROP TABLE TEST_CMNTS
/
CREATE TABLE TEST_CMNTS
 (CMNT_NO NUMBER(11,0) NOT NULL
 ,CMNT_TEXT VARCHAR2(400) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,TEST_NO NUMBER(11,0) NOT NULL
 )
/

COMMENT ON TABLE TEST_CMNTS IS 'Optional Comments on Tests';

COMMENT ON COLUMN TEST_CMNTS.CMNT_NO IS 'Comment no. for this test';
COMMENT ON COLUMN TEST_CMNTS.CMNT_TEXT IS 'Text of comment';
COMMENT ON COLUMN TEST_CMNTS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TEST_CMNTS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN TEST_CMNTS.TEST_NO IS 'ID no. of test';

PROMPT Creating Table 'LOCNS'
DROP TABLE LOCNS
/
CREATE TABLE LOCNS
 (LOCN_NAME VARCHAR2(60) NOT NULL
 ,LOCN NUMBER(3) NOT NULL
 ,USERNAME VARCHAR2(30) NOT NULL
 ,ADDRESS VARCHAR2(240)
 ,URL VARCHAR2(200)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 )
/

COMMENT ON TABLE LOCNS IS 'Institutes';

COMMENT ON COLUMN LOCNS.LOCN_NAME IS 'Institute name';
COMMENT ON COLUMN LOCNS.LOCN IS 'Location code';
COMMENT ON COLUMN LOCNS.USERNAME IS 'Login username of institute';
COMMENT ON COLUMN LOCNS.ADDRESS IS 'Postal address';
COMMENT ON COLUMN LOCNS.URL IS 'WWW link for more info';
COMMENT ON COLUMN LOCNS.LAST_MOD IS 'Date record last updated'; 

PROMPT Creating Table 'ASSM_DESCR'
DROP TABLE ASSM_DESCR
/
CREATE TABLE ASSM_DESCR
 (POSN NUMBER(3,0) NOT NULL
 ,POSN_DESCR VARCHAR2(80) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,ASSM_CTYPE VARCHAR2(20) NOT NULL
 ,CTYPE VARCHAR2(20) NOT NULL
 )
/

COMMENT ON TABLE ASSM_DESCR IS 'Assembly Descriptions';

COMMENT ON COLUMN ASSM_DESCR.POSN IS 'Position of component on assembly';
COMMENT ON COLUMN ASSM_DESCR.POSN_DESCR IS 'Description of position on assembly';
COMMENT ON COLUMN ASSM_DESCR.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN ASSM_DESCR.ASSM_CTYPE IS 'Item type of assembly';
COMMENT ON COLUMN ASSM_DESCR.CTYPE IS 'Item type of component';

PROMPT Creating Table 'TSTDETMFRS'
DROP TABLE TSTDETMFRS
/
CREATE TABLE TSTDETMFRS
 (TEST_NO NUMBER(11,0) NOT NULL
 ,BATCH VARCHAR2(10)
 ,THICKNESS NUMBER(11,0) 
 ,VISUAL_INSP VARCHAR2(4) NOT NULL 
 ,IS_STRUCTEST VARCHAR2(3) DEFAULT 'YES' NOT NULL
 ,V_DEP NUMBER(10,2)
 ,I_LEAK_100 NUMBER(10,2)
 ,I_LEAK_300 NUMBER(10,2)
 ,I_LEAK_500 NUMBER(10,2)
 ,I_LEAK_700 NUMBER(10,2)
 ,R_BIAS_AVER NUMBER(10,2)
 ,R_BIAS_UPPER NUMBER(10,2)
 ,R_BIAS_LOWER NUMBER(10,2)
 ,R_ALU_TRACE NUMBER(10,2)
 ,R_IMPLANT NUMBER(10,2)
 ,C_COUPLING NUMBER(10,2)
 ,VC_BREAK NUMBER(10,2)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,MISS_INFO VARCHAR2(3) DEFAULT 'NO' NOT NULL
 )
/

COMMENT ON TABLE TSTDETMFRS IS 'Manufacturer''s, or Vendor''s Test Information';

COMMENT ON COLUMN TSTDETMFRS.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TSTDETMFRS.BATCH IS 'Wafer batch, or lot information';
COMMENT ON COLUMN TSTDETMFRS.THICKNESS IS 'Wafer thickness (microns)';
COMMENT ON COLUMN TSTDETMFRS.VISUAL_INSP IS 'Result of visual inspection';
COMMENT ON COLUMN TSTDETMFRS.IS_STRUCTEST IS 'Is structure test available?';
COMMENT ON COLUMN TSTDETMFRS.V_DEP IS 'Depletion voltage (V)';
COMMENT ON COLUMN TSTDETMFRS.I_LEAK_100 IS 'Leakage current at 100V (microAmps)';
COMMENT ON COLUMN TSTDETMFRS.I_LEAK_300 IS 'Leakage current at 300V (microAmps)';
COMMENT ON COLUMN TSTDETMFRS.I_LEAK_500 IS 'Leakage current at 500V (microAmps)';
COMMENT ON COLUMN TSTDETMFRS.I_LEAK_700 IS 'Leakage current at 700V (microAmps)';
COMMENT ON COLUMN TSTDETMFRS.R_BIAS_AVER IS 'Polysilicon bias resistance average (M Ohm)';
COMMENT ON COLUMN TSTDETMFRS.R_BIAS_UPPER IS 'Polysilicon bias resistance upper bound (M Ohm)';
COMMENT ON COLUMN TSTDETMFRS.R_BIAS_LOWER IS 'Polysilicon bias resistance lower bound (M Ohm)';
COMMENT ON COLUMN TSTDETMFRS.R_ALU_TRACE IS 'Aluminium trace resistance (M Ohm)';
COMMENT ON COLUMN TSTDETMFRS.R_IMPLANT IS 'Implant resistance (M Ohm)';
COMMENT ON COLUMN TSTDETMFRS.C_COUPLING IS 'Average coupling capacitor (pF)'; 
COMMENT ON COLUMN TSTDETMFRS.VC_BREAK IS 'Coupling capacitor breakdown (V)';
COMMENT ON COLUMN TSTDETMFRS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TSTDETMFRS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN TSTDETMFRS.MISS_INFO IS 'Flag for potential missing vendor information';

PROMPT Creating Table 'TSTDETKEY'
DROP TABLE TSTDETKEY
/
CREATE TABLE TSTDETKEY
 (TEST_NO NUMBER(11,0) NOT NULL
 ,TEMPERATURE NUMBER(10,2) NOT NULL
 ,HUMIDITY NUMBER(10,2) NOT NULL
 ,V_DEP NUMBER(10,2) NOT NULL
 ,I_LEAK_100 NUMBER(12,10) NOT NULL
 ,I_LEAK_200 NUMBER(12,10) NOT NULL
 ,I_LEAK_300 NUMBER(12,10) NOT NULL
 ,I_LEAK_400 NUMBER(12,10) NOT NULL
 ,I_LEAK_500 NUMBER(12,10) NOT NULL
 ,I_LEAK_600 NUMBER(12,10)
 ,I_LEAK_700 NUMBER(12,10)
 ,V_BREAK NUMBER(10,2) NOT NULL
 ,MARK NUMBER(2,0) DEFAULT 1 NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
)
/

COMMENT ON TABLE TSTDETKEY IS 'Sensor Key Test: I-V and C-V curves and visual inspection';

COMMENT ON COLUMN TSTDETKEY.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TSTDETKEY.TEMPERATURE IS 'Temperature for test (deg C)';
COMMENT ON COLUMN TSTDETKEY.HUMIDITY IS 'Relative humidity for test (%)';
COMMENT ON COLUMN TSTDETKEY.V_DEP IS 'Depletion voltage (V)';
COMMENT ON COLUMN TSTDETKEY.I_LEAK_100 IS 'Leakage current at 100V (microAmps)';
COMMENT ON COLUMN TSTDETKEY.I_LEAK_200 IS 'Leakage current at 200V (microAmps)';
COMMENT ON COLUMN TSTDETKEY.I_LEAK_300 IS 'Leakage current at 300V (microAmps)';
COMMENT ON COLUMN TSTDETKEY.I_LEAK_400 IS 'Leakage current at 400V (microAmps)';
COMMENT ON COLUMN TSTDETKEY.I_LEAK_500 IS 'Leakage current at 500V (microAmps)';
COMMENT ON COLUMN TSTDETKEY.I_LEAK_600 IS 'Leakage current at 600V (microAmps)';
COMMENT ON COLUMN TSTDETKEY.I_LEAK_700 IS 'Leakage current at 700V (microAmps)';
COMMENT ON COLUMN TSTDETKEY.V_BREAK IS 'Breakdown voltage (V)';
COMMENT ON COLUMN TSTDETKEY.MARK IS 'Number mark assigned to, and indicating other tests';
COMMENT ON COLUMN TSTDETKEY.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TSTDETKEY.OWNER IS 'Owner institute of the record';


PROMPT Creating Table 'TSTSENLEAK'
DROP TABLE TSTSENLEAK
/
CREATE TABLE TSTSENLEAK
 (TEST_NO NUMBER(11,0) NOT NULL
 ,V_BIAS NUMBER(10,2) NOT NULL
 ,DURATION NUMBER(10,2) NOT NULL
 ,I_MIN NUMBER(10,2) NOT NULL
 ,I_MAX NUMBER(10,2) NOT NULL
 ,TEMPERATURE NUMBER(10,2) NOT NULL
 ,HUMIDITY NUMBER(10,2) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
)
/

COMMENT ON TABLE TSTSENLEAK IS 'Sensor Leakage Current Stability Test';

COMMENT ON COLUMN TSTSENLEAK.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TSTSENLEAK.V_BIAS IS 'Applied bias voltage setting (Volts)';
COMMENT ON COLUMN TSTSENLEAK.DURATION IS 'Duration of test (Hours)'; 
COMMENT ON COLUMN TSTSENLEAK.I_MIN IS 'Minimum current (nanoAmps)';
COMMENT ON COLUMN TSTSENLEAK.I_MAX IS 'Maximum current (nanoAmps)'; 
COMMENT ON COLUMN TSTSENLEAK.TEMPERATURE IS 'Temperature for test (degree Celsius)';
COMMENT ON COLUMN TSTSENLEAK.HUMIDITY IS 'Average humidity for test (%)';
COMMENT ON COLUMN TSTSENLEAK.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TSTSENLEAK.OWNER IS 'Owner institute of the record';


PROMPT Creating Table 'TSTSENDIAG'
DROP TABLE TSTSENDIAG
/
CREATE TABLE TSTSENDIAG
 (TEST_NO NUMBER(11,0) NOT NULL
 ,TEMPERATURE NUMBER(10,2) NOT NULL
 ,HUMIDITY NUMBER(10,2) NOT NULL
 ,V_FULL_DEP NUMBER(10,2) NOT NULL
 ,I_LEAK_FDV NUMBER(10,2) NOT NULL
 ,V_BREAK NUMBER(10,2)
 ,I_DIEL_AVE NUMBER(10,6)
 ,I_DIEL_RMS NUMBER(10,6)
 ,C_AC_AVE NUMBER(10,6)
 ,C_AC_RMS NUMBER(10,6)
 ,I_STRIP_AVE NUMBER(10,6)
 ,I_STRIP_RMS NUMBER(10,6)
 ,R_BIAS_AVE NUMBER(10,6)
 ,R_BIAS_RMS NUMBER(10,6)
 ,C_INT_AVE NUMBER(10,6)
 ,C_INT_RMS NUMBER(10,6)
 ,COND_INT_AVE NUMBER(10,6)
 ,COND_INT_RMS NUMBER(10,6)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
)
/

COMMENT ON TABLE TSTSENDIAG IS 'Sensor Diagnostic Test (if TSTDETKEY.MARK=2)';

COMMENT ON COLUMN TSTSENDIAG.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TSTSENDIAG.TEMPERATURE IS 'Temperature for test (degree Celsius)';
COMMENT ON COLUMN TSTSENDIAG.HUMIDITY IS 'Average humidity for test (%)';
COMMENT ON COLUMN TSTSENDIAG.V_FULL_DEP IS 'Full depletion voltage - FDV (V)';
COMMENT ON COLUMN TSTSENDIAG.I_LEAK_FDV IS 'Leakage current at FDV + 20% (nanoAmps)';
COMMENT ON COLUMN TSTSENDIAG.V_BREAK IS 'Breakdown voltage as measured or 9999. (V)';
COMMENT ON COLUMN TSTSENDIAG.I_DIEL_AVE IS 'Average Idiel per strip (nanoAmps)';
COMMENT ON COLUMN TSTSENDIAG.I_DIEL_RMS IS 'RMS of Idiel strip measurement (nanoAmps)';
COMMENT ON COLUMN TSTSENDIAG.C_AC_AVE IS 'Average CaC from strip test (pF)';
COMMENT ON COLUMN TSTSENDIAG.C_AC_RMS IS 'RMS of CaC strip measurement (pF)';
COMMENT ON COLUMN TSTSENDIAG.I_STRIP_AVE IS 'Average strip current (nanoAmps)';
COMMENT ON COLUMN TSTSENDIAG.I_STRIP_RMS IS 'RMS of strip current (nanoAmps)';
COMMENT ON COLUMN TSTSENDIAG.R_BIAS_AVE IS 'Polysilicon bias resistance average (M Ohm)';
COMMENT ON COLUMN TSTSENDIAG.R_BIAS_RMS IS 'Polysilicon bias resistance RMS (M Ohm)';
COMMENT ON COLUMN TSTSENDIAG.C_INT_AVE IS 'Average Cint (pF)';
COMMENT ON COLUMN TSTSENDIAG.C_INT_RMS IS 'RMS of Cint (pF)';
COMMENT ON COLUMN TSTSENDIAG.COND_INT_AVE IS 'Average CondInt (pA/V)';
COMMENT ON COLUMN TSTSENDIAG.COND_INT_RMS IS 'RMS of CondInt (pA/V)';
COMMENT ON COLUMN TSTSENDIAG.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TSTSENDIAG.OWNER IS 'Owner institute of the record';

PROMPT Creating Table 'TSTSENMECH'
DROP TABLE TSTSENMECH
/
CREATE TABLE TSTSENMECH
 (TEST_NO NUMBER(11,0) NOT NULL
 ,THICKNESS NUMBER(10,2) NOT NULL
 ,FLATNESS NUMBER(10,2) NOT NULL
 ,CUT_TOL_SIDE NUMBER(10,2) NOT NULL
 ,CUT_TOL_END NUMBER(10,2) NOT NULL
 ,MECH_GRADE VARCHAR2(10) NOT NULL
 ,VISUAL_INSP VARCHAR2(4) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 )
/

COMMENT ON TABLE TSTSENMECH IS 'Sensor Mechanical Test (if TSTDETKEY.MARK=3)';

COMMENT ON COLUMN TSTSENMECH.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TSTSENMECH.THICKNESS IS 'Average Thickness (microns)';
COMMENT ON COLUMN TSTSENMECH.FLATNESS IS 'Flatness from OGP (microns)';
COMMENT ON COLUMN TSTSENMECH.CUT_TOL_SIDE IS 'Sides maximum-minimum deviation from ideal (microns)';
COMMENT ON COLUMN TSTSENMECH.CUT_TOL_END IS 'Ends maximum-minimum deviation from ideal (microns)';
COMMENT ON COLUMN TSTSENMECH.MECH_GRADE IS 'Final mechanical sensor grade';
COMMENT ON COLUMN TSTSENMECH.VISUAL_INSP IS 'Result of visual inspection';
COMMENT ON COLUMN TSTSENMECH.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TSTSENMECH.OWNER IS 'Owner institute of the record';


PROMPT Creating Table 'TSTSENIRRAD'
DROP TABLE TSTSENIRRAD
/
CREATE TABLE TSTSENIRRAD
 (TEST_NO NUMBER(11,0) NOT NULL
 ,ANNEAL_TYPE VARCHAR2(20) NOT NULL
 ,FLUENCE NUMBER(10,2) NOT NULL
 ,V_FULL_DEP NUMBER(10,2) NOT NULL
 ,I_LEAK_FDV NUMBER(10,2) NOT NULL
 ,TEMPERATURE NUMBER(10,2) NOT NULL
 ,V_BREAK NUMBER(10,2)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
)
/

COMMENT ON TABLE TSTSENIRRAD IS 'Sensor Irradiation Test (if TSTDETKEY.MARK=4)';

COMMENT ON COLUMN TSTSENIRRAD.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TSTSENIRRAD.ANNEAL_TYPE IS 'Annealing procedure';
COMMENT ON COLUMN TSTSENIRRAD.FLUENCE IS 'Fluence of irradiation (in 1.E13 of 10 MeV protons/cm^2)';
COMMENT ON COLUMN TSTSENIRRAD.V_FULL_DEP IS 'Full depletion voltage after irradiaton (V)';
COMMENT ON COLUMN TSTSENIRRAD.I_LEAK_FDV IS 'Leakage current at FDV + 50V after irradiaton (nanoAmps)';
COMMENT ON COLUMN TSTSENIRRAD.TEMPERATURE IS 'Temperature after irradiation (degree Celsius)'; 
COMMENT ON COLUMN TSTSENIRRAD.V_BREAK IS 'Breakdown voltage after irradiation, as measured or 9999. (V)';
COMMENT ON COLUMN TSTSENIRRAD.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TSTSENIRRAD.OWNER IS 'Owner institute of the record';

PROMPT Creating Table 'PERSONS'
DROP TABLE PERSONS
/
CREATE TABLE PERSONS
 (INITLS VARCHAR2(4) NOT NULL
 ,SURNAME VARCHAR2(16) NOT NULL
 ,FSTNAME VARCHAR2(16)
 ,EMAIL VARCHAR2(40)
 ,PHONE VARCHAR2(20)
 ,FAX VARCHAR2(20)
 ,URL VARCHAR2(200)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,LOCN_NAME VARCHAR2(60) NOT NULL
 )
/

COMMENT ON TABLE PERSONS IS 'Database Users';

COMMENT ON COLUMN PERSONS.INITLS IS 'Initials of database user';
COMMENT ON COLUMN PERSONS.SURNAME IS 'Person''s surname';
COMMENT ON COLUMN PERSONS.FSTNAME IS 'Person''s first name';
COMMENT ON COLUMN PERSONS.EMAIL IS 'E-mail address';
COMMENT ON COLUMN PERSONS.PHONE IS 'Telephone no.';
COMMENT ON COLUMN PERSONS.FAX IS 'Fax no.';
COMMENT ON COLUMN PERSONS.URL IS 'WWW link for more info';
COMMENT ON COLUMN PERSONS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN PERSONS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN PERSONS.LOCN_NAME IS 'Institute name';

PROMPT Creating Table 'ASSM_ITEMS'
DROP TABLE ASSM_ITEMS
/
CREATE TABLE ASSM_ITEMS
 (OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,ASSM_CTYPE VARCHAR2(20) NOT NULL
 ,CTYPE VARCHAR2(20) NOT NULL
 ,POSN NUMBER(3,0) NOT NULL
 ,ASSM_GLO_ID VARCHAR2(35) NOT NULL
 ,GLO_ID VARCHAR2(35) NOT NULL
 ,ASSM_DATE DATE NOT NULL
 )
/

COMMENT ON TABLE ASSM_ITEMS IS 'Map Component Items onto Assembly';

COMMENT ON COLUMN ASSM_ITEMS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN ASSM_ITEMS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN ASSM_ITEMS.ASSM_CTYPE IS 'Item type of assembly';
COMMENT ON COLUMN ASSM_ITEMS.CTYPE IS 'Item type of component';
COMMENT ON COLUMN ASSM_ITEMS.POSN IS 'Position of component on assembly';
COMMENT ON COLUMN ASSM_ITEMS.ASSM_GLO_ID IS 'Global id of assembly';
COMMENT ON COLUMN ASSM_ITEMS.GLO_ID IS 'Global id of component';
COMMENT ON COLUMN ASSM_ITEMS.ASSM_DATE IS 'Date item was assembled';

PROMPT Creating Table 'SHIP_ITEMS'
DROP TABLE SHIP_ITEMS
/
CREATE TABLE SHIP_ITEMS
 (RECVD VARCHAR2(3) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,SHIP_NO NUMBER(11,0) NOT NULL
 ,GLO_ID VARCHAR2(35) NOT NULL
 )
/

COMMENT ON TABLE SHIP_ITEMS IS 'Map Items onto Shipment';

COMMENT ON COLUMN SHIP_ITEMS.RECVD IS 'Item received at destination?';
COMMENT ON COLUMN SHIP_ITEMS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN SHIP_ITEMS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN SHIP_ITEMS.SHIP_NO IS 'ID no. of shipment';
COMMENT ON COLUMN SHIP_ITEMS.GLO_ID IS 'D0 Global id';

PROMPT Creating Table 'ITEM_DESCR'
DROP TABLE ITEM_DESCR
/
CREATE TABLE ITEM_DESCR
 (CTYPE VARCHAR2(20) NOT NULL
 ,NO_COMPS NUMBER(3,0) DEFAULT 0 NOT NULL
 ,IS_UNIQUE VARCHAR2(3) DEFAULT 'YES' NOT NULL
 ,DESCRIPTION VARCHAR2(80) NOT NULL
 ,URL VARCHAR2(200)
 ,DOC_ID VARCHAR2(40)
 ,DOC_NAME VARCHAR2(40)
 ,DOC_VER VARCHAR2(40)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,CATEGORY VARCHAR2(20) NOT NULL
 ,BATCH_REQUIRED VARCHAR2(3) NOT NULL
 )
/

COMMENT ON TABLE ITEM_DESCR IS 'Item Type Descriptions';

COMMENT ON COLUMN ITEM_DESCR.CTYPE IS 'Item type';
COMMENT ON COLUMN ITEM_DESCR.NO_COMPS IS 'Number of components';
COMMENT ON COLUMN ITEM_DESCR.IS_UNIQUE IS 'Used once only?';
COMMENT ON COLUMN ITEM_DESCR.DESCRIPTION IS 'Description';
COMMENT ON COLUMN ITEM_DESCR.URL IS 'WWW link for more info';
COMMENT ON COLUMN ITEM_DESCR.DOC_ID IS '';
COMMENT ON COLUMN ITEM_DESCR.DOC_NAME IS '';
COMMENT ON COLUMN ITEM_DESCR.DOC_VER IS '';
COMMENT ON COLUMN ITEM_DESCR.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN ITEM_DESCR.CATEGORY IS 'Generic or Si Detector';
COMMENT ON COLUMN ITEM_DESCR.BATCH_REQUIRED IS 'Batch info for item required?';

PROMPT Creating Table 'EVENT_DESCR'
DROP TABLE EVENT_DESCR
/
CREATE TABLE EVENT_DESCR
 (EVENT_NAME VARCHAR2(20) NOT NULL
 ,DESCR VARCHAR2(200) NOT NULL
 ,URL VARCHAR2(100)
 ,LAST_MOD DATE DEFAULT SYSDATE NOT NULL
 )
/

PROMPT Creating Table 'WEB_LINKS'
DROP TABLE WEB_LINKS
/
CREATE TABLE WEB_LINKS
 (WEB_LINK_ID NUMBER(11,0) NOT NULL
 ,WEB_LINK_DESC VARCHAR2(100) NOT NULL
 ,URL VARCHAR2(200) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,TEST_NO NUMBER(11,0) NOT NULL
 )
/

COMMENT ON TABLE WEB_LINKS IS 'Links to web pages of test results';

COMMENT ON COLUMN WEB_LINKS.WEB_LINK_ID IS 'Primary Key no. for link';
COMMENT ON COLUMN WEB_LINKS.WEB_LINK_DESC IS 'Description of results';
COMMENT ON COLUMN WEB_LINKS.URL IS 'WWW link for more info';
COMMENT ON COLUMN WEB_LINKS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN WEB_LINKS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN WEB_LINKS.TEST_NO IS 'ID no. of test';

PROMPT Creating Table 'TEST_IMAGES'
DROP TABLE TEST_IMAGES
/
CREATE TABLE TEST_IMAGES
 (NAME VARCHAR2(128) NOT NULL
 ,TITLE VARCHAR2(128)
 ,DATA BLOB DEFAULT empty_blob() NOT NULL
 ,TEST_NO NUMBER(11,0) NOT NULL
 ,URL VARCHAR2(200)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 )
 LOB (DATA) STORE AS (TABLESPACE SDB_TIMG_BLOBS)
/

COMMENT ON TABLE TEST_IMAGES IS 'Optional GIF or JPEG Images';

COMMENT ON COLUMN TEST_IMAGES.NAME IS 'Name of this image';
COMMENT ON COLUMN TEST_IMAGES.TITLE IS 'Title to be displayed with image';
COMMENT ON COLUMN TEST_IMAGES.DATA IS 'GIF or JPEG picture (image file) for the test (eg illustration of visual inspection)';
COMMENT ON COLUMN TEST_IMAGES.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TEST_IMAGES.URL IS 'WWW link for more info';
COMMENT ON COLUMN TEST_IMAGES.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TEST_IMAGES.OWNER IS 'Owner institute of the record';


PROMPT Creating Table 'MAILS'
DROP TABLE MAILS
/
CREATE TABLE MAILS
 (ADDRESS VARCHAR2(240) NOT NULL
 ,LOCN_NAME VARCHAR2(60) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,MAIL_LIST_NAME VARCHAR2(64) NOT NULL
 )
/

PROMPT Creating Table 'ITEM_CMNTS'
DROP TABLE ITEM_CMNTS
/
CREATE TABLE ITEM_CMNTS
 (CMNT_NO NUMBER(11,0) NOT NULL
 ,CMNT_DATE DATE NOT NULL
 ,CMNT_TEXT VARCHAR2(400)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,INITLS VARCHAR2(4) NOT NULL
 ,GLO_ID VARCHAR2(35) NOT NULL
 )
/

COMMENT ON TABLE ITEM_CMNTS IS 'Optional Comments about Items';

COMMENT ON COLUMN ITEM_CMNTS.CMNT_NO IS 'Comment no. for this item';
COMMENT ON COLUMN ITEM_CMNTS.CMNT_DATE IS 'Date comment entered';
COMMENT ON COLUMN ITEM_CMNTS.CMNT_TEXT IS 'Text of comment';
COMMENT ON COLUMN ITEM_CMNTS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN ITEM_CMNTS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN ITEM_CMNTS.INITLS IS 'Initials of user making entry';
COMMENT ON COLUMN ITEM_CMNTS.GLO_ID IS 'Global id of component';


PROMPT Creating Table 'TEST_LIST'
DROP TABLE TEST_LIST
/
CREATE TABLE TEST_LIST
 (REQUIRED VARCHAR2(3) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,CTYPE VARCHAR2(20) NOT NULL
 ,TEST_NAME VARCHAR2(20) NOT NULL
 )
/

COMMENT ON TABLE TEST_LIST IS 'Links Tests to Item Types';

COMMENT ON COLUMN TEST_LIST.REQUIRED IS 'Must these items pass?';
COMMENT ON COLUMN TEST_LIST.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TEST_LIST.CTYPE IS 'Tested item type';
COMMENT ON COLUMN TEST_LIST.TEST_NAME IS 'Name of test';

PROMPT Creating Table 'TEST_LIMITS'
DROP TABLE TEST_LIMITS
/
CREATE TABLE TEST_LIMITS
 (PARM_NAME VARCHAR2(20) NOT NULL
 ,MFR VARCHAR2(20) NOT NULL
 ,NOMINAL_VAL NUMBER(16,4)
 ,PARM_DESCR VARCHAR2(40) NOT NULL
 ,LOWER_REJECT NUMBER(16,4) NOT NULL
 ,LOWER_WARN NUMBER(16,4)
 ,CTYPE VARCHAR2(20) NOT NULL
 ,TEST_NAME VARCHAR2(20) NOT NULL
 ,UPPER_REJECT NUMBER(16,4) NOT NULL
 ,UPPER_WARN NUMBER(16,4)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 )
/

COMMENT ON TABLE TEST_LIMITS IS 'Warning and Reject Limits for Test Parameters';

COMMENT ON COLUMN TEST_LIMITS.PARM_NAME IS 'Name of test parameter';
COMMENT ON COLUMN TEST_LIMITS.MFR IS 'Manufacturer';
COMMENT ON COLUMN TEST_LIMITS.NOMINAL_VAL IS 'Nominal value';
COMMENT ON COLUMN TEST_LIMITS.PARM_DESCR IS 'Description of parameter with units';
COMMENT ON COLUMN TEST_LIMITS.LOWER_REJECT IS 'Lower reject limit';
COMMENT ON COLUMN TEST_LIMITS.LOWER_WARN IS 'Lower warning limit';
COMMENT ON COLUMN TEST_LIMITS.CTYPE IS 'Item type to be tested'; 
COMMENT ON COLUMN TEST_LIMITS.TEST_NAME IS 'Name of test';
COMMENT ON COLUMN TEST_LIMITS.UPPER_REJECT IS 'Upper reject limit';
COMMENT ON COLUMN TEST_LIMITS.UPPER_WARN IS 'Upper warning limit';
COMMENT ON COLUMN TEST_LIMITS.LAST_MOD IS 'Date record last updated';

PROMPT Creating Table 'DISASSMLOGS'
DROP TABLE DISASSMLOGS
/
CREATE TABLE DISASSMLOGS
 (DISASSM_DATE DATE NOT NULL
 ,ASSM_DATE DATE NOT NULL
 ,POSN NUMBER(3) NOT NULL
 ,LOCN_NAME VARCHAR2(60) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,GLO_ID VARCHAR2(35) NOT NULL
 ,ASSM_GLO_ID VARCHAR2(35) NOT NULL
 )
/

COMMENT ON TABLE DISASSMLOGS IS 'Assembly / Disassembly History';

COMMENT ON COLUMN DISASSMLOGS.DISASSM_DATE IS 'Date item was disassembled';
COMMENT ON COLUMN DISASSMLOGS.ASSM_DATE IS 'Date item was assembled';
COMMENT ON COLUMN DISASSMLOGS.POSN IS 'Position of component on assembly';
COMMENT ON COLUMN DISASSMLOGS.LOCN_NAME IS 'Institute where item was disassembled';
COMMENT ON COLUMN DISASSMLOGS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN DISASSMLOGS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN DISASSMLOGS.GLO_ID IS 'Global id of component';
COMMENT ON COLUMN DISASSMLOGS.ASSM_GLO_ID IS 'Global id of assembly';

PROMPT Creating Table 'DEFECTS'
DROP TABLE DEFECTS
/
CREATE TABLE DEFECTS
 (CHAN_1ST NUMBER(11) NOT NULL
 ,CHAN_LAST NUMBER(11) NOT NULL
 ,URL VARCHAR2(200)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,DEFECT_NAME VARCHAR2(20) NOT NULL
 ,TEST_NO NUMBER(11,0) NOT NULL
 )
/

COMMENT ON TABLE DEFECTS IS 'Channels Tested with Defects';

COMMENT ON COLUMN DEFECTS.CHAN_1ST IS '1st channel with the defect';
COMMENT ON COLUMN DEFECTS.CHAN_LAST IS 'Last channel with the defect';
COMMENT ON COLUMN DEFECTS.URL IS 'WWW link for more info';
COMMENT ON COLUMN DEFECTS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN DEFECTS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN DEFECTS.DEFECT_NAME IS 'Name of the defect';
COMMENT ON COLUMN DEFECTS.TEST_NO IS 'ID no. of test';

PROMPT Creating Table 'MFRS'
DROP TABLE MFRS
/
CREATE TABLE MFRS
 (MFR VARCHAR2(20) NOT NULL
 ,ADDRESS VARCHAR2(240)
 ,URL VARCHAR2(200)
 ,MFR_NO NUMBER(3,0)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 )
/

COMMENT ON TABLE MFRS IS 'Manufacturers';

COMMENT ON COLUMN MFRS.MFR IS 'Manufacturer''s name';
COMMENT ON COLUMN MFRS.ADDRESS IS 'Postal address';
COMMENT ON COLUMN MFRS.URL IS 'WWW link for more info';
COMMENT ON COLUMN MFRS.MFR_NO IS 'Manufacturer number';
COMMENT ON COLUMN MFRS.LAST_MOD IS 'Date record last updated';

PROMPT Creating Table 'ITEM_IMAGES'
DROP TABLE ITEM_IMAGES
/
CREATE TABLE ITEM_IMAGES
 (NAME VARCHAR2(128) NOT NULL
 ,TITLE VARCHAR2(128)
 ,DATA BLOB DEFAULT empty_blob() NOT NULL
 ,URL VARCHAR2(200)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,GLO_ID VARCHAR2(35) NOT NULL
 )
 LOB (DATA) STORE AS (TABLESPACE SDB_ITEM_BLOBS)
/

COMMENT ON TABLE ITEM_IMAGES IS 'Optional GIF or JPEG Images';

COMMENT ON COLUMN ITEM_IMAGES.NAME IS 'Name of this image';
COMMENT ON COLUMN ITEM_IMAGES.TITLE IS 'Title to be displayed with image';
COMMENT ON COLUMN ITEM_IMAGES.DATA IS 'GIF or JPEG picture (image file) of the item';
COMMENT ON COLUMN ITEM_IMAGES.URL IS 'WWW link with more info';
COMMENT ON COLUMN ITEM_IMAGES.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN ITEM_IMAGES.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN ITEM_IMAGES.GLO_ID IS 'Global id of component';

PROMPT Creating Table 'TESTS'
DROP TABLE TESTS
/
CREATE TABLE TESTS
 (GLO_ID VARCHAR2(35) NOT NULL
 ,TEST_NAME VARCHAR2(20) NOT NULL
 ,TEST_NO NUMBER(11,0) NOT NULL
 ,RUN_NO VARCHAR2(80)
 ,TEST_DATE DATE DEFAULT sysdate NOT NULL
 ,PROBLEM VARCHAR2(3) NOT NULL
 ,PASS VARCHAR2(3) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,LOCN_NAME VARCHAR2(60) NOT NULL
 ,INITLS VARCHAR2(4) NOT NULL
 )
/

COMMENT ON TABLE TESTS IS 'General Details of Tests on Items';

COMMENT ON COLUMN TESTS.GLO_ID IS 'D0 Global id of item';
COMMENT ON COLUMN TESTS.TEST_NAME IS 'Name of test';
COMMENT ON COLUMN TESTS.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TESTS.RUN_NO IS 'Data taking run no.';
COMMENT ON COLUMN TESTS.TEST_DATE IS 'Date of test';
COMMENT ON COLUMN TESTS.PROBLEM IS 'Problems encountered?';
COMMENT ON COLUMN TESTS.PASS IS 'Passed unconditionally?';
COMMENT ON COLUMN TESTS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TESTS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN TESTS.LOCN_NAME IS 'Location of test';
COMMENT ON COLUMN TESTS.INITLS IS 'Initials of user';

PROMPT Creating Table 'SHIP'
DROP TABLE SHIP
/
CREATE TABLE SHIP
 (SHIP_NO NUMBER(11,0) NOT NULL
 ,CARRIER VARCHAR2(20) NOT NULL
 ,CARRIER_REF VARCHAR2(35)
 ,LOCN_NAME VARCHAR2(60) NOT NULL
 ,CONFIRM_DATE DATE
 ,DEST_INITLS VARCHAR2(4) NOT NULL
 ,GROSS_WT NUMBER(16,3)
 ,NO_PACKS NUMBER(3)
 ,SHIP_DATE DATE NOT NULL
 ,SHIP_REF VARCHAR2(35)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,DEST_LOCN_NAME VARCHAR2(60) NOT NULL
 ,INITLS VARCHAR2(4) NOT NULL
 )
/

COMMENT ON TABLE SHIP IS 'Details of Shipments';

COMMENT ON COLUMN SHIP.SHIP_NO IS 'ID no. of shipment';
COMMENT ON COLUMN SHIP.CARRIER IS 'Name of carrier';
COMMENT ON COLUMN SHIP.CARRIER_REF IS 'Ref. no. assigned by carrier';
COMMENT ON COLUMN SHIP.LOCN_NAME IS 'Name of shipping institute';
COMMENT ON COLUMN SHIP.CONFIRM_DATE IS 'Date shipment confirmed by sender';
COMMENT ON COLUMN SHIP.DEST_INITLS IS 'Initials of addressee';
COMMENT ON COLUMN SHIP.GROSS_WT IS 'Gross weight of final package (Kg)';
COMMENT ON COLUMN SHIP.NO_PACKS IS 'No. of packages handled by carrier';
COMMENT ON COLUMN SHIP.SHIP_DATE IS 'Date of dispatch';
COMMENT ON COLUMN SHIP.SHIP_REF IS 'Ref. no. assigned by shipping institute';
COMMENT ON COLUMN SHIP.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN SHIP.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN SHIP.DEST_LOCN_NAME IS 'Name of destination institute';
COMMENT ON COLUMN SHIP.INITLS IS 'Initials of person sending shipment';

PROMPT Creating Table 'ITEM_CATEGORIES'
DROP TABLE ITEM_CATEGORIES
/
CREATE TABLE ITEM_CATEGORIES
 (CATEGORY VARCHAR2(20) NOT NULL
 ,DESCRIPTION VARCHAR2(80) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 )
/

COMMENT ON TABLE ITEM_CATEGORIES IS 'Item Type Categories';

COMMENT ON COLUMN ITEM_CATEGORIES.CATEGORY IS 'Item type category';
COMMENT ON COLUMN ITEM_CATEGORIES.DESCRIPTION IS 'Description';
COMMENT ON COLUMN ITEM_CATEGORIES.LAST_MOD IS 'Date record last updated';


PROMPT Creating Table 'ITEM_EVENTS'
DROP TABLE ITEM_EVENTS
/
CREATE TABLE ITEM_EVENTS
 (ID NUMBER NOT NULL
 ,ITEM_GLO_ID VARCHAR2(35) NOT NULL
 ,EVENT_DESC_EVENT_NAME VARCHAR2(20) NOT NULL
 ,EVENT_DATE DATE NOT NULL
 ,LOCN_LOCN_NAME VARCHAR2(60) NOT NULL
 ,DURATION NUMBER(10,2)
 ,HI_TEMP NUMBER(10,2)
 ,LOW_TEMP NUMBER(10,2)
 ,TEMP NUMBER(10,2)
 ,COMMENTS VARCHAR2(200)
 ,URL VARCHAR2(100)
 ,LAST_MOD DATE DEFAULT SYSDATE NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT USER NOT NULL
 )
/

PROMPT Creating Table 'ITEMS'
DROP TABLE ITEMS
/
CREATE TABLE ITEMS
 (CTYPE VARCHAR2(20) NOT NULL
 ,PROD_TYPE VARCHAR2(20) NOT NULL
 ,GLO_ID VARCHAR2(35) NOT NULL
 ,TRASHED VARCHAR2(3) DEFAULT 'NO' NOT NULL
 ,ENTRY_DATE DATE NOT NULL
 ,ASSEMBLED VARCHAR2(3) NOT NULL
 ,PASSED VARCHAR2(3) NOT NULL
 ,MFR_SER_NO VARCHAR2(35)
 ,RECEIPT_DATE DATE
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,INITLS VARCHAR2(4) NOT NULL
 ,LOCN_NAME VARCHAR2(60) NOT NULL
 ,MFR VARCHAR2(20) NOT NULL
 ,BATCH_NAME VARCHAR2(20)
 )
/

COMMENT ON TABLE ITEMS IS 'Details of Items';

COMMENT ON COLUMN ITEMS.CTYPE IS 'Item type';
COMMENT ON COLUMN ITEMS.PROD_TYPE IS 'Production type';
COMMENT ON COLUMN ITEMS.GLO_ID IS 'D0 Global id';
COMMENT ON COLUMN ITEMS.TRASHED IS 'Item has been discarded?';
COMMENT ON COLUMN ITEMS.ENTRY_DATE IS 'Date entered into database';
COMMENT ON COLUMN ITEMS.ASSEMBLED IS 'Mounted on assembly?';
COMMENT ON COLUMN ITEMS.PASSED IS 'Passed all necessary tests?';
COMMENT ON COLUMN ITEMS.MFR_SER_NO IS 'Manufacturer''s serial no.';
COMMENT ON COLUMN ITEMS.RECEIPT_DATE IS 'Date received at institute';
COMMENT ON COLUMN ITEMS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN ITEMS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN ITEMS.INITLS IS 'Initials of user making entry';
COMMENT ON COLUMN ITEMS.LOCN_NAME IS 'Current institute location';
COMMENT ON COLUMN ITEMS.MFR IS 'Manufacturer''s name';
COMMENT ON COLUMN ITEMS.BATCH_NAME IS 'Name of batch an item comes from';

PROMPT Creating Table 'MFR_ACCESS'
DROP TABLE MFR_ACCESS
/
CREATE TABLE MFR_ACCESS
 (LAST_MOD DATE DEFAULT sysdate
 ,OWNER VARCHAR2(30) DEFAULT user
 ,HOSTNAME VARCHAR2(240) NOT NULL
 ,IP_ADDRESS VARCHAR2(240) NOT NULL
 ,ID NUMBER(12) NOT NULL
 )
/

PROMPT Creating Table 'STOCKS'
DROP TABLE STOCKS
/
CREATE TABLE STOCKS
 (QTY NUMBER(11) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,LOCN_NAME VARCHAR2(60) NOT NULL
 ,GLO_ID VARCHAR2(35) NOT NULL
 )
/

COMMENT ON TABLE STOCKS IS 'Stock Levels of Batch Type Items';

COMMENT ON COLUMN STOCKS.QTY IS 'Quantity of items in stock at institute';
COMMENT ON COLUMN STOCKS.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN STOCKS.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN STOCKS.LOCN_NAME IS 'Institute location code';
COMMENT ON COLUMN STOCKS.GLO_ID IS 'Global id of batch of items';


PROMPT Creating Table 'DEFECT_DESCR'
DROP TABLE DEFECT_DESCR
/
CREATE TABLE DEFECT_DESCR
 (DEFECT_NAME VARCHAR2(20) NOT NULL
 ,DEFECT_DESCR VARCHAR2(100) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 )
/

COMMENT ON TABLE DEFECT_DESCR IS 'Descriptions of Named Defects';

COMMENT ON COLUMN DEFECT_DESCR.DEFECT_NAME IS 'Name of the defect';
COMMENT ON COLUMN DEFECT_DESCR.DEFECT_DESCR IS 'Description of the defect';
COMMENT ON COLUMN DEFECT_DESCR.LAST_MOD IS 'Date record last updated';

PROMPT Creating Table 'TEST_DESCR'
DROP TABLE TEST_DESCR
/
CREATE TABLE TEST_DESCR
 (TEST_NAME VARCHAR2(20) NOT NULL
 ,GENERIC VARCHAR2(3) DEFAULT 'NO' NOT NULL
 ,TEST_DESCR VARCHAR2(80)
 ,TABLE_NAME VARCHAR2(20)
 ,NO_CHANS NUMBER(11,0) NOT NULL
 ,URL VARCHAR2(200)
 ,DOC_ID VARCHAR2(40)
 ,DOC_NAME VARCHAR2(40)
 ,DOC_VER VARCHAR2(40)
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 )
/

COMMENT ON TABLE TEST_DESCR IS 'Description of Test';

COMMENT ON COLUMN TEST_DESCR.TEST_NAME IS 'Name of test';
COMMENT ON COLUMN TEST_DESCR.GENERIC IS '';
COMMENT ON COLUMN TEST_DESCR.TEST_DESCR IS 'Description of the test';
COMMENT ON COLUMN TEST_DESCR.TABLE_NAME IS 'Name of database table for results';
COMMENT ON COLUMN TEST_DESCR.NO_CHANS IS 'Max no. of channels to test';
COMMENT ON COLUMN TEST_DESCR.URL IS 'WWW link for more info or EDMS';
COMMENT ON COLUMN TEST_DESCR.DOC_ID IS 'EDMS document ID';
COMMENT ON COLUMN TEST_DESCR.DOC_NAME IS 'EDMS document NAME';
COMMENT ON COLUMN TEST_DESCR.DOC_VER IS 'EDMS document version';
COMMENT ON COLUMN TEST_DESCR.LAST_MOD IS 'Date record last updated';

PROMPT Creating Table 'TEST_RAWDATA'
DROP TABLE TEST_RAWDATA
/
CREATE TABLE TEST_RAWDATA
 (TEST_NO NUMBER(11,0) NOT NULL
 ,FILENAME VARCHAR2(100) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 ,OWNER VARCHAR2(30) DEFAULT user NOT NULL
 ,RAW_DATA BLOB DEFAULT empty_blob()
 )
 LOB (RAW_DATA) STORE AS (TABLESPACE SDB_TRAW_BLOBS)
/

COMMENT ON TABLE TEST_RAWDATA IS 'Raw Data Test';

COMMENT ON COLUMN TEST_RAWDATA.TEST_NO IS 'ID no. of test';
COMMENT ON COLUMN TEST_RAWDATA.FILENAME IS 'Name of original data file';
COMMENT ON COLUMN TEST_RAWDATA.LAST_MOD IS 'Date record last updated';
COMMENT ON COLUMN TEST_RAWDATA.OWNER IS 'Owner institute of the record';
COMMENT ON COLUMN TEST_RAWDATA.RAW_DATA IS 'Raw data file used to upload test ascii files limited to 32kB';


PROMPT Creating Table 'BATCH_LIST'
DROP TABLE BATCH_LIST
/
CREATE TABLE BATCH_LIST
 (BATCH_NAME VARCHAR2(20) NOT NULL
 ,BATCH_DESCR VARCHAR2(80)
 ,CTYPE VARCHAR2(20) NOT NULL
 ,LAST_MOD DATE DEFAULT sysdate NOT NULL
 )
/

COMMENT ON TABLE BATCH_LIST IS 'Stores batch names/wafer lot for certain items (eg sensors)';

COMMENT ON COLUMN BATCH_LIST.BATCH_NAME IS 'Name of batch';
COMMENT ON COLUMN BATCH_LIST.BATCH_DESCR IS 'Description of the batch';
COMMENT ON COLUMN BATCH_LIST.CTYPE IS 'Batched item type';
COMMENT ON COLUMN BATCH_LIST.LAST_MOD IS 'Date record last updated';

PROMPT Creating Table 'LAST_MOD'
DROP TABLE LAST_MOD
/
CREATE TABLE LAST_MOD
 (TABLE_NAME VARCHAR2(20)
 ,UPDATE_ORDER NUMBER(3)
 ,LAST_MOD DATE
 ,INSERT_LOCAL VARCHAR2(3)
 )
/

PROMPT Creating Table 'THIS_LOCN'
DROP TABLE THIS_LOCN
/
CREATE TABLE THIS_LOCN
 (ENTRY_NO NUMBER(3)
 ,LOCN NUMBER(3)
 ,LOCN_NAME VARCHAR2(60)
 ,VERSION_NO NUMBER(8,2) NOT NULL
 )
/

