CREATE TABLE ETL_ALLOCATION_DETAIL (
       ALLOCATION_DETAIL_ID NUMBER NOT NULL,
       ALLOCATION_VERSION   NUMBER NOT NULL,
       ALLOCATION_MATCH_VALUE VARCHAR2(1024) NOT NULL,
       ALLOCATION_COMPARISON_METHOD VARCHAR2(512) NULL,
       ALLOCATION_METHOD    VARCHAR2(255) NULL,
       ALLOCATION_RATE      VARCHAR2(255) NULL,
       ALLOCATION_AMOUNT    VARCHAR2(255) NULL,
       ALLOCATION_FORMULA   VARCHAR2(512) NULL,
       ALLOCATION_MULTIPLIER VARCHAR2(255) NULL,
       ALLOCATION_DIVISOR   VARCHAR2(255) NULL,
       ALLOCATION_FUNCTION  VARCHAR2(255) NULL,
       ALLOCATION_APPLICATION VARCHAR2(512) NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL
);

COMMENT ON COLUMN ETL_ALLOCATION_DETAIL.IS_DISABLED IS 'required: domain[Y,N]; used to temporarily inactivate a formerly active allocation detail';
CREATE UNIQUE INDEX XPKETL_ALLOCATION_DETAIL ON ETL_ALLOCATION_DETAIL
(
       ALLOCATION_VERSION             ASC,
       ALLOCATION_DETAIL_ID           ASC
);

CREATE INDEX XIF4ETL_ALLOCATION_DETAIL ON ETL_ALLOCATION_DETAIL
(
       ALLOCATION_VERSION             ASC
);


CREATE TABLE ETL_ALLOCATION_METHOD (
       ALLOCATION_VERSION   NUMBER NOT NULL,
       ALLOCATION_NAME      VARCHAR2(50) NOT NULL,
       ALLOCATION_DESCRIPTION VARCHAR2(255) NOT NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL
);

CREATE UNIQUE INDEX XPKETL_ALLOCATION_METHOD ON ETL_ALLOCATION_METHOD
(
       ALLOCATION_VERSION             ASC
);


CREATE TABLE ETL_BATCH (
       BATCH_ID             NUMBER NOT NULL,
       BATCH_NAME           VARCHAR2(50) NOT NULL,
       BATCH_DESCRIPTION    VARCHAR2(255) NOT NULL,
       BATCH_REASON         VARCHAR2(255) NOT NULL,
       ENVIRONMENT          VARCHAR2(50) NOT NULL
);

COMMENT ON COLUMN ETL_BATCH.BATCH_ID IS 'uniquely identifies a batch of ETL processing;';
COMMENT ON COLUMN ETL_BATCH.BATCH_NAME IS 'a short name for referencing a batch';
COMMENT ON COLUMN ETL_BATCH.BATCH_DESCRIPTION IS 'a verbose description of a batch to provide as much detail as needed';
COMMENT ON COLUMN ETL_BATCH.BATCH_REASON IS 'the reason for executing this ETL batch';
COMMENT ON COLUMN ETL_BATCH.ENVIRONMENT IS 'identifies which environment: Dev, QA, Prod from which this batch was launched';
CREATE UNIQUE INDEX XPKETL_BATCH ON ETL_BATCH
(
       BATCH_ID                       ASC
);


CREATE TABLE ETL_CURRENCY_CONVERSION (
       CURRENCY_CONVERSION_VERSION_ID NUMBER NOT NULL,
       CURRENCY_CONVERSION_NAME VARCHAR2(255) NOT NULL,
       CURRENCY_CONVERSION_DESC VARCHAR2(512) NOT NULL,
       CONVERSION_FACTOR    NUMBER(18,6) NOT NULL,
       CONVERSION_EFFECTIVE_DATE DATE NOT NULL,
       CONVERSION_FUNCTION  VARCHAR2(512) NULL,
       CONVERSION_SOURCE    VARCHAR2(255) NULL,
       CURRENCY_BASE        VARCHAR2(255) NOT NULL,
       CURRENCY_TARGET      VARCHAR2(255) NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL
);

COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CURRENCY_CONVERSION_VERSION_ID IS '(PK)/required: A row-unique numeric value identifying the version of the currency conversion';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CURRENCY_CONVERSION_NAME IS 'required: A short descriptive name for the currency conversion';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CURRENCY_CONVERSION_DESC IS 'required: a more narrative description of the currency conversion';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CONVERSION_FACTOR IS 'required: numeric conversion rate or factor';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CONVERSION_EFFECTIVE_DATE IS 'required: effective date of the currency conversion';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CONVERSION_FUNCTION IS 'optional: may reference a database function or contain a calculation pertinent to the conversion';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CONVERSION_SOURCE IS 'optional: identify the body, source or author of the conversion rate or factor';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CURRENCY_BASE IS 'required: name of country and/or base currency being converted';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.CURRENCY_TARGET IS 'optional: name of country and/or target currency to which the currency will become equivalent';
COMMENT ON COLUMN ETL_CURRENCY_CONVERSION.IS_DISABLED IS 'required: domain[Y,N]; used to temporarily inactivate a formerly active currency conversion factor';
CREATE UNIQUE INDEX XPKETL_CURRENCY_CONVERSION ON ETL_CURRENCY_CONVERSION
(
       CURRENCY_CONVERSION_VERSION_ID ASC
);


CREATE TABLE ETL_ERROR_FACT (
       BATCH_ID             NUMBER NOT NULL,
       SCREEN_ID            NUMBER NOT NULL,
       EVENT_DATE_ID        NUMBER NOT NULL,
       ERROR_FACT_RECORD_NUMBER LONG NOT NULL,
       FINAL_SEVERITY_SCORE NUMBER(18,2) NULL,
       FACT_AUDIT_ID        NUMBER NULL,
       REMEDIATION_ACTION   VARCHAR2(2048) NULL,
       REMEDIATION_STATUS   VARCHAR2(255) NULL,
       REMEDIATION_ASSIGNED_TO VARCHAR2(50) NULL,
       REMEDIATION_ASSIGNED_DATE DATE NULL,
       REMEDIATION_DUE_BY_DATE DATE NULL,
       REMEDIATION_CMP_DATE DATE NULL,
       REMEDIATION_CMP_BY   VARCHAR2(50) NULL
);

COMMENT ON COLUMN ETL_ERROR_FACT.BATCH_ID IS '(PK part) (FK); required; numeric batch identifier associated with the error event';
COMMENT ON COLUMN ETL_ERROR_FACT.SCREEN_ID IS '(PK part) (FK); required; numeric screen identifier associated with the error event';
COMMENT ON COLUMN ETL_ERROR_FACT.EVENT_DATE_ID IS '(PK part) (FK); required numeric event date identifier when the event occurred';
COMMENT ON COLUMN ETL_ERROR_FACT.ERROR_FACT_RECORD_NUMBER IS 'required; numeric row number associated with error event';
COMMENT ON COLUMN ETL_ERROR_FACT.FINAL_SEVERITY_SCORE IS 'numeric; composite severity score associated with error';
COMMENT ON COLUMN ETL_ERROR_FACT.FACT_AUDIT_ID IS 'unique identifier for this entry in the audit table; this audit identifier may reference multiple rows in a fact table provided all of them match the qualifications of this audit dimension row;';
COMMENT ON COLUMN ETL_ERROR_FACT.REMEDIATION_ACTION IS 'optional; verbose description of recommended or hypothetical remediation action(s) required';
COMMENT ON COLUMN ETL_ERROR_FACT.REMEDIATION_STATUS IS 'N=None required; C=Completed; A=Assigned;';
COMMENT ON COLUMN ETL_ERROR_FACT.REMEDIATION_ASSIGNED_TO IS 'optional; identifies current application, group or person responsible for remediating error';
COMMENT ON COLUMN ETL_ERROR_FACT.REMEDIATION_ASSIGNED_DATE IS 'optional; most recent date a remediation task was assigned';
COMMENT ON COLUMN ETL_ERROR_FACT.REMEDIATION_DUE_BY_DATE IS 'optional; date remediation must be completed, if known and required';
COMMENT ON COLUMN ETL_ERROR_FACT.REMEDIATION_CMP_DATE IS 'optional; date remediation actually completed, if known and required';
COMMENT ON COLUMN ETL_ERROR_FACT.REMEDIATION_CMP_BY IS 'optional; identifies the application, group or person resolving the issue';
CREATE INDEX XIF6ETL_ERROR_FACT ON ETL_ERROR_FACT
(
       SCREEN_ID                      ASC
);

CREATE INDEX XIF7ETL_ERROR_FACT ON ETL_ERROR_FACT
(
       BATCH_ID                       ASC
);

CREATE INDEX XIF8ETL_ERROR_FACT ON ETL_ERROR_FACT
(
       FACT_AUDIT_ID                  ASC
);


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

