/*****************************************************************************
*
* Filename         - $Workfile:   lost_baggage.sql  $
*
* Author           - Chris Sawer
*
* Project Name     - UltraTrak (Oracle Database)
*
* Contents         - Live Schema: lost_baggage table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

PROMPT Creating table LOST_BAGGAGE

CREATE TABLE lost_baggage (
   bag_tag                       CHAR(10)      NOT NULL,
   bag_tag1                      CHAR(1),
   bag_tag2a                     CHAR(3),
   bag_tag2b                     CHAR(3)       NOT NULL,
   bag_tag3                      NUMBER(10)    NOT NULL,
   site_id                       NUMBER(10)    NOT NULL,
   pax_name                      VARCHAR2(20),
   pax_airport_time              DATE,
   agent_codes                   VARCHAR2(20),
   connection_time               NUMBER,
   out_flight_no                 CHAR(8),
   out_flight_date               CHAR(5),
   out_pax_dest                  CHAR(3),
   out_std                       CHAR(4),
   out_ped                       DATE,
   out_sed                       DATE,
   out_atd			 DATE,
   out_box                       NUMBER,
   out_tar                       CHAR(5),
   in_flight_no                  CHAR(8),
   in_flight_date                CHAR(5),
   in_pax_org                    CHAR(3),
   in_tar                        CHAR(5),
   first_scan_loc                CHAR(6),
   first_scan_time               DATE,
   last_scan_loc                 CHAR(6),
   last_scan_time                DATE,
   transport_time                NUMBER,
   record_name                   VARCHAR2(20),
   record_ir_code                NUMBER(2),
   record_description            VARCHAR2(50),
   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 lost_baggage add ( constraint lost_baggage_pk primary key (appn_row_id) using index tablespace &1._indexes);

COMMENT ON TABLE LOST_BAGGAGE IS 'This table contains details of lost baggage';

COMMENT ON COLUMN LOST_BAGGAGE.AGENT_CODES IS  'Comma separated list of flight.s agents';
COMMENT ON COLUMN LOST_BAGGAGE.BAG_TAG IS  'Bag tag';
COMMENT ON COLUMN LOST_BAGGAGE.BAG_TAG1 IS  'First character of the bag tag';
COMMENT ON COLUMN LOST_BAGGAGE.BAG_TAG2A IS  'Bag tag carrier';
COMMENT ON COLUMN LOST_BAGGAGE.BAG_TAG2B IS  'Bag tag carrier number';
COMMENT ON COLUMN LOST_BAGGAGE.BAG_TAG3 IS  'Serial number of bag tag';
COMMENT ON COLUMN LOST_BAGGAGE.CONNECTION_TIME IS  'Pax connection time (in minutes)';
COMMENT ON COLUMN LOST_BAGGAGE.FIRST_SCAN_LOC IS  'Location this bag was first scanned';
COMMENT ON COLUMN LOST_BAGGAGE.FIRST_SCAN_TIME IS  'Time/date this bag was first scanned';
COMMENT ON COLUMN LOST_BAGGAGE.IN_FLIGHT_DATE IS  'Inbound flight date (DDMON)';
COMMENT ON COLUMN LOST_BAGGAGE.IN_FLIGHT_NO IS  'Inbound flight no';
COMMENT ON COLUMN LOST_BAGGAGE.IN_PAX_ORG IS  'Inbound origin for this pax';
COMMENT ON COLUMN LOST_BAGGAGE.IN_TAR IS  'Inbound stand';
COMMENT ON COLUMN LOST_BAGGAGE.LAST_SCAN_LOC IS  'Location this bag was last scanned';
COMMENT ON COLUMN LOST_BAGGAGE.LAST_SCAN_TIME IS  'Time/date this bag was last scanned';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_BOX IS  'Oubound flight sorting destination';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_FLIGHT_DATE IS  'Outbound flight date (DDMON)';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_FLIGHT_NO IS  'Outbound flight no';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_PAX_DEST IS  'Outbound destination for this pax';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_PED IS  'Outbound public est. departure time';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_SED IS  'Outbound staff estimated departure time';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_STD IS  'Outbound scheduled departure time';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_ATD IS	 'Outbound actual departure time';
COMMENT ON COLUMN LOST_BAGGAGE.OUT_TAR IS  'Outbound stand';
COMMENT ON COLUMN LOST_BAGGAGE.PAX_AIRPORT_TIME IS  'Pax arrival time at airport';
COMMENT ON COLUMN LOST_BAGGAGE.PAX_NAME IS  'Pax name from BSM';
COMMENT ON COLUMN LOST_BAGGAGE.RECORD_DESCRIPTION IS  'Manually input description for record';
COMMENT ON COLUMN LOST_BAGGAGE.RECORD_IR_CODE IS  'Manually input 2-digit irregularity code for record';
COMMENT ON COLUMN LOST_BAGGAGE.RECORD_NAME IS  'Manually input name for record';
COMMENT ON COLUMN LOST_BAGGAGE.TRANSPORT_TIME IS  'Difference between first + last bag scan times (in minutes)';
COMMENT ON COLUMN LOST_BAGGAGE.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN LOST_BAGGAGE.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN LOST_BAGGAGE.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN LOST_BAGGAGE.CUSER IS 'The user who created the row';
COMMENT ON COLUMN LOST_BAGGAGE.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN LOST_BAGGAGE.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN LOST_BAGGAGE.SITE_ID IS 'The site which created the lost bag';
COMMENT ON COLUMN LOST_BAGGAGE.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN LOST_BAGGAGE.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN LOST_BAGGAGE.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
COMMENT ON COLUMN LOST_BAGGAGE.BAG_IDENT_CODE IS 'The Baggage Identification Code to identify an unknown bag';
