**APPENDIX D-12**Number range CHAPTER

**Create/Update Chlorine/Chloramine Summaries Process**

This page is intentionally left blank

**D-12.** Number range CHAPTER**Create/Update Chlorine/Chloramine
Summaries Process**

This process consists of four subprocesses:

-   Set FANL CDS trigger timestamps due to TCR Schedule Additions or
    Modifications

-   Create/Modify Chlorine/Chloramine Summaries due to FANL Additions or
    Modifications

-   Create/Modify Chlorine/Chloramine Summaries due to Field Result
    Additions or Modifications

-   Calculate MRDL RAA, Assess DSRD or SDRD Compliance due to MRDL, DSRD
    or SDRD Additions or Modifications

*Subprocess A. Set FANL CDS trigger timestamps due to TCR Schedule
Additions or Modifications*

Check-Point Restart Issues: This process will be driven by changes to
TCR Schedules (TMNSASCH).

> The record key for this subprocess is the composite of CDS trigger
> timestamp and the TMNSASCH_IS_NUMBER columns.
>
> The sort order for this subprocess will be CDS trigger timestamp
> ascending, TMNSASCH_IS_NUMBER ascending.

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

2.  Identify Sample Schedules where:

    a.  its CDS trigger timestamp is greater than the last run date &
        time for the CDS Setup process AND

    b.  is for '3100' analyte AND

    c.  whose Monitoring Requirement Sample Type is either Routine (RT)
        or Temporary Routine (TR) AND

    d.  whose end date is either open or, if valued, is greater than the
        CDS History Date

3.  For each Sample Schedule identified, read FANLs where:

    a.  the Type Code is MRDL and Control Level Type is MAX or FANL Type
        Code is DSRD or SDRD and Control Level Type is MIN AND

    b.  whose effective end date is either open or, if valued, is
        greater than or equal to the Sample Schedule's Begin Date AND

    c.  whose Water System through its parent Water System Facility is
        the same Water System as the TCR Sample Schedule.

4.  For each FANL identified, set its CDS trigger timestamp to CURRENT
    TIMESTAMP.

*Subprocess B. Create/Modify Chlorine/Chloramine Summaries due to FANL
Additions or Modifications*

Check-Point Restart Issues: This process will be driven by changes to
FANLs (TMNFANL).

> The record key for this subprocess is the composite of CDS trigger
> timestamp and the TMNFANL_IS_NUMBER columns.
>
> The sort order for this subprocess will be CDS trigger timestamp
> ascending, TMNFANL_IS_NUMBER ascending.

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

2.  Identify FANLs where:

    a.  its CDS trigger timestamp is greater than the last run date &
        time for the CDS Setup process AND

    b.  the Type Code is MRDL and Control Level Type is MAX or FANL Type
        Code is DSRD or SDRD and Control Level Type is MIN AND

    c.  whose effective end date is either open or, if valued, is
        greater than or equal to the Sample Schedule's Begin Date

