-- h:\xconnect\TableGeneration\XCTableGen.tab
--

SET ECHO ON
SPOOL XCTableGen.tab.OUT
  
undefine tbl_owner
define tbl_owner = &1

-- PROMPT Creating Table '&&tbl_owner..XC_SITES'
CREATE TABLE &&tbl_owner..XC_EXT_SITES
 (
  STATION_ID VARCHAR2(240) NOT NULL  
 ,ENABLED VARCHAR2(1) NOT NULL
 ,UNIT_ID VARCHAR2(15)
 --,SITE_COMMENT VARCHAR2(240)
 ,INSTALLATION_DATE DATE
 ,AGENCY VARCHAR2(32)
 ,COUNTRY VARCHAR2(32)
 ,DISTRICT VARCHAR2(32)
 ,CITY VARCHAR2(32)
-- ,STATE VARCHAR2(20)
 ,COUNTY VARCHAR2(32)
-- ,BASIN VARCHAR2(32)
-- ,LATITUDE FLOAT(126)
-- ,LONGITUDE FLOAT(126)
-- ,ELEVATION FLOAT(126)
 ,COEFFICIENT1 FLOAT(126)             
 ,COEFFICIENT2 FLOAT(126)             
 ,COEFFICIENT3 FLOAT(126)             
 ,WEIGHTING_FACTOR FLOAT(126)
 ,SATELLITE_ID VARCHAR2(8)
 ,PRIMARY_CHANNEL NUMBER(6)
 ,RANDOM_CHANNEL NUMBER(6)
 ,REPORTING_TIME DATE
 ,REPORTING_INTERVAL FLOAT(126)
 ,LAST_UPDATE DATE
 ,TZONE_CODE VARCHAR2(3)
 ,GMT_OFFSET NUMBER(6)
-- ,SHEF_ID VARCHAR2(8)
 ,ALTERNATE_CHAR_ID_1 VARCHAR2(10)
 ,ALTERNATE_CHAR_ID_2 VARCHAR2(10)
 ,GPSTRING1 VARCHAR2(20)
 ,GPSTRING2 VARCHAR2(20)
 ,GPNUMBER1 FLOAT(126)
 ,GPNUMBER2 FLOAT(126)
 ,SETUP_FILE_NAME VARCHAR2(55)
 ,PICTURE_FILE_NAME VARCHAR2(55)
 )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.STATION_ID IS 'Sites ID';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.ENABLED IS 'Y or N';

-- SITE_COMMENT = HDB_SITE.site_common_name
-- COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.SITE_COMMENT IS 'General purpose string for comments on site status';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.INSTALLATION_DATE IS 'Date site was installed';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.AGENCY IS 'Agency/Entity that owns this site';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.COUNTRY IS 'Country where site is located';

-- STATE = HDB_SITE.state_id
--COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.STATE IS 'Political boundary';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.CITY IS 'City Name';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.DISTRICT IS 'District Name';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.COUNTY IS 'Political boundary name';

-- BASIN = HDB_SITE.basin_id
--COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.BASIN IS 'Name of river basin where site is located';

-- LATITUDE = HDB_SITE.lat
--COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.LATITUDE IS 'Latitude in degrees, minutes, seconds';

-- LONGITUDE = HDB_SITE.longi
--COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.LONGITUDE IS 'Longitude in degrees, minutes, seconds';

-- ELEVATION = HDB_SITE.elevation
--COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.ELEVATION IS 'Site elevation to 3 decimal precision';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.COEFFICIENT1 IS 'COEFFICIENT1';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.COEFFICIENT2 IS 'COEFFICIENT2';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.COEFFICIENT3 IS 'COEFFICIENT3';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.WEIGHTING_FACTOR IS 'Precip. station weighting for MAP calculation';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.SATELLITE_ID IS 'NESDIS Identifier';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.PRIMARY_CHANNEL IS 'Primary satellite channel';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.RANDOM_CHANNEL IS 'Secondary satellite channel';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.REPORTING_TIME IS 'Assigned time slot, GMT';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.REPORTING_INTERVAL IS 'Time between reports, hrs';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.LAST_UPDATE IS 'Time of last report from site';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.TZONE_CODE IS 'ASCII zone code, e.g. EST';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.GMT_OFFSET IS 'Time zone offset from GMT';

