**APPENDIX D-1**

Update Sample Schedules Due to Inventory Change

This Page is intentionally left blank

**D-1. Update Sample Schedules Due to Inventory Change** (Section 9.2.1)

This process is split into five subprocesses:

1.  Subprocess: Clear CDS Setup Process Log

> For SSWr1, several tables were introduced to store Water System, Water
> System Facility, Sampling Point, Schedule, Lab, Sample, Result, and
> other relevant data about error or alert conditions that may be
> encountered during *CDS Setup* runs. Users may now use the data more
> conveniently, e.g., using MS Access, in the new tables, referred to as
> the CDS Setup Process Log tables, to easily identify the specific
> records that caused the error or alert condition so that these can be
> corrected prior to the next *CDS Setup* run. The log tables will
> replace the text-based CDS Setup Processing Report that is currently
> being created during each run of *CDS Setup* (although a text file
> listing all the start and end times of each process will continue to
> be generated). To enable automated cleanup of these tables, this
> process checks and clears the CDS Setup Process Log tables for data
> that have been in the tables longer than the user-specified time frame
> (in days) retrieved from the TINPVALS table where code_name =
> \>CDSRPTDAYS= .

a.  Retrieve the retention period (in days) stored in TINPVALS where
    code_name = \>CDSRPTDAYS= and sequence_number = 1.

b.  Calculate Reference Date to be equal to CURRENT DATE minus retention
    period (in days).

c.  Delete the CDS Setup Processing Time Report text file referenced by
    the CDS Setup Processing Log record (if found in folder
    c:\\sdwis\\cds\\setup) where the CDS setup_start_timestamp is less
    than the Reference Date.

d.  Delete records in CDS Setup Process Log (TCDSPLOG) where the CDS
    setup_start_timestamp is less than the Reference Date. This also
    cascade deletes child records.

```{=html}
<!-- -->
```
2.  Subprocess: Water System Changes

> This subprocess is driven by changes to the water system. This
> subprocess will obtain the referenced timestamp and referenced
> TINWSYS_IS_NUMBER from the CDS Setup Execution Log (for Process_ID =
> "1A"). The subprocess will read each water system that has a CDS
> trigger timestamp value that is greater than the referenced timestamp
> value corresponding to the process; or each water system that has a
> CDS trigger timestamp value that is equal to the referenced timestamp
> value and the TINWSYS_IS_NUMBER is greater than the referenced
> \_IS_NUMBER. This subprocess will always read the water systems sorted
> by CDS trigger timestamp, ascending and TINWSYS_IS_NUMBER, ascending.
> For every one of these water systems, the subprocess will read the
> WATER_SYSTEM_HISTORY table associated with this water system, sorted
> by the LAST_UPDATE \_TIMESTAMP of the WATER_SYSTEM_HISTORY table,
> where the LAST_UPDATE \_TIMESTAMP of the WATER_SYSTEM_HISTORY table is
> greater than the referenced timestamp in the CDS Setup Execution Log
> corresponding to this subprocess. If there is one or more history
> records returned, the one with the earliest timestamp will be used to
> determine what was changed since the last time *CDS Setup* ran. If
> there are no history records, then it is inferred that the water
> system is newly created or has not changed significantly since it
> entered the database. Based on the comparison between the snapshot in
> the history record and the Water System table, the changes are
> determined and the changes are processed as follows:
>
> The driver for this subprocess is the Water System table.
>
> The record key for this subprocess will be CDS trigger timestamp and
> TINWSYS_IS_NUMBER columns.
>
> The sort order for this process will be CDS trigger timestamp
> ascending and TINWSYS_IS_NUMBER ascending.