3.  For each FANL retrieved, determine if associated MDBP Summaries are
    still appropriate for the FANL (check for overlap between MDBP
    Summary's associated monitoring period and FANL effective dates).

    a.  Read each MDBP Summary and its Monitoring Period that is
        associated to the FANL

        i.  If FANL Effective Begin Date is less than or equal to the
            Monitoring Period Begin Date and if FANL Effective End Date
            is valued, is greater than or equal to the Monitoring Period
            End Date AND FANL Summary Type Code is equal to MDBP Summary
            Type Code, then MDBP is still appropriate for the FANL
            therefore keep it.

        ii. Otherwise, if MDBP Summary was not entered or modified by a
            user (Userid Code is equal to 'CDSSETUP'), delete the MDBP
            Summary. If an association to a Violation prevents the
            deletion, create an advisory that says "An MDBP Summary
            exists for a Monitoring Period in which the FANL is not
            effective" -- Scott, please reword as appropriate and update
            Section 9 to include this new message in TCDSPMSG -
            referencing message number XX and record the water system
            number, name, tinwsys_is_number, and tinwsys_st_code of the
            water system record; the state_asgn_id and name of the water
            system facility record; the analyte for the FANL; and the
            begin and end dates for the monitoring period, etc.
            (A.D-12.a)

        iii. After deleting the MDBP Summary:

             1.  If the MDBP Summary is a MRDL, update the CDS trigger
                 timestamp of MRDL Summaries whose MPA_APP_BEGIN_DATE is
                 greater than this MRDL's MP End Date and is less than
                 or equal to this MRDL's MP Begin Date + 1 year -- 1 day
                 (to trigger RAA recalculation and compliance
                 determination).

             2.  If the MDBP Summary is a DSRD or SDRD, update the CDS
                 trigger timestamp of the MDBP Summary for the next
                 monitoring period (to trigger compliance
                 determination).

4.  Read each Sample Schedule where:

    a.  is for '3100' analyte AND

    b.  whose Water System is the same Water System as the FANL through
        its parent Water System Facility AND

    c.  whose Monitoring Requirement Sample Type is either Routine (RT)
        or Temporary Routine (TR)

    d.  whose end date is either open or, if valued, is greater or equal
        to the FANL's Effective Begin Date

```{=html}
<!-- -->
```
5.  Read each Monitoring Period (that overlaps both FANL and TCR Sample
    Schedule effective dates), i.e.:

    a.  its Type Code is equal to the TCR Sample Schedule's monitoring
        periodicity (associated Monitoring Requirement's Sample Count
        Unit Code) AND

    b.  its Begin Date is greater or equal to both the Sample Schedule
        Begin Date and FANL's Effective Begin Date

    c.  its End Date is less or equal to both the Sample Schedule End
        Date, if valued, and FANL's Effective End Date, if valued.

6.  Read MDBP Summary associated to the FANL and for each Monitoring
    Period retrieved.

    a.  If a record is found and its Userid Code is not equal to
        'CDSSETUP', create an advisory referencing message number 55 and
        record the water system number, name, tinwsys_is_number, and
        tinwsys_st_code of the water system record; the state_asgn_id
        and name of the water system facility record; the analyte for
        the FANL; and the begin and end dates for the monitoring period.
        Otherwise, modify the MDBP Summary depending on its Type Code as
        follows:

+------------------+----------------+---------------+----------------+
| **Field**        | **MRDL         | **DSRD        | **SDRD         |
|                  | Summary**      | Summary**     | Summary**      |
+==================+================+===============+================+
| TYPE_CODE_CV     | MRDL           | DSRD          | SDRD           |
+------------------+----------------+---------------+----------------+
| REPORTED_DATE    | If creating    | If creating   | If creating    |
|                  | the MRDL,      | the DSRD,     | the SDRD,      |
|                  | null.          | null.         | null.          |
|                  |                |               |                |
|                  | If             | If            | If             |
|                  | recalculating  | recalculating | recalculating  |
|                  | an existing    | an existing   | an existing    |
|                  | MRDL, leave as | MRDL, leave   | MRDL, leave as |
|                  | is.            | as is.        | is.            |
+------------------+----------------+---------------+----------------+
| SAMPLES_REQUIRED | Number of      |               | The "Days to   |
|                  | total coliform |               | monitoring per |
|                  | samples        |               | month" x the   |
|                  | indicated by   |               | "Samples       |
|                  | the TCR        |               | required per   |
|                  | rou            |               | day" x actual  |
|                  | tine/temporary |               | number of      |
|                  | routine        |               | months in the  |
|                  | schedule in    |               | monitoring     |
|                  | effect on the  |               | period (i.e. 3 |
|                  | Monitoring     |               | for QT, 12 for |
|                  | Period begin   |               | YR, etc)       |
|                  | date plus the  |               | indicated in   |
|                  | "Days to       |               | the FANL (MIN  |
|                  | monitoring per |               | for 0999 or    |
|                  | month" x the   |               | 1006)          |
|                  | "Samples       |               |                |
|                  | required per   |               |                |
|                  | day" x actual  |               |                |
|                  | number of      |               |                |
|                  | months in the  |               |                |
|                  | monitoring     |               |                |
|                  | period (i.e. 3 |               |                |
|                  | for QT, 12 for |               |                |
|                  | YR, etc)       |               |                |
|                  | indicated in   |               |                |
|                  | the FANL (MAX  |               |                |
|                  | for 0999 or    |               |                |
|                  | 1006)          |               |                |
+------------------+----------------+---------------+----------------+
| S                | Number of      |               |                |
| AMPLES_COLLECTED | Field Result   |               |                |
|                  | records with   |               |                |
|                  | either a field |               |                |
|                  | free or total  |               |                |
|                  | chlorine       |               |                |
|                  | result or with |               |                |
|                  | both[^1]       |               |                |
|                  | collected      |               |                |
|                  | during the     |               |                |
|                  | monitoring     |               |                |
|                  | period         |               |                |
|                  | (ff            |               |                |
|                  | _chlor_res_msr |               |                |
|                  | is not null    |               |                |
|                  | and            |               |                |
|                  | fldt           |               |                |
|                  | ot_chl_res_msr |               |                |
|                  | is not null),  |               |                |
|                  | whose parent   |               |                |
|                  | sample is a    |               |                |
|                  | routine (type  |               |                |
|                  | code is RT or  |               |                |
|                  | TR), is not    |               |                |
|                  | rejected       |               |                |
|                  | (re            |               |                |
|                  | ject_reason_cd |               |                |
|                  | = ' ') and is  |               |                |
|                  | "for           |               |                |
|                  | compliance"    |               |                |
|                  | (com           |               |                |
|                  | pl_purp_ind_cd |               |                |
|                  | = Y).          |               |                |
+------------------+----------------+---------------+----------------+
| M                | "YES" if       |               |                |
| R_COMPLIANCE_IND | Samples        |               |                |
|                  | Col            |               |                |
|                  | lected/Samples |               |                |
|                  | Required       |               |                |
|                  | \>=1.0         |               |                |
|                  |                |               |                |
|                  | "NMN" if       |               |                |
|                  | Samples        |               |                |
|                  | Col            |               |                |
|                  | lected/Samples |               |                |
|                  | Required       |               |                |
|                  | \>=0.90 and    |               |                |
|                  | \<1.0          |               |                |
|                  |                |               |                |
|                  | "NMJ" if       |               |                |
|                  | Samples        |               |                |
|                  | Col            |               |                |
|                  | lected/Samples |               |                |
|                  | Required       |               |                |
|                  | \<0.90         |               |                |
+------------------+----------------+---------------+----------------+
| SM               | Null           | Number of     |                |
| PLS_BYND_MEA_LVL |                | Field Results |                |
|                  |                | \< FANL-MIN   |                |
+------------------+----------------+---------------+----------------+
| PR               | Null           | (Number of    |                |
| CNT_BYND_MEA_LVL |                | Field Result  |                |
|                  |                | \< FANL-MIN   |                |
|                  |                | divided by    |                |
|                  |                | Samples       |                |
|                  |                | Collected)    |                |
|                  |                | times 100     |                |
|                  |                | rounded to    |                |
|                  |                | one digit     |                |
|                  |                | (e.g., 5.6%)  |                |
+------------------+----------------+---------------+----------------+
| PR               | Null           | Prcnt         |                |
| CNT_BYND_MEA_TXT |                | _Bynd_Mea_Lvl |                |
|                  |                | converted to  |                |
|                  |                | text rounded  |                |
|                  |                | to one digit  |                |
|                  |                | (e.g., 5.6,   |                |
|                  |                | 4.0) and      |                |
|                  |                | trimmed (so   |                |
|                  |                | there are no  |                |
|                  |                | spaces in     |                |
|                  |                | front of or   |                |
|                  |                | behind the    |                |
|                  |                | number)       |                |
+------------------+----------------+---------------+----------------+
| HIGHEST_MSR      | Null           |               |                |
+------------------+----------------+---------------+----------------+
| HIGHEST_MSR_TXT  | Null           |               |                |
+------------------+----------------+---------------+----------------+
| MON_PER_AVG_MSR  | Sum of Field   | Null          |                |
|                  | Results for    |               |                |
|                  | the Monitoring |               |                |
|                  | Period divided |               |                |
|                  | by Sample      |               |                |
|                  | Collected and  |               |                |
|                  | rounded to the |               |                |
|                  | significant    |               |                |
|                  | digits of the  |               |                |
|                  | result with    |               |                |
|                  | fewest         |               |                |
|                  | significant    |               |                |
|                  | digits.        |               |                |
|                  |                |               |                |
|                  | If there is no |               |                |
|                  | Field Result   |               |                |
|                  | for the        |               |                |
|                  | Monitoring     |               |                |
|                  | Period, set to |               |                |
|                  | zero.          |               |                |
+------------------+----------------+---------------+----------------+
| MO               | M              | Null          |                |
| N_PER_AVG_MSR_TX | on_Per_Avg_Msr |               |                |
|                  | converted to a |               |                |
|                  | text, rounded  |               |                |
|                  | to the         |               |                |
|                  | significant    |               |                |
|                  | digits of the  |               |                |
|                  | result with    |               |                |
|                  | fewest         |               |                |
|                  | significant    |               |                |
|                  | digits and     |               |                |
|                  | trimmed        |               |                |
+------------------+----------------+---------------+----------------+
| MPA_UOM_CODE     | MG/L           | Null          |                |
+------------------+----------------+---------------+----------------+
| RUN_ANL_AVG_MSR  | Sum of Mon Per | Null          |                |
|                  | Avg Msr for    |               |                |
|                  | this           |               |                |
|                  | Monitoring     |               |                |
|                  | Period and     |               |                |
|                  | other MRDL     |               |                |
|                  | Summaries for  |               |                |
|                  | same WS and    |               |                |
|                  | WSF where      |               |                |
|                  | Monitoring     |               |                |
|                  | Period Begin   |               |                |
|                  | Date is        |               |                |
|                  | between this   |               |                |
|                  | Summary's MP   |               |                |
|                  | End Date and   |               |                |
|                  | One Year less  |               |                |
|                  | one day before |               |                |
|                  | it divided by  |               |                |
|                  | the sum of the |               |                |
|                  | mpa_total_days |               |                |
|                  | and rounded to |               |                |
|                  | the same       |               |                |
|                  | significant    |               |                |
|                  | digits as the  |               |                |
|                  | FANL-MAX.[^2]  |               |                |
+------------------+----------------+---------------+----------------+
| RU               | R              | Null          |                |
| N_ANL_AVG_MSR_TX | UN_ANL_AVG_MSR |               |                |
|                  | converted to a |               |                |
|                  | text, rounded  |               |                |
|                  | to the         |               |                |
|                  | significant    |               |                |
|                  | digits as the  |               |                |
|                  | FANL-MAX and   |               |                |
|                  | trimmed        |               |                |
+------------------+----------------+---------------+----------------+
| RAA_UOM_CODE     | MG/L           | Null          |                |
+------------------+----------------+---------------+----------------+
| MPA_APP_BEG_DAT  | Latest date    | Null          |                |
|                  | from the TCR   |               |                |
|                  | Sample         |               |                |
|                  | Schedule Begin |               |                |
|                  | Date, AOP and  |               |                |
|                  | Monitoring     |               |                |
|                  | Period         |               |                |
|                  | associated to  |               |                |
|                  | the Summary    |               |                |
|                  | (even for MRDL |               |                |
|                  | whose Userid   |               |                |
|                  | Code is not    |               |                |
|                  | CDSSETUP)      |               |                |
|                  | (A.D-12.b)     |               |                |
+------------------+----------------+---------------+----------------+
| MPA_APP_END_DAT  | Earliest date  | Null          |                |
|                  | from the TCR   |               |                |
|                  | Sample         |               |                |
|                  | Schedule End   |               |                |
|                  | Date (if any), |               |                |
|                  | AOP and        |               |                |
|                  | Monitoring     |               |                |
|                  | Period         |               |                |
|                  | associated to  |               |                |
|                  | the Summary    |               |                |
|                  | (even for MRDL |               |                |
|                  | whose Userid   |               |                |
|                  | Code is not    |               |                |
|                  | CDSSETUP)      |               |                |
|                  | (A.D-12.c)     |               |                |
+------------------+----------------+---------------+----------------+
| MPA_TOTAL_DAYS   | If there is no | Null          |                |
|                  | Field Result   |               |                |
|                  | for the        |               |                |
|                  | Monitoring     |               |                |
|                  | Period, set to |               |                |
|                  | zero.          |               |                |
|                  |                |               |                |
|                  | Otherwise      |               |                |
|                  | number of days |               |                |
|                  | from           |               |                |
|                  | M              |               |                |
|                  | PA_APP_BEG_DAT |               |                |
|                  | to             |               |                |
|                  | M              |               |                |
|                  | PA_APP_END_DAT |               |                |
|                  | (even for MRDL |               |                |
|                  | whose Userid   |               |                |
|                  | Code is not    |               |                |
|                  | CDSSETUP)      |               |                |
|                  | (A.D-12.d)     |               |                |
+------------------+----------------+---------------+----------------+
| D_I              | If creating    |               |                |
| NITIAL_TIMESTAMP | the Summary,   |               |                |
|                  | the current    |               |                |
|                  | date and time. |               |                |
|                  |                |               |                |
|                  | Do not change  |               |                |
|                  | for an         |               |                |
|                  | existing       |               |                |
|                  | record.        |               |                |
+------------------+----------------+---------------+----------------+
| D                | If creating,   |               |                |
| _INITIAL_USER_CD | set to         |               |                |
|                  | "CDSSETUP"     |               |                |
+------------------+----------------+---------------+----------------+
| D_LAST_UPDT_TS   | The current    |               |                |
|                  | date and time. |               |                |
+------------------+----------------+---------------+----------------+
| D_USERID_CODE    | If the record  | If the record |                |
|                  | is created or  | is created or |                |
|                  | modified by    | modified by   |                |
|                  | CDS Setup, set | CDS Setup,    |                |
|                  | to "CDSSETUP"  | set to        |                |
|                  |                | "CDSSETUP"    |                |
|                  | Note that even |               |                |
|                  | if not equal   |               |                |
|                  | to CDS Setup,  |               |                |
|                  | CDS Setup can  |               |                |
|                  | update the RAA |               |                |
|                  | fields for a   |               |                |
|                  | MRDL Summary.  |               |                |
+------------------+----------------+---------------+----------------+

b.  If a record is not found, create an MDBP Summary and value the
    fields as detailed above.

    i.  If the MDBP Summary is a MRDL, update the CDS trigger timestamp
        of MRDL Summaries whose MPA_APP_BEGIN_DATE is greater than this
        MRDL's MP End Date and is less than or equal to this MRDL's MP
        Begin Date + 1 year -- 1 day (to trigger RAA recalculation and
        compliance determination).

    ii. If the MDBP Summary is a DSRD or SDRD, update the CDS trigger
        timestamp of the MDBP Summary for the next monitoring period (to
        trigger compliance determination).

```{=html}
<!-- -->
```
7.  If an advisory with message number 55 was generated for any MDBP
    Summary for the FANL, create an advisory referencing message number
    56 and record the water system number, name, 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 for the
    FANL.

*Subprocess C. Create/Modify Chlorine/Chloramine Summaries due to Field
Result Additions or Modifications*

Check-Point Restart Issues: This process will be driven by changes to
Field Results (TSAMCSMP).

> The record key for this subprocess is the composite of CDS trigger
> timestamp and the TSASAMPL_IS_NUMBER (of the Field Result's parent
> Sample) columns.
>
> The sort order for this subprocess will be CDS trigger timestamp
> ascending, TSASAMPL_IS_NUMBER ascending.

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

2.  Identify Field Results (TSAMCSMP) where:

    a.  its CDS trigger timestamp is greater than the last run date &
        time for the CDS Setup process AND

    b.  the associated parent Sample is routine (Type Code = 'RT')

3.  Identify FANLs where:

    a.  the Type Code is MRDL and Control Level Type is MAX or FANL Type
        Code is DSRD or SDRD and Control Level Type is MIN AND

    b.  

    c.  (A.D-12.e)

    d.  whose effective begin date is less than or equal to the Field
        Result's parent Sample's Collection End Date

    e.  whose effective end date is either open or, if valued, is
        greater than or equal to the Field Result's parent Sample's
        Collection End Date

4.  If a Monitoring Period is associated to the Field Result (through
    the Sample Analytical Result whose Analyte = 3100 associated to the
    parent Sample), use it to check for an existing MDBP Summary.

    a.  Read MDBP Summary associated to the current FANL and Monitoring
        Period.

    b.  If a record is found and its Userid Code is not equal to
        'CDSSETUP', create an advisory referencing message number 55 and
        record the water system number, name, tinwsys_is_number, and
        tinwsys_st_code of the water system record; the state_asgn_id
        and name of the water system facility record; the analyte for
        the FANL; and the begin and end dates for the monitoring period.
        Otherwise, modify the MDBP Summary depending on its Type Code as
        detailed in Subprocess B above.

    c.  If a record is not found, create an MDBP Summary and value the
        fields as detailed in Subprocess B above.

        i.  If the MDBP Summary is a MRDL, update the CDS trigger
            timestamp of MRDL Summaries whose MPA_APP_BEGIN_DATE is
            greater than this MRDL's MP End Date and is less than or
            equal to this MRDL's MP Begin Date + 1 year -- 1 day (to
            trigger RAA recalculation and compliance determination).

        ii. If the MDBP Summary is a DSRD or SDRD, update the CDS
            trigger timestamp of the MDBP Summary for the next
            monitoring period (to trigger compliance determination).

