+--------------------+-------------------------------------------------+
| ![](media/ima      | **SDWIS/Electronic Data Verification**          |
| ge1.wmf){width="2. |                                                 |
| 104861111111111in" | **(SDWIS/eDV)**                                 |
| height="1.1875in"} |                                                 |
|                    | **Release 1.0**                                 |
| United States      |                                                 |
| Environmental      | **Design Document**                             |
| Protection Agency  |                                                 |
|                    | ![](                                            |
| Office of Ground   | media/image2.jpeg){width="1.4583333333333333in" |
| Water and Drinking | height="0.7916666666666666in"}                  |
| Water              |                                                 |
|                    |                                                 |
| Contract No. EPA   |                                                 |
| BPA OARMSC-SYS-04  |                                                 |
|                    |                                                 |
| SDWIS Project      |                                                 |
|                    |                                                 |
| Product Control    |                                                 |
| No.                |                                                 |
|                    |                                                 |
| Systa              |                                                 |
| lex-SDWIS-01-5.d1d |                                                 |
|                    |                                                 |
| July 31, 2007      |                                                 |
+--------------------+-------------------------------------------------+

**ELECTRONIC DATA VERIFICATION**

**(SDWIS/eDV)**

**RELEASE 1.0 DESIGN DOCUMENT**

**CONTRACT NO.**

**EPA BPA OARMSC-SYS-04**

**SDWIS PROJECT**

**Prepared for:**

**United States Environmental Protection Agency**

**Office of Ground Water and Drinking Water**

**Drinking Water Protection Division**

**1200 Pennsylvania Ave., NW**

**Washington, DC 20460**

**Contracting Officer Representative**

**Edward Cottrill**

**Prepared by:**

**SAIC Solutions Delivery Center**

**Science Applications International Corporation**

**6565 Arlington Boulevard**

**Falls Church, VA 22042**

CONTENTS

