/*****************************************************************************
*
* Filename    - $Workfile$
*
* Project Name    - UltraTrak Database
*
* Source Code Identifier  - 0/3/300/8010/112
*
* Description   - Vancouver FIDS interface load table.
*
* Language    - Oracle SQL/DDL
*
* File History    - For the development history run the Show History report in PVCS
*
* (c) Ultra Electronics Limited, 2002
*
*****************************************************************************/

/*****************************************************************************
*
* Table         - yvr_fids_info
*
* Description - Vancouver FIDS interface load table.
*
* CR History:
* Date  Name    CR Number CR Description
*
*****************************************************************************/

PROMPT Creating table YVR_FIDS_INFO

CREATE TABLE yvr_fids_info (
   id                            NUMBER NOT NULL,
   flight_no                     CHAR(8) NOT NULL,
   flight_date                   CHAR(7) NOT NULL,
   leg                           CHAR(3) NOT NULL,
   carrier                       CHAR(3),          
   sch_dt                        CHAR(7),
   sch_tm                        CHAR(4),
   est_dt                        CHAR(7),
   est_tm                        CHAR(4),
   act_dt                        CHAR(7),
   act_tm                        CHAR(4),
   std                           DATE NOT NULL,
   via_1                         CHAR(3) NOT NULL ,                   
   via_2                         CHAR(3),         
   via_3                         CHAR(3),        
   via_4                         CHAR(3),       
   via_5                         CHAR(3),      
   via_6                         CHAR(3),     
   range                         CHAR(1),    
   carousel                      CHAR(4),   
   gate                          CHAR(4),  
   actype                        CHAR(4), 
   acreg                         CHAR(8),
   remark                        CHAR(10),
   timestamp                     DATE DEFAULT SYSDATE NOT NULL,
   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 yvr_fids_info add ( constraint yvr_fids_info_pk primary key (appn_row_id) using index tablespace &1._indexes);

COMMENT ON TABLE YVR_FIDS_INFO IS 'This table contains flight information for the Vancouver project';

COMMENT ON COLUMN YVR_FIDS_INFO.ACREG IS  'Aircraft registration';
COMMENT ON COLUMN YVR_FIDS_INFO.ACTYPE IS  'Aircraft type';
COMMENT ON COLUMN YVR_FIDS_INFO.ACT_DT IS  'Actual date of departure';
COMMENT ON COLUMN YVR_FIDS_INFO.ACT_TM IS  'Actual time of departure';
COMMENT ON COLUMN YVR_FIDS_INFO.CAROUSEL IS  'Carousel number';
COMMENT ON COLUMN YVR_FIDS_INFO.CARRIER IS  'Carrier code';
COMMENT ON COLUMN YVR_FIDS_INFO.EST_DT IS  'Estimated date of departure';
COMMENT ON COLUMN YVR_FIDS_INFO.EST_TM IS  'Estimated time of departure';
COMMENT ON COLUMN YVR_FIDS_INFO.FLIGHT_DATE IS  'Flight date';
COMMENT ON COLUMN YVR_FIDS_INFO.FLIGHT_NO IS  'Flight number';
COMMENT ON COLUMN YVR_FIDS_INFO.GATE IS  'Gate number';
COMMENT ON COLUMN YVR_FIDS_INFO.ID IS  'Unique identifier';
COMMENT ON COLUMN YVR_FIDS_INFO.LEG IS  'Flight leg number';
COMMENT ON COLUMN YVR_FIDS_INFO.RANGE IS  'Range';
COMMENT ON COLUMN YVR_FIDS_INFO.REMARK IS  'Flight remarks';
COMMENT ON COLUMN YVR_FIDS_INFO.SCH_DT IS  'Scheduled date of departure';
COMMENT ON COLUMN YVR_FIDS_INFO.SCH_TM IS  'Scheduled time of departure';
COMMENT ON COLUMN YVR_FIDS_INFO.STD IS  'Scheduled departure time';
COMMENT ON COLUMN YVR_FIDS_INFO.TIMESTAMP IS  'Time the row was inserted';
COMMENT ON COLUMN YVR_FIDS_INFO.VIA_1 IS  'First port of call';
COMMENT ON COLUMN YVR_FIDS_INFO.VIA_2 IS  'Second port of call';
COMMENT ON COLUMN YVR_FIDS_INFO.VIA_3 IS  'Third port of call';
COMMENT ON COLUMN YVR_FIDS_INFO.VIA_4 IS  'Fourth port of call';
COMMENT ON COLUMN YVR_FIDS_INFO.VIA_5 IS  'Fifth port of call';
COMMENT ON COLUMN YVR_FIDS_INFO.VIA_6 IS  'Sixth port of call';
COMMENT ON COLUMN YVR_FIDS_INFO.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN YVR_FIDS_INFO.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN YVR_FIDS_INFO.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN YVR_FIDS_INFO.CUSER IS 'The user who created the row';
COMMENT ON COLUMN YVR_FIDS_INFO.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN YVR_FIDS_INFO.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN YVR_FIDS_INFO.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN YVR_FIDS_INFO.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN YVR_FIDS_INFO.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';

