**APPENDIX D-9**

Calculate Monitoring Period Averages

This Page is intentionally left blank

**D-9. Calculate Monitoring Period Averages (Section 9.2.10)**

*For SSWr1, this process was redesigned to use the Compliance Value
field in the new SSMPA SAR Association table for calculating the
monitoring period average. This field contains the associated routine
result=s concentration measure already converted in the applicable UOM
which is required by this process (for monitoring requirements
indicating an MCL, it is the MCL UOM; for monitoring requirements
indicating FANL, it is the FANL UOM; for monitoring requirements
indicating a UOM, it is the UOM indicated in the monitoring
requirement). Confirmation results will not have their own Compliance
Values but will be averaged with their associated routine result in the
compliance value for the routine result.*

*Subprocess A. Calculate Monitoring Period Averages for New/Changed
SSMPAs*

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

Check-Point Restart Issues: Calculate Monitoring Period Averages for
New/Changed SSMPAs.

> The driver for this subprocess is the SSMPA 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.  Creates a log in the CDS Execution Log indicating the process start
    time.

2.  Identify SSMPA records for which monitoring period averages may need
    to be calculated, i.e. newly created SSMPAs and those associated to
    new sample analytical results, modified samples/results, new
    schedules, etc. \[*Developer Note: The CDS software will identify
    these by checking the CDS Trigger Timestamp of SSMPA records.*\]

3.  For each identified SSMPA

> Select the Monitoring Period Average to which it is associated.
>
> If none found - then create Monitoring Period Average
>
> If found
>
> If Monitoring Period Average User ID not equal to CDSSETUP, update its
> CDS trigger timestamp (to trigger downstream MCL calculation) and move
> on to the next SSMPA
>
> Else - modify Monitoring Period Average

4.  Calculate/Recalculate MP average as follows:

    a.  First, determine UOM basis (this UOM will be the MP Average UOM)
        as follows:

        i.  Determine if a current MCL exists for the analyte and, if
            so, read its unit measure and MCL_COMPLIANCE_METHOD value.
            "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.*

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

            1.  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

            2.  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

            3.  the Control Level Type is equal to MAX and

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

        iii. If both MCL and FANL are found, set UOM basis to FANL UOM
             and Compliance Method to MCL Compliance Method.

        iv. If MCL is found and FANL is not found, set UOM basis to MCL
            UOM and Compliance Method to MCL Compliance Method.

        v.  If MCL is not found and FANL is found, set UOM basis to FANL
            UOM and do not set Compliance Method (Compliance method will
            be determined just prior to creating the actual MPA record
            and will be based on periodicity -- RAA if periodicity less
            than 'YR' and MPA otherwise). (A.D-9.a)

        vi. If both MCL and FANL are not found,

            1.  If Analyte Code is either 2920, 1927, 1067 or 1004, set
                the UOM basis to MG/L and do not set Compliance Method
                (Compliance method will be determined just prior to
                creating the actual MPA record and will be based on
                periodicity -- RAA if periodicity less than 'YR' and MPA
                otherwise). (A.D-9.b)

            2.  If Analyte Code is 3014, set the UOM basis to CFU/100ML
                and Compliance Method to RAA. (A.D-9.c)

            3.  If Analyte Code is 3015, set the UOM basis to OOCYSTS/L
                and Compliance Method to RAA. (A.D-9.d)

            4.  Otherwise, set UOM basis to spaces (MPA will not be
                calculated and process will retrieve and process next
                SSMPA).

> \[*Developer Note: The CDS software will use SSMPA Applicable Period
> Begin Date which takes into consideration all three dates.*\]

b.  Identifies all SSMPA SAR Associations already associated to the
    retrieved SSMPA. \[These associations have been created by process
    *Associate Sample Analytical Results to Sample Schedule Monitoring
    Period Assignments* and each SSMPA SAR Association stores a
    Compliance Value that is calculated/averaged from routine or maximum
    residence time results (and any associated confirmation results)
    that are accepted or validated Afor compliance@ sample and whose
    rejection reason is not valued.\]

    i.  If the unit of measure for a Compliance Value is not the same as
        the UOM basis, the Compliance Value is temporarily converted to
        the UOM basis. Use the conversion algorithm for converting a
        concentration measure from one UOM to another as described in
        Appendix D-6: Associate Sample Analytical Results to Sample
        Schedule Monitoring Period Assignments and Sampling Point
        Sub-SSMPAs. For a Uranium confirmation result not in the same
        UOM category as the compliance value, use the conversion factor
        specified by regulation (1.49 ug/pCi or .67 pCi/ug). (A.D-9.l)

    ii. Calculate the monitoring period average for the SSMPA by adding
        all the compliance values and dividing the sum by the number of
        SSMPA SAR Associations processed for the SSMPA and rounding to
        the least significant figure used to calculate the average.

