Continue development of Repository for Electronic Data Deliverables (REDD). A data repository has been established for all program analytical results. The next stage is to expand the repository to track monitoring site location and characteristics and develop web-based reporting to allow comprehensive presentation and analysis (e.g., trend evaluation, GIS mapping, etc.). This project would be classified as tactical because it allows for improved reporting of field and analytical data for all DWM program areas, and operational because it will result in more efficient reporting/evaluation of analytical results. In the near term, create or specify a repository for ADaPT EDD and other monitoring data for PRP. Longer term efforts would produce a consolidated Oracle-based data management system for all DWM program areas that includes a flexible import process for field and analytical data received as ADaPT EDDs and other formats with flexibility to support UIC or other groundwater and soil sampling ADaPT data imports needed for the Department. The analytical results table is in production. Attached at the end of this form please find the most recent requirements summary for adding testsite inventory tracking capability. Changed text**************************************************************** REDD OTIS Project Proposal for REDD Development Solid and Hazardous Waste programs have been using ADaPt for 7 years. We want to improve upon and extend the work that SW and HW have developed with the eDocument Log, OCULUS, WACS, ASP reports and automated email . notifications to be used by the entire Division using REDD (Repository of Electronic Data Deliverables). Currently REDD contains only a large table of ADaPT exported data with over 2 E6 records with improvements to the process. We need to develop REDD into a complete database that integrates well with WACS, STCM, and ERIC. We plan on building a web page that is used by HW, SW< PR and SW to be used by labs and consultants to submit ADaPT EDDs, used by FDEP and delegated staff and private consultants to track the submittal and the processing of ADaPT EDDs including what issues there may be with the data and how these issues were resolved, and include links to web based reports used by project managers and site managers to review groundwater, soil and air data. for completeness of reporting ( table of analytes vs. Monitoring Sites and Date sampled) including results and lab and FDEP assigned qualifiers), compliance/report which applies site specific criteria showing any results above the specific applied criteria or any results with lab or FDEP (ADaPT) assigned data QC qualifiers that have the potential to affect the quality of the data. This Any analyte listed in the compliance/exceedance report would include a link to show in a table all historic data for the specific Monitoring Testsite and analyte of interest. Here is the proposed unified Division workflow for the receipt of ADaPT EDDs/(electronic data deliverables). ESSA OCULUS Doc Log ADaPT Processing REDD email notification PHP reports. When ADaPT EDD Export Files are loaded into REDD, file information is loaded into table REDD.ADAPT_EDD_FILES and all records within the file are loaded into REDD.ADAPT_EDD_RECORDS. If the program area is identified as SW or HW then a database procedure stored in WACS is called and validates the records within the file and if validation is successful then another WACS database procedure is called and data is uploaded into the designated WACS tables. If the program area is identified as PRP then a different process takes place. Currently there are no testsite IDs in the export files to perform validation. Originally it was suggested that the inventory be built based on the data received from the ADaPT export files. As testsites are validated they will be entered into the permanent table REDD.PCT_MONITOR_WELL_TESTSITES. If the testsite is not found then a record is inserted into the REDD temporary table REDD.ADAPT_EDD_TESTSITE_TEMP for validation with an initial status of PENDING. The Designated user(s) would access the REDD testsite validation screen which displays all testsites in the REDD.ADAPT_EDD_TESTSITE_TEMP table. The current plan is to have PRP consultants fill out fillable PDF forms for existing Monitoring Testsites for existing facility wells to submit to the department. The Monitoring well information can be automatically extracted and uploaded to a temporary REDD Monitoring Testsite table. FDEP and contracted Site supervisors can then verify each monitoring well in REDD and the well is moved to a permanent Monitoring Well Testsite Inventory Table. Wells will be checked for proper names, lat-longs and physical well construction details. The user would update the status from PENDING to either VALID or REJECT. If the status is set to REJECT then the user will be required to document the reason in the comments field and that record will remain in the table so this process will not need to be repeated if additional data comes in on an EDD for the same testsite. When the status is set to VALID and the user commits, they will receive a confirmation alert message If they click the Yes button to confirm, then • the testsite is assigned an ID in REDD.PCT_MONITOR_WELL_TESTSITES • EDD records in REDD.ADAPT_EDD_RECORDS for the testsite are updated with the new ID in ADAPT_EDD_RECORDS table • the REDD.ADAPT_EDD_FILES processing comments field is updated with a reference to the testsite validation • the record is deleted from the REDD.ADAPT_EDD_TESTSITE_TEMP table • Navigation is transferred to the data entry screen for the monitor well testsite to complete entry of the remaining fields. When Navigation is set to the data entry screen, the new testsite is automatically queried and ready for the additional data entry to be performed. These screen shots and the table structure have been modeled after the WACS design. PRP Clicking on the return from WebShapeLite returns navigation to the oracle form and clicking on the “Launch/Populate GIS” button will populate the coordinates and verification fields. 7. Business Need & Objectives: Briefly describe the business need that is driving the request. Business needs should be considered in three categories: (1) Strategic – affecting the entire organization or key external stakeholders, PRP and WC staff, delegated county staff and external Consultants and site owners want to track the receipt and processing of environmental data. (2) Tactical – affecting multiple business units, and SW, HW< PRP, WC internally track receipt and processing of ADaPT EDDs, via automated emails, receive automated emails with links to ADaPt data, Monitoring Reports and links to review data for completeness of reporting, exceedances of analytes against applicable RCRA; GW, SW CTLs and SCTLs. Review of data with data qualifiers indicating the poss ability of data being affected by QC limit exceedances. (3) Operational – affecting specific business processes Comprehensive workflow, Efficient monitoring of what data has been received, completeness of data reported, any issues with quality of submitted data and the quality of the data. New Stuff************************************************************************* Constraints Constraints depend upon some decision making. When data is uploaded to REDD data is Validated, numerous checks are performed on specific data fields. currently for Solid and Hazardous Waste, Facility ID and Monitoring Testsite associations are also checked. We need to build the Monitoring Testsite Inventory into REDD for both Petroleum Cleanup and various Waste Cleanup programs. The question is, do we want to validate FAcility and Testsite IDs against WACS or REDD tables for Solid Waste and Hazardous Waste programs. For HW and SW I think it is most efficient to verify data against WACS and not have to copy data from WACS to REDD. The verification queries are merely run using different tables for SW and HW vs. PR and WC. This eliminates the issue of depending upon and further bogging down nightly table refresh processes. In the future if one would like all Monitoring Testsite information to reside in REDD, one would have to change all Monitoring Testsite related forms in WACS to access REDD tables. REDD table update permissions through Oracle forms would have to be granted to all WACS_Clerk and WACS_Admin user roles so that Tallahassee and District Solid Waste staff and Tallahassee Hazardous Waste staff could seamlessly maintain the HW and SW FAcility Monitoring Testsite Inventory. Additionally the following WACS tables are used to verify data uploaded to REDD for HW, PR SW and WC. (I have successfully uploaded WC lab data to REDD without any field EDDs). Data Validation ADaPT (export) WACS (import to WACS and REDD) Facility/Testsite Valid? tblWacs_Svl_SolidWaste_Testsite_Inventory WACS_SNAP.MONITOR_WELL_TESTSITES (SW & HW; add for PR and WC) Sample Type Valid? tblWacs_Svl_Sample_Type_Codes WACS_SNAP.WACS_SAMPLE_TYPE_CODES Storet Valid? TblWACS_STORET_Numbers WACS_SNAP.STORET_NUMBER_CODES Qualifier Valid? tblSvlLabQualifiers and WACS_SNAP.TEST_REMARK_TYPE_CODES tblWacs_Svl_Field_Parameter_Qualifier_Code Report Type Valid? tblWacs_Svl_Report_Type WACS_SNAP.WACS_REPORT_TYPE_CODES Which tables do we want to copy over to REDD? Copying tables over from WACS to REDD means that we are dependant upon overtaxed nightly refreshes. The STORET code table in WACS contains many historic STORET codes and STORET codes used by UIC. UIC uses STORET codes with multiple units for the same analyte and matrix. I suggest we import and maintain only ADaPT STORET codes in REDD. When STORET codes are added to ADaPT, they must then be added to both REDD and WACS. This is a bit intensive, but WACS is used by other programs that need their own STORET codes. The other tables are quite static and could be copied from WACS to REDD. an important verification that ADaPT rarely but still does miss is the "Qualifier verification?". ADaPT looks at data qualifiers on an individual basis. WACS verifies the Qualifiers based on their combination. There are qualifier combinations that are occasionally reported that are NOT valid. 7. Business Need & Objectives: Briefly describe the business need that is driving the request. Business needs should be considered in three categories: (1) Strategic – affecting the entire organization or key external stakeholders, PRP and WC staff, delegated county staff and external Consultants and site owners want to track the receipt and processing of environmental data. (2) Tactical – affecting multiple business units, and SW, HW< PRP, WC internally track receipt and processing of ADaPT EDDs, via automated emails, receive automated emails with links to ADaPT data, Monitoring Reports and links to review data for completeness of reporting, exceedances of analytes against applicable RCRA; GW, SW CTLs and SCTLs. Review of data with data qualifiers indicating the possibility of data being affected by QC limit exceedances. (3) Operational – affecting specific business processes Comprehensive workflow, Efficient monitoring of what data has been received, completeness of data reported, any issues with quality of submitted data and the quality of the data.