a.  If a Water System does not have history records and its activity
    status is \>A= - active, then it is inferred that the water system
    is newly created, i.e., either migrated in or entered online. *CDS
    Setup* will create an advisory record. The message (Message \# 1)
    and data recorded in the advisory is documented at Requirement
    #A.9.2.1.i in Section 9 of the design document.

b.  If an active water system=s classification has changed from any one
    of the three public types (community (C); non-transient,
    non-community (NTNC); or transient, non-community (NC)) to
    non-public (NP), this process will set the Effective End Date to the
    current date for all non-TCR Sample Schedules and TCR Sample
    Schedules at raw water sources (WSF Facility Type Code \<\> DS) and
    Facility Analyte Levels for the water system that have an open or
    future Effective End Date (i.e., Effective End Date is null or is
    greater than the current date) and the Effective Begin Date is less
    than or equal to the current date.

> If the Sample Schedule or Facility Analyte Level's Effective Begin
> Date is later than the current date, *CDS Setup* will delete the
> sample schedule or facility analyte level. As part of deleting the
> sample schedule, the following associated records are also cascade
> deleted:

i.  mcl_value records associated via a
    sample_schedule_mntrg_period_asgmt.

ii. monitoring_period_average records associated via a
    sample_schedule_mntrg_period_asgmt.

iii. ssmpa_sar_association records associated via a
     sample_schedule_mntrg_period_asgmt.

iv. spuba_sar_association records associated via a
    sampling_point_sub_ssmpa and samling_point_subschedule.

v.  sampling_point_sub_ssmpa records associated via a
    samling_point_subschedule.

vi. samling_point_subschedule records associated directly to the sample
    schedule.

vii. sample_schedule_mntrg_period_asgmt associated directly to the
     sample schedule.

viii. schedule_package records associated via a schedule_package_role.

ix. If the sample schedule being deleted is associated to a
    sample_schedule_group, then *CDS Setup* will also delete the
    following before deleting the schedule:

    1.  sampling_pt_sched_grp_subsched records associated via a
        sample_schedule_group.

    2.  sample_schedule_group records associated directly to the
        sample_schedule.

        The process will also create an advisory record. The message
        (Message \# 2) and data recorded in the advisory is documented
        at Requirement #A.9.2.1.b in Section 9 of the design document.

```{=html}
<!-- -->
```
a.  If an active water system=s classification changes from transient,
    non-community (NC) to either non-transient, non-community (NTNC) or
    community (C) \[TINWSYS.D_PWS_FED_TYPE_CD In (\"C\",\"NTNC\")) AND
    TINWASH.D_PWS_FED_TYPE_CD =\"NC\" AND TINWSYS.ACTIVITY_STATUS_CD =
    \"A\"\] (A.D-1.a); the process will create an advisory record. The
    message (Message \# 3) and data recorded in the advisory is
    documented at Requirement #A.9.2.1.f in Section 9 of the design
    document.

b.  If an active water system=s classification changes from non-public
    (NP) to any one of the three public types (community (C);
    non-transient, non-community (NTNC); or transient, non-community
    (NC)) \[TINWSYS.D_PWS_FED_TYPE_CD In (\"C\",\"NTNC\",\"NC\") AND
    D_PWS_FED_TYPE_CD = \"NP\" AND TINWSYS.ACTIVITY_STATUS_CD = \"A\"\]
    (A.D-1.b), the process will create an advisory record. The message
    (Message \# 3) and data recorded in the advisory is documented at
    Requirement #A.9.2.1.g in Section 9 of the design document.

c.  If a water system is deleted (which actually does not delete the
    water system record but changes its status from active (A) to
    inactive (I) and changes its Historic Indicator Code to historic
    (H)), this process will set the Effective End Date to the current
    date for all non-TCR Sample Schedules and TCR Sample Schedules at
    raw water sources (WSF Facility Type Code \<\> DS) and Facility
    Analyte Levels for the water system that have an open or future
    Effective End Date (i.e., Effective End Date is null or is greater
    than the current date) and the Effective Begin Date is less than or
    equal to the current date.

> If the Sample Schedule or Facility Analyte Level's Effective Begin
> Date is later than the current date, *CDS Setup* will delete the
> sample schedule or facility analyte level.

The process will also create an advisory record. The message (Message \#
4) and data recorded in the advisory is documented at Requirement
#A.9.2.1.c in Section 9 of the design document. (A.D-1.c)

d.  If the Water System=s activity status is changed from active to
    inactive, this process will set the Effective End Date to the
    inactivity date entered for the water system for all non-TCR Sample
    Schedules and TCR Sample Schedules at raw water sources (WSF
    Facility Type Code \<\> DS) and Facility Analyte Levels for the
    water system that have an open or future Effective End Date (i.e.,
    Effective End Date is null or is greater than the current date) and
    the Effective Begin Date is less than or equal to the date of
    inactivity.

> If the Sample Schedule or Facility Analyte Level's Effective Begin
> Date is later than the date of inactivity, *CDS Setup* will delete the
> sample schedule or facility analyte level.
>
> The process will also create an advisory record. The message (Message
> \# 5) and data recorded in the advisory is documented at Requirement
> #A.9.2.1.c in Section 9 of the design document.

e.  If the Water System=s activity status is changed from inactive or
    proposed to active, the process will create an advisory record. The
    message (Message \# 6) and data recorded in the advisory is
    documented at Requirement #A.9.2.1.h in Section 9 of the design
    document. (R01230)

f.  If an active community (C) or non-transient, noncommunity (NTNC)
    Water System=s population changes, the process will create an
    advisory record. The message (Message \# 32) and data recorded in
    the advisory is documented at Requirement #A.9.2.1.m in Section 9 of
    the design document.

g.  If a public (C, NTNC or NC) Water System=s Federal Primary Source
    Type changes, the process will create an advisory record. The
    message (Message \# 33) and data recorded in the advisory is
    documented at Requirement #A.9.2.1.n in Section 9 of the design
    document. In addition to identifying the Water System in the report,
    list the population before (from TINWASH) and after (from TINWSYS),
    and the applicable rule(s).

h.  When closing schedules as specified above, this process will also
    delete SSMPAs and cascade delete Sub SSMPAs that are associated to
    these schedules and have a applicable period begin date after the
    Effective End Date of the schedule. The process will also:

    i.  Disassociate linked Sample Results by deleting all associated
        SSMPA SAR Associations and SPUBA SAR Associations. Prior to
        disassociating, update each Sample Result=s CDS trigger
        timestamp.

    ii. Disassociate linked Sample Summaries. Prior to disassociating,
        update each Sample Summary=s CDS trigger timestamp.

    iii. In the process of deleting the SSMPA, all associated Monitoring
         Period Averages and MCL Values are also cascade deleted.

```{=html}
<!-- -->
```
1.  Subprocess: Water System Facility Changes

> This subprocess is driven by changes to Water System Facility. The
> subprocess will read each water system facility that has a CDS trigger
> timestamp value that is greater than the reference timestamp value on
> the CDS Setup Execution Log corresponding to the process; or each
> water system facility that has a last update time stamp value that is
> equal to the reference timestamp value and TINWSF_IS_NUMBER is greater
> than the reference \_IS_NUMBER. This subprocess will always read the
> water system facilities sorted by CDS trigger timestamp ascending and
> TINWSF_IS_NUMBER ascending. For every one of these water system
> facilities the subprocess would read the WATER_SYSTEM_FACILITY_HISTORY
> table associated with this water system facility sorted by the LAST
> UPDATE TIMESTAMP of the water system facility history table, where the
> last update timestamp of the water system facility history table is
> greater than the reference timestamp in the CDS Setup Execution Log
> corresponding to this subprocess. If there are one or more history
> records returned, the one with the earliest timestamp will be used to
> determine what was changed since the last time *CDS Setup* ran. If
> there are no history records, then it is inferred that the water
> system facility is newly created. Based on the comparison between the
> snapshot in the history record and the Water System Facility table,
> the changes are determined and the changes are processed as follows.
>
> The driver for this subprocess is the Water System Facility table.
>
> The record key for this subprocess will be CDS trigger timestamp and
> TINWSF_IS_NUMBER columns.
>
> The sort order for this process will be CDS trigger timestamp
> ascending and TINWSF_IS_NUMBER ascending.

a.  If a Water System Facility does not have history records and its
    activity status is \>A= - active, then it is inferred that the water
    system facility is newly created, i.e., either migrated in or
    entered online. The process will create an advisory record. The
    message (Message \# 7) and data recorded in the advisory is
    documented at Requirement #A.9.2.1.k in Section 9 of the design
    document.

b.  If a Water System Facility=s activity status is changed from active
    to inactive, this process will set the Effective End Date to the
    activity date (which, in this case is the date of inactivity) for
    all non-TCR Sample Schedules and TCR Sample Schedules at raw water
    sources (WSF Facility Type Code \<\> DS) and Facility Analyte Levels
    for the water system that have an open or future Effective End Date
    (i.e., Effective End Date is null or is greater than the current
    date) and the Effective Begin Date is less than or equal to the date
    of inactivity.

> If the Sample Schedule or Facility Analyte Level's Effective Begin
> Date is later than the date of inactivity, *CDS Setup* will delete the
> sample schedule or facility analyte level.
>
> The process will also create an advisory record. The message (Message
> \# 8) and data recorded in the advisory is documented at Requirement
> #A.9.2.1.d in Section 9 of the design document.

c.  If a Water System Facility=s activity status is changed from
    inactive or proposed to active, the process will create an advisory
    record. The message (Message \# 9) and data recorded in the advisory
    is documented at Requirement #A.9.2.1.j in Section 9 of the design
    document. (R01250)

d.  If a Water System Facility=s availability is changes, the process
    will create an advisory record. The message (Message \# 34) and data
    recorded in the advisory is documented at Requirement #A.9.2.1.o in
    Section 9 of the design document. (R02253)

e.  If a Water System Facility=s water type changes, the process will
    create an advisory record. The message (Message \# 35) and data
    recorded in the advisory is documented at Requirement #A.9.2.1.p in
    Section 9 of the design document. In addition to identifying the
    Water System and Water System Facility in the report, list the water
    type before (from TINWSFH) and after (from TINWSF). (R02253)

f.  If a Water System Facility is deleted, this process does not need to
    do anything since a Water System Facility cannot be deleted without
    first deleting all Sampling Points that are associated to it. A
    Sampling Point cannot be deleted until all Sample Schedules and
    Samples associated to it are first deleted.

g.  When closing schedules as specified above, this process will also
    delete SSMPAs and cascade delete Sub SSMPAs that are associated to
    these schedules and have an applicable period begin date after the
    Effective End Date of the schedule. The process will also:

    ?.  Disassociate linked Sample Results by deleting all associated
        SSMPA SAR Associations and SPUBA SAR Associations. Prior to
        disassociating, update each Sample Result=s CDS trigger
        timestamp.

    ```{=html}
    <!-- -->
    ```
    i.  Disassociate linked Sample Summaries. Prior to disassociating,
        update each Sample Summary=s CDS trigger timestamp.

    ii. In the process of deleting the SSMPA, all associated Monitoring
        Period Averages and MCL Values are also cascade deleted.

```{=html}
<!-- -->
```
2.  Subprocess: Sampling Point Changes

> This subprocess is driven by changes to the Sampling Point Entity
> type. The subprocess will read each Sampling Point that has a last CDS
> trigger timestamp value that is greater than the reference timestamp
> value on the CDS Setup Execution Log corresponding to the process, or
> Each sampling point that has a last CDS trigger timestamp value that
> is equal to the reference timestamp value and a TSASMPPT_IS_NUMBER
> greater than the reference \_IS_NUMBER. This subprocess will always
> read the sampling points sorted by CDS trigger timestamp ascending and
> TSASMPPT_IS_NUMBER ascending. For every one of these sampling points
> the subprocess would read the Sampling Point History table associated
> with this sampling point, sorted by the LAST_UPDATE time stamp of the
> sampling point history table, where the LAST_UPDATE_TIMESTAMP of the
> sampling point history table is greater than the reference timestamp
> in the CDS Setup Execution Log corresponding to this subprocess. If
> there are one or more history records returned, the one with the
> smallest timestamp would be used to determine what was changed since
> the last time *CDS Setup* ran. If there are no history records, then
> it is inferred that the sampling point is newly created. Based on the
> comparison between the snapshot in the history record and the Sampling
> Point table, the changes are determined and the changes are processed
> as follows:
>
> The driver for this subprocess is the Sampling Point table.
>
> The record key for this subprocess will be CDS trigger timestamp and
> TSASMPPT_IS_NUMBER columns.
>
> The sort order for this process will be CDS trigger timestamp
> ascending and TSASMPPT_IS_NUMBER ascending.

p.  If a Sampling Point does not have history records, then it is
    inferred that the sampling point is newly created. if the Activity
    Status for the new Sampling Point is \>A= - Active, *CDS Setup* will
    create an advisory record. The message (Message \# 10) and data
    recorded in the advisory is documented at Requirement #A.9.2.1.l in
    Section 9 of the design document.

```{=html}
<!-- -->
```
a.  If a Sampling Point=s activity status is changed from active to
    inactive, this process will:

    i.  Determine associated Sampling Plan Sampling Point Asgmts whose
        Begin Date is less than the Sampling Point Activity Date (which,
        in this case, is the inactivation date). For each Sampling Plan
        identified, *CDS Setup* will create an advisory record. The
        message (Message \# 37) and data recorded in the advisory is
        documented at Requirement #A.9.2.1.s in Section 9 of the design
        document.

    ii. For each individual schedule that is associated to the sampling
        point (via sampling point sub-schedule records), create an
        advisory record. The message (Message \# 36) and data recorded
        in the advisory is documented at Requirement #A.9.2.1.r in
        Section 9 of the design document.

b.  If a Sampling Point=s activity status is changed from inactive or
    proposed to active and the Sampling Point is an entry point (type
    equal to EP), the process will create an advisory record. The
    message (Message \# 12) and data recorded in the advisory is
    documented at Requirement #A.9.2.1.l in Section 9 of the design
    document.

c.  If a Sampling Point is deleted, this process does not need to do
    anything, since a Sampling Point cannot be deleted until all Sample
    Schedules and Samples associated to it are first deleted.

```{=html}
<!-- -->
```
3.  Subprocess: Sampling Plan Sampling Point Asgmt Changes

> This subprocess is driven by changes to the Sampling Plan Sampling
> Point Asgmt Entity type, which can happen if the user changes any of
> the following: Sampling Plan, Sampling Point Schedule Dates, or the
> Sampling Plan Sampling Point Asgmt itself. The subprocess will read
> each Sampling Plan Sampling Point Asgmt that has a CDS trigger
> timestamp value greater than the reference timestamp value on the CDS
> Setup Execution Log corresponding to the process. This subprocess will
> always read the sampling points sorted by CDS trigger timestamp
> ascending.
>
> The driver for this subprocess is the Sampling Plan Sampling Point
> table.
>
> The record key for this subprocess will be CDS trigger timestamp
> column.
>
> The sort order for this process will be CDS trigger timestamp
> ascending.

a.  Identify Sampling Plan Sampling Point Asgmt (and associated Sampling
    Point) where its CDS trigger timestamp is greater than the reference
    timestamp on the CDS Setup Execution Log corresponding to the
    process.

b.  For each Sampling Point associated to the Sampling Plan Sampling
    Point Asgmt:

    i.  Identify the Sub SSMPAs linked to the Sampling Point that are
        for the same Analyte associated to the Sampling Plan or, if the
        Sampling Plan is for an Analyte Group, additionally check if the
        Sub SSMPA (through its associated schedule) is for an Analyte
        that is part of the Analyte Group.

    ii. For each Sub SSMPA retrieved, disassociate all linked Results.
        Prior to disassociating each Result, update its CDS trigger
        timestamp.

    iii. Retrieve each retrieved Sub SSMPA=s parent SSMPA and update its
         CDS trigger timestamp. This sets up the triggers required for
         *CDS Setup* to associate Results to Sub SSMPAs, based on the
         new or revised Sampling Plan.