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

CREATE TABLE ESI_TABLE_MAPPINGS
 (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) NOT NULL
 ,DEST_SYSTEM VARCHAR2(10) NOT NULL
 ,SOURCE_TABLE VARCHAR2(30) NOT NULL
 ,DEST_TABLE VARCHAR2(30) NOT NULL
 ,AMENDMENT_MODE VARCHAR2(1) NOT NULL
 ,DISTRIBUTION_RULE VARCHAR2(2000)
 ,RESYNC_ORDER NUMBER(4)
 ,LINK_TRIGGER_COLUMNS VARCHAR2(100)
 ,ORDER_BY_CLAUSE VARCHAR2(240)
 ,EXTERNAL_TABLE VARCHAR2(30)
 ,SOURCE_KEY_COLUMNS VARCHAR2(240)
 ,DEST_KEY_COLUMNS VARCHAR2(240)
 )
 PCTUSED 90
 PCTFREE 5
 STORAGE
 (
   INITIAL 20K
   NEXT 20K
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_TABLES
/

ALTER TABLE ESI_TABLE_MAPPINGS
 ADD (CONSTRAINT ESI_TABLE_MAPPINGS_PK PRIMARY KEY
  (APPN_ROW_ID)
 USING INDEX
 PCTFREE 5
 STORAGE
 (
   INITIAL 20K
   NEXT 20K
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_IDXS)
/

ALTER TABLE ESI_TABLE_MAPPINGS
 ADD (CONSTRAINT ESI_TABLE_MAPPINGS_UK1 UNIQUE
  (SYSTEM_ID
  ,DEST_SYSTEM
  ,SOURCE_TABLE
  ,DEST_TABLE)
 USING INDEX
 PCTFREE 5
 STORAGE
 (
   INITIAL 20K
   NEXT 20K
   PCTINCREASE 0
 ) TABLESPACE APPN_GENERAL_IDXS)
/

ALTER TABLE ESI_TABLE_MAPPINGS
 ADD (CONSTRAINT ESI_TABLE_MAPPINGS_AMEND_MODE CHECK (AMENDMENT_MODE IN ('A','D','U','I')))
/


