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

PROMPT CREATE TABLE flight_mapping

CREATE TABLE flight_mapping (
    AIRPORT_CODE    VARCHAR2(3) NOT NULL,
    ORIG_FLT_NO     VARCHAR2(8) NOT NULL,
    NEW_FLT_NO      VARCHAR2(8) NOT NULL,
    FLT_DATE        VARCHAR2(5),
    FLT_TYPE        VARCHAR2(1),
    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
)
TABLESPACE          &1._tables;

PROMPT ALTER TABLE flight_mapping

ALTER TABLE flight_mapping ADD ( CONSTRAINT flight_mapping_pk PRIMARY KEY
  (appn_row_id)
  USING INDEX TABLESPACE &1._indexes );

ALTER TABLE flight_mapping ADD ( CONSTRAINT flight_mapping_uk1 UNIQUE
  (airport_code, orig_flt_no, flt_date, flt_type)
  USING INDEX TABLESPACE &1._indexes );

COMMENT ON TABLE FLIGHT_MAPPING IS 'This table contains the dynamic mapping of flight numbers when the FIDS interface changes the flight number, allowing the original flight to still be updated';

COMMENT ON COLUMN FLIGHT_MAPPING.AIRPORT_CODE IS  'Airport code';
COMMENT ON COLUMN FLIGHT_MAPPING.ORIG_FLT_NO IS  'Original flight number';
COMMENT ON COLUMN FLIGHT_MAPPING.NEW_FLT_NO IS  'New flight number';
COMMENT ON COLUMN FLIGHT_MAPPING.FLT_DATE IS  'Flight date';
COMMENT ON COLUMN FLIGHT_MAPPING.FLT_TYPE IS  'Flight direction (O or I)';
COMMENT ON COLUMN FLIGHT_MAPPING.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN FLIGHT_MAPPING.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN FLIGHT_MAPPING.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN FLIGHT_MAPPING.CUSER IS 'The user who created the row';
COMMENT ON COLUMN FLIGHT_MAPPING.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN FLIGHT_MAPPING.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN FLIGHT_MAPPING.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN FLIGHT_MAPPING.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN FLIGHT_MAPPING.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