```{=html}
<!-- -->
```
a.  Create a new or modify an existing record as follows:

+---------------------------+------------------------------------------+
| **Monitoring Period       | **Source**                               |
| Average**                 |                                          |
|                           |                                          |
| **Attributes**            |                                          |
+===========================+==========================================+
| Average Measure           | Product of averaging calculation rounded |
|                           | to the least number of significant       |
|                           | figures used in the calculation          |
+---------------------------+------------------------------------------+
| UOM Code                  | UOM basis as determined above            |
+---------------------------+------------------------------------------+
| MCL Compliance Method     | Compliance Method as determined above.   |
|                           | However, if Compliance Method is RAA or  |
|                           | spaces, perform additional check based   |
|                           | on periodicity. If periodicity is not    |
|                           | equal to MN, QT or 6M, then override     |
|                           | Compliance Method and set to MPA.        |
+---------------------------+------------------------------------------+
| Measure Text              | The average measure stored and rounded   |
|                           | to the least number of significant       |
|                           | figures used in the calculation          |
+---------------------------+------------------------------------------+
| Total Days                | Days between (TMNSSMPA Begin Date and    |
|                           | TMNSSMPA End Date) plus 1 minus days     |
|                           | between (TMNSSMPA App Per Gap Begin Date |
|                           | minus TMNSSMPA App Pert Gap End Date)    |
+---------------------------+------------------------------------------+
| Number of Results Used    | The number of Compliance Values used to  |
|                           | calculate the average measure            |
+---------------------------+------------------------------------------+
| Applicable Period Begin   | TMNSSMPA Applicable Period Begin Date    |
| Date                      |                                          |
+---------------------------+------------------------------------------+
| Applicable Period End     | TMNSSMPA Applicable Period End Date      |
| Date                      |                                          |
+---------------------------+------------------------------------------+
| Last Update Timestamp     | Current Date and Time                    |
+---------------------------+------------------------------------------+
| UserID                    | CDSSETUP                                 |
+---------------------------+------------------------------------------+

(A.D-9.e)

> If there are no results related to a Sample Schedule Monitoring Period
> Assignment record, the Calculate Monitoring Period Averages creates a
> Monitoring Period Average record anyway using the following values:

+---------------------------+------------------------------------------+
| **Monitoring Period       | **Source**                               |
| Average**                 |                                          |
|                           |                                          |
| **Attributes**            |                                          |
+===========================+==========================================+
| Average Measure           | 0.0                                      |
+---------------------------+------------------------------------------+
| UOM Code                  | UOM basis as determined above            |
+---------------------------+------------------------------------------+
| MCL Compliance Method     | Compliance Method as determined above.   |
|                           | However, if Compliance Method is RAA or  |
|                           | spaces, perform additional check based   |
|                           | on periodicity. If periodicity is not    |
|                           | equal to MN, QT or 6M, then override     |
|                           | Compliance Method and set to MPA.        |
+---------------------------+------------------------------------------+
| MCL Text                  | SPACES                                   |
+---------------------------+------------------------------------------+
| Total Days                | 0                                        |
+---------------------------+------------------------------------------+
| Number of Results Used    | 0                                        |
+---------------------------+------------------------------------------+
| Applicable Period Begin   | TMNSSMPA Applicable Period Begin Date    |
| Date                      |                                          |
+---------------------------+------------------------------------------+
| Applicable Period End     | TMNSSMPA Applicable Period End Date      |
| Date                      |                                          |
+---------------------------+------------------------------------------+
| UserID                    | CDSSETUP                                 |
+---------------------------+------------------------------------------+
| Last Update Timestamp     | Current Date and Time                    |
+---------------------------+------------------------------------------+

(A.D-9.f)

