CREATE TABLE ETL_ALLOCATION_DETAIL (
       ALLOCATION_DETAIL_ID numeric NOT NULL,
       ALLOCATION_VERSION   numeric NOT NULL,
       ALLOCATION_MATCH_VALUE varchar(1024) NOT NULL,
       ALLOCATION_COMPARISON_METHOD varchar(512) NULL,
       ALLOCATION_METHOD    varchar(255) NULL,
       ALLOCATION_RATE      varchar(255) NULL,
       ALLOCATION_AMOUNT    varchar(255) NULL,
       ALLOCATION_FORMULA   varchar(512) NULL,
       ALLOCATION_MULTIPLIER varchar(255) NULL,
       ALLOCATION_DIVISOR   varchar(255) NULL,
       ALLOCATION_FUNCTION  varchar(255) NULL,
       ALLOCATION_APPLICATION varchar(512) NULL,
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_ALLOCATION_DETAIL ON ETL_ALLOCATION_DETAIL
(
       ALLOCATION_VERSION,
       ALLOCATION_DETAIL_ID
)
go

CREATE INDEX XIF4ETL_ALLOCATION_DETAIL ON ETL_ALLOCATION_DETAIL
(
       ALLOCATION_VERSION
)
go


CREATE TABLE ETL_ALLOCATION_METHOD (
       ALLOCATION_VERSION   numeric NOT NULL,
       ALLOCATION_NAME      varchar(50) NOT NULL,
       ALLOCATION_DESCRIPTION varchar(255) NOT NULL,
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__2
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_ALLOCATION_METHOD ON ETL_ALLOCATION_METHOD
(
       ALLOCATION_VERSION
)
go


CREATE TABLE ETL_BATCH (
       BATCH_ID             numeric NOT NULL,
       BATCH_NAME           varchar(50) NOT NULL,
       BATCH_DESCRIPTION    varchar(255) NOT NULL,
       BATCH_REASON         varchar(255) NOT NULL,
       ENVIRONMENT          varchar(50) NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_BATCH ON ETL_BATCH
(
       BATCH_ID
)
go


CREATE TABLE ETL_CURRENCY_CONVERSION (
       CURRENCY_CONVERSION_VERSION_ID numeric NOT NULL,
       CURRENCY_CONVERSION_NAME varchar(255) NOT NULL,
       CURRENCY_CONVERSION_DESC varchar(512) NOT NULL,
       CONVERSION_FACTOR    numeric(18,6) NOT NULL,
       CONVERSION_EFFECTIVE_DATE datetime NOT NULL,
       CONVERSION_FUNCTION  varchar(512) NULL,
       CONVERSION_SOURCE    varchar(255) NULL,
       CURRENCY_BASE        varchar(255) NOT NULL,
       CURRENCY_TARGET      varchar(255) NULL,
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__3
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_CURRENCY_CONVERSION ON ETL_CURRENCY_CONVERSION
(
       CURRENCY_CONVERSION_VERSION_ID
)
go


CREATE TABLE ETL_ERROR_FACT (
       BATCH_ID             numeric NOT NULL,
       SCREEN_ID            numeric NOT NULL,
       EVENT_DATE_ID        numeric NOT NULL,
       ERROR_FACT_RECORD_NUMBER text NOT NULL,
       FINAL_SEVERITY_SCORE numeric(18,2) NULL,
       FACT_AUDIT_ID        numeric NULL,
       REMEDIATION_ACTION   varchar(2048) NULL,
       REMEDIATION_STATUS   varchar(255) NULL,
       REMEDIATION_ASSIGNED_TO varchar(50) NULL,
       REMEDIATION_ASSIGNED_DATE datetime NULL,
       REMEDIATION_DUE_BY_DATE datetime NULL,
       REMEDIATION_CMP_DATE datetime NULL,
       REMEDIATION_CMP_BY   varchar(50) NULL
)
go

CREATE INDEX XIF6ETL_ERROR_FACT ON ETL_ERROR_FACT
(
       SCREEN_ID
)
go

CREATE INDEX XIF7ETL_ERROR_FACT ON ETL_ERROR_FACT
(
       BATCH_ID
)
go

CREATE INDEX XIF8ETL_ERROR_FACT ON ETL_ERROR_FACT
(
       FACT_AUDIT_ID
)
go


CREATE TABLE ETL_FACT_AUDIT (
       FACT_AUDIT_ID        numeric NOT NULL,
       OVERALL_QUALITY_CATEGORY varchar(255) NULL,
       OVERALL_QUALITY_SCORE numeric(18,4) NULL,
       COMPLETENESS_CATEGORY varchar(255) NULL,
       COMPLETENESS_SEVERITY_SCORE numeric(18,4) NOT NULL,
       COMPLETENESS_SCREEN_COUNT numeric NULL,
       COMPLETENESS_SCREENS_FAILED numeric NULL,
       VALIDATION_CATEGORY  varchar(255) NULL,
       VALIDATION_SEVERITY_SCORE numeric(18,4) NULL,
       VALIDATION_SCREEN_COUNT numeric NULL,
       VALIDATION_SCREENS_FAILED numeric NULL,
       OUT_OF_BOUNDS_CATEGORY varchar(255) NULL,
       OUT_OF_BOUNDS_SEVERITY_SCORE numeric(18,4) NULL,
       OUT_OF_BOUNDS_SCREEN_COUNT numeric NULL,
       OUT_OF_BOUNDS_SCREENS_FAILED numeric NULL,
       TOTAL_SCREENS_FAILED numeric NULL,
       MAX_SEVERITY_SCORE   numeric(18,4) NULL,
       EXTRACT_DATETIME     datetime NULL,
       CLEAN_DATETIME       datetime NULL,
       CONFORM_DATETIME     datetime NULL,
       ALLOCATION_VERSION   numeric NULL,
       ETL_SYSTEM_VERSION_ID numeric(18,4) NOT NULL,
       CURRENCY_CONVERSION_VERSION_ID numeric NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_FACT_AUDIT ON ETL_FACT_AUDIT
(
       FACT_AUDIT_ID
)
go

CREATE INDEX XIF10ETL_FACT_AUDIT ON ETL_FACT_AUDIT
(
       CURRENCY_CONVERSION_VERSION_ID
)
go

CREATE INDEX XIF5ETL_FACT_AUDIT ON ETL_FACT_AUDIT
(
       ETL_SYSTEM_VERSION_ID
)
go


CREATE TABLE ETL_FILE_FIELD_LAYOUT (
       SOURCE_ID            numeric NOT NULL,
       FIELD_ID             numeric NOT NULL,
       FIELD_NAME           varchar(255) NOT NULL,
       CHARACTER_LENGTH     numeric NOT NULL,
       START_POSITION       numeric NULL,
       END_POSITION         numeric NULL,
       FIELD_DATA_CLASS     varchar(50) NULL,
       REQUIRED_YN          char(1) NULL
                                   CONSTRAINT DEFAULT___N__4
                                          DEFAULT ('N'),
       COMMENTS             varchar(512) NULL,
       VALIDATION_RULE_SET_ID numeric NULL,
       DELIMETER_STRING     varchar(50) NULL,
       CONVERSION_FUNCTION  varchar(1024) NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_FILE_FIELD_LAYOUT ON ETL_FILE_FIELD_LAYOUT
(
       SOURCE_ID,
       FIELD_ID
)
go

CREATE INDEX XIF9ETL_FILE_FIELD_LAYOUT ON ETL_FILE_FIELD_LAYOUT
(
       SOURCE_ID
)
go


CREATE TABLE ETL_PROCESS_LOG (
       ETL_PROCESS_LOG_ID   numeric NOT NULL,
       PROJECT_NAME         varchar(255) NOT NULL,
       HOST_NAME            varchar(255) NOT NULL,
       JOB_NAME             varchar(255) NOT NULL,
       DATA_SOURCE_ID       varchar(255) NOT NULL,
       JOB_START_DATE       datetime NOT NULL,
       JOB_END_DATE         datetime NULL,
       RECORDS_IN           numeric(18) NULL,
       RECORDS_OUT          numeric(18) NULL,
       RECORDS_ERROR        numeric(18) NULL,
       RECORDS_SUSPENDED    numeric(18) NULL,
       IS_JOB_STARTED       char(1) NULL
                                   CONSTRAINT DEFAULT___N__5
                                          DEFAULT ('N'),
       IS_JOB_COMPLETED     char(1) NULL
                                   CONSTRAINT DEFAULT___N__6
                                          DEFAULT ('N'),
       IS_JOB_INERROR       char(1) NULL
                                   CONSTRAINT DEFAULT___N__7
                                          DEFAULT ('N'),
       IS_JOB_SUSPENDED     char(1) NULL
                                   CONSTRAINT DEFAULT___N__8
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_PROCESS_LOG ON ETL_PROCESS_LOG
(
       ETL_PROCESS_LOG_ID
)
go


CREATE TABLE ETL_SCREEN (
       SCREEN_ID            numeric NOT NULL,
       SOURCE_SYSTEM_ID     numeric NOT NULL,
       TABLE_ID             numeric NOT NULL,
       SCREEN_TYPE          varchar(255) NOT NULL,
       SCREEN_CATEGORY      varchar(255) NOT NULL,
       ETL_STAGE            varchar(255) NOT NULL,
       PROCESSING_ORDER_NUMBER numeric(18,4) NOT NULL,
       DEFAULT_SEVERITY_SCORE numeric(18,4) NOT NULL,
       EXCEPTION_ACTION     varchar(255) NOT NULL,
       SCREEN_PASSED        char(1) NULL
                                   CONSTRAINT DEFAULT___N__9
                                          DEFAULT ('N'),
       VALIDATION_RULE_SET_ID numeric NULL,
       ABORT_ON_FAIL        char(1) NULL
                                   CONSTRAINT DEFAULT___N__10
                                          DEFAULT ('N'),
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__11
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_SCREEN ON ETL_SCREEN
(
       SCREEN_ID
)
go


CREATE TABLE ETL_SOURCE (
       SOURCE_ID            numeric NOT NULL,
       SOURCE_SYSTEM_ID     numeric NOT NULL,
       SOURCE_DESCRIPTION   varchar(512) NOT NULL,
       LOGICAL_SOURCE_STAGING_NAME varchar(255) NOT NULL,
       SOURCE_TYPE          varchar(255) NOT NULL,
       SOURCE_SYSTEM_NAME   varchar(255) NOT NULL,
       SOURCE_OWNER         varchar(255) NOT NULL,
       STAGING_FILENAME     varchar(255) NOT NULL,
       SOURCE_SYSTEM_FILENAME varchar(255) NOT NULL,
       CONTACT              varchar(255) NOT NULL,
       DATA_MOVEMENT_MECHANISM varchar(255) NULL,
       CONNECTION_STRING    varchar(255) NULL,
       CONNECTION_USERID    varchar(255) NULL,
       CONNECTION_PW        varchar(255) NULL,
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__12
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_SOURCE ON ETL_SOURCE
(
       SOURCE_ID
)
go

CREATE INDEX XIF1ETL_SOURCE ON ETL_SOURCE
(
       SOURCE_SYSTEM_ID
)
go


CREATE TABLE ETL_SOURCE_SYSTEM (
       SOURCE_SYSTEM_ID     numeric NOT NULL,
       SOURCE_SYSTEM_NAME   varchar(255) NOT NULL,
       DATA_STEWARD         varchar(255) NOT NULL,
       OWNER                varchar(255) NOT NULL,
       BUSINESS_AREA        varchar(255) NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_SOURCE_SYSTEM ON ETL_SOURCE_SYSTEM
(
       SOURCE_SYSTEM_ID
)
go


CREATE TABLE ETL_TABLE (
       TABLE_ID             numeric NOT NULL,
       NEXT_RECORD_VALUE    numeric NULL
                                   CONSTRAINT DEFAULT_1
                                          DEFAULT 1,
       TABLE_NAME           varchar(128) NOT NULL,
       IS_OBSOLETE          char(1) NULL
                                   CONSTRAINT DEFAULT___N__13
                                          DEFAULT ('N'),
       ESTIMATED_ROW_COUNT  numeric NOT NULL
                                   CONSTRAINT DEFAULT_0
                                          DEFAULT 0,
       LAST_UPDATED         datetime NULL
                                   CONSTRAINT DEFAULT_sysdate
                                          DEFAULT sysdate,
       PRIMARY_KEY_COLUMNS  varchar(255) NULL,
       AVERAGE_ROW_LENGTH   numeric NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_TABLE ON ETL_TABLE
(
       TABLE_ID
)
go


CREATE TABLE ETL_VALIDATION_RULE (
       VALIDATION_RULE_ID   numeric NOT NULL,
       VALIDATION_RULE_SET_NAME varchar(255) NOT NULL,
       RULE_FIRE_PREREQUISITE_TEST varchar(1024) NOT NULL,
       RULE_TEST_LOGIC      varchar(1024) NULL,
       RULE_TEST_APPLICATION varchar(512) NOT NULL,
       EXPECTED_RESULT_VALUE varchar(255) NULL,
       EXPECTED_RESULT_APPLICATION varchar(512) NOT NULL,
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__14
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_VALIDATION_RULE ON ETL_VALIDATION_RULE
(
       VALIDATION_RULE_ID
)
go


CREATE TABLE ETL_VALIDATION_RULE_SET (
       VALIDATION_RULE_SET_ID numeric NOT NULL,
       VALIDATION_RULE_SET_NAME varchar(255) NOT NULL,
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__15
                                          DEFAULT ('N')
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_VALIDATION_RULE_SET ON ETL_VALIDATION_RULE_SET
(
       VALIDATION_RULE_SET_ID
)
go


CREATE TABLE ETL_VALIDATION_RULE_SET_RULE (
       VALIDATION_RULE_SET_ID numeric NOT NULL,
       VALIDATION_RULE_ID   numeric NOT NULL,
       IS_DISABLED          char(1) NULL
                                   CONSTRAINT DEFAULT___N__16
                                          DEFAULT ('N'),
       EXCEPTION_ACTION     varchar(255) NOT NULL,
       LOG_OUTPUT_TO        varchar(255) NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_VALIDATION_RULE_SET_RULE ON ETL_VALIDATION_RULE_SET_RULE
(
       VALIDATION_RULE_SET_ID,
       VALIDATION_RULE_ID
)
go

CREATE INDEX XIF2ETL_VALIDATION_RULE_SET_RULE ON ETL_VALIDATION_RULE_SET_RULE
(
       VALIDATION_RULE_SET_ID
)
go

CREATE INDEX XIF3ETL_VALIDATION_RULE_SET_RULE ON ETL_VALIDATION_RULE_SET_RULE
(
       VALIDATION_RULE_ID
)
go


CREATE TABLE ETL_VERSION (
       ETL_SYSTEM_VERSION_ID numeric(18,4) NOT NULL,
       ETL_SYSTEM_NAME      varchar(255) NOT NULL,
       ETL_SYSTEM_VERSION_DESCRIPTION varchar(1024) NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKETL_VERSION ON ETL_VERSION
(
       ETL_SYSTEM_VERSION_ID
)
go