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

CREATE TABLE ARCHIVE_SUB_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,0) NOT NULL ENABLE,
    SYSTEM_ID NUMBER(4,0) DEFAULT 0 NOT NULL ENABLE,
    ARCHIVE_SEQUENCE NUMBER(18) NOT NULL,
    ARCHIVE_TABLE VARCHAR2(30) NOT NULL,
    ARCHIVE_SUB_TABLE VARCHAR2(30) NOT NULL,
    ARCHIVE_SUB_COLUMN VARCHAR2(100) NOT NULL,
    ARCHIVE_DEST_TABLE VARCHAR2(61) NOT NULL,
    ARCHIVE_SUB_QUERY VARCHAR2(2000),
    ARCHIVE_PROCEDURE VARCHAR2(1) NOT NULL,
    ARCHIVE_STATUS VARCHAR2(512),
    LAST_ARCHIVED DATE
)
STORAGE
(
   INITIAL 1560576
   NEXT 1560576
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_TABLES
/

ALTER TABLE ARCHIVE_SUB_RULES
 ADD (CONSTRAINT ARCHIVE_SUB_RULES_PK PRIMARY KEY
  (APPN_ROW_ID)
 USING INDEX
 PCTFREE 5
 STORAGE
 (
   INITIAL 61440
   NEXT 61440
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_IDXS)
/

ALTER TABLE ARCHIVE_SUB_RULES
 ADD (CONSTRAINT ARCHIVE_SUB_RULES_UK1 UNIQUE
  (SYSTEM_ID
  ,ARCHIVE_SEQUENCE)
 USING INDEX
 PCTFREE 5
 STORAGE
 (
   INITIAL 86016
   NEXT 86016
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_IDXS)
/

ALTER TABLE ARCHIVE_SUB_RULES ADD CONSTRAINT ARCHIVE_SUB_RULES_ARCH_PROC1 
      CHECK (ARCHIVE_PROCEDURE IN ('A','E','D','N','C','S'))
/