> It is necessary to create Monitoring Period Averages even when there
> are no results for two reasons:
>
> \- so that, when calculating MCL Values using the running annual
> average method, a value will be calculated for a Monitoring Period
> during which no result was collected; AND
>
> \- so that, when a result for a quarter is added to the database late
> and the running annual average method applies, the Calculate MCL
> Values process will not only know to recalculate the MCL value for
> that quarter but also for any subsequent quarters to which that
> Monitoring Period Average applies.

5.  If Analyte referenced by the SSMPA is either TThm (2950) or HAA5
    (2456), MPA at each Sampling Point (Locational-MPA) is calculated
    for each applicable Sampling Point as follows: (A.D-9.g)

    a.  Identify (distinct) Sampling Points referenced by SSMPA SAR
        Associations already associated to the retrieved SSMPA.

    b.  For each Sampling Point identified, retrieve associated SSMPA
        SAR Associations also associated to the same SSMPA

        i.  If the unit of measure for a Compliance Value is not the
            same as the UOM basis, the Compliance Value is temporarily
            converted to the UOM basis. Use the conversion algorithm for
            converting a concentration measure from one UOM to another
            as described in Appendix D-6: Associate Sample Analytical
            Results to Sample Schedule Monitoring Period Assignments and
            Sampling Point Sub-SSMPAs.

        ii. Calculate the monitoring period average for the SSMPA and
            Sampling Point by adding all the compliance values and
            dividing the sum by the number of SSMPA SAR Associations
            processed for the SSMPA/Sampling Point and rounding to the
            least significant figure used to calculate the average.

    c.  Create a new or modify an existing L-MPA record for the same
        SSMPA and Sampling Point.

6.  If the MCL Compliance Method is "RAA" and SSMPA Applicable Period
    End Date is less than CURRENT DATE, then update the timestamps of
    all the MP Averages whose Applicable Period Begin Date falls within
    1 year of the current SSMPA's Applicable Period Begin Date (to
    trigger MCL recalculation).

*The following is for addressing the case when the Raw TOC or Alkalinity
SSMPAs were processed in this CDS Setup run (due to changed results,
etc) but the corresponding Finished TOC is not. Updating the MPA
timestamp of the Finished TOC guarantees that the Precursor Achieved
Removal Ratio will be recalculated in the next subprocess.*

7.  If the SSMPA is for a Raw TOC or Alkalinity schedule, determine if
    there is an existing relevant Finished TOC Monitoring Period Average
    record for the same monitoring period. Determine as follows:

    a.  Read Raw TOC or Alkalinity schedule (and related Schedule
        Package). If there is no associated schedule package, skip and
        process next SSMPA. Otherwise, proceed to next steps.

    b.  Read each Sample Schedule associated to the current Schedule
        Package where:

        i.  Either:

            1.  the Sample Schedule is associated to the current
                Schedule Package through a Schedule Package Role record
                OR

            2.  the Sample Schedule is associated to the current
                Schedule Package through a Schedule Package Role and a
                Group Schedule

AND

i.  the Type for the Schedule Package Role is equal to F (finished) AND

ii. the Sample Type of the Monitoring Requirement associated to the
    Sample Schedule is "RT" AND

iii. the Analyte Code associated to the Monitoring Requirement is equal
     to "2920"

```{=html}
<!-- -->
```
a.  Retrieve the Finished TOC=s Monitoring Period Average (via the
    associated SSMPA) for the identified Finished TOC Schedule with the
    same monitoring period as the Raw TOC or Alkalinity SSMPA. Update
    the retrieved Finished TOC Monitoring Period Average CDS Trigger
    Timestamp to the database current timestamp (use Action Block:
    Shi012_Get_DB_Timestamp ).

*Subprocess B. Calculate Precursor Achieved Removal Ratio*

After processing all SSMPAs as indicated above, initiate the following,
new Calculate Precursor Achieved Removal Ratio process.

In Release 8.0, a new entity, **Schedule Package Role**, was added to
resolve the problem of creating schedule packages between Sample
Schedule records and Schedule Group records and vice versa as well as
between Sample Schedule and Sample Schedule as well as Group Schedule
and Group Schedule.

Note that it is possible for a finished TOC schedule to be packaged with
only TOC raw schedules or only raw alkalinity schedules, neither of
these situations provide all the necessary information to calculate a
Precursor Achieved Removal Ratio. The following design details
anticipate this situation.

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

