/*****************************************************************************
*
* Filename         - $Workfile:   LOG_RULES.sql  $
*
* Author           - Dave Banks
*
* Project Name     - UltraTrak Core 
*
* Source Code
* Identifier       - Database (Master Standby) 
*
* Design Spec
* Section          - 
*
* Contents         - Database script to create table LOG_RULES
*
* Language         - Oracle SQL script      
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

PROMPT Creating Table 'LOG_RULES'
CREATE TABLE LOG_RULES
 (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
 ,LOG_NUMBER NUMBER(6) NOT NULL
 ,TABLE_NAME VARCHAR2(30) NOT NULL
 ,DESTINATION VARCHAR2(30) NOT NULL
 ,DELETE_NUMBER NUMBER(6) DEFAULT 0 NOT NULL
 ,PRIORITY NUMBER(1) NOT NULL
 ,SET_TO_BE_DELETED VARCHAR2(1)
 ,COLOUR NUMBER(18)
 ,PARAMETER_CHECK VARCHAR2(4)
 ,PARAMETER_VALUE VARCHAR2(255)
 )
 PCTUSED 90
 PCTFREE 5
 STORAGE
 (
   INITIAL 1056768
   NEXT 1056768
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_TABLES
/


PROMPT Creating Primary Key on 'LOG_RULES'
ALTER TABLE LOG_RULES
 ADD (CONSTRAINT LOG_RULES_PK PRIMARY KEY
  (APPN_ROW_ID)
 USING INDEX
 PCTFREE 5
 STORAGE
 (
   INITIAL 147456
   NEXT 147456
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_IDXS)
/

PROMPT Creating Check Constraint on 'LOG_RULES'
ALTER TABLE LOG_RULES
 ADD (CONSTRAINT LOG_RULES_DELETE_NO CHECK (DELETE_NUMBER >= 0))
/

PROMPT Creating Check Constraint on 'LOG_RULES'
ALTER TABLE LOG_RULES
 ADD (CONSTRAINT LOG_RULES_PRIORITY CHECK (PRIORITY >=1 AND PRIORITY <= 5))
/

PROMPT Creating Check Constraint on 'LOG_RULES'
ALTER TABLE LOG_RULES
 ADD (CONSTRAINT LOG_RULES_LOG_NO CHECK (LOG_NUMBER >= 0))
/

PROMPT Creating Check Constraint on 'LOG_RULES'
ALTER TABLE LOG_RULES
 ADD (CONSTRAINT LOG_RULES_SET_TO_BE_DEL CHECK (SET_TO_BE_DELETED = 'Y'))
/

PROMPT Creating Index 'LOG_RULES_IDX1'
CREATE INDEX LOG_RULES_IDX1 ON LOG_RULES
 (SYSTEM_ID
 ,LOG_NUMBER)
/