5.  If a Monitoring Period is not associated to the Field Result, use
    the collection end date of the parent sample and the TCR Sample
    Schedule in effect to determine the Monitoring Period to use to
    either search for an existing MDBP Summary or to create one, if one
    is not already existing.

    a.  Read Sample Schedule where:

        i.  is for '3100' analyte AND

        ii. whose Water System is the same Water System as the FANL
            through its parent Water System Facility AND

        iii. whose Monitoring Requirement Sample Type is either Routine
             (RT) or Temporary Routine (TR)

        iv. whose begin date is less or equal to the Field Result's
            collection end date

        v.  whose end date is either open or, if valued, is greater or
            equal to the Field Result's collection end date

    ```{=html}
    <!-- -->
    ```
    a.  If a Sample Schedule is found, read for a Monitoring Period with
        the same periodicity as the Sample Schedule (MN, QT, YR) and use
        it to check for an existing MDBP Summary.

        i.  Read MDBP Summary associated to the current FANL and
            Monitoring Period.

        ii. If a record is found and its Userid Code is not equal to
            'CDSSETUP', create an advisory referencing message number 55
            and record the water system number, name, tinwsys_is_number,
            and tinwsys_st_code of the water system record; the
            state_asgn_id and name of the water system facility record;
            the analyte for the FANL; and the begin and end dates for
            the monitoring period. Otherwise, modify the MDBP Summary
            depending on its Type Code as detailed in Subprocess B
            above.

            1.  If the MDBP Summary is a MRDL, update the CDS trigger
                timestamp of MRDL Summaries whose MPA_APP_BEGIN_DATE is
                greater than this MRDL's MP End Date and is less than or
                equal to this MRDL's MP Begin Date + 1 year -- 1 day (to
                trigger RAA recalculation and compliance determination).

            2.  If the MDBP Summary is a DSRD or SDRD, update the CDS
                trigger timestamp of the MDBP Summary for the next
                monitoring period (to trigger compliance determination).

        iii. If a record is not found, create an MDBP Summary and value
             the fields as detailed in Subprocess B above.

             1.  If the MDBP Summary is a MRDL, update the CDS trigger
                 timestamp of MRDL Summaries whose MPA_APP_BEGIN_DATE is
                 greater than this MRDL's MP End Date and is less than
                 or equal to this MRDL's MP Begin Date + 1 year -- 1 day
                 (to trigger RAA recalculation and compliance
                 determination).

             2.  If the MDBP Summary is a DSRD or SDRD, update the CDS
                 trigger timestamp of the MDBP Summary for the next
                 monitoring period (to trigger compliance
                 determination).

    b.  If a Sample Schedule is not in effect during the sample
        collection end date, then look for a quarterly Sample Schedule
        with a begin date that is 2 months or less than the Field
        Result's collection end date. If still not found, then look for
        a yearly Sample Schedule with a begin date that is 10 months or
        less than the Field Result's collections end date. If a Sample
        Schedule is found,

        i.  Read MDBP Summary associated to the current FANL and a
            monthly Monitoring Period whose begin date and end dates
            overlap the Sample collection end date.

        ii. If a record is found and its Userid Code is not equal to
            'CDSSETUP', create an advisory referencing message number 55
            and record the water system number, name, tinwsys_is_number,
            and tinwsys_st_code of the water system record; the
            state_asgn_id and name of the water system facility record;
            the analyte for the FANL; and the begin and end dates for
            the monitoring period. Otherwise, modify the MDBP Summary
            depending on its Type Code as detailed in Subprocess B
            above.

            1.  If the MDBP Summary is a MRDL, update the CDS trigger
                timestamp of MRDL Summaries whose MPA_APP_BEGIN_DATE is
                greater than this MRDL's MP End Date and is less than or
                equal to this MRDL's MP Begin Date + 1 year -- 1 day (to
                trigger RAA recalculation and compliance determination).

            2.  If the MDBP Summary is a DSRD or SDRD, update the CDS
                trigger timestamp of the MDBP Summary for the next
                monitoring period (to trigger compliance determination).

        ```{=html}
        <!-- -->
        ```
        i.  If a record is not found, create an MDBP Summary and value
            the fields as detailed in Subprocess B above.

            1.  If the MDBP Summary is a MRDL, update the CDS trigger
                timestamp of MRDL Summaries whose MPA_APP_BEGIN_DATE is
                greater than this MRDL's MP End Date and is less than or
                equal to this MRDL's MP Begin Date + 1 year -- 1 day (to
                trigger RAA recalculation and compliance determination).

            2.  If the MDBP Summary is a DSRD or SDRD, update the CDS
                trigger timestamp of the MDBP Summary for the next
                monitoring period (to trigger compliance determination).

    c.  If neither of the Sample Schedules specified above were found,
        create an advisory referencing message number 54 and record the
        water system number, name, tinwsys_is_number, and
        tinwsys_st_code of the water system record.

