The Combined Sewer Overflow (CSO) group of the Indiana Department of Environmental Management (IDEM)/Office of Water Quality (OWQ)/Permits Branch developed a database module to track Long Term Control Plan (LTCP) milestones, internal IDEM milestones, compliance reviews, information related to CSO outfalls, and general information related to each CSO community.
This database is in “TEMPO” – Tools for Environmental Management and Protection Organizations. TEMPO is a software application created by CGI Technology and Solutions Inc., which has been customized by IDEM to meet the needs of the CSO group.
Information logged in the CSO TEMPO Database follows:
- LTCP CSO tasks that must be completed by each community to meet the requirements of the LTCP are entered into the database and can be tracked. These “tasks” include the LTCP implementation tasks, LTCP approval/completion dates, and various other milestone dates. Each task has a description of what activity must be completed by the permittee as well as the required start date and due date. The completion date for the tasks will be entered by IDEM staff when the communities provide notification that each task is finished.
- General information related to each CSO community that frequently needs to be searched, such as the type of enforceable document through which the LTCP schedule is enforced (i.e., agreed order, state judicial agreement, or federal consent decree), NPDES permit number, associated Virtual File Cabinet (VFC) document number, LTCP design goals, and major/minor facility designation.
- CSO outfall information for each CSO community, including receiving stream, basin, sub-basin, watershed, county, latitude/longitude, active/inactive status, treated/untreated status, etc.
- Compliance reviews completed by the CSO staff. These reviews include audits and compliance reviews of fully implemented LTCPs to ensure they achieve their design goals.
Results to Date:
Various reports can be generated using the information that has been entered into the database.
The current reports include:
- Compliance Status Report: This report can be used to report the Compliance Status for CSO communities. The report can account for both types of compliance reviews and can be manipulated to provide data for various date ranges and other screening criteria. The report example below is a Compliance Status Report for all compliance reviews completed in 2012.
- Document Attributes Report: This report can retrieve a list of facilities by enforceable document (agreed order, state judicial agreement, federal consent decree), or can search LTCP design goals, associated VFC document numbers, or NPDES permit numbers. This report has the ability to accommodate other general information as the program moves forward. The example report below shows all agreed orders related to LTCPs.
- Metrics Report: Generates a report which lists the total CSO communities, total approved LTCPs, total LTCPs remaining to be approved, and total LTCPs fully implemented. Frequently, upper management is asked to provide status reports on CSO related topics. The Metrics Report provides information that is readily obtainable without the need to query staff.
- Outfalls Report: Filters on this report can be set to provide a wide range of information regarding CSO outfalls. Based on the data logged in TEMPO (as indicated above), this report can generate location, waterbody information, active status, treatment status, etc. by community, county, receiving stream, or watershed. This report is particularly useful for public information requests. The example report below shows all CSOs and related CSO statuses in Madison County
- WAL (Work Activity Log) Tasks Reports: This report is the most used for CSO program activities. The filters on this report provide a limitless range of information. For example, this report can provide overdue tasks for each community, can provide completed tasks for each community, can search a common task among all communities, can give “universe” of tasks for the whole CSO program, etc. This report is being used monthly to keep track of which communities may be behind in their LTCP implementation. This report also is used to track completion of internal tasks, such as audits and LOC reviews, for staff scheduling purposes. Another useful way to use the report is to search a specific type of project. Below is a typical report generated at the end of each month by each project manager to determine which facilities are behind schedule.
Possible Future Capabilities:
- Various future enhancements to the IDEM CSO Tracking database are possible. The TEMPO software application is Oracle-based and can be interfaced with the EPA Integrated Compliance Information System (ICIS) system. This would enable future data exchange between TEMPO and ICIS.
- Also, the outfall information in TEMPO contains geographic coordinates that allows integration with ArcGIS to display maps containing CSO related information. This part of the program is currently under development.
- This example corresponds to the Outfall Report above, showing the CSOs in Madison County with their related status.
Contact:
Holly Zurcher
Indiana Department of Environmental Management
