PROMPT Creating table REFLIGHT_BAG

CREATE TABLE REFLIGHT_BAG ( 
   BAG_TAG                       CHAR (10)     NOT NULL, 
   PAX_ID                        NUMBER (10)   NOT NULL, 
   FLIGHT_ID                     NUMBER (10)   NOT NULL,
   SITE_ID                       NUMBER (10)   NOT NULL,
   BAG_TAG1                      CHAR (1), 
   BAG_TAG2A                     CHAR (3), 
   BAG_TAG2B                     CHAR (3), 
   BAG_TAG3                      NUMBER (10), 
   BAG_DEST                      CHAR (3), 
   ULD_NO                        CHAR (10), 
   ULD_FLIGHT_ID                 NUMBER (10), 
   BAG_SEQUENCE_NO               NUMBER (6), 
   ORIG_PAX_ID                   NUMBER (10),
   ORIG_PAX_INIT                 CHAR (4),
   ORIG_PAX_SURNAME              CHAR (27),
   REFLIGHT_NEEDED               VARCHAR2 (1),
   REFLIGHT_FLIGHT_ID            NUMBER (10),
   REFLIGHT_FLIGHT_NO            CHAR (8),
   REFLIGHT_FLIGHT_DATE          CHAR (5),
   REFLIGHT_FLIGHT_TIME          CHAR (4),
   REFLIGHT_FLIGHT_DEST          CHAR (3),
   REFLIGHT_BAG_TAG              CHAR (10),
   REFLIGHT_BAG_TAG1             CHAR (1), 
   REFLIGHT_BAG_TAG2A            CHAR (3), 
   REFLIGHT_BAG_TAG2B            CHAR (3), 
   REFLIGHT_BAG_TAG3             NUMBER (10),
   REFLIGHT_PAX_ID               NUMBER (10),
   REFLIGHT_LOGIN_NAME           CHAR (15), 
   REFLIGHT_DATE_TIME            DATE,
   REASON_FOR_LOSS               VARCHAR2 (3),
   FAULT_STATION                 CHAR (3),
   WORLDTRACER_REF               VARCHAR2(30),
   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 reflight_bag add ( constraint reflight_bag_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table reflight_bag add ( constraint reflight_bag_uk1 unique (BAG_TAG,PAX_ID) using index tablespace &1._indexes);

ALTER TABLE reflight_bag ADD CONSTRAINT refbag_fk_refpax FOREIGN KEY (pax_id) REFERENCES reflight_pax (pax_id) ;

ALTER TABLE reflight_bag ADD CONSTRAINT refbag_fk_refulds FOREIGN KEY (uld_no, uld_flight_id) REFERENCES reflight_ulds (uld_no, flight_id) ;

CREATE INDEX reflight_bag_ind1 ON reflight_bag (pax_id) TABLESPACE &1._indexes;

CREATE INDEX reflight_bag_ind2 ON reflight_bag (uld_no,uld_flight_id) TABLESPACE &1._indexes;

COMMENT ON TABLE REFLIGHT_BAG IS 'This table contains details of reflighted bags';

COMMENT ON COLUMN REFLIGHT_BAG.BAG_DEST IS  'Bag destination';
COMMENT ON COLUMN REFLIGHT_BAG.BAG_SEQUENCE_NO IS  'Bag sequence number';
COMMENT ON COLUMN REFLIGHT_BAG.BAG_TAG IS  'Ten digit bag tag number';
COMMENT ON COLUMN REFLIGHT_BAG.BAG_TAG1 IS  'First digit of the bag tag number';
COMMENT ON COLUMN REFLIGHT_BAG.BAG_TAG2A IS  'Bag tag carrier';
COMMENT ON COLUMN REFLIGHT_BAG.BAG_TAG2B IS  'Bag tag carrier number';
COMMENT ON COLUMN REFLIGHT_BAG.BAG_TAG3 IS  'Last six digits of the bag tag number';
COMMENT ON COLUMN REFLIGHT_BAG.FAULT_STATION IS  'Fault Station (airport code)';
COMMENT ON COLUMN REFLIGHT_BAG.FLIGHT_ID IS  'Flight identifier';
COMMENT ON COLUMN REFLIGHT_BAG.ORIG_PAX_ID IS  'Original passenger identifier';
COMMENT ON COLUMN REFLIGHT_BAG.ORIG_PAX_INIT IS  'Passenger initials';
COMMENT ON COLUMN REFLIGHT_BAG.ORIG_PAX_SURNAME IS  'Passenger surname';
COMMENT ON COLUMN REFLIGHT_BAG.PAX_ID IS  'Passenger identifier';
COMMENT ON COLUMN REFLIGHT_BAG.REASON_FOR_LOSS IS  'Reason for bag loss';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_BAG_TAG IS  'Ten digit reflighted bag tag number';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_BAG_TAG1 IS  'First digit of the reflighted bag tag number';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_BAG_TAG2A IS  'Reflighted bag tag carrier';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_BAG_TAG2B IS  'Reflighted bag tag carrier number';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_BAG_TAG3 IS  'Last six digits of the reflighted bag tag';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_DATE_TIME IS  'Reflight date and time';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_FLIGHT_DATE IS  'Reflighted flight date';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_FLIGHT_DEST IS  'Reflighted flight destination';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_FLIGHT_ID IS  'Reflighted flight identifier';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_FLIGHT_NO IS  'Reflighted flight number';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_FLIGHT_TIME IS  'Reflighted flight time';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_LOGIN_NAME IS  'Reflight login name';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_NEEDED IS  'Indicates reflight is required';
COMMENT ON COLUMN REFLIGHT_BAG.REFLIGHT_PAX_ID IS  'Reflighted passneger identifier';
COMMENT ON COLUMN REFLIGHT_BAG.SITE_ID IS  'Site identifier';
COMMENT ON COLUMN REFLIGHT_BAG.ULD_FLIGHT_ID IS  'ULD flight identifier';
COMMENT ON COLUMN REFLIGHT_BAG.ULD_NO IS  'ULD number';
COMMENT ON COLUMN REFLIGHT_BAG.WORLDTRACER_REF IS  'Worldtracer reference';
COMMENT ON COLUMN REFLIGHT_BAG.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN REFLIGHT_BAG.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN REFLIGHT_BAG.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN REFLIGHT_BAG.CUSER IS 'The user who created the row';
COMMENT ON COLUMN REFLIGHT_BAG.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN REFLIGHT_BAG.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN REFLIGHT_BAG.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN REFLIGHT_BAG.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN REFLIGHT_BAG.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