```{=html}
<!-- -->
```
1.  Determine the status of the water system each Field Result is
    associated and whether there are FANLs of type MRDL, DSRD and/or
    SDRD in effect on the Field Result's collection end date.

    a.  If the water system is a current, active, PWS with a federal
        source type that is not SW or GU, and has either a current DSRD
        or SDRD FANL but does not have a current MRDL FANL (for any
        analyte), create an advisory referencing message number 49 and
        record the water system number, name, tinwsys_is_number, and
        tinwsys_st_code of the water system record.

    b.  If the water system is a current, active, PWS with a federal
        source type of SW or GU, but does not have either a current DSRD
        or SDRD FANL but does not have a current MRDL FANL (for any
        analyte), create an advisory referencing message number 50 and
        record the water system number, name, tinwsys_is_number, and
        tinwsys_st_code of the water system record.

    c.  If the water system is a current, active, PWS with a federal
        primary source type of SW or GU, but does not have a current
        MRDL FANL (for any analyte), create an advisory referencing
        message number 51 and record the water system number, name,
        tinwsys_is_number, and tinwsys_st_code of the water system
        record.

    d.  If the water system is not a current, active, PWS and has any of
        the three types currently in effect (MRDL, DSRD, and/or SDRD),
        create an advisory referencing message number 52 and record the
        water system number, name, tinwsys_is_number, and
        tinwsys_st_code of the water system record.

    e.  If the water system is not a current, active, PWS (i.e., it is a
        historic system or an inactive PWS or non-public system), create
        an advisory referencing message number 53 and record the water
        system number, name, tinwsys_is_number, and tinwsys_st_code of
        the water system record.

