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

PROMPT Creating Table 'MISSING_BAG_RULE'
CREATE TABLE MISSING_BAG_RULE
 (SITE_ID NUMBER(10) NOT NULL
 ,TIMING_GROUP_1 VARCHAR2(10)
 ,TIMING_GROUP_2 VARCHAR2(10)
 ,TIME_PERIOD NUMBER(4) NOT NULL
 ,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 missing_bag_rule ADD (CONSTRAINT missing_bag_rule_pk1 PRIMARY KEY (appn_row_id));
ALTER TABLE missing_bag_rule ADD (CONSTRAINT missing_bag_rule_uk1 UNIQUE (site_id, timing_group_1, timing_group_2));
ALTER TABLE missing_bag_rule ADD (CONSTRAINT missing_bag_rule_fk1_loc_group FOREIGN KEY (site_id, timing_group_1) REFERENCES location_group (site_id, name));
ALTER TABLE missing_bag_rule ADD (CONSTRAINT missing_bag_rule_fk2_loc_group FOREIGN KEY (site_id, timing_group_2) REFERENCES location_group (site_id, name));
ALTER TABLE missing_bag_rule ADD (CONSTRAINT missing_bag_rule_ck1 CHECK (timing_group_1 != timing_group_2));
ALTER TABLE missing_bag_rule ADD (CONSTRAINT missing_bag_rule_ck2 CHECK (time_period > 0));


COMMENT ON TABLE missing_bag_rule IS 'Rules defining when a bag is classified as missing.';

COMMENT ON COLUMN missing_bag_rule.site_id IS 'The site for which this check is defined.';
COMMENT ON COLUMN missing_bag_rule.timing_group_1 IS 'The first timing location group for this rule.';
COMMENT ON COLUMN missing_bag_rule.timing_group_2 IS 'The second timing location group for this rule.';
COMMENT ON COLUMN missing_bag_rule.time_period IS 'The period in minutes between these timing points.';
COMMENT ON COLUMN missing_bag_rule.appn_row_id IS 'Unique sequence number for this row';
COMMENT ON COLUMN missing_bag_rule.row_create_time IS 'The time the row was created';
COMMENT ON COLUMN missing_bag_rule.row_date_time IS 'The time the row was last modified';
COMMENT ON COLUMN missing_bag_rule.cuser IS 'The user who created the row';
COMMENT ON COLUMN missing_bag_rule.muser IS 'The user who last modified the row';
COMMENT ON COLUMN missing_bag_rule.mterm IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN missing_bag_rule.system_id IS 'The system identifier';
COMMENT ON COLUMN missing_bag_rule.source IS 'The process that last amended this row ';
COMMENT ON COLUMN missing_bag_rule.to_be_archived IS 'Indicates that this row is to be archived or has been archived';