COMMENT ON TABLE ETL_FACT_AUDIT IS 'The audit table is used to associate ETL data quality processing with each fact table row added';
COMMENT ON COLUMN ETL_FACT_AUDIT.FACT_AUDIT_ID IS 'unique identifier for this entry in the audit table; this audit identifier may reference multiple rows in a fact table provided all of them match the qualifications of this audit dimension row;';
COMMENT ON COLUMN ETL_FACT_AUDIT.OVERALL_QUALITY_CATEGORY IS 'summation of the completeness, validation and out-of-bounds category values;';
COMMENT ON COLUMN ETL_FACT_AUDIT.OVERALL_QUALITY_SCORE IS 'summation of the completeness, validation and out-of-bounds scores;';
COMMENT ON COLUMN ETL_FACT_AUDIT.COMPLETENESS_CATEGORY IS 'incomplete; partially incomplete; complete;';
COMMENT ON COLUMN ETL_FACT_AUDIT.COMPLETENESS_SEVERITY_SCORE IS 'incomplete = 0; partially incomplete = 50; complete = 100;';
COMMENT ON COLUMN ETL_FACT_AUDIT.COMPLETENESS_SCREEN_COUNT IS 'total number of completeness screens run against this fact row;  may be used to calculate weighted averages as new screens/tests are added;';
COMMENT ON COLUMN ETL_FACT_AUDIT.COMPLETENESS_SCREENS_FAILED IS 'number of completeness screens failed by this fact row; an ideal score would be zero if all completeness checks have been defined';
COMMENT ON COLUMN ETL_FACT_AUDIT.VALIDATION_CATEGORY IS 'invalid; partially invalid; valid;';
COMMENT ON COLUMN ETL_FACT_AUDIT.VALIDATION_SEVERITY_SCORE IS 'invalid = 0; partially invalid = 50; valid = 100;';
COMMENT ON COLUMN ETL_FACT_AUDIT.VALIDATION_SCREEN_COUNT IS 'total number of data validation screens/tests run against this fact row';
COMMENT ON COLUMN ETL_FACT_AUDIT.VALIDATION_SCREENS_FAILED IS 'number of validation screens/tests failed by this row; if all validation tests have been defined, an ideal score would be zero;';
COMMENT ON COLUMN ETL_FACT_AUDIT.OUT_OF_BOUNDS_CATEGORY IS 'below minimum domain value; above maximum domain value; exceeds the maximum length expected; shorter than the minimum length expected;';
COMMENT ON COLUMN ETL_FACT_AUDIT.OUT_OF_BOUNDS_SEVERITY_SCORE IS 'below minimum domain value = 10; above maximum domain value = 100; shorter than the minimum length expected = 10; exceeds the maximum length expected 100;';
COMMENT ON COLUMN ETL_FACT_AUDIT.OUT_OF_BOUNDS_SCREEN_COUNT IS 'total number of screens/tests performed against this fact row for out-of-bounds;';
COMMENT ON COLUMN ETL_FACT_AUDIT.OUT_OF_BOUNDS_SCREENS_FAILED IS 'number of out-of-bounds type screens/tests which failed for this fact; if all possible out of bounds tests have been defined, an ideal score would be zero';
COMMENT ON COLUMN ETL_FACT_AUDIT.TOTAL_SCREENS_FAILED IS 'completeness_screens_failed + validation_screens_failed + out_of_bounds_screens_failed this may be handled as a computed column if desired;';
COMMENT ON COLUMN ETL_FACT_AUDIT.MAX_SEVERITY_SCORE IS 'max(completeness_severity_score, validation_severity_score, out_of_bounds_severity_score)';
COMMENT ON COLUMN ETL_FACT_AUDIT.EXTRACT_DATETIME IS 'date and time the driving data for this fact was extracted from the source system';
COMMENT ON COLUMN ETL_FACT_AUDIT.CLEAN_DATETIME IS 'date and time the driving data for this fact row was cleaned in the ETL staging area';
COMMENT ON COLUMN ETL_FACT_AUDIT.CONFORM_DATETIME IS 'date and time the cleaned data for this fact row was conformed in the ETL staging area';
COMMENT ON COLUMN ETL_FACT_AUDIT.ALLOCATION_VERSION IS 'the allocation methodology version in use at the time this fact row was added (may be ''not applicable'' or ''unused'' if no allocation method was required to derive this fact)';
CREATE UNIQUE INDEX XPKETL_FACT_AUDIT ON ETL_FACT_AUDIT
(
       FACT_AUDIT_ID                  ASC
);