-- SHEF_ID = HDB_SITE.shef_code
--COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.SHEF_ID IS 'Std. Hydrologic Exchange Fmt. site ID';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.ALTERNATE_CHAR_ID_1 IS 'Space for alternate character ID';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.ALTERNATE_CHAR_ID_2 IS 'Space for alternate character ID';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.GPSTRING1 IS 'General purpose string 1';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.GPSTRING2 IS 'General purpose string 2';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.GPNUMBER1 IS 'General purpose number 1';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.GPNUMBER2 IS 'General purpose number 2';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.SETUP_FILE_NAME IS 'Path to stored RTU setup file';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITES.PICTURE_FILE_NAME IS 'Path to .jpg or some other site picture';

-- PROMPT Creating Table '&&tbl_owner..XC_SITESENSORS'
CREATE TABLE &&tbl_owner..XC_EXT_SITESENSORS
 (STE_STATION_ID VARCHAR2(240) NOT NULL
 ,SENSORNAME VARCHAR2(240) NOT NULL
 ,ENABLED VARCHAR2(1) NOT NULL
 ,DATA_TABLE VARCHAR2(12) NOT NULL
 ,DESCRIPTION VARCHAR2(32)
 ,MANUFACTURER VARCHAR2(20)
 ,MODEL_NUMBER VARCHAR2(20)
 ,SHEF_PE_CODE VARCHAR2(7)
 ,PARAMETER_CODE VARCHAR2(12)
 ,ALT_PARAMETER_CODE_1 VARCHAR2(12)
 ,ALT_PARAMETER_CODE_2 VARCHAR2(12)
 ,EQUATION VARCHAR2(128)
 ,RATING_TABLE VARCHAR2(128)
 ,OFFSET FLOAT(126)
 ,SHIFT FLOAT(126)
 ,RIGHT_DIGIT NUMBER(6)
 ,HIGH_HIGH_LIMIT FLOAT(126)
 ,HIGH_HIGH_ALARM NUMBER(6)
 ,HIGH_LIMIT FLOAT(126)
 ,HIGH_ALARM NUMBER(6)
 ,LOW_LIMIT FLOAT(126)
 ,LOW_ALARM NUMBER(6)
 ,LOW_LOW_LIMIT FLOAT(126)
 ,LOW_LOW_ALARM NUMBER(6)
 ,ROC_LIMIT FLOAT(126)
 ,ROC_ALARM NUMBER(6)
 ,ROC_INTERVAL NUMBER(6)
 ,DEADBAND FLOAT(126)
 ,NO_CHANGE_INTERVAL FLOAT(126)
 ,NO_CHANGE_ALARM NUMBER(6)
 ,TEXT_LIMIT VARCHAR2(32)
 ,TEXT_ALARM NUMBER(6)
 ,COEFFICIENT1 FLOAT(126)             
 ,COEFFICIENT2 FLOAT(126)             
 ,COEFFICIENT3 FLOAT(126)             
 ,COEFFICIENT4 FLOAT(126)             
 ,COEFFICIENT5 FLOAT(126)             
 ,GPNUMBER1 FLOAT(126)
 ,GPNUMBER2 FLOAT(126)
 ,GPSTRING1 VARCHAR2(20)
 ,GPSTRING2 VARCHAR2(20)
 ,ALERT_ID NUMBER(6)
 ,ALERT_TYPE VARCHAR2(10)
 -- FK to HDB
 ,site_datatype_id number(11) NULL
 ,interval varchar2(16) NULL
 )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.STE_STATION_ID IS 'Logger name for SSP sites';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.SENSORNAME IS 'Short name for sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ENABLED IS 'Processing enabled for this sensor Y/N';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.DATA_TABLE IS 'Data table number where data are stored';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.DESCRIPTION IS 'Long descriptive name for sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.MANUFACTURER IS 'Manufacturer of sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.MODEL_NUMBER IS 'Model Number of sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.SHEF_PE_CODE IS 'Std. Hydrologic Exchange Fmt. physical element code';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.PARAMETER_CODE IS 'Code applied to this type of sensor data';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ALT_PARAMETER_CODE_1 IS 'May be used for an alternate parameter code';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ALT_PARAMETER_CODE_2 IS 'May be used for an alternate parameter code';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.EQUATION IS 'String containing processing equation';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.RATING_TABLE IS 'Path to table file';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.OFFSET IS 'Rating table offset';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.SHIFT IS 'Rating table shift';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.RIGHT_DIGIT IS 'Sensor Right Digit';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.HIGH_HIGH_LIMIT IS 'Highest valid value for this sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.HIGH_HIGH_ALARM IS 'Switch - none, above, below, both';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.HIGH_LIMIT IS 'Highest valid value for this sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.HIGH_ALARM IS 'Switch - none, above, below, both';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.LOW_LIMIT IS 'Lowest valid value for this sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.LOW_ALARM IS 'Switch - none, above, below, both';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.LOW_LOW_LIMIT IS 'Lowest valid value for this sensor';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.LOW_LOW_ALARM IS 'Switch - none, above, below, both';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ROC_LIMIT IS 'Max change rate for data value in units of  per minute';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ROC_ALARM IS 'Switch - none, above, below, both';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ROC_INTERVAL IS 'Interval (secs.) over whch rate is computed';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.DEADBAND IS 'Change in sensor readings of less than this in ROC limit trigger NOC flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.NO_CHANGE_INTERVAL IS 'Max time with no change, DAYS AND FRACTION';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.NO_CHANGE_ALARM IS 'Switch - none, on';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.TEXT_LIMIT IS 'Alarm if text matches this string';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.TEXT_ALARM IS 'Switch - none, on';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.COEFFICIENT1 IS 'COEFFICIENT1';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.COEFFICIENT2 IS 'COEFFICIENT2';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.COEFFICIENT3 IS 'COEFFICIENT3';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.COEFFICIENT4 IS 'COEFFICIENT4';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.COEFFICIENT5 IS 'COEFFICIENT5';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.GPNUMBER1 IS 'General purpose number 1';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.GPNUMBER2 IS 'General purpose number 2';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.GPSTRING1 IS 'General purpose string 1';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.GPSTRING2 IS 'General purpose string 2';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ALERT_ID IS 'Alrt sensor ID';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_SITESENSORS.ALERT_TYPE IS 'Alert sensor types';


