/*****************************************************************************
*
* Filename         - $Workfile$
*
* Author           - Andrew Bailey
* Last Update      - $Date$
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          -
*
* Contents         - Live Schema: wt_config table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

PROMPT Creating Table 'WT_CONFIG'
CREATE TABLE WT_CONFIG
 (ROW_CREATE_TIME DATE
 ,ROW_DATE_TIME DATE
 ,CUSER VARCHAR2(30)
 ,MUSER VARCHAR2(30)
 ,MTERM VARCHAR2(64)
 ,SOURCE VARCHAR2(8)
 ,TO_BE_ARCHIVED VARCHAR2(1)
 ,APPN_ROW_ID NUMBER(18) NOT NULL
 ,SYSTEM_ID NUMBER(4) DEFAULT 0 NOT NULL
 ,SITE VARCHAR2(3) NOT NULL
 ,HOSTNAME VARCHAR2(30) NOT NULL
 ,PASSWORD VARCHAR2(30)
 ,PORT NUMBER(4) NOT NULL
 ,IATA_HTH_HEADER VARCHAR2(100)
 ,WT_DAH_TRAINING VARCHAR2(1)
 ,MATIP_SO_FREQUENCY NUMBER(4)
 ,WT_RESPONSE_TIMEOUT NUMBER(4)
 ,DB_POLL_FREQ NUMBER(6)
 ,TRAINING_SITE VARCHAR2(3)
 ,TRAINING_STATION_AIRLINE VARCHAR2(6)
 ,TRAINING_DESTINATION VARCHAR2(3)
 );

PROMPT Creating Primary Key on 'WT_CONFIG'
ALTER TABLE WT_CONFIG
 ADD (CONSTRAINT WT_CONFIG_PK PRIMARY KEY
  (APPN_ROW_ID)
 USING INDEX TABLESPACE &1._INDEXES);

PROMPT Creating Unique Key on 'WT_CONFIG'
ALTER TABLE WT_CONFIG
 ADD (CONSTRAINT WT_CONFIG_UK1 UNIQUE
  (SITE)
 USING INDEX TABLESPACE &1._INDEXES);

PROMPT Creating Check Constraint on 'WT_CONFIG'
ALTER TABLE WT_CONFIG
 ADD (CONSTRAINT WT_CONFIG_CC_TIMEOUT CHECK (WT_RESPONSE_TIMEOUT>=0));

PROMPT Creating Check Constraint on 'WT_CONFIG'
ALTER TABLE WT_CONFIG
 ADD (CONSTRAINT WT_CONFIG_CC_DAH_TRAINING CHECK (WT_DAH_TRAINING IN('T', NULL)));

PROMPT Creating Check Constraint on 'WT_CONFIG'
ALTER TABLE WT_CONFIG
 ADD (CONSTRAINT WT_CONFIG_CC_FREQUENCY CHECK (MATIP_SO_FREQUENCY>=0));

COMMENT ON TABLE WT_CONFIG IS 'Configuration settings for WorldTracer Interface';
COMMENT ON COLUMN WT_CONFIG.SITE is 'The site that the configuration settings are relevant for';
COMMENT ON COLUMN WT_CONFIG.HOSTNAME is 'Hostname of WorldTracer server for the interface to connect to';
COMMENT ON COLUMN WT_CONFIG.PASSWORD is 'This can be used to specify a password to be used in WorldTracer messages that support passwords';
COMMENT ON COLUMN WT_CONFIG.PORT is 'Port number to use when establishing the connection to the WorldTracer server';
COMMENT ON COLUMN WT_CONFIG.IATA_HTH_HEADER is 'IATA Host-to-Host Header to prefix WorldTracer messages';
COMMENT ON COLUMN WT_CONFIG.WT_DAH_TRAINING is 'Allows the transactions on this interface to be sent as though for a training station rather than a real station';
COMMENT ON COLUMN WT_CONFIG.MATIP_SO_FREQUENCY is 'The Session Open frequency, specifying how often the interface will attempt to open a connection to WorldTracer in seconds';
COMMENT ON COLUMN WT_CONFIG.WT_RESPONSE_TIMEOUT is 'How long the interface will wait for a response before timing out the transaction';
COMMENT ON COLUMN WT_CONFIG.DB_POLL_FREQ is 'The frequency in milliseconds to poll the WT_TRANSACTIONS table';
COMMENT ON COLUMN WT_CONFIG.TRAINING_SITE is 'Site to use in messages when operating as a training station';
COMMENT ON COLUMN WT_CONFIG.TRAINING_STATION_AIRLINE is 'Station airline to use in messages when operating as a training station';
COMMENT ON COLUMN WT_CONFIG.TRAINING_DESTINATION is 'Flight destination to use in messages when operating as a training station';
COMMENT ON COLUMN WT_CONFIG.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN WT_CONFIG.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN WT_CONFIG.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN WT_CONFIG.CUSER IS 'The user who created the row';
COMMENT ON COLUMN WT_CONFIG.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN WT_CONFIG.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN WT_CONFIG.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN WT_CONFIG.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN WT_CONFIG.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
