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

PROMPT Creating Table 'WT_TRANSACTIONS'
CREATE TABLE WT_TRANSACTIONS
 (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
 ,CARRIER VARCHAR2(3) NOT NULL
 ,BAG_APPN_ROW_ID NUMBER(18)
 ,QOH_APPN_ROW_ID NUMBER(18)
 ,WT_CODE VARCHAR2(3) NOT NULL
 ,WT_REQUEST VARCHAR2(4000) NOT NULL
 ,WT_REQUEST_TIME DATE
 ,WT_RESPONSE VARCHAR2(4000)
 ,WT_RESPONSE_TIME DATE
 ,RESPONSE_ERROR VARCHAR2(250)
 );

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

PROMPT Creating Index 'WT_TRANSACTIONS_IDX1'
CREATE INDEX WT_TRANSACTIONS_IDX1 ON WT_TRANSACTIONS
 (trunc(row_date_time))
 TABLESPACE &1._INDEXES;

COMMENT ON TABLE WT_TRANSACTIONS IS 'Outgoing WorldTracer messages and their associated response';
COMMENT ON COLUMN WT_TRANSACTIONS.SITE is 'The site that this transaction applies to';
COMMENT ON COLUMN WT_TRANSACTIONS.CARRIER is 'The carrier that this transaction applies to';
COMMENT ON COLUMN WT_TRANSACTIONS.BAG_APPN_ROW_ID is 'Unique identity of bag that this transaction applies to (in REFLIGHT_BAG)';
COMMENT ON COLUMN WT_TRANSACTIONS.QOH_APPN_ROW_ID is 'Identity of QOH message trace entry in table QOH_MESSAGES. Not currently used on UltraTrak';
COMMENT ON COLUMN WT_TRANSACTIONS.WT_CODE is 'Message type e.g. QOH, FWD or DAH. Only FWD currently used on UltraTrak';
COMMENT ON COLUMN WT_TRANSACTIONS.WT_REQUEST is 'Content of message to send to WorldTracer';
COMMENT ON COLUMN WT_TRANSACTIONS.WT_REQUEST_TIME is 'Date and time message sent to WorldTracer';
COMMENT ON COLUMN WT_TRANSACTIONS.WT_RESPONSE is 'Response message received from WorldTracer';
COMMENT ON COLUMN WT_TRANSACTIONS.WT_RESPONSE_TIME is 'Date and time response received from WorldTracer';
COMMENT ON COLUMN WT_TRANSACTIONS.RESPONSE_ERROR is 'Text indicating the error response';
COMMENT ON COLUMN WT_TRANSACTIONS.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN WT_TRANSACTIONS.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN WT_TRANSACTIONS.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN WT_TRANSACTIONS.CUSER IS 'The user who created the row';
COMMENT ON COLUMN WT_TRANSACTIONS.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN WT_TRANSACTIONS.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN WT_TRANSACTIONS.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN WT_TRANSACTIONS.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN WT_TRANSACTIONS.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
