/*****************************************************************************
*
* Filename         - $Workfile:   unknow_bag.sql  $
*
*                     Name              Date      Version     CR Number
* Author           -  G Clark 19/07/04      1.0
* Last Update      -  See revision history below.
*
* Project Name     - FALCP.
*
* Source Code
* Identifier       - N/A.
*
* Design Spec
* Section          - UAS/URBRS/TN/10488.
*
* Contents         - Live Schema: Creates the unauthorised_bag table.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2004
*
*****************************************************************************/

PROMPT Creating table UNKNOWN_BAG

CREATE TABLE unknown_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,
   group_no                      VARCHAR2(10) NULL,
   user_id                       NUMBER(10)   NOT NULL,
   pax_surname                   CHAR(27)  NULL,
   pax_full_inits                CHAR(60)  NULL,
   flt_manually_entered          CHAR(1)   NULL,
   location_code                 CHAR(6)   NULL,
   bag_ident_code			   VARCHAR2(7),
   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 unknown_bag add ( constraint unknown_bag_pk primary key (appn_row_id) using index tablespace &1._indexes);

ALTER TABLE unknown_bag ADD CONSTRAINT unknown_bag_uk1 unique (id)  using index tablespace &1._indexes;

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

CREATE INDEX unknown_bag_sk1 ON unknown_bag(error_id) TABLESPACE &1._indexes;

create index unknown_bag_idx_group on unknown_bag (group_no) TABLESPACE &1._indexes;

ALTER TABLE unknown_bag ADD CONSTRAINT unknown_bag_fk_error FOREIGN KEY (error_id) REFERENCES error (error_id);

COMMENT ON TABLE UNKNOWN_BAG IS 'This table contains details of unknown bags';

COMMENT ON COLUMN UNKNOWN_BAG.BAG_TAG IS  'Ten digit bag tag number';
COMMENT ON COLUMN UNKNOWN_BAG.BAG_TAG1 IS  'First digit of the bag tag number';
COMMENT ON COLUMN UNKNOWN_BAG.BAG_TAG2A IS  'Bag tag carrier';
COMMENT ON COLUMN UNKNOWN_BAG.BAG_TAG2B IS  'Bag tag carrier number';
COMMENT ON COLUMN UNKNOWN_BAG.BAG_TAG3 IS  'Last six digits of the bag tag number';
COMMENT ON COLUMN UNKNOWN_BAG.ERROR_ID IS  'Error identifier';
COMMENT ON COLUMN UNKNOWN_BAG.FLIGHT_ID IS  'Flight identifier';
COMMENT ON COLUMN UNKNOWN_BAG.FLT_MANUALLY_ENTERED IS  'Indicates whether the flight was manually entered';
COMMENT ON COLUMN UNKNOWN_BAG.GROUP_NO IS  'Group number ';
COMMENT ON COLUMN UNKNOWN_BAG.ID IS  'Bag identifier ';
COMMENT ON COLUMN UNKNOWN_BAG.LOCATION_CODE IS  'Location code';
COMMENT ON COLUMN UNKNOWN_BAG.OK_TO_FLY IS  'Indicates whether it is okay to fly with this bag';
COMMENT ON COLUMN UNKNOWN_BAG.PAX_FULL_INITS IS  'Full initials of the passenger';
COMMENT ON COLUMN UNKNOWN_BAG.PAX_ID IS  'Passenger identifier';
COMMENT ON COLUMN UNKNOWN_BAG.PAX_SURNAME IS  'Passenger surname';
COMMENT ON COLUMN UNKNOWN_BAG.TIMESTAMP IS  'Timestamp';
COMMENT ON COLUMN UNKNOWN_BAG.USER_ID IS  'User identifier';
COMMENT ON COLUMN UNKNOWN_BAG.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN UNKNOWN_BAG.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN UNKNOWN_BAG.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN UNKNOWN_BAG.CUSER IS 'The user who created the row';
COMMENT ON COLUMN UNKNOWN_BAG.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN UNKNOWN_BAG.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN UNKNOWN_BAG.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN UNKNOWN_BAG.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN UNKNOWN_BAG.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
COMMENT ON COLUMN UNKNOWN_BAG.BAG_IDENT_CODE IS 'Baggage Identification Code to identify unknown bags';
