/*
==============================================================
Database name:  DW_T1   
Schema Name:    WFSDW   
Version: @version $Id: createWFS_DW.sql,v 1.11 2009/11/02 19:09:02 mhabib Exp $                              
DBMS name:      ORACLE Version 10.1 or 10.2                       
Script purpose: Create the WFSDW user and all its
		corresponding objects: sequences,
		tables, procedures, and constraints			
Created by: Chris Scherlacher 08/16/2007
Updated by: Murtaza Habib 11/07/2008. 
            Increased column width of WFSDW.DIM_TEMPLATE.NAME varchar2(400)
            Increased column width of WFSDW.STG1_DIFF_TEMPLATE varchar2(400)

NOTE:		Must be logged on as sys user    
==============================================================
*/

CREATE OR REPLACE PACKAGE SYS.createWFSDWDB AS
	PROCEDURE createWFS_DW;
  vschema varchar2(100) := 'WFSDW';

END;
/

CREATE OR REPLACE PACKAGE BODY SYS.createWFSDWDB AS

/* Procedure: CreateObject. Executes a dynamic SQL statement and logs error in install_table*/
  PROCEDURE CREATEOBJECT(vsql varchar2,errmsg varchar2, mask varchar2 default 'N') IS
    verrmsg varchar2(32000);
  BEGIN
    execute immediate vsql;
  exception
    when others then
          verrmsg := substr(errmsg ||' '||sqlerrm,1,4000);
          --dbms_output.put_line(vsql);
      if upper(mask) != 'Y' then
          execute immediate 'INSERT INTO install_table values('''||sysdate||''','''||replace(verrmsg,'''')||''')';
          commit;
          raise;
      end if;
  END CREATEOBJECT;

PROCEDURE createWFS_DW IS
  /*  
          Created By:  	Chris Scherlacher 08/16/2007
          Create Reason: Create WFSDW tables, sequences,triggers,views,etc
                          
          Note:		When run, this procedure will log any error message into the 
                          install_table and exit.
  */
  
          vsql varchar2(32000);
          wk_database_path	varchar2(100);
          wk_message		varchar2(500);
          wk_count		number := 0;

BEGIN
  
  /* 
  ------------------------------------
      PREP WORK
  ------------------------------------
  */
	wk_message := '';
	-- Drop the install_table
    vsql:='Drop table sys.install_table';
		CREATEOBJECT(vsql,'DROP INSTALL_TABLE','Y');
      
    
    -- Create Install_table
    vsql:='CREATE TABLE sys.install_table (msgdate date default sysdate, install_message varchar2(4000))';
		CREATEOBJECT(vsql,'CREATE SYS.INSTALL_TABLE');
  /* 
  ------------------------------------
     Create Database Link
  ------------------------------------
  */

	-- Drop the link if it already exists
    vsql:='drop public database link WFS_PRODUCTIONDB';
		CREATEOBJECT(vsql,'drop database link','Y');

	vsql:='Create public database link WFS_PRODUCTIONDB connect to WFS identified by "Wf$1234" using ''WFS_PRODUCTIONDB''';
	CREATEOBJECT(vsql,'CREATE DATABASE LINK');
	
  /* 
  ------------------------------------
      CLEANUP USER AND ROLES
  ------------------------------------
  */
    vsql:='drop user reportreader cascade';
		CREATEOBJECT(vsql,'drop report reader','Y');

    vsql:='drop user wfsdw cascade';
		CREATEOBJECT(vsql,'drop wfsdw','Y');

    vsql:='drop role datawriterrole';
		CREATEOBJECT(vsql,'drop datawriterrole','Y');

    vsql:='drop role datareaderrole';
		CREATEOBJECT(vsql,'drop datareaderrole','Y');

  /* 
  ------------------------------------
      CREATE TABLESPACES
  ------------------------------------
  */
  
      -- Drop WFSDW_DATA tablespace
      vsql :='drop tablespace WFSDW_DATA including contents and datafiles';
			CREATEOBJECT(vsql,'DROP TABLESPACE WFSDW_DATA','Y');

      -- Creating WFSDW_DATA tablespace
      vsql :='create tablespace WFSDW_DATA datafile size 500M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO';
			CREATEOBJECT(vsql,'CREATE TABLESPACE WFSDW_DATA');
			
	  -- Drop WFSDW_INDEX tablespace
      vsql :='drop tablespace WFSDW_INDEX including contents and datafiles';
			CREATEOBJECT(vsql,'DROP TABLESPACE WFSDW_INDEX','Y');

      -- Creating WFSDW_DATA tablespace
      vsql :='create tablespace WFSDW_INDEX datafile size 250M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO';
			CREATEOBJECT(vsql,'CREATE TABLESPACE WFSDW_INDEX');

  /* 
  ------------------------------------
      CREATE USERS and ROLES
  ------------------------------------
  */
  
  /* ------ DATAREADERROLE Role ----- */

  --- Create the DATAREADERROLE

  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE ROLE DATAREADERROLE NOT IDENTIFIED';
    
  EXCEPTION
    WHEN OTHERS THEN
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:  
          Unable to create the DATAREADERROLE.'')';
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT CONNECT TO DATAREADERROLE';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
          Unable to grant CONNECT to the DATAREADERROLE role.'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO DATAREADERROLE';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant SESSION to the DATAREADERROLE.'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT SELECT ANY TABLE TO DATAREADERROLE';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant SELECT ANY TABLE to the DATAREADERROLE'')';
      GOTO bypass_install;
      
  END;
  
  --- Create the DATAWRITERROLE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE ROLE DATAWRITERROLE NOT IDENTIFIED';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the DATAWRITERROLE role'')';
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT CONNECT TO DATAWRITERROLE';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant CONNECT to the DATAWRITERROLE role'')';
      GOTO bypass_install;
      
  END;
  
  /* ---------- WFSDW User ---------- */

  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE USER WFSDW IDENTIFIED BY "wfsdw"
      DEFAULT TABLESPACE WFSDW_DATA
      TEMPORARY TABLESPACE temp
      PROFILE DEFAULT';
                         
  EXCEPTION 
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create user WFSDW'')';
      GOTO bypass_install;
      
  END;
  
  BEGIN
  
    EXECUTE IMMEDIATE 'grant DataReaderRole, DataWriterRole to WFSDW';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant DataReaderRole and DataWriterRole to the WFSDW user'')';
      GOTO bypass_install;
      
  END;
  
  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT DBA TO WFSDW';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant DBA to the WFSDW user'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT CREATE ANY TABLE TO WFSDW';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant CREATE ANY TABLE to WFSDW'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT CREATE ANY SEQUENCE TO WFSDW';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant CREATE ANY SEQUENCE to the WFSDW user'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO WFSDW';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant UNLIMITED TABLESPACE to the WFSDW user'')';
      GOTO bypass_install;
      
    END;
    
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER USER WFSDW
      QUOTA UNLIMITED ON WFSDW_DATA
      QUOTA UNLIMITED ON WFSDW_INDEX';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant QUOTA UNLIMITED ON WFSDW_DATA and WFSDW_INDEX to the WFSDW user'')';
      GOTO bypass_install;
      
    END;

  -- Create ReportReader User
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE USER REPORTREADER IDENTIFIED BY "reports4me"
      DEFAULT TABLESPACE WFSDW_DATA
      TEMPORARY TABLESPACE TEMP
      PROFILE DEFAULT';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the REPORTREADER user'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT DATAREADERROLE, DATAWRITERROLE TO REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant DATAREADERROLE and DATAWRITERROLE to the REPORTREADER user'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant CREATE PROCEDURE to the REPORTREADER user'')';
      GOTO bypass_install;
      
  END;

  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER USER REPORTREADER
      QUOTA UNLIMITED ON WFSDW_DATA
      QUOTA UNLIMITED ON WFSDW_INDEX';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant QUOTA UNLIMITED on WFSDW_DATA and WFSDW_INDEX to the REPORTREADER user'')';
      GOTO bypass_install;
      
  END;
  