CREATE INDEX XIF10ETL_FACT_AUDIT ON ETL_FACT_AUDIT
(
       CURRENCY_CONVERSION_VERSION_ID ASC
);

CREATE INDEX XIF5ETL_FACT_AUDIT ON ETL_FACT_AUDIT
(
       ETL_SYSTEM_VERSION_ID          ASC
);


CREATE TABLE ETL_FILE_FIELD_LAYOUT (
       SOURCE_ID            NUMBER NOT NULL,
       FIELD_ID             NUMBER NOT NULL,
       FIELD_NAME           VARCHAR2(255) NOT NULL,
       CHARACTER_LENGTH     NUMBER NOT NULL,
       START_POSITION       NUMBER NULL,
       END_POSITION         NUMBER NULL,
       FIELD_DATA_CLASS     VARCHAR2(50) NULL,
       REQUIRED_YN          CHAR(1) DEFAULT ('N') NULL,
       COMMENTS             VARCHAR2(512) NULL,
       VALIDATION_RULE_SET_ID NUMBER NULL,
       DELIMETER_STRING     VARCHAR2(50) NULL,
       CONVERSION_FUNCTION  VARCHAR2(1024) NULL
);

COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.SOURCE_ID IS 'foreign key reference identifying the source with which this field is associated;';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.FIELD_ID IS 'a 1 relative; unique integer value to identify this field in this source;';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.FIELD_NAME IS 'a logical name for this field';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.CHARACTER_LENGTH IS 'maximum number of characters in the field;';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.START_POSITION IS '1 relative; starting position for this field in the record; assumes fixed length record;';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.END_POSITION IS '1 relative; ending position of the field in the record; assumes fixed length record; end position minus start position + 1 should = character length;';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.FIELD_DATA_CLASS IS 'anticipated class of data to expect in this field: alphanumeric, numeric, date';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.REQUIRED_YN IS 'is this field required Y/N?  A value of Y would indicate the field must not be empty;';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.COMMENTS IS 'any useful comments which may help with extraction, transformation or conversion';
COMMENT ON COLUMN ETL_FILE_FIELD_LAYOUT.VALIDATION_RULE_SET_ID IS 'a reference to any business rules which will help with validating the contents of the field';
CREATE UNIQUE INDEX XPKETL_FILE_FIELD_LAYOUT ON ETL_FILE_FIELD_LAYOUT
(
       SOURCE_ID                      ASC,
       FIELD_ID                       ASC
);

CREATE INDEX XIF9ETL_FILE_FIELD_LAYOUT ON ETL_FILE_FIELD_LAYOUT
(
       SOURCE_ID                      ASC
);


CREATE TABLE ETL_PROCESS_LOG (
       ETL_PROCESS_LOG_ID   NUMBER NOT NULL,
       PROJECT_NAME         VARCHAR2(255) NOT NULL,
       HOST_NAME            VARCHAR2(255) NOT NULL,
       JOB_NAME             VARCHAR2(255) NOT NULL,
       DATA_SOURCE_ID       VARCHAR2(255) NOT NULL,
       JOB_START_DATE       DATE NOT NULL,
       JOB_END_DATE         DATE NULL,
       RECORDS_IN           NUMBER(18) NULL,
       RECORDS_OUT          NUMBER(18) NULL,
       RECORDS_ERROR        NUMBER(18) NULL,
       RECORDS_SUSPENDED    NUMBER(18) NULL,
       IS_JOB_STARTED       CHAR(1) DEFAULT ('N') NULL,
       IS_JOB_COMPLETED     CHAR(1) DEFAULT ('N') NULL,
       IS_JOB_INERROR       CHAR(1) DEFAULT ('N') NULL,
       IS_JOB_SUSPENDED     CHAR(1) DEFAULT ('N') NULL
);

CREATE UNIQUE INDEX XPKETL_PROCESS_LOG ON ETL_PROCESS_LOG
(
       ETL_PROCESS_LOG_ID             ASC
);