-- PROMPT Creating Table 'XC_EXT_DATA1'
CREATE TABLE &&tbl_owner..XC_EXT_DATA1
 (
    -- STATION_ID VARCHAR2(20) NOT NULL,
    -- SENSORNAME VARCHAR2(12) NOT NULL,
    site_datatype_id NUMBER NOT NULL,
    interval VARCHAR2(16) NOT NULL, 
    start_date_time DATE NOT NULL,  
    end_date_time DATE NOT NULL,   
    --TIME_TAG DATE NOT NULL,
    SOURCE VARCHAR2(1) NOT NULL,
    ORIG_VALUE FLOAT(126) NOT NULL,
    -- ED_VALUE FLOAT(126),
    FLAG1 VARCHAR2(1),
    FLAG2 VARCHAR2(1),
    FLAG3 VARCHAR2(1),
    FLAG4 VARCHAR2(1),
    HIGH_HIGH_FLAG NUMBER(6),
    HIGH_FLAG NUMBER(6),
    LOW_FLAG NUMBER(6),
    LOW_LOW_FLAG NUMBER(6),
    ROC_FLAG NUMBER(6),
    NO_CHG_FLAG NUMBER(6),
    ALARM_FLAG NUMBER(6)
 )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);


COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.site_datatype_id IS 'Key to HDB.RBASE.site_datatype_id';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.interval IS 'data interval, eg. instant';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.start_date_time IS 'start date time';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.end_date_time IS 'end date time';

--COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.STATION_ID IS 'STATION ID';

--COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.SENSORNAME IS 'Sensor name';

-- TIME_TAG = RBASE.start_date_time
--COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.TIME_TAG IS 'Data time tag - system format';

--COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.SOURCE IS 'Used to identify message source, e.g. random or self-timed GOES messages';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.ORIG_VALUE IS 'Original data value';

-- ED_VALUE = RBASE.VALUE
-- COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.ED_VALUE IS 'Edited data value';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.FLAG1 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.FLAG2 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.FLAG3 IS 'Data quality falg';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.FLAG4 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.HIGH_HIGH_FLAG IS 'Alarm flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.HIGH_FLAG IS 'Alarm flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.LOW_FLAG IS 'Alarm flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.LOW_LOW_FLAG IS 'Alarm flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.ROC_FLAG IS 'Alarm flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.NO_CHG_FLAG IS 'Alarm flag';

COMMENT ON COLUMN &&tbl_owner..XC_EXT_DATA1.ALARM_FLAG IS 'Alarm flag';

