PROMPT Creating table INBOUND_ULD

create table INBOUND_ULD (
   INBOUND_ULD_ID                NUMBER(10) not null,
   INBOUND_FLIGHT_ID             NUMBER(10) not null,
   SOURCE_ULD_NO                 CHAR(10),
   SOURCE_MESSAGE_ID             NUMBER(10),
   RESULT_ULD_NO                 CHAR(10),
   RESULT_ULD_FLIGHT_ID          NUMBER(10),
   RESULT_ULD_STATUS             VARCHAR2(11) default 'UNPROCESSED' not null,
   ERROR_CODE                    NUMBER(10),
   INBOUND_ULD_STATUS            VARCHAR2(11),
   ULD_NUMBER                    CHAR(10),
   ULD_TYPE                      CHAR(1),
   ULD_CATEGORY                  VARCHAR2(5),
   ULD_POSITION                  CHAR(6),
   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_uld add ( constraint inbound_uld_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table INBOUND_ULD add constraint inbound_uld_uk1 unique (INBOUND_ULD_ID) using index tablespace &1._indexes;

-- Add comments to the columns 
comment on column INBOUND_ULD.SOURCE_ULD_NO
  is 'ULD NO from the initiating flight for inbound transfers';
comment on column INBOUND_ULD.SOURCE_MESSAGE_ID
  is 'BMM ID for incoming BMMs';
comment on column INBOUND_ULD.RESULT_ULD_NO
  is 'ULD NO of any ULD created on the outbound flight';

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

alter table INBOUND_ULD add constraint INBOUND_ULD_SOURCE_ULD_ID_CH check ((SOURCE_ULD_NO IS NOT NULL AND SOURCE_MESSAGE_ID IS NULL AND ULD_NUMBER IS NULL AND ULD_TYPE IS NULL AND ULD_CATEGORY IS NULL AND ULD_POSITION IS NULL) OR (SOURCE_ULD_NO IS NULL AND SOURCE_MESSAGE_ID IS NOT NULL));

alter table INBOUND_ULD add constraint INBOUND_ULD_RESULT_STATUS_CH check (result_uld_status IN ('UNPROCESSED', 'ERROR', 'CREATED', 'AMENDED', 'REVERSED'));

alter table INBOUND_ULD add constraint INBOUND_ULD_STATUS_CH check (inbound_uld_status IN ('TERMINATING', 'TRANSFER', 'THROUGH'));

CREATE INDEX inbound_uld_sk1 ON inbound_uld(inbound_flight_id) TABLESPACE &1._indexes;

COMMENT ON TABLE INBOUND_ULD IS 'This table contains details about ULDs for inbound flights';

COMMENT ON COLUMN INBOUND_ULD.ERROR_CODE IS  'Error code';
COMMENT ON COLUMN INBOUND_ULD.INBOUND_FLIGHT_ID IS  'Flight identifier';
COMMENT ON COLUMN INBOUND_ULD.INBOUND_ULD_ID IS  'ULD identifier';
COMMENT ON COLUMN INBOUND_ULD.INBOUND_ULD_STATUS IS  'ULD status';
COMMENT ON COLUMN INBOUND_ULD.RESULT_ULD_FLIGHT_ID IS  'Resulting ULD flight identifier';
COMMENT ON COLUMN INBOUND_ULD.RESULT_ULD_NO IS  'Resulting ULD number';
COMMENT ON COLUMN INBOUND_ULD.RESULT_ULD_STATUS IS  'Resulting ULD status';
COMMENT ON COLUMN INBOUND_ULD.SOURCE_MESSAGE_ID IS  'Source message identifier';
COMMENT ON COLUMN INBOUND_ULD.SOURCE_ULD_NO IS  'Source ULD number';
COMMENT ON COLUMN INBOUND_ULD.ULD_CATEGORY IS  'ULD category';
COMMENT ON COLUMN INBOUND_ULD.ULD_NUMBER IS  'ULD number';
COMMENT ON COLUMN INBOUND_ULD.ULD_POSITION IS  'ULD position';
COMMENT ON COLUMN INBOUND_ULD.ULD_TYPE IS  'ULD type';
COMMENT ON COLUMN INBOUND_ULD.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN INBOUND_ULD.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN INBOUND_ULD.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN INBOUND_ULD.CUSER IS 'The user who created the row';
COMMENT ON COLUMN INBOUND_ULD.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN INBOUND_ULD.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN INBOUND_ULD.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN INBOUND_ULD.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN INBOUND_ULD.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