Check-Point Restart Issues: Calculate Precursor Achieved Removal Ratio.

> The driver for this subprocess is the SSMPA 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.

21660. Creates a log in the CDS Execution Log indicating the process
       start time.

```{=html}
<!-- -->
```
1.  Identify Monitoring Period Average records for which Precursor
    Achieved Removal Ratio may need to be calculated/recalculated, i.e.
    newly created or updated Monitoring Period Averages for Finished TOC
    schedules. \[*Developer Note: The CDS software will identify these
    by checking the CDS trigger timestamp of Monitoring Period Average
    records.*\] A Finished TOC Schedule is one where:

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

    ```{=html}
    <!-- -->
    ```
    a.  (A.D-9.h)

    b.  (A.D-9.i)

    c.  Either:

        i.  The Sample Schedule references a Group Schedule that
            references a Schedule Package Role with Type = F OR

        ii. The Sample Schedule references a Schedule Package Role with
            Type = F

```{=html}
<!-- -->
```
1.  For each identified (Finished TOC) Monitoring Period Average:

    a.  Read Finished TOC schedule (and related Schedule Package)
        associated to the Monitoring Period Average identified (via the
        associated SSMPA) where:

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

        ii. (A.D-9.j)

        iii. (A.D-9.k)

        iv. Either:

            1.  The Sample Schedule references a Group Schedule that
                references a Schedule Package Role with Type = F OR

            2.  The Sample Schedule references a Schedule Package Role
                with Type = F

    b.  Determine the RAW TOC average by averaging the MP Averages of
        all relevant Raw TOC schedules as follows:

        i.  Read each Sample Schedule associated to the current Schedule
            Package where:

            1.  Either:

                a.  the Sample Schedule is associated to the current
                    Schedule Package through a Schedule Package Role
                    record OR

                b.  the Sample Schedule is associated to the current
                    Schedule Package through a Schedule Package Role and
                    a Group Schedule

AND

1.  the Type for the Schedule Package Role is equal to R (raw) AND

2.  the Sample Type of the Monitoring Requirement associated to the
    Sample Schedule is "RT" AND

3.  the Analyte Code associated to the Monitoring Requirement is equal
    to "2920"

```{=html}
<!-- -->
```
i.  For each Sample Schedule, read, convert and temporarily store the
    Measure of Monitoring Period Average associated to the Sample
    Schedule (through a TMNSSMPA) where the TMNSSMPA referenced by the
    Monitoring Period Average and current Sample Schedule references the
    current Monitoring Period that Monitoring Period Average Number of
    Results \> 0.

> Before temporarily storing the average, convert it (if necessary) to
> MG/L using the following table. (Note that many times there will only
> be one Raw TOC Schedule that has been packaged with the finished
> schedule.)

  ------------------------------------------------------------------------
  **From UOM**       **To UOM**                  **Multiplier**
  ------------------ --------------------------- -------------------------
  MG/L               UG/L                        1000

  MG/L               NG/L                        1000000

  UG/L               MG/L                        0.001

  UG/L               NG/L                        1000

  NG/L               MG/L                        0.000001

  NG/L               UG/L                        0.001
  ------------------------------------------------------------------------

> If a conversion cannot be made (e.g., pCI/L for a MP Avg), An advisory
> will be created referencing message number 17 and will record: the
> water system number, name, tinwsys_is_number, and tinwsys_st_code; the
> water system facility state assigned ID and name; the analyte
> associated to the MP Average (and, if the schedule is a hidden
> schedule, the analyte group for the group schedule), begin date and
> d_userid_code.

ii. If there are no Raw TOC Schedules associated to the finished
    schedule:

    1.  An advisory will be created referencing message number 18 and
        will record: the water system number, name, tinwsys_is_number,
        and tinwsys_st_code; the water system facility state assigned ID
        and name; the finished TOC schedule=s analyte (and, if the
        schedule is a hidden schedule, the analyte group for the group
        schedule), begin date and d_userid_code.

> And

2.  cease processing the current SSMPA and go to the next Finished
    Schedule.

