/*****************************************************************************
*
* Filename         - $Workfile:   pax.sql  $
*
*                     Name              Date      Version     CR Number
* 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: pax table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT Creating table PAX

create table pax (
   pax_id                        number(10)  not null,
   flight_id                     number(10)  not null,
   booked_flight_id              number(10)  not null,
   pax_initials                  char(4),
   pax_surname                   char(27),
   pax_status                    char(1)     not null,
   time_pax_stored               date        not null,
   no_of_bags                    number(6)   not null,
   no_same_surname               number(6),
   pax_destination               char(3)     not null,
   pax_seat_no                   char(3),
   pax_class                     char(1)     not null,
   pax_sequence_no               number(10)  not null,
   pax_security_no               char(3),
   pax_name_record               char(6),
   pax_security_status           char(1),
   exception_type_id             number(1),
   departure_airport_code        char(3)     not null,
   inbound_flight_no             char(8),
   inbound_date                  char(5),
   inbound_boarding_point        char(3),
   delete_marker                 number(10) default 0 not null,
   authority_to_load_dcs         char(1) default 'N'      not null,
   pax_full_inits                char(60),
   onward_transfer               number(6),
   pax_dcs_sequence_no           char(3),
   onward_flight_count           number(6),
   connection_type               char(6),
   leg_number                    number(6) default 0 ,
   freq_flyer                    varchar2(60),
   pax_bags_class                varchar2(3),
   embassy                       varchar(20),
   vfdb                          char(1),
   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 pax add ( constraint pax_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table pax add ( constraint pax_uk1 unique (pax_id) using index tablespace &1._indexes);

alter table pax add ( constraint pax_fk_flight foreign key (flight_id) references flight (flight_id) on delete cascade);

alter table pax add ( constraint pax_fk_flight_2 foreign key (booked_flight_id) references flight (flight_id) on delete cascade);

alter table pax add (constraint pax_fk_excep_type foreign key (exception_type_id) references BAG_EXCEPTION_TYPES(BAG_EXCEP_ID) );

create index pax_sk1 on pax(pax_sequence_no) tablespace &1._indexes;
 
create index pax_sk2 on pax(pax_surname) tablespace &1._indexes;
 
create index pax_sk5 on pax(flight_id) tablespace &1._indexes;

create index pax_sk6 on pax(booked_flight_id) tablespace &1._indexes;

CREATE INDEX pax_sk7 ON pax(exception_type_id) TABLESPACE &1._indexes;

COMMENT ON TABLE PAX IS 'This table contains details about the passengers is the system';

COMMENT ON COLUMN PAX.AUTHORITY_TO_LOAD_DCS IS  'Indicates that the reconciliation data in the BSM authorises this passenger for loading';
COMMENT ON COLUMN PAX.BOOKED_FLIGHT_ID IS  'Flight identifier of the booked flight (this differs from FLIGHT_ID only if the passenger is on a codeshare flight)';
COMMENT ON COLUMN PAX.CONNECTION_TYPE IS  'Passenger connection type';
COMMENT ON COLUMN PAX.DELETE_MARKER IS  'If a DCS deletes a passenger via a BSM, it is not physically deleted but is marked using this field';
COMMENT ON COLUMN PAX.DEPARTURE_AIRPORT_CODE IS  'Airport departure code for the passenger';
COMMENT ON COLUMN PAX.EMBASSY IS  'Embassy name for diplomatic bags';
COMMENT ON COLUMN PAX.EXCEPTION_TYPE_ID IS  'exception type identifier';
COMMENT ON COLUMN PAX.FLIGHT_ID IS  'Master flight identifier';
COMMENT ON COLUMN PAX.FREQ_FLYER IS  'Indicates whether the passenger is a frequent flyer';
COMMENT ON COLUMN PAX.INBOUND_BOARDING_POINT IS  'Boarding point of the inbound flight for this passenger';
COMMENT ON COLUMN PAX.INBOUND_DATE IS  'Flight date of the inbound flight';
COMMENT ON COLUMN PAX.INBOUND_FLIGHT_NO IS  'Flight number of the inbound flight';
COMMENT ON COLUMN PAX.LEG_NUMBER IS  'Number of this flight leg';
COMMENT ON COLUMN PAX.NO_OF_BAGS IS  'Number of bags checked in by the passenger';
COMMENT ON COLUMN PAX.NO_SAME_SURNAME IS  'Records the "number with same surname" indication from the creating BSM';
COMMENT ON COLUMN PAX.ONWARD_FLIGHT_COUNT IS  'Number of onward flights for this passenger';
COMMENT ON COLUMN PAX.ONWARD_TRANSFER IS  'Number of onward transfers for this passenger';
COMMENT ON COLUMN PAX.PAX_BAGS_CLASS IS  'Passenger bag class (possibly upgraded from PAX_CLASS if a frequent flyer)';
COMMENT ON COLUMN PAX.PAX_CLASS IS  'Passenger class';
COMMENT ON COLUMN PAX.PAX_DCS_SEQUENCE_NO IS  'The passengers sequence number provided in the reconciliation data in the BSM for this passenger';
COMMENT ON COLUMN PAX.PAX_DESTINATION IS  'Destination of the passenger';
COMMENT ON COLUMN PAX.PAX_FULL_INITS IS  'The passenger.s first name and title';
COMMENT ON COLUMN PAX.PAX_ID IS  'Passenger identifier';
COMMENT ON COLUMN PAX.PAX_INITIALS IS  'Passenger initials';
COMMENT ON COLUMN PAX.PAX_NAME_RECORD IS  'The PNR data if provided in the BSM';
COMMENT ON COLUMN PAX.PAX_SEAT_NO IS  'Passenger seat number';
COMMENT ON COLUMN PAX.PAX_SECURITY_NO IS  'The security number from the reconciliation data provided by the BSM';
COMMENT ON COLUMN PAX.PAX_SECURITY_STATUS IS  'Passenger security status as indicated in the BSM';
COMMENT ON COLUMN PAX.PAX_SEQUENCE_NO IS  'Passenger sequence.  This is either internally generated, or created from the reconciliation data provided by the BSM for this passenger';
COMMENT ON COLUMN PAX.PAX_STATUS IS  'Passenger status';
COMMENT ON COLUMN PAX.PAX_SURNAME IS  'Passenger surname';
COMMENT ON COLUMN PAX.TIME_PAX_STORED IS  'Time the row was created';
COMMENT ON COLUMN PAX.VFDB IS  'A Y in this column indicates that the passenger has volunteered for denied boarding.  A NULL or N indicates that they havent';
COMMENT ON COLUMN PAX.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN PAX.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN PAX.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN PAX.CUSER IS 'The user who created the row';
COMMENT ON COLUMN PAX.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN PAX.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN PAX.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN PAX.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN PAX.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
