/*****************************************************************************
 *
 * Filename         - $Workfile:   hbmd.sql         $
 *
 *                    Name              Date      Version     CR Number
 * Author           - Chris Reason      27/05/03  
 * Last Update      - See revision history below.
 *
 * Project Name     - FALCP.
 *
 * Source Code
 * Identifier       - N/A.
 *
 * Design Spec
 * Section          - FAS/BBTRK/SD/238.
 *
 * Contents         - Live Schema: Creates the hbmd table.
 *
 * Language         - Oracle SQL.
 *
 * Classification   - Commercial In Confidence
 *                    (c) Ultra Electronics Limited, 2003
 *
 *****************************************************************************/

PROMPT Creating table HBMD

create table HBMD (
   FLIGHT_ID                     number(10) NOT NULL,
   VERSION                       number(4)  NOT NULL,
   FLIGHT_NO                     varchar2(8),
   FLIGHT_DATE                   varchar2(5),
   FLIGHT_SCH_TIME               varchar2(4),
   USER_ID                       number(10),
   AUTH_DATE                     date,
   FIRSTNAME                     varchar2(20),
   SURNAME                       varchar2(27),
   AUTH_TYPE                     varchar2(10),
   STATUS                        varchar2(12) DEFAULT 'UNAUTHORISED' NOT NULL,
   CHECKED_BAGS_BRS              NUMBER(6)    DEFAULT 0,
   CREW_BAGS_BRS                 NUMBER(6)    DEFAULT 0,
   RUSH_BAGS_BRS                 NUMBER(6)    DEFAULT 0 , 
   GATE_BAGS_BRS                 NUMBER(6)    DEFAULT 0,
   MAIL_BAGS_BRS                 NUMBER(6)    DEFAULT 0,
   DIPL_BAGS_BRS                 NUMBER(6)    DEFAULT 0,
   OTHER_BAGS_BRS                NUMBER(6)    DEFAULT 0 ,  
   CHECKED_BAGS_BRS_EDITED       NUMBER(6)    DEFAULT 0,
   CREW_BAGS_BRS_EDITED          NUMBER(6)    DEFAULT 0 ,     
   RUSH_BAGS_BRS_EDITED          NUMBER(6)    DEFAULT 0  ,             
   GATE_BAGS_BRS_EDITED          NUMBER(6)    DEFAULT 0,
   MAIL_BAGS_BRS_EDITED          NUMBER(6)    DEFAULT 0,
   DIPL_BAGS_BRS_EDITED          NUMBER(6)    DEFAULT 0 ,
   OTHER_BAGS_BRS_EDITED         NUMBER(6)    DEFAULT 0  ,        
   CHECKED_BAGS_MANUAL           NUMBER(6)    DEFAULT 0,
   CREW_BAGS_MANUAL              NUMBER(6)    DEFAULT 0,
   RUSH_BAGS_MANUAL              NUMBER(6)    DEFAULT 0 ,                 
   GATE_BAGS_MANUAL              NUMBER(6)    DEFAULT 0,
   MAIL_BAGS_MANUAL              NUMBER(6)    DEFAULT 0 ,                 
   DIPL_BAGS_MANUAL              NUMBER(6)    DEFAULT 0,
   OTHER_BAGS_MANUAL             NUMBER(6)    DEFAULT 0,
   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 hbmd add ( constraint hbmd_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table hbmd add constraint hbmd_uk1 unique (FLIGHT_ID,VERSION) using index tablespace &1._indexes;

alter table HBMD add constraint ck_hbmd_status check( STATUS IN ('INVALID', 'UNAUTHORISED', 'AUTHORISED') );

alter table HBMD add constraint ck_hbmd_type check( AUTH_TYPE IN ('MANUAL', 'ELECTRONIC') );

ALTER TABLE HBMD ADD CONSTRAINT HBMD_FK_FLIGHT FOREIGN KEY (FLIGHT_ID) REFERENCES FLIGHT (FLIGHT_ID) ON DELETE CASCADE;

create index hbmd_sk1 on hbmd(flight_id) tablespace &1._indexes;

COMMENT ON TABLE HBMD IS 'This table contains information about whether or not a flight has produced and authorised an HBMD (Hold Baggage Manifest Declaration)';

COMMENT ON COLUMN HBMD.AUTH_DATE IS  'The date/time the HBMD was authorised or printed for a manual authorisation';
COMMENT ON COLUMN HBMD.AUTH_TYPE IS  'The type of authorisation being performed, either MANUAL or ELECTRONIC';
COMMENT ON COLUMN HBMD.CHECKED_BAGS_BRS IS  'Number of checked bags calculated by the BRS';
COMMENT ON COLUMN HBMD.CHECKED_BAGS_BRS_EDITED IS  'Number of crew bags edited';
COMMENT ON COLUMN HBMD.CHECKED_BAGS_MANUAL IS  'Number of checked bags';
COMMENT ON COLUMN HBMD.CREW_BAGS_BRS IS  'Number of crew bags calculated by the BRS';
COMMENT ON COLUMN HBMD.CREW_BAGS_BRS_EDITED IS  'Number of crew bags edited';
COMMENT ON COLUMN HBMD.CREW_BAGS_MANUAL IS  'Number of crew bags';
COMMENT ON COLUMN HBMD.DIPL_BAGS_BRS IS  'Number of diplomatic bags calculated by the BRS';
COMMENT ON COLUMN HBMD.DIPL_BAGS_BRS_EDITED IS  'Number of diplomatic bags edited';
COMMENT ON COLUMN HBMD.DIPL_BAGS_MANUAL IS  'Number of diplomatic bags';
COMMENT ON COLUMN HBMD.FIRSTNAME IS  'The first name of the person authorising the HBMD, or requesting it printed for a manual authorisation';
COMMENT ON COLUMN HBMD.FLIGHT_DATE IS  'Flight date';
COMMENT ON COLUMN HBMD.FLIGHT_ID IS  'Flight identifier';
COMMENT ON COLUMN HBMD.FLIGHT_NO IS  'Flight number';
COMMENT ON COLUMN HBMD.FLIGHT_SCH_TIME IS  'Scheduled time for the flight';
COMMENT ON COLUMN HBMD.GATE_BAGS_BRS IS  'Number of gate bags calculated by the BRS';
COMMENT ON COLUMN HBMD.GATE_BAGS_BRS_EDITED IS  'Number of gate bags edited';
COMMENT ON COLUMN HBMD.GATE_BAGS_MANUAL IS  'Number of gate bags';
COMMENT ON COLUMN HBMD.MAIL_BAGS_BRS IS  'Number of mail bags calculated by the BRS';
COMMENT ON COLUMN HBMD.MAIL_BAGS_BRS_EDITED IS  'Number of mail bags edited';
COMMENT ON COLUMN HBMD.MAIL_BAGS_MANUAL IS  'Number of mail bags';
COMMENT ON COLUMN HBMD.OTHER_BAGS_BRS IS  'Number of other bags calculated by the BRS';
COMMENT ON COLUMN HBMD.OTHER_BAGS_BRS_EDITED IS  'Number of other bags edited.';
COMMENT ON COLUMN HBMD.OTHER_BAGS_MANUAL IS  'Number of other bags';
COMMENT ON COLUMN HBMD.RUSH_BAGS_BRS IS  'Number of rush bags calculated by the BRS';
COMMENT ON COLUMN HBMD.RUSH_BAGS_BRS_EDITED IS  'Number of rush bags edited';
COMMENT ON COLUMN HBMD.RUSH_BAGS_MANUAL IS  'Number of rush bags';
COMMENT ON COLUMN HBMD.STATUS IS  'HBMD status';
COMMENT ON COLUMN HBMD.SURNAME IS  'The last name of the person authorising the HBMD, or requesting it printed for a manual authorisation';
COMMENT ON COLUMN HBMD.USER_ID IS  'The identifier of the user performing the authorisation';
COMMENT ON COLUMN HBMD.VERSION IS  'The issue of the current HBMD for the flight';
COMMENT ON COLUMN HBMD.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN HBMD.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN HBMD.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN HBMD.CUSER IS 'The user who created the row';
COMMENT ON COLUMN HBMD.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN HBMD.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN HBMD.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN HBMD.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN HBMD.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';

