/*****************************************************************************
*
* Filename         - fgos_security_messages.sql
*
*                     Name              Date      Version     CR Number
* Author           -  Phil Burns        25-09-2007
* Last Update      - 
*
* Project Name     -  Air China               
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -  UAS/FALCP/CA/2736
*
* Contents         - Live Schema: FGOS_SECURITY_MESASGES table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

PROMPT CREATE TABLE fgos_security_messages

CREATE TABLE fgos_security_messages (
    FLT_NO           VARCHAR2(12)  NOT NULL,
    FLT_DATE         VARCHAR2(5)   NOT NULL,
    AIRPORT_CODE     VARCHAR(3)    NOT NULL,
    MESSAGE_TYPE     VARCHAR2(1)   NOT NULL,
    BAG_TAG          VARCHAR2(12),
    PNR              VARCHAR2(6),
    ENGLISH_NAME     VARCHAR2(30),
    CHINESE_NAME     VARCHAR2(20),
    BLOCK_DEPT       VARCHAR2(30),
    BLOCK_REASON     VARCHAR2(80),
    BLOCK_STATUS     VARCHAR(30),
    BLOCK_TIME       DATE,
    TEL              VARCHAR2(80),
    SECURITY_RESULT  VARCHAR2(30),
    PROCESSED        VARCHAR2(1)  DEFAULT 'N' 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
)
TABLESPACE          &1._tables;

PROMPT ALTER TABLE fgos_security_messages

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

ALTER TABLE fgos_security_messages ADD CONSTRAINT ck_fgos_sec_mess_processed check (processed IN ('Y','N'));
ALTER TABLE fgos_security_messages ADD CONSTRAINT ck_fgos_sec_mess_message_type check (message_type IN ('P','B'));

COMMENT ON TABLE fgos_security_messages IS 'This table contains the pax and bag security messages received from FGOS';

COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.FLT_NO IS  'Flight number';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.FLT_DATE IS  'Flight date';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.AIRPORT_CODE IS 'Airport Code';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.MESSAGE_TYPE IS 'A Pax or Bag security message';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.BAG_TAG IS 'IATA Bag Tag';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.PNR IS 'Passenger number record'; 
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.ENGLISH_NAME IS 'Passenger name in English';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.CHINESE_NAME IS 'Passenger name in Chinese';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.BLOCK_DEPT IS 'Blocking department';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.BLOCK_REASON IS 'Reason for block';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.BLOCK_STATUS IS 'Blocking status';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.BLOCK_TIME IS 'Blocking time'; 
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.TEL IS 'Passenger contact details'; 
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.SECURITY_RESULT IS 'Combined value of security results';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.PROCESSED IS 'Flag to indicate whether message has been processed'; 
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.CUSER IS 'The user who created the row';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.MTERM IS 'The terminal from which the last modification to this row was made';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN FGOS_SECURITY_MESSAGES.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';

