/*****************************************************************************
*
* Filename         - $Workfile:   ARCHIVE_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_RULES
*
* Language         - Oracle SQL script      
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

CREATE TABLE ARCHIVE_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,
    TABLE_NAME VARCHAR2(30) NOT NULL ENABLE,
    ARCHIVE_PROCEDURE VARCHAR2(1) NOT NULL ENABLE,
    ARCHIVE_CLAUSE VARCHAR2(4000) NOT NULL ENABLE,
    ARCHIVE_SCHEMA VARCHAR2(30),
    ARCHIVE_TABLE VARCHAR2(30),
    ARCHIVE_SPECIAL VARCHAR2(4000),
    ARCHIVE_SEQUENCE NUMBER(18,0),
    ARCHIVE_FREQUENCY VARCHAR2(1) DEFAULT 'D',
    ARCHIVE_DAY NUMBER(4,0),
    ARCHIVE_STATUS VARCHAR2(512),
    LAST_ARCHIVED DATE
)
 STORAGE
 (
   INITIAL 1560576
   NEXT 1560576
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_TABLES
/

ALTER TABLE ARCHIVE_RULES
 ADD (CONSTRAINT ARCHIVE_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_RULES
 ADD (CONSTRAINT ARCHIVE_RULES_UK1 UNIQUE
  (TABLE_NAME
  ,SYSTEM_ID
  ,ARCHIVE_SEQUENCE)
 USING INDEX
 PCTFREE 5
 STORAGE
 (
   INITIAL 86016
   NEXT 86016
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_IDXS)
/

ALTER TABLE ARCHIVE_RULES
 ADD (CONSTRAINT ARCHIVE_RULES_ARCH_SEQ CHECK (ARCHIVE_SEQUENCE >= 0))
/

ALTER TABLE ARCHIVE_RULES
 ADD (CONSTRAINT ARCHIVE_RULES_ARCH_FREQ2 CHECK ((ARCHIVE_FREQUENCY='M' AND ARCHIVE_DAY >=1 AND ARCHIVE_DAY <=31) OR
(ARCHIVE_FREQUENCY='W' AND ARCHIVE_DAY >=0 AND ARCHIVE_DAY <=6) OR
ARCHIVE_FREQUENCY='D'))
/

ALTER TABLE ARCHIVE_RULES
 ADD (CONSTRAINT ARCHIVE_RULES_ARCH_PROC2 CHECK ((ARCHIVE_PROCEDURE IN ('A','C') AND ARCHIVE_TABLE IS NOT NULL) OR
(ARCHIVE_PROCEDURE NOT IN ('A','C') AND ARCHIVE_TABLE IS NULL)))
/

ALTER TABLE ARCHIVE_RULES
 ADD (CONSTRAINT ARCHIVE_RULES_ARCH_FREQ1 CHECK (ARCHIVE_FREQUENCY IN ('D','W','M','Y')))
/

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


