PROMPT Creating Snapshot Logs for Master Tables

--DROP SNAPSHOT LOG ON DEVICES;
CREATE SNAPSHOT LOG
  ON DEVICES
WITH PRIMARY KEY
EXCLUDING NEW VALUES
/

--DROP SNAPSHOT LOG ON TEMPL_RECORDS;
CREATE SNAPSHOT LOG
  ON TEMPL_RECORDS
WITH PRIMARY KEY
EXCLUDING NEW VALUES
/

--DROP SNAPSHOT LOG ON TEMPL_FIELDS;
CREATE SNAPSHOT LOG
  ON TEMPL_FIELDS
WITH PRIMARY KEY
EXCLUDING NEW VALUES
/

commit;

PROMPT Creating Materialized View 'MV_RECORDS'
--DROP MATERIALIZED VIEW MV_RECORDS;

CREATE MATERIALIZED VIEW MV_RECORDS
BUILD IMMEDIATE
REFRESH FORCE
START WITH to_date('23-Apr-2004 01:00:00 AM','dd-Mon-yyyy HH:MI:SS AM')
NEXT /*6:Hrs*/ SYSDATE + 6/24
AS
SELECT
   substr(substitute(T.DFLT_NAME,D.ID), 1, 80) RECORD_NAME
  ,T.RECORD_TYPE RECORD_TYPE
  ,T.ALARM_ID ALARM_ID
  ,D.ID DEVICE_ID
  ,D.TEMPL_NAME TEMPL_NAME
  ,D.NODE_ID NODE_ID
 FROM TEMPL_RECORDS T, DEVICES D
 WHERE
   D.DEV_TYPE_ID = T.DEV_TYPE AND
   D.TEMPL_NAME = T.TEMPL_NAME
/

PROMPT Creating Index 'MV_REC_I'
CREATE INDEX MV_REC_I ON MV_RECORDS
 (RECORD_NAME)
/
-- 
PROMPT Creating Materialized View 'MV_DEV_INSTANCES'
--DROP MATERIALIZED VIEW MV_DEV_INSTANCES;

CREATE MATERIALIZED VIEW MV_DEV_INSTANCES 
BUILD IMMEDIATE
REFRESH FORCE
START WITH to_date('23-Apr-2004 03:00:00 AM','dd-Mon-yyyy HH:MI:SS AM')
NEXT /*1:Days*/ SYSDATE + 1
AS
SELECT 
  D.ID DEVICE_ID
 ,substr(substitute(TR.DFLT_NAME,D.ID), 1, 80) RECORD_NAME
 ,TR.RECORD_TYPE RECORD_TYPE
 ,TR.ALARM_ID ALARM_ID
 ,TF.FIELD_NAME FIELD_NAME
 ,substr(substitute(TF.FIELD_VALUE,D.ID), 1, 80) FIELD_VALUE
 ,TF.DFLT_ORDER DFLT_ORDER
FROM DEVICES D, TEMPL_RECORDS TR, TEMPL_FIELDS TF
WHERE
  D.DEV_TYPE_ID = TR.DEV_TYPE AND
  D.TEMPL_NAME = TR.TEMPL_NAME AND
  TR.TEMPL_NAME = TF.TEMPL_NAME AND
  TR.DFLT_NAME = TF.DFLT_NAME AND
  TR.RECORD_TYPE = TF.RECORD_TYPE
/

PROMPT Creating Index 'MV_DEV_INS_I'
CREATE INDEX MV_DEV_INS_I ON MV_DEV_INSTANCES
 (DEVICE_ID,RECORD_NAME)
/