*Subprocess D. Calculate MRDL RAA, Assess DSRD or SDRD Compliance due to
MRDL, DSRD or SDRD Additions or Modifications*

Check-Point Restart Issues: This process will be driven by changes to
MDBP Summaries (TSAMDBPS).

> The record key for this subprocess is the composite of CDS trigger
> timestamp and the TSAMDBPS_IS_NUMBER columns.
>
> The sort order for this subprocess will be CDS trigger timestamp
> ascending, TSAMDBPS \_IS_NUMBER ascending.

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

2.  Identify MDBP Summaries where:

    a.  its CDS trigger timestamp is greater than the last run date &
        time for the CDS Setup process AND

    b.  the Type Code is either MRDL, DSRD or SDRD

3.  If MDBP Summary is a MRDL, calculate/recalculate RAA as follows:

    a.  Read each MRDL Summary:

        i.  whose MP Begin Date is greater or equal to the current MRDL
            Summary's MP End Date minus 1 year + 1 day AND is less than
            or equal to the current MRDL Summary's MP End Date AND

        ii. Is for the same Water System Facility as the current MRDL
            Summary's Water System Facility (through their respective
            parent FANLs)

    b.  Sum the total days from each MRDL Summary

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

> E (MDBP Summary MP Average Measure \* Total Days) / E Total Days

