PROMPT Creating table INBOUND_BAG

create table inbound_bag
(
   inbound_bag_id                number(10) not null,
   inbound_pax_id                number(10) not null,
   inbound_uld_id                number(10),
   source_bag_id                 number(10),
   source_message_id             number(10),
   result_bag_id                 number(10),
   result_bag_status             varchar2(11) default 'UNPROCESSED' not null,
   error_code                    number(10),
   bag_tag                       char(10),
   bag_weight                    number(5),
   bag_load_sequence             number(6),
   bag_exception_type            char(4),
   bag_security_status           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 inbound_bag add ( constraint inbound_bag_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table inbound_bag add constraint inbound_bag_uk1 unique (INBOUND_BAG_ID) using index tablespace &1._indexes;

alter table inbound_bag add constraint inbound_bag_fk_pax foreign key (INBOUND_PAX_ID) references inbound_pax (INBOUND_PAX_ID) on delete cascade;

alter table inbound_bag add constraint inbound_bag_fk_inbound_uld foreign key (INBOUND_ULD_ID) references inbound_uld (INBOUND_ULD_ID);

alter table inbound_bag add constraint inbound_bag_source_bag_id_ch check (((source_bag_id IS NOT NULL AND source_message_id IS NULL AND bag_tag IS NULL AND bag_weight IS NULL AND bag_load_sequence IS NULL AND bag_exception_type IS NULL AND bag_security_status IS NULL) OR (source_bag_id IS NULL AND source_message_id IS NOT NULL)));

alter table INBOUND_BAG add constraint INBOUND_BAG_RESULT_STATUS_CH check (result_bag_status IN ('UNPROCESSED', 'ERROR', 'CREATED', 'AMENDED', 'REVERSED'));

CREATE INDEX inbound_bag_sk1 ON inbound_bag(inbound_pax_id) TABLESPACE &1._indexes;

CREATE INDEX inbound_bag_sk2 ON inbound_bag(inbound_uld_id) TABLESPACE &1._indexes;

COMMENT ON TABLE INBOUND_BAG IS 'This table contains details about bags from inbound flights';

COMMENT ON COLUMN INBOUND_BAG.BAG_EXCEPTION_TYPE IS  'Bag exception type';
COMMENT ON COLUMN INBOUND_BAG.BAG_LOAD_SEQUENCE IS  'Loading sequence for the bag';
COMMENT ON COLUMN INBOUND_BAG.BAG_SECURITY_STATUS IS  'Security status';
COMMENT ON COLUMN INBOUND_BAG.BAG_TAG IS  'Ten digit bag tag number';
COMMENT ON COLUMN INBOUND_BAG.BAG_WEIGHT IS  'Bag weight';
COMMENT ON COLUMN INBOUND_BAG.ERROR_CODE IS  'Error code';
COMMENT ON COLUMN INBOUND_BAG.INBOUND_BAG_ID IS  'Unique identifier for the bag';
COMMENT ON COLUMN INBOUND_BAG.INBOUND_PAX_ID IS  'Inbound passenger identifier';
COMMENT ON COLUMN INBOUND_BAG.INBOUND_ULD_ID IS  'Inbound ULD number - NULL if the bag is loaded in a hold';
COMMENT ON COLUMN INBOUND_BAG.RESULT_BAG_ID IS  'The bag identifier of the corresponding bag created on the outbound flight';
COMMENT ON COLUMN INBOUND_BAG.RESULT_BAG_STATUS IS  'The processed status of the corresponding outbound bag';
COMMENT ON COLUMN INBOUND_BAG.SOURCE_BAG_ID IS  'Set to the bag ID of the bag on the initiating flight for an inbound transfer';
COMMENT ON COLUMN INBOUND_BAG.SOURCE_MESSAGE_ID IS  'Set to the BMM ID for incoming BMMs';
COMMENT ON COLUMN INBOUND_BAG.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN INBOUND_BAG.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN INBOUND_BAG.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN INBOUND_BAG.CUSER IS 'The user who created the row';
COMMENT ON COLUMN INBOUND_BAG.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN INBOUND_BAG.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN INBOUND_BAG.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN INBOUND_BAG.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN INBOUND_BAG.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
