**APPENDIX** **A.D-10**

Calculate MCL Values

This Page is intentionally left blank

**A.D-10. Calculate MCL Values (Section 9.2.11)**

When this process changes or creates a record, value the D_USERID_CODE
with CDSSETUP.

Check-Point Restart Issues: Calculate MCL Values for New or Changed
SSMPAs.

The driver for this subprocess is the MPA table.

> The record key for this subprocess is the composite of CDS trigger
> timestamp and the TMNSASCH_IS_NUMBER and the TMNMPRD_IS_NUMBER
> columns.
>
> The sort order for this subprocess will be CDS trigger timestamp
> ascending and TMNSASCH_IS_NUMBER and TMNMPRD_IS_NUMBER ascending.
>
> This process might require a secondary Reference_IS_NUMBER in the
> CDS_SETUP_EXECUTION_LOG table.

1.  Create a log in the CDS Execution Log indicating the process start
    time.

2.  Read Monitoring Period Average records whose CDS trigger timestamp
    is greater than the last time the *CDS Setup* process was run

3.  Determine UOM basis (this UOM will be the MCL UOM) as follows:

    a.  Determine if a current MCL exists for the analyte and, if so,
        read its measure and unit of measure. "Current MCL" means the
        MCL is in effect on the first day of the monitoring period to
        which the average applies. Usually this is equivalent to the
        Begin Date of the Monitoring Period but it could be the
        Effective Begin Date or the Seasonal Period Begin Month and Day
        of the Sample Schedule if either are after the Begin Day of the
        Monitoring Period. \[*Developer Note: The CDS software will use
        SSMPA Applicable Period Begin Date which takes into
        consideration all three dates.*

    b.  Read the Control Level Text and unit of measure from FANL where:

        i.  the FANL=s Analyte Code is equal to the Analyte Code
            referenced by the Monitoring Requirement that is referenced
            by the Sample Schedule that is associated to the current
            SSMPA and

        ii. the FANL=s Water System Facility is the same as the Water
            System Facility associated to the Monitoring Requirement
            (through Sample Schedule and SSMPA) and

        iii. the Control Level Type is equal to MAX and

        iv. the Effective Begin Date is less than or equal to the SSMPA
            Applicable Period Begin Date

    c.  If both MCL and FANL are found, set Significant Digit/UOM basis
        to FANL Control Level Text/UOM.

    d.  If MCL is found and FANL is not found, set Significant Digit/UOM
        basis to MCL Measure Text/UOM.

    e.  If MCL is not found and FANL is found, set Significant Digit/UOM
        basis to FANL Control Level Text/UOM.

    f.  If both MCL and FANL are not found,

        i.  If Analyte Code is either 2920, 1927, 1067 or 1004, set the
            UOM basis to MG/L.

        ii. Otherwise, set UOM basis to MP Average UOM for the same
            period.

4.  If MCL Value Method (from Monitoring Period Average) is equal to
    RAA, then:

    a.  Call the current Monitoring Period Average the "End Monitoring
        Period Average" AND

    b.  Invoke the "Running Annual Average Method" action block:

> ["Running Annual Average Method" Action Block]{.underline}

i.  If the MCL Value with Average_Type_Code equal to "RAA" associated to
    the same SSMPA as the \>End Monitoring Period Average= exists, then
    if its USER ID is not equal to \>CDSSETUP=, do not calculate a MCL
    Value for this SSMPA. (A.D-10.a)

ii. Else, read Average Measure and Total Days for the End Monitoring
    Period Average and every other Monitoring Period Average:

    1.  whose Applicable Period Begin Date is greater than the
        Applicable Period End Date of the End Monitoring Period Average
        minus one year AND

    2.  whose Applicable Period Begin Date is less than or equal to the
        Applicable Period End Date of the End Monitoring Period Average
        AND

    3.  whose MCL Compliance Method is equal to "RAA"

    4.  the Number of Results used is greater than 0

    5.  that is associated to a Sample Schedule (through SSMPA):

        a.  that references the same Analyte as the End Monitoring
            Period Average AND

        b.  references the same Water System Facility as the End
            Monitoring Period Average.

iii. Sum the Total Days from each Monitoring Period Average

     1.  If the sum of Total Days is greater than zero, then calculate
         the weighted Average as

> E (MPA Average Measure \* Total Days) / E Total Days

Before calculating, temporarily convert (do not modify the record MP Avg
Measure) each Avg Measure that is not the same as the UOM Basis. Set the
MCL Value UOM to the UOM basis.

1.  If the sample analytical result (TSASAR) analyte is 2920, 1927, 1067
    or 1004, round the value to the least precise MP Avg Measure (with
    the least significant digits). Otherwise, round the weight average
    to the same significant digits as the Significant Digit basis
    determined in 3c-f.

```{=html}
<!-- -->
```
2.  Create a new or modify existing MCL Value with Average_Type_Code =
    RAA (A.D-10.b) record for that same SSMPA and set the USER ID to
    "CDSSETUP." Store the rounded weighted value as the Value in the MCL
    Value table and

    a.  link to all the Monitoring Period Average Records used in the
        computation (the link is accomplished through the associative
        table since there is a many-to-many relationship between MCL
        Value and Monitoring Period Average tables)

    b.  Link the MCL Value to the SSMPA of the End Monitoring Period
        Average and

    c.  Link the MCL Value to the ALRA retrieved while determining the
        UOM basis (note that for analytes 2950 and 2456, whose UOM Basis
        is from a FANL, an ALRA was not used and so no association to an
        ALRA should be made - the model does not support an association
        to the FANL x MAX that was used for these two analytes).

```{=html}
<!-- -->
```
1.  Otherwise, create a MCL Value record with mostly null values.

> [End of "Running Annual Average Method" action block]{.underline}

c.  If Analyte associated to the MP Average is either *E. coli* (3014)
    or cryptosporidium (3015), calculate SAM (Schedule Arithmetic Mean)
    as follows:

> (A.D-10.c)
>
> ["Schedule Arithmetic Mean"]{.underline} [Action Block]{.underline}

i.  If the MCL Value with Average_Type_Code equal to "SAM" and
    associated to the same SSMPA as the \>End Monitoring Period Average=
    exists, then if its USER ID is not equal to \>CDSSETUP=, do not
    calculate a MCL Value SAM for this SSMPA.

ii. Else, read Average Measure and Total Days for the End Monitoring
    Period Average and every other Monitoring Period Average:

    1.  whose Applicable Period Begin Date is less than or equal to the
        Applicable Period End Date of the End Monitoring Period Average
        AND

    2.  whose MCL Compliance Method is equal to "RAA"

    3.  the Number of Results used is greater than 0

    4.  that is associated to the same Sample Schedule (through SSMPA):

iii. Sum the Total Days from each Monitoring Period Average

     1.  If the sum of Total Days is greater than zero, then calculate
         the weighted Average as

> E (MPA Average Measure \* Total Days) / E Total Days

2.  For cryptosporidium, the process will round the value to the nearest
    thousands (e.g. 1.050). For *E. coli*, the process will round to the
    same significant digits as the FANL x SCR. If there is not a FANL x
    SCR, then it will round to the nearest integer (e.g. 1, 2, 8, etc).

3.  Create a new or modify existing MCL Value (Average Type Code = SAM)
    record for that same SSMPA and set the USER ID to "CDSSETUP."

    a.  Store the rounded weighted value as the Value in the MCL Value
        table,

    b.  Value the Average Duration Type Code with the periodicity of the
        Monitoring Period associated to the Average,

    c.  Value the Average Number Monitoring Period field with the number
        of monitoring periods used to calculate the current Average,

    d.  Value the Bin_Number_Text with the BIN number based on the
        following table

+----------------------+-----------------------------------------------+
| Cryptosporidium      | BIN                                           |
|                      |                                               |
| Average              | Number                                        |
+----------------------+-----------------------------------------------+
| \>= 3.000            | 4                                             |
+----------------------+-----------------------------------------------+
| \>= 1.000 and \<     | 3                                             |
| 3.000                |                                               |
+----------------------+-----------------------------------------------+
| \>=0.075 and \<      | 2                                             |
| 1.000                |                                               |
+----------------------+-----------------------------------------------+
| \< 0.075             | 1                                             |
+----------------------+-----------------------------------------------+

e.  Link the MCL Value - SAM to all the Monitoring Period Average
    Records used in the computation (the link is accomplished through
    the associative table since there is a many-to-many relationship
    between MCL Value and Monitoring Period Average tables)

f.  Link the MCL Value -- SAM to the SSMPA of the End Monitoring Period
    Average

```{=html}
<!-- -->
```
4.  Otherwise, create a MCL Value -- SAM record with mostly null values.

[End of]{.underline} ["Schedule Arithmetic Mean"]{.underline} [action
block]{.underline}

5.  If MCL Value Method (from Monitoring Period Average) is equal to
    MPA, then run the "Monitoring Period Average Method" action block.

["Monitoring Period Average Method" Action Block]{.underline}

a.  If the USER ID for the MCL Value associated to the same SSMPA as the
    Monitoring Period Average is not equal to "CDSSETUP," do not
    calculate a MCL Value for this SSMPA.

b.  If the MP Avg Measure is not in the same UOM as the UOM basis,
    convert the MP Avg Measure (but do not modify the recorded MP Avg
    Measure). Use the converted MPA Measure for storing the MCL Value.

```{=html}
<!-- -->
```
a.  Round the weight average to the same significant digits as the
    Significant Digit basis determined in 3c-f.

```{=html}
<!-- -->
```
a.  Create new or modify existing MCL Value with Average Type Code = MPA
    (A.D-10.d) record for that same SSMPA and set the USER ID to
    "CDSSETUP." Store the rounded Average Measure as the Value in the
    MCL Value table and

    i.  Link the MCL Value to the current Monitoring Period Average

    ii. Link the MCL Value to the SSMPA of the End Monitoring Period
        Average and

    iii. Link the MCL Value to the ALRA retrieved while determining the
         UOM basis (note that for analytes 2950 and 2456, whose UOM
         Basis is from a FANL, an ALRA was not used and so no
         association to an ALRA should be made - the model does not
         support an association to the FANL x MAX that was used for
         these two analytes).

> [End of "Monitoring Period Average Method" Action Block]{.underline}

6.  If Analyte associated to the MP Average is either TThm (2950) or
    HAA5 (2456), LRAA (Locational Running Annual Average) is calculated
    for each applicable Sampling Point as follows: (A.D-10.e)

    a.  Identify (distinct) Sampling Points associated to Monitoring
        Period Averages:

        i.  whose Applicable Period Begin Date is greater than the
            Applicable Period End Date of the End Monitoring Period
            Average minus one year AND

        ii. whose Applicable Period Begin Date is less than or equal to
            the Applicable Period End Date of the End Monitoring Period
            Average AND

        iii. whose MCL Compliance Method is equal to "RAA"

        iv. the Number of Results used is greater than 0

        v.  that is associated to a Sample Schedule (through SSMPA):

            1.  that references the same Analyte as the End Monitoring
                Period Average AND

            2.  references the same Water System Facility as the End
                Monitoring Period Average.

    b.  Calculate LRAA for each Sampling Point identified

> ["Locational Running Annual Average Method"]{.underline} [Action
> Block]{.underline}

c.  If the MCL Value with Average_Type_Code equal to "LRAA" and
    associated to the same SSMPA as the \>End Monitoring Period Average=
    exists, then if its USER ID is not equal to \>CDSSETUP=, do not
    calculate a MCL Value - LRAA for this SSMPA.

d.  Else, read Average Measure and Total Days for the End Monitoring
    Period Average and every other Monitoring Period Average:

    i.  whose Applicable Period Begin Date is greater than the
        Applicable Period End Date of the End Monitoring Period Average
        minus one year AND

    ii. whose Applicable Period Begin Date is less than or equal to the
        Applicable Period End Date of the End Monitoring Period Average
        AND

    iii. whose MCL Compliance Method is equal to "RAA"

    iv. the Number of Results used is greater than 0

    v.  that is associated to a Sample Schedule (through SSMPA):

        1.  that references the same Analyte as the End Monitoring
            Period Average AND

        2.  references the same Sampling Point and Water System Facility
            as the Sampling Point identified.

e.  Sum the Total Days from each Monitoring Period Average

    i.  If the sum of Total Days is greater than zero, then calculate
        the weighted Average as

> E (MPA Average Measure \* Total Days) / E Total Days

Before calculating, temporarily convert (do not modify the record MP Avg
Measure) each Avg Measure that is not the same as the UOM Basis. Set the
MCL Value UOM to the UOM basis.

i.  Round the weight average to the same significant digits as the
    Significant Digit basis determined in 3c-f.

```{=html}
<!-- -->
```
ii. Create a new or modify existing MCL Value (Average Type Code = LRAA)
    record for that same SSMPA and set the USER ID to "CDSSETUP." Store
    the rounded weighted value as the Value in the MCL Value table and

    1.  link to all the Monitoring Period Average Records used in the
        computation (the link is accomplished through the associative
        table since there is a many-to-many relationship between MCL
        Value and Monitoring Period Average tables)

    2.  Link the MCL Value to the SSMPA of the End Monitoring Period
        Average and

    3.  Link the MCL Value to the ALRA retrieved while determining the
        UOM basis (note that for analytes 2950 and 2456, whose UOM Basis
        is from a FANL, an ALRA was not used and so no association to an
        ALRA should be made - the model does not support an association
        to the FANL x MAX that was used for these two analytes).

    4.  Associate to the Sampling Point identified

iii. Otherwise, create a MCL Value -- LRAA record with mostly null
     values.

iv. Process next Sampling Point.

> [End of]{.underline} [ALocational Running Annual Average
> Method@]{.underline} [action block]{.underline}

7.  If an MCL or FANL-MAX was retrieved during UOM basis determination
    and the current MCL Value has an Average_Type_Code equal to "RAA",
    "MPA" or "LRAA", perform Chem/Rad MCL Compliance: (A.D-10.f)

    a.  If a FANL-MAX was retrieved, check to see if it references a
        level violation type. If it does not, create an advisory
        referencing message number 46 and record the water system
        number, name, federal PWS type code, federal primary source
        code, population, tinwsys_is_number, and tinwsys_st_code of the
        water system record; the state_asgn_id and name of the water
        system facility record; and the analyte code for the FANL.
        Otherwise, store the Measure of this TMNFANL record in a
        temporary variable "Level" (numeric - 16(9)) and proceed to Step
        c to compare Value (CALCULATED_VALUE of entity
        CALCULATED_MCL_VALUE) against this Level.

    b.  If an MCL was retrieved, then store the Measure of this TMNALRA
        record in a temporary variable "Level" (numeric - 16(9)) and
        proceed to Step b below to compare Value (CALCULATED_VALUE of
        entity CALCULATED_MCL_VALUE) against this Level.

    c.  If the Value is greater than the Level (note that both have been
        rounded so that the comparison is done at the same significant
        digits) for the TMNALRA or FANL-MAX record referenced by this
        qualifying MCL Value record.

        i.  Call action block **Clear Previous** **CDS Candidate MCL
            Violation (**first described here). Check for a CDS
            Candidate Violation where:

            1.  The CDS candidate violation is for the Water System
                Facility referenced by the MCL Value AND

            2.  The CDS candidate violation=s Compliance Period Begin
                Date is equal to the Begin Date of the Monitoring Period
                referenced by the MCL Value AND

            3.  The CDS candidate violation=s Compliance Period End Date
                is equal to the End Date of the Monitoring Period
                referenced by the MCL Value AND

            4.  The CDS candidate violation=s Analyte Code is equal to
                the Analyte Code referenced by the MCL Value AND

            5.  The CDS candidate violation=s Type Code is either 01 or
                02 (for an ALRA) or is equal to the Level Violation Type
                for a FANL

> If found, delete the CDS Candidate Violation and all associated
> records in the CDS Reporting entities.

ii. Pass a CDS Candidate MCL violation for the Analyte referenced by the
    MCL Value to the **Create CDS Candidate Violation** action block

    1.  If ALRA is the basis, pass Violation Type 01 if the number of
        results used to calculate the MCL Value is 1; otherwise pass
        Violation Type 02).

    2.  If FANL is the basis pass Level Violation Type associated to the
        FANL.

    3.  Create an advisory record with the message (Message \# 25) and
        record the water system number, name, federal PWS type code,
        federal primary source code, population, tinwsys_is_number, and
        tinwsys_st_code of the water system record; the state_asgn_id
        and name of the water system facility record; and the analyte,
        violation type, federal begin date and federal end date of the
        candidate violation.

iii. Check if a matching MCL violation with a status of preliminary,
     validate or rejected is already existing where: (A.D-10.g)

     1.  The Violation is for the Water System Facility referenced by
         the MCL Value AND

     2.  The Violation's associated Monitoring Period is the same
         Monitoring Period referenced by the MCL Value AND

     3.  The Violation's Analyte Code is equal to the Analyte Code
         referenced by the MCL Value AND

     4.  The Violation's Type Code is either 01 or 02 (for an ALRA) or
         is equal to the Level Violation Type for the FANL basis.

     5.  If a matching record is found, create an advisory record with
         the message (Message \# 57) and record the water system number,
         name, federal PWS type code, federal primary source code,
         population, tinwsys_is_number, and tinwsys_st_code of the water
         system record; the state_asgn_id and name of the water system
         facility record; and the analyte, violation type, federal begin
         date and federal end date of the existing violation.

> The following action involves evaluation of a Monitoring Period
> Average that was first evaluated above. This time, however, the
> Monitoring Period Average/SSMPA is selected based on the type of
> Sample Schedule it is associated to, namely a Finished TOC Sample
> Schedule.

8.  If Monitoring Period Average is associated to a Finished TOC Sample
    Schedule, then run the "TOC RAA Method" action block.

> A Finished TOC Schedule is one where

-   the Analyte Code associated to the Monitoring Requirement is equal
    to "2920" AND

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

-   

-   

-   (A.D-10.h) (A.D-10.i)

```{=html}
<!-- -->
```
-   Either

    -   The Sample Schedule is not associated to a Group Schedule and is
        associated to a Schedule Package Role with Type = F OR

    -   The Sample Schedule is associated to a Group Schedule and the
        Group Schedule is associate to a Schedule Package Role with Type
        = F

"TOC RAA Method" Action Block:

a.  If the USER ID for the MCL Value associated to the same SSMPA as the
    Monitoring Period Average is not equal to "CDSSETUP," do not
    calculate a Precursor Achieved Removal Ratio for the MCL Value
    associated to this SSMPA.

b.  Else, use the Monitoring Period associated to the SSMPA being
    processed as the End Monitoring Period. Go back 1 year and
    arithmetically average the Precursor Achieved Removal Ratio (the
    ones stored with the Monitoring Period Averages whose Number of
    Results used field value \> 0 in the new PRC_ACH_RMVL_RA_NO field)
    for the same schedule and store in both the PRC_ACH_RMVL_RA_NO and
    the PRC_ACH_RMVL_RA_TX (both new fields in the MCL Value table).

> Calculate the arithmetic average as follows:
>
> E (TMNMPAVG.PRC_ACH_RMVL_RA_NO \* TOTAL_DAYS) / E TOTAL_DAYS
>
> End "TOC RAA Method" Action Block.

c.  Identify the Facility Analyte Level (TMNFANL) where:

    i.  The Code for the Analyte is equal to 2920 AND

    ii. The Water System Facility referenced by the Facility Analyte
        Level is the same as the Water System Facility related to the
        MCL Value through the Sample Schedule, through the SSMPA related
        to the MCL Value AND

    iii. The Control Level Type of the Facility Analyte Level is equal
         to "MIN"AND

    iv. Facility Analyte Level EFFECTIVE_BEGIN_DATE is less than or
        equal to the SSMPA (referenced by the MCL Value) Applicable
        Period Begin Date AND

    v.  Facility Analyte Level EFFECTIVE_END_DATE is greater than or
        equal to the SSMPA (referenced by the MCL Value) Applicable
        Period Begin Date Or Facility Analyte Level EFFECTIVE_END_DATE
        is NULL/Blank).

d.  If the PARR is less than the Level (note that both have been rounded
    so that the comparison is done at the same significant digits) for
    the FANL-MIN record referenced by this qualifying MCL Value record.

    i.  Call action block **Clear Previous** **CDS Candidate MCL
        Violation (**first described here). Check for a CDS Candidate
        Violation where:

        1.  The CDS candidate violation is for the Water System Facility
            referenced by the MCL Value AND

        2.  The CDS candidate violation=s Compliance Period Begin Date
            is equal to the Begin Date of the Monitoring Period
            referenced by the MCL Value AND

        3.  The CDS candidate violation=s Compliance Period End Date is
            equal to the End Date of the Monitoring Period referenced by
            the MCL Value AND

        4.  The CDS candidate violation=s Analyte Code is equal to the
            Analyte Code referenced by the MCL Value AND

        5.  The CDS candidate violation=s Type Code is equal to the
            Level Violation Type for the FANL minimum

The following specifies what values to use when passing information to
the **Create Candidate Violation**. This action block will create one
candidate schedule and one or more candidate results for each candidate
MCL violation determined. In addition, this action block will create a
record in CDS Report Log entity for the first candidate MCL violation it
determines in a given run so that users can review the candidate
violations using the Review Reports Log and Reports. For other candidate
MCL violations determined in the same run of CDS Setup, associate them
to the CDS Report Log record created when the first candidate MCL
violation was determined.

  -----------------------------------------------------------------------
  **CDS REPORT LOG**        
  ------------------------- ---------------------------------------------
  **Field Name**            **Value**

  TCDSRLOG_IS_NUMBER        Running Sequence number to be generated by
                            getting Max of sequence number and increment
                            by 1.

  TCDSRLOG_ST_CODE          State Code of the state obtained from the
                            TINPRT table.

  REPORT_RUN_USERID         CDSSETUP

  REPORT_RUN_TS             State Code of the state obtained from the
                            TINPRT table.

  REPORT_TYPE               MCL
  -----------------------------------------------------------------------

+-------------------------+--------------------------------------------+
| **CDS CANDIDATE         |                                            |
| VIOLATION**             |                                            |
+=========================+============================================+
| **Field Name**          | **Value**                                  |
+-------------------------+--------------------------------------------+
| TCDSVIOL_IS_NUMBER      | Running Sequence number to be generated by |
|                         | getting Max of sequence number and         |
|                         | increment by 1.                            |
+-------------------------+--------------------------------------------+
| TCDSVIOL_ST_CODE        | State Code of the state obtained from the  |
|                         | TINPRT table.                              |
+-------------------------+--------------------------------------------+
| PWS_TINWSYS_IS_NO       | TINWSYS_IS_NUMBER                          |
|                         |                                            |
| PWS_TINWSYS_ST_CO       | TINWSYS_ST_CODE                            |
|                         |                                            |
| PWS_NUMBER              | NUMBER                                     |
|                         |                                            |
| PWS_NAME                | NAME                                       |
|                         |                                            |
| PWS_FED_PRI_SRC_CD      | FED_PRIMARY_SOURCE_CODE                    |
|                         |                                            |
| PWS_FED_TYPE_CD         | FEDERAL_TYPE_CODE                          |
|                         |                                            |
| PWS_POPULATION_CNT      | D_POPULATION_COUNT                         |
|                         |                                            |
| PWS_ACTIV_STAT_CD       | ACTIVITY_STATUS_CODE                       |
|                         |                                            |
| PWS_ACTIVITY_DATE       | ACTIVITY_DATE                              |
|                         |                                            |
|                         | From the TINWSYS associated to the MCL     |
|                         | Value that exceeds the MCL. This           |
|                         | denormalized information is used for       |
|                         | reporting purposes and to create the link  |
|                         | to the water system record when migrating  |
|                         | the candidate violations.                  |
+-------------------------+--------------------------------------------+
| WSF_TINWSF_IS_NO        | TINWSF_IS_NUMBER                           |
|                         |                                            |
| WSF_TINWSF_ST_CO        | TINWSF_ST_CODE                             |
|                         |                                            |
| WSF_STATE_ASGN_ID       | STATE_ASGN_ID                              |
|                         |                                            |
| WSF_NAME                | NAME                                       |
|                         |                                            |
| WSF_TYPE_CODE           | TYPE_CODE                                  |
|                         |                                            |
| WSF_ACTIVITY_DATE       | ACTIVITY_DATE                              |
|                         |                                            |
| WSF_WATER_TYP_CD        | WATER_TYPE_CODE                            |
|                         |                                            |
| WSF_AVAIL_CD            | AVAILABILITY_CODE                          |
|                         |                                            |
| WSF_EX_SYS_NO           | EXTERNAL_SYSTEM_NUMBER                     |
|                         |                                            |
|                         | From the Water system facility associated  |
|                         | to the Sample Schedule that is related to  |
|                         | the MCL Value that exceeds the MCL.        |
+-------------------------+--------------------------------------------+
| TMNVTYPE_IS_NUMBER      | TMNVTYPE_IS_NUMBER                         |
|                         |                                            |
| TMNVTYPE_ST_CODE        | TMNVTYPE_ST_CODE                           |
|                         |                                            |
| VIOLATION_TYPE_CD       | For ALRA Basis, value with \>01= if        |
|                         | NUMBER_OF_RESULTS_USED for the MCL Value   |
| VIOLATION_TYPE_NAM      | that exceeds the MCL is equal to 1; \>02=  |
|                         | otherwise.                                 |
| VIO_TYPE_SEV_LVL        |                                            |
|                         | For FANL Basis, value with Level Violation |
|                         | Type referenced by the FANL.               |
|                         |                                            |
|                         | NAME that corresponds to the Violation     |
|                         | Type Code                                  |
|                         |                                            |
|                         | SEVERITY_LEVEL that corresponds to the     |
|                         | Violation Type Code                        |
+-------------------------+--------------------------------------------+
| VIO_TO_FED_ANL_CD       | If Violation Type is associated to         |
|                         | Analyte, value with that Analyte Code. If  |
|                         | the Violation Type record is not           |
|                         | associated to an Analyte record, then the  |
|                         | Analyte Code for the analyte related to    |
|                         | the MCL Value that exceeds the MCL.        |
+-------------------------+--------------------------------------------+
| ANL_TSAANLYT_IS_NO      | TSAANLYT_IS_NUMBER                         |
|                         |                                            |
| ANL_TSAANLYT_ST_CO      | TSAANLYT_ST_CODE                           |
|                         |                                            |
| ANALYTE_CODE            | The Analyte Code for the analyte related   |
|                         | to the MCL Value that exceeds the MCL.     |
| ANALYTE_NAME            |                                            |
|                         | Use the name corresponding to the code to  |
|                         | value the name field.                      |
+-------------------------+--------------------------------------------+
| ANALYTE_GROUP_CODE      | Never valued for an MCL candidate          |
|                         | violation.                                 |
| ANALYTE_GROUP_NAME      |                                            |
+-------------------------+--------------------------------------------+
| MP_TMNMPRD_IS_NO        | TMNPRD_IS_NUMBER                           |
|                         |                                            |
| MP_TMNPRD_ST_CO         | TMNPRD_ST_CODE                             |
|                         |                                            |
| MP_CP_BEG_DAT           | BEGIN_DATE                                 |
|                         |                                            |
| MP_CP_END_DAT           | END_DATE                                   |
|                         |                                            |
| MP_MON_PER_DUR          | TYPE_CODE                                  |
|                         |                                            |
| MP_MON_PER_NAME         | NAME                                       |
|                         |                                            |
|                         | of the Monitoring Period referenced by the |
|                         | MCL Value that exceeds the MCL.            |
+-------------------------+--------------------------------------------+
| VIO_ANLYS_RSLT_TXT      | Set to CALCULATED_VALUE_TEXT (entity       |
|                         | CALCULATED_MCL_VALUE) from the MCL Value   |
|                         | that exceeds the MCL.                      |
+-------------------------+--------------------------------------------+
| VIO_ANLYS_RSLT_UOM      | Set to UOM_CODE (entity                    |
|                         | CALCULATED_MCL_VALUE) from the MCL Value   |
|                         | that exceeds the MCL.                      |
+-------------------------+--------------------------------------------+
| VIO_MCL_VIOLTD_TXT      | Set to CONTROL_LEVEL_TEXT (entity          |
|                         | FACILITY_ANALYTE_LEVEL) if the Averages    |
|                         | Basis recorded in the Monitoring           |
|                         | Requirement is "FANL." .                   |
|                         |                                            |
|                         | Otherwise, set to MEASURE_TEXT (table      |
|                         | TMNALRA) of MCL violated. None of the      |
|                         | records in ALRA (except TCR related ALRAs) |
|                         | have HQ. Leaving this in the code does not |
|                         | hurt but it is misleading. The only reason |
|                         | to record the MCL violated is when it is   |
|                         | different than the federal MCL. There is   |
|                         | no way in SDWIS/STATE to determine whether |
|                         | this is the case so it is best to always   |
|                         | value it.                                  |
+-------------------------+--------------------------------------------+
| VIO_MCL_VIOLTD_UOM      | Set to UOM_CODE (entity                    |
|                         | FACILITY_ANALYTE_LEVEL) if the Averages    |
|                         | Basis recorded in the Monitoring           |
|                         | Requirement is "FANL."                     |
|                         |                                            |
|                         | Otherwise, set to UOM_CODE (table TMNALRA) |
|                         | of MCL violated.                           |
+-------------------------+--------------------------------------------+
| VIO_MCL_NUM_RSLTS       | Set to NUMBER_OF_RESULTS_USED (entity      |
|                         | CALCULATED_MCL_VALUE) from the MCL Value   |
|                         | that exceeds the MCL.                      |
+-------------------------+--------------------------------------------+
| VIO_DATA_ORIGIN_CD      | Set to \>S or R= depending on whether the  |
|                         | Government Agency where Primacy Indicator  |
|                         | = Y has type of ST (set to S) or RG (set   |
|                         | to R)                                      |
+-------------------------+--------------------------------------------+
| VIO_ON_SKD_GRP_IND      | Blank                                      |
+-------------------------+--------------------------------------------+
| VTYPE_CATEGORY_CD       | Set to \>MCL= if Averages Basis recorded   |
|                         | in the Monitoring Requirement is "ALRA",   |
|                         | set to "TT" otherwise.                     |
+-------------------------+--------------------------------------------+

The following CDS CANDIDATE SCHEDULE record will be created and
associated to the CDS CANDIDATE VIOLATION:

+-----------------------+----------------------------------------------+
| **CDS CANDIDATE       |                                              |
| SCHEDULE**            |                                              |
+=======================+==============================================+
| **Field Name**        | **Value**                                    |
+-----------------------+----------------------------------------------+
| TCDSSASCH_IS_NUMBER   | Running Sequence number to be generated by   |
|                       | getting Max of sequence number and increment |
|                       | by 1.                                        |
+-----------------------+----------------------------------------------+
| TCDSSASCH_ST_CODE     | State Code of the state obtained from the    |
|                       | TINPRT table.                                |
+-----------------------+----------------------------------------------+
| PWS_TINWSYS_IS_NO     | TINWSYS_IS_NUMBER                            |
|                       |                                              |
| PWS_TINWSYS_ST_CO     | TINWSYS_ST_CODE                              |
|                       |                                              |
| PWS_NUMBER            | NUMBER                                       |
|                       |                                              |
| PWS_NAME              | NAME                                         |
|                       |                                              |
| PWS_FED_PRI_          | FED_PRIMARY_SOURCE_CODEFEDERAL_TYPE_CODE     |
| SRC_CDPWS_FED_TYPE_CD |                                              |
|                       | D_POPULATION_COUNT                           |
| PWS_POPULATION_CNT    |                                              |
|                       | ACTIVITY_STATUS_CODE                         |
| PWS_ACTIV_STAT_CD     |                                              |
|                       | ACTIVITY_DATE                                |
| PWS_ACTIVITY_DATE     |                                              |
|                       | From the TINWSYS that is related to the MCL  |
|                       | Value that exceeds the MCL.                  |
+-----------------------+----------------------------------------------+
| WSF_TINWSF_IS_NO      | TINWSF_IS_NUMBER                             |
|                       |                                              |
| WSF_TINWSF_ST_CO      | TINWSF_ST_CODE                               |
|                       |                                              |
| WSF_STATE_ASGN_ID     | STATE_ASGN_ID                                |
|                       |                                              |
| WSF_NAME              | NAME                                         |
|                       |                                              |
| WSF_TYPE_CODE         | TYPE_CODE                                    |
|                       |                                              |
| WSF_ACTIVITY_DATE     | ACTIVITY_DATE                                |
|                       |                                              |
| WSF_WATER_TYP_CD      | WATER_TYPE_CODE                              |
|                       |                                              |
| WSF_AVAIL_CD          | AVAILABILITY_CODE                            |
|                       |                                              |
| WSF_EX_SYS_NO         | EXTERNAL_SYSTEM_NUMBER                       |
|                       |                                              |
|                       | From the Water system facility associated to |
|                       | the Sample Schedule that is related to the   |
|                       | MCL Value that exceeds the MCL.              |
+-----------------------+----------------------------------------------+
| SCH_TMNSASCH_IS_NO    | TMNSASCH_IS_NUMBER                           |
|                       |                                              |
| SCH_TMNSASCH_ST_CO    | TMNSASCH_ST_CODE                             |
|                       |                                              |
| SCH_BEGIN_DATE        | BEGIN_DATE                                   |
|                       |                                              |
| SCH_END_DATE          | END_DATE                                     |
|                       |                                              |
| SCH_SEAS_PRD_BEG      | START_DAY + "/" +START_MONTH                 |
|                       |                                              |
| SCH_SEAS_PRD_END      | END_DAY + "/" + END_MONTH                    |
|                       |                                              |
| SCH_ST_SEA_PRD_BEG    | STATE_START_DAY + "/" +STATE_START_MONTH     |
|                       |                                              |
| SCH_ST_SEA_PRD_END    | STATE_END_DAY + "/" +STATE_END_MONTH         |
|                       |                                              |
| SCH_STATE_YEAR        | STATE_YEAR                                   |
|                       |                                              |
| SCH_REASON_TEXT       | REASON_TEXT                                  |
|                       |                                              |
| SCH_INIT_MP_BEG_DT    | INITIAL_MP_BEGIN_DAT                         |
|                       |                                              |
|                       | from the Sample_Schedule that is related to  |
|                       | the MCL Value that exceeds the MCL.          |
+-----------------------+----------------------------------------------+
| MNR_TMNMNR_IS_NO      | TMNMNR_IS_NUMBER                             |
|                       |                                              |
| MNR_TMNMNR_ST         | TMNMNR_ST_CODESAMPLE_TYPE_CODE               |
| _COMNR_SAMPLE_TYPE_CD |                                              |
|                       | SAMPLE_COUNT                                 |
| MNR_SAMPLE_COUNT      |                                              |
|                       | SAMPLE_COUNT_UNIT_CODE                       |
| MNR_SMPL_CNT_UOM      |                                              |
|                       | of the monitoring requirement that results   |
|                       | in the sample schedule that is related to    |
|                       | the MCL Value that exceeds the MCL.          |
+-----------------------+----------------------------------------------+
| ANL_TSAANLYT_IS_NO    | TSAANLYT_IS_NUMBER                           |
|                       |                                              |
| ANL_TSAANLYT_ST_CO    | TSAANLYT_ST_CODE                             |
|                       |                                              |
| ANALYTE_CODE          | The Analyte Code for the analyte related to  |
|                       | the Sample Schedule (whether an individual   |
| ANALYTE_NAME          | or hidden schedule) through the Monitoring   |
|                       | Requirement.                                 |
|                       |                                              |
|                       | Name corresponding to Analyte Code           |
+-----------------------+----------------------------------------------+
| ANALYTE_GROUP_CODE    | Never valued for a MCL candidate violation.  |
|                       |                                              |
| ANALYTE_GROUP_NAME    |                                              |
+-----------------------+----------------------------------------------+

The following CDS SAMPLE RESULT record(s) will be created and associated
to the CDS Candidate MCL violation with values from Sample Analytical
Results associated to the MCL Value that exceeds the MCL:

+-----------------------+----------------------------------------------+
| **CDS SAMPLE RESULT** |                                              |
+=======================+==============================================+
| **Field Name**        | **Value**                                    |
+-----------------------+----------------------------------------------+
| TCDSSAR_IS_NUMBER     | Running Sequence number to be generated by   |
|                       | getting Max of sequence number and increment |
|                       | by 1.                                        |
+-----------------------+----------------------------------------------+
| TCDSSAR_ST_CODE       | State Code of the state obtained from the    |
|                       | TINPRT table.                                |
+-----------------------+----------------------------------------------+
| PWS_TINWSYS_IS_NO     | TINWSYS_IS_NUMBER                            |
|                       |                                              |
| PWS_TINWSYS_ST_CO     | TINWSYS_ST_CODE                              |
|                       |                                              |
| PWS_NUMBER            | NUMBER                                       |
|                       |                                              |
| PWS_NAME              | NAME                                         |
|                       |                                              |
| PWS_FED_PRI_SRC_CD    | FED_PRIMARY_SOURCE_CODE                      |
|                       |                                              |
| PWS_FED_TYPE_CD       | FEDERAL_TYPE_CODE                            |
|                       |                                              |
| PWS_POPULATION        | D_POPULATION_COUNTACTIVITY_STATUS_CODE       |
| _CNTPWS_ACTIV_STAT_CD |                                              |
|                       | ACTIVITY_DATE                                |
| PWS_ACTIVITY_DATE     |                                              |
|                       | from the TINWSYS where the sample/sample     |
|                       | analytical result applies.                   |
+-----------------------+----------------------------------------------+
| WSF_TINWSF_IS_NO,     | TINWSF_IS_NUMBER                             |
|                       |                                              |
| WSF_TINWSF_ST_CO      | TINWSF_ST_CODE                               |
|                       |                                              |
| WSF_ACTIVITY_DATE     | STATE_ASGN_ID                                |
|                       |                                              |
| WSF_AVAIL_CD          | NAME                                         |
|                       |                                              |
| WSF_NAME,             | TYPE_CODE                                    |
|                       |                                              |
| WSF_STATE_ASGN_ID,    | ACTIVITY_DATE                                |
|                       |                                              |
| WSF_EX_SYS_NO         | WATER_TYPE_CODE                              |
|                       |                                              |
| WSF_TYPE_CODE         | AVAILABILITY_CODE                            |
|                       |                                              |
| WSF_WATER_TYP_CD      | EXTERNAL_SYSTEM_NUMBER                       |
|                       |                                              |
| WSF_ACTIV_STAT_CD     | ACTIVITY_STATUS_CODE                         |
|                       |                                              |
|                       | from the Water System Facility, which is the |
|                       | location for the sampling point, which is    |
|                       | the sample site for the sample/sample        |
|                       | analytical result.                           |
+-----------------------+----------------------------------------------+
| SP_TSASMPPT_IS_NO     | TSASMPPT_IS_NUMBER                           |
|                       |                                              |
| SP_TSASMPPT_ST_CO     | TSASMPPT_ST_CODE                             |
|                       |                                              |
| SP_IDENT_CD           | IDENTIFICATION_CODE                          |
|                       |                                              |
| SP_DESCRIPTION        | DESCRIPTION                                  |
|                       |                                              |
| SP_TYPE_CODE          | TYPE_CODE                                    |
|                       |                                              |
| SP_SOURCE_TYPE_CD     | SOURCE_TYPE_CODE                             |
|                       |                                              |
| SP_POST_DISINF_CD     | POST_DISINFECTION_INDICATOR                  |
|                       |                                              |
| SP_PROC_PHASE_CD      | PROCESS_PHASE_INDICATOR_CODE                 |
|                       |                                              |
| SP_ACTIV_STAT_CD      | ACTIVITY_STATUS_CODE                         |
|                       |                                              |
| SP_STATUS_DATE        | ACTIVITY_DATE                                |
|                       |                                              |
|                       | from the sampling point which is the sample  |
|                       | site for the sample/sample analytical        |
|                       | result.                                      |
+-----------------------+----------------------------------------------+
| SAR_TSASAR_IS_NO      | TSASAR_IS_NUMBER                             |
|                       |                                              |
| SAR_TSASAR_ST_CO      | TSASAR_ST_CODE                               |
|                       |                                              |
| SAR_DATA_QUAL_CD      | DATA_QUALITY_CODE                            |
|                       |                                              |
| SAR_DETECT_LIM_       | DE                                           |
| NUMSAR_DETECT_LIM_UOM | TECTION_LIMIT_NUMBERDETECTION_LIMIT_UOM_CODE |
|                       |                                              |
| SAR_LESS_THAN_IND     | LESS_THAN_INDICATOR                          |
|                       |                                              |
| SAR_LESS_THAN_CD      | LESS_THAN_CODE                               |
|                       |                                              |
| SAR_REPORTED_MSR      | REPORTED_MEASURE                             |
|                       |                                              |
| SAR_RAD_COUNT_ERR     | RAD_COUNTING_ERROR                           |
|                       |                                              |
| SAR_UOM_CD            | UOM_CODE                                     |
|                       |                                              |
| SAR_CONCENTRAT_MSR    | CONCENTRATION_MEASURE                        |
|                       |                                              |
|                       | from the sample analytical result.           |
+-----------------------+----------------------------------------------+
| TSASAMPL_IS_NUMBER    | TSASAMPL_IS_NUMBER                           |
|                       |                                              |
| TSASAMPL_ST_CODE      | TSASAMPL_ST_CODE                             |
|                       |                                              |
| SMP_LAB_ASGN_ID       | LAB_SAMPLE_ASGN_ID                           |
|                       |                                              |
| SMP_ST_ASGN_ID        | STATE_SAMPLE_ASGN_ID                         |
|                       |                                              |
| SMP_SAMPLE_TYPE_CD    | SAMPLE_TYPE_CODE                             |
|                       |                                              |
| SMP_COLLECTION_DAT    | COLLECTION_END_DATE                          |
|                       |                                              |
| SMP_COLLECTION_TIM    | COLLECTION_END_TIME                          |
|                       |                                              |
| SMP_COMPL_PURP_IND    | COMPLIANCE_PURPOSE_IND_CODE                  |
|                       |                                              |
| SMP_INFORML_CLCTR     | INFORMAL_COLLECTOR                           |
|                       |                                              |
| SMP_REPLACE_IND       | REPLACEMENT_INDICATOR_CODE                   |
|                       |                                              |
|                       | from the SBS Sample associated to the SAR.   |
+-----------------------+----------------------------------------------+
| ANL_TSAANLYT_IS_NO    | TSAANLYT_IS_NUMBER                           |
|                       |                                              |
| ANL_TSAANLYT_ST_CO    | TSAANLYT_ST_CODE                             |
|                       |                                              |
| ANALYTE_CODE          | CODE                                         |
|                       |                                              |
| ANALYTE_NAME          | NAME                                         |
|                       |                                              |
|                       | code and name of the analyte associated to   |
|                       | the SAR.                                     |
+-----------------------+----------------------------------------------+
| MP_TMNMPRD_IS_NO      | TMNPRD_IS_NUMBER                             |
|                       |                                              |
| MP_TMNMPRD_ST_CO      | TMNPRD_ST_CODE                               |
|                       |                                              |
| MP_BEGIN_DATE         | BEGIN_DATE                                   |
|                       |                                              |
| MP_END_DATE           | END_DATE                                     |
|                       |                                              |
| MP_DURATION           | TYPE_CODE                                    |
|                       |                                              |
| MP_NAME               | NAME                                         |
|                       |                                              |
|                       | of the Monitoring Period associated to the   |
|                       | SAR.                                         |
+-----------------------+----------------------------------------------+
| LAB_TSALAB_IS_NO      | TSALAB_IS_NUMBER                             |
|                       |                                              |
| LAB_TSALAB_ST_CO      | TSALAB_ST_CODE                               |
|                       |                                              |
| LAB_NAME              | NAME                                         |
|                       |                                              |
| LAB_ST_ASSIGNED_ID    | STATE_ASSIGNED_ID_NUMBER                     |
|                       |                                              |
| LAB_FEDERAL_ID_NO     | FEDERAL_ID_NUMBER                            |
|                       |                                              |
|                       | of the LAB that is associated to the SAR.    |
+-----------------------+----------------------------------------------+

This page is intentionally left blank.