1、Systalex-SDWIS-01-9.d1bJuly 31, 2007App E version AAPPENDIX EThis page is intentionally left blank.Systalex-SDWIS-01-9.d1bJuly 31, 2007App E version A7E-iiiCONTENTS1 APPENDIX E OVERVIEW 1-12 TOTAL COLIFORM RULE (TCR) COMPLIANCE REPORT 2-12.1 INPUTS 2-12.2 PROCESSES 2-12.2.1 Check for Unvalidated SAR
2、s .2-12.2.2 Clear2-32.2.3 TCR Compliance Check Process2-42.2.4 Assign Future Monitoring Periods to Water System2-62.2.5 Routine TCR Schedule Compliance Check2-72.2.6 Repeat TCR Schedule Compliance Check .2-102.2.7 Non-Acute TCR MCL Check.2-122.2.8 Triggered Source Water Sample Schedule Check2-122.3
3、POPULATE CDS TABLES.2-142.4 REPORT.2-153 RESULTS ALERT REPORT .3-13.1 INPUTS AVAILABLE 3-13.2 PROCESSES 3-23.2.1 Clear3-23.2.2 Action Level Exceedance Check3-23.2.3 Threshold Level Exceedance3-93.2.4 Facility Level Exceedance3-153.3 POPULATE CDS TABLES.3-193.4 REPORTS.3-194 CHEM/RAD/STATE M tcr_mr_r
4、t_ind, tcr_mr_rpt_ind, and tcr_non_acute_ind; are selected) AND (A.E.2.2.2.a)2. For each Candidate Violation that satisfies above criteria, the software will delete the Candidate Violation, cascade delete associated CDS Schedule and CDS Sample Result(s), if any (cascade delete enforced in the model)
5、.3. If Triggered Source Water Sample Schedules is selected, Select Monitoring Assessment records (A.E.2.2.2.b)a. That are associated to a Report Log of type “TCR” ANDb. That are for a Water System that is part of the selected Water System Group (WHERE Candidate Violation references WATER_SYSTEM and
6、WATER_SYSTEM Sometimes IS_ASSIGNED_TO One or More WATER_SYSTEM_GROUP_ASGMT and WATER_SYSTEM_GROUP_ASGMT Always ASSIGNS One WATER_SYSTEM_GROUP and WATER_SYSTEM_GROUP = Water System Group Selected by User) or selected Regulating Agency (WHERE (TINLGENT.NAME)=Regulating Agency Selected by User dependin
7、g on selected criteria.4. For each Candidate Monitoring Assessment that satisfies above criteria, the software will delete the Candidate Monitoring Assessment, cascade delete associated CDS Schedule and CDS Sample Results(s), if any (cascade delete enforce in model.)2.2.3 TCR Compliance Check Proces
8、sAssess Candidate TCR Violation (action block: tcr_get_routine_sched_for_comp is called by rpt_s_tcr_compliance)1. For each monitoring period that is to be processed a. User-specified single monitoring period OR b. a set of monitoring periods with end dates falling within the specified date range),
9、perform steps 2-8 below.2. If Water System Group selected, then select Sample Schedule, Monitoring Requirement, Water System ordered by Water System ascending (A.E.2.2.3.a) where:a. The Monitoring Requirement is for the Sample Schedule and is Routine (Sample Type Code = RT) or Temporary Routine (Sam
10、ple Type Code = TR) ANDb. The Monitoring Requirement is the same type as the Monitoring Period periodicity (TMNMNR.Sample_Count_Unit_Code = TMNMPRD.Type_Code) ANDc. The rule abbreviation associated to the Monitoring Requirement is TCR (TMNRULE.ABBREVIATION_TEXT = TCR) ANDd. The Monitoring Requiremen
11、t is for Analyte 3100 AND (A.E.2.2.3.b)e. The Water System is part of the selected Water System Group ANDf. The Water System is assigned the selected Monitoring Period via the Water System Monitoring Period Asgmt association3. Else, Regulating Agency selected, select Sample Schedule, Monitoring Requ
12、irement, Water System ordered by Water System Ascending (A.E.2.2.3.c) where:Systalex-SDWIS-01-9.d1bJuly 31, 2007App E version AE-2-5a. The Monitoring Requirement is for the Sample Schedule and is of type “RT” or “TR” ANDb. The Monitoring Requirement periodicity is the same as the Monitoring Period d
13、uration ANDc. The rule abbreviation associated to the Monitoring Requirement is TCR ANDd. The Monitoring Requirement is for Analyte 3100 (A.E.2.2.3.d)e. The Water System is regulated by the selected Regulating Agency i. (WHERE (TINLGENT.NAME)=Regulating Agency Selected by User ANDii. TINRAA.ACTIVE_I
14、ND_CD = A) f. The Water System is assigned the selected Monitoring Period via the Water System Monitoring Period Asgmt association4. Check each retrieved Sample Schedule, if the selected Monitoring Period applies by first, checking its seasonality using action block Check Overlap of Schedule and Mon
15、itoring Period (check_fr_overlap_of_sched_n_mp) and then checking whether the Monitoring Period Begin and End Dates fall within the scope of the Sample Schedule Begin and End Dates.5. For schedule being processed, a. If M however, no M&R violations will be created and only non-acute TCR MCL candidat
16、e violations will be created.Systalex-SDWIS-01-9.d1bJuly 31, 2007App E version AE-2-6d. If Triggered Source Water Sample Schedules compliance check is selected, then for the water system being processed, perform Triggered Source Water Sample Schedule check. (A.E.2.2.3.f)6. Call Assign Future Monitor
17、ing Periods to Water System for TCR Compliance (action block name tcr_assign_mp_for_comp) once per water system (not once per sample schedule) (A.E.2.2.3.h) to create association records in both the Water System Mon Period Asgmt and Rule WS MP Asgmt. Tables unless Triggered Source Water Sample Sched
18、ule is the only selected compliance check. (A.E.2.2.3.g)2.2.4 Assign Future Monitoring Periods to Water SystemProcess: Assign Future Monitoring Periods to Water System for TCR Compliance (Action Block: tcr_assign_mp_for_comp) Inputs: Water System 1. Select current TCR Sample Schedule where a. Sample
19、 Schedule is for input Water System ANDb. Sample Schedule Begin_date is = current date ANDd. Sample Schedules Monitoring Requirement is:i. For Routine (Sample_Type_Code = RT) ANDFor analyte 3100 ANDii. The associated rule abbreviation is TCRe. Retrieve the most current Monitoring Period for which th
20、is Water System has a rule_monitoring period_water system assignment to establish the starting point for future associationsi. Read Each current Monitoring period sorted descending begin date where:1). The monitoring period has a water rule water system monitoring period assignment to the TCR Rule a
21、nd the input water system AND2). The monitoring period type_code = the monitoring requirement sample count unit codeii. For only the first one found1). For Each future monitoring period sorted descending begin date where: Future monitoring period begin date is = current monitoring period begin date
22、AND Future Monitoring period type_code = current monitor period type codea). Read for existing Water System Monitoring Period assignment between the input water system and this future monitoring periodb). When foundi). Read for association of the monitoring period water system assignment to the TCR
23、RuleSystalex-SDWIS-01-9.d1bJuly 31, 2007App E version AE-2-7ii). When found1) Next future monitoring periodiii).When not found1) Use shm025_identify_rule_ws_mp_asgmt to create one2) Next future monitoring periodc). When not foundi). Use shm026_identify_ws_mn_prd_asgmt to create oneii). Use shm025_id
24、entify_rule_ws_mp_asgmt to create oneiii).Next future monitoring periodiii. If none found1). For Each future monitoring period sorted descending begin date where: (Future monitoring period begin date is = current sample schedule begin date) OR (future monitoring begin date is = current sample schedu
25、le begin date) AND Future Monitoring period type_code = current monitor period type codea). Use shm026_identify_ws_mn_prd_asgmt to create oneb). Use shm025_identify_rule_ws_mp_asgmt to create onec). Next future monitoring period2.2.5 Routine TCR Schedule Compliance Check(action block: tcr_deter_rout
26、ine_mr_compliance ) called by action block by tcr_perform_rt_rp_mon_acute_comp.This process counts the total number of routines and total number of positive routines collected by the current water system for the current monitoring period. The first count is used to determine routine M&R compliance.
27、Both counts are carried forward to the Non-Acute MCL Violation check. It also sends an advisory to the TCR Compliance Check Report if a positive routine TC sample does not have a repeat schedule. INPUTS:TCR RuleWater SystemSample ScheduleRegulating AgencyMonitoring PeriodRoutine M&R Compliance Check
28、 Indicator Repeat M&R Compliance Check Indicator1. Record initial Routine Samples Collected for WS = 0Systalex-SDWIS-01-9.d1bJuly 31, 2007App E version AE-2-82. If one or more TCR Sample Summaries were selected for the Monitoring Period being processed exist, select Sample Summaries and correspondin
29、g Sample Summary Results where:a. The Water System is one currently being processedb. Associated Water System Facility (WSF) Type = DSc. Monitoring Period = Period currently being processedd. “For Compliance” indicator is set to Yes (Compliance Purpose Indicator Code = Y)e. Analyte Code= 3100f. Asso
30、ciated Sample Summary Results are Routine (Type Code = RT)3. For each Sample Summary retrieved, set Routine Samples Collected for WS=Routine Samples Collected for WS+ Sample Summary Result Count Quantity4. Retrieve individual TCR results that count as samples collected for the monitoring period. Sel
31、ect Samples, Sample Analytical Results (SARs) and Microbial Analytical Results (MARs) where:a. Water System is one currently being processedb. WSF associated via sampling point association Type = DSc. Rejection Reason Code equal to spaces, i.e. sample is not rejected.d. Type Code = Routine-RTe. For
32、compliance indicator code =Yes-Yf. Associated SAR wherei. Monitoring Period = Period currently being processedii. Analyte is 3100iii. Data Quality Code is Validated - V or Accepted -A)g. Associated MAR where either:i. Presence Indicator Code is P Present ii. Or Presence Indicator Code is A Absent AN
33、D Rejection Reason Code is spaces5. For each TCR result retrieved, increment Routine Samples Collected for WS = Routine Samples Collected for WS + 1.6. If retrieved TCR result is positive (MAR Presence Indicator Code is P-Present), a. Record Routine Samples Positive for WS = Routine Samples Positive
34、 for WS + 1.b. Check both Routine and Repeat M&R Compliance Check Indicators (tcr_mr_rt_ind and tcr_mr_rpt_ind) (A.E.2.2.5.a)i. If neither set to Yes, do not need to check for speciation or repeat schedule, proceed to Repeat Compliance Checkii. Else, if either set to Yes, need to check for speciatio
35、n and whether a repeat schedule is associated to it:1). If the current positive TC routine result does not have an associated Repeat Schedule, create a record in entity CDS_REPORT_EXECUTION_ERRORS (TCDSREER) with Error Message: A positive, routine, TC sample, collected on Collection End Date from Wa
36、ter System WS Number - Water System Name, Systalex-SDWIS-01-9.d1bJuly 31, 2007App E version AE-2-9with Lab Sample No. Lab Sample Number does not have a Repeat Schedule associated to it. As a result, repeat M&R compliance could not be determined and non-acute MCL compliance is likely incorrect. iii.
37、Check Routine M&R Compliance Check Indicator, tcr_mr_rtcomp_ind (A.E.2.2.5.b)1). If not selected, there is no need to check for speciation so skip the following steps, read the next result2). Else,a). Check for E Coli (Analyte Code = 3014) sample result. Select SAR where:i). Associated to Sample ret
38、rieved in step 3 above.ii). Analyte is E. Coli (3014)iii).Data Quality Code is Validated -V or Accepted-Ab). If E Coli result was not found, then Check for Fecal Coliform (Analyte Code = 3013) sample result. Select SAR associated to Sample retrieved in step 3 above where:i). Analyte is Fecal Colifor
39、m (3013)ii). Data Quality Code is Validated=-V or Accepted-Ac). If both E Coli and Fecal Coliform results were not found, then create TCR M/R candidate violation type 25 (Repeat Major) (A.E.2.2.5.e) 24 (Routine Minor) (A.E.2.2.5.c) for failure to speciate using action block create_cand_tcr_mr_viol7.
40、 Check Routine M&R Compliance Indicator, tcr_mr_rtcomp_ind, (A.E.2.2.5.d)a. If Y-Yes, i. If Routine Samples Collected for WS = 0 (none collected), then a create TCR M/R violation type 23 (Routine Major) is assessed, using create_cand_tcr_mr_viol action blockii. Else, if the total was less than requi
41、red based on the Monitoring Requirement, create a TCR M/R violation type 24 (Routine Minor) using create_cand_tcr_mr_viol action blockiii. Set compliance run indicator using action block Set Compliance Run Indicator (action block update_compliance_run_indicator.)b. Else, proceed.2.2.6 Repeat TCR Sch
42、edule Compliance CheckDetermine Repeat TCR Schedule Compliance action block name is tcr_deter_repeat_mr_compliance is called by tcr_perform_rt_rp_mon_acute_comp.). INPUTS:TCR RuleWater SystemRegulating AgencySample ScheduleMonitoring PeriodSystalex-SDWIS-01-9.d1bJuly 31, 2007App E version AE-2-10Rep
43、eat M&R Compliance Check Indicator1. Select Sample Schedule, Monitoring Requirement, Originating Positive Sample where:a. The Monitoring Requirement is for the Sample Schedule and is of type Repeat (Sample_Type_Code = RP)b. The associated rule abbreviation is TCRc. Is for the Water System currently
44、being processedd. Is precipitated by a positive Sample Result that is part of the Originating Positive Sample that was taken to satisfy monitoring requirements in the Monitoring Period being processede. The Monitoring Requirement is for Analyte 3100 (A.E.2.2.6.a)2. Retrieve individual TCR results th
45、at count as repeat samples collected for the sample schedule, monitoring requirement above and the monitoring period being processed. Select Samples and SARs where:a. The Samplesi. Is associated to the Originating Sample retrieved above.ii. Associated via sampling point association to WSF Type = DS
46、iii. Sample Rejection Reason Code is spacesiv. Type Code = Repeat-RPv. For-compliance indicator code = Yb. The SARs i. Analyte=Total coliform 3100ii. Data Quality Code =Validated-V or Accepted-Aiii. And either1). Presence Indicator Code is P Present 2). Or Presence Indicator Code is A Absent AND Rej
47、ection Reason Code is spacesc. For each result retrieved above, ensure that the Sample was taken while the Repeat Sample Schedule was in effect. i. Sample Collection End Date is greater or equal to Sample Schedule Begin Date ANDii. If Sample Schedule End Date is valued, Sample Collection End Date is
48、 less than or equal to Sample Schedule End Date. If this criterion is satisfied, count this result towards the repeat monitoring requirement. Repeat Samples Collected for WS = Repeat Samples Collected for WS + 1.d. If repeat TCR result is positive (MAR Presence Indicator Code is P-Present), i. Recor
49、d Repeat Samples Positive for WS = Repeat Samples Positive for WS + 1.ii. Check Repeat M&R Compliance Check Indicator, tcr_mr_rptcomp_ind (A.E.2.2.6.b)1). If not Yes, there is no need to check for speciation read the next result.2). Else, a). Check for E Coli (Analyte Code = 3014) sample result. Select SAR where:i). Associated to Sample retrieved above.ii). Analyte is E. Coli (3014)iii).Data Quality Code is Validated -V or Accepted-ASystalex-SDWIS-01-9.d1bJuly 31, 2007App E version AE-2-11b). If E Coli result was
