/*****************************************************************************
*
* Filename         - $Workfile:   stats.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live schema stats table - loading statistics
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   stats.sql  $  $Revision:   1.3  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/tables/stats.svl  $
* 
*    Rev 1.3   21 Jun 2001 14:48:12   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
*    Rev 1.2   16 Dec 1998 11:34:46   tagtrak
* OR: 12712 12689
* CR: 12712
* REV:
* Modifications to support bag source (local or transfer etc)
* in loading statistics.
* -------------
* Andrew Walker
* -------------
* (branch ATS revision copied to trunk.)
* Andrew Walker
* 
*    Rev 1.1.1.0   24 Nov 1998 16:10:40   tagtrak
* OR: 12712 12689
* CR: 12712
* REV:
* Modifications to support bag source (local or transfer etc)
* in loading statistics.
* -------------
* Andrew Walker
* 
*    Rev 1.1   02 Apr 1998 19:43:42   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.0   26 Feb 1998 11:16:08   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
****************************************************************************/

PROMPT Creating table STATS

create table stats (
    date_time                    date,
    hour                         number(2),
    login_name                   char(15),
    flight_no                    char(8),
    flight_date                  char(5),
    bag_source                   char(1),
    airport_code                 char(3),
    normal                       number default 0,
    gate                         number default 0,
    crew                         number default 0,
    rush                         number default 0,
    mail                         number default 0,
    dipl                         number default 0,
    misc                         number default 0,
   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
);

alter table stats add ( constraint stats_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table stats add ( constraint stats_uk1 unique (date_time,hour,login_name,flight_no,flight_date,bag_source) using index tablespace &1._indexes);

alter table stats add constraint stats_fk_tagtrak_users foreign key (login_name) references tagtrak_users (loginname);
	
create index stats_flight on stats(flight_no, flight_date) tablespace &1._indexes;

CREATE INDEX stats_sk1 ON stats(login_name) TABLESPACE &1._indexes;

COMMENT ON TABLE STATS IS 'This table contains statistics about flights';

COMMENT ON COLUMN STATS.AIRPORT_CODE IS  'Airport code';
COMMENT ON COLUMN STATS.BAG_SOURCE IS  'Bag source';
COMMENT ON COLUMN STATS.CREW IS  'Number of crew bags';
COMMENT ON COLUMN STATS.DATE_TIME IS  'Date';
COMMENT ON COLUMN STATS.DIPL IS  'Number of diplomatic bags';
COMMENT ON COLUMN STATS.FLIGHT_DATE IS  'Flight date';
COMMENT ON COLUMN STATS.FLIGHT_NO IS  'Flight number';
COMMENT ON COLUMN STATS.GATE IS  'Number of gate bags';
COMMENT ON COLUMN STATS.HOUR IS  'Hour of the day';
COMMENT ON COLUMN STATS.LOGIN_NAME IS  'Login name';
COMMENT ON COLUMN STATS.MAIL IS  'Number of mail bags';
COMMENT ON COLUMN STATS.MISC IS  'Number of miscellaneous bags';
COMMENT ON COLUMN STATS.NORMAL IS  'Number of normal bags';
COMMENT ON COLUMN STATS.RUSH IS  'Number of rush bags';
COMMENT ON COLUMN STATS.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN STATS.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN STATS.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN STATS.CUSER IS 'The user who created the row';
COMMENT ON COLUMN STATS.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN STATS.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN STATS.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN STATS.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN STATS.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