-- PROMPT Creating Table 'XC_GOESQC'
CREATE TABLE &&tbl_owner..XC_GOESQC
 (NESDIS_ID VARCHAR2(8) NOT NULL
 ,TIME_TAG DATE NOT NULL
 ,SOURCE VARCHAR2(1) NOT NULL
 ,STATION_ID VARCHAR2(240)
 ,FAILURE_CODE VARCHAR2(1)
 ,SIG_STRENGTH NUMBER(6)
 ,FREQ_OFFSET NUMBER(6)
 ,MOD_INDEX VARCHAR2(1)
 ,MOD_QUAL VARCHAR2(1)
 ,CHANNEL NUMBER(6)
 ,SAT_LOC VARCHAR2(1)
 ,UC_STATUS VARCHAR2(2)
 ,MESSAGE_LEN NUMBER(6)
 ,ACTUAL_LEN NUMBER(6)
 ,PARITY NUMBER(6)
 ,BUFFER_EMPTY NUMBER(6)
 ,BATTERY_VOLTAGE FLOAT(126)
 ,SPARE1 NUMBER(6)
 ,SPARE2 NUMBER(6)
 )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.NESDIS_ID IS 'NESDIS satellite station ID';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.TIME_TAG IS 'Time tag of message';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.SOURCE IS 'Data source , R,S,  other';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.STATION_ID IS 'Site ID if we can match it';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.FAILURE_CODE IS 'g,?,W,D,A,B,T,U,M,I,N,Q,C,S';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.SIG_STRENGTH IS 'Number in range 32 - 57 in dB';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.FREQ_OFFSET IS 'Frequency Offset in 50Hz increments -8 to 8 or + or - A - e.g. 0 = 0 to 49Hz, A=500-549 Hz';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.MOD_INDEX IS 'N,H, or L - (Normal, high,low)';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.MOD_QUAL IS 'N,F,P (Normal, fair, poor)';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.CHANNEL IS '0-999 Channel message was on';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.SAT_LOC IS 'E,W,C,A - East,west,central,argos';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.UC_STATUS IS 'Not used always FF';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.MESSAGE_LEN IS 'Message length 0 - 15750';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.PARITY IS 'Parity flag';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.BUFFER_EMPTY IS 'Buffer Empty flag';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.BATTERY_VOLTAGE IS 'Battery Voltage if Sutron message';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.SPARE1 IS 'Spare for later use';

COMMENT ON COLUMN &&tbl_owner..XC_GOESQC.SPARE2 IS 'Spare for later us';

-- PROMPT Creating Table 'XC_RTUQC'
CREATE TABLE &&tbl_owner..XC_RTUQC
(
    STATION_ID          VARCHAR2(20) NOT NULL  ,
    TIME_TAG            DATE NOT NULL          ,
    RX_TX               VARCHAR2(1) NOT NULL   ,
    PORT                VARCHAR2(30)           ,
    SEQUENCENUM         NUMBER                 ,
    OPCODE              NUMBER                 ,
    STATUS              NUMBER(6)              ,
    RETRIES             NUMBER(6)              ,
    GOOD                NUMBER(6)              ,
    BAD                 NUMBER(6)              ,
    CRC                 NUMBER(6)              ,
    BATTERY_VOLTAGE     FLOAT(126)             ,
    SPARE1              NUMBER(6)              ,
    SPARE2              NUMBER(6)               
)
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.STATION_ID IS 'Site ID';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.TIME_TAG IS 'Time tag of message';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.RX_TX IS 'RX or TX';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.PORT IS 'COM port name or modem name';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.SEQUENCENUM IS 'SSP sequence number';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.OPCODE IS 'SSP opcode';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.STATUS IS 'SSP status';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.RETRIES IS 'SSP retries';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.GOOD IS 'SSP successfully completes one transmission';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.BAD IS 'SSP fails to complete one transmission';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.CRC IS 'SSP message with CRC error';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.BATTERY_VOLTAGE IS 'Battery Voltage if Sutron message';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.SPARE1 IS 'Spare for later use';

COMMENT ON COLUMN &&tbl_owner..XC_RTUQC.SPARE2 IS 'Spare for later us';


