/*****************************************************************************
*
* Filename         - $Workfile:   unauthorised_bag.sql  $
*
*                     Name              Date      Version     CR Number
* Author           -  M G C Helliwell 12/09/03      1.0
* Last Update      -  See revision history below.
*
* Project Name     - FALCP.
*
* Source Code
* Identifier       - N/A.
*
* Design Spec
* Section          - FAS/BBTRK/SD/1245.
*
* Contents         - Live Schema: Creates the unauthorised_bag table.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/

PROMPT Creating table UNAUTHORISED_BAG

CREATE TABLE unauthorised_bag (
   id                            NUMBER(10)   NOT NULL,
   bag_tag                       CHAR(10)     NOT NULL,
   bag_tag1                      CHAR(1)      NULL,
   bag_tag2a                     CHAR(3)      NULL,
   bag_tag2b                     CHAR(3)      NOT NULL,
   bag_tag3                      NUMBER(10)   NOT NULL,
   flight_id                     NUMBER(10)   NOT NULL,
   pax_id                        NUMBER(10)   NULL,
   error_id                      NUMBER       NOT NULL,
   ok_to_fly                     NUMBER(1)    DEFAULT 0 NOT NULL,
   "TIMESTAMP"                   DATE         DEFAULT SYSDATE NOT NULL,
   user_id                       NUMBER(10)   NOT NULL,
   location_code                 CHAR(6)   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 unauthorised_bag add ( constraint unauthorised_bag_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table unauthorised_bag add ( constraint unauthorised_bag_uk1 unique (id) using index tablespace &1._indexes);

CREATE INDEX unauthorised_bag_ind1 ON unauthorised_bag ( flight_id, bag_tag2b, bag_tag3, pax_id) TABLESPACE &1._indexes;

ALTER TABLE unauthorised_bag ADD CONSTRAINT unauthorised_bag_fk_error FOREIGN KEY ( error_id) REFERENCES error ( error_id);

ALTER TABLE unauthorised_bag ADD CONSTRAINT unauthorised_bag_fk_flight FOREIGN KEY ( flight_id) REFERENCES flight ( flight_id) ON DELETE CASCADE;

CREATE INDEX unauthorised_bag_sk1 ON unauthorised_bag(error_id) TABLESPACE &1._indexes;

COMMENT ON TABLE UNAUTHORISED_BAG IS 'This table contains detals of unauthorised bags';

COMMENT ON COLUMN UNAUTHORISED_BAG.BAG_TAG IS  'Ten digit bag tag number';
COMMENT ON COLUMN UNAUTHORISED_BAG.BAG_TAG1 IS  'First digit of the bag tag number';
COMMENT ON COLUMN UNAUTHORISED_BAG.BAG_TAG2A IS  'Bag tag carrier';
COMMENT ON COLUMN UNAUTHORISED_BAG.BAG_TAG2B IS  'Bag tag carrier number';
COMMENT ON COLUMN UNAUTHORISED_BAG.BAG_TAG3 IS  'Last six digits of the bag tag number';
COMMENT ON COLUMN UNAUTHORISED_BAG.ERROR_ID IS  'Error identifier';
COMMENT ON COLUMN UNAUTHORISED_BAG.FLIGHT_ID IS  'Flight identifier';
COMMENT ON COLUMN UNAUTHORISED_BAG.ID IS  'Bag identifier';
COMMENT ON COLUMN UNAUTHORISED_BAG.LOCATION_CODE IS  'Location code';
COMMENT ON COLUMN UNAUTHORISED_BAG.OK_TO_FLY IS  'Indicates whether it is okay to fly with this bag';
COMMENT ON COLUMN UNAUTHORISED_BAG.PAX_ID IS  'Passenger identifier';
COMMENT ON COLUMN UNAUTHORISED_BAG.TIMESTAMP IS  'timestamp';
COMMENT ON COLUMN UNAUTHORISED_BAG.USER_ID IS  'User identifier';
COMMENT ON COLUMN UNAUTHORISED_BAG.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN UNAUTHORISED_BAG.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN UNAUTHORISED_BAG.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN UNAUTHORISED_BAG.CUSER IS 'The user who created the row';
COMMENT ON COLUMN UNAUTHORISED_BAG.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN UNAUTHORISED_BAG.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN UNAUTHORISED_BAG.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN UNAUTHORISED_BAG.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN UNAUTHORISED_BAG.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
