PROMPT Creating table INBOUND_PAX

create table INBOUND_PAX (
   INBOUND_PAX_ID                NUMBER(10) not null,
   INBOUND_FLIGHT_ID             NUMBER(10) not null,
   SOURCE_PAX_ID                 NUMBER(10),
   SOURCE_MESSAGE_ID             NUMBER(10),
   RESULT_PAX_ID                 NUMBER(10),
   RESULT_PAX_STATUS             VARCHAR2(11) default 'UNPROCESSED' not null,
   ERROR_CODE                    NUMBER(10),
   INBOUND_PAX_STATUS            VARCHAR2(11),
   PAX_SURNAME                   CHAR(27),
   PAX_FULL_INITS                CHAR(60),
   PAX_INITIALS                  CHAR(4),
   PAX_NUMBER_SAME_SURNAME       NUMBER(6),
   PAX_DESTINATION               CHAR(3),
   FREQUENT_FLYER                VARCHAR2(60),
   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 inbound_pax add ( constraint inbound_pax_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table INBOUND_PAX add constraint inbound_pax_uk1 unique (INBOUND_PAX_ID) using index tablespace &1._indexes;

alter table INBOUND_PAX add constraint INBOUND_PAX_FK_FLIGHT foreign key (INBOUND_FLIGHT_ID) references FLIGHT (FLIGHT_ID) on delete cascade;

alter table INBOUND_PAX add constraint INBOUND_PAX_SOURCE_PAX_ID_CH check ((SOURCE_PAX_ID IS NOT NULL AND SOURCE_MESSAGE_ID IS NULL AND PAX_SURNAME IS NULL AND PAX_INITIALS IS NULL AND PAX_NUMBER_SAME_SURNAME IS NULL AND FREQUENT_FLYER IS NULL) OR (SOURCE_PAX_ID IS NULL AND SOURCE_MESSAGE_ID IS NOT NULL));

alter table INBOUND_PAX add constraint INBOUND_PAX_RESULT_STATUS_CH check (result_pax_status IN ('UNPROCESSED', 'ERROR', 'CREATED', 'AMENDED', 'REVERSED'));

alter table INBOUND_PAX add constraint INBOUND_PAX_STATUS_CH check (inbound_pax_status IN ('TERMINATING', 'TRANSFER', 'THROUGH'));

CREATE INDEX inbound_pax_sk1 ON inbound_pax(inbound_flight_id) TABLESPACE &1._indexes;

COMMENT ON TABLE INBOUND_PAX IS 'This table contains details about passengers from inbound flights';

COMMENT ON COLUMN INBOUND_PAX.ERROR_CODE IS  'Error code';
COMMENT ON COLUMN INBOUND_PAX.FREQUENT_FLYER IS  'Indicates whether the passenger is a frequent flyer';
COMMENT ON COLUMN INBOUND_PAX.INBOUND_FLIGHT_ID IS  'Inbound flight identifier';
COMMENT ON COLUMN INBOUND_PAX.INBOUND_PAX_ID IS  'Unique passenger identifier';
COMMENT ON COLUMN INBOUND_PAX.INBOUND_PAX_STATUS IS  'Passenger status';
COMMENT ON COLUMN INBOUND_PAX.PAX_DESTINATION IS  'Passenger destination';
COMMENT ON COLUMN INBOUND_PAX.PAX_FULL_INITS IS  'Full initials of the passenger';
COMMENT ON COLUMN INBOUND_PAX.PAX_INITIALS IS  'Passenger initials';
COMMENT ON COLUMN INBOUND_PAX.PAX_NUMBER_SAME_SURNAME IS  'Number of passengers with same surname';
COMMENT ON COLUMN INBOUND_PAX.PAX_SURNAME IS  'Passenger surname';
COMMENT ON COLUMN INBOUND_PAX.RESULT_PAX_ID IS  'The passenger identifier of any passenger created on the outbound flight';
COMMENT ON COLUMN INBOUND_PAX.RESULT_PAX_STATUS IS  'The processed status of the passenger created on the outbound flight';
COMMENT ON COLUMN INBOUND_PAX.SOURCE_MESSAGE_ID IS  'BMM ID for incoming BMMs';
COMMENT ON COLUMN INBOUND_PAX.SOURCE_PAX_ID IS  'PAX ID from the initiating flight for inbound transfers';
COMMENT ON COLUMN INBOUND_PAX.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN INBOUND_PAX.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN INBOUND_PAX.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN INBOUND_PAX.CUSER IS 'The user who created the row';
COMMENT ON COLUMN INBOUND_PAX.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN INBOUND_PAX.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN INBOUND_PAX.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN INBOUND_PAX.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN INBOUND_PAX.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
