**APPENDIX G**

Drinking Water Watch Queries

This page is intentionally left blank.

1.0 Drinking water watch queries [1-3](#drinking-water-watch-queries)

1.1 Public Water System Search Page
[1-3](#public-water-system-search-page)

1.1.1 Search by Water System Number
[1-3](#search-by-water-system-number)

1.1.2 Search by Water System Name [1-3](#search-by-water-system-name)

1.1.3 Search by Water System County
[1-3](#search-by-water-system-county)

1.2 Water System Details Page [1-4](#water-system-details-page)

1.2.1 Water System Details [1-4](#water-system-details)

1.2.2 Points of Contact [1-5](#points-of-contact)

1.2.3 AOP and Population Served [1-5](#aop-and-population-served)

1.2.4 Service Connections [1-6](#__RefHeading___Toc163358703)

1.2.5 Sources of Water [1-6](#sources-of-water)

1.2.6 Service Areas [1-6](#service-areas)

1.2.7 Water Purchases [1-7](#water-purchases)

1.3 Water System Facilities List Page
[1-7](#water-system-facilities-list-page)

WaterSystemFacilities.jsp (Exhibit 11-4. Water System Facilities List)
[1-7](#watersystemfacilities.jsp-exhibit-11-4.-water-system-facilities-list)

1.3.1 Water System Facilities [1-7](#water-system-facilities)

1.4 Water System Facility Detail Page
[1-7](#water-system-facility-detail-page)

1.4.1 Water System Facility Detail [1-7](#water-system-facility-detail)

1.4.2 Sampling Points [1-8](#sampling-points)

1.4.3 Facility AOP [1-8](#facility-aop)

1.5 Summarized Field Sample Results List
[1-8](#summarized-field-sample-results-list)

1.6 Facility Analyte Level List [1-9](#facility-analyte-level-list)

1.7 Sample Schedule List [1-9](#sample-schedule-list)

1.7.1 Total Coliform Sample Schedules
[1-9](#total-coliform-sample-schedules)

1.7.2 Facility Analyte Field Levels
[1-10](#facility-analyte-field-levels)

1.7.3 Non-TCR Sample Schedule Groups
[1-10](#non-tcr-sample-schedule-groups)

1.7.4 Non-TCR Individual Analyte Sample Schedules
[1-11](#non-tcr-individual-analyte-sample-schedules)

1.8 Coliform Sample Results List [1-11](#coliform-sample-results-list)

1.9 Coliform Sample Summary Results
[1-12](#coliform-sample-summary-results)

1.10 Lead and Copper Sample Results List
[1-13](#lead-and-copper-sample-results-list)

1.11 Non-Coliform Samples List [1-14](#non-coliform-samples-list)

1.12 Non-Coliform Sample Results List
[1-14](#non-coliform-sample-results-list)

1.13 Analyte Selection List [1-15](#analyte-selection-list)

1.14 Violations Page [1-16](#violations-page)

1.14.1 Group Violations [1-16](#group-violations)

1.14.2 Individual Violations [1-16](#individual-violations)

1.15 Analytes in Analyte Group Page
[1-17](#analytes-in-analyte-group-page)

1.16 Violation Detail Page [1-17](#violation-detail-page)

1.16.1 Violation Detail [1-17](#violation-detail)

1.16.2 Enforcement Actions [1-18](#enforcement-actions)

1.17 Site Visits List [1-18](#site-visits-list)

1.17.1 Site Visits [1-18](#site-visits)

1.17.2 Deficiencies [1-18](#deficiencies)

1.17.3 Resolved Deficiencies Count [1-19](#resolved-deficiencies-count)

1.18 Milestones List [1-19](#milestones-list)

1.19 Coliform Sample Results List
[1-19](#coliform-sample-results-list-1)

1.20 Coliform Sample Summary Results List
[1-20](#coliform-sample-summary-results-list)

1.21 Lead and Copper Sample Summary Results List
[1-21](#lead-and-copper-sample-summary-results-list)

1.22 Summarized Field Sample Results List (MDBP Summaries)
[1-22](#summarized-field-sample-results-list-mdbp-summaries)

1.23 Non-Coliform Samples List [1-22](#non-coliform-samples-list-1)

1.24 Analyte Selection List [1-23](#analyte-selection-list-1)

1.25 Non-Coliform Sample Results List
[1-24](#non-coliform-sample-results-list-1)

# Drinking water watch queries

## Public Water System Search Page

### Search by Water System Number

Select TINWSYS.NUMBER0, TINWSYS.TINWSYS_IS_NUMBER,
TINWSYS.TINWSYS_ST_CODE, TINWSYS.NAME,

> TINWSYS.D_PWS_FED_TYPE_CD, TINWSYS.ACTIVITY_STATUS_CD,
> TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.D_FED_PRIM_SRC_CD

FROM TINWSYS WHERE NUMBER0 IS NOT NULL AND

> TINWSYS.NUMBER0 LIKE ?

ORDER BY TINWSYS.ACTIVITY_STATUS_CD, TINWSYS.NAME, TINWSYS.NUMBER0

### Search by Water System Name

Select TINWSYS.NUMBER0, TINWSYS.TINWSYS_IS_NUMBER,
TINWSYS.TINWSYS_ST_CODE, TINWSYS.NAME,

> TINWSYS.D_PWS_FED_TYPE_CD, TINWSYS.ACTIVITY_STATUS_CD,
> TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.D_FED_PRIM_SRC_CD
>
> FROM TINWSYS WHERE NUMBER0 IS NOT NULL AND
>
> TINWSYS.NAME LIKE?
>
> ORDER BY TINWSYS.ACTIVITY_STATUS_CD, TINWSYS.NAME, TINWSYS.NUMBER0

### Search by Water System County

Select TINWSYS.NUMBER0, TINWSYS.TINWSYS_IS_NUMBER,
TINWSYS.TINWSYS_ST_CODE, TINWSYS.NAME,

> TINWSYS.D_PWS_FED_TYPE_CD, TINWSYS.ACTIVITY_STATUS_CD,
> TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.D_FED_PRIM_SRC_CD
>
> FROM TINWSYS WHERE NUMBER0 IS NOT NULL AND TINWSYS.D_PRIN_CNTY_SVD_NM
> LIKE?
>
> ORDER BY TINWSYS.ACTIVITY_STATUS_CD, TINWSYS.NAME, TINWSYS.NUMBER0

Select TINWSYS.NUMBER0, TINWSYS.TINWSYS_IS_NUMBER,
TINWSYS.TINWSYS_ST_CODE, TINWSYS.NAME,

> TINWSYS.D_PWS_FED_TYPE_CD, TINWSYS.ACTIVITY_STATUS_CD,
> TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.D_FED_PRIM_SRC_CD
>
> FROM TINWSYS WHERE NUMBER0 IS NOT NULL AND TINWSYS.D_PWS_FED_TYPE_CD
> \<\> \'NP\'
>
> ORDER BY TINWSYS.ACTIVITY_STATUS_CD, TINWSYS.NAME, TINWSYS.NUMBER0

Select TINWSYS.NUMBER0, TINWSYS.TINWSYS_IS_NUMBER,
TINWSYS.TINWSYS_ST_CODE, TINWSYS.NAME,

> TINWSYS.D_PWS_FED_TYPE_CD, TINWSYS.ACTIVITY_STATUS_CD,
> TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.D_FED_PRIM_SRC_CD
>
> FROM TINWSYS WHERE NUMBER0 IS NOT NULL AND TINWSYS.D_PWS_FED_TYPE_CD
> =?
>
> ORDER BY TINWSYS.ACTIVITY_STATUS_CD, TINWSYS.NAME, TINWSYS.NUMBER0

Select TINWSYS.NUMBER0, TINWSYS.TINWSYS_IS_NUMBER,
TINWSYS.TINWSYS_ST_CODE, TINWSYS.NAME,

> TINWSYS.D_PWS_FED_TYPE_CD, TINWSYS.ACTIVITY_STATUS_CD,
> TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.D_FED_PRIM_SRC_CD
>
> FROM TINWSYS WHERE NUMBER0 IS NOT NULL AND TINWSYS.D_FED_PRIM_SRC_CD
> =?
>
> ORDER BY TINWSYS.ACTIVITY_STATUS_CD, TINWSYS.NAME, TINWSYS.NUMBER0

Above queries are displayed using WaterSystems.jsp (Exhibit 11-2. Water
Systems List).

## Water System Details Page

WaterSystemDetail.jsp (Exhibit 11-3. Water System Details Page**)**

### Water System Details

Select number0, d_pws_fed_type_cd, name, D_pws_st_type_cd,
d_prin_cnty_svd_nm, d_fed_prim_src_cd AS d_st_prim_src_cd,

activity_status_cd, activity_date

from TINWSYS where tinwsys_is_number =? and tinwsys_st_code =?

### Points of Contact

####  {#section .unnumbered}

SELECT TININDIV.LAST_NAME, TININDIV.FIRST_NAME_TEXT, \" +
TININDIV.JOB_TITLE_TEXT, TINWSLEC.TYPE_CODE, TINLGENT.ADDR_LINE_ONE_TXT,
TINLGENT.ADDR_LINE_TWO_TXT, TINLGENT.ADDRESS_CITY_NAME,
TINLGENT.ADDRESS_STATE_CODE, TINLGENT.ADDRESS_ZIP_CODE,
TINLGENT.TINLGENT_IS_NUMBER, TINLGENT.TINLGENT_ST_CODE

FROM TINWSLEC, TINLGENT, TININDIV

WHERE TINWSLEC.TINWSYS_IS_NUMBER = ?

> AND TINWSLEC.TINWSYS_ST_CODE = ?
>
> AND TINWSLEC.TINLGENT_IS_NUMBER = TINLGENT.TINLGENT_IS_NUMBER
>
> AND TINWSLEC.TINLGENT_ST_CODE = TINLGENT.TINLGENT_ST_CODE
>
> AND TINLGENT.TINLGENT_IS_NUMBER = TININDIV.TINLGENT_IS_NUMBER
>
> AND TINLGENT.TINLGENT_ST_CODE = TININDIV.TINLGENT_ST_CODE
>
> AND TINWSLEC.ACTIVE_IND_CD = 'A'

ORDER BY TINLGENT.NAME, TINWSLEC.TYPE_CODE (A.G.1.2.2.a)

For each contact fetched replacing ? with TINLGENT.TINLGENT_IS_NUMBER
AND TINLGENT.TINLGENT.\_ST_CODE respectively:

> SELECT TINLGCOM.PHONE_NBR
>
> FROM TINLGCOM
>
> WHERE TINLGCOM.TINLGENT_IS_NUMBER = ?
>
> AND TINLGCOM.TINLGENT_ST_CODE = ?
>
> AND TINLGCOM_PURPOSE_CODE = 'BUS'

For each contact fetched replacing ? with TINLGENT.TINLGENT_IS_NUMBER
AND TINLGENT.TINLGENT.\_ST_CODE respectively:

> SELECT TINLGCOM.EMAIL_ADDRESS
>
> FROM TINLGCOM
>
> WHERE TINLGCOM.TINLGENT_IS_NUMBER = ?
>
> AND TINLGCOM.TINLGENT_ST_CODE = ?
>
> AND TINLGCOM_PURPOSE_CODE = 'EMAIL'

### AOP and Population Served

SELECT TINAOPRD.START_MONTH, TINAOPRD.START_DAY, TINAOPRD.END_MONTH,
TINAOPRD.END_DAY, TINPOPSV.TYPE_CODE, TINPOPSV.AVG_DAILY_CNT

FROM TINAOPRD, TINPOPSV

WHERE TINAOPRD.TINWSYS_IS_NUMBER =?

> AND TINAOPRD.TINWSYS_ST_CODE =?
>
> AND TINPOPSV.TINAOPRD_IS_NUMBER = TINAOPRD.TINAOPRD_IS_NUMBER
>
> AND TINPOPSV.TINWSYS_IS_NUMBER = TINAOPRD.TINWSYS_IS_NUMBER
>
> AND TINPOPSV.TINWSYS_ST_CODE = TINAOPRD.TINWSYS_ST_CODE
>
> AND (TINAOPRD.EFF_END_DT IS NULL OR TINAOPRD.EFF_END_DT \> current
> date)[]{#__RefHeading___Toc163358703 .anchor}

ORDER BY TINPOPSV.TYPE_CODE (A.G.1.2.3.a)

### Service Connections

SELECT TINSCC.TYPE_CODE, TINSCC.METER_TYPE_CODE,
TINSCC.METER_SIZE_MEASURE, TINSCC.SVC_CONNECT_CNT

FROM TINSCC

WHERE TINSCC.TINWSYS_IS_NUMBER =?

> AND TINSCC.TINWSYS_ST_CODE =?

ORDER BY TINSCC.TYPE_CODE, TINSCC.METER_TYPE_CODE,
TINSCC.METER_SIZE_MEASURE (A.G.1.2.4.a)

### Sources of Water

SELECT TINWSF.NAME, TINWSF.TYPE_CODE, TINWSF.ACTIVITY_STATUS_CD

FROM TINWSF

WHERE TINWSF.TINWSYS_IS_NUMBER = ?

> AND TINWSF.TINWSYS_ST_CODE =?
>
> AND TINWSF.TYPE_CODE IN (\'WL\', \'IN\', \'CC\', \'NP\', \'IG\',
> \'RS\', \'RC\', \'SP\')

ORDER BY TINWSF.ACTIVITY_STATUS_CD, TINWSF.TYPE_CODE, TINWSF.NAME
(A.G.1.2.5.a)

### Service Areas

SELECT TINSAT.TINSAT_CLASS_CODE, TINSAT.NME

FROM TINSAT, TINWSSAA

WHERE TINWSSAA.TINWSYS_IS_NUMBER =?

> AND TINWSSAA.TINWSYS_ST_CODE =?
>
> AND TINWSSAA.TINSAT_CLASS_CODE = TINSAT.TINSAT_CLASS_CODE
>
> AND TINWSSAA.TINSAT_ST_CODE = TINSAT.TINSAT_ST_CODE

AND TINWSSAA.TINSAT_NAME_CODE = TINSAT.TINSAT_NAME_CODE

### Water Purchases

SELECT TINWSYS.NUMBER0, TINWSYS.NAME, TINWPURC.WATER_TYPE_CODE,
TINWPURC.ACTIVE_DATE, SELLER.TYPE_CODE AS SELLER_TYPE_CODE,
SELLER.ST_ASGN_IDENT_CD AS SELLER_CODE, BUYER.TYPE_CODE AS
BUYER_TYPE_CODE, BUYER.ST_ASGN_IDENT_CD AS BUYER_CODE

FROM TINWSYS, TINWPURC, TINWSF BUYER, TINWSF SELLER

WHERE TINWPURC.TINWSYS_IS_NUMBER =?

> AND TINWPURC.TINWSYS_ST_CODE =?
>
> AND TINWPURC.TINWSYS0IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER
>
> AND TINWPURC.TINWSYS0ST_CODE = TINWSYS.TINWSYS_ST_CODE
>
> AND TINWPURC.TINWSF_IS_NUMBER = SELLER.TINWSF_IS_NUMBER (+)
>
> AND TINWPURC.TINWSF_ST_CODE = SELLER.TINWSF_ST_CODE (+)
>
> AND TINWPURC.TINWSF0IS_NUMBER = BUYER.TINWSF_IS_NUMBER (+)
>
> AND TINWPURC.TINWSF0ST_CODE = BUYER.TINWSF_ST_CODE (+)
>
> AND BUYER.TINWSF.ACTIVITY_STATUS_CD = \'A\'

ORDER BY TINWSYS.NAME, BUYER.ST_ASGN_IDENT_CD (A.G.1.2.7.a)

## Water System Facilities List Page

### WaterSystemFacilities.jsp (Exhibit 11-4. Water System Facilities List) {#watersystemfacilities.jsp-exhibit-11-4.-water-system-facilities-list .unnumbered}

### Water System Facilities

SELECT TINWSF.ST_ASGN_IDENT_CD, TINWSF.NAME, TINWSF.TINWSF_IS_NUMBER,
TINWSF.TINWSF_ST_CODE, TINWSF.TYPE_CODE, TINWSF.ACTIVITY_STATUS_CD

FROM TINWSF

WHERE TINWSF.TINWSYS_IS_NUMBER =?

> AND TINWSF.TINWSYS_ST_CODE =?
>
> ORDER BY TINWSF.ACTIVITY_STATUS_CD, TINWSF.TYPE_CODE,
> TINWSF.ST_ASGN_IDENT_CD (A.G.1.3.1.a)

## Water System Facility Detail Page

WaterSystemFacility.jsp (Exhibit 11-5. Water System Facility Detail
Page)

### Water System Facility Detail

SELECT TINWSF.ST_ASGN_IDENT_CD, TINWSF.TYPE_CODE, TINWSF.NAME,
TINWSF.ACTIVITY_STATUS_CD, TINWSF.ACTIVITY_RSN_TXT,
TINWSF.ACTIVITY_DATE,

FROM TINWSF

WHERE TINWSF.TINWSF_IS_NUMBER =? AND TINWSF.TINWSF_ST_CODE =?

### Sampling Points

SELECT TSASMPPT.IDENTIFICATION_CD, TSASMPPT.DESCRIPTION_TEXT,
TSASMPPT.TYPE_CODE

FROM TSASMPPT

WHERE TSASMPPT.TINWSF0IS_NUMBER =?

> AND TSASMPPT.TINWSF0ST_CODE =?
>
> AND TSASMPPT.ACTIVITY_STATUS_CD = \'A\'

ORDER BY TSASMPPT.IDENTIFICATION_CD (A.G.1.4.2.a)

### Facility AOP

SELECT TINFAOP.START_MONTH, TINFAOP.START_DAY, TINFAOP.END_MONTH,
TINFAOP.END_DAY, TINFAOP.EFFECTIVE_BEGIN_DT, TINFAOP.EFFECTIVE_END_DATE

FROM TINFAOP

WHERE TINFAOP.TINWSF_IS_NUMBER =? AND TINFAOP.TINWSF_ST_CODE =?

ORDER BY TINFAOP.EFFECTIVE_BEGIN_DT DESC

## Summarized Field Sample Results List

MDBPSummaryList.jsp (Exhibit 11-6. Summarized Field Sample Results List)

SELECT TSAMDBPS.SMPLS_BYND_MEA_LVL, TSAMDBPS.PRCNT_BYND_MEA_LVL,
TSAMDBPS.MON_PER_AVG_MSR, TSAMDBPS.MPA_UOM_CODE,
TSAMDBPS.RUN_ANL_AVG_MSR, TSAMDBPS.RAA_UOM_CODE,
TMNMPRD.CP_PRD_BEGIN_DT, TMNMPRD.CP_PRD_END_DT, TSAANLYT.CODE,
TSAMDBPS.TYPE_CODE_CV, TSAMDBPS.MR_COMPLIANCE_IND,
TSAMDBPS.LVL_COMPLIANCE_IND

FROM TINWSF, TSAMDBPS, TMNFANL, TMNMPRD, TSAANLYT

WHERE TINWSF.TINWSF_IS_NUMBER =?

> AND TINWSF.TINWSF_ST_CODE =?
>
> AND TMNMPRD.CP_PRD_BEGIN_DT \>= ?
>
> AND TMNMPRD.CP_PRD_BEGIN_DT \<=?
>
> AND TMNFANL.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER
>
> AND TMNFANL.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE
>
> AND TSAMDBPS.TMNFANL_IS_NUMBER = TMNFANL.TMNFANL_IS_NUMBER
>
> AND TMNFANL.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TMNFANL.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE
>
> AND TSAMDBPS.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER
>
> AND TSAMDBPS.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE

ORDER BY TMNMPRD.CP_PRD_BEGIN_DT DESC

## Facility Analyte Level List

FANLList.jsp (Exhibit 11-7. Facility Analyte Level List)

SELECT TSAANLYT.CODE, TMNFANL.CONTROL_LEVEL_TYPE, TMNFANL.REPORTED_MSR,
TMNFANL.UOM_CODE, TMNFANL.EFFECTIVE_BEG_DAT, TMNFANL.EFFECTIVE_END_DAT,
SUM_TYPE_CODE_CV

FROM TINWSF, TMNFANL, TSAANLYT

WHERE TINWSF.TINWSF_IS_NUMBER = ?

> AND TINWSF.TINWSF_ST_CODE =?
>
> AND TINWSF.TINWSF_IS_NUMBER = TMNFANL.TINWSF_IS_NUMBER
>
> AND TINWSF.TINWSF_ST_CODE = TMNFANL.TINWSF_ST_CODE
>
> AND TMNFANL.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TMNFANL.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE

ORDER BY TSAANLYT.CODE

## Sample Schedule List

SampleSchedules.jsp (Exhibit 11-8. Sample Schedule List)

### Total Coliform Sample Schedules

SELECT TMNMNR.SAMPLE_COUNT, TMNMNR.SAMPLE_TYPE_CODE,
TMNMNR.SMPL_UNIT_COUNT, TMNMNR.SMPL_CNT_UNIT_CD, TMNSASCH.BEGIN_DATE,
TMNSASCH.END_DATE, TMNSASCH.BEGIN_MONTH, TMNSASCH.BEGIN_DAY,
TMNSASCH.END_MONTH, TMNSASCH.END_DAY, TSAANLYT.CODE, TSAANLYT.NAME

FROM TMNMNR, TMNSASCH, TSAANLYT, TMNRULE

WHERE TSAANLYT.CODE = \'3100\'

> AND TMNSASCH.TINWSYS_IS_NUMBER = ?
>
> AND TMNSASCH.TINWSYS_ST_CODE = ?
>
> AND (TMNSASCH.END_DATE IS NULL OR TMNSASCH.END_DATE \> ?)
>
> AND TMNSASCH.TMNMNR_IS_NUMBER = TMNMNR.TMNMNR_IS_NUMBER
>
> AND TMNSASCH.TMNMNR_ST_CODE = TMNMNR.TMNMNR_ST_CODE
>
> AND TMNMNR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TMNMNR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE
>
> AND TMNMNR.TMNRULE_IS_NUMBER = TMNRULE.TMNRULE_IS_NUMBER
>
> AND TMNMNR.TMNRULE_ST_CODE = TMNRULE.TMNRULE_ST_CODE

AND TMNRULE.ABBREVIATION_TEXT = 'TCR'

### Facility Analyte Field Levels

SELECT TINWSF.ST_ASGN_IDENT_CD, TINWSF.NAME AS WSF_NAME, TSAANLYT.CODE,
TSAANLYT.NAME, TMNFANL.NUM_DAYS_PER_MONTH, TMNFANL.SAMPLE_RQT_PER_DAY,
TMNFANL.EFFECTIVE_BEG_DAT, TMNFANL.EFFECTIVE_END_DAT, SUM_TYPE_CODE_CV

FROM TINWSF, TMNFANL, TSAANLYT

WHERE TINWSF.TINWSYS_IS_NUMBER = ?

> AND TINWSF.TINWSYS_ST_CODE =?
>
> AND TINWSF.TINWSF_IS_NUMBER = TMNFANL.TINWSF_IS_NUMBER
>
> AND TINWSF.TINWSF_ST_CODE = TMNFANL.TINWSF_ST_CODE
>
> AND TMNFANL.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TMNFANL.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE
>
> AND TMNFANL.NUM_DAYS_PER_MONTH \> 0

ORDER BY TMNFANL.EFFECTIVE_BEG_DAT DESC

### Non-TCR Sample Schedule Groups

SELECT TINWSF.ST_ASGN_IDENT_CD, TINWSF.NAME, TSAANGRP.CODE,
TSAANGRP.TSAANGRP_IS_NUMBER, TSAANGRP.TSAANGRP_ST_CODE, TSAANGRP.NAME AS
ANALYTE NAME, TMNMNR.SAMPLE_COUNT, TMNMNR.SAMPLE_TYPE_CODE,
TMNMNR.SMPL_CNT_UNIT_CD, TMNSSGRP.BEGIN_DATE, TMNSSGRP.END_DATE

FROM TMNMNR, TSAANGRP, TINWSF, TMNSSGRP

WHERE TMNSSGRP.TINWSYS_IS_NUMBER = ?

> AND TMNSSGRP.TINWSYS_ST_CODE = ?
>
> AND (TMNSSGRP.END_DATE IS NULL OR TMNSSGRP.END_DATE \> ?) AND
> TMNSSGRP.TMNMNR_IS_NUMBER = TMNMNR.TMNMNR_IS_NUMBER
>
> AND TMNSSGRP.TMNMNR_ST_CODE = TMNMNR.TMNMNR_ST_CODE
>
> AND TMNMNR.TSAANGRP_IS_NUMBER = TSAANGRP.TSAANGRP_IS_NUMBER
>
> AND TMNMNR.TSAANGRP_ST_CODE = TSAANGRP.TSAANGRP_ST_CODE
>
> AND TMNSSGRP.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER
>
> AND TMNSSGRP.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE

ORDER BY TINWSF.ST_ASGN_IDENT_CD, TSAANGRP.NAME, TMNSSGRP.BEGIN_DATE
DESC (A.G.1.7.3.a)

### Non-TCR Individual Analyte Sample Schedules

SELECT TINWSF.ST_ASGN_IDENT_CD, TINWSF.NAME, TSAANLYT.CODE,
TSAANLYT.NAME as analyte_name, TMNMNR.SAMPLE_COUNT,
TMNMNR.SAMPLE_TYPE_CODE, TMNMNR.SMPL_CNT_UNIT_CD, TMNSASCH.BEGIN_DATE,
TMNSASCH.END_DATE

FROM TMNMNR, TSAANLYT, TMNSASCH, TINWSF

WHERE TMNSASCH.TINWSYS_IS_NUMBER =?

> AND TMNSASCH.TINWSYS_ST_CODE =?
>
> AND (TMNSASCH.END_DATE IS NULL OR TMNSASCH.END_DATE \>?)
>
> AND TMNSASCH.TMNMNR_IS_NUMBER = TMNMNR.TMNMNR_IS_NUMBER
>
> AND TMNSASCH.TMNMNR_ST_CODE = TMNMNR.TMNMNR_ST_CODE
>
> AND TMNMNR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TMNMNR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE
>
> AND TMNSASCH.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER
>
> AND TMNSASCH.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE
>
> AND TMNSASCH.TMNSSGRP_IS_NUMBER IS NULL
>
> AND TMNSASCH.TMNSSGRP_ST_CODE IS NULL
>
> AND TMNMNR.TMNRULE_IS_NUMBER IS NULL

ORDER BY TINWSF.ST_ASGN_IDENT_CD, TSAANLYT.NAME, TMNSASCH.BEGIN_DATE
DESC (A.G.1.7.4.a)

## Coliform Sample Results List 

TcrSampleResults.jsp (Exhibit 11-9. Coliform Sample Results List (For
Selected Water System))

SELECT TSASAMPL.TYPE_CODE, TSASAMPL.LAB_ASGND_ID_NUM,
TSASAMPL.COLLLECTION_END_DT, TSASAMPL.COLLCTN_END_TIME,
TSASMPPT.IDENTIFICATION_CD, TSASMPPT.DESCRIPTION_TEXT,
TSAMAR.PRESENCE_IND_CODE, TSAANLYT.CODE, TSAANLYT.NAME,
TMNMPRD.CP_PRD_BEGIN_DT, TMNMPRD.CP_PRD_END_DT, TSALAB.NAME AS LAB_NAME

FROM TSASAMPL, TSASAR, TSAANLYT, TSASMPPT, TSAMAR, TSALAB, TMNMPRD

WHERE TSASAMPL.TINWSYS_IS_NUMBER =?

> AND TSASAMPL.TINWSYS_ST_CODE =?
>
> AND TSASAMPL.COLLLECTION_END_DT \>= ((current date -- 2 years
> (default)) or user entered Sample Collection Date From)
>
> AND TSASAMPL.COLLLECTION_END_DT \<= (current date (default) or
> \[Sample Collection Date\] To)
>
> AND TSASAMPL.COMPL_PURP_IND_CD = \'Y\'
>
> AND TSASAMPL.REJECT_REASON_CD = \' \'
>
> AND TSASAR.DATA_QUALITY_CODE IN (\'A\', \'V\')
>
> AND TSAANLYT.CODE IN (\'3100\', \'3013\', \'3014\')
>
> AND TSASAMPL.TSASAMPL_IS_NUMBER = TSASAR.TSASAMPL_IS_NUMBER
>
> AND TSASAMPL.TSASAMPL_ST_CODE = TSASAR.TSASAMPL_ST_CODE
>
> AND TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE
>
> AND TSASAMPL.TSASMPPT_IS_NUMBER = TSASMPPT.TSASMPPT_IS_NUMBER
>
> AND TSASAMPL.TSASMPPT_ST_CODE = TSASMPPT.TSASMPPT_ST_CODE
>
> AND TSASAR.TSASAR_IS_NUMBER = TSAMAR.TSASAR_IS_NUMBER
>
> AND TSASAR.TSASAR_ST_CODE = TSAMAR.TSASAR_ST_CODE
>
> AND TSASAMPL.TSALAB_IS_NUMBER = TSALAB.TSALAB_IS_NUMBER
>
> AND TSASAMPL.TSALAB_ST_CODE = TSALAB.TSALAB_ST_CODE
>
> AND TSASAR.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER
>
> AND TSASAR.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE

ORDER BY TSASAMPL.COLLLECTION_END_DT DESC, TSASMPPT.IDENTIFICATION_CD,
TSASAMPL.LAB_ASGND_ID_NUM, TSAANLYT.CODE DESC (A.G.1.8.a)

## Coliform Sample Summary Results

TcrSampleSummaryResults.jsp (Exhibit 11-10. Coliform Sample Summary
Results List (For Selected Water System))

SELECT TSASSR.DATA_QUALITY_CODE, TMNMPRD.CP_PRD_BEGIN_DT,
TMNMPRD.CP_PRD_END_DT, TSASSR.COUNT_QTY, TINWSF.ST_ASGN_IDENT_CD,
SUMMARY_RECPT_DT

FROM TMNMPRD, TSASMPSM, TINWSF, TSAANLYT, TSASSR

WHERE TSASMPSM.TINWSYS_IS_NUMBER = ?

> AND TSASMPSM.TINWSYS_ST_CODE = ?
>
> AND TMNMPRD.CP_PRD_BEGIN_DT \>= ((current date -- 2 years (default))
> or Monitoring Period Begin Date From)
>
> AND TMNMPRD.CP_PRD_BEGIN_DT \<= ((current date (default) or
> \[Monitoring Period Begin Date\] To)
>
> AND TSASMPSM.COMPL_PURP_IND_CD = \'Y\'
>
> AND TSASSR.DATA_QUALITY_CODE IN (\'A\', \'V\')
>
> AND TSAANLYT.CODE = \'3100\'
>
> AND TSASSR.TYPE_CODE = \'RT\' (A.G.1.9.a)
>
> AND TSASMPSM.TSASMPSM_IS_NUMBER = TSASSR.TSASMPSM_IS_NUMBER
>
> AND TSASMPSM.TSASMPSM_ST_CODE = TSASSR.TSASMPSM_ST_CODE
>
> AND TSASMPSM.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER
>
> AND TSASMPSM.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE
>
> AND TSASMPSM.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TSASMPSM.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE
>
> AND TSASMPSM.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER (+)
>
> AND TSASMPSM.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE (+)

ORDER BY TMNMPRD.CP_PRD_BEGIN_DT DESC

## Lead and Copper Sample Results List

LeadAndCopperSampleSummaryResults.jsp (Exhibit 11-11. Lead and Copper
Sample Results List (For Selected Water System))

SELECT TSASSR.DATA_QUALITY_CODE, TMNMPRD.CP_PRD_BEGIN_DT,

TMNMPRD.CP_PRD_END_DT, TSASSR.COUNT_QTY, TSASSR.MEASURE,

TINWSF.ST_ASGN_IDENT_CD, SUMMARY_RECPT_DT, TSAANLYT.CODE

FROM TSAANLYT, TSASMPSM, TINWSF, TSASSR, TMNMPRD

WHERE TSASMPSM.TINWSYS_IS_NUMBER =? AND TSASMPSM.TINWSYS_ST_CODE =?

AND TMNMPRD.CP_PRD_BEGIN_DT \>= (current date -- 2 years (default)) or
(Monitoring Period Begin Date From)

AND TMNMPRD.CP_PRD_BEGIN_DT \<= (current date (default)) or \[Monitoring
Period Begin Date\] To)

AND TSASMPSM.COMPL_PURP_IND_CD = \'Y\' AND

TSASSR.DATA_QUALITY_CODE IN (\'A\', \'V\') AND

TSAANLYT.CODE IN (\'PB90\', \'CU90\')

AND TSASSR.TYPE_CODE = \'90\'

AND TSASMPSM.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TSASMPSM.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE AND

TSASMPSM.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER AND

TSASMPSM.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE AND

TSASMPSM.TSASMPSM_IS_NUMBER = TSASSR.TSASMPSM_IS_NUMBER AND

TSASMPSM.TSASMPSM_ST_CODE = TSASSR.TSASMPSM_ST_CODE AND

TSASMPSM.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER (+) AND

TSASMPSM.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE (+)

ORDER BY TMNMPRD.CP_PRD_BEGIN_DT DESC, TSAANLYT.CODE (A.G.1.10.a)

## Non-Coliform Samples List

NonTcrSamples.jsp (Exhibit 11-12. Non-Coliform Samples List (For
Selected Water System))

SELECT DISTINCT TSASAMPL.LAB_ASGND_ID_NUM, TSASAMPL.COLLLECTION_END_DT,

> TSASAMPL.TSASAMPL_IS_NUMBER, TSASAMPL.TSASAMPL_ST_CODE,
>
> TSASAMPL.TYPE_CODE, TSASAMPL.COLLCTN_END_TIME,
> TSASMPPT.IDENTIFICATION_CD,
>
> TSASMPPT.DESCRIPTION_TEXT, TSALAB.NAME AS LAB_NAME
>
> FROM TSASAMPL, TSASAR, TSASMPPT, TSALAB, TSAANLYT
>
> WHERE TSASAMPL.TINWSYS_IS_NUMBER = ?
>
> AND TSASAMPL.TINWSYS_ST_CODE = ?
>
> AND TSASAMPL.COMPL_PURP_IND_CD = \'Y\'
>
> AND TSASAMPL.REJECT_REASON_CD = \' \'
>
> AND TSASAR.DATA_QUALITY_CODE IN (\'A\', \'V\')
>
> AND TSAANLYT.TYPE_CODE NOT = 'MOR' (A.G.1.11.a)
>
> AND TSASAMPL.COLLLECTION_END_DT \>= ?
>
> AND TSASAMPL.COLLLECTION_END_DT \<= ?
>
> AND TSASAMPL.TSASAMPL_IS_NUMBER = TSASAR.TSASAMPL_IS_NUMBER
>
> AND TSASAMPL.TSASAMPL_ST_CODE = TSASAR.TSASAMPL_ST_CODE
>
> AND TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER
>
> AND TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE
>
> AND TSASAMPL.TSASMPPT_IS_NUMBER = TSASMPPT.TSASMPPT_IS_NUMBER
>
> AND TSASAMPL.TSASMPPT_ST_CODE = TSASMPPT.TSASMPPT_ST_CODE
>
> AND TSASAMPL.TSALAB_IS_NUMBER = TSALAB.TSALAB_IS_NUMBER
>
> AND TSASAMPL.TSALAB_ST_CODE = TSALAB.TSALAB_ST_CODE
>
> ORDER BY TSASAMPL.COLLLECTION_END_DT DESC

## Non-Coliform Sample Results List

NonTcrSampleResults.jsp (Exhibit 11-13. Non-Coliform Sample Results List
(For Selected Sample))

SELECT TSAANLYT.CODE, TSAANLYT.NAME, TSASMN.CODE AS METHOD_CODE,

TSASAR.LESS_THAN_IND, TSASAR.LESS_THAN_CODE,

TSASAR.DETECTN_LIMIT_NUM, TSASAR.DETECTN_LIM_UOM_CD,

> TSASAR.REPORTED_MSR, TSASAR.UOM_CODE, TMNMPRD.CP_PRD_BEGIN_DT,
>
> TMNMPRD.CP_PRD_END_DT, TMNALRA.MEASURE,
>
> TMNALRA.UOM_CODE AS ALRA_UOM

FROM TSASAR, TSAANLYT, TSASMN, TMNALRA, TMNMPRD

WHERE TSASAR.TSASAMPL_IS_NUMBER =?

> AND TSASAR.TSASAMPL_ST_CODE =?

AND TMNALRA.THRESHOLD_TYPE_CD = \'MCL\' AND TMNALRA.END_DATE IS NULL
(Event 14633:A.G.1.12.a)

AND TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER

AND TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE

AND TSAANLYT.TSAANLYT_IS_NUMBER = TMNALRA.TSAANLYT_IS_NUMBER (+)

AND TSAANLYT.TSAANLYT_ST_CODE = TMNALRA.TSAANLYT_ST_CODE (+)

AND TSASAR.TSASMN_IS_NUMBER = TSASMN.TSASMN_IS_NUMBER (+)

AND TSASAR.TSASMN_ST_CODE = TSASMN.TSASMN_ST_CODE (+)

AND TSASAR.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER (+)

AND TSASAR.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE (+)

> // if Detection only AND TSASAR.CONCENTRATION_MSR \> 0

ORDER BY TSAANLYT.CODE

## Analyte Selection List

AnalyteList.jsp (Exhibit 11-14. Analyte Selection List))

SELECT TSAANLYT.TSAANLYT_IS_NUMBER, TSAANLYT.TSAANLYT_ST_CODE,

> TSAANLYT.CODE, TSAANLYT.NAME, TSAANLYT.TYPE_CODE,
>
> TMNALRA.MEASURE, TMNALRA.UOM_CODE, COUNT(\*)

FROM TSASAMPL, TSASAR, TSAANLYT, TMNALRA

WHERE TSASAMPL.TINWSYS_IS_NUMBER =?

AND TSASAMPL.TINWSYS_ST_CODE =?

AND TSAANLYT.CODE NOT IN (\'3100\', \'3013\', \'3014\')

AND TSASAMPL.COMPL_PURP_IND_CD = \'Y\'

AND TSASAMPL.REJECT_REASON_CD = \' \'

AND TSASAR.DATA_QUALITY_CODE IN (\'A\', \'V\')

AND TMNALRA.END_DATE IS NULL AND TMNALRA.THRESHOLD_TYPE_CD = \'MCL\'
(Event 14633:A.G.1.13.a)

AND TSASAMPL.TSASAMPL_IS_NUMBER = TSASAR.TSASAMPL_IS_NUMBER

AND TSASAMPL.TSASAMPL_ST_CODE = TSASAR.TSASAMPL_ST_CODE

AND TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER

AND TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE

AND TSAANLYT.TSAANLYT_IS_NUMBER = TMNALRA.TSAANLYT_IS_NUMBER (+)

AND TSAANLYT.TSAANLYT_ST_CODE = TMNALRA.TSAANLYT_ST_CODE (+)

GROUP BY TSAANLYT.TSAANLYT_IS_NUMBER, TSAANLYT.TSAANLYT_ST_CODE,

> TSAANLYT.CODE, TSAANLYT.NAME, TSAANLYT.TYPE_CODE, TMNALRA.MEASURE,
> TMNALRA.UOM_CODE

ORDER BY TSAANLYT.NAME

## Violations Page

Violations.jsp (Exhibit 11-15. Violations Page (for selected water
system))

### Group Violations

SELECT DISTINCT TSAANGRP.TSAANGRP_IS_NUMBER,TSAANGRP.TSAANGRP_ST_CODE,

TMNVGRP.TMNVGRP_IS_NUMBER, TMNVGRP.TMNVGRP_ST_CODE,

TMNVGRP.FISCAL_YEAR, TMNVGRP.GROUP_NUMBER,

TMNVIOL.STATUS_TYPE_CODE, TMNVTYPE.TYPE_CODE, TMNVTYPE.NAME,

TSAANGRP.CODE, TSAANGRP.NAME AS tsaanlyt_name,

TMNVIOL.DETERMINATION_DATE, TINWSF.ST_ASGN_IDENT_CD,

TINWSF.NAME as wsf_name

FROM TMNVIOL, TMNVTYPE, TSAANGRP, TMNVGRP, TINWSF

WHERE TMNVIOL.STATUS_TYPE_CODE=\'V\'

AND TMNVIOL.TMNVTYPE_IS_NUMBER = TMNVTYPE.TMNVTYPE_IS_NUMBER

AND TMNVIOL.TMNVTYPE_ST_CODE = TMNVTYPE.TMNVTYPE_ST_CODE

AND TMNVIOL.TMNVGRP_IS_NUMBER = TMNVGRP.TMNVGRP_IS_NUMBER

AND TMNVIOL.TMNVGRP_ST_CODE = TMNVGRP.TMNVGRP_ST_CODE

AND TMNVGRP.TSAANGRP_IS_NUMBER = TSAANGRP.TSAANGRP_IS_NUMBER

AND TMNVGRP.TSAANGRP_ST_CODE = TSAANGRP.TSAANGRP_ST_CODE

AND TMNVIOL.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER (+)

AND TMNVIOL.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE (+)

AND TMNVIOL.TINWSYS_IS_NUMBER =? AND TMNVIOL.TINWSYS_ST_CODE =?

ORDER BY TMNVIOL.DETERMINATION_DATE DESC

### Individual Violations

SELECT TMNVIOL.TMNVIOL_IS_NUMBER, TMNVIOL.TMNVIOL_ST_CODE,

TMNVIOL.FISCAL_YEAR, TMNVIOL.EXTERNAL_SYS_NUM,

TMNVIOL.STATUS_TYPE_CODE, TMNVTYPE.TYPE_CODE,

> TMNVTYPE.NAME, TSAANLYT.CODE, TSAANLYT.NAME as tsaanlyt_name,
>
> TMNVIOL.DETERMINATION_DATE, TINWSF.ST_ASGN_IDENT_CD,
>
> TINWSF.NAME as wsf_name

FROM TMNVIOL, TMNVTYPE, TSAANLYT, TINWSF

WHERE TMNVIOL.TINWSYS_IS_NUMBER =?

AND TMNVIOL.TINWSYS_ST_CODE =?

AND TMNVIOL.STATUS_TYPE_CODE = \'V\'

AND TMNVIOL.TMNVTYPE_IS_NUMBER = TMNVTYPE.TMNVTYPE_IS_NUMBER

AND TMNVIOL.TMNVTYPE_ST_CODE = TMNVTYPE.TMNVTYPE_ST_CODE

AND TMNVIOL.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER

AND TMNVIOL.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE

AND TMNVIOL.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER (+)

AND TMNVIOL.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE (+)

AND TMNVIOL.TMNVGRP_IS_NUMBER IS NULL

ORDER BY TMNVIOL.DETERMINATION_DATE DESC

## Analytes in Analyte Group Page

AnalyteGroupListing.jsp (Exhibit 11-16. Analytes in Analyte Group Page))

SELECT TSAANLYT.CODE, TSAANLYT.NAME

FROM TSAANLYT, TSAAGA

WHERE TSAANLYT.TSAANLYT_IS_NUMBER = TSAAGA.TSAANLYT_IS_NUMBER

AND TSAANLYT.TSAANLYT_ST_CODE = TSAAGA.TSAANLYT_ST_CODE

AND TSAAGA.TSAANLGP_IS_NUMBER =? AND TSAAGA.TSAANLGP_ST_CODE =?

ORDER BY TSAANLYT.CODE

## Violation Detail Page

Violation.jsp (Exhibit 11-17. Violation Detail Page)

### Violation Detail

SELECT TMNVIOL.FISCAL_YEAR, TMNVIOL.EXTERNAL_SYS_NUM,

TMNVIOL.DETERMINATION_DATE, TMNVTYPE.TYPE_CODE,

TMNVTYPE.NAME, TMNVTYPE.CATEGORY_CODE,

TMNVIOL.STATUS_TYPE_CODE,

TSAANLYT.CODE, TSAANLYT.NAME as TSAANLYT_NAME,

> TMNVIOL.STATE_PRD_BEGIN_DT, TMNVIOL.STATE_PRD_END_DT,
>
> TMNVIOL.FED_PRD_BEGIN_DT, TMNVIOL.FED_PRD_END_DT,
>
> TINWSF.ST_ASGN_IDENT_CD, TINWSF.NAME as wsf_name ,
>
> TMNVIOL.ANALYSIS_RESULT_TE, TMNVIOL.ANALYSIS_RESULT_UO
>
> FROM TMNVIOL, TMNVTYPE, TSAANLYT, TINWSF
>
> WHERE TMNVIOL.TMNVIOL_IS_NUMBER =?
>
> AND TMNVIOL.TMNVIOL_ST_CODE =? AND
>
> TMNVIOL.TMNVTYPE_IS_NUMBER = TMNVTYPE.TMNVTYPE_IS_NUMBER AND
>
> TMNVIOL.TMNVTYPE_ST_CODE = TMNVTYPE.TMNVTYPE_ST_CODE AND

TMNVIOL.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER (+) AND

TMNVIOL.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE (+) AND

TMNVIOL.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TMNVIOL.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE

### Enforcement Actions

SELECT TENENACT.FED_FISCAL_YEAR, TENENACT.EXTERNAL_SYS_NUM,

TENACTYP.LOCATION_TYPE_CODE, TENACTYP.FORMAL_TYPE_CODE,

TENACTYP.SUB_CATEGORY_CODE, TENACTYP.NAME,

TENENACT.D_STATUS_CODE, TENENACT.STATUS_DATE

FROM TMNVIEAA, TENENACT, TENACTYP

WHERE TMNVIEAA.TMNVIOL_IS_NUMBER =? AND TMNVIEAA.TMNVIOL_ST_CODE =?

AND TENENACT.D_STATUS_CODE = \'T\' AND

TMNVIEAA.TENENACT_IS_NUMBER = TENENACT.TENENACT_IS_NUMBER AND

TMNVIEAA.TENENACT_ST_CODE = TENENACT.TENENACT_ST_CODE AND

TMNVIEAA.TENACTYP_IS_NUMBER = TENACTYP.TENACTYP_IS_NUMBER AND

TMNVIEAA.TENACTYP_ST_CODE = TENACTYP.TENACTYP_ST_CODE

ORDER BY TENENACT.STATUS_DATE DESC

## Site Visits List

SiteVisits.jsp (Exhibit 11-18. Site Visits List)

### Site Visits

SELECT TINVISIT.TINVISIT_IS_NUMBER, TINVISIT.TINVISIT_ST_CODE,

TINVISIT.REASON_CD, TINVISIT.VISIT_DATE, TINVISIT.HIGHEST_DEFICIENCY

FROM TINVISIT

WHERE TINVISIT.TINWSYS_IS_NUMBER =? AND TINVISIT.TINWSYS_ST_CODE =? AND

> TINVISIT.VISIT_DATE \>=? AND
>
> TINVISIT.VISIT_DATE \<=? AND TINVISIT.STATUS = \'C\'

ORDER BY TINVISIT.VISIT_DATE DESC

### Deficiencies

SELECT COUNT(\*) AS COUNT

FROM TINDEFCY, TINSVDFA

WHERE TINDEFCY.SEVERITY IN (\'MIN\', \'SIG\') AND

TINDEFCY.TINDEFCY_IS_NUMBER = TINSVDFA.TINDEFCY_IS_NUMBER AND

TINDEFCY.TINDEFCY_ST_CODE = TINSVDFA.TINDEFCY_ST_CODE AND

TINSVDFA.TINVISIT_IS_NUMBER =? AND

TINSVDFA.TINVISIT_ST_CODE =?

### Resolved Deficiencies Count

SELECT COUNT(\*) AS COUNT

FROM TINDEFCY, TINSVDFA

WHERE TINDEFCY.SEVERITY IN (\'MIN\', \'SIG\')

AND TINDEFCY.TINDEFCY_IS_NUMBER = TINSVDFA.TINDEFCY_IS_NUMBER

AND TINDEFCY.TINDEFCY_ST_CODE = TINSVDFA.TINDEFCY_ST_CODE

AND TINSVDFA.TINVISIT_IS_NUMBER =?

AND TINSVDFA.TINVISIT_ST_CODE =?

AND TINDEFCY.RESOLVED_DATE IS NOT NULL

## Milestones List

Milestones.jsp (Exhibit 11-19. Milestones List)

SELECT TFRMEVNT.TYPE_CODE, TFRMEVNT.REASON_CODE,

TFRMEVNT.ACTUAL_DATE, TFRMEVNT.MEASURE, TFRMEVNT.STATUS

FROM TFRMEVNT

WHERE TFRMEVNT.TINWSYS_IS_NUMBER =?

AND TFRMEVNT.TINWSYS_ST_CODE =?

AND TFRMEVNT.STATUS = \'V\'

## Coliform Sample Results List

TcrSampleResultsGlobal.jsp (Exhibit 11-20. Coliform Sample Results List)

SELECT TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TINWSYS.NAME AS WS_NAME, TSASAMPL.TYPE_CODE,

TSASAMPL.LAB_ASGND_ID_NUM, TSASAMPL.COLLLECTION_END_DT,

TSASAMPL.COLLCTN_END_TIME, TSASMPPT.IDENTIFICATION_CD,

TSASMPPT.DESCRIPTION_TEXT, TSAMAR.PRESENCE_IND_CODE,

TSAANLYT.CODE, TSAANLYT.NAME, TMNMPRD.CP_PRD_BEGIN_DT,

TMNMPRD.CP_PRD_END_DT, TSALAB.NAME AS LAB_NAME

FROM TMNMPRD, TSAMAR, TSAANLYT, TSASAR, TSASMPPT, TSASAMPL,

TSALAB, TINWSYS

WHERE TSASAMPL.COMPL_PURP_IND_CD = \'Y\' AND

TSASAMPL.REJECT_REASON_CD = \' \' AND

TSASAR.DATA_QUALITY_CODE IN (\'A\', \'V\') AND

TSAANLYT.CODE IN (\'3100\', \'3013\', \'3014\') AND

TSASAMPL.TSASAMPL_IS_NUMBER = TSASAR.TSASAMPL_IS_NUMBER AND

TSASAMPL.TSASAMPL_ST_CODE = TSASAR.TSASAMPL_ST_CODE AND

TSASAMPL.TSASMPPT_IS_NUMBER = TSASMPPT.TSASMPPT_IS_NUMBER AND

TSASAMPL.TSASMPPT_ST_CODE = TSASMPPT.TSASMPPT_ST_CODE AND

TSASAR.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER AND

TSASAR.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE AND

TSASAR.TSASAR_IS_NUMBER = TSAMAR.TSASAR_IS_NUMBER AND

TSASAR.TSASAR_ST_CODE = TSAMAR.TSASAR_ST_CODE AND

TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE AND

TSASAMPL.TINWSYS_IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER AND

TSASAMPL.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE AND

TSASAMPL.TSALAB_IS_NUMBER = TSALAB.TSALAB_IS_NUMBER AND

TSASAMPL.TSALAB_ST_CODE = TSALAB.TSALAB_ST_CODE AND

TSASAMPL.COLLLECTION_END_DT \>=? AND

TSASAMPL.COLLLECTION_END_DT \<=?

extraClause AND TSAMAR.PRESENCE_IND_CODE = \'P\'

ORDER BY TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,
TSASAMPL.COLLLECTION_END_DT DESC, TSASMPPT.IDENTIFICATION_CD,
TSASAMPL.LAB_ASGND_ID_NUM, TSAANLYT.CODE DESC (A.G.1.19.a)

## Coliform Sample Summary Results List

TcrSampleSummaryResultsGlobal.jsp (Exhibit 11-21. Coliform Sample
Summary Results List)

SELECT TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TINWSYS.NAME AS WS_NAME, TSASSR.DATA_QUALITY_CODE,

TMNMPRD.CP_PRD_BEGIN_DT, TMNMPRD.CP_PRD_END_DT,

TSASSR.COUNT_QTY, TINWSF.ST_ASGN_IDENT_CD, SUMMARY_RECPT_DT

FROM TINWSYS, TMNMPRD, TSASMPSM, TINWSF, TSAANLYT, TSASSR

WHERE TSASMPSM.COMPL_PURP_IND_CD = \'Y\' AND

TSASSR.DATA_QUALITY_CODE IN (\'A\', \'V\') AND

TSAANLYT.CODE = \'3100\'

AND TSASSR.TYPE_CODE = \'RT\' (A.G.1.20.a)

AND TSASMPSM.TINWSYS_IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER AND

TSASMPSM.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE AND

TSASMPSM.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER AND

TSASMPSM.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE AND

TSASMPSM.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TSASMPSM.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE AND

TSASMPSM.TSASMPSM_IS_NUMBER = TSASSR.TSASMPSM_IS_NUMBER AND

TSASMPSM.TSASMPSM_ST_CODE = TSASSR.TSASMPSM_ST_CODE AND

TSASMPSM.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER (+) AND

TSASMPSM.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE (+) AND

TMNMPRD.CP_PRD_BEGIN_DT \>=? AND TMNMPRD.CP_PRD_BEGIN_DT \<=?

extraClause +

ORDER BY TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TMNMPRD.CP_PRD_BEGIN_DT DESC

## Lead and Copper Sample Summary Results List

LeadAndCopperSampleSummaryResultsGlobal.jsp (Exhibit 11-22. Lead and
Copper Sample Summary Results List)

SELECT TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TINWSYS.NAME AS WS_NAME, TSASSR.DATA_QUALITY_CODE,

TMNMPRD.CP_PRD_BEGIN_DT, TMNMPRD.CP_PRD_END_DT,

TSASSR.COUNT_QTY, TSASSR.MEASURE, TINWSF.ST_ASGN_IDENT_CD,
SUMMARY_RECPT_DT, TSAANLYT.CODE

FROM TINWSYS, TSASMPSM, TSAANLYT, TMNMPRD, TINWSF, TSASSR

WHERE TSASMPSM.COMPL_PURP_IND_CD = \'Y\' AND

TSASSR.DATA_QUALITY_CODE IN (\'A\', \'V\') AND

TSAANLYT.CODE IN (\'PB90\', \'CU90\')

AND TSASSR.TYPE_CODE = \'90\'

AND TSASMPSM.TINWSYS_IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER AND

TSASMPSM.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE AND

TSASMPSM.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TSASMPSM.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE AND

TSASMPSM.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER AND

TSASMPSM.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE AND

TSASMPSM.TSASMPSM_IS_NUMBER = TSASSR.TSASMPSM_IS_NUMBER AND +

TSASMPSM.TSASMPSM_ST_CODE = TSASSR.TSASMPSM_ST_CODE AND

TSASMPSM.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER AND

TSASMPSM.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE AND

TMNMPRD.CP_PRD_BEGIN_DT \>= ? AND TMNMPRD.CP_PRD_BEGIN_DT \<= ?

extraClause +

ORDER BY TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TMNMPRD.CP_PRD_BEGIN_DT DESC, TSAANLYT.CODE, TSASSR.TYPE_CODE
(A.G.1.21.a)

## Summarized Field Sample Results List (MDBP Summaries)

MDBPSummaryListGlobal.jsp (Exhibit 11-23. Summarized Field Sample
Results List (MDBP Summaries))

SELECT TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TINWSYS.NAME AS WS_NAME, TINWSF.ST_ASGN_IDENT_CD,

TMNMPRD.CP_PRD_BEGIN_DT, TMNMPRD.CP_PRD_END_DT,

TSAANLYT.CODE, TSAMDBPS.TYPE_CODE_CV,

TSAMDBPS.MR_COMPLIANCE_IND, TSAMDBPS.LVL_COMPLIANCE_IND

FROM TSAMDBPS, TINWSF, TINWSYS, TMNFANL, TMNMPRD, TSAANLYT

WHERE TSAMDBPS.TMNFANL_IS_NUMBER = TMNFANL.TMNFANL_IS_NUMBER AND

TMNFANL.TINWSF_IS_NUMBER = TINWSF.TINWSF_IS_NUMBER AND

TMNFANL.TINWSF_ST_CODE = TINWSF.TINWSF_ST_CODE AND

TINWSF.TINWSYS_IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER AND

TINWSF.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE AND

TSAMDBPS.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER AND

TSAMDBPS.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE AND

TSAMDBPS.TMNFANL_IS_NUMBER = TMNFANL.TMNFANL_IS_NUMBER AND

TMNFANL.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TMNFANL.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE AND

TMNMPRD.CP_PRD_BEGIN_DT \>=? AND TMNMPRD.CP_PRD_BEGIN_DT \<=?

extraClause

ORDER BY TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TMNMPRD.CP_PRD_BEGIN_DT DESC

## Non-Coliform Samples List

NonTcrSamplesGlobal.jsp (Exhibit 11-24. Non- Coliform Samples List))

SELECT DISTINCT TSASAMPL.LAB_ASGND_ID_NUM,

TSASAMPL.COLLLECTION_END_DT, TINWSYS.D_PRIN_CNTY_SVD_NM,

TINWSYS.NUMBER0, TINWSYS.NAME AS WS_NAME,

TINWSYS.TINWSYS_IS_NUMBER, TINWSYS.TINWSYS_ST_CODE,

TSASAMPL.TSASAMPL_IS_NUMBER, TSASAMPL.TSASAMPL_ST_CODE,

TSASAMPL.TYPE_CODE, TSASAMPL.COLLCTN_END_TIME,

TSASMPPT.IDENTIFICATION_CD, TSASMPPT.DESCRIPTION_TEXT,

TSALAB.NAME AS LAB_NAME

FROM TSASAMPL, TSASAR, TSASMPPT, TSALAB, TSAANLYT, TINWSYS

WHERE TSASAMPL.COMPL_PURP_IND_CD = \'Y\' AND

TSASAMPL.REJECT_REASON_CD = \' \' AND

TSASAR.DATA_QUALITY_CODE IN (\'A\', \'V\') AND

TSAANLYT.TYPE_CODE NOT = 'MOR' (A.G.1.23.a) AND

TSASAMPL.TSASAMPL_IS_NUMBER = TSASAR.TSASAMPL_IS_NUMBER AND

TSASAMPL.TSASAMPL_ST_CODE = TSASAR.TSASAMPL_ST_CODE AND

TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE AND

TSASAMPL.TSASMPPT_IS_NUMBER = TSASMPPT.TSASMPPT_IS_NUMBER AND

TSASAMPL.TSASMPPT_ST_CODE = TSASMPPT.TSASMPPT_ST_CODE AND

TSASAMPL.TSALAB_IS_NUMBER = TSALAB.TSALAB_IS_NUMBER AND

TSASAMPL.TSALAB_ST_CODE = TSALAB.TSALAB_ST_CODE AND

TSASAMPL.TINWSYS_IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER AND

TSASAMPL.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE AND

TSASAMPL.COLLLECTION_END_DT \>=? AND

TSASAMPL.COLLLECTION_END_DT \<=?

extraClause

ORDER BY TINWSYS.D_PRIN_CNTY_SVD_NM, TINWSYS.NUMBER0,

TSASAMPL.COLLLECTION_END_DT DESC

## Analyte Selection List

AnalyteListGlobal.jsp (Exhibit 11-25. Analyte Selection List)

SELECT TSAANLYT.TSAANLYT_IS_NUMBER, TSAANLYT.TSAANLYT_ST_CODE,

TSAANLYT.CODE, TSAANLYT.NAME, TSAANLYT.TYPE_CODE,

TMNALRA.MEASURE, TMNALRA.UOM_CODE, COUNT(\*)

FROM TMNALRA, TSAANLYT, TSASAR, TSASAMPL, TINWSYS

WHERE TSAANLYT.TYPE_CODE NOT = 'MOR' AND

TSASAMPL.COMPL_PURP_IND_CD = \'Y\' AND

TSASAMPL.REJECT_REASON_CD = \' \' AND

TSASAR.DATA_QUALITY_CODE IN (\'A\', \'V\') AND

TMNALRA.END_DATE IS NULL AND TMNALRA.THRESHOLD_TYPE_CD = \'MCL\' (Event
14633:A.G.1.24.a)

AND TINWSYS.TINWSYS_IS_NUMBER = TSASAMPL.TINWSYS_IS_NUMBER AND

TINWSYS.TINWSYS_ST_CODE = TSASAMPL.TINWSYS_ST_CODE AND

TSASAMPL.TSASAMPL_IS_NUMBER = TSASAR.TSASAMPL_IS_NUMBER AND

TSASAMPL.TSASAMPL_ST_CODE = TSASAR.TSASAMPL_ST_CODE AND

TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER AND

TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE AND +

TSAANLYT.TSAANLYT_IS_NUMBER = TMNALRA.TSAANLYT_IS_NUMBER (+)

AND TSAANLYT.TSAANLYT_ST_CODE = TMNALRA.TSAANLYT_ST_CODE (+) AND

TSASAMPL.COLLLECTION_END_DT \>=? AND

TSASAMPL.COLLLECTION_END_DT \<=?

extraClause

GROUP BY TSAANLYT.TSAANLYT_IS_NUMBER, TSAANLYT.TSAANLYT_ST_CODE,

TSAANLYT.CODE, TSAANLYT.NAME, TSAANLYT.TYPE_CODE,

TMNALRA.MEASURE, TMNALRA.UOM_CODE

ORDER BY TSAANLYT.NAME

## Non-Coliform Sample Results List

NonTcrSampleResultsGlobal.jsp (Exhibit 11-26**.** Non-Coliform Sample
Results List**)**

SELECT TSAANLYT.CODE, TSAANLYT.NAME, TSASMN.CODE AS METHOD_CODE,

TSASAR.LESS_THAN_IND, TSASAR.LESS_THAN_CODE,

TSASAR.DETECTN_LIMIT_NUM, TSASAR.DETECTN_LIM_UOM_CD,

TSASAR.REPORTED_MSR, TSASAR.UOM_CODE,

TMNMPRD.CP_PRD_BEGIN_DT, TMNMPRD.CP_PRD_END_DT,

TMNALRA.MEASURE, TMNALRA.UOM_CODE AS ALRA_UOM

FROM TSASAR, TSAANLYT, TSASMN, TMNALRA, TMNMPRD

WHERE TSASAR.TSASAMPL_IS_NUMBER =? AND TSASAR.TSASAMPL_ST_CODE =?

AND TMNALRA.THRESHOLD_TYPE_CD = \'MCL\' AND TMNALRA.END_DATE IS NULL
(Event 14633:A.G.1.25.a)

AND TSASAR.TSAANLYT_IS_NUMBER = TSAANLYT.TSAANLYT_IS_NUMBER

AND TSASAR.TSAANLYT_ST_CODE = TSAANLYT.TSAANLYT_ST_CODE

AND TSAANLYT.TSAANLYT_IS_NUMBER = TMNALRA.TSAANLYT_IS_NUMBER (+)

AND TSAANLYT.TSAANLYT_ST_CODE = TMNALRA.TSAANLYT_ST_CODE (+)

AND TSASAR.TSASMN_IS_NUMBER = TSASMN.TSASMN_IS_NUMBER (+)

AND TSASAR.TSASMN_ST_CODE = TSASMN.TSASMN_ST_CODE (+)

AND TSASAR.TMNMPRD_IS_NUMBER = TMNMPRD.TMNMPRD_IS_NUMBER (+)

AND TSASAR.TMNMPRD_ST_CODE = TMNMPRD.TMNMPRD_ST_CODE (+)

extraClause AND TSASAR.CONCENTRATION_MSR \> 0

ORDER BY TSAANLYT.CODE