/*****************************************************************************
*
* Filename         - $Workfile$
*
* Author           - Michael Clark
* Last Update      - $Date$
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          -
*
* Contents         - Live Schema: missing_bag_alert table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

PROMPT Creating Table 'missing_bag_alert'
CREATE TABLE missing_bag_alert
 (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
 ,BAG_ID NUMBER(10) NOT NULL
 ,SITE_ID NUMBER(10) NOT NULL
 ,ACKNOWLEDGED CHAR(1) DEFAULT 'N' NOT NULL
 ,ACKNOWLEDGED_BY NUMBER(10)
 ,ACKNOWLEDGED_WHERE CHAR(6)
 ,ACKNOWLEDGED_WHEN DATE
 ,CLEARED CHAR(1) DEFAULT 'N' NOT NULL
 );

ALTER TABLE missing_bag_alert ADD (CONSTRAINT missing_bag_alert_pk1 PRIMARY KEY (appn_row_id));
ALTER TABLE missing_bag_alert ADD (CONSTRAINT missing_bag_alert_uk1 UNIQUE (bag_id));
ALTER TABLE missing_bag_alert ADD (CONSTRAINT missing_bag_alert_fk1_bag FOREIGN KEY (bag_id) REFERENCES bag (bag_id));


COMMENT ON TABLE missing_bag_alert IS 'List of missing bags as defined by rules in table MISSING_BAG_RULE.';

COMMENT ON COLUMN missing_bag_alert.bag_id IS 'The ID of the missing bag.';
COMMENT ON COLUMN missing_bag_alert.site_id IS 'The site where this bag is missing.';
COMMENT ON COLUMN missing_bag_alert.acknowledged IS 'Whether this alert has been manually acknowledged. Constrained to Y or N.';
COMMENT ON COLUMN missing_bag_alert.acknowledged_by IS 'If this bag has been manually acknowledged, this is the user ID of the person who acknowledged it.';
COMMENT ON COLUMN missing_bag_alert.acknowledged_where IS 'If this bag has been manually acknowledged, this is the location of the preson who acknowledged it.';
COMMENT ON COLUMN missing_bag_alert.acknowledged_when IS 'If this bag has been manually acknowledged, this was the date and the time that the bag was acknowledged.';
COMMENT ON COLUMN missing_bag_alert.cleared IS 'If a bag goes missing and is then loaded, this flag is used to indicate the fact and remove the alert if it is still active. Constrained to Y or N';
COMMENT ON COLUMN missing_bag_alert.appn_row_id IS 'Unique sequence number for this row';
COMMENT ON COLUMN missing_bag_alert.row_create_time IS 'The time the row was created';
COMMENT ON COLUMN missing_bag_alert.row_date_time IS 'The time the row was last modified';
COMMENT ON COLUMN missing_bag_alert.cuser IS 'The user who created the row';
COMMENT ON COLUMN missing_bag_alert.muser IS 'The user who last modified the row';
COMMENT ON COLUMN missing_bag_alert.mterm IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN missing_bag_alert.system_id IS 'The system identifier';
COMMENT ON COLUMN missing_bag_alert.source IS 'The process that last amended this row ';
COMMENT ON COLUMN missing_bag_alert.to_be_archived IS 'Indicates that this row is to be archived or has been archived';