-- PROMPT Creating Table '&&tbl_owner..XC_TXTDATA1'
CREATE TABLE &&tbl_owner..XC_TXTDATA1
 (STATION_ID VARCHAR2(240) NOT NULL
 ,SENSORNAME VARCHAR2(240) NOT NULL
 ,TIME_TAG DATE NOT NULL
 ,SOURCE VARCHAR2(1) NOT NULL
 ,TEXT_VALUE VARCHAR2(256) NOT NULL
 ,FLAG1 VARCHAR2(1)
 ,FLAG2 VARCHAR2(1)
 ,FLAG3 VARCHAR2(1)
 ,FLAG4 VARCHAR2(1)
 ,MESSAGE_STATUS VARCHAR2(2)
 ,TXT_ALARM_FLAG NUMBER(6)
 )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.STATION_ID IS 'Site ID where data originates';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.SENSORNAME IS 'Sensor associated with this text';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.TIME_TAG IS 'Time tag associated with text data';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.SOURCE IS 'Used to identify message source, e.g. random or self-timed GOES messages';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.TEXT_VALUE IS 'Text data string';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.FLAG1 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.FLAG2 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.FLAG3 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.FLAG4 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.MESSAGE_STATUS IS 'Way to track if message read, forwarded, etc., if rqd.';

COMMENT ON COLUMN &&tbl_owner..XC_TXTDATA1.TXT_ALARM_FLAG IS 'Alarm flag';

-- PROMPT Creating Table '&&tbl_owner..XC_PPLIST'
CREATE TABLE &&tbl_owner..XC_PPLIST
 (STATION_ID VARCHAR2(240) NOT NULL
 ,SENSORNAME VARCHAR2(240) NOT NULL
 ,DATA_LABEL VARCHAR2(20) NOT NULL
 ,FUNCTION_NAME VARCHAR2(10) NOT NULL
 )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_PPLIST.STATION_ID IS 'Station ID';

COMMENT ON COLUMN &&tbl_owner..XC_PPLIST.SENSORNAME IS 'Short name for sensor';

COMMENT ON COLUMN &&tbl_owner..XC_PPLIST.DATA_LABEL IS 'Data Label for the Post Process';

COMMENT ON COLUMN &&tbl_owner..XC_PPLIST.FUNCTION_NAME IS 'The function name that calculated, such as AVG, SUM, MIN, MAX';


-- PROMPT Creating Table '&&tbl_owner..XC_PPDATA1'
CREATE TABLE &&tbl_owner..XC_PPDATA1
 (STATION_ID VARCHAR2(240) NOT NULL
 ,SENSORNAME VARCHAR2(240) NOT NULL
 ,DATA_LABEL VARCHAR2(20) NOT NULL
 ,FUNCTION_NAME VARCHAR2(10) NOT NULL
 ,TIME_TAG DATE NOT NULL
 ,TIME_PERIOD FLOAT(126) NOT NULL
 ,PP_VALUE FLOAT(126) NOT NULL
 ,FLAG1 VARCHAR2(1)
 ,FLAG2 VARCHAR2(1)
 ,FLAG3 VARCHAR2(1)
 ,VALUE_TIME_TAG DATE
 ,PROCESS_TIME_TAG DATE NOT NULL
 ,ALTERNATE_LABEL VARCHAR2(20) 
 )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.STATION_ID IS 'Station ID';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.SENSORNAME IS 'Short name for sensor';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.DATA_LABEL IS 'Data Label for the Post Process';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.FUNCTION_NAME IS 'The function name that calculated, such as AVG, SUM, MIN, MAX';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.TIME_TAG IS 'Starting time tag of the processed period';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.TIME_PERIOD IS 'Number of hours of data used by function.  Note, for monthly data, the hours reflect the actual hours in that month';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.PP_VALUE IS 'Post-processed value';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.FLAG1 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.FLAG2 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.FLAG3 IS 'Data quality flag';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.VALUE_TIME_TAG IS 'Exact time value occurred - e.g. time of max';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.PROCESS_TIME_TAG IS 'Exact time the calculation is made';

COMMENT ON COLUMN &&tbl_owner..XC_PPDATA1.ALTERNATE_LABEL IS 'Alternate Label';

-- &&tbl_owner..XC_VER
CREATE TABLE &&tbl_owner..XC_VER
( VERSION NUMBER(6) )
pctfree 10
pctused 80
tablespace HDB_data
storage (initial 100k
         next    100k
         pctincrease 0);

COMMENT ON COLUMN &&tbl_owner..XC_VER.VERSION IS 'Version number';

INSERT INTO &&tbl_owner..XC_VER VALUES ( 6 );

SPOOL OFF