ii. If MRDL Summary's parent FANL's Control Level UOM is not MG/L,
    temporarily convert the Average Measure, to the same UOM as the
    FANL.

iii. Round the converted weighted average to the same number of
     significant digits as the Control Level of the MRDL Summary's
     parent FANL.

iv. If the weighted average is greater than the Control Level, set the
    current MRDL Summary's Level Compliance indicator to N, otherwise
    set to Y.

v.  Store the weighted average in the MRDL Summary Running Annual
    Average Measure.

```{=html}
<!-- -->
```
4.  If MDBP Summary is a DSRD or SDRD, determine Level Compliance as
    follows:

    a.  If Percent Samples Beyond Msr Lvl is greater than 5.0

        i.  Read MDBP Summary of the same Summary Type Code for prior
            Monitoring Period

            1.  If that MDBP Summary's Percent Samples Beyond Msr Lvl is
                also greater than 5.0, set Level Compliance Indicator to
                N (if not already N)

            2.  Else set Level Compliance Indicator to Y (if not already
                Y)

    b.  Otherwise, set Level Compliance Indicator for current MDBP
        Summary to Y (if not already Y).

[^1]: Whether the Field Result has one the other or both, it is only
    counted as one sample.

[^2]: For example, say the current MDRL Summary is for April 2005. The
    RAA calculate would sum the MP Averages for the same WS and WSF with
    Monitoring Period Begin Dates \>= 5/1/2004 and \<= 4/30/2005.