/*
--------------------------------------------------------------------------------
            Create the Metadata database objects and data 
--------------------------------------------------------------------------------
*/

  -- Create the MD_EVENTLOG table 
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_EVENTLOG (
      EVENTLOGID          NUMBER NOT NULL,
      PROCESSNAME         VARCHAR2(100) NOT NULL,
      SECTION             VARCHAR2(500) NULL,
      ERRORCODE           NUMBER NOT NULL,
      ERRORMESSAGE        VARCHAR2(2000) NOT NULL,
      CREATEDATE          TIMESTAMP(6) NOT NULL,
      CONSTRAINT PK_MD_EVENTLOG PRIMARY KEY(EVENTLOGID)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_EVENTLOG ON WFSDW.MD_EVENTLOG(EVENTLOGID)
                     TABLESPACE WFSDW_INDEX)
                  )
      TABLESPACE WFSDW_DATA';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the MD_EVENTLOG table'')';
      GOTO bypass_install;
      
  END;
  
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.MD_EVENTLOG_SEQ
      INCREMENT BY 1
      START WITH 45
      NOCYCLE';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the MD_EVENTLOG_SEQ sequence'')';
      GOTO bypass_install;
      
  END;
  
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER WFSDW.TRG_MD_EVENTLOG_SEQ
      BEFORE INSERT ON WFSDW.MD_EVENTLOG
      FOR EACH ROW
      BEGIN
          IF :NEW.EventLogId IS NULL THEN
          
            SELECT WFSDW.MD_EVENTLOG_SEQ.NEXTVAL INTO :NEW.EventLogId FROM DUAL;

          END IF;
      END;';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the TRG_MD_EVENTLOG_SEQ'')';
      GOTO bypass_install;
      
  END;

  -- Create the MD_ETLPROCESSROLE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_ETLPROCESSROLE (
      ETLPROCESSROLEKEY   NUMBER NOT NULL,
      DESCRIPTION         VARCHAR2(50) NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      CONSTRAINT PK_MD_ETLPROCESSROLE PRIMARY KEY(ETLPROCESSROLEKEY)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_ETLPROCESSROLE ON WFSDW.MD_ETLPROCESSROLE(ETLPROCESSROLEKEY)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the MD_ETLPROCESSROLE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create the MD_ETLPROCESSSTATUS table
  BEGIN

    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_ETLPROCESSSTATUS (
      ETLPROCESSSTATUSKEY NUMBER NOT NULL,
      DESCRIPTION         VARCHAR2(30) NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      CONSTRAINT PK_MD_ETLPROCESSSTATUS PRIMARY KEY(ETLPROCESSSTATUSKEY)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_ETLPROCESSSTATUS ON WFSDW.MD_ETLPROCESSSTATUS(ETLPROCESSSTATUSKEY)
                     TABLESPACE WFSDW_INDEX)
      )
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create MD_ETLPROCESSSTATUS table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create the MD_ETLPROCESSTYPE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_ETLPROCESSTYPE (
      ETLPROCESSTYPEKEY   NUMBER NOT NULL,
      DESCRIPTION         VARCHAR2(50) NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      CONSTRAINT PK_MD_ETLPROCESSTYPE PRIMARY KEY(ETLPROCESSTYPEKEY)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_ETLPROCESSTYPE ON WFSDW.MD_ETLPROCESSTYPE(ETLPROCESSTYPEKEY)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the MD_ETLPROCESSTYPE table'')';
      GOTO bypass_install;
      
  END;
  
  --Create the MD_LOCKABLEOBJECTSTATUS table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_LOCKABLEOBJECTSTATUS (
      LOCKABLEOBJECTSTATUSKEY     NUMBER NOT NULL,
      DESCRIPTION                 VARCHAR2(30) NOT NULL,
      CREATEDATE                  TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      CONSTRAINT PK_MD_LOCKABLEOBJECTSTATUS PRIMARY KEY(LOCKABLEOBJECTSTATUSKEY)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_LOCKABLEOBJECTSTATUS ON WFSDW.MD_LOCKABLEOBJECTSTATUS(LOCKABLEOBJECTSTATUSKEY)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the MD_LOCKABLEOBJECTSTATUS table'')';
      GOTO bypass_install;
      
  END;

  -- Create MD_LOCKABLEOBJECTTYPE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_LOCKABLEOBJECTTYPE (
      LOCKABLEOBJECTTYPEKEY       NUMBER NOT NULL,
      DESCRIPTION                 VARCHAR2(50) NOT NULL,
      CREATEDATE                  TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      CONSTRAINT PK_MD_LOCKABLEOBJECTTYPE PRIMARY KEY(LOCKABLEOBJECTTYPEKEY)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_LOCKABLEOBJECTTYPE ON WFSDW.MD_LOCKABLEOBJECTTYPE(LOCKABLEOBJECTTYPEKEY)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create the MD_LOCKABLEOBJECTTYPE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create the MD_LOCKABLEOBJECT table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_LOCKABLEOBJECT (
      LOCKABLEOBJECTKEY   NUMBER NOT NULL,
      LOCKABLEOBJECTTYPE  NUMBER NOT NULL,
      NAME                VARCHAR2(50) NOT NULL,
      DESCRIPTION         VARCHAR2(100) NOT NULL,
      LASTOBJECTSTATUS    NUMBER NULL,
      LASTSYNCDATE        TIMESTAMP(6) DEFAULT to_date(''1974/10/12'', ''yyyy/mm/dd'') NULL,
      LASTETLPROCESS      NUMBER NULL,
      LASTRUNINSTANCE     NUMBER NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      UPDATEDATE          TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      CONSTRAINT PK_MD_LOCKABLEOBJECT PRIMARY KEY(LOCKABLEOBJECTKEY)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_LOCKABLEOBJECT ON WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create MD_LOCKABLEOBJECT table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create the MD_ETLPROCESS table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_ETLPROCESS (
      ETLPROCESSKEY               NUMBER NOT NULL,
      NAME                        VARCHAR2(50) NOT NULL,
      DESCRIPTION                 VARCHAR2(100) NULL,
      ETLPROCESSTYPE              NUMBER NOT NULL,
      ETLPROCESSROLE              NUMBER NOT NULL,
      LOCKABLEOBJECTLOADED        NUMBER NULL,
      SENDEMAILONSUCCESS          NUMBER(1,0) DEFAULT 0 NOT NULL,
      SENDEMAILONERROR            NUMBER(1,0) DEFAULT 0 NOT NULL,
      EMAILRECIPIENTS             VARCHAR2(255) NULL,
      CREATEDATE                  TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      UPDATEDATE                  TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NULL,
      CONSTRAINT PK_MD_ETLPROCESS PRIMARY KEY(ETLPROCESSKEY)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_ETLPROCESS ON WFSDW.MD_ETLPROCESS(ETLPROCESSKEY)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create MD_ETLPROCESS table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create the MD_ETLPROCESSRUNINSTANCE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_ETLPROCESSRUNINSTANCE (
      ETLPROCESS                  NUMBER NOT NULL,
      RUNINSTANCE                 NUMBER NOT NULL,
      PARENTETLPROCESS            NUMBER NULL,
      PARENTRUNINSTANCE           NUMBER NULL,
      STATUS                      NUMBER NOT NULL,
      COMMENTS                    VARCHAR2(500) NULL,
      LOCKABLEOBJECTLOADED        NUMBER NULL,
      LOCKABLEOBJECTSTATUS        NUMBER NULL,
      PREVIOUSSYNCDATE            TIMESTAMP(6) NULL,
      NEWSYNCDATE                 TIMESTAMP(6) NULL,
      AFFECTEDROWS                NUMBER(19,0) NULL,
      SYSERRORCODE                NUMBER NULL,
      DBPROCESSID                 NUMBER NULL,
      STARTDATE                   TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      ENDDATE                     TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NULL,
      CONSTRAINT PK_MD_ETLPROCESSRUNINSTANCE PRIMARY KEY(ETLPROCESS,RUNINSTANCE)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_ETLPROCESSRUNINSTANCE ON WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS,RUNINSTANCE)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create MD_ETLPROCESSRUNINSTANCE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create the MD_ETLPROCESSMESSAGE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.MD_ETLPROCESSMESSAGE (
      ETLPROCESS          NUMBER NOT NULL,
      RUNINSTANCE         NUMBER NOT NULL,
      MESSAGENUMBER       NUMBER NOT NULL,
      MESSAGETYPE         VARCHAR2(5) NOT NULL,
      MESSAGE             VARCHAR2(4000) NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (SYSTIMESTAMP) NOT NULL,
      CONSTRAINT PK_MD_ETLPROCESSMESSAGE PRIMARY KEY(ETLPROCESS,RUNINSTANCE,MESSAGENUMBER)
        USING INDEX (CREATE INDEX WFSDW.PK_MD_ETLPROCESSMESSAGE ON WFSDW.MD_ETLPROCESSMESSAGE(ETLPROCESS,RUNINSTANCE,MESSAGENUMBER)
                     TABLESPACE WFSDW_INDEX)
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create MD_ETLPROCESSMESSAGE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Creating Table 'SYSTEM_SETTINGS'
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.SYSTEM_SETTINGS(
                        SETTING_TYPE       varchar2(25 char),
                        SETTING_VALUE      varchar2(1024 char))
                       TABLESPACE WFSDW_DATA';
    EXCEPTION 
       WHEN OTHERS THEN
       wk_message := 'ERROR';
           EXECUTE IMMEDIATE 'INSERT INTO install_table values
        (sysdate, ''error in installing of data warehouse objects: 
           Unable to create table SYSTEM_SETTINGS'')';
           GOTO bypass_install;
           
  END;

  -- Insert MD_ETLPROCESSSTATUS Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSSTATUS(ETLPROCESSSTATUSKEY, DESCRIPTION, CREATEDATE)
      VALUES(0, ''Finished OK'', TO_DATE(''2006-08-28 02:12:31'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSSTATUS(ETLPROCESSSTATUSKEY, DESCRIPTION, CREATEDATE)
      VALUES(1, ''Running'', TO_DATE(''2006-08-22 16:51:49'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSSTATUS(ETLPROCESSSTATUSKEY, DESCRIPTION, CREATEDATE)
      VALUES(2, ''FAILED!'', TO_DATE(''2006-08-28 02:24:11'',''YYYY-MM-DD HH24:MI:SS''))';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert MD_ETLPROCESSSTATUS reference data'')';
      GOTO bypass_install;
      
  END;

  -- Insert MD_ETLPROCESSTYPE Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSTYPE(ETLPROCESSTYPEKEY, DESCRIPTION, CREATEDATE)
      VALUES(1, ''Stored Procedure'', TO_DATE(''2006-08-22 16:51:32'',''YYYY-MM-DD HH24:MI:SS''))';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert MD_ETLPROCESSTYPE reference data'')';
      GOTO bypass_install;
      
  END;

  --  Insert MD_ETLPROCESSROLE Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSROLE(ETLPROCESSROLEKEY, DESCRIPTION, CREATEDATE)
      VALUES(1, ''Loads ODS table'', TO_DATE(''2006-08-22 16:51:39'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSROLE(ETLPROCESSROLEKEY, DESCRIPTION, CREATEDATE)
      VALUES(2, ''Loads Fact table'', TO_DATE(''2006-08-23 19:46:25'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSROLE(ETLPROCESSROLEKEY, DESCRIPTION, CREATEDATE)
      VALUES(3, ''Loads Dimension table'', TO_DATE(''2006-08-28 02:29:58'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSROLE(ETLPROCESSROLEKEY, DESCRIPTION, CREATEDATE)
      VALUES(8, ''Auxiliary / Miscellaneous'', TO_DATE(''2006-09-11 22:37:14'',''YYYY-MM-DD HH24:MI:SS''))';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert MD_ETLPROCESSROLE reference data'')';
      GOTO bypass_install;
      
  END;

  --  Insert MD_LOCKABLEOBJECTSTATUS Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTSTATUS(LOCKABLEOBJECTSTATUSKEY, DESCRIPTION, CREATEDATE)
      VALUES(1, ''Loading'', TO_DATE(''2006-08-21 23:32:38'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTSTATUS(LOCKABLEOBJECTSTATUSKEY, DESCRIPTION, CREATEDATE)
      VALUES(0, ''Loaded OK'', TO_DATE(''2006-08-26 01:32:08'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTSTATUS(LOCKABLEOBJECTSTATUSKEY, DESCRIPTION, CREATEDATE)
      VALUES(2, ''FAILED!'', TO_DATE(''2006-08-29 23:44:51'',''YYYY-MM-DD HH24:MI:SS''))';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert MD_LOCKABLEOBJECTSTATUS table'')';
      GOTO bypass_install;
      
  END;

  --  Insert MD_LOCKABLEOBJECTYPE Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTTYPE(LOCKABLEOBJECTTYPEKEY, DESCRIPTION, CREATEDATE)
      VALUES(2, ''Fact Table'', TO_DATE(''2006-09-11 22:48:58'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTTYPE(LOCKABLEOBJECTTYPEKEY, DESCRIPTION, CREATEDATE)
      VALUES(3, ''Dimension Table'', TO_DATE(''2006-09-11 22:48:58'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTTYPE(LOCKABLEOBJECTTYPEKEY, DESCRIPTION, CREATEDATE)
      VALUES(4, ''Aggregate Table'', TO_DATE(''2006-09-11 22:48:58'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTTYPE(LOCKABLEOBJECTTYPEKEY, DESCRIPTION, CREATEDATE)
      VALUES(5, ''MD Table / Virtual lock'', TO_DATE(''2006-09-11 22:48:58'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECTTYPE(LOCKABLEOBJECTTYPEKEY, DESCRIPTION, CREATEDATE)
      VALUES(1, ''ODS Table'', TO_DATE(''2006-08-22 16:51:15'',''YYYY-MM-DD HH24:MI:SS''))';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert MD_LOCKABLEOBJECTTYPE reference data'')';
      GOTO bypass_install;
      
  END;

  -- Insert MD_ETLPROCESS Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(4, ''spSYNC_ResourceGroup'', ''Resource SYNC Process Group'', 1, 3, 4, 0, 0, NULL, 
        TO_DATE(''2006-09-30 03:48:14'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-09-30 03:48:14'',''YYYY-MM-DD HH24:MI:SS''))';
        
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
      LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(5, ''spSYNC_ProcessDefinition'', ''Process Definition (Template)'', 1, 3, 5, 0, 0, NULL, 
        TO_DATE(''2006-10-02 16:07:29'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-02 23:12:59'',''YYYY-MM-DD HH24:MI:SS''))';
        
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(1, ''spSYNC_WorkOrderStatus'', ''WorkOrderStatus SYNC Process'', 1, 3, 1, 0, 1, ''~TLADatabase@tandbergtv.com'', 
        TO_DATE(''2006-08-22 16:51:54'',''YYYY-MM-DDHH24:MI:SS''), TO_DATE(''2006-08-22 16:51:54'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(999999, ''Zero Load - Manual processes'', 
        ''Used to cover FK on LockableObject and dims to initialize objects that have never been loaded before'', 1, 8,
        NULL, 0, 0, NULL, TO_DATE(''2006-09-11 22:36:12'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-09-11 22:36:12'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(6, ''spSYNC_Node'', ''Node SYNC Process'', 1, 3, 6, 0, 0, NULL, TO_DATE(''2006-10-03 18:38:28'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-03 18:38:28'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(9, ''spSYNC_VariableInstance'', ''Variable Instance SYNC Process'', 1, 3, 9, 0, 0, '' '', 
        TO_DATE(''2006-10-05 23:47:26'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-05 23:47:26'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(3, ''spSYNC_Resource'', ''Resource SYNC Process'', 1, 3, 3, 0, 0, NULL, 
        TO_DATE(''2006-09-29 12:21:14'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-09-29 12:21:14'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(7, ''spSYNC_WorkOrder'', ''WorkOrder SYNC Process'', 1, 3, 7, 0, 0, NULL, 
        TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(10, ''spSYNC_Log'', ''Log SYNC Process'', 1, 3, 10, 0, 0, NULL, TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(11, ''spSYNC_MonitoredDrive'', ''MonitoredDrive SYNC Process'', 1, 3, 11, 0, 0, NULL, 
        TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESS(ETLPROCESSKEY, NAME, DESCRIPTION, ETLPROCESSTYPE, ETLPROCESSROLE, 
        LOCKABLEOBJECTLOADED, SENDEMAILONSUCCESS, SENDEMAILONERROR,EMAILRECIPIENTS, CREATEDATE, UPDATEDATE)
      VALUES(12, ''spSYNC_MonitoredDriveHistory'', ''MonitoredDriveHistory SYNC Process'', 1, 3, 12, 0, 0, NULL, 
        TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-04 18:47:15'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(200, ''spAGG_FactWorkOrderDay'', ''Aggregation of WorkOrder Fact Data to the Day'', 1, 8, 13, 0, 0, systimestamp, systimestamp)';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(201, ''spAGG_FactWorkOrderMonth'', ''Aggregation of WorkOrder Fact Data to the Month'', 1, 8, 14, 0, 0, systimestamp, systimestamp)';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(205, ''spAGG_FactResourceQueueDay'', ''Aggregation of Log Queue Fact Data to the Day'', 1, 8, 15, 0, 0, systimestamp, systimestamp)';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(206, ''spAGG_FactResourceQueueMonth'', ''Aggregation of Log Queue Fact Data to the Month'', 1, 8, 16, 0, 0, systimestamp, systimestamp)';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(210, ''spAGG_FactResourceTaskDay'', ''Aggregation of Log Task Fact Data to the Day'', 1, 8, 17, 0, 0, systimestamp, systimestamp)';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(211, ''spAGG_FactResourceTaskMonth'', ''Aggregation of Log Task Fact Data to the Month'', 1, 8, 18, 0, 0, systimestamp, systimestamp)';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(215, ''spAGG_FactMDHistoryDay'', ''Aggregation of MDHisotryDetail Fact Data to the Day'', 1, 8, 19, 0, 0, systimestamp, systimestamp)';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_ETLPROCESS"("ETLPROCESSKEY", "NAME", "DESCRIPTION", "ETLPROCESSTYPE", "ETLPROCESSROLE", 
        "LOCKABLEOBJECTLOADED", "SENDEMAILONSUCCESS","SENDEMAILONERROR", "CREATEDATE", "UPDATEDATE")
      VALUES(216, ''spAGG_FactMDHistoryMonth'', ''Aggregation of MDHistoryDetail Fact Data to the Month'', 1, 8, 20, 0, 0, systimestamp, systimestamp)';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert MD_ETLPROCESS reference data'')';
      GOTO bypass_install;
      
  END;      

  -- Insert MD_ETLPROCESSRUNINSTANCE Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE, PARENTETLPROCESS, PARENTRUNINSTANCE, STATUS, 
        COMMENTS, LOCKABLEOBJECTLOADED, LOCKABLEOBJECTSTATUS, PREVIOUSSYNCDATE, NEWSYNCDATE, AFFECTEDROWS, SYSERRORCODE, DBPROCESSID, 
        STARTDATE, ENDDATE)
      VALUES(999999, 1, NULL, NULL, 0, ''SEED - ZERO LOAD / INITIAL LOAD'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
        TO_DATE(''2006-09-11 22:41:12'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-09-11 22:41:12'',''YYYY-MM-DD HH24:MI:SS''))';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to MD_ETLPROCESSRUNINSTANCE table'')';
      GOTO bypass_install;
      
  END;
  
  --  Insert MD_LOCKABLEOBJECT Reference Data
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(4, 3, ''DIM_ResourceGroup'', ''Resource Group Dimension'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, 
        TO_DATE(''2006-09-29 03:48:14'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-03 19:50:45'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(5, 3, ''DIM_Template'', ''Template Dimension'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2006-10-02 23:09:16'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-06 18:14:13'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(6, 3, ''DIM_Node'', ''Node Dimension'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2006-10-03 18:37:12'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-06 18:20:55'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(1, 3, ''DIM_WorkOrderStatus'', ''Work Order Status Dimension'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, 
        TO_DATE(''2006-08-23 20:04:16'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-09-28 23:28:37'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(9, 3, ''FACT_VariableInstance'', ''Variable Instance Facts'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, 
        TO_DATE(''2006-10-05 23:45:45'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-06 18:26:50'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(3, 3, ''DIM_Resource'', ''Resource Dimension'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2006-09-29 12:21:14'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-04 19:29:12'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(7, 2, ''FACT_WorkOrder'', ''Work Order Facts'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2006-10-04 18:45:17'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-06 18:22:56'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(10, 2, ''FACT_Log'', ''Log Facts'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2006-10-04 18:45:17'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-06 18:22:56'',''YYYY-MM-DD HH24:MI:SS''))';
        
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(11, 3, ''DIM_Monitoreddrive'', ''Monitoreddrive Dimension'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2006-09-29 12:21:14'',''YYYY-MM-DD HH24:MI:SS''), 
        TO_DATE(''2006-10-04 19:29:12'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY, LOCKABLEOBJECTTYPE, NAME, DESCRIPTION, LASTOBJECTSTATUS, 
        LASTSYNCDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE)
      VALUES(12, 2, ''FACT_MonitoredDriveHistory'', ''MonitoredDriveHistory Facts'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, 
        TO_DATE(''2006-10-04 18:45:17'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2006-10-06 18:22:56'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(13, 4, ''FACT_WorkOrderDay'', ''Aggregated WorkOrders by Day'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(14, 4, ''FACT_WorkOrderMonth'', ''Aggregated WorkOrders by Month'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(15, 4, ''FACT_ResourceQueueDay'', ''Aggregated Log Queue Durations by Day'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(16, 4, ''FACT_ResourceQueueMonth'', ''Aggregated Log Queue Durations by Month'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(17, 4, ''FACT_ResourceTaskDay'', ''Aggregated Log Task Durations by Day'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(18, 4, ''FACT_ResourceTaskMonth'', ''Aggregated Log Task Durations by Month'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(19, 4, ''FACT_MDHistoryDay'', ''Aggregated Used Drive Space by Day'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

    EXECUTE IMMEDIATE 'INSERT INTO "WFSDW"."MD_LOCKABLEOBJECT"("LOCKABLEOBJECTKEY", "LOCKABLEOBJECTTYPE", "NAME", "DESCRIPTION", 
        "LASTOBJECTSTATUS", "LASTSYNCDATE", "LASTETLPROCESS", "LASTRUNINSTANCE","CREATEDATE","UPDATEDATE")
      VALUES(20, 4, ''FACT_MDHistoryMonth'', ''Aggregated Used Drive Space by Month'', 0, TO_DATE(''2000-01-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), 999999, 1, TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''2007-03-01 00:00:00'',''YYYY-MM-DD HH24:MI:SS''))';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable insert MD_LOCKABLEOBJECT reference data'')';
      GOTO bypass_install;
      
  END;

  -- Create CK_MD_ETLPROCESSMESSAGE check constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSMESSAGE
      ADD ( CONSTRAINT CK_MD_ETLPROCESSMESSAGE
        CHECK (MessageType  = ''INFO''
              OR MessageType  = ''ERROR'')
        NOT DEFERRABLE INITIALLY IMMEDIATE )';
        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create CK_MD_ETLPROCESSMESSAGE check constraint'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_MD_ETLPROCESSMESSAGE_INST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSMESSAGE
      ADD ( CONSTRAINT FK_MD_ETLPROCESSMESSAGE_INST
            FOREIGN KEY(ETLPROCESS, RUNINSTANCE)
            REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
            ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_ETLPROCESSMESSAGE_INST foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_LOCKABLEOBJECT_OBJTYP foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_LOCKABLEOBJECT
      ADD ( CONSTRAINT FK_MD_LOCKABLEOBJECT_OBJTYP
            FOREIGN KEY(LOCKABLEOBJECTTYPE)
            REFERENCES WFSDW.MD_LOCKABLEOBJECTTYPE(LOCKABLEOBJECTTYPEKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_LOCKABLEOBJECT_OBJTYP foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_LOCKABLEOBJECT_OBJST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_LOCKABLEOBJECT
      ADD ( CONSTRAINT FK_MD_LOCKABLEOBJECT_OBJST
            FOREIGN KEY(LASTOBJECTSTATUS)
            REFERENCES WFSDW.MD_LOCKABLEOBJECTSTATUS(LOCKABLEOBJECTSTATUSKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_LOCKABLEOBJECT_OBJST foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_LOCKABLEOBJECT_INST
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_LOCKABLEOBJECT
      ADD ( CONSTRAINT FK_MD_LOCKABLEOBJECT_INST
            FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
            REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_LOCKABLEOBJECT_INST foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_ETLPROCESS_TYPE foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESS
      ADD ( CONSTRAINT FK_MD_ETLPROCESS_TYPE
            FOREIGN KEY(ETLPROCESSTYPE)
            REFERENCES WFSDW.MD_ETLPROCESSTYPE(ETLPROCESSTYPEKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_ETLPROCESS_TYPE foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_ETLPROCESS_ROLE
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESS
      ADD ( CONSTRAINT FK_MD_ETLPROCESS_ROLE
            FOREIGN KEY(ETLPROCESSROLE)
            REFERENCES WFSDW.MD_ETLPROCESSROLE(ETLPROCESSROLEKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_ETLPROCESS_ROLE foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_ETLPROCESS_OBJECT foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESS
      ADD ( CONSTRAINT FK_MD_ETLPROCESS_OBJECT
            FOREIGN KEY(LOCKABLEOBJECTLOADED)
            REFERENCES WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_ETLPROCESS_OBJECT foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create CK_MD_RUNINSTANCE check constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSRUNINSTANCE
      ADD ( CONSTRAINT CK_MD_RUNINSTANCE
            CHECK (RunInstance > 0)
            NOT DEFERRABLE INITIALLY IMMEDIATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create CK_MD_RUNINSTANCE check constraint'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_RUNINSTANCE_STATUS foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSRUNINSTANCE
      ADD ( CONSTRAINT FK_MD_RUNINSTANCE_STATUS
            FOREIGN KEY(STATUS)
            REFERENCES WFSDW.MD_ETLPROCESSSTATUS(ETLPROCESSSTATUSKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_RUNINSTANCE_STATUS foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_RUNINSTANCE_PROC foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSRUNINSTANCE
      ADD ( CONSTRAINT FK_MD_RUNINSTANCE_PROC
            FOREIGN KEY(ETLPROCESS)
            REFERENCES WFSDW.MD_ETLPROCESS(ETLPROCESSKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_RUNINSTANCE_PROC foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_ETLPROCESSRUNINSTANCE_PARENT foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSRUNINSTANCE
                        ADD ( CONSTRAINT FK_MD_RUNINSTANCE_PARENT
                        FOREIGN KEY(PARENTETLPROCESS, PARENTRUNINSTANCE)
                        REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
                        ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_RUNINSTANCE_PARENT foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_MD_RUNINSTANCE_OBJECTST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSRUNINSTANCE
      ADD ( CONSTRAINT FK_MD_RUNINSTANCE_OBJECTST
            FOREIGN KEY(LOCKABLEOBJECTSTATUS)
            REFERENCES WFSDW.MD_LOCKABLEOBJECTSTATUS(LOCKABLEOBJECTSTATUSKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_RUNINSTANCE_OBJECTST foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_MD_RUNINSTANCE_OBJECT foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.MD_ETLPROCESSRUNINSTANCE
      ADD ( CONSTRAINT FK_MD_RUNINSTANCE_OBJECT
            FOREIGN KEY(LOCKABLEOBJECTLOADED)
            REFERENCES WFSDW.MD_LOCKABLEOBJECT(LOCKABLEOBJECTKEY)
            ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
            
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_MD_RUNINSTANCE_OBJECT foreign key'')';
      GOTO bypass_install;
      
  END;

/*
--------------------------------------------------------------------------------
                        Create Staging Tables
--------------------------------------------------------------------------------
*/

  -- Create Stage I Tables
  
  -- Create STG1_DIFF_LOG
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_LOG (
      ROWID_              NUMBER NOT NULL,
      LOGID               NUMBER NOT NULL,
      LOGCLASS            CHAR(1) NULL,
      LOGCLASSNAME        VARCHAR2(10) NULL,
      LOCALDATE           TIMESTAMP(6) NULL,
      TEMPLATEWK          NUMBER NULL,
      PROCESSDEFINITIONID NUMBER NULL,
      WORKORDERWK         NUMBER NULL,
      PROCESSINSTANCEID   NUMBER NULL,
      NODEWK              NUMBER NULL,
      NODEID              NUMBER NULL,
      LOCALENTERDATE      TIMESTAMP(6) NULL,
      LOCALENTERDATEWK    NUMBER NULL,
      LOCALENTERTIMEWK    NUMBER NULL,
      LOCALLEAVEDATE      TIMESTAMP(6) NULL,
      LOCALLEAVEDATEWK    NUMBER NULL,
      LOCALLEAVETIMEWK    NUMBER NULL,
      DURATION            NUMBER NULL,
      RESOURCEGROUPWK     NUMBER NULL,
      RESOURCEGROUPID     NUMBER NULL,
      RESOURCEWK          NUMBER NULL,
      RESOURCEID          NUMBER NULL,
      ADMINSTATUSID       NUMBER NULL,
      ADMINSTATUSWK       NUMBER NULL,
      OPERSTATUSID        NUMBER NULL,
      OPERSTATUSWK        NUMBER NULL,
      INSERTOK            NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE       TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NULL,
      DIFFETLPROCESS      NUMBER NULL,
      DIFFRUNINSTANCE     NUMBER NULL,
      DIFFDATE            TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';
  
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_LOG table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_S_LOG_RUNINST table
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_LOG
      ADD ( CONSTRAINT FK_S_LOG_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_LOGRUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_MONITOREDDRIVE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_MONITOREDDRIVE (
      ROWID_                      NUMBER NOT NULL,
      MONITOREDDRIVEID            NUMBER NULL,
      NAME_                       VARCHAR2(500) NULL,
      DESCRIPTION                 VARCHAR2(500) NULL,
      PATH_                       VARCHAR2(1000) NULL,
      RESOURCEWK                  NUMBER NULL,
      RESOURCEID                  NUMBER NULL,
      INTERNALDRIVEID             NUMBER NULL,
      TOTALCAPACITY               NUMBER(19,0) NULL,
      WARNINGTHRESHOLDPERCENT     NUMBER NULL,
      ERRORTHRESHOLDPERCENT       NUMBER NULL,
      CURRENTDRIVESTATUSID        NUMBER NULL,
      CURRENTDRIVESTATUSNAME      VARCHAR2(50) NULL,
      LASTSAMPLEDATETIME          TIMESTAMP(6) NULL,
      INSERTOK                    NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE               TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE          TIMESTAMP(6) NULL,
      DIFFETLPROCESS              NUMBER NULL,
      DIFFRUNINSTANCE             NUMBER NULL,
      DIFFDATE                    TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_MONITOREDDRIVE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_S_MONITOREDDRIVE foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_MONITOREDDRIVE
      ADD ( CONSTRAINT FK_S_MONITOREDDRIVE_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_LOG table'')';
      GOTO bypass_install;
      
  END;
  
  
  -- Create table STG1_DIFF_DRIVEHISTORY
  BEGIN 
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_DRIVEHISTORY (
      ROWID_                      NUMBER NOT NULL,
      MONITOREDDRIVEHISTORYID     NUMBER NULL,
      MONITOREDDRIVEWK            NUMBER NULL,
      MONITOREDDRIVEID            NUMBER NULL,
      CURRENTBYTESUSED            NUMBER(19,0) NULL,
      TOTALCAPACITY               NUMBER(19,0) NULL,
      WARNINGTHRESHOLDPERCENT     NUMBER NULL,
      ERRORTHRESHOLDPERCENT       NUMBER NULL,
      CURRENTDRIVESTATUSID        NUMBER NULL,
      CURRENTDRIVESTATUSNAME      VARCHAR2(50) NULL,
      SAMPLEDATETIME              TIMESTAMP(6) NULL,
      INSERTOK                    NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE               TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE          TIMESTAMP(6) NULL,
      DIFFETLPROCESS              NUMBER NULL,
      DIFFRUNINSTANCE             NUMBER NULL,
      DIFFDATE                    TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_DRIVEHISTORY table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create foreign key constraint FK_S_DRIVEHISTORY_RUNINST on STG1_DIFF_DRIVEHISTORY
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_DRIVEHISTORY
      ADD ( CONSTRAINT FK_S_DRIVEHISTORY_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_DRIVEHISTORY table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_NODE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_NODE (
      ROWID_              NUMBER NOT NULL,
      NODEID              NUMBER NULL,
      NODECLASS           VARCHAR2(50) NULL,
      NAME                VARCHAR2(255) NULL,
      TEMPLATEWK          NUMBER NULL,
      PROCESSDEFINITIONID NUMBER NULL,
      NODEINDEX           NUMBER NULL,
      TASKID              NUMBER NULL,
      TASKNAME            VARCHAR2(255) NULL,
      ISSTARTNODE         NUMBER NULL,
      RESOURCEGROUPWK     NUMBER NULL,
      RESOURCEGROUPID     NUMBER NULL,
      TASKCONTROLLERID    NUMBER NULL,
      INSERTOK            NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE       TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NULL,
      DIFFETLPROCESS      NUMBER NULL,
      DIFFRUNINSTANCE     NUMBER NULL,
      DIFFDATE            TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX)
  TABLESPACE WFSDW_DATA';
  
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_NODE table'')';
      GOTO bypass_install;
      
  END;

  -- Create STG1_DIFF_NODE
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_NODE
      ADD ( CONSTRAINT FK_S_NODE_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_NODE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_RESOURCE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_RESOURCE (
      ROWID_              NUMBER NOT NULL,
      RESOURCEID          NUMBER NULL,
      RESOURCENAME        VARCHAR2(50) NULL,
      RESOURCETYPEID      NUMBER NULL,
      RESOURCETYPENAME    VARCHAR2(50) NULL,
      ISACTIVE            NUMBER NULL,
      INSERTOK            NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE       TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NULL,
      DIFFETLPROCESS      NUMBER NULL,
      DIFFRUNINSTANCE     NUMBER NULL,
      DIFFDATE            TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX)
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_RESOURCE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_S_RES_RUNINST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_RESOURCE
      ADD ( CONSTRAINT FK_S_RES_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_RES_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_RESOURCEGROUP
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_RESOURCEGROUP (
      ROWID_              NUMBER NOT NULL,
      RESOURCEGROUPID     NUMBER NULL,
      RESOURCEGROUPNAME   VARCHAR2(50) NULL,
      RESOURCETYPEID      NUMBER NULL,
      RESOURCETYPENAME    VARCHAR2(50) NULL,
      ACCESSLEVELID       NUMBER NULL,
      ACCESSLEVELNAME     VARCHAR2(50) NULL,
      INSERTOK            NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE       TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NULL,
      DIFFETLPROCESS      NUMBER NULL,
      DIFFRUNINSTANCE     NUMBER NULL,
      DIFFDATE            TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX)
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_RESOURCEGROUP table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create foreign key constraint FK_S_RESGRP_RUNINST on STG1_DIFF_RESOURCEGROUP
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_RESOURCEGROUP
      ADD ( CONSTRAINT FK_S_RESGRP_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
  
    EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_RESGRP_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_TEMPLATE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_TEMPLATE (
      ROWID_                      NUMBER NOT NULL,
      PROCESSDEFINITIONID         NUMBER NULL,
      NAME                        VARCHAR2(400) NULL,
      PROCESSDEFINITIONTYPEID     NUMBER NULL,
      PROCESSDEFINITIONTYPENAME   VARCHAR2(50) NULL,
      STARTNODEID                 NUMBER NULL,
      WFSCREATEDATE               TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE          TIMESTAMP(6) NULL,
      DIFFETLPROCESS              NUMBER NULL,
      DIFFRUNINSTANCE             NUMBER NULL,
      DIFFDATE                    TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_TEMPLATE foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_S_TEMPLATE_RUNINST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_TEMPLATE
      ADD ( CONSTRAINT FK_S_TEMPLATE_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_TEMPLATE_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_VARIABLEINSTANCE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_VARIABLEINSTANCE (
      ROWID_                      NUMBER NOT NULL,
      VARIABLEINSTANCENAME        VARCHAR2(255) NULL,
      VARIABLEINSTANCEVALUE       VARCHAR2(255) NULL,
      INSERTOK                    NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE               TIMESTAMP(6) NULL,
      DIFFETLPROCESS              NUMBER NULL,
      DIFFRUNINSTANCE             NUMBER NULL,
      DIFFDATE                    TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX)
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_VARIABLEINSTANCE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_S_VARIABLEINSTANCE_RUNINST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_VARIABLEINSTANCE
      ADD ( CONSTRAINT FK_S_VARIABLEINSTANCE_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_VARIABLEINSTANCE_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_WORKORDER
  BEGIN
  
  EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_WORKORDER (
      ROWID_                      NUMBER NOT NULL,
      PROCESSINSTANCEID           NUMBER NULL,
      LOCALSTARTDATE              TIMESTAMP(6) NOT NULL,
      LOCALSTARTDATEWK            NUMBER(8) NULL,
      LOCALSTARTTIMEWK            NUMBER(6) NULL,
      LOCALENDDATE                TIMESTAMP(6) NULL,
      ISSUSPENDED                 NUMBER NULL,
      PROCESSDEFINITIONID         NUMBER NOT NULL,
      TEMPLATEWK                  NUMBER NULL,
      PROVIDERWK                  NUMBER NULL,
      ADMINSTATUSID               NUMBER NULL,
      ADMINSTATUSWK               NUMBER NULL,
      OPERSTATUSID                NUMBER NULL,
      OPERSTATUSWK                NUMBER NULL,
      PRIORITYID                  NUMBER NULL,
      PRIORITY                    VARCHAR2(50) NULL,
      PROVIDERNAME                VARCHAR2(255) NULL,
      PACKAGEASSETID              VARCHAR2(255) NULL,
      TITLE                       VARCHAR2(255) NULL,
      BRIEFTITLE                  VARCHAR2(255) NULL,
      ASSETID                     VARCHAR2(255) NULL,
      OBJECTTYPE                  VARCHAR2(255) NULL,
      HOUSEMATERIALID             VARCHAR2(255) NULL,
      BROADCASTMATERIALID         VARCHAR2(255) NULL,
      LICENSESTART                VARCHAR2(255) NULL,
      LICENSEEND                  VARCHAR2(255) NULL,
      LICENSESTARTDATE            DATE,
      LICENSEENDDATE              DATE,
      DWPROCESSCOMPLETE           NUMBER(1,0) DEFAULT (0) NULL,
      DWPROCESSERROR              NUMBER(1,0) DEFAULT (0) NULL,
      DWPROCESSMESSAGE            VARCHAR2(500) NULL,
      INSERTOK                    NUMBER DEFAULT 0 NULL,
      WFSCREATEDATE               TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE          TIMESTAMP(6) NULL,
      DIFFETLPROCESS              NUMBER NULL,
      DIFFRUNINSTANCE             NUMBER NULL,
      DIFFDATE                    TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_WORKORDER table'')';
      GOTO bypass_install;
      
  END; 
  
  -- Create FK_S_WORKORDER_RUNINST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_WORKORDER
      ADD ( CONSTRAINT FK_S_WORKORDER_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_WORKORDER_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create table STG1_DIFF_WORKORDERSTATUS
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_WORKORDERSTATUS (
      ROWID_              NUMBER NOT NULL,
      PROCESSSTATUSID     NUMBER NULL,
      NAME                VARCHAR2(50) NULL,
      WFSCREATEDATE       TIMESTAMP(6) NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NULL,
      DIFFETLPROCESS      NUMBER NULL,
      DIFFRUNINSTANCE     NUMBER NULL,
      DIFFDATE            TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_WORKORDERSTATUS table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_S_WOSTAT_RUNINST table
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_WORKORDERSTATUS
      ADD ( CONSTRAINT FK_S_WOSTAT_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_WOSTAT_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- CREATE TABLE STG1_DIFF_MDRIVEHISTORY
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.STG1_DIFF_MDRIVEHISTORY (
      ROWID_                              NUMBER          NOT NULL,
      MONITOREDDRIVEHISTORYID             NUMBER          NULL,
      MONITOREDDRIVEID                    NUMBER          NULL,
      MONITOREDDRIVEWK                    NUMBER          NULL,
      CURRENTDRIVENAME                    VARCHAR2(1500)  NULL,
      CURRENTDESCRIPTION                  VARCHAR2(150)   NULL,
      CURRENTPATH                         VARCHAR2(3000)  NULL,
      CURRENTINTERNALDRIVEID              NUMBER          NULL,
      CURRENTTOTALCAPACITY                NUMBER          NULL,
      CURRENTWARNINGTHRESHOLDPERCENT      NUMBER          NULL,
      CURRENTERRORTHRESHOLDPERCENT        NUMBER          NULL,
      CURRENTBYTESUSED                    NUMBER          NULL,
      CURRENTDRIVESTATUSID                NUMBER          NULL,
      MONITOREDDRIVESTATUSWK              NUMBER          NULL,
      CURRENTDRIVESTATUSNAME              VARCHAR2(25)    NULL,
      SAMPLEDATETIME                      TIMESTAMP(6)    NULL,
      LOCALDATEWK                         NUMBER          NULL,
      LOCALTIMEWK                         NUMBER          NULL,
      WFSCREATEDATE                       TIMESTAMP(6)    NULL,
      LASTPRODUPDATEDATE                  TIMESTAMP(6)    NULL,
      DIFFETLPROCESS                      NUMBER          NULL,
      DIFFRUNINSTANCE                     NUMBER          NULL,
      DIFFDATE                            TIMESTAMP(6) DEFAULT (systimestamp) NULL,
      PRIMARY KEY(ROWID_)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_MDRIVEHISTORY table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_S_MDHISTORY_RUNINST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.STG1_DIFF_MDRIVEHISTORY
      ADD ( CONSTRAINT FK_S_MDHISTORY_RUNINST
          FOREIGN KEY(DIFFETLPROCESS, DIFFRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_S_MDHISTORY_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;

/*
--------------------------------------------------------------------------------
                         Create Dimension Tables
--------------------------------------------------------------------------------
*/

  -- Create DIM_DATE Table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_DATE (
      DATEWK          NUMBER(8) NOT NULL,
      CALENDERDATE        DATE NOT NULL,
      DAYOFWEEKNAME  VARCHAR2(25) NOT NULL,
      DAYOFWEEKNUM    NUMBER(1) NOT NULL,
      DAYOFMONTH      NUMBER(2) NOT NULL,
      WEEKOFYEAR      NUMBER(2) NOT NULL,
      MONTHNAME       VARCHAR2(25) NOT NULL,
      MONTHNUM        NUMBER(2) NOT NULL,
      QUARTER         NUMBER(1) NOT NULL,
      YEARNUM         NUMBER(4) NOT NULL,
      CREATEDATE      TIMESTAMP(6) NOT NULL,
      PRIMARY KEY(DATEWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
    TABLESPACE WFSDW_DATA';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_DATE table'')';
      GOTO bypass_install;
      
  END;

  -- Create DIM_MONTH Table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_MONTH (
      MONTHWK          NUMBER(8) NOT NULL,
      MONTHNAME       VARCHAR2(25) NOT NULL,
      MONTHNUM        NUMBER(2) NOT NULL,
      QUARTER         NUMBER(1) NOT NULL,
      YEARNUM         NUMBER(4) NOT NULL,
      CREATEDATE      TIMESTAMP(6) NOT NULL,
      PRIMARY KEY(MONTHWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_MONTH table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_TIME Table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_TIME (
      TIMEWK                  NUMBER(6) NOT NULL,
      TIMEOFDAY               TIMESTAMP NOT NULL,
      STANDARDDISPLAY         VARCHAR2(25) NOT NULL,
      MILITARYDISPLAY         VARCHAR2(25) NOT NULL,
      STANDARDHOUR            NUMBER(2) NOT NULL,
      MILITARYHOUR            NUMBER(2) NOT NULL,
      MERIDIAN                VARCHAR2(2) NOT NULL,
      MINUTES                 NUMBER(2) NOT NULL,
      MINUTESAFTERMIDNIGHT    NUMBER(4) NOT NULL,
      SECONDS                 NUMBER(4) NOT NULL,
      SECONDSINHOUR           NUMBER(4) NOT NULL,
      SECONDSAFTERMIDNIGHT    NUMBER(5) NOT NULL,
      CREATEDATE              TIMESTAMP(6) NOT NULL,
      PRIMARY KEY(TIMEWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_TIME table'')';
      GOTO bypass_install;
      
  END;

  -- Create DIM_TEMPLATE Table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_TEMPLATE (
      TEMPLATEWK                  NUMBER NOT NULL,
      PROCESSDEFINITIONID         NUMBER NOT NULL,
      NAME                        VARCHAR2(400) NOT NULL,
      PROCESSDEFINITIONTYPEID     NUMBER NOT NULL,
      PROCESSDEFINITIONTYPENAME   VARCHAR2(50) NOT NULL,
      STARTNODEID                 NUMBER NOT NULL,
      WFSCREATEDATE               TIMESTAMP(6) NOT NULL,
      LASTPRODUPDATEDATE          TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS              NUMBER NOT NULL,
      LASTRUNINSTANCE             NUMBER NOT NULL,
      CREATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(TEMPLATEWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_TEMPLATE table'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_D_TEMPLATE_RUNINST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_TEMPLATE
      ADD ( CONSTRAINT FK_D_TEMPLATE_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_TEMPLATE_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;

  -- Create IDX_DIM_TEMPLATE_ID index
  BEGIN
  
    EXECUTE IMMEDIATE '  CREATE INDEX WFSDW.IDX_DIM_TEMPLATE_ID
        ON WFSDW.DIM_TEMPLATE(PROCESSDEFINITIONID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_TEMPLATE_ID'')';
      GOTO bypass_install;
      
  END;

  -- Create DIM_MONITOREDDRIVESTATUS table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_MONITOREDDRIVESTATUS (
      MONITOREDDRIVESTATUSWK   NUMBER NOT NULL,
      DRIVESTATUSID     NUMBER NOT NULL,
      NAME                VARCHAR2(50) NOT NULL,
      WFSCREATEDATE       TIMESTAMP(6) NOT NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS      NUMBER NOT NULL,
      LASTRUNINSTANCE     NUMBER NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(MONITOREDDRIVESTATUSWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_MONITOREDDRIVESTATUS table'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_D_MDSTAT_RUNINST foreign key
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_MONITOREDDRIVESTATUS
      ADD ( CONSTRAINT FK_D_MDSTAT_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_MDSTAT_RUNINST foreign key'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_D_MDSTATUS index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_D_MDSTATUS_ID
        ON WFSDW.DIM_MONITOREDDRIVESTATUS(DRIVESTATUSID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_D_MDSTATUS'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_MONITOREDDRIVE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_MONITOREDDRIVE (
      MONITOREDDRIVEWK            NUMBER NOT NULL,
      MONITOREDDRIVEID            NUMBER NOT NULL,
      NAME_                       VARCHAR2(500) NOT NULL,
      DESCRIPTION                 VARCHAR2(500) NOT NULL,
      PATH_                       VARCHAR2(1000) NOT NULL,
      INTERNALDRIVEID             NUMBER NOT NULL,
      TOTALCAPACITY               NUMBER(19,0) NOT NULL,
      WARNINGTHRESHOLDPERCENT     NUMBER NOT NULL,
      ERRORTHRESHOLDPERCENT       NUMBER NOT NULL,
      WFSCREATEDATE               TIMESTAMP(6) NOT NULL,
      WFSENDDATE                  TIMESTAMP(6) NULL,
      LASTETLPROCESS              NUMBER NOT NULL,
      LASTRUNINSTANCE             NUMBER NOT NULL,
      CREATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(MONITOREDDRIVEWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_MONITOREDDRIVE'')';
      GOTO bypass_install;
      
  END;
 
  -- Create FK_D_MONITOREDDRIVE_RUNINST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_MONITOREDDRIVE
      ADD ( CONSTRAINT FK_D_MONITOREDDRIVE_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN 
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_MONITOREDDRIVE_RUNINST foreighn key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_MONITOREDDRIVE_ID index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_DIM_MONITOREDDRIVE_ID
          ON WFSDW.DIM_MONITOREDDRIVE(MONITOREDDRIVEID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_MONITOREDDRIVE_ID index.'')';
      GOTO bypass_install;
      
  END;  

  -- Create DIM_WORKORDERSTATUS table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_WORKORDERSTATUS (
      WORKORDERSTATUSWK   NUMBER NOT NULL,
      PROCESSSTATUSID     NUMBER NOT NULL,
      NAME                VARCHAR2(50) NOT NULL,
      WFSCREATEDATE       TIMESTAMP(6) NOT NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS      NUMBER NOT NULL,
      LASTRUNINSTANCE     NUMBER NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(WORKORDERSTATUSWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA
      NOCACHE
      MONITORING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_WORKORDERSTATUS table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_D_WOSTAT_RUNINST foriegn key constraint.
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_WORKORDERSTATUS
      ADD ( CONSTRAINT FK_D_WOSTAT_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_WOSTAT_RUNINST foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_WORKORDERSTATUS_ID index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_DIM_WORKORDERSTATUS_ID
        ON WFSDW.DIM_WORKORDERSTATUS(PROCESSSTATUSID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_WORKORDERSTATUS_ID index.'')';
      GOTO bypass_install;
      
  END;
    
  -- Create table DIM_NODE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_NODE (
      NODEWK              NUMBER NOT NULL,
      NODEID              NUMBER NOT NULL,
      NODECLASS           VARCHAR2(50) NOT NULL,
      NAME                VARCHAR2(255) NOT NULL,
      NODEINDEX           NUMBER NOT NULL,
      TASKID              NUMBER NULL,
      TASKNAME            VARCHAR2(255) NULL,
      ISSTARTNODE         NUMBER NOT NULL,
      TASKCONTROLLERID    NUMBER NULL,
      WFSCREATEDATE       TIMESTAMP(6) NOT NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS      NUMBER NOT NULL,
      LASTRUNINSTANCE     NUMBER NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(NODEWK)
          USING INDEX
          TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_NODE table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_D_NODE_RUNINST foriegn key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_NODE
      ADD ( CONSTRAINT FK_D_NODE_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_NODE_RUNINST foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_NODE_ID index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_DIM_NODE_ID
        ON WFSDW.DIM_NODE(NODEID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_NODE_ID index.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_RESOURCE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_RESOURCE (
      RESOURCEWK          NUMBER NOT NULL,
      RESOURCEID          NUMBER NOT NULL,
      RESOURCENAME        VARCHAR2(50) NOT NULL,
      RESOURCETYPEID      NUMBER NOT NULL,
      RESOURCETYPENAME    VARCHAR2(50) NOT NULL,
      ISACTIVE            NUMBER NOT NULL,
      WFSCREATEDATE       TIMESTAMP(6) NOT NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS      NUMBER NOT NULL,
      LASTRUNINSTANCE     NUMBER NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(RESOURCEWK)
         USING INDEX
         TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_RESOURCE'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_D_RES_RUNINST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_RESOURCE
      ADD ( CONSTRAINT FK_D_RES_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_RES_RUNINST foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_RESOURCE_ID index
  BEGIN
    
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_DIM_RESOURCE_ID
        ON WFSDW.DIM_RESOURCE(RESOURCEID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_RESOURCE_ID index.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_RESOURCEGROUP table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_RESOURCEGROUP (
      RESOURCEGROUPWK     NUMBER NOT NULL,
      RESOURCEGROUPID     NUMBER NOT NULL,
      RESOURCEGROUPNAME   VARCHAR2(50) NOT NULL,
      RESOURCETYPEID      NUMBER NOT NULL,
      RESOURCETYPENAME    VARCHAR2(50) NOT NULL,
      ACCESSLEVELID       NUMBER NOT NULL,
      ACCESSLEVELNAME     VARCHAR2(50) NOT NULL,
      WFSCREATEDATE       TIMESTAMP(6) NOT NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS      NUMBER NOT NULL,
      LASTRUNINSTANCE     NUMBER NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(RESOURCEGROUPWK)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_RESOURCEGROUP table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_D_RESGRP_RUNINST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_RESOURCEGROUP
      ADD ( CONSTRAINT FK_D_RESGRP_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
    EXCEPTION
      WHEN OTHERS THEN
      
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_RESGRP_RUNINST foreign key constraint'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_RESOURCEGROUP_ID index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_DIM_RESOURCEGROUP_ID
        ON WFSDW.DIM_RESOURCEGROUP(RESOURCEGROUPID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_RESOURCEGROUP_ID index.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_VARIABLEINSTANCE table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_VARIABLEINSTANCE (
      VARIABLEINSTANCEWK          NUMBER NOT NULL,
      VARIABLEINSTANCENAME        VARCHAR2(255) NULL,
      VARIABLEINSTANCEVALUE       VARCHAR2(255) NULL,
      WFSCREATEDATE               TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS              NUMBER NOT NULL,
      LASTRUNINSTANCE             NUMBER NOT NULL,
      CREATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(VARIABLEINSTANCEWK)
        USING INDEX
        TABLESPACE WFSDW_INDEX
      )
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_VARIABLEINSTANCE table'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_D_VARIABLEINSTANCE_RUNINST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_VARIABLEINSTANCE
      ADD ( CONSTRAINT FK_D_VARIABLEINSTANCE_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_DVARIABLEINSTACNE_RUNINST foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_VI_NAME_VALUE_UK index
  BEGIN
    
    EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX WFSDW.IDX_DIM_VI_NAME_VALUE_UK
        ON WFSDW.DIM_VARIABLEINSTANCE(VARIABLEINSTANCENAME, VARIABLEINSTANCEVALUE)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_VI_NAME_VALUE_UK index.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_WORKORDER table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.DIM_WORKORDER (
      WORKORDERWK         NUMBER NOT NULL,
      PROCESSINSTANCEID   NUMBER NULL,
      SUSPENDED           VARCHAR2(3) NULL,
      PRIORITY            VARCHAR2(50) NULL,
      LOCALSTARTDATE      TIMESTAMP(6) NOT NULL,
      LOCALENDDATE        TIMESTAMP(6) NULL,
      PACKAGEASSETID      VARCHAR2(255) NOT NULL,
      ASSETID             VARCHAR2(255) NULL,
      ASSETTYPE           VARCHAR2(255) NULL,
      TITLE               VARCHAR2(255) NULL,
      BRIEFTITLE          VARCHAR2(255) NULL,
      HOUSEMATERIALID     VARCHAR2(255) NULL,
      BROADCASTMATERIALID VARCHAR2(255) NULL,
      LICENSESTARTDATE    DATE NULL,
      LICENSEENDDATE      DATE NULL,
      WFSCREATEDATE       TIMESTAMP(6) NOT NULL,
      LASTPRODUPDATEDATE  TIMESTAMP(6) NOT NULL,
      LASTETLPROCESS      NUMBER NOT NULL,
      LASTRUNINSTANCE     NUMBER NOT NULL,
      CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
      PRIMARY KEY(WORKORDERWK)
        USING INDEX
        TABLESPACE WFSDW_INDEX)
      TABLESPACE WFSDW_DATA';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_WORKORDER table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_D_WORKORDER_RUNINST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.DIM_WORKORDER
      ADD ( CONSTRAINT FK_D_WORKORDER_RUNINST
          FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
          REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
          ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_D_WORKORDER_RUNINST foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_WORKORDER_ID index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_DIM_WORKORDER_ID
        ON WFSDW.DIM_WORKORDER(PROCESSINSTANCEID)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_WORKORDER_ID index.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_DIM_WRKORDER_PCKGASSETID index
  BEGIN
	  EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_DIM_WRKORDER_PCKGASSETID
	          ON WFSDW.DIM_WORKORDER(PACKAGEASSETID)
	      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
	      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
	      PARALLEL( DEGREE 1 )
	      LOGGING';
	      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_DIM_WRKORDER_PCKGASSETID index.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_PROVIDER View
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW WFSDW.DIM_PROVIDER ( VARIABLEINSTANCEWK,
                                              PROVIDERNAME,
                                              WFSCREATEDATE,
                                              LASTETLPROCESS,
                                              LASTRUNINSTANCE,
                                              CREATEDATE,
                                              UPDATEDATE )
      AS
      SELECT "VARIABLEINSTANCEWK",
             "VARIABLEINSTANCEVALUE",
             "WFSCREATEDATE",
             "LASTETLPROCESS",
             "LASTRUNINSTANCE",
             "CREATEDATE",
            "UPDATEDATE"
      FROM DIM_VARIABLEINSTANCE
      WHERE VARIABLEINSTANCENAME = ''providerId''';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_PROVIDER view.'')';
      GOTO bypass_install;
      
  END;

  -- Insert Default Dimension Data

  -- Insert a default DIM_VARIABLEINSTANCE provider value
  BEGIN

    EXECUTE IMMEDIATE 'INSERT INTO WFSDW."DIM_VARIABLEINSTANCE" (VARIABLEINSTANCEWK, VARIABLEINSTANCENAME, VARIABLEINSTANCEVALUE, WFSCREATEDATE, LASTETLPROCESS, LASTRUNINSTANCE, CREATEDATE, UPDATEDATE) VALUES (''0'', ''providerId'', ''N/A'', TO_TIMESTAMP(''29-NOV-07 10.00.00.'', ''DD-MON-RR HH.MI.SS.FF AM''), ''999999'', ''1'', TO_TIMESTAMP(''29-NOV-07 10.00.00.'', ''DD-MON-RR HH.MI.SS.FF AM''), TO_TIMESTAMP(''29-NOV-07 10.00.00.'', ''DD-MON-RR HH.MI.SS.FF AM''))';

  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert DIM_VARIABLEINSTANCE provider default value'')';
      GOTO bypass_install;

  END;
  
/*
--------------------------------------------------------------------------------
                         Create Dimension Views
--------------------------------------------------------------------------------
*/

  -- Grant SELECT on DIM_MONITOREDDRIVE to REPORTREADER
  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT SELECT ON WFSDW.DIM_MONITOREDDRIVE TO REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant select on DIM_MONITOREDDRIVE to REPORTREADER.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create view vDIM_MONITOREDDRIVE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW REPORTREADER.VDIM_MONITOREDDRIVE ( 
                         MONITOREDDRIVEWK, 
                         MONITOREDDRIVEID, 
                         NAME_, 
                         DESCRIPTION, 
                         PATH_, 
                         INTERNALDRIVEID, 
                         TOTALCAPACITY, 
                         WARNINGTHRESHOLDPERCENT, 
                         ERRORTHRESHOLDPERCENT, 
                         LASTSAMPLEDATETIME )
                       AS
                       SELECT MONITOREDDRIVEWK,
                              MONITOREDDRIVEID,
                              NAME_,
                              DESCRIPTION,
                              PATH_,
                              INTERNALDRIVEID,
                              TOTALCAPACITY,
                              WARNINGTHRESHOLDPERCENT,
                              ERRORTHRESHOLDPERCENT,
                              WFSCREATEDATE
                      FROM WFSDW.DIM_MONITOREDDRIVE
                      WHERE WFSENDDATE is NULL';
                      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create VDIM_MONITOREDDRIVE view.'')';
      GOTO bypass_install;
      
  END;

  -- Grant SELECT on DIM_PROVIDER to REPORTREADER
  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT SELECT ON WFSDW.DIM_PROVIDER TO REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant select on DIM_PROVIDER to REPORTREADER.'')';
      GOTO bypass_install;
      
  END;      

  -- Create view vDIM_PROVIDER
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW REPORTREADER.VDIM_PROVIDER ( 
                         PROVIDERWK, 
                         PROVIDERNAME, 
                         LASTETLPROCESS, 
                         LASTRUNINSTANCE, 
                         CREATEDATE, 
                         UPDATEDATE )
                       AS
                       SELECT VARIABLEINSTANCEWK,
                              PROVIDERNAME,
                              LASTETLPROCESS,
                              LASTRUNINSTANCE,
                              CREATEDATE,
                              UPDATEDATE
                       FROM WFSDW.DIM_PROVIDER';
                       
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create VDIM_PROVIDER view.'')';
      GOTO bypass_install;
      
  END;
     
  -- Grant SELECT on DIM_RESOURCE to REPORTREADER
  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT SELECT ON WFSDW.DIM_RESOURCE TO REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant SELECT on DIM_RESOURCE to REPORTREADER view.'')';
      GOTO bypass_install;
      
  END;

  -- Create view vDIM_RESOURCE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW REPORTREADER.VDIM_RESOURCE ( 
                         RESOURCEWK, 
                         RESOURCEID, 
                         NAME, 
                         ISACTIVE )
                       AS
                       SELECT RESOURCEWK,
                              RESOURCEID,
                              RESOURCENAME,
                              ISACTIVE
                       FROM WFSDW.DIM_RESOURCE';
                       
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create VDIM_RESOURCE view.'')';
      GOTO bypass_install;
      
  END;
  
  -- Grant SELECT on DIM_REOUSRCEGROUP to REPORTREADER
  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT SELECT ON WFSDW.DIM_RESOURCEGROUP TO REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant SELECT on DIM_RESOURCEGROUP to REPORTREADER.'')';
      GOTO bypass_install;
      
  END;

  -- Create view vDIM_RESOURCEGROUP
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW REPORTREADER.VDIM_RESOURCEGROUP ( 
                         RESOURCEGROUPWK, 
                         RESOURCEGROUPID, 
                         NAME, 
                         ACCESSLEVELID )
                       AS
                       SELECT RESOURCEGROUPWK,
                              RESOURCEGROUPID,
                              RESOURCEGROUPNAME,
                              ACCESSLEVELID
                       FROM WFSDW.DIM_RESOURCEGROUP';
                       
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create VDIM_RESOURCEGROUP view.'')';
      GOTO bypass_install;
      
  END;

  -- Grant SELECT on DIM_TEMPLATE to REPORTREADER
  BEGIN
  
    EXECUTE IMMEDIATE 'GRANT SELECT ON WFSDW.DIM_TEMPLATE TO REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant SELECT on DIM_TEMPLATE to REPORTREADER.'')';
      GOTO bypass_install;
      
  END;

  -- Create view vDIM_TEMPLATE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW REPORTREADER.VDIM_TEMPLATE ( 
                         TEMPLATEWK, 
                         PROCESSDEFINITIONID, 
                         NAME, 
                         PROCESSDEFINITIONTYPEID, 
                         PROCESSDEFINITIONTYPENAME, 
                         STARTNODEID, 
                         WFSCREATEDATE, 
                         LASTPRODUPDATEDATE, 
                         LASTETLPROCESS, 
                         LASTRUNINSTANCE, 
                         CREATEDATE, 
                         UPDATEDATE )
                       AS
                       SELECT TEMPLATEWK,
                              PROCESSDEFINITIONID,
                              NAME,
                              PROCESSDEFINITIONTYPEID,
                              PROCESSDEFINITIONTYPENAME,
                              STARTNODEID,
                              WFSCREATEDATE,
                              LASTPRODUPDATEDATE,
                              LASTETLPROCESS,
                              LASTRUNINSTANCE,
                              CREATEDATE,
                              UPDATEDATE
                       FROM WFSDW.DIM_TEMPLATE';
                       
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create VDIM_TEMPLATE view.'')';
      GOTO bypass_install;
      
  END;

  -- Grant DIM_WORKORDER to REPORTREADER
  BEGIN
  
    EXECUTE IMMEDIATE 'Grant SELECT on WFSDW.DIM_WORKORDER to REPORTREADER';
    
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to grant select on DIM_WORKORDER to REPORTREADER.'')';
      GOTO bypass_install;
      
  END;
      
  -- Create view vDIM_OBJECTTYPE
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW REPORTREADER.VDIM_OBJECTTYPE ( 
                         OBJECTTYPENAME )
                       AS
                       SELECT DISTINCT ASSETTYPE
                       FROM WFSDW.DIM_WORKORDER
                       WHERE ASSETTYPE is not null';
                       
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create VDIM_OBJECTTYPE view.'')';
      GOTO bypass_install;
      
  END;
  
/*
--------------------------------------------------------------------------------
                         Create Fact Tables
--------------------------------------------------------------------------------
*/
  
  -- Create FACT_WORKORDERDETAIL table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_WORKORDERDETAIL (
                            WORKORDERWK                 NUMBER NOT NULL,
                            TEMPLATEWK                  NUMBER NOT NULL,
                            PROVIDERWK                  NUMBER NOT NULL,
                            ADMINSTATUSWK               NUMBER NOT NULL,
                            OPERSTATUSWK                NUMBER NOT NULL,
                            LOCALSTARTDATEWK            NUMBER(8) NOT NULL,
                            LOCALSTARTTIMEWK            NUMBER(6) NOT NULL,
                            WFSCREATEDATE               TIMESTAMP(6) NOT NULL,
                            LASTPRODUPDATEDATE          TIMESTAMP(6) NOT NULL,
                            LASTETLPROCESS              NUMBER NOT NULL,
                            LASTRUNINSTANCE             NUMBER NOT NULL,
                            CREATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                            UPDATEDATE                  TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                            PRIMARY KEY(WORKORDERWK,TEMPLATEWK,PROVIDERWK,ADMINSTATUSWK,OPERSTATUSWK,LOCALSTARTDATEWK,LOCALSTARTTIMEWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_WORKORDERDETAIL table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERDETAIL_RUNINST foreign key constraint
  BEGIN

    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDETAIL
         ADD ( CONSTRAINT FK_F_WORKORDERDETAIL_RUNINST
             FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
             REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDETAIL_RUNINST foreign key constraint.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_WORKORDERDETAIL_STARTDATE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDETAIL
         ADD ( CONSTRAINT FK_F_WORKORDERDETAIL_STARTDATE
             FOREIGN KEY(LOCALSTARTDATEWK)
             REFERENCES WFSDW.DIM_DATE(DATEWK)
             NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDETAIL_STARTDATE foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERDETAIL_STARTTIME foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDETAIL
         ADD ( CONSTRAINT FK_F_WORKORDERDETAIL_STARTTIME
             FOREIGN KEY(LOCALSTARTTIMEWK)
             REFERENCES WFSDW.DIM_TIME(TIMEWK)
             NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKRORDERDETAIL_STARTTIME foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERDETAIL_TEMPLATE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDETAIL
         ADD ( CONSTRAINT FK_F_WORKORDERDETAIL_TEMPLATE
             FOREIGN KEY(TEMPLATEWK)
             REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION 
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDETAIL_TEMPLATE foreign key constraint.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERDETAIL_PROVIDER foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDETAIL
         ADD ( CONSTRAINT FK_F_WORKORDERDETAIL_PROVIDER
             FOREIGN KEY(PROVIDERWK)
             REFERENCES WFSDW.DIM_VARIABLEINSTANCE(VARIABLEINSTANCEWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDETAIL_PROVIDER foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_WORKORDERDETAIL_ADMINSTS foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDETAIL
         ADD ( CONSTRAINT FK_F_WORKORDERDETAIL_ADMINSTS
             FOREIGN KEY(ADMINSTATUSWK)
             REFERENCES WFSDW.DIM_WORKORDERSTATUS(WORKORDERSTATUSWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDETAIL_ADMINSTS foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_WORKORDERDETAIL_OPERSTS foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDETAIL
         ADD ( CONSTRAINT FK_F_WORKORDERDETAIL_OPERSTS
             FOREIGN KEY(OPERSTATUSWK)
             REFERENCES WFSDW.DIM_WORKORDERSTATUS(WORKORDERSTATUSWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDETAIL_OPERSTS foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FACT_WORKORDERDAY table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_WORKORDERDAY (
                            TEMPLATEWK          NUMBER NOT NULL,
                            PROVIDERWK          NUMBER NOT NULL,
                            DATEWK              NUMBER NOT NULL,
                            WORKORDERCOUNT      NUMBER NOT NULL,
                            LASTETLPROCESS      NUMBER NOT NULL,
                            LASTRUNINSTANCE     NUMBER NOT NULL,
                            CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                            UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                            PRIMARY KEY(TEMPLATEWK,PROVIDERWK,DATEWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX
                            )
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_WORKORDERDAY table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERDAY_TEMPLATE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDAY
         ADD ( CONSTRAINT FK_F_WORKORDERDAY_TEMPLATE
             FOREIGN KEY(TEMPLATEWK)
             REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDAY_TEMPLATE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERDAY_DATE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDAY
         ADD ( CONSTRAINT FK_F_WORKORDERDAY_DATE
             FOREIGN KEY(DATEWK)
             REFERENCES WFSDW.DIM_DATE(DATEWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDAY_DATE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERDAY_RUNINST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDAY
         ADD ( CONSTRAINT FK_F_WORKORDERDAY_RUNINST
             FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
             REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDAY_RUNINST foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDER_PROVIDER foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERDAY
         ADD ( CONSTRAINT FK_F_WORKORDERDAY_PROVIDER
             FOREIGN KEY(PROVIDERWK)
             REFERENCES WFSDW.DIM_VARIABLEINSTANCE(VARIABLEINSTANCEWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERDAY_PROVIDER foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FACT_WORKORDERMONTH table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_WORKORDERMONTH (
                            TEMPLATEWK          NUMBER NOT NULL,
                            PROVIDERWK          NUMBER NOT NULL,
                            MONTHWK             NUMBER NOT NULL,
                            WORKORDERCOUNT      NUMBER NOT NULL,
                            LASTETLPROCESS      NUMBER NOT NULL,
                            LASTRUNINSTANCE     NUMBER NOT NULL,
                            CREATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                            UPDATEDATE          TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                            PRIMARY KEY(TEMPLATEWK,PROVIDERWK,MONTHWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX
                            )
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_WORKORDERMONTH table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERMONTH_TEMPLATE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERMONTH
         ADD ( CONSTRAINT FK_F_WORKORDERMONTH_TEMPLATE
             FOREIGN KEY(TEMPLATEWK)
             REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERMONTH_TEMPLATE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERMONTH_RUNINST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERMONTH
          ADD ( CONSTRAINT FK_F_WORKORDERMONTH_RUNINST
              FOREIGN KEY(LASTETLPROCESS, LASTRUNINSTANCE)
              REFERENCES WFSDW.MD_ETLPROCESSRUNINSTANCE(ETLPROCESS, RUNINSTANCE)
              ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
              
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERMONTH_RUNINST foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_WORKORDERMONTH_PROVIDER foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERMONTH
         ADD ( CONSTRAINT FK_F_WORKORDERMONTH_PROVIDER
             FOREIGN KEY(PROVIDERWK)
             REFERENCES WFSDW.DIM_VARIABLEINSTANCE(VARIABLEINSTANCEWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERMONTH_PROVIDER foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_WORKORDERMONTH_MONTH foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_WORKORDERMONTH
         ADD ( CONSTRAINT FK_F_WORKORDERMONTH_MONTH
             FOREIGN KEY(MONTHWK)
             REFERENCES WFSDW.DIM_MONTH(MONTHWK)
             ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
             
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_WORKORDERMONTH_MONTH foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FACT_RESOURCEQUEUEDETAIL table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL (
                                   TEMPLATEWK             NUMBER NOT NULL,
                                   WORKORDERWK            NUMBER NOT NULL,
                                   NODEWK                 NUMBER NOT NULL,
                                   LOCALENTERDATEWK       NUMBER NOT NULL,
                                   LOCALENTERTIMEWK       NUMBER NOT NULL,
                                   RESOURCEWK             NUMBER NOT NULL,
                                   RESOURCEGROUPWK        NUMBER NOT NULL,
                                   ADMINSTATUSWK          NUMBER NOT NULL,
                                   OPERSTATUSWK           NUMBER NOT NULL,
                                   DURATION               NUMBER NOT NULL,
                                   WFSCREATEDATE          TIMESTAMP(6) NOT NULL,
                                   LASTPRODUPDATEDATE     TIMESTAMP(6) NOT NULL,
                                   LASTETLPROCESS         NUMBER NOT NULL,
                                   LASTRUNINSTANCE        NUMBER NOT NULL,
                                   CREATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                   UPDATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                   PRIMARY KEY(TEMPLATEWK,WORKORDERWK,NODEWK,LOCALENTERDATEWK,LOCALENTERTIMEWK,RESOURCEWK,RESOURCEGROUPWK,ADMINSTATUSWK,OPERSTATUSWK)
                                  USING INDEX
                                  TABLESPACE WFSDW_INDEX)
                          TABLESPACE WFSDW_DATA';
                          
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_RESOURCEQUEUEDETAIL table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEUEDETAIL_TMPLT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
              ADD ( CONSTRAINT FK_F_RESOUREQUEDETAIL_TMPLT
                  FOREIGN KEY (TEMPLATEWK)
                  REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
                  ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                  
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_TMPLT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDETAIL_WRKORDR foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_WRKORDR
                 FOREIGN KEY (WORKORDERWK)
                 REFERENCES WFSDW.DIM_WORKORDER(WORKORDERWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_WRKORDR foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEUEDETAIL_NODE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_NODE
                 FOREIGN KEY (NODEWK)
                 REFERENCES WFSDW.DIM_NODE(NODEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION 
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_NODE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDETAIL_LCENTDT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_LCENTDT
                 FOREIGN KEY (LOCALENTERDATEWK)
                 REFERENCES WFSDW.DIM_DATE(DATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_LCENTDT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_RESOURCEQUEDETAIL_LCENTDT foreign key
  BEGIN
  
    EXECUTE IMMEDIATE '  ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_LCENTTM
                 FOREIGN KEY (LOCALENTERTIMEWK)
                 REFERENCES WFSDW.DIM_TIME(TIMEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_LCENTTM foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDETAIL_RSRC foreign key constraint
  BEGIN
  
  EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_RSRC
                 FOREIGN KEY (RESOURCEWK)
                 REFERENCES WFSDW.DIM_RESOURCE(RESOURCEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_RSRC foreign key constriant.'')';
      GOTO bypass_install;

  END;
  
  -- Create FK_F_RESOURCEQUEDETAIL_RSRCBRG foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_RSRCBRG
                 FOREIGN KEY (RESOURCEGROUPWK)
                 REFERENCES WFSDW.DIM_RESOURCEGROUP(RESOURCEGROUPWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION 
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_RSRCBRG foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDETAIL_ADMNST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_ADMNST
                 FOREIGN KEY (ADMINSTATUSWK)
                 REFERENCES WFSDW.DIM_WORKORDERSTATUS(WORKORDERSTATUSWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_ADMNST foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDETAIL_OPRST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDETAIL_OPRST
                 FOREIGN KEY (OPERSTATUSWK)
                 REFERENCES WFSDW.DIM_WORKORDERSTATUS(WORKORDERSTATUSWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDETAIL_OPRST foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FACT_RESOURCEQUEUEDAY table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_RESOURCEQUEUEDAY (
                                 TEMPLATEWK             NUMBER NOT NULL,
                                 WORKORDERWK            NUMBER NOT NULL,
                                 NODEWK                 NUMBER NOT NULL,
                                 RESOURCEWK             NUMBER NOT NULL,
                                 RESOURCEGROUPWK        NUMBER NOT NULL,
                                 DATEWK                 NUMBER NOT NULL,
                                 TOTALDURATION          NUMBER NOT NULL,
                                 TOTALQUEUED            NUMBER NOT NULL,
                                 LASTETLPROCESS         NUMBER NOT NULL,
                                 LASTRUNINSTANCE        NUMBER NOT NULL,
                                 CREATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 UPDATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 PRIMARY KEY(TEMPLATEWK,WORKORDERWK,NODEWK,RESOURCEWK,RESOURCEGROUPWK,DATEWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_RESOURCEQUEUEDAY table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDAY_TMPLT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDAY
             ADD ( CONSTRAINT FK_F_RESOUREQUEDAY_TMPLT
                 FOREIGN KEY (TEMPLATEWK)
                 REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDAY_TMPLT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDAY_WRKORDR foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDAY
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDAY_WRKORDR
                 FOREIGN KEY (WORKORDERWK)
                 REFERENCES WFSDW.DIM_WORKORDER(WORKORDERWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDAY_WRKORDR foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDAY_NODE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDAY
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDAY_NODE
                 FOREIGN KEY (NODEWK)
                 REFERENCES WFSDW.DIM_NODE(NODEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDAY_NODE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDAY_DT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDAY
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDAY_DT
                 FOREIGN KEY (DATEWK)
                 REFERENCES WFSDW.DIM_DATE(DATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDAY_DT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDAY_RSRC foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDAY
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDAY_RSRC
                 FOREIGN KEY (RESOURCEWK)
                 REFERENCES WFSDW.DIM_RESOURCE(RESOURCEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDAY_RSRC foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEDAY_RSRCBRG foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEDAY
             ADD ( CONSTRAINT FK_F_RESOURCEQUEDAY_RSRCBRG
                 FOREIGN KEY (RESOURCEGROUPWK)
                 REFERENCES WFSDW.DIM_RESOURCEGROUP(RESOURCEGROUPWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEDAY_RSRCBRG foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FACT_RESOURCEQUEUEMONTH table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_RESOURCEQUEUEMONTH (
                               TEMPLATEWK             NUMBER NOT NULL,
                               WORKORDERWK            NUMBER NOT NULL,
                               NODEWK                 NUMBER NOT NULL,
                               RESOURCEWK             NUMBER NOT NULL,
                               RESOURCEGROUPWK        NUMBER NOT NULL,
                               MONTHWK                NUMBER NOT NULL,
                               TOTALDURATION          NUMBER NOT NULL,
                               TOTALQUEUED            NUMBER NOT NULL,
                               LASTETLPROCESS         NUMBER NOT NULL,
                               LASTRUNINSTANCE        NUMBER NOT NULL,
                               CREATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                               UPDATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                               PRIMARY KEY(TEMPLATEWK,WORKORDERWK,NODEWK,RESOURCEWK,RESOURCEGROUPWK,MONTHWK)
                              USING INDEX
                              TABLESPACE WFSDW_INDEX)
                      TABLESPACE WFSDW_DATA';
                      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_RESOURCEQUEUEMONTH table.'')';
      GOTO bypass_install;
      
  END;
  
    -- Create IDX_FACT_RESQUEUEMNTH index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_FACT_RESQUEUEMNTH
          ON WFSDW.FACT_RESOURCEQUEUEMONTH(MONTHWK, RESOURCEWK)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_FACT_RESQUEUEMNTH index.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEMONTH_TMPLT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEMONTH
             ADD ( CONSTRAINT FK_F_RESOUREQUEMONTH_TMPLT
                 FOREIGN KEY (TEMPLATEWK)
                 REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEMONTH_TMPLT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEMONTH_WRKORDR foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEMONTH
             ADD ( CONSTRAINT FK_F_RESOURCEQUEMONTH_WRKORDR
                 FOREIGN KEY (WORKORDERWK)
                 REFERENCES WFSDW.DIM_WORKORDER(WORKORDERWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEMONTH_WRKORDR foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEMONTH_NODE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEMONTH
             ADD ( CONSTRAINT FK_F_RESOURCEQUEMONTH_NODE
                 FOREIGN KEY (NODEWK)
                 REFERENCES WFSDW.DIM_NODE(NODEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEMONTH_NODE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEMONTH_DT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEMONTH
             ADD ( CONSTRAINT FK_F_RESOURCEQUEMONTH_DT
                 FOREIGN KEY (MONTHWK)
                 REFERENCES WFSDW.DIM_MONTH(MONTHWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEMONTH_DT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEMONTH_RSRC foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEMONTH
             ADD ( CONSTRAINT FK_F_RESOURCEQUEMONTH_RSRC
                 FOREIGN KEY (RESOURCEWK)
                 REFERENCES WFSDW.DIM_RESOURCE(RESOURCEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEMONTH_RSRC foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCEQUEMONTH_RSRCBRG foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCEQUEUEMONTH
             ADD ( CONSTRAINT FK_F_RESOURCEQUEMONTH_RSRCBRG
                 FOREIGN KEY (RESOURCEGROUPWK)
                 REFERENCES WFSDW.DIM_RESOURCEGROUP(RESOURCEGROUPWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCEQUEMONTH_RSRCBRG foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FACT_RESOURCETASKDETAIL table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_RESOURCETASKDETAIL (
                                 TEMPLATEWK             NUMBER NOT NULL,
                                 WORKORDERWK            NUMBER NOT NULL,
                                 NODEWK                 NUMBER NOT NULL,
                                 LOCALENTERDATEWK       NUMBER NOT NULL,
                                 LOCALENTERTIMEWK       NUMBER NOT NULL,
                                 LOCALLEAVEDATEWK       NUMBER NOT NULL,
                                 LOCALLEAVETIMEWK       NUMBER NOT NULL,
                                 RESOURCEWK             NUMBER NOT NULL,
                                 RESOURCEGROUPWK        NUMBER NOT NULL,
                                 ADMINSTATUSWK          NUMBER NOT NULL,
                                 OPERSTATUSWK           NUMBER NOT NULL,
                                 DURATION               NUMBER NOT NULL,
                                 WFSCREATEDATE          TIMESTAMP(6) NOT NULL,
                                 LASTPRODUPDATEDATE     TIMESTAMP(6) NOT NULL,
                                 LASTETLPROCESS         NUMBER NOT NULL,
                                 LASTRUNINSTANCE        NUMBER NOT NULL,
                                 CREATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 UPDATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 PRIMARY KEY(TEMPLATEWK,
                                             WORKORDERWK,
                                             NODEWK,
                                             LOCALENTERDATEWK,
                                             LOCALENTERTIMEWK,
                                             LOCALLEAVEDATEWK,
                                             LOCALLEAVETIMEWK,
                                             RESOURCEWK,
                                             RESOURCEGROUPWK,
                                             ADMINSTATUSWK,
                                             OPERSTATUSWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_RESOURCETASKDETAIL table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_TMPLT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURETSKDETAIL_TMPLT
                 FOREIGN KEY (TEMPLATEWK)
                 REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_TMPLT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_WRKORDR foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_WRKORDR
                 FOREIGN KEY (WORKORDERWK)
                 REFERENCES WFSDW.DIM_WORKORDER(WORKORDERWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_WRKORDR foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_NODE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_NODE
                 FOREIGN KEY (NODEWK)
                 REFERENCES WFSDW.DIM_NODE(NODEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_NODE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_LCENTDT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_LCENTDT
                 FOREIGN KEY (LOCALENTERDATEWK)
                 REFERENCES WFSDW.DIM_DATE(DATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
  
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_LCENTDT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_LCENTTM foreign key constraint.
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_LCENTTM
                 FOREIGN KEY (LOCALENTERTIMEWK)
                 REFERENCES WFSDW.DIM_TIME(TIMEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_LCENTTM foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_LCLVDT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_LCLVDT
                 FOREIGN KEY (LOCALLEAVEDATEWK)
                 REFERENCES WFSDW.DIM_DATE(DATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_LCLVDT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_LCLVTM foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_LCLVTM
                 FOREIGN KEY (LOCALLEAVETIMEWK)
                 REFERENCES WFSDW.DIM_TIME(TIMEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_LCLVTM foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_RESOURCETSKDETAIL_RSRC foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_RSRC
                 FOREIGN KEY (RESOURCEWK)
                 REFERENCES WFSDW.DIM_RESOURCE(RESOURCEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_RSRC foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_RSRCBRG foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_RSRCBRG
                 FOREIGN KEY (RESOURCEGROUPWK)
                 REFERENCES WFSDW.DIM_RESOURCEGROUP(RESOURCEGROUPWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_RSRCBRG foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDETAIL_ADMST forein key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_ADMST
                 FOREIGN KEY (ADMINSTATUSWK)
                 REFERENCES WFSDW.DIM_WORKORDERSTATUS(WORKORDERSTATUSWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_ADMST foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_RESOURCETSKDETAIL_ADMST foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDETAIL
             ADD ( CONSTRAINT FK_F_RESOURCETSKDETAIL_OPRST
                 FOREIGN KEY (OPERSTATUSWK)
                 REFERENCES WFSDW.DIM_WORKORDERSTATUS(WORKORDERSTATUSWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDETAIL_OPRST foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FACT_RESOURCETASKDAY table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_RESOURCETASKDAY (
                                 TEMPLATEWK             NUMBER          NOT NULL,
                                 WORKORDERWK            NUMBER          NOT NULL,
                                 NODEWK                 NUMBER          NOT NULL,
                                 RESOURCEWK             NUMBER          NOT NULL,
                                 RESOURCEGROUPWK        NUMBER          NOT NULL,
                                 DATEWK                 NUMBER          NOT NULL,
                                 TOTALDURATION          NUMBER          NOT NULL,
                                 TOTALTASKS             NUMBER          NOT NULL,
                                 TOTALERRORS            NUMBER          NOT NULL,
                                 LASTETLPROCESS         NUMBER          NOT NULL,
                                 LASTRUNINSTANCE        NUMBER          NOT NULL,
                                 CREATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 UPDATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 PRIMARY KEY(TEMPLATEWK,WORKORDERWK,NODEWK,RESOURCEWK,RESOURCEGROUPWK,DATEWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_RESOURCETASKDAY table.'')';
      GOTO bypass_install;

  END;
  
  -- Create FK_F_RESOURCETSKDAY_TMPLT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDAY
             ADD ( CONSTRAINT FK_F_RESOURETSKDAY_TMPLT
                 FOREIGN KEY (TEMPLATEWK)
                 REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDAY_TMPLT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDAY_WORKORDR foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDAY
             ADD ( CONSTRAINT FK_F_RESOURCETSKDAY_WRKORDR
                 FOREIGN KEY (WORKORDERWK)
                 REFERENCES WFSDW.DIM_WORKORDER(WORKORDERWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN

      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDAY_WRKORDR foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDAY_NODE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDAY
             ADD ( CONSTRAINT FK_F_RESOURCETSKDAY_NODE
                 FOREIGN KEY (NODEWK)
                 REFERENCES WFSDW.DIM_NODE(NODEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDAY_NODE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDAY_DT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDAY
             ADD ( CONSTRAINT FK_F_RESOURCETSKDAY_DT
                 FOREIGN KEY (DATEWK)
                 REFERENCES WFSDW.DIM_DATE(DATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDAY_DT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDAY_RSRC foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDAY
             ADD ( CONSTRAINT FK_F_RESOURCETSKDAY_RSRC
                 FOREIGN KEY (RESOURCEWK)
                 REFERENCES WFSDW.DIM_RESOURCE(RESOURCEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDAY_RSRC foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKDAY_RSRCBRG foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKDAY
             ADD ( CONSTRAINT FK_F_RESOURCETSKDAY_RSRCBRG
                 FOREIGN KEY (RESOURCEGROUPWK)
                 REFERENCES WFSDW.DIM_RESOURCEGROUP(RESOURCEGROUPWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKDAY_RSRCBRG foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FACT_RESOURCETASKMONTH table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_RESOURCETASKMONTH (
                                 TEMPLATEWK             NUMBER NOT NULL,
                                 WORKORDERWK            NUMBER NOT NULL,
                                 NODEWK                 NUMBER NOT NULL,
                                 RESOURCEWK             NUMBER NOT NULL,
                                 RESOURCEGROUPWK        NUMBER NOT NULL,
                                 MONTHWK                NUMBER NOT NULL,
                                 TOTALDURATION          NUMBER NOT NULL,
                                 TOTALTASKS             NUMBER NOT NULL,
                                 TOTALERRORS            NUMBER NOT NULL,
                                 LASTETLPROCESS         NUMBER NOT NULL,
                                 LASTRUNINSTANCE        NUMBER NOT NULL,
                                 CREATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 UPDATEDATE             TIMESTAMP(6) DEFAULT (systimestamp) NOT NULL,
                                 PRIMARY KEY(TEMPLATEWK,WORKORDERWK,NODEWK,RESOURCEWK,RESOURCEGROUPWK,MONTHWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_RESOURCETASKMONTH table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create IDX_FACT_RESTASKMNTH index
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE INDEX WFSDW.IDX_FACT_RESTASKMNTH
          ON WFSDW.FACT_RESOURCETASKMONTH(MONTHWK, RESOURCEWK)
      TABLESPACE WFSDW_INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 )
      PARALLEL( DEGREE 1 )
      LOGGING';
      
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create IDX_FACT_RESTASKMNTH index.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_RESOURCETSKMONTH_TMPLT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKMONTH
             ADD ( CONSTRAINT FK_F_RESOURETSKMONTH_TMPLT
                 FOREIGN KEY (TEMPLATEWK)
                 REFERENCES WFSDW.DIM_TEMPLATE(TEMPLATEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';

  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKMONTH_TMPLT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKMONTH_WRKORDR foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKMONTH
             ADD ( CONSTRAINT FK_F_RESOURCETSKMONTH_WRKORDR
                 FOREIGN KEY (WORKORDERWK)
                 REFERENCES WFSDW.DIM_WORKORDER(WORKORDERWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKMONTH_WRKORDR foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKMONTH_NODE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKMONTH
             ADD ( CONSTRAINT FK_F_RESOURCETSKMONTH_NODE
                 FOREIGN KEY (NODEWK)
                 REFERENCES WFSDW.DIM_NODE(NODEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKMONTH_NODE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKMONTH_DT foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKMONTH
             ADD ( CONSTRAINT FK_F_RESOURCETSKMONTH_DT
                 FOREIGN KEY (MONTHWK)
                 REFERENCES WFSDW.DIM_MONTH(MONTHWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION 
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKMONTH_DT foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKMONTH_RSRC foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKMONTH
             ADD ( CONSTRAINT FK_F_RESOURCETSKMONTH_RSRC
                 FOREIGN KEY (RESOURCEWK)
                 REFERENCES WFSDW.DIM_RESOURCE(RESOURCEWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKMONTH_RSRC foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_RESOURCETSKMONTH_RSRCBRG foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_RESOURCETASKMONTH
             ADD ( CONSTRAINT FK_F_RESOURCETSKMONTH_RSRCBRG
                 FOREIGN KEY (RESOURCEGROUPWK)
                 REFERENCES WFSDW.DIM_RESOURCEGROUP(RESOURCEGROUPWK)
                 ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                 
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_RESOURCETSKMONTH_RSRCBRG foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FACT_MDHISTORYDETAIL table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_MDHISTORYDETAIL (
                                MONITOREDDRIVEWK        NUMBER  NOT NULL,
                                MONITOREDDRIVESTATUSWK  NUMBER  NOT NULL,
                                LOCALDATEWK             NUMBER  NOT NULL,
                                LOCALTIMEWK             NUMBER  NOT NULL,
                                CURRENTBYTESUSED        NUMBER  NOT NULL,
                                LASTETLPROCESS          NUMBER  NOT NULL,
                                LASTRUNINSTANCE         NUMBER  NOT NULL,
                                CREATEDATE              TIMESTAMP(6)    DEFAULT(systimestamp)   NOT NULL,
                                UPDATEDATE              TIMESTAMP(6)    DEFAULT(systimestamp)   NOT NULL,
                                PRIMARY KEY(MONITOREDDRIVEWK,MONITOREDDRIVESTATUSWK,LOCALDATEWK,LOCALTIMEWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_MDHISTORYDETAIL table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_MDHISTORY_MONDRIVE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYDETAIL
             ADD( CONSTRAINT FK_F_MDHISTORY_MONDRIVE
                FOREIGN KEY (MONITOREDDRIVEWK)
                REFERENCES WFSDW.DIM_MONITOREDDRIVE(MONITOREDDRIVEWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORY_MONDRIVE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_MDHISTORY_MDSTATUS foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYDETAIL
             ADD( CONSTRAINT FK_F_MDHISTORY_MDSTATUS
                FOREIGN KEY (MONITOREDDRIVESTATUSWK)
                REFERENCES WFSDW.DIM_MONITOREDDRIVESTATUS(MONITOREDDRIVESTATUSWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORY_MDSTATUS foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_MDHISTORY_DATE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYDETAIL
             ADD( CONSTRAINT FK_F_MDHISTORY_DATE
                FOREIGN KEY (LOCALDATEWK)
                REFERENCES WFSDW.DIM_DATE(DATEWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORY_DATE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_MDHISTORY_TIME foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYDETAIL
             ADD( CONSTRAINT FK_F_MDHISTORY_TIME
                FOREIGN KEY (LOCALTIMEWK)
                REFERENCES WFSDW.DIM_TIME(TIMEWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORY_TIME foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FACT_MDHISTORYDAY table
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_MDHISTORYDAY (
                                MONITOREDDRIVEWK        NUMBER          NOT NULL,
                                MONITOREDDRIVESTATUSWK  NUMBER          NOT NULL,
                                LOCALDATEWK             NUMBER          NOT NULL,
                                BYTESUSED               NUMBER          NOT NULL,
                                PERCENTUSED             NUMBER(6,3)     NOT NULL,
                                LASTETLPROCESS          NUMBER          NOT NULL,
                                LASTRUNINSTANCE         NUMBER          NOT NULL,
                                CREATEDATE              TIMESTAMP(6)    DEFAULT(systimestamp)   NOT NULL,
                                UPDATEDATE              TIMESTAMP(6)    DEFAULT(systimestamp)   NOT NULL,
                                PRIMARY KEY(MONITOREDDRIVEWK,MONITOREDDRIVESTATUSWK,LOCALDATEWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_MDHISTORYDAY table.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_MDHISTORYDAY_MONDRIVE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYDAY
             ADD( CONSTRAINT FK_F_MDHISTORYDAY_MONDRIVE
                FOREIGN KEY (MONITOREDDRIVEWK)
                REFERENCES WFSDW.DIM_MONITOREDDRIVE(MONITOREDDRIVEWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORYDAY_MONDRIVE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_MDHISTORYDAY_MDSTATUS foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYDAY
             ADD( CONSTRAINT FK_F_MDHISTORYDAY_MDSTATUS
                FOREIGN KEY (MONITOREDDRIVESTATUSWK)
                REFERENCES WFSDW.DIM_MONITOREDDRIVESTATUS(MONITOREDDRIVESTATUSWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORYDAY_MDSTATUS foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FK_F_MDHISTORYDAY_DATE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYDAY
             ADD( CONSTRAINT FK_F_MDHISTORYDAY_DATE
                FOREIGN KEY (LOCALDATEWK)
                REFERENCES WFSDW.DIM_DATE(DATEWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORYDAY_DATE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create FACT_MDHISTORYMONTH table
  BEGIN

    EXECUTE IMMEDIATE 'CREATE TABLE WFSDW.FACT_MDHISTORYMONTH (
                                MONITOREDDRIVEWK        NUMBER          NOT NULL,
                                MONTHWK                 NUMBER          NOT NULL,
                                MAXPERCENTUSED             NUMBER(10,3)     NOT NULL,
                                LASTETLPROCESS          NUMBER          NOT NULL,
                                LASTRUNINSTANCE         NUMBER          NOT NULL,
                                CREATEDATE              TIMESTAMP(6)    DEFAULT(systimestamp)   NOT NULL,
                                UPDATEDATE              TIMESTAMP(6)    DEFAULT(systimestamp)   NOT NULL,
                                PRIMARY KEY(MONITOREDDRIVEWK,MONTHWK)
                                USING INDEX
                                TABLESPACE WFSDW_INDEX)
                        TABLESPACE WFSDW_DATA';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FACT_MDHISTORYMONTH table.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_MDHISTORYMONTH_MONDRIVE foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYMONTH
             ADD( CONSTRAINT FK_F_MDHISTORYMONTH_MONDRIVE
                FOREIGN KEY (MONITOREDDRIVEWK)
                REFERENCES WFSDW.DIM_MONITOREDDRIVE(MONITOREDDRIVEWK)
                ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
                
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORYMONTH_MONDRIVE foreign key constriant.'')';
      GOTO bypass_install;
      
  END;

  -- Create FK_F_MDHISTORYMONTH_MONTH foreign key constraint
  BEGIN
  
    EXECUTE IMMEDIATE 'ALTER TABLE WFSDW.FACT_MDHISTORYMONTH
            ADD( CONSTRAINT FK_F_MDHISTORYMONTH_MONTH
               FOREIGN KEY (MONTHWK)
               REFERENCES WFSDW.DIM_MONTH(MONTHWK)
               ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )';
               
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create FK_F_MDHISTORYMONTH_MONTH foreign key constriant.'')';
      GOTO bypass_install;
      
  END;
  
/*
--------------------------------------------------------------------------------
                         Create Sequences
--------------------------------------------------------------------------------
*/
  
  -- Create Stage I Sequences
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_LOG_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 100
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_LOG_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create STG1_DIFF_MDRIVEHISTORY_SEQ sequence
  BEGIN
  
  EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_MDRIVEHISTORY_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 100
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_MDRIVEHISTORY_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create STG1_DIFF_MONITOREDDRIVE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_MONITOREDDRIVE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
  
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_MONITOREDDRIVE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create STG1_DIFF_NODE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_NODE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_NODE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create STG1_DIFF_RESOURCE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_RESOURCE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_RESOURCE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create STG1_DIFF_RESOURCEGROUP_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_RESOURCEGROUP_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_RESOURCEGROUP_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create STG1_DIFF_TEMPLATE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_TEMPLATE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_TEMPLATE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create STG1_DIFF_VARIABLEINSTANCE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_VARIABLEINSTANCE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_VARIABLEINSTANCE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create STG1_DIFF_WORKORDER_SEQ sequence
  BEGIN
       
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_WORKORDER_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_WORKORDER_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create STG1_DIFF_WORKORDERSTATUS_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.STG1_DIFF_WORKORDERSTATUS_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        CACHE 20
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create STG1_DIFF_WORKORDERSTATUS_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
              
  -- Create DIM_RESOURCE_SEQ Sequences
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_RESOURCE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_RESOURCE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create DIM_RESOURCEGROUP_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_RESOURCEGROUP_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
        
  EXCEPTION
    WHEN OTHERS THEN
    
        wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_RESOURCEGROUP_SEQ sequence.'')';
      GOTO bypass_install;
  
  END;
                    
  -- Create DIM_MONITOREDDRIVE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_MONITOREDDRIVE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_MONITOREDDRIVE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_MONITOREDDRIVESTATUS_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_MONITOREDDRIVESTATUS_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_MONITOREDDRIVESTATUS_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create DIM_TEMPLATE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_TEMPLATE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_TEMPLATE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create DIM_NODE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_NODE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_NODE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create DIM_WORKORDER_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_WORKORDER_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_WORKORDER_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Create DIM_WORKORDERSTATUS_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_WORKORDERSTATUS_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_WORKORDERSTATUS_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
                    
  -- Create DIM_VARIABLEINSTANCE_SEQ sequence
  BEGIN
  
    EXECUTE IMMEDIATE 'CREATE SEQUENCE WFSDW.DIM_VARIABLEINSTANCE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        NOMAXVALUE
                        NOMINVALUE
                        NOCYCLE
                        NOCACHE
                        NOORDER';
                        
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to create DIM_VARIABLEINSTANCE_SEQ sequence.'')';
      GOTO bypass_install;
      
  END;
  
  -- Insert Release version number into SYSTEM_SETTINGS table.
  BEGIN
  
    EXECUTE IMMEDIATE 'INSERT INTO WFSDW.SYSTEM_SETTINGS(SETTING_TYPE, SETTING_VALUE)
                       VALUES(''RELEASE'',''2.7'')';
                       
  EXCEPTION
    WHEN OTHERS THEN
    
      wk_message := 'ERROR';
      EXECUTE IMMEDIATE 'INSERT INTO install_table values
         (sysdate, ''error in installing of data warehouse objects:
           Unable to insert the RELEASE record into SYSTEM SETTINGS.'')';
      GOTO bypass_install;
      
  END;

  <<bypass_install>>

  COMMIT;

  IF wk_message = 'ERROR' THEN

     NULL;

  ELSE

     EXECUTE IMMEDIATE 'INSERT INTO install_table values
   (sysdate, ''Installation completed SUCCESSFULLY'')';

  END IF;
  
END createWFS_DW;

END createWFSDWDB;
/