[1.0 Introduction 1](#introduction)

[1.1 Background 1](#background)

[1.2 Concept of Operations 1](#concept-of-operations)

[1.2.1 Setup of Data Sources 2](#setup-of-data-sources)

[1.2.2 Rule Compliance Determination 2](#rule-compliance-determination)

[1.2.3 Identification of Discrepancies
3](#identification-of-discrepancies)

[1.2.4 Reporting and Presentation of Results
3](#reporting-and-presentation-of-results)

[1.3 Requirements 3](#requirements)

[1.4 Scope and Assumptions 4](#scope-and-assumptions)

[2.0 Technical Description 4](#technical-description)

[2.1 Data Sources 4](#data-sources)

[2.1.1 SDWIS/ODS QA Extract Database 4](#sdwisods-qa-extract-database)

[2.1.2 Extraction Criteria From Primacy Agency Actual Database
4](#extraction-criteria-from-primacy-agency-actual-database)

[2.1.3 Sample and Sample Result Extraction Criteria
7](#sample-and-sample-result-extraction-criteria)

[2.1.4 Violation Extraction Criteria 7](#violation-extraction-criteria)

[2.1.5 eDV Data Base 9](#edv-data-base)

[2.2 User Interface 10](#user-interface)

[2.2.1 Home Page 10](#home-page)

[2.2.2 Dashboard Page 11](#dashboard-page)

[2.2.3 eDV Reports Page 13](#edv-reports-page)

[2.2.4 Perform eDV 16](#perform-edv)

[2.2.5 Specify Verification Periods Page
19](#specify-verification-periods-page)

[2.2.6 Configure eDV 22](#configure-edv)

[3.0 SDWIS/eDV detailed design 24](#sdwisedv-detailed-design)

[3.1 Extract from SDWIS/State Database
24](#extract-from-sdwisstate-database)

[3.2 Load SDWIS/STATE Database Extraction
25](#load-sdwisstate-database-extraction)

[3.3 Load SDWIS/ODS QA Extract 25](#load-sdwisods-qa-extract)

[3.4 Perform Data Verification 25](#perform-data-verification)

[3.4.1 Process 1: eDV CDS Setup 26](#process-1-edv-cds-setup)

[3.4.2 Process 2: Lead and Copper Rule Compliance Process
28](#process-2-lead-and-copper-rule-compliance-process)

[3.4.3 Process 3: Surface Water Treatment Rule (SWTR) Compliance Process
32](#process-3-surface-water-treatment-rule-swtr-compliance-process)

[3.4.4 Process 4: Disinfectant/Disinfection By Product (D/DBP) Rule
Compliance Process
35](#process-4-disinfectantdisinfection-by-product-ddbp-rule-compliance-process)

[3.4.5 Process 5: Total Coliform Rule (TCR) Compliance Process
40](#process-5-total-coliform-rule-tcr-compliance-process)

[3.4.6 Process 6: Radionuclide (RAD) Rule Compliance Process
46](#process-6-radionuclide-rad-rule-compliance-process)

[3.4.7 Process 7: Chemical (Phase 2/5) Compliance Process
48](#process-7-chemical-phase-25-compliance-process)

[3.4.8 Process 8: Public Notification (PN) Rule Compliance Process
52](#process-8-public-notification-pn-rule-compliance-process)

[3.4.9 Process 9: Schedule Activity Compliance Process
54](#process-9-schedule-activity-compliance-process)

[3.4.10 Process 10: Arsenic Rule Compliance Processing
55](#process-10-arsenic-rule-compliance-processing)

[3.4.11 Process 11: Reload of the Primacy Agency Violations
57](#process-11-reload-of-the-primacy-agency-violations)

[3.4.12 Process 12: DV Discrepancy Identification
58](#process-12-dv-discrepancy-identification)

[4.0 Testing Approach 69](#testing-approach)

[**APPENDIX A**](#AppendixA) Violation Types Not Supported by
SDWIS/STATE Web Rel 1.0 Compliance Determination/Assistance Processes

[**APPENDIX B**](#AppendixB) Table Data Definition Language

**EXHIBITS**

[**Exhibit 2-1.** SDWIS/STATE Extraction Criteria
6](#__RefHeading___Toc173723081)

[**Exhibit 2-2.** SDWIS/eDV Home Page 10](#__RefHeading___Toc173723082)

[**Exhibit 2-3.** Dashboard Page 12](#__RefHeading___Toc173723083)

[**Exhibit 2-4.** eDV Discrepancy Report Page
14](#__RefHeading___Toc173723084)

[**Exhibit 2-5.** Perform eDV Page 16](#__RefHeading___Toc173723085)

[**Exhibit 2-6.** Specify Verification Periods Page
20](#__RefHeading___Toc173723086)

[**Exhibit 2-7.** Extraction Dates by Rule
22](#__RefHeading___Toc173723087)

[**Exhibit 2-8.** Configure eDV Page 23](#__RefHeading___Toc173723088)

[**Exhibit 3-1.** Process Logic Row -- Process 1
26](#__RefHeading___Toc173723089)

[**Exhibit 3-2.** Process Logic Row -- Process 1 Subprocesses
28](#__RefHeading___Toc173723090)

[**Exhibit 3-3.** Process Logic Row -- Process 2
28](#__RefHeading___Toc173723091)

[**Exhibit 3-4.** Process Logic Row -- Process 2, Subprocess 1
29](#__RefHeading___Toc173723092)

[**Exhibit 3-5.** Process Logic Row -- Process 2, Subprocess 2
30](#__RefHeading___Toc173723093)

[**Exhibit 3-6.** Process Logic Row -- Process 2, Subprocess 3
31](#__RefHeading___Toc173723094)

[**Exhibit 3-7.** Process Logic Row -- Process 2, Subprocess 4
31](#__RefHeading___Toc173723095)

[**Exhibit 3-8.** Process Logic Row -- Process 3
32](#__RefHeading___Toc173723096)

[**Exhibit 3-9.** Process Logic Row -- Process 3, Subprocess 1
33](#__RefHeading___Toc173723097)

[**Exhibit 3-10.** Process Logic Row -- Process 3, Subprocess 2
33](#__RefHeading___Toc173723098)

[**Exhibit 3-11.** Process Logic Row -- Process 3, Subprocess 3
34](#__RefHeading___Toc173723099)

[**Exhibit 3-12.** Process Logic Row -- Process 4
35](#__RefHeading___Toc173723100)

[**Exhibit 3-13.** Process Logic Row -- Process 4, Subprocess 1
36](#__RefHeading___Toc173723101)

[**Exhibit 3-14.** Process Logic Row -- Process 4, Subprocess 2
37](#__RefHeading___Toc173723102)

[**Exhibit 3-15.** Process Logic Row -- Process 4, Subprocess 3
37](#__RefHeading___Toc173723103)

[**Exhibit 3-16.** Process Logic Row -- Process 4, Subprocess 4
38](#__RefHeading___Toc173723104)

[**Exhibit 3-17.** Process Logic Row -- Process 4, Subprocess 5
39](#__RefHeading___Toc173723105)

[**Exhibit 3-18.** Process Logic Row -- Process 4, Subprocess 6
39](#__RefHeading___Toc173723106)

[**Exhibit 3-19.** Process Logic Row -- Process 5
40](#__RefHeading___Toc173723107)

[**Exhibit 3-20.** Process Logic Row -- Process 5, Subprocess 1
41](#__RefHeading___Toc173723108)

[**Exhibit 3-21.** Process Logic Row -- Process 5, Subprocess 2
44](#__RefHeading___Toc173723109)

[**Exhibit 3-22.** TCR Acute violation copy from TMNVIOL to TCDSVIO
45](#__RefHeading___Toc173723110)

[**Exhibit 3-23.** Process Logic Row -- Process 5, Subprocess 3
45](#__RefHeading___Toc173723111)

[**Exhibit 3-24.** Process Logic Row -- Process 6
46](#__RefHeading___Toc173723112)

[**Exhibit 3-25.** Process Logic Row -- Process 6, Routine Subprocess
47](#__RefHeading___Toc173723113)

[**Exhibit 3-26.** Process Logic Row -- Process 6, Confirmation
Subprocess 48](#__RefHeading___Toc173723114)

[**Exhibit 3-27**. Process Logic Row -- Process 7
49](#__RefHeading___Toc173723115)

[**Exhibit 3-28.** Process Logic Row -- Process 7, Routine Acute
Chemicals Subprocess 49](#__RefHeading___Toc173723116)

[**Exhibit 3-29.** Process Logic Row -- Process 7, Confirmation Chemical
Acute Subprocess 50](#__RefHeading___Toc173723117)

[**Exhibit 3-30.** Process Logic Row -- Process 7, Routine Chronic
Chemical Subprocess 51](#__RefHeading___Toc173723118)

[**Exhibit 3-31.** Process Logic Row -- Process 7, Confirmation Chemical
Chronic Subprocess 52](#__RefHeading___Toc173723119)

[**Exhibit 3-32.** Process Logic Row -- Process 8
53](#__RefHeading___Toc173723120)

[**Exhibit 3-33.** Process Logic Row -- Process 9
54](#__RefHeading___Toc173723121)

[**Exhibit 3-34.** Process Logic Row -- Process 10
55](#__RefHeading___Toc173723122)

[**Exhibit 3-35.** Process Logic Row -- Process 10, Routine Subprocess
56](#__RefHeading___Toc173723123)

[**Exhibit 3-36.** Process Logic Row -- Process 10, Confirmation
Subprocess 57](#__RefHeading___Toc173723124)

[**Exhibit 3-37.** Column Matching Criteria for TDVRESLT and TMNVIOL
61](#__RefHeading___Toc173723125)

[**Exhibit 3-38.** Analyte and Violation Type Code Matching Example
61](#__RefHeading___Toc173723126)

[**Exhibit 3-40.** Column Matching Criteria for TMNVIOL and ODSVIOL
65](#__RefHeading___Toc173723127)

[**Exhibit 3-41.** Column Matching Criteria for TDVRESLT and ODSVIOL -
Overreporting 66](#__RefHeading___Toc173723128)

[**Exhibit 3-42.** Column Matching Criteria for TDVRESLT and TMNVIOL --
non Validated Status 68](#__RefHeading___Toc173723129)

[**Exhibit 4-1.** Certification Hardware/Software Environment for
SDWIS/eDV 1.0 70](#__RefHeading___Toc173723130)

[**Exhibit A-1.** Rule Violation Type Combinations For Which SDWIS/STATE
Does Not Determine Violations 4](#__RefHeading___Toc173723131)

[**Exhibit A-2.** Rule Requirements Not Supported in SDWIS/STATE
A-4](#__RefHeading___Toc173723132)

[**Exhibit B-1.** DV Period Table Design
B-3](#__RefHeading___Toc173723133)

# Introduction

The United States Environmental Protection Agency (EPA) Office of Ground
Water and Drinking Water (OGWDW) is responsible for implementing the
Public Water System Supervision Program established under the auspices
of the Safe Drinking Water Act of 1974 (Public Law 93-523) and the 1996
Amendments to the Act (Public Law 104-183). The Safe Drinking Water
Information System (SDWIS) suite of computer applications has been
developed as the repository for much of the public drinking water
information. Information flows from the Primacy Agency to the EPA where
the national information is available for decision making at the federal
level. In oversight of this responsibility, the EPA conducts Data
Verification (DV) to ensure that the information at the federal level is
complete, accurate, and consistent with the information used at the
Primacy Agency.

To date, the DV process has relied on a manual protocol with on-site
audit teams. It requires many hours of effort from both Primacy Agency
personnel and EPA personnel to prepare and conduct the DV. The
SDWIS/Electronic Data Verification (SDWIS/eDV) project is intended to
automate as many of these processes as possible with the goals of:

-   Reducing Primacy Agency personnel's time in preparation of DV
    information

-   Incorporating a larger assessment pool of Water Systems into the
    review

-   Focusing EPA personnel's time on areas of concern

-   Providing quick and accurate results

## Background

SDWIS/eDV has been developed in phases. An original proof of concept was
completed in 2005. The proof of concept successfully demonstrated that
by using a Primacy Agency's SDWIS/STATE database and an extraction from
the federal SDWIS/Operational Data Store (SDWIS/ODS), processes could be
developed to compare the information between the two data sources and
reports could be automatically generated to assist the DV auditors. The
MS Access-based SDWIS/eDV Prototype followed in 2006. The Prototype
focused on DV for the Total Coliform Rule and presented reports similar
to those used in the existing DV process.

## Concept of Operations

SDWIS/eDV is designed for use by either EPA DV staff or by Primacy
Agency personnel who wish to prepare for a DV. SDWIS/eDV Release 1.0
will build on the 2006 Prototype to automate the following processes:

-   Setup of Data Sources

-   Rule Compliance Determination

-   Identification of discrepancies

-   Reporting and presentation of the results

SDWIS/eDV Release 1.0 will use a suite of open-source tools to extract
data from SDWIS/STATE and SDWIS/ODS databases, load the data into an eDV
database, and render browser-based interactive reports.

### Setup of Data Sources

SDWIS/eDV uses two data sources at the start of the DV process:

-   SDWIS/ODS Quality Assurance (QA) Extract - the primacy agency's data
    extracted from SDWIS/ODS into comma-separated (CSV) formatted-files

-   State Actual -- Extraction from the Primacy Agency's SDWIS/STATE
    database into CSV files

EPA or primacy agency staff requests the SDWIS/ODS QA Extract directly
from SDWIS/ODS. State Actual database extraction is performed on-site at
the primacy agency using the primacy agency's production SDWIS/STATE
database as input.

Once the data from both sources are locally available, the primacy
agency or EPA user specifies the location of the source files to
initiate loading of SDWIS/ODS QA Extract and SDWIS/STATE actual
production data. SDWIS/eDV loads data from the CSV files into the
SDWIS/eDV database.

### Rule Compliance Determination 

The Rule Compliance Determination component reuses existing compliance
determination processes and will process the following EPA rules in
accordance with SDWIS/STATE Web Release 1.0 business rule
implementation. It supports the following rules:

-   Arsenic Rule

-   Chemical Phase Rules (Acutes and Chronics)

-   Consumer Confidence Rule

-   Disinfectant and Disinfection By Product (Stage 1)

-   Lead and Copper Rule

-   Public Notification Rule

-   Radionuclide

```{=html}
<!-- -->
```
-   Surface Water Treatment Rule (Long-Term 1, Filter Backwash,
    Turbidity, Interim Enhanced)

```{=html}
<!-- -->
```
-   Total Coliform Rule

Users indicate the rule(s) for which they wish SDWIS/eDV to provide DV
by specifying a DV Period for each rule. The DV periods also limit the
timeframe for which sample information is extracted from the primacy
agency's SDWIS/STATE database.

SDWIS/STATE Web Release 1.0 handles the vast majority of compliance
determination scenarios for these rules. However, as compliance
determination was implemented for each rule in SDWIS/STATE,
implementation of a few aspects of some rules was assessed to have a
relatively low cost-benefit ratio. While these design decisions are
recorded in SDWIS/STATE design documentation produced over the last
several years, they have been re-summarized in Appendix A of this
document for the convenience of those not familiar with SDWIS/STATE
documentation.

### Identification of Discrepancies

The SDWIS/eDV Compliance Determination component generates candidate
violations in the SDWIS/eDV database. In SDWIS/STATE, the compliance
processes are designed to assess existing violations and to not identify
redundant candidate violations. For these same processes to work when
applied to SDWIS/eDV, the violations must be temporarily removed from
the State Actual database. The software copies and saves the State
Actual violations prior to deleting them from the State Actual data
source. After the compliance processes have completed creating the eDV
data source, the deleted violations are restored to the State Actual
data source.

### Reporting and Presentation of Results

A multi-dimensional database based in Mondrian by Pentaho will enable
online analytical processing (OLAP) of the discrepancy data. The
interactive report will present summarized counts of discrepancies (and
non-discrepancies) at the Rule level that can then be drilled-down to
public water system type, violation type category, and violation type.
Jasper Intelligence by JasperSoft is the OLAP software that provides
this interactive functionality.

## Requirements

The requirements for a SDWIS/eDV 1.0 are:

[]{.mark}

1.  Automate the setup of the data sources.

2.  Support all databases targeted by SDWIS/STATE.

3.  Support Compliance Determination/Assistance for same federal rules
    as supported in SDWIS/STATE.

4.  Present eDV Discrepancy Report so that user may "drill down" into
    the discrepancy data to identify issues in three areas of interest:

-   Compliance Determination (CD) -- a violation that was determined by
    the SDWIS/eDV is missing in the Primacy Agency database of record.

-   Data Flow (DF) -- a violation that was determined by the SDWIS/eDV,
    found in the Primacy Agency database, but is not found in the
    SDWIS/ODS QA Extract.

-   Over Reporting (OR) -- a violation is in the SDWIS/ODS QA Extract
    but that was not determined by the SDWIS/eDV.

5.  Provide a SDWIS/eDV application that can be used either by EPA HQ DV
    staff or by a Primacy Agency in preparation for an on-site DV to
    enable generation of preliminary information that highlights
    potential corrections or to support information gathering.

## Scope and Assumptions

SDWIS/eDV 1.0 has been designed according to the following EPA-specified
assumptions:

-   SDWIS/STATE Web Release 1.0 is the primacy agency's database of
    record.

-   Primacy Agency maintains sample schedules and sample results in
    SDWIS/STATE.

-   Sample Schedules in SDWIS/STATE are established at the appropriate
    monitoring frequency.

-   Where possible, open source development products should be used.

-   Existing SDWIS/STATE compliance assistance processes should be used
    and extended to

> generate anticipated violations.

# Technical Description

SDWIS/eDV Rel 1.0 will be a web-based J2EE-compliant Java product that
accesses a Derby database. An extraction-transformation-load (ETL) tool
called Kettle (by Pentaho) will be used to extract the data source CSV
files and load into the Derby database. Java processing will be used to
run the SDWIS/STATE compliance processes and the discrepancy
identification process. A multi-dimensional database based in Mondrian
by Pentaho will be built to enable online analytical processing (OLAP)
using the JasperIntelligence tool from JasperSoft.

## Data Sources

### SDWIS/ODS QA Extract Database

A SDWIS/ODS QA Extract of the Primacy Agency\'s data is obtained from
SDWIS/ODS upon request. The SDWIS/ODS QA Extract includes text files
containing the Primacy Agency\'s data in SDWIS/ODS.

The SDWIS/ODS QA Extract file location is provided as input to
SDWIS/eDV. During the load process, all water system and all violation
data from the QA Extract is loaded into SDWIS/eDV tables.

### Extraction Criteria From Primacy Agency Actual Database

The Primacy Agency's production SDWIS/STATE database will be the source
for extracting a subset of information constrained by the DV Period Data
from the following SDWIS/STATE tables will be extracted:

+-------------------------------+--------------------------------------+
| TABLE                         | EXTRACTION                           |
|                               |                                      |
|                               | CRITERIA                             |
+-------------------------------+--------------------------------------+
| TCDSPMSG                      | All                                  |
+-------------------------------+--------------------------------------+
| TCDSSLOG                      | All (timestamps for processes 10-15  |
|                               | are set to null)                     |
+-------------------------------+--------------------------------------+
| TENACTIV                      | All                                  |
+-------------------------------+--------------------------------------+
| TENACTYP                      | All                                  |
+-------------------------------+--------------------------------------+
| TENENACT                      | SIE and EIE action types linked to   |
|                               | selected TMNVIOL                     |
+-------------------------------+--------------------------------------+
| TENSCHAT                      | All                                  |
+-------------------------------+--------------------------------------+
| TENSCHD                       | All                                  |
+-------------------------------+--------------------------------------+
| TINAOPRD                      | All                                  |
+-------------------------------+--------------------------------------+
| TINCHANG                      | None                                 |
+-------------------------------+--------------------------------------+
| TINCLD                        | None                                 |
+-------------------------------+--------------------------------------+
| TINCODE                       | All                                  |
+-------------------------------+--------------------------------------+
| TINDEFCY                      | None                                 |
+-------------------------------+--------------------------------------+
| TINDFSAA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINEISN                       | All                                  |
+-------------------------------+--------------------------------------+
| TINEXSN                       | All                                  |
+-------------------------------+--------------------------------------+
| TINFAOP                       | All                                  |
+-------------------------------+--------------------------------------+
| TINGEOAR                      | All                                  |
+-------------------------------+--------------------------------------+
| TINGOVAG                      | All                                  |
+-------------------------------+--------------------------------------+
| TINIGAA                       | All                                  |
+-------------------------------+--------------------------------------+
| TININDIV                      | All                                  |
+-------------------------------+--------------------------------------+
| TINLGCOM                      | All                                  |
+-------------------------------+--------------------------------------+
| TINLGENT                      | All                                  |
+-------------------------------+--------------------------------------+
| TINLOC                        | None                                 |
+-------------------------------+--------------------------------------+
| TINOUSER                      | (view, not real)                     |
+-------------------------------+--------------------------------------+
| TINPOPSV                      | All                                  |
+-------------------------------+--------------------------------------+
| TINPRT                        | All                                  |
+-------------------------------+--------------------------------------+
| TINPVALS                      | All                                  |
+-------------------------------+--------------------------------------+
| TINRAA                        | All                                  |
+-------------------------------+--------------------------------------+
| TINSAT                        | All                                  |
+-------------------------------+--------------------------------------+
| TINSCC                        | All                                  |
+-------------------------------+--------------------------------------+
| TINSCRN                       | None                                 |
+-------------------------------+--------------------------------------+
| TINSTRG                       | None                                 |
+-------------------------------+--------------------------------------+
| TINSVDFA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINSVINA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINTPAR                       | None                                 |
+-------------------------------+--------------------------------------+
| TINTROBJ                      | None                                 |
+-------------------------------+--------------------------------------+
| TINTROPA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINTRPLT                      | None                                 |
+-------------------------------+--------------------------------------+
| TINTRPRO                      | None                                 |
+-------------------------------+--------------------------------------+
| TINTRUNA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINTUFR                       | None                                 |
+-------------------------------+--------------------------------------+
| TINTUIND                      | None                                 |
+-------------------------------+--------------------------------------+
| TINTUMSR                      | None                                 |
+-------------------------------+--------------------------------------+
| TINUNPRO                      | None                                 |
+-------------------------------+--------------------------------------+
| TINUPF                        | None                                 |
+-------------------------------+--------------------------------------+
| TINUSER                       | All                                  |
+-------------------------------+--------------------------------------+
| TINUSERL                      | All                                  |
+-------------------------------+--------------------------------------+
| TINVISIT                      | None                                 |
+-------------------------------+--------------------------------------+
| TINVTOPA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWASH                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWELL                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWLCAS                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWPURC                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSF                        | All                                  |
+-------------------------------+--------------------------------------+
| TINWSFC                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWSFF                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWSFFR                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSFH                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWSFIN                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSFMR                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSFR                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWSFSC                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSG                        | All                                  |
+-------------------------------+--------------------------------------+
| TINWSGA                       | All                                  |
+-------------------------------+--------------------------------------+
| TINWSGAA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSGGA                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSIN                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWSLEC                      | None                                 |
+-------------------------------+--------------------------------------+
| TINWSMR                       | None                                 |
+-------------------------------+--------------------------------------+
| TINWSSAA                      | All                                  |
+-------------------------------+--------------------------------------+
| TINWSYS                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNALRA                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNCMCLV                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNCMMAA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNCOR                        | All                                  |
+-------------------------------+--------------------------------------+
| TMNFANL                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNLCWQP                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNMNCON                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNMNR                        | All                                  |
+-------------------------------+--------------------------------------+
| TMNMPAVG                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNMPRD                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNMRCA                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNMRLOC                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNORVIA                      | Associated to selected TMNVIOL       |
+-------------------------------+--------------------------------------+
| TMNRGVAA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNRSA                        | None                                 |
+-------------------------------+--------------------------------------+
| TMNRULE                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNRWSMA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSAA                        | None                                 |
+-------------------------------+--------------------------------------+
| TMNSAPLN                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNSASCH                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSBSRA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSCHTM                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNSKPAC                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSKPAR                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSPLDT                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNSPSGS                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSPSPA                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNSPSRA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSPSUB                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSPUBA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSSGRP                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSSMPA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNSTAT                       | None                                 |
+-------------------------------+--------------------------------------+
| TMNSTMEM                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNVAAA                       | None                                 |
+-------------------------------+--------------------------------------+
| TMNVAGAA                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNVCRAT                      | Associated to selected               |
|                               |                                      |
|                               | TMNVIOL                              |
+-------------------------------+--------------------------------------+
| TMNVDCR                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNVGRP                       | Associated to selected               |
|                               |                                      |
|                               | TMNVIOL                              |
+-------------------------------+--------------------------------------+
| TMNVIEAA                      | Associated to selected TMNVIOL and   |
|                               | SIE and EIE action types             |
+-------------------------------+--------------------------------------+
| TMNVIOL                       | Reference detail                     |
|                               |                                      |
|                               | design below                         |
+-------------------------------+--------------------------------------+
| TMNVIOST                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNVISPA                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNVISRA                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNVPRDR                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNVTAA                       | All                                  |
+-------------------------------+--------------------------------------+
| TMNVTRA                       | Table recreated for eDV with 104     |
|                               | records                              |
+-------------------------------+--------------------------------------+
| TMNVTYPE                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNVULAS                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNWAIVE                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNWASPA                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNWAWSA                      | None                                 |
+-------------------------------+--------------------------------------+
| TMNWSMPA                      | All                                  |
+-------------------------------+--------------------------------------+
| TMNXANLT                      | All                                  |
+-------------------------------+--------------------------------------+
| TSAAGA                        | All                                  |
+-------------------------------+--------------------------------------+
| TSAANGRP                      | All                                  |
+-------------------------------+--------------------------------------+
| TSAANLYT                      | All                                  |
+-------------------------------+--------------------------------------+
| TSACVCR                       | None                                 |
+-------------------------------+--------------------------------------+
| TSALAB                        | All                                  |
+-------------------------------+--------------------------------------+
| TSALAMA                       | None                                 |
+-------------------------------+--------------------------------------+
| TSALLEA                       | All                                  |
+-------------------------------+--------------------------------------+
| TSAMAR                        | Associated to selected TSASAR        |
+-------------------------------+--------------------------------------+
| TSAMCSMP                      | Associated to selected TSASAMPL      |
+-------------------------------+--------------------------------------+
| TSAMDBPS                      | All                                  |
+-------------------------------+--------------------------------------+
| TSAOSAM                       | Associated to selected TSASAR        |
+-------------------------------+--------------------------------------+
| TSAOSAPM                      | None                                 |
+-------------------------------+--------------------------------------+
| TSAOSM                        | Associated to selected TSASAMPL      |
+-------------------------------+--------------------------------------+
| TSAOSPM                       | None                                 |
+-------------------------------+--------------------------------------+
| TSASAMPL                      | Reference detailed design below      |
+-------------------------------+--------------------------------------+
| TSASAMT                       | None                                 |
+-------------------------------+--------------------------------------+
| TSASAR                        | Reference detailed design below      |
+-------------------------------+--------------------------------------+
| TSASMAA                       | None                                 |
+-------------------------------+--------------------------------------+
| TSASMMT                       | None                                 |
+-------------------------------+--------------------------------------+
| TSASMN                        | None                                 |
+-------------------------------+--------------------------------------+
| TSASMPPT                      | All                                  |
+-------------------------------+--------------------------------------+
| TSASMPSM                      | All                                  |
+-------------------------------+--------------------------------------+
| TSASPHIS                      | None                                 |
+-------------------------------+--------------------------------------+
| TSASRSC                       | All                                  |
+-------------------------------+--------------------------------------+
| TSASRSR                       | All                                  |
+-------------------------------+--------------------------------------+
| TSASSR                        | All                                  |
+-------------------------------+--------------------------------------+
| TSASTM                        | None                                 |
+-------------------------------+--------------------------------------+
| TSAUOM                        | None                                 |
+-------------------------------+--------------------------------------+

[]{#__RefHeading___Toc173723081 .anchor}**Exhibit 2-1.** SDWIS/STATE
Extraction Criteria

### Sample and Sample Result Extraction Criteria

The extraction of Samples and Results is based on a date range and, for
Results, analyte code. The user will be able to provide a date range for
each rule for which the DV is being performed.

The DV extraction date range for each rule is stored in the DV Period
(TDVDVPRD) table. Each row in TDVDVPRD with an extract_begin_dt that is
not null defines the Rules to be included in the DV extraction via the
tmnrule_is_number and tmnrule_st_code foreign keys.

Using the (tmnrule_is_number and tmnrule_st_code) from the TDVDVPRD
table, the software identifies the analyte codes that define the Rule.
This is accomplished using the Analyte Level Rule (TMNALRA) table which
contains foreign keys to be the Rule (tmnrule_is_number and
tmnrule_st_code) and Analyte (tsaanlyt_is_number and tsaanlyt_st_code)
tables. It further qualifies the selection of TMNALRA to retrieve
distinct tsaanlyt_is_number for each TMNRULE where the
(TMNALRA.threshold_type_cd = 'MCL' or 'ACL') and (the TMNALRA.begin_date
\<= TDVDVPRD.extract_end_dt) and (the TMNALRA.end_date is null or \>=
TDVDVPRD.extract_begin_dt)).

With this list of analytes for each rule, proceed to extraction of
Samples and Results.

For each Rule's extraction begin and end date and the grouping of
analytes, extract a set of Samples and Sample Analytical Results with:

-   Tsasampl joined to tsasar on tsasampl.tsasampl_is_number and
    tsasampl_st_code

-   Tsasampl.collection_end_dt \>= tdvdvprd.extract_begin_dt and \<=
    tdvdvprd.extract_end_dt

-   tsasar.tsaanlyt_is_number = tmnalra.tsaanlyt_is_number

-   tsasar.tsaanlyt_st_code = tmnalra.tsaanlyt_is_number

### Violation Extraction Criteria

Violations are associated to Rules in two ways but neither way will
return 100 percent of the violations needed for SDWIS/eDV.  Therefore,
violations will be extracted based on the earliest DV Period Begin Date
and the latest DV Period End Date.  This will extract more violations
than are needed for the DV but will insure that all those needed for the
DV are extracted.  Because the DV Period for LCR may be significantly
longer than for any other rule, LCR violations are extracted separately
from other violations. 

The DV dates for each rule are stored in the DV Period (TDVDVPRD) table.
Each row in TDVDVPRD defines the Rules to be included in the DV
extraction via the tmnrule_is_number and tmnrule_st_code foreign keys.
The software identifies the violation extraction low and high dates to
be used for extracting the non-LCR violations. (EPA's current criteria
for verifying LCR data is to review the last two rounds of monitoring
for a selected PWS -- if a PWS is on triennial monitoring, this would
encompass 6 years.) The following logic is used:

-   Sort TDVDVPRD ascending user_begin_date

-   Ignore the extraction dates for the PBCU Rule

-   Calculate violation_extraction_low_date as user_begin_date -- 3
    years

-   Sort TDVDVPRD descending user_end_date

-   Ignore the extraction dates for the PBCU Rule

-   Calculate violation_extraction_high_date as user_end_date + 1 year

Violations, for which only a Federal Violation Period Begin Date is
required, are extracted using the following logic:

-   Join TMNVIOL to TMNVTYPE using the TMNVTYPE_IS_NUMBER and
    TMNVTYPE_ST_CODE

-   Join TMNVTYPE to TMNVPRDR using TMNVTYPE_IS_NUMBER and
    TMNVTYPE_ST_CODE . Get a single TMNVPRDR by using distinct on the
    TMNVTYPE_IS_NUMBER and TMNVTYPE_ST_CODE.

For LCR (those with violation type codes between 51 and 65) violations,
extract violations where:

-   TMNVPRDR.END_DT_NULL_IND = \"Y\" AND

-   TMNVTYPE.TYPE_CODE Between \"51\" And \"65\" (inclusive) AND

-   TMNVIOL.FED_PRD_BEGIN_DT \>= TDVVPRDR.EXTRACT_BEGIN_DT for PBCU Rule
    And

-   TMNVIOL. FED \_PRD_BEGIN_DT \<= TDVVPRDR.EXTRACT_END_DT for PBCU
    Rule.

For non-LCR (those with violation type code other than between 51 and
65) violations, extract violations where:

-   TMNVPRDR.END_DT_NULL_IND = \"Y\" AND

-   TMNVTYPE.TYPE_CODE less than \"51\" or greater than \"65\"
    (inclusive) AND

```{=html}
<!-- -->
```
-   TMNVIOL. FED \_PRD_BEGIN_DT \>= violation_extraction_low_date

-   TMNVIOL. FED \_PRD_BEGIN_DT \<= violation_extraction_high_date

Violations, for which both a Federal Violation Period Begin Date and End
Date are required, are extracted using the following logic:

For LCR (those with violation type codes between 51 and 65) violations,
extract violations where:

-   TMNVPRDR.END_DT_NULL_IND \<\> \"Y\" AND

-   TMNVTYPE.TYPE_CODE Between \"51\" And \"65\" AND

-   TMNVIOL. FED \_PRD_END_DT \>= TDVDVPRD.EXTRACT_BEGIN_DT for PBCU And

-   TMNVIOL. FED \_PRD_END_DT \<= TDVDVPRD.EXTRACT_END_DT for PBCU;

For non-LCR (those with violation type code other than between 51 and
65) violations, extract violations where:

-   TMNVPRDR.END_DT_NULL_IND \<\> \"Y\" AND

-   TMNVTYPE.TYPE_CODE Not Between \"51\" And \"65\" AND

-   TMNVIOL. FED \_PRD_END_DT \>= violation_extraction_low_date TMNVIOL.
    FED \_PRD_END_DT \<= violation_extraction_high_date

Extract no data from tables beginning TMG or TFR.

The result of the extraction is a series of CSV files. If the user
elects to extract from the SDWIS/STATE database and then load the
SDWIS/eDV database, the load occurs automatically following the
successful extraction. If the user elects to extract from the
SDWIS/STATE database independent of loading SDWIS/eDV (such as may be
the case if the DV team requests the extraction from the primacy
agency), the user must supply the location of the CSV file before
executing the SDWIS/eDV load process.

### eDV Data Base

During the load function, the .CSV files populated with the extracted
primacy agency database and the ODS Data Quality Extract are loaded into
a Derby database containing tables formatted in the SDWIS/STATE schema
definition and the ODS DQ schema definition.

The TMNVIOL extracted records will not be loaded back to the eDV
database until after the compliance determination processes have run.
This is so that the compliance determination processes identify a full
set of candidate violations without being influenced by existing
violations.

After the load is completed, additional data manipulation must occur so
that the CDS processes are triggered. Specifically, the TCR sample
results for analytes 3100, 3014, and 3013 need to be (return to
"accepted" status) so that the validation process can be rerun and any
acute MCLs identified by the eDV process. To do this, update every
TSASAR's DATA_QUALITY_CODE to 'A' where:

-   TSAANLYTE_ST_CODE = 'HQ' and

-   TSAANLYT_IS_NUMBER is in (406, 408, 409) and

-   DATA_QUALITY_CODE = 'V'

-   Joining to TSASAMPL on tsasampl_is_number and tsasampl_st_code where
    TSASAMPL.collection_end_dt \>= TDVDVPRD.user_begin_date -- 3 MONTHS
    and \<= TDVDVPRD user_end_date + 3 MONTHS for the TCR Rule.

Additional tables that are new and used by the DV processes are:

-   DV Periods (TDVDVPRD)

-   DV Result (TDVRESLT)

-   DV Process Log (TDVPRCLG)

-   DV Log (TDVLOG)

-   Rule-Violation Type-Analyte Association (TDVVTARA)

[]{.mark}

Data Definition Language (DDL) for these tables is located in Appendix
B.

Analyte Groups are used to control the execution of the Chemical Phase
1/2/5 Rule. This requires that new Analyte Groups be created for this
purpose. The final step in the load process is to create the rows in the
Analyte Group (TSAANGRP) and Analyte Group Assignment (TSAAGA) tables.
The rows to be inserted into these two tables are defined in the
attached [EDV Analyte Grps.xls](../EDV%20Analyte%20Grps.xls) .

## User Interface

The following sections document the functions associated with each
SDWIS/eDV web page.

### Home Page

.![](media/image3.png){width="5.950694444444444in"
height="4.310416666666667in"}

[]{#__RefHeading___Toc173723082 .anchor}**Exhibit 2-2.** SDWIS/eDV Home
Page

When SDWIS/eDV is launched in a web browser, the SDWIS/eDV Home Page
will be presented. The menu options on the left navigation pane are:

**Home** Presents the SDWIS/eDV Home Page.

**Dashboard** Presents the Dashboard page which displays the Processing
Log status of the SDWIS/eDV batch processes.

**eDV Reports** Launches the SDWIS/eDV Reports via the
JasperIntelligence OLAP Reporting tool.

**Saved eDV Reports** Presents a list of reports saved to the
\\eDV\\apache-tomcat\\webapps

\\eDV\\Reports folder.

**Perform eDV** Presents the Perform eDV page where users may specify
request to extract data from SDWIS/STATE, load SDWIS/STATE and SDWIS/ODS
QA Extract, and perform eDV discrepancy processing.

**Specify Verification**

**Periods** Presents the Specify Verification Periods page where date
ranges can be set for each rule to be processed in the DV process.

**Configure eDV** Presents the Configure eDV page where technical
information regarding the SDWIS/STATE database and the server connection
parameters are specified.

**Help** Will present online help for SDWIS/eDV.

### Dashboard Page

The Dashboard page displays a list of rows from the DV Log (TDVLOG) and
Processing Log (TDVPRCLG) table. These rows are maintained by various
SDWIS/eDV processes to track processing status/completion of SDWIS/eDV
functions.

The list displays start and end timestamps for processes and sub
processes executed during the Perform eDV function. The Status column
will indicate "In Progress" when the process is executing, "Failed" if
the process fails, and "Finished" when the end timestamp is populated.

When a user requests a SDWIS/STATE Extract, the software posts the
completed extract in a zip file on a local server. The user may then
access the SDWIS/STATE Extract from a link on the Dashboard page.

The list default sort order will be by End Time descending.

![](media/image4.png){width="5.911111111111111in"
height="8.596527777777778in"}

[]{#__RefHeading___Toc173723083 .anchor}**Exhibit 2-3.** Dashboard Page

[Column Definitions]{.underline}

**Process** The name of the process.

**Sub Process** The name of an integral unit of the compliance process.

**Start Time** The date and time that the process started.

**End Time** The date and time that the process ended.

**Status** Indicates if the process is in progress or has finished.

**Message** A text message that the process may have recorded. Not all
processes create messages.

**SDWIS/State Extract**

**File** A link to the zip file created by the SDWIS/State extraction
process. Opens the winzip utility.

[Buttons]{.underline}:

**Refresh** Refreshes the list with new data from the TDVPRCLG table.

**Compliance Process Log**

The second list displays the compliance processes and sub processes
along with the start and end times. These are the various EPA rule
compliance processes and are useful in tracking the progress of the
SDWIS/eDV Compliance Process. Only completed processes will be
presented.

[Column Definitions]{.underline}

**Process** The name of the process.

**Sub Process** The name of the Sub Process.

**Start Time** The date and time that the sub process started.

**End Time** The date and time that the sub process ended.

### eDV Reports Page

The eDV Discrepancy Report will be launched in a new web browser
session. Login to the JasperServer is required.

![](media/image5.jpeg){width="6.493055555555555in"
height="4.327083333333333in"}

[]{#__RefHeading___Toc173723084 .anchor}**Exhibit 2-4.** eDV Discrepancy
Report Page

[Column Definitions]{.underline}

**Rule** Displays the Rule Abbreviation Code corresponding to the EPA
regulation.

**PWS Type Code** The code describing the type of public water system.

**County** The name of the PWS principal county served.

**Violation Category**

**Code** Summarizes the violation types into a category of either
Monitoring and Reporting (M&R) or Maximum Contaminant Level (MCL).

**Violation Type**

**Code** The violation type code.

**DV Category** The category for the result such as No Discrepancy,
Compliance Decision, Data Flow, or Over Reporting Discrepancy.

**Number of PWS**

**with Violations** Count of the unique PWSs for which the eDV Process
identified a violation.

**Number of eDV**

**Violations** Count of the violations identified by the eDV Process
independent of

> any other source..

**Number of eDV**

**Violations also**

**in State** Count of violations identified by the eDV Process for which
a match was found in the Primacy Agency data source.

**Number of eDV Violations**

**without Discrepancy**

Count of violations identified by the eDV Process for which a match was
found in the Primacy Agency data source and the SDWIS/ODS QA Extract.

**Percent Without**

**Discrepancy** Calculated as the Number of eDV Violations Without
Discrepancy divided by Number of eDV Violations.

**Number of Compliance**

**Decision Discrepancies**

Count of violations identified by the eDV Process for which no match was
found in the Primacy Agency data source. A match may or may not have
been found in the SDWIS/ODS QA Extract

> data source.

**Percent Compliance Decision**

**Discrepancies** Calculated as the Number of Compliance Decision
Discrepancies divided by Number of eDV Violations.

**Number of Data Flow**

**Discrepancies** Count of violations identified by the eDV Process for
which a match was found in the Primacy Agency data source but no match
was found in the SDWIS/ODS QA Extract data source.

**Percent Data Flow**

**Discrepancies** Calculated as the Number of Data Flow Discrepancies
divided by Number of eDV Violations.

**Number of Over Reporting**

**Discrepancies** Count of violations found in the SDWIS/ODS QA Extract
data source for which no match was identified by the eDV Process. It may
or may not have a match in the Primacy Agency data source.

**Total Number of**

**Discrepancies** A summation of the Number of Compliance Decision
Discrepancies, Number of Data Flow Discrepancies, and Number of Over
Reporting Discrepancies.

### Perform eDV

![](media/image6.png){width="6.495138888888889in" height="4.7125in"}

[]{#__RefHeading___Toc173723085 .anchor}**Exhibit 2-5.** Perform eDV
Page

[Entry Fields]{.underline}

**Extract data from SDWIS/STATE Database Checkbox**

When checked, this function will use the SDWIS/STATE database specified
in the Configure eDV setup and the Specified Verification Periods to
extract data into a series of CSV files. The result will be a .zip file
with the name XX_EDV_extracttimestamp.zip where XX is the two-character
Primacy Agency specified in the Configure eDV page. Example:
IL_EDV_2007-03-1615-15-05.zip. The file will be located on the
application server in the EDVExport folder of the eDV context. It can be
accessed from the Dashboard page which will contain a link to the file
when the process completes successfully.

Prerequisites for this process are:

-   Configure eDV page \-- all entry field values are required

-   Specify Verification Periods page \-- at least one rule should have
    dates specified (only data related to the rules for which
    verification dates have been specified will be extracted).

If this checkbox is not valued, the first radio button under the Import
SDWIS/STATE Extract into eDV will be unselected and disabled.

This function may be run by itself to create the extracted SDWIS/STATE
zip file and may take several hours depending on database size, number
of rules, and timeframe within which DV Periods are specified. Status of
the extract may be tracked on the Dashboard page.

**Import SDWIS/STATE Extract into eDV Checkbox**

When checked, this function will load data from a SDWIS/STATE database
extraction into the eDV database. The subordinate radio buttons indicate
the extract file to be used.

If *Extract data from SDWIS/STATE Database* is checked, the first radio
button, *Use the currently extracted data*, is enabled. This selection
indicates that the input file for the import function is the output file
from the requested extract function. This radio button will not be
enabled if the *Extract data from SDWIS/STATE Database* is not checked.

The second radio button, *Use the extract supplied by a Primacy Agency*,
is used to indicate a previously extracted file should be used for the
import and requires that file path be specified. The directory structure
can be searched using the Browse button or the path and file name can be
typed into the field directly. Selection of this second radio button
requires a valid file name and path be specified. The input field and
Browse button will only be enabled if this radio button is selected. If
the radio button is unselected, the input field value will be removed
and the input field and Browse button will disable.

This function may be run by itself to load the SDWIS/STATE extraction
into SDWIS/eDV and may take several hours, depending on extraction size.
Status of the import may be tracked on the Dashboard page.

**Import SDWIS/ODS QA Extract into eDV Checkbox**

When checked, this function will load data from a SDWIS/ODS QA Extract
into the eDV database and requires the file path be specified. The input
field and Browse button will only be enabled if this option is checked.
If the option is unchecked, the input field value will be removed and
the input field and Browse button will disable. The directory structure
can be searched using the Browse button or the path and file name can be
typed into the field directly. Selection of this function requires that
a valid file name and path be specified.

This function may be run by itself to load the SDWIS/ODS QA Extract into
the eDV database. Status may be tracked on the Dashboard page.

**Perform Data Verification Checkbox**

When checked, this function will:

-   Execute SDWIS/eDV Compliance function similar to SDWIS/STATE CDS
    Setup.

-   Execute SDWIS/eDV Compliance functions for each rule for which a DV
    Period was specified.

-   Execute SDWIS/eDV Discrepancy Identification function which compares
    the violations from the three data sources (SDWIS/ODS QA Extract,
    Primacy Agency's SDWIS/STATE, eDV Compliance functions).

-   Prepare the data for presentation via the eDV Reports menu item.

Detailed specifications for this function are described in section 3.0
of this document.

This function will populate the SDWIS/eDV database with violations and
discrepancies and may take several hours, depending on database size and
number of rules for which DV periods are specified. Status may be
tracked on the Dashboard page.

[Buttons]{.underline}

**Start** The Start button will begin processing the functions indicated
by the check box selections. The start button will be enabled when a
check box is selected. Edits performed are:

-   If the Import of the SDWIS/STATE extract was checked but neither
    radio button was specified the error message will be **To import
    data into SDWIS/eDV, please select either radio button to use the
    SDWIS/STATE extract supplied by a Primacy Agency, or use the
    currently extracted data.**

-   If the Import of the SDWIS/STATE data was checked and the radio
    button to use a Primacy Agency source was selected, but the file
    name and path were not provided, the error message will be **Please
    provide SDWIS/STATE Extract file path and name.** If file name and
    path are provided but cannot be found, the error message will be
    either **SDWIS/STATE Extract file is not a zip file, or SDWIS/STATE
    Extract file not found.** The erroneous file name and path will be
    removed from the input field.

-   If the Import of the SDWIS/ODS QA extract was checked but no file
    path and name was provided, the error message will be **Please
    provide SDWIS/ODS QA Extract file path and name. .** If file name
    and path are provided but cannot be found, the error message will be
    **SDWIS/ODS QA Extract file is not a zip file, or SDWIS/ODS QA
    Extract file not found.** The erroneous file name and path will be
    removed from the input field.

> If an import of a SDWIS/STATE database is requested, all SDWIS/STATE
> tables in the SDWIS/eDV database will be emptied and the contents
> replaced. The TDVResult and TDVPRCLG tables will also be emptied.

If an import of the SDWIS/ODS QA Extract is requested, the SDWIS/eDV's
ODS tables will be emptied and the contents replaced. The TDVRESLT and
TDVPRCLG tables will also be emptied.

> If **Perform Data Verification** is requested, the software checks if
> a row exists in TDVPRCLG table with Process Number of 11. If not, it
> proceeds with process initiation. If such a row exists, it presents a
> confirmation page:

**Clear** Will remove all user selections from check boxes, radio
buttons, and input fields.

### Specify Verification Periods Page

Selecting **Specify Verification Periods** menu item from the SDWIS/eDV
Home Page will present the Specify Verification Periods page (Exhibit
2-6) with existing date ranges from the DV Period table presented beside
each rule.

The user should enter a DV period begin date and end date for each Rule
for which DV should be performed.

![](media/image7.png){width="6.495138888888889in" height="4.7125in"}

[]{#__RefHeading___Toc173723086 .anchor}**Exhibit 2-6.** Specify
Verification Periods Page

[Entry Fields]{.underline}

**Begin Date** The date when the DV period will begin. It will also

> determine the data extraction period begin date, depending on the Rule
> for which the begin date applies. Begin Date will be optional. If left
> blank, the corresponding rule will not be included in the DV process
> and no assessments will be included in the DV reports.

**End Date** The date when the DV period will end. It will also be used
to determine the data extraction period end date, dependent upon the
Rule for which the end date applies.

End Date will be required if the corresponding Begin Date is provided.
If Begin Date is blank but End Date is provided, the error message will
be Both Begin and **End Dates are needed for rule.** The End Date must
be greater than or equal to the Begin Date. If not, the error message
will be **End Date must be on or after the Begin Date.** In either case,
the End Date field will be designated an error and will contain the
cursor.

**TCR Routine**

**Sample Count** The threshold number of routine samples to be required
before a repeat schedule will be created after a repeat positive sample
is validated. Default value will be 20. Field will be protected until
the TCR DV Period dates are provided. Must be a numeric value.

[Buttons]{.underline}

**Apply** Edits to ensure all provided end dates have provided begin
dates that are equal or prior to the end dates.

If no errors:

Delete all rows in the DV Period Table (TDVDVPRD).

For each rule for which dates are provided:

-   Create rows in the DV Period Table (TDVDVPRD)

    -   Using Exhibit 2-7, the software creates a row for each
        corresponding TMNRULE row.

    -   Calculate the data extraction date range (extract_begin_dt and
        extract_end_dt columns) using the user-supplied data range and
        the extraction rule.

    -   If a DV period is provided for the TCR, also populate the
        TCR_ROUTINE_CNT with the value supplied on the page.

Return to the Home Page.

**Refresh** Ignores the changes and refreshes the page with current
values from the DV Period Table.

Exhibit 2-7 lists the logic used to create records in table TDVDVPRD.

+-------------------+--------------------------+----------+----------+
| **Data            | **TMNRULE.name rows      | **TM     | **Ex     |
| Verification      | populated with user      | NRULE.ab | traction |
| Period            | provided data range**    | breviati | Date     |
| Maintenance Page  |                          | on_txt** | Cal      |
| Label**           |                          |          | culation |
|                   |                          |          | Rule**   |
+-------------------+--------------------------+----------+----------+
| Arsenic           | Arsenic Rule             | AR       | User_Be  |
|                   |                          |          | gin_date |
|                   |                          |          | -- 3     |
|                   |                          |          | years;   |
|                   |                          |          | Equal    |
|                   |                          |          | user_    |
|                   |                          |          | end_date |
+-------------------+--------------------------+----------+----------+
| Consumer          | 1.  Consumer confidence  | CCR      | None-no  |
| Confidence and    |     reports              |          | ex       |
| Public Notice     |                          | PNR      | traction |
|                   | 2.  public notice rule,  |          | relative |
|                   |     revised              |          | to       |
|                   |                          |          | CCR/PN.  |
|                   |                          |          | Columns  |
|                   |                          |          | will     |
|                   |                          |          | always   |
|                   |                          |          | be null. |
+-------------------+--------------------------+----------+----------+
| Chemicals         | 1.  PHASE I RULE         | PH1      | User_Be  |
|                   |                          |          | gin_date |
|                   | 2.  PHASE II RULE        | PH2      | -- 3     |
|                   |                          |          | years;   |
|                   | 3.  PHASE V RULE         | PH5      | Equal    |
|                   |                          |          | user_    |
|                   |                          |          | end_date |
+-------------------+--------------------------+----------+----------+
| Disinfectants and | STAGE 1 DISINFECTANTS &  | DBP1     | User_Be  |
| Disinfection      | D. BYPRODUCTS            |          | gin_date |
| Byproducts        |                          |          | -- 1     |
|                   |                          |          | year;    |
|                   |                          |          | User_en  |
|                   |                          |          | d_date + |
|                   |                          |          | 1 year   |
+-------------------+--------------------------+----------+----------+
| Lead and Copper   | LEAD & COPPER RULE       | PBCU     | User_Be  |
|                   |                          |          | gin_date |
|                   |                          |          | -- 3     |
|                   |                          |          | years;   |
|                   |                          |          | Equal    |
|                   |                          |          | user_    |
|                   |                          |          | end_date |
+-------------------+--------------------------+----------+----------+
| Radionuclides     | RADIONUCLIDE RULE,       | RAD,     | User_Be  |
|                   | REVISED                  | RADR,    | gin_date |
|                   |                          | RADN     | -- 3     |
|                   |                          |          | years;   |
|                   |                          |          | Equal    |
|                   |                          |          | user_    |
|                   |                          |          | end_date |
+-------------------+--------------------------+----------+----------+
| Surface Water     | 1.  SURFACE WATER        | SWTR     | User_Be  |
| Treatment         |     TREATMENT            |          | gin_date |
|                   |                          | ESWT     | -- 1     |
|                   | 2.  INTERIM ENHANCED     |          | year;    |
|                   |     SWTR                 | FBR      | User_en  |
|                   |                          |          | d_date + |
|                   | 3.  FILTER BACKWASH RULE | LT1R     | 1 year   |
|                   |                          |          |          |
|                   | 4.  LONG TERM 1 SWTR     |          |          |
+-------------------+--------------------------+----------+----------+
| Total Coliform    | TOTAL COLIFORM RULE      | TCR      | User_Be  |
|                   |                          |          | gin_date |
|                   |                          |          | -- 1     |
|                   |                          |          | year;    |
|                   |                          |          | User_en  |
|                   |                          |          | d_date + |
|                   |                          |          | 1 year   |
+-------------------+--------------------------+----------+----------+

[]{#__RefHeading___Toc173723087 .anchor}**Exhibit 2-7.** Extraction
Dates by Rule

### Configure eDV

The Configure eDV menu item from the SDWIS/eDV home page presents the
page where specifications relating to the data extraction from a
SDWIS/STATE database are made. These inputs are only required if the
extraction from SDWIS/STATE database function is to be performed.

![](media/image8.png){width="6.495138888888889in" height="4.7125in"}

[]{#__RefHeading___Toc173723088 .anchor}**Exhibit 2-8.** Configure eDV
Page

[Entry Fields:]{.underline}

**Database Type** This drop-down selection indicates the database
management system in

> which the source SDWIS/STATE database is defined. Available selections
> are SQL Server, Oracle, and DB2.

**Database Server (Machine name or IP Address)**

This entry field is the database server IP address or resolved DSN name
for the server on which the source SDWIS/STATE database is defined. It
is used to formulate the connection to the database.

**Database Port (e.g., 1521, 1433, 50000)**

This entry field is the server port on which the source SDWIS/STATE
database is defined. It is used to formulate the connection to the
database.

**Service\\Database**

**Name**

**(e.g., SDWIS2003)** In Oracle this parameter is known as a Service
name (SDWS2003) and in SQL server it is known as a Database name (e.g.,
MIR10)

**Database Schema**

**Name** The database schema name is used to authenticate the extraction
function to the database.

**Database Schema**

**Password** The database schema password is used to authenticate the
extraction function to the database.

[Buttons]{.underline}

**Apply** Saves the provided values in an EDV.ini file located within
the eDV context of the Web Apps folder. Returns message **Parameters
have been updated.**

**Test Database**

**Connection** Tests the connection to the SDWIS/STATE database using
the

> provided values. The connection must test successfully in order to
> perform a SDWIS/STATE database extract. Returns message **Test
> Completed Successfully** when true.

# SDWIS/eDV detailed design

## Extract from SDWIS/State Database

The extraction process is designed to create a set of files that contain
a focused set of data from the SDWIS/STATE database. The extraction uses
the logic defined in sections 2.1.2, 2.1.3, and 2.1.4 above and the
extraction dates derived from the user's DV Period input (Exhibit 2-7)
to define the extraction domain.

The extraction process connects to the SDWIS/STATE database using JDBC.
It runs a set of ETL (Kettle by Pentaho) transformations to extract data
from the SDWIS/STATE tables into CSV format. The files are created in
the \"Export\" folder on the server. Each CSV file contains data from
only one table. In order to save disk space, Kettle zips each CSV file
into an individual zip file.

At the end of the data extraction, all the individual zip files are
combined into a single zip file, namedt XX-EDV-YYYY-MM-DD-HH-MM.zip,
where XX is the two-character state code that the extraction process
retrieves from the TINPRT_GOVT_CODE column of the TINPRT table in the
SDWIS/STATE database. The process stores this combined zip in the
\"EDVExports\" folder on the server and deletes all the intermediate
files from the \"Export\" folder. 

 

The combined ZIP file is accessible from a link on the Dashboard page of
SDWIS/eDV.

## Load SDWIS/STATE Database Extraction

The Load SDWIS/STATE Database Extraction process uploads the ZIP file
from the user-designated path into a temporary folder and unzips its
contents into \"Import\\SDWIS\" folder. Using a set of load processes
created with the Kettle product, it processes each individual table file
one file at a time into the SDWIS/eDV database. Existing data in the
SDWIS/eDV database is deleted prior to the load.

Using the extraction logic documented in Exhibit 2-1, the software loads
all tables extracted from the primacy agency's SDWIS/STATE database into
the eDV database with the exception of:

-   TMNVIOL

-   TMNVGRP

-   TMNVCRAT

-   TMNORVIA

These tables must remain empty during the DV process so that the
compliance process will not find duplicate violations. These tables will
be loaded at the completion of the compliance processing.

After loading the tables, the load process deletes records from the DV
Result table (TDVRESLT) and all the CDS tables (tables that begin with
TCD) except the CDS Setup Execution Log (TCDSSLOG) and CDS Message
(TCDSPMG) tables.

## Load SDWIS/ODS QA Extract

The Load SDWIS/ODS QA Extract process uploads the SDWIS/ODS QA Extract
from the user-designated path into a temporary folder, unzips its
contents into the \"Import\\ODS\" folder. Using a set of Kettle load
processes, the software loads the WaterSystem and Violation data from
the SDWIS/ODS QA Extract into the SDWIS/eDV database. At the end of the
load, the load process deletes all records from the DV Result (TDVRESLT)
table.

## Perform Data Verification

This section describes the detailed design specifications of the
SDWIS/eDV Determination processes. All processes in this section, with
the exception of those documented in sections Reload Primacy Agency
Violations and DV Discrepancy Identification reuse existing SDWIS/STATE
compliance determination/assistance processing functions that have been
regenerated for SDWIS/eDV.

For all Compliances processes, the regulating agency marked as Primacy
will be used. The software selects the government agency
(TINGOVAG)/legal entity (TINLGENT) with primacy_indicator_code = 'Y'.
This will be referenced below as the Primacy Agency.

The System Variable work group is used throughout these processes. For
the SDWIS/eDV function, set the system variable state-code to DV and the
UserID to EDV.

### Process 1: eDV CDS Setup 

This java-based process, EDV Compliance Determination, executes the
following subset of SDWIS/STATE CDS Setup:

-   2 - cds s assoc mon prd to smp sched

-   3a- cds s process mcl acl trl addchg

-   3b - cds s process fanl max addchg

-   4 - cds s dissaoc sar from ssmpa

-   5 - cds s calculate total results

-   6 - cds s assoc sar to ssmpa

-   7 - cds s assoc mon prd to smp sched

-   8 - cds s aggregate 90th %

-   9 - cds s assign smp smry to ssmpa

-   10 - cds s calculate mp average

-   11 - cds s calculate mcl value

-   13 - cds s reassess cand violation

-   14 - cds s chlorite chlorine dioxide

-   15 - cds s proc chlorn chlormne sum

At the beginning of the process, if the DV Process Log table is empty,
create a row in TDVPRCLG. If the TDVPRCLG has a row with process number
of 1 and Subprocess number of 0 with an end_timestamp not null, skip
this process and proceed to process 2.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      1

  Sub Process Number                  0

  Process Name                        eDV CDS Setup

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723089 .anchor}**Exhibit 3-1.** Process Logic
Row -- Process 1

Prior to each process, the software checks if this process was already
executed. If the SubProcess about to execute has a row in the eDV
Process Log with an end_timestamp not null, then the process completed
and can be skipped. If it neither exists nor has a valued end_timestamp,
it should be run.

  ----------- ----------- ----------- ----------- -------------- -------------
              **Process   **Sub       **Process   **SubProcess   **Init
              Number**    Process     Name**      Name**         Timestamp**
                          Number**                               

  cds s       1           1           eDV CDS     ASC MP TO      Current
  associ mon                          Setup       SCHED          timestamp
  prd to smp                                                     
  sched                                                          

  cds s       1           2           eDV CDS     MCL ACL TRL    Current
  process mcl                         Setup                      timestamp
  acl trl                                                        
  addchg                                                         

  cds s       1           3           eDV CDS     FANL MAX       Current
  process                             Setup                      timestamp
  fanl max                                                       
  addchg                                                         

  cds s       1           4           eDV CDS     DIS SAR        Current
  dissaoc sar                         Setup                      timestamp
  from ssmpa                                                     

  cds s       1           5           eDV CDS     CALC TOTAL     Current
  calculate                           Setup                      timestamp
  total                                                          
  results                                                        

  cds s assoc 1           6           eDV CDS     ASC SAR        Current
  sar to                              Setup                      timestamp
  ssmpa                                                          

  cds s assoc 1           7           eDV CDS     ASC MP TO      Current
  mon prd to                          Setup       SCHED          timestamp
  smp sched                                                      

  cds s       1           8           eDV CDS     AGG 90^TH^     Current
  aggregate                           Setup                      timestamp
  90th %                                                         

  cds s       1           9           eDV CDS     ASGN SAMP SUM  Current
  assign smp                          Setup                      timestamp
  smry to                                                        
  ssmpa                                                          

  cds s       1           10          eDV CDS     CALC MP AVG    Current
  calculate                           Setup                      timestamp
  mp average                                                     

  cds s       1           11          eDV CDS     CALC MCL       Current
  calculate                           Setup                      timestamp
  mcl value                                                      

  cds s       1           12          eDV CDS     REASSESS CAND  Current
  reassess                            Setup       VIO            timestamp
  cand                                                           
  violation                                                      

  cds s       1           13          eDV CDS     CHLORITE       Current
  chlorite                            Setup       CHLORIN DIOX   timestamp
  chlorine                                                       
  dioxide                                                        

  cds s proc  1           14          eDV CDS     CHLORINE       Current
  chlorn                              Setup       CHLORAMINE     timestamp
  chlormne                                                       
  sum                                                            
  ----------- ----------- ----------- ----------- -------------- -------------

[]{#__RefHeading___Toc173723090 .anchor}**Exhibit 3-2.** Process Logic
Row -- Process 1 Subprocesses

At the end of each process, update the end_timestamp in the eDV Process
Log table with the current timestamp and then take a commit using the
**MG-COMMIT** action block.

At the end of the last process, the software updates the eDV Process Log
end_timestamp for the Process Number 1, Sub Process Number 0 with the
current timestamp.

### Process 2: Lead and Copper Rule Compliance Process

The process for the Lead and Copper Rule will implement identification
of the ACL exceedence and the M&R for tap, and TT and M&R for water
quality parameters and source water.

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 2, Sub Process Number 0

    a.  create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      2

  Sub Process Number                  0

  Process Name                        LCR

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723091 .anchor}**Exhibit 3-3.** Process Logic
Row -- Process 2

b.  Reuse common action block **cds_create_rec_in_cds_report_log**.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PBCU rule
>
> CDS Report Log populated with

-   Report Type of 'LC'

-   Import_lcr acl_check indicator = 'Y'

-   Import_lcr tap_check indicator = 'Y'

-   Import_lcr wqp tt n mnr_check indicator = 'Y'

-   Import_lcr sw tt n mnr_check indicator = 'Y'

> Outputs: CDS Report log IS number

2.  If it exists and the end_timestamp is valued, proceed to Process 3.

3.  If it exists and the end_timestamp is null

    a.  Read for an existing CDS Report Log row with report_type = LC.

    b.  If not found reuse common action block
        **cds_create_rec_in_cds_report_log**.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the LCR rule
>
> CDS Report Log populated with

-   Report Type of 'LC'

-   Import_lcr acl_check indicator = 'Y'

-   Import_lcr tap_check indicator = 'Y'

-   Import_lcr wqp tt n mnr_check indicator = 'Y'

-   Import_lcr sw tt n mnr_check indicator = 'Y'

> Outputs: CDS Report log IS number

####  LCR Action Level Exceedence 

1)  Check to see if a row exists in the eDV Process Log for Process 2,
    Sub Process 1.

    a.  If none is found, create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      2

  Sub Process Number                  1

  Process Name                        LCR

  SubProcess Name                     ACL

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723092 .anchor}**Exhibit 3-4.** Process Logic
Row -- Process 2, Subprocess 1

b.  If one is found

    i.  If the end_timestamp is valued, skip and proceed to the next sub
        process

    ii. Else proceed with this sub process.

```{=html}
<!-- -->
```
2)  Reuse action block **rpt_s_pbcu_acl_exceedence_check**

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PBCU rule
>
> CDS Report Log IS number
>
> Outputs: None

3)  Update the eDV Process Log row for Process Number 2, Sub Process
    Number 1 with the end_timestamp.

4)  Reuse action block **MG_commit**.

####  LCR Tap Monitoring and Reporting

1)  Check to see if a row exists in the eDV Process Log for Process 2,
    Sub Process 2.

    a.  If none is found, create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      2

  Sub Process Number                  2

  Process Name                        LCR

  SubProcess Name                     TAP

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723093 .anchor}**Exhibit 3-5.** Process Logic
Row -- Process 2, Subprocess 2

b.  If one is found

    i.  If the end_timestamp is valued, skip and proceed to the next sub
        process

    ii. Else proceed with this sub process.

```{=html}
<!-- -->
```
2)  Reuse action block **rpt_s_pbcu_tap_mnr_check**

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PBCU rule
>
> CDS Report Log IS number
>
> Outputs: None

3)  Update the eDV Process Log row for Process Number 2, Sub Process
    Number 2 with the end_timestamp.

4)  Reuse action block **MG_commit**.

####  LCR WQP M&R and Treatment Technique

1)  Check to see if a row exists in the eDV Process Log for Process 2,
    Sub Process 3.

    a.  If none is found, create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      2

  Sub Process Number                  3

  Process Name                        LCR

  SubProcess Name                     WQP

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723094 .anchor}**Exhibit 3-6.** Process Logic
Row -- Process 2, Subprocess 3

b.  If one is found

    i.  If the end_timestamp is valued, skip and proceed to the next sub
        process

    ii. Else proceed with this sub process.

```{=html}
<!-- -->
```
2)  Reuse action block **rpt_s_pbcu_wqp_tt_mnr_comp_chk**

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PBCU rule
>
> CDS Report Log IS number
>
> Outputs: None

3)  Update the eDV Process Log row for Process Number 2, Sub Process
    Number 3 with the end_timestamp.

4)  Reuse action block **MG_commit**

####  LCR Source Water TT & M&R

1)  Check to see if a row exists in the eDV Process Log for Process 2,
    Sub Process 4.

    a.  If none is found, create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      2

  Sub Process Number                  4

  Process Name                        LCR

  SubProcess Name                     SRCWATER

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723095 .anchor}**Exhibit 3-7.** Process Logic
Row -- Process 2, Subprocess 4

b.  If one is found

    i.  If the end_timestamp is valued, skip and proceed to the next sub
        process

    ii. Else proceed with this sub process.

```{=html}
<!-- -->
```
2)  Reuse action block **rpt_s_pbcu_src_water_tt_mnr_chk**

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PBCU rule
>
> CDS Report Log IS number
>
> Outputs: None

3)  Update the eDV Process Log row for Process Number 2, Sub Process
    Number 4 with the end_timestamp.

4)  Reuse action block **MG_commit**

5)  Update the eDV Process Log row for Process Number 2, Sub Process
    Number 0 with the end_timestamp.

### Process 3: Surface Water Treatment Rule (SWTR) Compliance Process 

The process for the Surface Water Treatment Rule will implement
identification of the monitoring violation and treatment technique
violation for Entry Point and Distribution System Residual Disinfectant
Concentration (RDC) and Turbidity.

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 3, Sub Process Number 0

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      3

  Sub Process Number                  0

  Process Name                        SWTR

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723096 .anchor}**Exhibit 3-8.** Process Logic
Row -- Process 3

b.  Read for an existing CDS Report Log row with report_type = SW.

c.  If not found, reuse common action block
    **cds_create_rec_in_cds_report_log** to create the CDS Report Log.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   Report Type of 'SW'

-   Cds_report_log federal_ep_rdc_selected_ind = 'Y'

-   Cds_report_log federal_ds_rdc_selected\_= 'Y'

-   Cds_report_log turbidity_selected\_= 'Y'

> Outputs: CDS Report log IS number

2.  If it exists and the end_timestamp is valued, proceed to Process 4.

3.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 3, Sub Process Number 1

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      3

  Sub Process Number                  1

  Process Name                        SWTR

  SubProcess Name                     FEDEP

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723097 .anchor}**Exhibit 3-9.** Process Logic
Row -- Process 3, Subprocess 1

4.  If it exists and the end_timestamp is valued, proceed to Process 3,
    Subprocess 2.

5.  Reuse common action block **cds_swtr_fed_ep_rdc_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

6.  Update the eDV Process Log row for Process Number 3, Sub Process
    Number 1 with the end_timestamp.

7.  Reuse action block **MG_commit**

8.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 3, Sub Process Number 2

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      3

  Sub Process Number                  2

  Process Name                        SWTR

  SubProcess Name                     FEDDS

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723098 .anchor}**Exhibit 3-10.** Process Logic
Row -- Process 3, Subprocess 2

9.  If it exists and the end_timestamp is valued, proceed to Process 3,
    Subprocess 3.

10. Reuse common action block **cds_swtr_fed_ds_rdc_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

11. Update the eDV Process Log row for Process Number 3, Sub Process
    Number 2 with the end_timestamp.

12. Reuse action block **MG_commit**

13. Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 3, Sub Process Number 3

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      3

  Sub Process Number                  3

  Process Name                        SWTR

  SubProcess Name                     TURBID

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723099 .anchor}**Exhibit 3-11.** Process Logic
Row -- Process 3, Subprocess 3

14. If it exists and the end_timestamp is valued, Update the eDV Process
    Log row for Process Number 3, Sub Process Number 0 with the
    end_timestamp.

15. Reuse common action block **cds_swtr_cf_indiv_eff_turb_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

16. Update the eDV Process Log row for Process Number 3, Sub Process
    Number 2 with the end_timestamp.

17. Reuse action block **MG_commit**

18. Update the eDV Process Log row for Process Number 3, Sub Process
    Number 0 with the end_timestamp.

19. Reuse action block **MG_commit**

### Process 4: Disinfectant/Disinfection By Product (D/DBP) Rule Compliance Process

The process for the Disinfectant/Disinfection By Product Rule will
implement identification of the violations for

-   Bromate/Bromide Monitoring

-   Chlorine/Chloramine Maximum Residual Disinfectant Level

-   Entry Point Chlorine Dioxide/Chlorite

-   Distribution System Chlorine Dioxide/Chlorite

-   Disinfectant By Product Precursor

-   Total Trihalomethane (TTHM) and Haloacetic Acid (HAA5).

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 4, Sub Process Number 0

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      4

  Sub Process Number                  0

  Process Name                        DDBP

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723100 .anchor}**Exhibit 3-12.** Process Logic
Row -- Process 4

b.  Read for an existing CDS Report Log row with report_type = DD.

c.  If not found, reuse common action block
    **cds_create_rec_in_cds_report_log** to create the CDS Report Log.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the DBP1 rule
>
> CDS Report Log populated with

-   Report Type of 'DD'

-   Cds_report_log tthm_haa5_mr_selected_ind = 'Y'

-   Cds_report_log dbp_precursors_selected_ind= 'Y'

-   Cds_report_log bromate_bromide_selected_ind= 'Y'

-   Cds_report_log cl2_chloramine_selected_ind = 'Y'

-   Cds_report_log clo2_chlorite_ep_selected_ind= 'Y'

-   Cds_report_log clo2_chlorite_ds \_selected_ind= 'Y'

> Outputs: CDS Report log IS number

2.  If it exists and the end_timestamp is valued, proceed to Process 5.

3.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 4, Sub Process Number 1

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      4

  Sub Process Number                  1

  Process Name                        DDBP

  SubProcess Name                     DBPPC

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723101 .anchor}**Exhibit 3-13.** Process Logic
Row -- Process 4, Subprocess 1

4.  If it exists and the end_timestamp is valued, proceed to Process 4,
    Subprocess 2.

5.  Reuse common action block **cds_swtr_dbp_precursor_comp_chec**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

6.  Update the eDV Process Log row for Process Number 4, Sub Process
    Number 1 with the end_timestamp.

7.  Reuse action block **MG_commit**

8.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 4, Sub Process Number 2

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      4

  Sub Process Number                  2

  Process Name                        DDBP

  SubProcess Name                     BBMR

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723102 .anchor}**Exhibit 3-14.** Process Logic
Row -- Process 4, Subprocess 2

9.  If it exists and the end_timestamp is valued, proceed to Process 3,
    Subprocess 3.

10. Reuse common action block **cds_swtr_brom_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

11. Update the eDV Process Log row for Process Number 4, Sub Process
    Number 2 with the end_timestamp.

12. Reuse action block **MG_commit**

13. Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 4, Sub Process Number 3

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      4

  Sub Process Number                  3

  Process Name                        DDBP

  SubProcess Name                     CLMRDL

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723103 .anchor}**Exhibit 3-15.** Process Logic
Row -- Process 4, Subprocess 3

14. If it exists and the end_timestamp is valued, proceed to Process 4,
    Subprocess 4.

15. Reuse common action block **cds_swtr_cl_mrdl_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

16. Update the eDV Process Log row for Process Number 4, Sub Process
    Number 3 with the end_timestamp.

17. Reuse action block **MG_commit**

18. Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 4, Sub Process Number 4

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      4

  Sub Process Number                  4

  Process Name                        DDBP

  SubProcess Name                     CLEP

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723104 .anchor}**Exhibit 3-16.** Process Logic
Row -- Process 4, Subprocess 4

19. If it exists and the end_timestamp is valued, proceed to Process 4,
    Subprocess 5.

20. Reuse common action block **cds_swtr_ep_clo2_clo3_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

21. Update the eDV Process Log row for Process Number 4, Sub Process
    Number 4 with the end_timestamp.

22. Reuse action block **MG_commit**

23. Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 4, Sub Process Number 5

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      4

  Sub Process Number                  5

  Process Name                        DDBP

  SubProcess Name                     CLDS

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723105 .anchor}**Exhibit 3-17.** Process Logic
Row -- Process 4, Subprocess 5

24. If it exists and the end_timestamp is valued, proceed to Process 4,
    Subprocess 6.

25. Reuse common action block **cds_swtr_ds_cl2_clo3_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

26. Update the eDV Process Log row for Process Number 4, Sub Process
    Number 5 with the end_timestamp.

27. Reuse action block **MG_commit**

28. Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 4, Sub Process Number 6

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      4

  Sub Process Number                  6

  Process Name                        DDBP

  SubProcess Name                     TTHM

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723106 .anchor}**Exhibit 3-18.** Process Logic
Row -- Process 4, Subprocess 6

29. If it exists and the end_timestamp is valued, proceed to Process 4,
    Subprocess 0 end timestamp update.

30. Reuse common action block **cds_swtr_tthm_haa_comp_check**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the SWTR rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

31. Update the eDV Process Log row for Process Number 4, Sub Process
    Number 6 with the end_timestamp.

32. Reuse action block **MG_commit**

33. Update the eDV Process Log row for Process Number 4, Sub Process
    Number 0 with the end_timestamp.

34. Reuse action block **MG_commit**

### Process 5: Total Coliform Rule (TCR) Compliance Process

The process for the Total Coliform Rule will implement identification of
the acute MCL violations as well as the non-acute MCL and monitoring
violations. It will reuse the existing processes for Acute MCL
identification that SDWIS/STATE implements in a real-time capacity. The
acute MCLs will be recorded in the TMNVIOL table, but then transferred
to the TCDSVIOL table and deleted from the TMNVIOL table.

Unlike any other compliance process, the TCR Compliance process requires
that the executing userid be associated to a government agency that is
authorized for the total coliform rule. Since the SDWIS/eDV processes
are all being executed under the userid 'EDV', this process will begin
by inserting the required rows in the individual (TININDIV), legal
entity (TINLGENT), user, (D_USER) and individual government agency
assignment (TINIGAA) to establish the EDV userid to the primacy agency
authorized for the total coliform rule.

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 5, Sub Process Number 0 create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      5

  Sub Process Number                  0

  Process Name                        TCR

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723107 .anchor}**Exhibit 3-19.** Process Logic
Row -- Process 5

2.  Read for an existing CDS Report Log row with report_type = TCR.

3.  If not found, reuse common action block
    **cds_create_rec_in_cds_report_log**.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> Data Range populated with the DV Period Table user begin and end dates
> for the TCR rule
>
> CDS Report Log populated with

-   Report Type of 'TCR'

-   Tcr_mr_comp_ind = 'Y'

-   Tcr_non_acute_mcl_ind = 'Y'

> Outputs: CDS Report log IS number

4.  If it exists and the end_timestamp is valued, proceed to Process 6.

5.  If it exists and the end_timestamp is null

6.  Read for an existing CDS Report Log row with report_type = TCR.

    If not found reuse common action block
    **cds_create_rec_in_cds_report_log**.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> Data Range populated with the DV Period Table user begin and end dates
> for the TCR rule
>
> CDS Report Log populated with

-   Report Type of 'TCR'

-   Tcr_mr_comp_ind = 'Y'

-   Tcr_non_acute_mcl_ind = 'Y'

Outputs: CDS Report log IS number

####  Acute MCL Identification

This is a batch implementation design of the online process for
validating positive TCR Results. For reference, the online procedure is
called MBS_C_TCR_SAR_WORKLIST. The SDWIS/STATE application uses an
action block called **new_read_sar_exceedences**. It is a very sluggish
performer and doesn't quite meet the eDV needs for order of processing.
The plan is to not reuse the action block, but create a new one that can
possibly be reused in the SS 2.0 application.

1)  Check to see if a row exists in the eDV Process Log for Process 5,
    Sub Process 1.

    a.  If none is found, create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      5

  Sub Process Number                  1

  Process Name                        TCR

  SubProcess Name                     ACUTE 21

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723108 .anchor}**Exhibit 3-20.** Process Logic
Row -- Process 5, Subprocess 1

b.  If one is found

    i.  If the end_timestamp is valued, skip and proceed to the next sub
        process

c.  Else proceed with this sub process Retrieving all the TCR positive
    SARs to be validated.

> For Each SAR with:

-   Concentration_measure \> 0

-   Data_quality_code = 'P' or 'A'

-   Associated to Analyte with code of '3100, 3014, or 3013'

-   Associated to a Sample with

```{=html}
<!-- -->
```
-   Rejection_reason_code = spaces

-   For Compliance Indicator = 'Y'

-   With monitoring period begin date \<= TCR DV Period End Date and
    monitoring period begin date \>= TCR DV Period Begin Date -- 3
    months

-   Associated to a WSF (via the association to sampling point) that is
    type_code of 'DS'

> Order by WATER_SYSTEM.number ascending, SAMPLE.collection_end_date
> ascending, ANALYTE.code descending.

1.  reuse action block **validate_sar_exceedence** Inputs: SAR

2.  reuse action **check_for_current_MCL.** This checks to see if a type
    21 violation already exists for this monitoring period and water
    system.

    1.  If it returns none found (exit state all is well)

        1.  reuse action block **determine_mcl_violation** Inputs: SAR,
            monitoring period of SAR, water system of sample associated
            to SAR, TCR rule (HQ, 1)

    2.  Else

        1.  continue processing without creating an MCL violation

3.  If analyte for SAR is 3100

    1.  reuse action block **shm0400_determine_nonroutine_rqmt.**

> Inputs:

-   Analyte from the SAR

-   Government agency -- type code set to 'FD'

-   Monitoring Requirement with sample_type_code set to RP,
    tmnmnr_is_number set to 0 and tmnmnr_st_code set to 'HQ'

-   Rule set to tmnrule_is_number = 1 and tmnrule_st_code = 'HQ'

-   Water system from the sample

-   Sample

> Outputs:

-   Monitoring requirement -- tmnmnr_is_number

    1.  Read for the TCR Sample schedule that was in effective on the
        Sample collection end date.

    2.  If SAR is for a Sample type code of RT

        1.  If the monitoring requirement ((is of type TR) or (if it is
            RT and the sample count is \> 1) and if the repeat
            monitoring requirement (returned from step 3 above) is for 4
            daily repeats

            1.  Read for the repeat monitoring requirement for the TCR
                Rule that calls for 3 daily repeats

        2.  Else

            1.  Use the monitoring requirement returned from step 3
                above

        3.  Reuse action block **determine repeat tcr schedule**

> Inputs:

-   SAR

-   Water System from the sample associated to the SAR

-   Analyte from SAR

-   Repeat Monitoring Requirement

-   Monitoring Period of the SAR

-   TCR Rule (HQ, 1)

-   Repeat Sample Schedule: begin_date set to sample collection end
    date. End_date set to sample collection end date + 15 days.

    1.  Proceed to next SAR

    ```{=html}
    <!-- -->
    ```
    1.  Else ( it is RP)

        1.  If the monitoring requirement (is of type TR or RT) and the
            sample count is \<
            TDVDVPRD.tcr_repeat_schedule_sample_count)

            1.  Reuse action block **determine repeat tcr schedule**

> Inputs:

-   SAR

-   Water System from the sample associated to the SAR

-   Analyte from SAR

-   Repeat Monitoring Requirement

-   Monitoring Period of the SAR

-   TCR Rule (HQ, 1)

-   Repeat Sample Schedule: begin_date set to sample collection end
    date. End_date set to sample collection end date + 15 days.

    1.  Else -- do not create any repeat schedules. Proceed to next SAR

    ```{=html}
    <!-- -->
    ```
    1.  Proceed to next SAR

> The process should commit every 100 sars processed using the
> **MG_commit** action block

2)  Update the eDV Process Log row for Process Number 5, Sub Process
    Number 0 with the end_timestamp.

3)  Reuse **MG_commit** to take a commit.

####  Acute MCL Copy

At this point in the processing, the only rows in the TMNVIOL table will
be the TCR Type 21 created in the previous step. These need to be
relocated to the TCDVIOL table.

1)  Check to see if a row exists in the eDV Process Log for Process 5,
    Sub Process 2.

    a.  If none is found, create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      5

  Sub Process Number                  2

  Process Name                        TCR

  SubProcess Name                     ACUTE COPY

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723109 .anchor}**Exhibit 3-21.** Process Logic
Row -- Process 5, Subprocess 2

b.  If one is found

    i.  If the end_timestamp is valued, skip and proceed to the next sub
        process

```{=html}
<!-- -->
```
2)  For each violation in the TMNVIOL table

    a.  Create a row in the CDS_CANDIDATE_VIOLATION associate it to the
        CDS REPORT LOG.

+-----------------------------------+-----------------------------------+
| **Attribute**                     | **Data source**                   |
+-----------------------------------+-----------------------------------+
| TCDSVIOL_IS_NUMBER                | 1 up                              |
+-----------------------------------+-----------------------------------+
| TCDSVIOL_ST_CODE                  | DV                                |
+-----------------------------------+-----------------------------------+
| PWS_TINWSYS_IS_NUMBER             | The Water System associated to    |
|                                   | the violation.                    |
| PWS_TINWSYS_ST_CODE               |                                   |
|                                   |                                   |
| PWS_NUMBER                        |                                   |
|                                   |                                   |
| PWS_NAME                          |                                   |
|                                   |                                   |
| PWS_FED_PRIMARY_SOURCE_CODE       |                                   |
|                                   |                                   |
| PWS_FEDERAL_TYPE_CODE             |                                   |
|                                   |                                   |
| PWS_POPULATION_COUNT              |                                   |
|                                   |                                   |
| PWS_ACTIVITY_DATE                 |                                   |
|                                   |                                   |
| PWS_ACTIVITY_STATUS_CODE          |                                   |
+-----------------------------------+-----------------------------------+
| MP_TMNMPRD_IS_NUMBER              | The monitoring period associated  |
|                                   | to the violation.                 |
| MP_TMNMPRD_ST_CODE                |                                   |
|                                   |                                   |
| MP_MONITORING_PERIOD_DURATION     |                                   |
|                                   |                                   |
| MP_MONITORING_PERIOD_NAME         |                                   |
+-----------------------------------+-----------------------------------+
| TMNVTYPE_IS_NUMBER                | The violation type associated to  |
|                                   | the violation.                    |
| TMNVTYPE_ST_CODE                  |                                   |
|                                   |                                   |
| VTYPE_CODE                        |                                   |
|                                   |                                   |
| VTYPE_NAME                        |                                   |
|                                   |                                   |
| VTYPE_SEVERITY_LEVEL              |                                   |
|                                   |                                   |
| VTYPE_CATEGORY_CODE               |                                   |
+-----------------------------------+-----------------------------------+
| ANALYTE_TSAANLYT_IS_NUMBER        | The analyte associated to the     |
|                                   | violation.                        |
| ANALYTE_TSAANLYT_ST_CODE          |                                   |
|                                   |                                   |
| ANALYTE_CODE                      |                                   |
|                                   |                                   |
| ANALYTE_NAME                      |                                   |
+-----------------------------------+-----------------------------------+
| VIO_TO_FED_REPORTED_ANALYTE_CD    | The matching data values from the |
|                                   | violation. Note that the          |
| VIOL_D_DATA_ORIGIN_CODE           | VIO_TO_FED_REPORTED_ANALYTE_CD is |
|                                   | the ANALYTE record associated to  |
| VIOL_FED_VP_BEGIN_DATE            | the Violation Type for the        |
|                                   | Violation. For Type 21, it is     |
| VIOL_FED_VP_END_DATE              | 3100.                             |
|                                   |                                   |
| VIOL_STATE_VP_BEGIN_DATE          |                                   |
|                                   |                                   |
| VIOL_STATE_VP_END_DATE            |                                   |
+-----------------------------------+-----------------------------------+

[]{#__RefHeading___Toc173723110 .anchor}**Exhibit 3-22.** TCR Acute
violation copy from TMNVIOL to TCDSVIO

a.  Delete the TMNVIOL row.

```{=html}
<!-- -->
```
3)  At the completion of the process,

    a.  Update the eDV Process Log row for Process Number 5, Sub Process
        Number 0 with the end_timestamp

    b.  take a commit using the **MG_commit** action block.

####  TCR M&R Compliance 

1.  Check to see if a row exists in the eDV Process Log for Process 5,
    Sub Process 3.

    -   If none is found, create one.

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      5

  Sub Process Number                  3

  Process Name                        TCR

  SubProcess Name                     MNR

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723111 .anchor}**Exhibit 3-23.** Process Logic
Row -- Process 5, Subprocess 3

-   If one is found

    i.  If the end_timestamp is valued, skip and proceed to the next sub
        process

2.  Reuse action block **tcr_get_routine_sched_for_comp.**

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> Data Range populated with the DV Period Table user begin and end dates
> for the TCR rule
>
> CDS Report Log populated with

-   Report Type of 'TCR'

-   Tcr_mr_comp_ind = 'Y'

-   Tcr_non_acute_mcl_ind = 'Y'

-   IS Number

> Outputs: None

3.  At the completion of the process,

    -   Update row in the eDV Process Log with Process Number 5, Sub
        Process Number 3 with end_timestamp = current timestamp.

    -   Update row in the eDV Process Log with Process Number 5, Sub
        Process Number 0 with end_timestamp = current timestamp.

    -   Take a commit using the **MG_commit** action block.

### Process 6: Radionuclide (RAD) Rule Compliance Process

The process for the Radionuclide Rule will implement identification of
the monitoring violation for Routine and Confirmation Samples

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 6, Sub Process Number 0

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      6

  Sub Process Number                  0

  Process Name                        RAD

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723112 .anchor}**Exhibit 3-24.** Process Logic
Row -- Process 6

2.  Read for an existing CDS Report Log row with report_type = MR.

3.  If not found, reuse common action block
    **cds_create_rec_in_cds_report_log** to create the CDS Report Log.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the RAD rule
>
> CDS Report Log populated with

-   Report Type of 'MR'

-   ch_rad_rtn_schedule_check_ind = 'Y'

-   ch_ra_confirmation_sched_chck\_\_ind\_= 'Y'

> Outputs: CDS Report log IS number

4.  If it exists and the end_timestamp is valued, proceed to Process 7.

5.  The following steps are a loop performed once for each analyte
    contained within the Analyte Group with code = ERAD.

    a.  Check the eDV Process Log (TDVPRCLG) table to see where this
        process should begin. If there is no row in the eDV Process Log
        table with Process Number 6, Sub Process Name = 'RT-xxxx' where
        xxxx is the analyte code to be processed,

        i.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      6

  Sub Process Number                  Increment by +1

  Process Name                        RAD

  SubProcess Name                     RT-xxxx (where xxxx = analyte code)

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723113 .anchor}**Exhibit 3-25.** Process Logic
Row -- Process 6, Routine Subprocess

b.  If it exists and the end_timestamp is valued, proceed to Process 6,
    Confirmation Subprocess. (step f below)

c.  Reuse common action block **cds_chem_rad_rt_mnr_compl_chk**

Inputs:

> Analyte from the ERAD analyte group
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the RAD rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

a.  Update the eDV Process Log row for Process Number 6, Sub Process
    Number N with the end_timestamp.

b.  Reuse action block **MG_commit**

c.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 6, Sub Process Name = 'CO-xxxx' where xxxx is the
    analyte code to be processed ,

```{=html}
<!-- -->
```
i.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      6

  Sub Process Number                  Increment +1

  Process Name                        RAD

  SubProcess Name                     CO-xxxx (where xxxx is the analyte
                                      being processed)

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723114 .anchor}**Exhibit 3-26.** Process Logic
Row -- Process 6, Confirmation Subprocess

a.  If it exists and the end_timestamp is valued, proceed to next
    analyte within analyte group with the Routine subprocess.

b.  Reuse common action block **cds_chem_rad_co_mnr_compl_chk**

Inputs:

> Analyte from ERAD analyte group
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the RAD rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

a.  Update the eDV Process Log row for Process Number 6, Confirmation
    Sub Process with the end_timestamp.

b.  Reuse action block **MG_commit**

```{=html}
<!-- -->
```
6.  Update the eDV Process Log row for Process Number 6, Sub Process
    Number 0 with the end_timestamp.

7.  Reuse action block **MG_commit**

### Process 7: Chemical (Phase 2/5) Compliance Process

The process for the Phase 2 and 5 Chemical Rule will implement
identification of the monitoring violation for Routine Samples

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 7, Sub Process Number 0

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      7

  Sub Process Number                  0

  Process Name                        CHEM

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723115 .anchor}**Exhibit 3-27**. Process Logic
Row -- Process 7

2.  Read for an existing CDS Report Log row with report_type = MR.

3.  If not found, reuse common action block
    **cds_create_rec_in_cds_report_log** to create the CDS Report Log.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PH1 rule
>
> CDS Report Log populated with

-   Report Type of 'MR'

-   ch_rad_rtn_schedule_check_ind = 'Y'

-   ch_ra_confirmation_sched_chck_ind = 'Y'

> Outputs: CDS Report log IS number

4.  If it exists and the end_timestamp is valued, proceed to Process 8.

5.  The following steps are a loop performed once for each analyte
    contained within the Analyte Group with code = ENO.

```{=html}
<!-- -->
```
a.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 7, Sub Process Name = 'RT-Acute-xxxx' where xxxx is
    the analyte code to be processed,

    i.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      7

  Sub Process Number                  Increment by +1

  Process Name                        CHEM

  SubProcess Name                     RT-Acute-xxxx (where xxxx = analyte
                                      code)

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723116 .anchor}**Exhibit 3-28.** Process Logic
Row -- Process 7, Routine Acute Chemicals Subprocess

b.  If it exists and the end_timestamp is valued, proceed to
    confirmation schedules check for this analyte. (step f below)

c.  Reuse common action block **cds_chem_rad_rt_mnr_compl_chk**

Inputs:

> Analyte from the ENO analyte group
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PH1 rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

d.  Update the eDV Process Log row for Process Number 7, Sub Process
    Number N with the end_timestamp.

e.  Reuse action block **MG_commit**

f.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 7, Sub Process Name = 'CO-Acute-xxxx' where xxxx is
    the analyte code to be processed ,

```{=html}
<!-- -->
```
ii. Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      7

  Sub Process Number                  Increment +1

  Process Name                        CHEM

  SubProcess Name                     CO-Acute-xxxx (where xxxx is the
                                      analyte being processed)

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723117 .anchor}**Exhibit 3-29.** Process Logic
Row -- Process 7, Confirmation Chemical Acute Subprocess

g.  If it exists and the end_timestamp is valued, proceed to next
    analyte within analyte group with the Routine subprocess.

h.  Reuse common action block **cds_chem_rad_co_mnr_compl_chk**

Inputs:

> Analyte from ENO analyte group
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PH1 rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

i.  Update the eDV Process Log row for Process Number 7, Confirmation
    Acute Chemicals Sub Process with the end_timestamp.

j.  Reuse action block **MG_commit**

```{=html}
<!-- -->
```
6.  The following steps are a loop performed once for each analyte
    contained within the Analyte Group with code = EPHC.

    a.  Check the eDV Process Log (TDVPRCLG) table to see where this
        process should begin. If there is no row in the eDV Process Log
        table with Process Number 7, Sub Process Name =
        'RT-Chronic-xxxx' where xxxx is the analyte code to be processed
        ,

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      7

  Sub Process Number                  Increment +1

  Process Name                        CHEM

  SubProcess Name                     RT-Chronic-xxxx (where xxxx is the
                                      analyte being processed)

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723118 .anchor}**Exhibit 3-30.** Process Logic
Row -- Process 7, Routine Chronic Chemical Subprocess

b.  Create a TDVPRCLG

c.  If it exists and the end_timestamp is valued, proceed to the
    confirmation chronic chemicals check (step g below)

d.  Reuse common action block **cds_chem_rad_co_mnr_compl_chk**

Inputs:

> Analyte from EPHC analyte group
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PH1 rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

e.  Update the eDV Process Log row for Process Number 7, Routine Chronic
    Chemical Sub Process with the end_timestamp.

f.  Reuse action block **MG_commit**

```{=html}
<!-- -->
```
g.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 7, Sub Process Name = 'CO-Acute-xxxx' where xxxx is
    the analyte code to be processed ,

```{=html}
<!-- -->
```
iii. Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      7

  Sub Process Number                  Increment +1

  Process Name                        CHEM

  SubProcess Name                     CO-Chronic-xxxx (where xxxx is the
                                      analyte being processed)

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723119 .anchor}**Exhibit 3-31.** Process Logic
Row -- Process 7, Confirmation Chemical Chronic Subprocess

h.  If it exists and the end_timestamp is valued, proceed to next
    analyte within analyte group with the Routine subprocess.

i.  Reuse common action block **cds_chem_rad_co_mnr_compl_chk**

Inputs:

> Analyte from EPHC analyte group
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the PH1 rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

j.  Update the eDV Process Log row for Process Number 7, Confirmation
    Chronic Chemicals Sub Process with the end_timestamp.

k.  Reuse action block **MG_commit**

```{=html}
<!-- -->
```
7.  Update the eDV Process Log row for Process Number 7, Sub Process
    Number 0 with the end_timestamp.

8.  Reuse action block **MG_commit**

### Process 8: Public Notification (PN) Rule Compliance Process

The process for the Public Notification (PN) Rule will implement
identification of the violation for public notice activities that have
note resulted in the original violation returning to compliance.

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 8, Sub Process Number 0

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      8

  Sub Process Number                  0

  Process Name                        PN

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723120 .anchor}**Exhibit 3-32.** Process Logic
Row -- Process 8

2.  Reuse common action block **cds_create_rec_in_cds_report_log** to
    create the CDS Report Log.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> PN Required Date Range populated with the DV Period Table user begin
> and end dates for the PN rule (Proof of PN Required date range will
> not be used)
>
> CDS Report Log populated with

-   Report Type of 'PN'

-   Pn_compliance_report_ind = 'Y'

> Outputs: CDS Report log IS number

3.  If it exists and the end_timestamp is valued, proceed to Process 9.

```{=html}
<!-- -->
```
4.  Reuse common action block **create_cds_process_pn_viol**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> PN Required Date Range populated with the DV Period Table user begin
> and end dates for the PN rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

5.  Update the eDV Process Log row for Process Number 8, Sub Process
    Number 0 with the end_timestamp.

6.  Reuse action block **MG_commit**

### Process 9: Schedule Activity Compliance Process

The Schedule Activity Compliance Process looks for Compliance Schedule
Activities for any rule with a DV period to determine of compliance
activities have been accomplished.

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 9, Sub Process Number 0

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      9

  Sub Process Number                  0

  Process Name                        CompSched

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723121 .anchor}**Exhibit 3-33.** Process Logic
Row -- Process 9

2.  Reuse common action block **cds_create_rec_in_cds_report_log** to
    create the CDS Report Log.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Check projected date flag set to 'Y'
>
> CDS Report Log populated with

-   Report Type of 'SA'

Note: For this process, the date range will be null due to the fact we
are going to create a single log row but vary the date ranges by rule.

Also, the schedule type and compliance officer options will be blank.

> Outputs: CDS Report log IS number

3.  If it exists and the end_timestamp is valued, proceed to Process 10.

```{=html}
<!-- -->
```
4.  This process is going to loop through the TDVPRD table to find the
    DV periods provided by the user. For each TDVPRD row

    a.  Track back from the TMNRULE to get each Activity Type (via
        Violation Types

> associated to the Rule via the TMNVTRA table)

b.  For each Activity type, reuse common action block
    **create_cds_process_schedule_activity**

Inputs:

> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Current Activity Type
>
> Activity Due Date Range populated with the DV Period Table user begin
> and end dates for the current rule
>
> Check Project Date flag set to 'Y'
>
> CDS Report Log populated with

-   CDS Report IS Number

(Note: No Schedule Type or Compliance Officer will be used to run this
process)

> Outputs: None
>
> Repeat step 4.b for each Activity Type

5.  Repeat Step 4 for the next TDVPRD row

6.  Update the eDV Process Log row for Process Number 9, Sub Process
    Number 0 with the end_timestamp.

7.  Reuse action block **MG_commit**

### Process 10: Arsenic Rule Compliance Processing

The process for the Arsenic Rule will implement identification of the
monitoring violation for Routine and Confirmation Samples

1.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 10, Sub Process Number 0

    a.  Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      10

  Sub Process Number                  0

  Process Name                        ARSENIC

  SubProcess Name                     Initial Start

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723122 .anchor}**Exhibit 3-34.** Process Logic
Row -- Process 10

2.  Read for an existing CDS Report Log row with report_type = MR.

3.  If not found, reuse common action block
    **cds_create_rec_in_cds_report_log** to create the CDS Report Log.

> Inputs:
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the Arsenic rule
>
> CDS Report Log populated with

-   Report Type of 'MR'

-   ch_rad_rtn_schedule_check_ind = 'Y'

-   ch_rad_confirmation_sched_chck_ind = 'Y'

> Outputs: CDS Report log IS number

4.  If it exists and the end_timestamp is valued, proceed to Process 11.

5.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 10, Sub Process Number 1 ,

```{=html}
<!-- -->
```
a.  Create a TDVPRCLOG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      10

  Sub Process Number                  1

  Process Name                        ARSENIC

  SubProcess Name                     RT

  Init_Timestamp                      Current timestamp

  End_Timestamp                       null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723123 .anchor}**Exhibit 3-35.** Process Logic
Row -- Process 10, Routine Subprocess

b.  If it exists and the end_timestamp is valued, proceed to
    confirmation schedules check for this analyte. (step f below)

c.  Reuse common action block **cds_chem_rad_rt_mnr_compl_chk**

Inputs:

> Analyte 1005
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the Arsenic rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

d.  Update the eDV Process Log row for Process Number 10, Sub Process
    Number 1 with the end_timestamp.

e.  Reuse action block **MG_commit**

f.  Check the eDV Process Log (TDVPRCLG) table to see where this process
    should begin. If there is no row in the eDV Process Log table with
    Process Number 10, Sub Process Number 2 ,

```{=html}
<!-- -->
```
iv. Create a TDVPRCLG

  ----------------------------------- -----------------------------------
  **Column name**                     **Value**

  Process Number                      10

  Sub Process Number                  2

  Process Name                        ARSENIC

  SubProcess Name                     CO

  Init_Timestamp                      Current timestamp

  End_Timestamp                       Null
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173723124 .anchor}**Exhibit 3-36.** Process Logic
Row -- Process 10, Confirmation Subprocess

g.  If it exists and the end_timestamp is valued, proceed to Process 11.

h.  Reuse common action block **cds_chem_rad_co_mnr_compl_chk**

Inputs:

> Analyte 1005
>
> Legal entity populated with the Primacy Agency
>
> System Variable
>
> Applicable Period Date Range populated with the DV Period Table user
> begin and end dates for the Arsenic rule
>
> CDS Report Log populated with

-   CDS Report IS Number

> Outputs: None

i.  Update the eDV Process Log row for Process Number 10, Sub Process
    Number 2 with the end_timestamp.

j.  Reuse action block **MG_commit**

### Process 11: Reload of the Primacy Agency Violations

Using the primacy agency extraction of violations from the SDWIS/STATE
database, populate the following tables:

TMNVIOL

TMNVGRP

TMNVCRAT

TMNORVIA

### Process 12: DV Discrepancy Identification

The calculating of discrepancies, compliance decisions, and other
results is the function of comparing the violations from the three data
sources and noting the matches and mismatches among them. The options
are:

-   Violation exists in all three data sources -- No discrepancy (ND)

-   Violation exists in only the eDV data source -- Compliance Decision
    1 (CD1)

-   Violation exists in the eDV data source and the Federal data source
    -- Compliance Decision 2 (CD2)

-   Violation exists in the eDV and Primacy Agency data source -- Data
    Flow (DF)

-   Violation exists only in the Federal data source -- Over Reporting 1
    (OR1)

-   Violation exists in the Federal data source and the Primacy Agency
    data source -- Over Reporting 2 (OR2)

#### Identifying qualifying violations in the eDV Datasource

The DV Data source is represented by violations in the TCDSVIOL table.

All PN Violations will be converted from type 76 to type 75 violations
due to the absence of the originating violation associations which are
required for the PN Compliance process to generate the type 75. Prior to
the identification of the qualifying violations, the following update
will be performed against the TCDSVIOL table:

> update tcdsviol
>
> set vtype_code = \'75\', tmnvtype_is_number = 56\
> where vtype_code = \'76\' 

For Each DV Period (TDVDVPRD) with begin date not null

Loop through each TMNVTRA matched on TMNRULE_IS_NUMBER and
TMNRULE_ST_CODE and where ods_rpt_ind = 'Y'

Loop through each TCDSVIOL where

-   TCDSVIOL.tmnvtype_is_number and TCDSCIOL.tmnvtype_st_code match the
    TDVVTARA

-   TCDSVIOL. viol_fed_vp_end_date between (inclusive)
    TDVDPRD.user_begin_date and TDVDVPRD.user_end_date

-   If TCDSVIOL.viol_fed_vp_end_date is null TCDSVIOL.
    viol_fed_vp_begin_date between (inclusive) TDVDPRD.user_begin_date
    and TDVDVPRD.user_end_date

-   If TMNVTRA has associated TDVVTARA check each for matching analytes

    -   TCDSVIOL.vio_to_fed_anl_cd (by getting the tsaanlyt IS number
        and state code via a join between TCDSVIOL.vio_to_fed_anl_cd and
        TSAANLYT.code)

    -   OR, when tcdsviol.vio to fed anl cd is not valued,

    -   TCDSVIOL.anl_tsaanlyt_is_no and TCDSVIOL.anl_tsaanlyt_st_cd
        match the TDVVTARA

[]{.mark}

Insert row into TDVRESULT table populating all columns except

-   dv_xx_result_code

-   tmnviol_is_number

-   tmnviol_st_code

-   Tmnviol_external_system_number

-   Any column beginning with ods\_

####  Determining Compliance Determination and Data Flow Discrepancies

Compliance Determination (CD) discrepancies occur when a reportable
violation is determined by the eDV and is not matched in the primacy
agency's data source. The violation involved in this discrepancy may or
may not also be found in Federal data source.

For each row in the TDVRESLT table

Read the TMNVIOL table matching data columns between TDVRESLT and
TMNVIOL as prescribed in Exhibit 3-37

When a match is found

Update the TDVRESLT with the TMNVIOL_IS_NUMBER and TMNVIOL_ST_CODE

+--------+--------------+--------------------+------------------------+
|        | Field Name   | Field Name         | Notes                  |
|        |              |                    |                        |
|        | TDVRESLT     | TMNVIOL            |                        |
+--------+--------------+--------------------+------------------------+
| PWS ID | pws_t        | tinwsys            |                        |
|        | inwsys_is_no | .tinwsys_is_number |                        |
+--------+--------------+--------------------+------------------------+
| WSF ID | wsf_         | tinws              | If the TMNVIOL record  |
|        | tinwsf_is_no | f.tinwsf_is_number | is null then this      |
|        |              |                    | field is not used to   |
|        |              |                    | match violations.      |
|        |              |                    |                        |
|        |              |                    | If                     |
|        |              |                    | t                      |
|        |              |                    | inwsf.tinwsf_is_number |
|        |              |                    | is null and            |
|        |              |                    | wsf_tinwsf_is_no is    |
|        |              |                    | not; identify other    |
|        |              |                    | TDVRESLT records that  |
|        |              |                    | match using the same   |
|        |              |                    | criteria as given in   |
|        |              |                    | this Exhibit except    |
|        |              |                    | WSF. If there is more  |
|        |              |                    | than one, update       |
|        |              |                    | tmnviol_is_number and  |
|        |              |                    | tmnviol_st_code for    |
|        |              |                    | all using the same     |
|        |              |                    | ISN. See example that  |
|        |              |                    | follows the exhibit.   |
+--------+--------------+--------------------+------------------------+
| Repo   | vio_t        | tsaanlyt.code      | Attempt to retrieve    |
| rtable | o_fed_anl_cd | (joined from the   | the TSAANLYT.code by   |
| A      |              | TMNVIOL to the     | joining the TMNVIOL    |
| nalyte |              | TSAANLYT table via | and TMNVTYP on the     |
| Code   |              | one of two         | tmnvtyp_is_number and  |
|        |              | possible routes)   | tmnvtyp_st_code.       |
|        |              |                    |                        |
|        |              |                    | If the                 |
|        |              |                    | tsaanlyt_is_number is  |
|        |              |                    | not null on the joined |
|        |              |                    | TMNVTYP table, use     |
|        |              |                    | TMNV                   |
|        |              |                    | TYP.tsaanlyt_is_number |
|        |              |                    | and                    |
|        |              |                    | T                      |
|        |              |                    | MNVTYP.tsaanly_st_code |
|        |              |                    | to join to the         |
|        |              |                    | TSAANLYT table to      |
|        |              |                    | retrieve the code      |
|        |              |                    | value.                 |
|        |              |                    |                        |
|        |              |                    | If the                 |
|        |              |                    | tsaanlyt_is_number is  |
|        |              |                    | null, then check the   |
|        |              |                    | TMNVI                  |
|        |              |                    | OL.tsaanlyt_is_number. |
|        |              |                    | If it is not null,     |
|        |              |                    | join to the TSAANLYT   |
|        |              |                    | table to retrieve the  |
|        |              |                    | code value.            |
|        |              |                    |                        |
|        |              |                    | If the                 |
|        |              |                    | TMNV                   |
|        |              |                    | IOL.tsaanlyt_is_number |
|        |              |                    | is null, then the      |
|        |              |                    | analyte is optional    |
|        |              |                    | and no match is made.  |
|        |              |                    |                        |
|        |              |                    | For the analyte codes  |
|        |              |                    | from either the        |
|        |              |                    | TDVRESLT and the       |
|        |              |                    | TMNVIOL tables, use    |
|        |              |                    | the Substitute         |
|        |              |                    | Contaminant Code from  |
|        |              |                    | the table below in     |
|        |              |                    | place of an analyte    |
|        |              |                    | code in the first      |
|        |              |                    | column.                |
|        |              |                    |                        |
|        |              |                    | +--------+---------+   |
|        |              |                    | | **If   | **Sub   |   |
|        |              |                    | | Anal   | stitute |   |
|        |              |                    | | yte/** | this**  |   |
|        |              |                    | |        |         |   |
|        |              |                    | | **C    | *       |   |
|        |              |                    | | ontami | *Contam |   |
|        |              |                    | | nant** | inant** |   |
|        |              |                    | |        |         |   |
|        |              |                    | | **Code | *       |   |
|        |              |                    | | is:**  | *Code** |   |
|        |              |                    | +========+=========+   |
|        |              |                    | | 1040   | 1038    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 4020   | 4010    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 4030   | 4010    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 4002   | 4000    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 4109   | 4000    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2455   | 2456    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2454   | 2456    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2453   | 2456    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2452   | 2456    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2451   | 2456    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2941   | 2950    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2942   | 2950    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2943   | 2950    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2944   | 2950    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2949   | 2950    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2384   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2388   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2390   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2392   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2394   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2396   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2398   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2399   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2400   | 2383    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2962   | 2955    |   |
|        |              |                    | +--------+---------+   |
|        |              |                    | | 2963   | 2955    |   |
|        |              |                    | +--------+---------+   |
+--------+--------------+--------------------+------------------------+
| Vio    | Vtype_code   | tmnvtype.type_code | See example in Exhibit |
| lation |              |                    | 3-38.                  |
| Type   |              |                    |                        |
+--------+--------------+--------------------+------------------------+
|        |              |                    |                        |
+--------+--------------+--------------------+------------------------+
| Fed VP | viol_f       | fed_prd_begin_dt   |                        |
| Begin  | ed_vp_beg_dt |                    |                        |
| Date   |              |                    |                        |
+--------+--------------+--------------------+------------------------+
| Status | n/a          | Status_type_code   |                        |
| Code   |              | must be 'V'        |                        |
+--------+--------------+--------------------+------------------------+

[]{#__RefHeading___Toc173723125 .anchor}**Exhibit 3-37.** Column
Matching Criteria for TDVRESLT and TMNVIOL

Following is an example of when multiple matching records in TDVRESLT
should be marked in the TDVRESLT.dv_nd_result_code. Assume the following
violations are in the TDVRESLT table and in the TMNVIOL and related
tables:

+---------+---------+-----+---------+--------+-------+-----+---------+
| **      | **PWS   | **  | **Repor | *      | **Fed | **  | **A     |
| Table** | ID**    | WSF | table** | *Viola | VP**  | Sta | ction** |
|         |         | I   |         | tion** |       | tus |         |
|         |         | D** | **An    |        | **    | Cod |         |
|         |         |     | alyte** | **Type | Begin | e** |         |
|         |         |     |         | &**    | D     |     |         |
|         |         |     | *       |        | ate** |     |         |
|         |         |     | *Code** | **Seve |       |     |         |
|         |         |     |         | rity** |       |     |         |
+---------+---------+-----+---------+--------+-------+-----+---------+
| T       | OK      | **1 | 1038    | 03MJ   | 1/1   |     | Update  |
| DVRESLT | 1020801 | 136 |         |        | /2003 |     | ISN and |
|         |         | 3** |         |        |       |     | SC      |
|         |         |     |         |        |       |     | using   |
|         |         |     |         |        |       |     | values  |
|         |         |     |         |        |       |     | from    |
|         |         |     |         |        |       |     | TMNVIOL |
|         |         |     |         |        |       |     | record  |
+---------+---------+-----+---------+--------+-------+-----+---------+
|         | OK      | **1 | 1038    | 03MJ   | 1/1   |     |         |
|         | 1020801 | 137 |         |        | /2003 |     |         |
|         |         | 0** |         |        |       |     |         |
+---------+---------+-----+---------+--------+-------+-----+---------+
|         | OK      | **1 | *       | 03MJ   | 1/1   |     |         |
|         | 1020801 | 137 | *1040** |        | /2003 |     |         |
|         |         | 7** | ^1^     |        |       |     |         |
+---------+---------+-----+---------+--------+-------+-----+---------+
|         | OK      | **1 | 1038    | 03MJ   | 1/1   |     |         |
|         | 1020801 | 137 |         |        | /2003 |     |         |
|         |         | 9** |         |        |       |     |         |
+---------+---------+-----+---------+--------+-------+-----+---------+
| TMNVIOL | OK      |     | 1038    | 03MJ   | 1/1   | V   |         |
|         | 1020801 |     |         | or MN  | /2003 |     |         |
|         |         |     |         | ^2^    |       |     |         |
+---------+---------+-----+---------+--------+-------+-----+---------+

[]{#__RefHeading___Toc173723126 .anchor}**Exhibit 3-38.** Analyte and
Violation Type Code Matching Example

> ^1^ This example also includes an example of using substitute analyte
> codes, in this case 1038 for 1040.
>
> ^2^ A primacy agency can elect to report M/R violations by WSF or by
> PWS. In the latter case, if the PWS failed to sample some but not all
> the WSF, then it should be reported with a minor severity.

Next, the TDVRESLT is compared to violations in the Federal data source
(ODSVIO).

Read the ODSVIOL table matching data columns between TDVRESLT and
ODSVIOL as prescribed in Exhibit 3-39.

+-------+--------------+--------------+-------------------------------+
|       | **Field      | **Field      | **Notes**                     |
|       | Name**       | Name**       |                               |
|       |              |              |                               |
|       | **TDVRESLT** | **ODSVIOL**  |                               |
+-------+--------------+--------------+-------------------------------+
| PWS   | pws_number   | pwsid        |                               |
| ID    |              |              |                               |
+-------+--------------+--------------+-------------------------------+
| WSF   | wsf_e        | facid        | If facid is null or spaces,   |
| ID    | xtern_sys_no |              | then this field is not used   |
|       |              |              | to match violations.          |
|       |              |              |                               |
|       |              |              | If facid is null and          |
|       |              |              | tdvreslt.wsf_extern_sys_no is |
|       |              |              | not; identify other TDVRESLT  |
|       |              |              | records that match using the  |
|       |              |              | same criteria as given in     |
|       |              |              | this Exhibit except WSF. If   |
|       |              |              | there is more than one, mark  |
|       |              |              | all as indicated above.       |
+-------+--------------+--------------+-------------------------------+
| Repor | Tables       |              | For both tables, use the      |
| table | TMNVTRA,     |              | Substitute Contaminant Code   |
| An    | TDVVTARA and |              | from the table in place of an |
| alyte | TSAANLYT are |              | analyte code in the first     |
| Code  | used to      |              | column.                       |
|       | match        |              |                               |
|       | Violation    |              | +-----------+-------------+   |
|       | Type and     |              | | If        | Substitute  |   |
|       | Analyte      |              | | Analyte/  | this        |   |
|       | /Contaminant |              | |           |             |   |
|       | as follows:  |              | | Co        | Contaminant |   |
|       |              |              | | ntaminant |             |   |
|       | TD           |              | |           | Code        |   |
|       | VRESLT.vio_t |              | | Code is:  |             |   |
|       | o_fed_anl_cd |              | +===========+=============+   |
|       | is equal to  |              | | 1040      | 1038        |   |
|       | TDVVTARA.ts  |              | +-----------+-------------+   |
|       | aanlyt_code, |              | | 4020      | 4010        |   |
|       | is equal to  |              | +-----------+-------------+   |
|       | vio          |              | | 4030      | 4010        |   |
|       | lation.ccode |              | +-----------+-------------+   |
|       | (supplying   |              | | 4002      | 4000        |   |
|       | substitute   |              | +-----------+-------------+   |
|       | contaminant  |              | | 4109      | 4000        |   |
|       | code for     |              | +-----------+-------------+   |
|       | both as      |              | | 2455      | 2456        |   |
|       | indicated in |              | +-----------+-------------+   |
|       | the          |              | | 2454      | 2456        |   |
|       | substitute   |              | +-----------+-------------+   |
|       | contaminant  |              | | 2453      | 2456        |   |
|       | code table)  |              | +-----------+-------------+   |
|       | if TDVVTARA  |              | | 2452      | 2456        |   |
|       | exists that  |              | +-----------+-------------+   |
|       | is a child   |              | | 2451      | 2456        |   |
|       | of the       |              | +-----------+-------------+   |
|       | TMNVTRA      |              | | 2941      | 2950        |   |
|       |              |              | +-----------+-------------+   |
|       | where        |              | | 2942      | 2950        |   |
|       | tdv          |              | +-----------+-------------+   |
|       | reslt.tmnvty |              | | 2943      | 2950        |   |
|       | pe_is_number |              | +-----------+-------------+   |
|       | is equal to  |              | | 2944      | 2950        |   |
|       | tm           |              | +-----------+-------------+   |
|       | nvtra.tmnvty |              | | 2949      | 2950        |   |
|       | pe_is_number |              | +-----------+-------------+   |
|       | and          |              | | 2384      | 2383        |   |
|       | t            |              | +-----------+-------------+   |
|       | dvreslt.tmnv |              | | 2388      | 2383        |   |
|       | type_st_code |              | +-----------+-------------+   |
|       | is equal to  |              | | 2390      | 2383        |   |
|       | tmnvtra.tmnv |              | +-----------+-------------+   |
|       | type_st_code |              | | 2392      | 2383        |   |
|       | and          |              | +-----------+-------------+   |
|       | tmnvt        |              | | 2394      | 2383        |   |
|       | ra.ods_vtype |              | +-----------+-------------+   |
|       | is equal to  |              | | 2396      | 2383        |   |
|       | vio          |              | +-----------+-------------+   |
|       | lation.vcode |              | | 2398      | 2383        |   |
|       |              |              | +-----------+-------------+   |
|       | where        |              | | 2399      | 2383        |   |
|       | tmnvtra      |              | +-----------+-------------+   |
|       | always       |              | | 2400      | 2383        |   |
|       | associates   |              | +-----------+-------------+   |
|       | to a         |              | | 2962      | 2955        |   |
|       | violation    |              | +-----------+-------------+   |
|       | type and     |              | | 2963      | 2955        |   |
|       | rule         |              | +-----------+-------------+   |
+-------+--------------+--------------+-------------------------------+
| Viol  |              |              |                               |
| ation |              |              |                               |
| Type  |              |              |                               |
+-------+--------------+--------------+-------------------------------+
| Sev   |              |              |                               |
| erity |              |              |                               |
| Level |              |              |                               |
+-------+--------------+--------------+-------------------------------+
| Fed   | viol_f       | compl        |                               |
| VP    | ed_vp_beg_dt | perbegindate |                               |
| Begin |              |              |                               |
| Date  |              |              |                               |
+-------+--------------+--------------+-------------------------------+

**Exhibit 3-39.** Column Matching Criteria for TDVRESLT and ODSVIOL

> When a match is found

Update the TDVRESLT with the ODS_VIOID, and all other columns prefixed
with ods\_ per Exhibit 3-39

If the tmnviol_is_number is not null

Update the dv_nd_result_code to 'Y'

Else

Update the dv_cd_result_code to 2

When no match is found

If the tmnviol_is_number is not null

Update the dv_df_result_code to 'Y'

Else

Update the dv_cd_result_code to 1

####  Determining Over Reporting Discrepancies

Over Reporting Discrepancies occur when a violation is found in the
Federal data source but is not determined by the eDV. The violation
involved in this discrepancy may or may not also be found in the Primacy
Agency's data source.

The Federal Data source is represented by violations in the ODSVIOL
table.

Following is the criteria selection violations within the DV period from
the Federal data source. The challenge is to uniquely associate a
violation type and analyte combination defined using SDWIS/STATE
methodology with a violation in SDWIS/ODS that is defined using federal
extraction methodology. For each record in the Violation Type Rule
Assignment (TMNVTRA) and Violation Type Analyte Rule Assignment table
(TMNVTARA), one unique "violation type contaminant combination" from the
Federal data source must be identified.

The fields in a ODSVIOL record that uniquely identify a "violation type
contaminant combination" are CCODE (contaminant code, which is
equivalent to Federal Reportable Code in TMNVIOL), VCODE (violation type
code), and MAJORVIOLIND.[^1] It must be noted that MAJORVIOLIND is null
for many violations and these must not be excluded.

1)  For Each DV Period (TDVDVPRD) with begin date not null

Loop through each TMNVTRA/TDVVTARA (outer join due to optionality of
analytes in tdvvtara) match on the TMNRULE_IS_NUMBER and TMNRULE_ST_CODE
and where ods_rpt_ind = 'Y'

2)  If the violation end date is not allowed to be reported to EPA
    (TMNVPRDR.end_dt_null_ind = \"Y\" where TMNVPRDR always defines
    rules for the same TMNVTRA.tmnvtype_is_number and
    TMNVTRA.tmnvtype_st_code AND, TDVVTARA.tsaanlyt_is_number and
    TDVVTARA.tsaanlyt_st_code)

    a.  Retrieve each ODSVIOL with Compliance Period Begin Date between
        User Begin Date and User End Date in the TDVDVPRD for the rule

> (ODSVIOL.complperbegindate \>= TDVDVPRD.user_begin_dt and
> ODSVIOL.complperbegindate \<= TDVDVPRD.user_end_dt and
>
> TMNVTRA.ods_violation_type = ODSVIOL.vcode and
>
> (the TDVVTARA.tsaanlyt_code= the ODSVIOL.ccode, when tdvvtara is
> present)

3)  If the violation end date is required to be reported to EPA
    (TMNVPRDR.end_dt_null_ind = \"Y\" where TMNVPRDR always defines
    rules for the same TMNVTRA.tmnvtype_is_number and
    TMNVTRA.tmnvtype_st_code AND, TDVVTARA.tsaanlyt_is_number and
    TDVVTARA.tsaanlyt_st_code)then select violations where:

    a.  Retrieve each ODSVIOL with Compliance Period End Date between
        User Begin Date and User End Date in the TDVDVPRD for the rule

> (ODSVIOL.complperenddate \>= TDVDVPRD.user_begin_dt and
> ODSVIOL.complperenddate \<= TDVDVPRD.user_end_dt and
>
> TMNVTRA.ods_violation_type = ODSVIOL.vcode and
>
> (TDVVTARA.tsaanlyt_code = the ODSVIOL.ccode)
>
> For each ODSVIOL record returned, Read the TDVRESLT table to see if
> the ODSVIOL.vioid exists in the TDVRESLT.ods_vioid field. If it does,
> skip to next ODSVIOL record. If not, an over reporting occurrence is
> found. Proceed with determining if it is a level 1 or level 2 my
> attempting a match between the federal data source and the primacy
> agency data source using the criteria defined in Exhibit 3-40.

+-------+-------------------------+-------------------+--------------+
|       | Field Name              | Field Name        | Notes        |
|       |                         |                   |              |
|       | TMNVIOL                 | ODSVIOL           |              |
+-------+-------------------------+-------------------+--------------+
| PWS   | tinwsys.number0         | pwsid             |              |
| ID    |                         |                   |              |
+-------+-------------------------+-------------------+--------------+
| WSF   | tinwsf.external_sys_num | facid             | If the       |
| ID    |                         |                   | violation in |
|       |                         |                   | ODSVIOL does |
|       |                         |                   | not include  |
|       |                         |                   | a facid,     |
|       |                         |                   | then exclude |
|       |                         |                   | this field   |
|       |                         |                   | from the     |
|       |                         |                   | match.       |
+-------+-------------------------+-------------------+--------------+
| Repor | Table TDVVTARA and      |                   |              |
| table | TSAANLYT are used to    |                   |              |
| An    | match Violation Type    |                   |              |
| alyte | and Analyte/Contaminant |                   |              |
| Code  | as follows:             |                   |              |
|       |                         |                   |              |
|       | If tsaanlyt.code is     |                   |              |
|       | equal to                |                   |              |
|       | violation.ccode         |                   |              |
|       | (Analyte referenced by  |                   |              |
|       | TMNVTYPE for the        |                   |              |
|       | Violation unless one is |                   |              |
|       | not referenced, in      |                   |              |
|       | which case, the Analyte |                   |              |
|       | referenced by TMNVIOL)  |                   |              |
|       |                         |                   |              |
|       | and TMNVTRA.ods_vtype   |                   |              |
|       | is equal to             |                   |              |
|       | violation.vcode         |                   |              |
|       |                         |                   |              |
|       | and                     |                   |              |
|       | TMNVTRA.majorviolind    |                   |              |
|       | (if valued) is equal to |                   |              |
|       | ODSVIOL.majorviolind    |                   |              |
|       |                         |                   |              |
|       | and                     |                   |              |
|       | TMN                     |                   |              |
|       | VIOL.tmnvtype_is_number |                   |              |
|       | is equal to             |                   |              |
|       | TMN                     |                   |              |
|       | VTRA.tmnvtype_is_number |                   |              |
|       |                         |                   |              |
|       | and                     |                   |              |
|       | T                       |                   |              |
|       | MNVIOL.tmnvtype_st_code |                   |              |
|       | is equal to             |                   |              |
|       | T                       |                   |              |
|       | MNVTRA.tmnvtype_st_code |                   |              |
|       |                         |                   |              |
|       | where TDVVTARA always   |                   |              |
|       | associates to a         |                   |              |
|       | violation type and rule |                   |              |
|       | an analyte              |                   |              |
+-------+-------------------------+-------------------+--------------+
| Viol  |                         |                   |              |
| ation |                         |                   |              |
| Type  |                         |                   |              |
+-------+-------------------------+-------------------+--------------+
| Sev   |                         |                   |              |
| erity |                         |                   |              |
| Level |                         |                   |              |
+-------+-------------------------+-------------------+--------------+
| Fed   | fed_prd_begin_dt        | complperbegindate |              |
| VP    |                         |                   |              |
| Begin |                         |                   |              |
| Date  |                         |                   |              |
+-------+-------------------------+-------------------+--------------+
| S     | Status_type_cd = 'V'    |                   |              |
| tatus |                         |                   |              |
| code  |                         |                   |              |
+-------+-------------------------+-------------------+--------------+

[]{#__RefHeading___Toc173723127 .anchor}**Exhibit 3-40.** Column
Matching Criteria for TMNVIOL and ODSVIOL

When a match is found

-   Insert into the TDVRESLT with data being populated from the
    ODSVIOL(as specified in Exhibit 3-41) and the tmnviol_is_number,
    tmnviol_st_code, and tmnviol_external_system_number from the
    matching TMNVIOL record.

-   The tcdsviol_is_number should be null

-   The dv_ov_result_code to 2

When no match is found

-   Insert into the TDVRESLT with data being populated from the ODSVIOL
    as specified in Exhibit 3-41.

-   The tmnviol_is_number, tmnviol_st_code, and
    tmnviol_external_system_number should be null.

-   The tcdsviol_is_number should be null

-   The dv_or_result_code to 1

+----------------+------------------------+---------------------------+
| **Field Name** | **Field Name**         | **Notes**                 |
|                |                        |                           |
| **O            | **TDVRESLT**           |                           |
| DS_VIOLATION** |                        |                           |
+----------------+------------------------+---------------------------+
| pwsid          | pws_number             | If ISN and St Code        |
|                |                        | useful, we could obtain   |
|                |                        | from TINWSYS with match   |
|                |                        | from pwsid to number0     |
+----------------+------------------------+---------------------------+
| vioid          | ods_vioid              |                           |
+----------------+------------------------+---------------------------+
| dataorigincode | Ods_d_data_origin_code |                           |
+----------------+------------------------+---------------------------+
| facid          | Wsf_                   | If ISN and St Code        |
|                | external_system_number | useful, we could obtain   |
|                |                        | from TINWSF with match    |
|                |                        | from facid to             |
|                |                        | external_sys_num          |
+----------------+------------------------+---------------------------+
| ccode          | Vio_to_f               |                           |
|                | ed_reported_analyte_cd |                           |
+----------------+------------------------+---------------------------+
| vcode          | vtype_code             |                           |
+----------------+------------------------+---------------------------+
| violmeasure    | vio                    |                           |
|                | l_analysis_result_text |                           |
+----------------+------------------------+---------------------------+
| statemcl       | viol_mcl_violated_text |                           |
+----------------+------------------------+---------------------------+
| majorviolind   | Ods_majorviolind       |                           |
+----------------+------------------------+---------------------------+
| com            | viol_fed_vp_begin_date |                           |
| plperbegindate |                        |                           |
+----------------+------------------------+---------------------------+
| c              | viol_fed_vp_end_date   |                           |
| omplperenddate |                        |                           |
+----------------+------------------------+---------------------------+
| val            | n/a                    |                           |
| idityindicator |                        |                           |
+----------------+------------------------+---------------------------+
| severity       | Ods\_                  |                           |
| indicatorcount | severityindicatorcount |                           |
+----------------+------------------------+---------------------------+
| fir            | Ods\_                  |                           |
| streporteddate | firstreporteddate      |                           |
+----------------+------------------------+---------------------------+
| la             | Ods\_ lastreporteddate |                           |
| streporteddate |                        |                           |
+----------------+------------------------+---------------------------+

[]{#__RefHeading___Toc173723128 .anchor}**Exhibit 3-41.** Column
Matching Criteria for TDVRESLT and ODSVIOL - Overreporting

#### Final match to Primacy Agency data source

In the above matches to the primacy agency data source, the violation
status was required to be validated (V) in order to match. In this final
step, another match will be done on the CD1, CD2, and OR1 DV results to
see if the primacy agency identified a violation, but made a decision to
dispose of it via another way.

For each TDVRESLT with a dv_cd_result_code not null or a
dv_or_result_code = 1,

Read the TMNVIOL table matching data columns between TDVRESLT and
TMNVIOL as prescribed in Exhibit 3-42

When a matches are found (could be more than one)

Update the TDVRESLT.comments field by concatenating "State Violation
found with a status of \[status value where P = preliminary, D =
deleted, R= rejected\] " to the beginning of the TMNVIOL.comments.

+--------+--------------+--------------------+-------------------------+
|        | Field Name   | Field Name         | Notes                   |
|        |              |                    |                         |
|        | TDVRESLT     | TMNVIOL            |                         |
+--------+--------------+--------------------+-------------------------+
| PWS ID | pws_t        | tinwsys            |                         |
|        | inwsys_is_no | .tinwsys_is_number |                         |
+--------+--------------+--------------------+-------------------------+
| WSF ID | wsf_         | tinws              | If the TMNVIOL record   |
|        | tinwsf_is_no | f.tinwsf_is_number | is null then this field |
|        |              |                    | is not used to match    |
|        |              |                    | violations.             |
|        |              |                    |                         |
|        |              |                    | If                      |
|        |              |                    | tinwsf.tinwsf_is_number |
|        |              |                    | is null and             |
|        |              |                    | wsf_tinwsf_is_no is     |
|        |              |                    | not; identify other     |
|        |              |                    | TDVRESLT records that   |
|        |              |                    | match using the same    |
|        |              |                    | criteria as given in    |
|        |              |                    | this Exhibit except     |
|        |              |                    | WSF. If there is more   |
|        |              |                    | than one, update        |
|        |              |                    | tmnviol_is_number and   |
|        |              |                    | tmnviol_st_code for all |
|        |              |                    | using the same ISN. See |
|        |              |                    | example that follows    |
|        |              |                    | the exhibit.            |
+--------+--------------+--------------------+-------------------------+
| Repo   | vio_t        | tsaanlyt.code      | Attempt to retrieve the |
| rtable | o_fed_anl_cd | (joined from the   | TSAANLYT.code by        |
| A      |              | TMNVIOL to the     | joining the TMNVIOL and |
| nalyte |              | TSAANLYT table via | TMNVTYP on the          |
| Code   |              | one of two         | tmnvtyp_is_number and   |
|        |              | possible routes)   | tmnvtyp_st_code.        |
|        |              |                    |                         |
|        |              |                    | If the                  |
|        |              |                    | tsaanlyt_is_number is   |
|        |              |                    | not null on the joined  |
|        |              |                    | TMNVTYP table, use      |
|        |              |                    | TMN                     |
|        |              |                    | VTYP.tsaanlyt_is_number |
|        |              |                    | and                     |
|        |              |                    | TMNVTYP.tsaanly_st_code |
|        |              |                    | to join to the TSAANLYT |
|        |              |                    | table to retrieve the   |
|        |              |                    | code value.             |
|        |              |                    |                         |
|        |              |                    | If the                  |
|        |              |                    | tsaanlyt_is_number is   |
|        |              |                    | null, then check the    |
|        |              |                    | TMNV                    |
|        |              |                    | IOL.tsaanlyt_is_number. |
|        |              |                    | If it is not null, join |
|        |              |                    | to the TSAANLYT table   |
|        |              |                    | to retrieve the code    |
|        |              |                    | value.                  |
|        |              |                    |                         |
|        |              |                    | If the                  |
|        |              |                    | TMN                     |
|        |              |                    | VIOL.tsaanlyt_is_number |
|        |              |                    | is null, then the       |
|        |              |                    | analyte is optional and |
|        |              |                    | no match is made.       |
|        |              |                    |                         |
|        |              |                    | For the analyte codes   |
|        |              |                    | from either the         |
|        |              |                    | TDVRESLT and the        |
|        |              |                    | TMNVIOL tables, use the |
|        |              |                    | Substitute Contaminant  |
|        |              |                    | Code from the table     |
|        |              |                    | below in place of an    |
|        |              |                    | analyte code in the     |
|        |              |                    | first column.           |
|        |              |                    |                         |
|        |              |                    | +---------+---------+   |
|        |              |                    | | **If    | **Sub   |   |
|        |              |                    | | Ana     | stitute |   |
|        |              |                    | | lyte/** | this**  |   |
|        |              |                    | |         |         |   |
|        |              |                    | | *       | *       |   |
|        |              |                    | | *Contam | *Contam |   |
|        |              |                    | | inant** | inant** |   |
|        |              |                    | |         |         |   |
|        |              |                    | | **Code  | *       |   |
|        |              |                    | | is:**   | *Code** |   |
|        |              |                    | +=========+=========+   |
|        |              |                    | | 1040    | 1038    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 4020    | 4010    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 4030    | 4010    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 4002    | 4000    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 4109    | 4000    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2455    | 2456    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2454    | 2456    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2453    | 2456    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2452    | 2456    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2451    | 2456    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2941    | 2950    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2942    | 2950    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2943    | 2950    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2944    | 2950    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2949    | 2950    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2384    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2388    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2390    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2392    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2394    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2396    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2398    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2399    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2400    | 2383    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2962    | 2955    |   |
|        |              |                    | +---------+---------+   |
|        |              |                    | | 2963    | 2955    |   |
|        |              |                    | +---------+---------+   |
+--------+--------------+--------------------+-------------------------+
| Vio    | Vtype_code   | tmnvtype.type_code | See example in Exhibit  |
| lation |              |                    | 3-38.                   |
| Type   |              |                    |                         |
+--------+--------------+--------------------+-------------------------+
| Fed VP | viol_f       | fed_prd_begin_dt   |                         |
| Begin  | ed_vp_beg_dt |                    |                         |
| Date   |              |                    |                         |
+--------+--------------+--------------------+-------------------------+
| Status | n/a          | Status_type_code   |                         |
| Code   |              | may be any value   |                         |
|        |              | other than 'V'     |                         |
+--------+--------------+--------------------+-------------------------+

[]{#__RefHeading___Toc173723129 .anchor}**Exhibit 3-42.** Column
Matching Criteria for TDVRESLT and TMNVIOL -- non Validated Status

####  Wrap Up

Finally, a conversion must be done on a few of the code values that
differ between SDWIS/STATE and DV terminologies.

1.  Update TDVRESLT. pws_federal_type_code as follows:

> When 'C' set to 'CWS'
>
> When 'NC' set to 'TNC'

2.  Update the TDVRESLT.pws_cnty_svd_nm with the
    TINWSYS.D_PRIN_CNTY_SVD_NM joined on TDVRESLT.PWS_TINWSYS_IS_NO and
    TINWSYS.TINWSYS_IS_NUMBER.

3.  Update the TDVRESLT.pws_city_svd_nm with the
    TINWSYS.D_PRIN_CITY_SVD_NM joined on TDVRESLT.PWS_TINWSYS_IS_NO and
    TINWSYS.TINWSYS_IS_NUMBER.

4.  Update the TDVRESLT.dv_rule_name as follows:

> Evaluate TMNRULE_ABRV_TXT
>
> When "AR" then "Arsenic"
>
> When "CCR" then "Consumer Confidence"
>
> When "PNR" then "Public Notice"
>
> When "PH1" then "Chemicals"

When "PH2" then "Chemicals"

When "PH5" then "Chemicals"

When "DBP1" then "Disinfection/Disinfectant Byproducts"

> When "PBCU" then "Lead and Copper"
>
> When "TCR" then "Total Coliform"
>
> When "RAD" then "Radionuclides"
>
> When "SWTR" then "Surface Water Treatment"
>
> When "ESWT" then "Surface Water Treatment"
>
> When "FBR" then "Surface Water Treatment"
>
> When "LT1R" then "Surface Water Treatment"
>
> Otherwise "Unknown"

# Testing Approach

Integration testing methodology of SDWIS/eDV 1.0 will be:

1.  Create independent queries for several data sources (MIR10, ILR10,
    NYR10, MER10, NDR10, R4R10)(which includes both Oracle and SQL
    Server bases) to compare counts of QA Extracts from SDWIS/ODS, the
    eDV database, and the production databases to identify:

a\) subsets of PWS types, and

b\) violation types by rules.

Tools will be MS Access, Spoon, and Derby/Squirrel.

2.  Use QA extracts on small, medium, and large databases comparing
    count versus source for tables.

3.  Use, review, and verify that eDV reports meet Design requirements,
    where appropriate.

4.  Test all SDWIS/eDV screens and processes including:

> a\) Dashboard - Start Time, End Time, Status, Message, SDWIS/State
> Extract File.
>
> b\) Perform eDV - Extracts from SDWIS/State Databases, Importing of
> SDWIS/State Extracts into eDV using both extracted data and extracts
> supplied by a Primacy Agency, Importing SDWIS/ODS QA extracts into
> eDV, and Running of CDS Setup.
>
> c\) Specify Verification Periods - Start Date, End Date, and TCR
> Routine Sample Count.
>
> d\) Configure eDV - SDWIS/State Database connection parameters (Type,
> Server, Port, Service/Database Name, Schema Name, Password).

5.  Reuse CDS Test Data and compare actual versus eDV CDS test results
    to verify the Lead and Copper Rule, Surface Water Treatment Rule,
    Disinfectant/Disinfection by Product Rule, Total Coliform Rule,
    Radionuclide Rule, Chemical (Phase 2/5), Public Notification Rule,
    Schedule Activity, and Arsenic Rule Compliance Processes.

Exhibit 4-1 depicts the Certification Hardware/Software Environment for
SDWIS/eDV 1.0.

+-----------+------------------+--------------------+-----------------+
| Machine   | Hardware         | System Software    | Application     |
| Type      |                  |                    | Software        |
+-----------+------------------+--------------------+-----------------+
| Client    | -   Dell         | MicrosoftWindows   | Internet        |
| Wo        |     OptiPlex     | XP Professional    | Explorer 6      |
| rkstation |     GX260        | Version 2002       |                 |
|           |                  | Service Pack 2     | Apache Derby    |
|           | -   Intel        |                    | 10.2.2.0        |
|           |     Pentium 4    |                    |                 |
|           |     CPU 3.06 GHz |                    | Kettle ETL 2.4  |
|           |                  |                    |                 |
|           | -   1 GB RAM     |                    | Jas             |
|           |                  |                    | perIntelligence |
|           |                  |                    | 1.2.1           |
|           |                  |                    |                 |
|           |                  |                    | Adobe 7.0       |
|           |                  |                    | and/or 8.1      |
|           |                  |                    | (Optional for   |
|           |                  |                    | r               |
|           |                  |                    | eading/printing |
|           |                  |                    | report pdf)     |
|           |                  |                    |                 |
|           |                  |                    | MS Excel 2003   |
|           |                  |                    | SP2 (Optional   |
|           |                  |                    | for use with    |
|           |                  |                    | SDWIS/eDV       |
|           |                  |                    | Report)         |
+-----------+------------------+--------------------+-----------------+

[]{#__RefHeading___Toc173723130 .anchor}**Exhibit 4-1.** Certification
Hardware/Software Environment for SDWIS/eDV 1.0

This page is intentionally left blank.

[]{#AppendixA .anchor}**Appendix A**

Violation Types Not Supported by SDWIS/STATE Web Rel 1.0 Compliance
Determination/Assistance Processes

SDWIS/STATE Compliance Determination/Assistance adheres to the following
methodology:

-   For Violation Determination

    -   SDWIS/STATE processes identify candidate violations.

    -   Compliance officer reviews candidate violations and determines
        whether to issue candidate violation.

-   For Monitoring Schedules

    -   TCR: SDWIS/State suggests actual change in the monitoring
        schedule.

    -   All other rules: SDWIS/STATE evaluates monitoring schedules and
        provides report of potential opportunities to increase/decrease
        monitoring.

    -   In both cases, compliance officer makes schedule change
        decision.

-   For Inventory Changes

    -   SDWIS/STATE advises users when inventory changes may require
        schedule changes (e.g., population crosses a Rule-Population
        threshold, new Water System Facility entered, PWS Type changes,
        etc.).

SDWIS/STATE Rule Implementation adheres to the following methodology:

-   In general, determines violations at detailed level desired by
    primacy agencies

    -   Tools in SDWIS/STATE combined with SDWIS/FedRep translate to EPA
        reporting requirements.

-   Total Coliform Rule

    -   Implements identification of Acutes via real-time validation of
        positive sample results.

    -   Enables creation of temporary routine, return to normal routine,
        and repeat schedules via real-time validation of positive sample
        results.

    -   Identifies non-acute and monitoring violations via user-launched
        compliance checks.

    -   Offers suggested monitoring schedule changes following inventory
        modification.

-   All other Rules

    -   Identifies MCL violations during batch processing of new data
        (typically nightly at most primacy agencies).

    -   Identifies monitoring and other treatment technique violations
        via user-launched compliance checks.

> []{.mark}

[]{.mark}

SDWIS/STATE handles the vast majority of compliance determination
scenarios for the federal drinking water rules listed in Section 1.0 of
this document. However, as compliance determination was implemented for
each rule in SDWIS/STATE, implementation of a few aspects of some rules
was determined to have a relatively low cost-benefit ratio. While these
design decisions are recorded in SDWIS/STATE design documentation
produced over the last several years, they are re-summarized here.
Exhibit A-1 lists rule-violation type combinations where SDWIS/STATE
does not support automated candidate violation creation. Although some
compliance assistance is available, these violations must be determined
manually.

  --------- -------------- ----------------- --------------- ---------------------------------
  **Rule\   **Violation\   **Contaminant**   **Violation     **SDWIS/STATE Assistance**
  Code**    Type**                           Description**   

  DBP       02             Chlorite          MCL Violation   The complexity and infrequency of
                                                             these violations led to decision
                                                             to not determine these violations
                                                             via SDWIS/STATE Compliance
                                                             Assistance. However, SDWIS/STATE
                                                             does report results that are
                                                             greater than the MCL, to help
                                                             ensure these are not missed.

  DBP       11             Chlorine dioxide  Chlorine        The complexity and infrequency of
                                             Dioxide         these violations led to decision
                                             Non-Acute MRDL  to not determine these violations
                                             due to results  via SDWIS/STATE Compliance
                                             (versus due to  Assistance. However, SDWIS/STATE
                                             lack of         does report results that are
                                             results)        greater than the MRDL to help
                                                             ensure these are not missed.

  DBP       12             0400              Qualified       SDWIS/STATE Compliance Schedule
                                             operator        function may be used to determine
                                                             candidate violations but, in most
                                                             cases, these violations are
                                                             determined during Site Visits or
                                                             some other way.

  DBP       13             Chlorine dioxide  Chlorine        The complexity and infrequency of
                                             Dioxide Acute   these violations led to decision
                                             MRDL due to     to not determine these violations
                                             results (versus via SDWIS/STATE Compliance
                                             due to lack of  Assistance. However, SDWIS/STATE
                                             results)        does report results that are
                                                             greater than the MRDL to help
                                                             ensure these are not missed.

  DBP       37             0400              Failure to      SDWIS/STATE Compliance Schedule
                                             submit/obtain   function may be used to determine
                                             state approval  candidate violations but, in most
                                             for significant cases, these violations are
                                             treatment       determined during Site Visits or
                                             modifications   some other way.

  FBR       39             0500              Failure To      No assistance -- although users
                                             Submit Plant    may build a query to identify
                                             Schematic       those that were required but have
                                                             not submitted a schematic; this
                                                             information can be recorded in
                                                             SDWIS/STATE.

  FBR       40             0500              Failure To      No assistance
                                             Properly        
                                             Recycle         

  SWT       12             0300              Qualified       SDWIS/STATE Compliance Schedule
                                             operator        function may be used to determine
                                                             candidate violations but, in most
                                                             cases, these violations are
                                                             determined during Site Visits or
                                                             some other way.

  SWT       29             0300              Failure to      The SWTR Compliance Report alerts
                                             submit report   compliance officer of potential
                                             required after  need for report.
                                             failure of      
                                             Individual      
                                             filter          
                                             performance     

  SWT       37             0300              Failure to File SDWIS/STATE Compliance Schedule
                                             Plan or Consult function may be used to determine
                                                             candidate violations.

  SWT       47             0300              Construction of SDWIS/STATE Compliance Schedule
                                             an uncovered    function may be used to
                                             finished        track/determine candidate
                                             storage         violations if an existing storage
                                             facility        facility needs to be addressed.

  Old PN    05                               Notification    No longer used.
                                             State           

  Old PN    06                               Failure to      No longer used.
                                             notify public   
                                             after a         
                                             violation.      

  Various   07                               Treatment       No assistance provided.
                                             Technique       
                                             (Non-SWTR)      

  Various   09                               Record Keeping  SDWIS/STATE Compliance Schedule
                                                             function may be used to determine
                                                             candidate violations.

  Various   10                               Operations      SDWIS/STATE Compliance Schedule
                                             Report          function may be used to determine
                                                             candidate violations.
  --------- -------------- ----------------- --------------- ---------------------------------

[]{#__RefHeading___Toc173723131 .anchor}**Exhibit A-1.** Rule Violation
Type Combinations For Which SDWIS/STATE Does Not Determine Violations

Exhibit A-2 lists EPA Rule Requirements not supported in SDWIS/STATE

+---------+------------------------------------------+-----------------+
| *       | **EPA Rule Requirement**                 | **SDWIS/STATE** |
| *Rule** |                                          |                 |
+=========+==========================================+=================+
| All     | Samples must be collected according to a | Not supported   |
|         | written sample site plan                 |                 |
+---------+------------------------------------------+-----------------+
| TCR     | Samples must be collected at regular     | Not supported   |
|         | time intervals throughout the month.     |                 |
+---------+------------------------------------------+-----------------+
| TCR     | Two special requirements for Subpart H   | Not supported   |
|         | systems to collected near the first      |                 |
|         | service connection given in 141.21 (a)   |                 |
|         | (5)                                      |                 |
+---------+------------------------------------------+-----------------+
| All     | -   Analytical Method Requirements       | Not supported   |
|         |                                          | in SDWIS/STATE  |
|         | -   Lab Certification Requirements       | Web Rel 1.0     |
|         |                                          | (scheduled for  |
|         |                                          | implementation  |
|         |                                          | in SDWIS/STATE  |
|         |                                          | 2.0)            |
+---------+------------------------------------------+-----------------+
| All     | Two quarterly samples in one year during | Not supported   |
|         | each repeat compliance period \[141.24   |                 |
|         | (h) (4) (ii)\]                           |                 |
+---------+------------------------------------------+-----------------+
| Phase   | If the concentration in the composite    | Not supported   |
| II/V    | sample is GT/EQ one-fifth the MCL, a     |                 |
|         | follow-up must be taken within 14 days   |                 |
|         | at each sampling point included in the   |                 |
|         | composite.                               |                 |
+---------+------------------------------------------+-----------------+
| Phase   | If the population served is \> 3,300,    | Not supported   |
| II/V    | compositing may only be permitted by the |                 |
|         | State at sampling points within a single |                 |
|         | system.                                  |                 |
+---------+------------------------------------------+-----------------+

[]{#__RefHeading___Toc173723132 .anchor}**Exhibit A-2.** Rule
Requirements Not Supported in SDWIS/STATE

.

[]{#AppendixB .anchor}**Appendix B**

New Table Data Definition Language

This page intentionally left blank.

**Create a new table Data Verification Period (TDVDVPRD)**

Columns

+---------------------------+-----------------+-----------------------+
| **Logical Name**          | **Physical      | **Characteristics**   |
|                           | Name**          |                       |
+---------------------------+-----------------+-----------------------+
| TDVDVPRD_IS_NUMBER        | TDV             | Number, 5, mandatory. |
|                           | DVPRD_IS_NUMBER | Increment             |
|                           |                 | sequentially within   |
|                           |                 | Rule.                 |
+---------------------------+-----------------+-----------------------+
| Association to RULE: Each | TM              | Number, 5, mandatory  |
| Rule sometimes has one or | NRULE_IS_NUMBER |                       |
| more established.         |                 | Text, 2, mandatory    |
| (cascade). Each DV Period | TMNRULE_ST_CODE |                       |
| always is established for |                 |                       |
| one and only one Rule.    |                 |                       |
| (disassociate)            |                 |                       |
+---------------------------+-----------------+-----------------------+
| USER_SUPPLIED_BEGIN_DATE  | USER_BEGIN_DT   | Date, 8, mandatory,   |
|                           |                 | Description: The      |
|                           |                 | user-supplied DV      |
|                           |                 | period begin date for |
|                           |                 | a specified rule.     |
+---------------------------+-----------------+-----------------------+
| USER_SUPPLIED_END_DATE    | USER_END_DT     | Date, 8, mandatory    |
|                           |                 | Description: The      |
|                           |                 | user-supplied DV      |
|                           |                 | period end date for a |
|                           |                 | specified rule.       |
+---------------------------+-----------------+-----------------------+
| D                         | E               | Date, 8, optional     |
| ATA_EXTRACTION_BEGIN_DATE | XTRACT_BEGIN_DT | Description: The      |
|                           |                 | begin date for which  |
|                           |                 | data extraction will  |
|                           |                 | be limited for a      |
|                           |                 | specified rule.       |
|                           |                 | Calculated based upon |
|                           |                 | the user-supplied     |
|                           |                 | begin date and        |
|                           |                 | business logic for    |
|                           |                 | the specified rule.   |
+---------------------------+-----------------+-----------------------+
| DATA_EXTRACTION_END_DATE  | EXTRACT_END_DT  | Date, 8, optional     |
|                           |                 | Description: The end  |
|                           |                 | date for which data   |
|                           |                 | extraction will be    |
|                           |                 | limited for a         |
|                           |                 | specified rule.       |
|                           |                 | Calculated based upon |
|                           |                 | the user-supplied end |
|                           |                 | date and business     |
|                           |                 | logic for the         |
|                           |                 | specified rule.       |
+---------------------------+-----------------+-----------------------+
| TCR_REP                   | TCR_RPT_SMP_CT  | Number, 4, optional.  |
| EAT_SCHEDULE_SAMPLE_COUNT |                 | Only used for the TCR |
|                           |                 | Rule dates. Stores    |
|                           |                 | the number of samples |
|                           |                 | of the routine        |
|                           |                 | requirement that must |
|                           |                 | be exceeded before    |
|                           |                 | the TCR compliance    |
|                           |                 | process will generate |
|                           |                 | a repeat schedule on  |
|                           |                 | a triggered repeat    |
|                           |                 | positive sample.      |
+---------------------------+-----------------+-----------------------+
| USER_BEGIN_MINUS3         | US              | Date, 8, optional,    |
|                           | ER_BEGIN_MINUS3 | Description: The      |
|                           |                 | user-supplied DV      |
|                           |                 | period begin date for |
|                           |                 | a specified rule      |
|                           |                 | minus 3 years         |
+---------------------------+-----------------+-----------------------+
| USER_END_PLUS1            | USER_END_PLUS1  | Date, 8, optional,    |
|                           |                 | Description: The      |
|                           |                 | user-supplied DV      |
|                           |                 | period end date for a |
|                           |                 | specified rule plus 1 |
|                           |                 | year.                 |
+---------------------------+-----------------+-----------------------+

[]{#__RefHeading___Toc173723133 .anchor}**Exhibit B-1.** DV Period Table
Design

CREATE TABLE TDVDVPRD

(TDVDVPRD_IS_NUMBER DECIMAL(5) NOT NULL,

USER_BEGIN_DT DATE NOT NULL,

USER_END_DT DATE NOT NULL,

EXTRACT_END_DT DATE ,

EXTRACT_BEGIN_DT DATE ,

TMNRULE_IS_NUMBER DECIMAL(5) NOT NULL,

TMNRULE_ST_CODE CHAR(2) NOT NULL,

DN_ABBREVIATION_TE CHAR(4) ,

TCR_RPT_SMP_CT INTEGER ,

USER_BEGIN_MINUS3 DATE ,

USER_END_PLUS1 DATE ,

CONSTRAINT IDVPRD1

PRIMARY KEY

(TMNRULE_IS_NUMBER ,

TMNRULE_ST_CODE ,

TDVDVPRD_IS_NUMBER ));

**Add new table DV Process Log (TDVPRCLG)**

CREATE TABLE TDVPRCLG

(PROCESS_NUMBER DECIMAL(4) NOT NULL,

SUB_PROCESS_NUMBER DECIMAL(4) NOT NULL,

PROCESS_NAME VARCHAR(50) ,

SUB_POCESS_NAME VARCHAR(50) ,

INITIAL_TIMESTAMP TIMESTAMP NOT NULL,

END_TIMESTAMP TIMESTAMP ,

CONSTRAINT IDVPRCLG

PRIMARY KEY

(SUB_PROCESS_NUMBER ,

PROCESS_NUMBER ));

**Add new table DV Log (TDVLOG)**

create table TDVLOG

(TDVLOG_IS_NUMBER INTEGER not null ,

PROCESS_NAME VARCHAR(30) ,

START_TIMESTAMP TIMESTAMP ,

END_TIMESTAMP TIMESTAMP,

STATUS VARCHAR(20),

STATUSMESSGE VARCHAR(2000),

extractfilename VARCHAR(100),

constraint IDVLG primary key (TDVLOG_IS_NUMBER));

**Add new table DV Result (TDVRESLT)**

CREATE TABLE TDVRESLT

(TDVVIOL_IS_NUMBER BIGINT GENERATED ALWAYS AS IDENTITY ,

TDVVIOL_ST_CODE CHAR(2) NOT NULL,

DV_RULE_NAME VARCHAR(50) ,

DV_CD_RESULT_CODE CHAR(3) ,

DV_DF_RESULT_CODE CHAR(3) ,

DV_OR_RESULT_CODE CHAR(3) ,

DV_ND_RESULT_CODE CHAR(3) ,

TMNVIOL_IS_NUMBER DECIMAL(7) ,

TMNVIOL_IS_NUMBER1 DECIMAL(7) ,

TMNVIOL_ST_CODE CHAR(2) ,

TMNVIOL_EXT_SYS_NO DECIMAL(18) ,

PWS_TINWSYS_IS_NO DECIMAL(7) ,

PWS_TINWSYS_ST_CD CHAR(2) ,

PWS_NUMBER VARCHAR(12) ,

CDS_PWSID VARCHAR(12) ,

CDS_IS_NUMBER DECIMAL(7) ,

PWS_NAME VARCHAR(40) ,

PWS_FED_PRI_SRC_CD CHAR(3) ,

PWS_FED_TYPE_CD VARCHAR(4) ,

PWS_POPULATION_CNT DECIMAL(10) ,

PWS_ACTIVITY_DATE DATE ,

PWS_ACTIV_STAT_CD CHAR(1) ,

PWS_CNTY_SVD_NM VARCHAR(40) ,

PWS_CITY_SVD_NM VARCHAR(40) ,

WSF_TINWSF_IS_NO DECIMAL(7) ,

WSF_TINWSF_ST_CODE CHAR(2) ,

WSF_ST_ASGN_ID VARCHAR(12) ,

WSF_NAME VARCHAR(40) ,

WSF_TYPE_CD CHAR(2) ,

WSF_ACTIVITY_DAT DATE ,

WSF_WATER_TYPE_CD CHAR(3) ,

WSF_AVAIL_CD CHAR(1) ,

WSF_EXTERN_SYS_NO DECIMAL(5) ,

TMNVTYPE_IS_NUMBER DECIMAL(7) ,

TMNVTYPE_ST_CODE VARCHAR(2) ,

VTYPE_CODE CHAR(2) ,

VTYPE_NAME VARCHAR(40) ,

VTYPE_SEV_LVL CHAR(2) ,

VTYPE_CAT_CD VARCHAR(3) ,

ANL_TSAANLYT_IS_NO DECIMAL(7) ,

ANL_TSAANLYT_ST_CD CHAR(2) ,

ANALYTE_CODE CHAR(4) ,

ANALYTE_NAME VARCHAR(40) ,

VIO_TO_FED_ANL_CD VARCHAR(4) ,

VIO_ANLYS_RSLT_TXT VARCHAR(16) ,

VIO_ANLYS_RSLT_UOM VARCHAR(9) ,

VIO_MCL_NUM_RSLTS DECIMAL(4) ,

VIO_MCL_VIOLTD_TXT VARCHAR(16) ,

VIO_MCL_VIOLTD_UOM VARCHAR(9) ,

VIO_MNR_NUM_RSLTS DECIMAL(5) ,

VIOL_FED_VP_BEG_DT DATE ,

VIOL_FED_VP_END_DT DATE ,

VIOL_ST_VP_BEG_DT DATE ,

VIOL_ST_VP_END_DT DATE ,

VIOL_YEAR DECIMAL(4) ,

SAMPLES_RQD_CNT DECIMAL(5) ,

SAMPLES_MISSNG_CNT DECIMAL(5) ,

EXCEEDENCES_CNT DECIMAL(5) ,

ODS_VIOID VARCHAR(20) ,

ODS_DATA_ORIGIN_CD CHAR(1) ,

ODS_MAJOR_VIO_IND CHAR(1) ,

ODS_SEVERITY_CODE CHAR(3) ,

ODS_1ST_REPORT_DT TIMESTAMP ,

ODS_LAST_REPORT_DT TIMESTAMP ,

TMNRULE_IS_NUMBER DECIMAL(7) NOT NULL,

TMNRULE_ST_CODE CHAR(2) NOT NULL,

TMNRULE_ABRV_TXT CHAR(4) ,

TYPECODE VARCHR(40),

COMMENTS VARCHAR(2000) ,

CONSTRAINT IDVRSLT1

PRIMARY KEY

(TDVVIOL_IS_NUMBER ,

TDVVIOL_ST_CODE ));

CREATE INDEX IDVRESLT2

ON TDVRESLT

(PWS_NUMBER ASC,

ODS_VIOID ASC);

**Add new table Violation Type Analyte Rule Association (TDVVTARA):**

Description: The association of Violation Types/Rule to Analytes, This
table serves three purposes. First it associates a given Violation Type
and Rule combination to an Analyte..

-   Relationships:

```{=html}
<!-- -->
```
-   Each VIOLATION TYPE ANALYTE RULE ASSC always associates to a
    Violation Type and Rule one and only one ANALYTE. On delete,
    disassociate.

-   Each ANALYTE sometimes is associated to a Violation Type and Rule in
    one or more VIOLATION TYPE ANALYTE RULE ASSC. On delete of Analyte,
    cascade delete.

> Identifier: Violation Type Rule Assignment and SDWIS/STATE Analyte

CREATE TABLE TDVVTARA

(D_LAST_UPDT_TS TIMESTAMP NOT NULL default current timestamp,

TMNRULE_IS_NUMBER INTEGER NOT NULL,

TMNRULE_ST_CODE CHAR(2) NOT NULL,

TMNVTYPE_IS_NUMBER INTEGER NOT NULL,

TMNVTYPE_ST_CODE CHAR(2) NOT NULL,

TMNVTRA_ST_CODE CHAR(2) NOT NULL DEFAULT \'DV\',

TSAANLYT_IS_NUMBER INTEGER NOT NULL,

TSAANLYT_ST_CODE CHAR(2) NOT NULL,

TSAANLYT_CODE CHAR(4) ,

CONSTRAINT IVTARA01

PRIMARY KEY

(TSAANLYT_IS_NUMBER ,

TSAANLYT_ST_CODE ,

TMNRULE_IS_NUMBER ,

TMNRULE_ST_CODE ,

TMNVTYPE_IS_NUMBER ,

TMNVTYPE_ST_CODE ,

TMNVTRA_ST_CODE ));

CREATE INDEX IVTARA02

ON TDVVTARA

(TSAANLYT_IS_NUMBER ASC,

TSAANLYT_ST_CODE ASC);

CREATE INDEX IVTARA03

ON TDVVTARA

(TMNRULE_IS_NUMBER ASC,

TMNRULE_ST_CODE ASC,

TMNVTYPE_IS_NUMBER ASC,

TMNVTYPE_ST_CODE ASC,

TMNVTRA_ST_CODE ASC);

**Changes to Violation Type Rule Assignment (TMNVTRA)**

-   Add Attributes:

    -   ODS Violation Type (ODS_VTYPE) (text, 2, optional)

> Description: The violation type value in QAE that corresponds to the
> Violation Type, Analyte, and Rule combination in SDWIS/STATE.

-   ODS Major Violation Indicator (ODS_MAJOR_VIO_IND) (text, 1,
    optional)

> Description: The major violation indicator in QAE that corresponds to
> the Violation Type, Analyte, and Rule combination in SDWIS/STATE.
>
> Permitted values:
>
> Y (yes this is a major M/R violation)
>
> N (no this is not a major M/R violation)
>
> Null (this attribute does not apply to this violation)

-   ODS Report Indicator (ODS_REPORT_IND) (text, 1, mandatory)

> Description: Indicates whether the SDWIS/STATE Violation Type, Analyte
> and Rule combination is to be reported to EPA or not. There are a few
> combinations that EPA does not want reported.
>
> Permitted values:
>
> Y (yes this combination is to be reported to EPA)
>
> N (no this combination should not be reported to EPA)

-   ODS PWS_Report_Level_Indicator (ODS_PWS_RPT_LVL) (text, 1, optional)

> Description: Indicates whether the SDWIS/STATE Violation Type, Analyte
> and Rule combination is to always be reported to EPA at the PWS level
> rather than having the option of reporting a WSF with the violation.
>
> Permitted values:
>
> Y (yes this combination should always be reported to EPA without a
> WSF)
>
> N (no this combination can be reported with or without the WSF)

-   Change Attribute

```{=html}
<!-- -->
```
-   Type Code -- change from mandatory to optional

CREATE TABLE TMNVTRA

(TMNRULE_IS_NUMBER INTEGER NOT NULL,

TMNRULE_ST_CODE CHAR(2) NOT NULL,

TMNVTYPE_IS_NUMBER INTEGER NOT NULL,

TMNVTYPE_ST_CODE CHAR(2) NOT NULL,

TMNVTRA_ST_CODE CHAR(2) NOT NULL,

TYPE_CODE CHAR(2) ,

D_LAST_UPDT_TS TIMESTAMP NOT NULL,

D_USERID_CODE CHAR(8) NOT NULL,

ODS_VTYPE CHAR(2) ,

ODS_MAJOR_VIO_IND CHAR(1) ,

ODS_REPORT_IND CHAR(1) ,

ODS_PWS_RPT_LVL CHAR(1) ,

CONSTRAINT IVTRA01

PRIMARY KEY

(TMNRULE_IS_NUMBER ,

TMNRULE_ST_CODE ,

TMNVTYPE_IS_NUMBER ,

TMNVTYPE_ST_CODE ,

TMNVTRA_ST_CODE ));

CREATE INDEX IVTRA02

ON TMNVTRA

(TMNVTYPE_IS_NUMBER ASC,

TMNVTYPE_ST_CODE ASC);

**Add new table SDWIS/ODS Water System (ODSWSYS)**

CREATE TABLE ODSWSYS

(PWSID VARCHAR(9) NOT NULL

,PWSTYPECODE VARCHAR(4) NOT NULL

,PWSNAME VARCHAR(80)

,PWSACTIVITYCODE VARCHAR(1)

,PWSDEACTIVATIONDATE DATE

,PWSWHOLESALERIND VARCHAR(1)

,PWSRETPOPSERVD integer

,PWSSVCCONN integer

,SEASONBEGINMONDAY VARCHAR(5)

,SEASONENDMONDAY VARCHAR(5)

,PWSOWNERCODE VARCHAR(1)

,SUBMISSIONSTATUSCODE VARCHAR(1)

,VALIDITYINDICATOR VARCHAR(1)

,FIRSTREPORTEDDATE timestamp

,LASTREPORTEDDATE timestamp

, CONSTRAINT INDXODSWSYS

primary key (pwsid) )

create index TEMPINDX1 on TEMP(VCODE,CCODE,STVCODE,
BEGINDATE,ENDDATE,ENDDATE_NULL_IND, TMNRULE_IS_NUMBER, tmnrule_st_code);

**Add new table SDWIS/ODS Violation (ODSVIO)**

create table ODSVIO (

PWSID VARCHAR(9),

VIOID VARCHAR(20) not null,

DATAORIGINCODE VARCHAR(1),

FACID VARCHAR(12),

CCODE VARCHAR(4),

VCODE VARCHAR(2),

VIOLMEASURE decimal(15,9),

STATEMCL decimal(15,9),

MAJORVIOLIND VARCHAR(1),

COMPLPERBEGINDATE DATE,

COMPLPERENDDATE DATE,

VALIDITYINDICATOR VARCHAR(1) default \'Y\',

SEVERITYINDICATORCOUNT decimal(9),

FIRSTREPORTEDDATE timestamp,

LASTREPORTEDDATE timestamp,

USERID VARCHAR(20),

constraint VIO_PK primary key (PWSID,VIOID)

)

**The following new indexes are created to assist with performance for
the SDWIS/eDV processes.**

 

CREATE  INDEX  IANLYT04\
     ON  TSAANLYT \
     (TSAANLYT_IS_NUMBER                   ,  \
      TSAANLYT_ST_CODE                     ,  \
      TYPE_CODE                         ) ;\
     \
CREATE  INDEX  IMCSMP03\
     ON  TSAMCSMP \
     (TSASAMPL_IS_NUMBER,\
     TSASAMPL_ST_CODE,\
     FF_CHLOR_RES_MSR,\
     FLDTOT_CHL_RES_MSR  ) ;\
    \
CREATE  INDEX  IMPAVG06\
    ON  TMNMPAVG\
(\
    SSMPA_SASCH_IS_NO,  \
     SSMPA_SASCH_ST_CD,  \
     APPLICABLE_BEG_DAT,\
     MCL_COMPLIANC_METH,\
     NUMB_RESULTS_USED,\
     TSAANLYT_IS_NUMBER,\
     TSAANLYT_ST_CODE\
) ;

 

CREATE  INDEX  IMPAVG07\
    ON  TMNMPAVG\
(\
     SSMPA_SASCH_IS_NO,  \
     SSMPA_SASCH_ST_CD,  \
     TSAANLYT_IS_NUMBER,\
     TSAANLYT_ST_CODE,\
     APPLICABLE_BEG_DAT,\
     CDS_PROCESSED_TS,\
     SSMPA_MPRD_IS_NO,\
     SSMPA_MPRD_ST_CD \
) ;

 

CREATE  INDEX  ISAMPL14\
    ON  TSASAMPL \
    (COLLLECTION_END_DT,\
    COMPL_PURP_IND_CD ,\
    TYPE_CODE,\
    REJECT_REASON_CD,\
    D_CDS_TRIGGER_TS,\
    TSASAMPL_IS_NUMBER,\
    TSASAMPL_ST_CODE ) ;

 

CREATE  INDEX  ISAMPL15\
    ON  TSASAMPL \
    ( COMPL_PURP_IND_CD,\
    COLLLECTION_END_DT,\
    REJECT_REASON_CD,\
    TSASAMPL_IS_NUMBER,\
    TSASAMPL_ST_CODE ) ;

 

CREATE  INDEX  ISAMPL16\
    ON  TSASAMPL  \
    ( TSASMPPT_IS_NUMBER,\
    TSASMPPT_ST_CODE,\
    COMPL_PURP_IND_CD,\
    COLLLECTION_END_DT,\
    REJECT_REASON_CD,\
    TSASAMPL_IS_NUMBER,\
    TSASAMPL_ST_CODE );

 

CREATE  INDEX  ISAMPL17\
    ON  TSASAMPL  \
    ( TSASMPPT_IS_NUMBER,\
    TSASMPPT_ST_CODE,\
    TYPE_CODE,\
    COMPL_PURP_IND_CD ,\
    COLLLECTION_END_DT,\
    TSASAMPL_IS_NUMBER,\
    TSASAMPL_ST_CODE);

 

CREATE  INDEX  ISASAR09\
    ON  TSASAR \
    (TSASAMPL_IS_NUMBER,\
    TSASAMPL_ST_CODE,\
    TSAANLYT_IS_NUMBER                   ,  \
     TSAANLYT_ST_CODE                     ) ;

 

CREATE  INDEX  ISASAR10\
    ON  TSASAR \
    (TSASAMPL_IS_NUMBER,\
    TSASAMPL_ST_CODE,\
    DATA_QUALITY_CODE                     ) ;

 

CREATE  INDEX  ISASAR11\
    ON  TSASAR \
    (TSASAMPL_IS_NUMBER,\
    TSASAMPL_ST_CODE,\
    DATA_QUALITY_CODE,\
    TSAANLYT_IS_NUMBER,  \
    TSAANLYT_ST_CODE,\
    CDS_PROCESSED_TS,\
    TSASAR_IS_NUMBER,\
    TSASAR_ST_CODE                       ) ;

 

CREATE  INDEX  ISASCH11\
    ON  TMNSASCH \
    (TINWSF_IS_NUMBER ,\
     TINWSF_ST_CODE ,                     \
     TMNMNR_IS_NUMBER,\
     TMNMNR_ST_CODE ,\
     TMNSASCH_IS_NUMBER,\
     TMNSASCH_ST_CODE                  ) ;\
    \
CREATE  INDEX  ISMPPT07\
    ON  TSASMPPT \
    (TSASMPPT_IS_NUMBER,\
     TSASMPPT_ST_CODE,\
     TINWSF0IS_NUMBER,  \
     TINWSF0ST_CODE \
                     ) ; 

 

**The following indexes exist but are modified as follows to improve
performance of the SDWIS/eDV processes.**

 \
  CREATE  INDEX  IMNR09\
    ON  TMNMNR \
    (TSAANLYT_IS_NUMBER                   ,  \
     TSAANLYT_ST_CODE  ,          \
     TMNMNR_IS_NUMBER                     ,  \
     TMNMNR_ST_CODE                          \
    ) ;

  CREATE  INDEX  ISASAR06\
    ON  TSASAR \
    (TMNMPRD_IS_NUMBER                    ,  \
     TMNMPRD_ST_CODE,\
     TSASAMPL_IS_NUMBER                   ,  \
     TSASAMPL_ST_CODE                     ,\
     TSASAR_IS_NUMBER                   ,  \
     TSASAR_ST_CODE                          )      ;

 

  CREATE  INDEX  ISASCH05\
    ON  TMNSASCH \
    (TINWSF_IS_NUMBER ,\
     TINWSF_ST_CODE ,                     \
    TMNSASCH_IS_NUMBER,\
     TMNSASCH_ST_CODE                   ) ;

 

  CREATE  INDEX  ISSMPA05\
    ON  TMNSSMPA \
    (TMNSASCH_IS_NUMBER                     ,  \
     TMNSASCH_ST_CODE                   ,  \
     APPLICABLE_BEG_DAT,\
     APPLICABLE_END_DAT,\
     TMNMPRD_IS_NUMBER,\
     TMNMPRD_ST_CODE                   ) ;

CREATE INDEX IANLYT02\
ON TSAANLYT\
(CODE ,\
TSAANLYT_IS_NUMBER ,\
TSAANLYT_ST_CODE )\
;

 

CREATE INDEX ISASCH03\
ON TMNSASCH\
(TMNMNR_IS_NUMBER ,\
TMNMNR_ST_CODE ,\
TINWSYS_IS_NUMBER ,\
TINWSYS_ST_CODE ,\
BEGIN_DATE,\
END_DATE )\
;

 

CREATE INDEX IMNR03\
ON TMNMNR\
(TSAANLYT_IS_NUMBER ,\
TSAANLYT_ST_CODE ,\
SAMPLE_TYPE_CODE,\
TMNRULE_IS_NUMBER1,\
TMNRULE_ST_CODE1 ,\
SMPL_CNT_UNIT_CD\
)\
;

[^1]: SEVERITYINDICATORCOUNT may also be required but analysis to date
    indicates it is not needed. If it is needed, instead of an exact
    match on this field, it would be necessary to apply an SQL statement
    on this field because it records the actual count and not an
    indication that a severity count is needed. The SQL Statements would
    be "is null" or "\>0."