CREATE TABLE ETL_SCREEN (
       SCREEN_ID            NUMBER NOT NULL,
       SOURCE_SYSTEM_ID     NUMBER NOT NULL,
       TABLE_ID             NUMBER NOT NULL,
       SCREEN_TYPE          VARCHAR2(255) NOT NULL,
       SCREEN_CATEGORY      VARCHAR2(255) NOT NULL,
       ETL_STAGE            VARCHAR2(255) NOT NULL,
       PROCESSING_ORDER_NUMBER NUMBER(18,4) NOT NULL,
       DEFAULT_SEVERITY_SCORE NUMBER(18,4) NOT NULL,
       EXCEPTION_ACTION     VARCHAR2(255) NOT NULL,
       SCREEN_PASSED        CHAR(1) DEFAULT ('N') NULL,
       VALIDATION_RULE_SET_ID NUMBER NULL,
       ABORT_ON_FAIL        CHAR(1) DEFAULT ('N') NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL
);

CREATE UNIQUE INDEX XPKETL_SCREEN ON ETL_SCREEN
(
       SCREEN_ID                      ASC
);


CREATE TABLE ETL_SOURCE (
       SOURCE_ID            NUMBER NOT NULL,
       SOURCE_SYSTEM_ID     NUMBER NOT NULL,
       SOURCE_DESCRIPTION   VARCHAR2(512) NOT NULL,
       LOGICAL_SOURCE_STAGING_NAME VARCHAR2(255) NOT NULL,
       SOURCE_TYPE          VARCHAR2(255) NOT NULL,
       SOURCE_SYSTEM_NAME   VARCHAR2(255) NOT NULL,
       SOURCE_OWNER         VARCHAR2(255) NOT NULL,
       STAGING_FILENAME     VARCHAR2(255) NOT NULL,
       SOURCE_SYSTEM_FILENAME VARCHAR2(255) NOT NULL,
       CONTACT              VARCHAR2(255) NOT NULL,
       DATA_MOVEMENT_MECHANISM VARCHAR2(255) NULL,
       CONNECTION_STRING    VARCHAR2(255) NULL,
       CONNECTION_USERID    VARCHAR2(255) NULL,
       CONNECTION_PW        VARCHAR2(255) NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL
);

COMMENT ON COLUMN ETL_SOURCE.SOURCE_ID IS 'An identifier which uniquely identifies this data source in the ETL metadata;';
COMMENT ON COLUMN ETL_SOURCE.SOURCE_DESCRIPTION IS 'Verbose description of the data source; the goal of this description is to provide pertinent information needed by the ETL team to properly manage the file';
COMMENT ON COLUMN ETL_SOURCE.LOGICAL_SOURCE_STAGING_NAME IS 'Logical staging name given to the data source; will be referenced in ETL documentation';
COMMENT ON COLUMN ETL_SOURCE.SOURCE_TYPE IS 'Spreadsheet; Fixed Record Flat File; Delimited Flat File; Database; Network pipe;';
COMMENT ON COLUMN ETL_SOURCE.SOURCE_SYSTEM_NAME IS 'This describes the name of the source system from which contained the data source.';
COMMENT ON COLUMN ETL_SOURCE.SOURCE_OWNER IS 'Functional business area responsible for managing or maintaining the majority of the data contained in this data source;';
COMMENT ON COLUMN ETL_SOURCE.STAGING_FILENAME IS '(Optional) If the source is a file, this describes the filename (or naming convention) used to contain the file upon its initial arrival into the staging area.';
COMMENT ON COLUMN ETL_SOURCE.SOURCE_SYSTEM_FILENAME IS '(Optional) If the source is provided as a file, this describes the fully qualified filename on the source system.';
COMMENT ON COLUMN ETL_SOURCE.CONTACT IS 'who is the primary contact (name, phone #, email) for this source?';
COMMENT ON COLUMN ETL_SOURCE.DATA_MOVEMENT_MECHANISM IS 'by what mechanism is the source provided?  database connection, ftp, file copy, network transaction';
CREATE UNIQUE INDEX XPKETL_SOURCE ON ETL_SOURCE
(
       SOURCE_ID                      ASC
);

CREATE INDEX XIF1ETL_SOURCE ON ETL_SOURCE
(
       SOURCE_SYSTEM_ID               ASC
);


CREATE TABLE ETL_SOURCE_SYSTEM (
       SOURCE_SYSTEM_ID     NUMBER NOT NULL,
       SOURCE_SYSTEM_NAME   VARCHAR2(255) NOT NULL,
       DATA_STEWARD         VARCHAR2(255) NOT NULL,
       OWNER                VARCHAR2(255) NOT NULL,
       BUSINESS_AREA        VARCHAR2(255) NOT NULL
);

