/*****************************************************************************
*
* Filename         - $Workfile:   bag.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell 6/8/2        1.6         ?????
*                     Yomi   Kogbe     7/8/02       1.6         ?????
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema: Bag table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT Creating Table BAG

create table bag (
   bag_id                        number(10) not null,
   bag_tag                       char(10) not null,
   pax_id                        number(10) not null,
   uld_no                        char(10) ,
   authority_to_load_dcs         char(1) default 'N' not null,
   authority_to_load_brs         char(1) default 'Y' not null,
   unassociated_bag              char(1) not null,
   bag_destination               char(3) not null,
   bag_exception                 char(4) ,
   bag_source_indicator          char(1) not null,
   bag_source_location           char(3) not null,
   loaded_status                 char(1) default 'N' not null,
   bag_sequence_no               number(6) ,
   time_loaded                   date ,
   time_bag_stored               date not null,
   delete_marker                 number(10) default 0 not null,
   bag_tag1                      char(1) ,
   bag_tag2a                     char(3) ,
   bag_tag2b                     char(3) not null,
   bag_tag3                      number(10) not null,
   location_code                 char(6) default '-' not null,
   scanner_id                    char(6) ,
   process_name                  char(20) ,
   unit_name                     char(10) ,
   dcsid                         char(7) ,
   subunit_name                  char(4) ,
   uld_flight_id                 number(10) ,
   dcs_bag_tag                   char(10) ,
   security_clearance_status     char(1) default 'U' not null,
   sc_user_id                    number(10),
   sc_location                   char(6),
   sc_time                       date,
   connection_type               char(6) ,
   bag_type                      varchar2(59) ,
   next_flight_no                varchar2(8),
   next_flight_date              varchar2(5),
   hbmd_flag                     varchar2(1) default 'Y',
   group_no                      varchar2(10),
   delivery_instr                varchar2(256),
   worldtracer_ref               varchar2(30),
   kg_weight                     number(5) default 0 not null,
   bvis_subheader                raw(42),
   authority_source              char(3) default 'DCS' not null,
   checkin_time                  date,
   checkin_location              char(4),
   checkin_location_desc         varchar2(12),
   security_result               varchar2(3) null,
   security_reason               varchar2(1) 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 bag add ( constraint bag_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table bag add ( constraint bag_uk1 unique (bag_id) using index tablespace &1._indexes );

alter table bag add ( constraint bag_uk2 unique (bag_tag, pax_id) using index tablespace &1._indexes );

alter table bag add ( constraint bag_fk_location foreign key (location_code) references location (location_code));

alter table bag add ( constraint bag_fk_pax foreign key (pax_id) references pax (pax_id) on delete cascade);

alter table bag add ( constraint bag_fk_ulds foreign key (uld_no, uld_flight_id) references ulds (uld_no, flight_id));

alter table bag add ( constraint bag_fk_system_cfg foreign key (process_name, unit_name, subunit_name, dcsid) references system_cfg (process_name, unit_name, subunit_name, dcsid));

alter table bag add ( constraint bag_authority_source_check CHECK (authority_source in ('DCS','MWS')));

alter table bag add ( constraint bag_ch CHECK (security_clearance_status in ('C','N','U')));

create index bag_sk4 on BAG(TIME_BAG_STORED) tablespace &1._indexes;

create index bag_sk8 on BAG(BAG_TAG3) tablespace &1._indexes;

create index bag_sk10 on BAG(PAX_ID) tablespace &1._indexes;

create index bag_sk11 on BAG(uld_no) tablespace &1._indexes;

create index bag_sk12 on BAG(ULD_FLIGHT_ID) tablespace &1._indexes;

create index bag_idx_group on bag(group_no) tablespace &1._indexes;

create index uld_flight_ind1 on bag (uld_no,uld_flight_id) tablespace &1._indexes;

create index bag_sk13 ON bag(location_code) tablespace &1._indexes;

CREATE INDEX bag_sk14 ON bag(process_name, unit_name, subunit_name, dcsid) TABLESPACE &1._indexes;

COMMENT ON TABLE BAG IS 'This table contains details about each bag in the system';

COMMENT ON COLUMN BAG.AUTHORITY_SOURCE IS  'The source of permission to load this bag';
COMMENT ON COLUMN BAG.AUTHORITY_TO_LOAD_BRS IS  'Bag has not been stopped';
COMMENT ON COLUMN BAG.AUTHORITY_TO_LOAD_DCS IS  'Authority to load has been granted via DCS';
COMMENT ON COLUMN BAG.BAG_DESTINATION IS  'The bag destination';
COMMENT ON COLUMN BAG.BAG_EXCEPTION IS  'Bag exception associated with the bag';
COMMENT ON COLUMN BAG.BAG_ID IS  'Unique bag identifier';
COMMENT ON COLUMN BAG.BAG_SEQUENCE_NO IS  'Bag sequence number';
COMMENT ON COLUMN BAG.BAG_SOURCE_INDICATOR IS  'Bag source indicator';
COMMENT ON COLUMN BAG.BAG_SOURCE_LOCATION IS  'Bag source location';
COMMENT ON COLUMN BAG.BAG_TAG IS  'Full 10 digit bag tag number';
COMMENT ON COLUMN BAG.BAG_TAG1 IS  'First character of the bag tag';
COMMENT ON COLUMN BAG.BAG_TAG2A IS  'Bag tag carrier';
COMMENT ON COLUMN BAG.BAG_TAG2B IS  'Bag tag carrier number';
COMMENT ON COLUMN BAG.BAG_TAG3 IS  'Last six digits of the bag tag';
COMMENT ON COLUMN BAG.BAG_TYPE IS  'Bag type';
COMMENT ON COLUMN BAG.BVIS_SUBHEADER IS  'BVIS Sub Header';
COMMENT ON COLUMN BAG.CONNECTION_TYPE IS  'Connection type';
COMMENT ON COLUMN BAG.DCSID IS  'The identifier for the DCS';
COMMENT ON COLUMN BAG.DCS_BAG_TAG IS  'The DCS bag tag';
COMMENT ON COLUMN BAG.DELETE_MARKER IS  'Indicates that the bag has been deleted';
COMMENT ON COLUMN BAG.DELIVERY_INSTR IS  'Delivery instructions for rush bags';
COMMENT ON COLUMN BAG.GROUP_NO IS  'Group number';
COMMENT ON COLUMN BAG.HBMD_FLAG IS  'HBMD flag';
COMMENT ON COLUMN BAG.KG_WEIGHT IS  'Bag weight in kg';
COMMENT ON COLUMN BAG.LOADED_STATUS IS  'Bag loaded status';
COMMENT ON COLUMN BAG.LOCATION_CODE IS  'Current bag location';
COMMENT ON COLUMN BAG.NEXT_FLIGHT_DATE IS  'Date of the next flight for transfer';
COMMENT ON COLUMN BAG.NEXT_FLIGHT_NO IS  'Flight number of the next flight for transfer';
COMMENT ON COLUMN BAG.PAX_ID IS  'Identity of the passenger to whom this bag belongs';
COMMENT ON COLUMN BAG.PROCESS_NAME IS  'Process name';
COMMENT ON COLUMN BAG.SCANNER_ID IS  'Scanner ID of the last scanner to scan the bag';
COMMENT ON COLUMN BAG.SC_LOCATION IS  'Location of last scan';
COMMENT ON COLUMN BAG.SC_TIME IS  'Time of last scan';
COMMENT ON COLUMN BAG.SC_USER_ID IS  'User who last scanned the bag';
COMMENT ON COLUMN BAG.SECURITY_CLEARANCE_STATUS IS  'Security clearance status for the bag';
COMMENT ON COLUMN BAG.SUBUNIT_NAME IS  'Subunit name';
COMMENT ON COLUMN BAG.TIME_BAG_STORED IS  'Time the bag was stored';
COMMENT ON COLUMN BAG.TIME_LOADED IS  'Time the bag was loaded';
COMMENT ON COLUMN BAG.ULD_FLIGHT_ID IS  'Flight identifier for the ULD associated with this bag';
COMMENT ON COLUMN BAG.ULD_NO IS  'ULD number for the ULD associated with this bag';
COMMENT ON COLUMN BAG.UNASSOCIATED_BAG IS  'Set to Y if the bag is not associated with a flight, N otherwise.';
COMMENT ON COLUMN BAG.UNIT_NAME IS  'Unit name';
COMMENT ON COLUMN BAG.WORLDTRACER_REF IS  'The worldtracer reference for this bag';
COMMENT ON COLUMN BAG.SECURITY_RESULT IS  'The security result for this bag';
COMMENT ON COLUMN BAG.SECURITY_REASON IS  'The security reason for this bag';
COMMENT ON COLUMN BAG.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN BAG.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN BAG.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN BAG.CUSER IS 'The user who created the row';
COMMENT ON COLUMN BAG.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN BAG.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN BAG.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN BAG.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN BAG.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