```{=html}
<!-- -->
```
a.  Determine the RAW Alkalinity average by averaging the MP Averages of
    all relevant Raw Alkalinity schedules as follows:

    i.  Read each Sample Schedule associated to the current Schedule
        Package where:

        1.  Either:

            a.  the Sample Schedule is associated to the current
                Schedule Package through a Schedule Package Role record
                OR

            b.  the Sample Schedule is associated to the current
                Schedule Package through a Schedule Package Role and a
                Group Schedule

> AND

1.  the Type for the Schedule Package Role is equal to R (raw) AND

2.  the Sample Type of the Monitoring Requirement associated to the
    Sample Schedule is "RT" AND

3.  the Analyte Code associated to the Monitoring Requirement is equal
    to "1067" or "1927"

```{=html}
<!-- -->
```
i.  For each Sample Schedule, read, convert and temporarily store the
    Measure of Monitoring Period Average associated to the Sample
    Schedule (through an TMNSSMPA) where the TMNSSMPA referenced by the
    Monitoring Period Average and current Sample Schedule references the
    current Monitoring Period that Monitoring Period Average Number of
    Results \> 0. (Note that many times there will only be one Raw
    Alkalinity Schedule that has been packaged with the finished
    schedule.)

> Treat a MP Average for either analyte code (i.e., 1067 or 1927) as the
> same. In other words, if there are two Raw Alkalinity Schedules, one
> referencing Analyte Code 1927 (and therefore having a MP Average for
> 1927) and the other for Analyte Code 1067 (and therefore having a MP
> Average for 1067), use MP Average for both analytes when calculating
> an alkalinity average.
>
> If there are no Raw Alkalinity Schedules associated to the finished
> schedule:

1.  An advisory will be created referencing message number 19 and will
    record: the water system number, name, tinwsys_is_number, and
    tinwsys_st_code; the water system facility state assigned ID and
    name; the raw alkalinity schedule=s analyte (and, if the schedule is
    a hidden schedule, the analyte group for the group schedule), begin
    date and d_userid_code.

And

2.  cease processing the current SSMPA and go to the next Finished
    Schedule.

```{=html}
<!-- -->
```
a.  Value the Precursor Achieved Removal Ratio attributes (field names
    PRC_ACH_RMVL_RA_NO and PRC_ACH_RMVL_RA_TX, the first a numeric
    field, the second a text field for displaying the value on the
    Results Averages Maintenance window) in the Monitoring Period
    Average for the Finished TOC Sample Schedule as follows:

    i.  Value both as 0:

        1.  If the Number of Results Used attribute in the MP Average
            for the Finished TOC schedule is equal to zero

        2.  Or if the Number of Results Used attribute in the MP Average
            for all the Raw TOC schedules are equal to zero

    ```{=html}
    <!-- -->
    ```
    i.  Else value both as 1.00:

        1.  If the calculated average of the Monitoring Period Averages
            for Raw TOC schedules is 0.

    ```{=html}
    <!-- -->
    ```
    i.  Else:

        1.  calculate preliminary ratio as follows

> "Actual TOC Removal Percentage"/"Required TOC Removal Percentage"
>
> Where:
>
> The "Required TOC Removal Percentage" is derived from the following
> matrix, using the Average Raw TOC and Average Raw Alkalinity results
> calculated above.

  ----------------------------- --------------- ------------- -------------
  **Required TOC Removal in                                   
  Percent**                                                   

  ***Average Raw TOC (mg/l)***  ***Calculated                 
                                Average Raw                   
                                Alkalinity                    
                                (mg/l)***                     

                                ***0 to 60***   ***\>60 to    ***\>120***
                                                120***        

  2.0 to 4.0                    35.0            25.0          15.0

  \> 4.0 to 8.0                 45.0            35.0          25.0

  \> 8.0                        50.0            40.0          30.0
  ----------------------------- --------------- ------------- -------------

> And the "Actual TOC Removal Percentage" is calculated as follows:
>
> (1 - (Finished TOC Average/ Raw TOC Average)) \* 100

2.  After calculating the preliminary PARR, set both PRC_ACH_RMVL_RA_NO
    and PRC_ACH_RMVL_RA_TX to:

    a.  If preliminary PARR is less than "1.00" and either Finished TOC
        MP Average or Raw TOC MP Average is less than 2.0, set to "1.00"

    b.  Otherwise set to the preliminary PARR even if it is a negative
        number