CREATE UNIQUE INDEX XPKETL_SOURCE_SYSTEM ON ETL_SOURCE_SYSTEM
(
       SOURCE_SYSTEM_ID               ASC
);


CREATE TABLE ETL_TABLE (
       TABLE_ID             NUMBER NOT NULL,
       NEXT_RECORD_VALUE    NUMBER DEFAULT 1 NULL,
       TABLE_NAME           VARCHAR2(128) NOT NULL,
       IS_OBSOLETE          CHAR(1) DEFAULT ('N') NULL,
       ESTIMATED_ROW_COUNT  NUMBER DEFAULT 0 NOT NULL,
       LAST_UPDATED         DATE DEFAULT sysdate NULL,
       PRIMARY_KEY_COLUMNS  VARCHAR2(255) NULL,
       AVERAGE_ROW_LENGTH   NUMBER NULL
);

COMMENT ON COLUMN ETL_TABLE.TABLE_ID IS 'A unique identifier for each table in the ETL system';
COMMENT ON COLUMN ETL_TABLE.NEXT_RECORD_VALUE IS 'required; numeric value incremented or adjusted as needed per table; should be updated at the end of each ETL cycle of the table';
COMMENT ON COLUMN ETL_TABLE.IS_OBSOLETE IS 'Y=record is no longer to be used; N=still active';
COMMENT ON COLUMN ETL_TABLE.LAST_UPDATED IS 'date the next record value was last updated for this table';
CREATE UNIQUE INDEX XPKETL_TABLE ON ETL_TABLE
(
       TABLE_ID                       ASC
);


CREATE TABLE ETL_VALIDATION_RULE (
       VALIDATION_RULE_ID   NUMBER NOT NULL,
       VALIDATION_RULE_SET_NAME VARCHAR2(255) NOT NULL,
       RULE_FIRE_PREREQUISITE_TEST VARCHAR2(1024) NOT NULL,
       RULE_TEST_LOGIC      VARCHAR2(1024) NULL,
       RULE_TEST_APPLICATION VARCHAR2(512) NOT NULL,
       EXPECTED_RESULT_VALUE VARCHAR2(255) NULL,
       EXPECTED_RESULT_APPLICATION VARCHAR2(512) NOT NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL
);

CREATE UNIQUE INDEX XPKETL_VALIDATION_RULE ON ETL_VALIDATION_RULE
(
       VALIDATION_RULE_ID             ASC
);


CREATE TABLE ETL_VALIDATION_RULE_SET (
       VALIDATION_RULE_SET_ID NUMBER NOT NULL,
       VALIDATION_RULE_SET_NAME VARCHAR2(255) NOT NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL
);

CREATE UNIQUE INDEX XPKETL_VALIDATION_RULE_SET ON ETL_VALIDATION_RULE_SET
(
       VALIDATION_RULE_SET_ID         ASC
);


CREATE TABLE ETL_VALIDATION_RULE_SET_RULE (
       VALIDATION_RULE_SET_ID NUMBER NOT NULL,
       VALIDATION_RULE_ID   NUMBER NOT NULL,
       IS_DISABLED          CHAR(1) DEFAULT ('N') NULL,
       EXCEPTION_ACTION     VARCHAR2(255) NOT NULL,
       LOG_OUTPUT_TO        VARCHAR2(255) NOT NULL
);

CREATE UNIQUE INDEX XPKETL_VALIDATION_RULE_SET_RUL ON ETL_VALIDATION_RULE_SET_RULE
(
       VALIDATION_RULE_SET_ID         ASC,
       VALIDATION_RULE_ID             ASC
);

CREATE INDEX XIF2ETL_VALIDATION_RULE_SET_RU ON ETL_VALIDATION_RULE_SET_RULE
(
       VALIDATION_RULE_SET_ID         ASC
);

CREATE INDEX XIF3ETL_VALIDATION_RULE_SET_RU ON ETL_VALIDATION_RULE_SET_RULE
(
       VALIDATION_RULE_ID             ASC
);


CREATE TABLE ETL_VERSION (
       ETL_SYSTEM_VERSION_ID NUMBER(18,4) NOT NULL,
       ETL_SYSTEM_NAME      VARCHAR2(255) NOT NULL,
       ETL_SYSTEM_VERSION_DESCRIPTION VARCHAR2(1024) NOT NULL
);

CREATE UNIQUE INDEX XPKETL_VERSION ON ETL_VERSION
(
       ETL_SYSTEM_VERSION_ID          ASC
);