(DRAFT) ICIS Release 7.5: ICIS Biosolids Program Report Database Design

(DRAFT) ICIS Release 7.5: ICIS Biosolids Program Report Database Design

Summary of Changes

Overall: The table, ICIS_PROG_RPT, currently exists in ICIS and is used to track the Program Reports. For ICIS v7.5, the table will be updated with additional columns to track Appendix A data such as Reporting Period Start and End Dates and Electronic Submission Type. Additionally, several new child tables will be created to store new data requirements to support the electronic reporting Rule.

Background Notes: ICIS 7.5 will not reuse icis_prog_rpt_biosolid, which is being used by the existing program report. Eventually the existing program report will be removed from the system and replaced with the new Program report.

Updated ICIS Tables

Subject Area / Table Name / Comments /
System / ICIS_PROG_RPT / -  Add new columns to the table to track Total Volume, Reporting dates, Addl. Info
-  Add unique index on Date + Activity ID + Program Report Type + Prog. Rpt. Nmbr
PR Contacts / XREF_PROG_RPT_CONTACT, XREF_PROG_RPT_ADDRESS / -  Add new column and FK constraint for Affiliation Type Code
-  Add Begin_Date and End_Date to both tables for consistency w/ other similar C/A tbls
Ref Table / Ref_Program_Report_Type / -  Add new column, npdes_data_group_code, for EP-635, add a new row, and update the description for the existing row

(NEW!) ICIS Tables

Subject Area / Table Name / Comments /
Ref Tables / Ref_NPDES_Data_Group / Add new table for EP-659
Ref Tables / Ref_Reporting_Obligation / Add new table for EP-654
Ref Tables / Ref_Bio_Treatment_Process / Add new table for EP-636 – pertains to Program Reports
Ref Tables / Ref_Bio_Mgmt_Practice_Handler / Add new table for EP-638
Ref Tables / Ref_Bio_Mgmt_Practice_Type / Add new table for EP-638
Ref Tables / Ref_Bio_Mgmt_Practice_SubType / Add new table for EP-638
Ref Tables / Ref_Bio_Analytical_Method / Add new table for EP-637
Ref Tables / Ref_Bio_Pathogen_Class / Add new table for EP-640
Ref Tables / Ref_Bio_Pathogen_Reduction / Add new table for EP-641
Ref Tables / Xref_Bio_Pathgn_Reductn_Class / Add new table for EP-641
Ref Tables / Ref_Bio_Vector_Reduction / Add new table for EP-642
Ref Tables / Xref_Bio_Vector_Reductn_Class / Add new table for EP-642
Ref Tables / Ref_Prog_Deficiency_Category / Add new table for EP-522 (CM Deficiencies) and EP-524 (Biosolids)
Ref Tables / Ref_Prog_Deficiency / Add new table for EP-522 (CM Deficiencies) and EP-524 (Biosolids)
Ref Tables / Ref_Bio_Boundary_Distance / Add new table for EP-746 – pertains to Mgmt. Practices
Ref Tables / Ref_Electr_Submission_Type / Add new table for EP-411
Ref Tables / Ref_Bio_Container_Type / Add new table for EP-639
ICIS Program Report / XREF_Prog_Rpt_Obligation / New table to capture Reporting Obligations (1:M, child of Prog. Rpt tbl)
ICIS Program Report / XREF_Prog_Rpt_Bio_Tment / New table to capture Treatment Processes (1:M, child of Prog. Rpt tbl)
ICIS Program Report / XREF_Prog_Rpt_Bio_Method / New table to capture Analytical Methods (1:M, child of Prog. Rpt tbl)
ICIS Program Report / XREF_Prog_Rpt_Contact_Addr / New table to link Contact & Address for the program report
BioSolids PR – Mgmt Practice / ICIS_PROG_RPT_BIO_MGMT / New ICIS table to capture management practices – user can enter 1:M disposition reports, will have diff required elements based on Mgmt_Practice_Type
BioSolids PR– Mgmt Practice / XREF_Prog_Rpt_Bio_Mgmt_Def / New ICIS table to capture deficiency types and comments; will be multiple types of program deficiencies for the management practice (0:M child table of icis_prog_rpt_bio_mgmt)
BioSolids PR– Mgmt Practice / XREF_Prog_Rpt_Bio_Mgmt_Pathgn / New table to capture Pathogen Reductions, 1:M, child of Mgmt Practice table
BioSolids PR– Mgmt Practice / XREF_Prog_Rpt_Bio_Mgmt_Vector / New table to capture Vector Reductions, 1:M, child of Mgmt Practice table
BioSolids PR– Mgmt Practice / XREF_Prog_Rpt_Bio_Mgmt_Contact / Added 10/20/16 for SSUID C/A
BioSolids PR– Mgmt Practice / XREF_Prog_Rpt_Bio_Mgmt_Address / Added 10/20/16 for SSUID C/A
BioSolids PR– Mgmt Practice / XREF_Prog_Rpt_Bio_Mgmt_Cnt_Add / Added 10/20/16 for SSUID C/A
Ref Tables / Ref_Bio_Analy_Method_Category / Added 11/7/16 for Reporting
Ref Tables / Ref_Bio_Treat_Proc_Category / Added 11/7/16 for Reporting

Ref Table Design Details

(NEW) REF_NPDES_DATA_GROUP

Table Comment: This table stores the unique code/description that identifies the types of NPDES program data that are required to be reported by the facility. This data element will record each NPDES Data Group that the facility is required to submit. For example, when a POTW is required to submit a Discharge Monitoring Report, Sewage Sludge/Biosolids Annual Program Report, Pretreatment Program Report, and Sewer Overflow/Bypass Event Report, the values for this data element for this facility will be 3, 4, 7, and 9.

Column Name / Data Type / Length / Nullable / Constraint / DED Comment
NPDES_Data_Group_Code / VARCHAR2 / 3 / N / PK / This is the code indicating the NPDES eReporting Rule Data Group that applies to the data.
NPDES_Data_Group_Desc / VARCHAR2 / 200 / N / This is the description of the NPDES eReporting Rule Data Group that applies to the data.
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.
Sort_Order / NUMBER / N / Column indicating the default order in which the data groups should be sorted on screens and reports.

(NEW) REF_REPORTING_OBLIGATION

Table Comment: This table stores the types of reasons permittees are or are not required to report the data.

Column Name / Data Type / Length / Nullable / Constraint / DED Comment
Reporting_Obligation_Code / VARCHAR2 / 3 / N / PK / This is the code indicating the reporting obligation reason.
Reporting_Obligation_Desc / VARCHAR2 / 100 / N / This is the description of the reporting obligation reason.
Program Code / VARCHAR2 / 9 / N / FK to Ref_ Program / The program to which the reporting obligation applies (e.g., Biosolids, CAFO).
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.

(NEW) REF_BIO_TREAT_PROC_CATEGORY

Table Comment: This table stores the unique codes/descriptions that identify the category for the Biosolids or sewage sludge treatment process or processes at the facility.

Column Name / Data Type / Length / Nullable / Constraint / DED Comment /
Bio_Treat_Proc_Category_Code / VARCHAR2 / 3 / N / PK / The unique code that identifies the category for the Biosolids or sewage sludge treatment process or processes at the facility.
Bio_Treat_Proc_Category_Desc / VARCHAR2 / 100 / N / The description of the category for the Biosolids or sewage sludge treatment process or processes at the facility.
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.

(NEW) REF_BIO_TREATMENT_PROCESS

Table Comment: This table stores the unique codes/descriptions that identify the biosolids or sewage sludge treatment process or processes at the facility.

Column Name / Data Type / Length / Nullable / Constraint / DED Comment /
Bio_Treatment_Process_Code / VARCHAR2 / 3 / N / PK / The unique code that identifies the biosolids or sewage sludge treatment process or processes at the facility
Bio_Treatment_Process_Desc / VARCHAR2 / 100 / N / The description of the biosolids or sewage sludge treatment process or processes at the facility
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.
Bio_Treat_Proc_Category_Code / VARCHAR2 / 3 / N / FK to Ref_Bio_Treat_Proc_Category / The unique code that identifies the category for the Biosolids or sewage sludge treatment process or processes at the facility.

(NEW) REF_BIO_MGMT_PRACTICE_HANDLER

Table Comment: This table stores the types of Biosolids or Sewage Sludge handlers/preparers.

Column Name / Data Type / Length / Nullable / Constraint / DED Comment
Bio_Mgmt_Practice_Handler_Code / VARCHAR2 / 3 / N / PK / This is the code indicating the type of Biosolids or Sewage Sludge handlers/preparers.
Bio_Mgmt_Practice_Handler_Desc / VARCHAR2 / 100 / N / The description of the Biosolids or Sewage Sludge handlers/preparers type.
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.

(NEW) REF_BIO_MGMT_PRACTICE_TYPE (Land App, Surface Disp., Incineration, Other Mgmt Practice)

Table Comment: This table stores the unique codes/descriptions that identify the type of biosolids or sewage sludge management practice or practices (e.g., land application, surface disposal, incineration) used by the facility. The facility will separately report the management practice for each biosolids or sewage sludge form and pathogen class. This data element will also identify the management practices used by surface disposal site owners/operators (see 40 CFR 503.24).

Column Name / Data Type / Length / Nullable / Constraint / DED Comment
Bio_Mgmt_Practice_Type_Code / VARCHAR2 / 3 / N / PK / The unique code that identifies the type of biosolids or sewage sludge management practice or practices (e.g., land application, surface disposal, incineration) used by the facility. The facility will separately report the management practice for each biosolids or sewage sludge form and pathogen class. This data element will also identify the management practices used by surface disposal site owners/operators (see 40 CFR 503.24).
Bio_Mgmt_Practice_Type_Desc / VARCHAR2 / 100 / N / The description of the type of biosolids or sewage sludge management practice or practices (e.g., land application, surface disposal, incineration) used by the facility. The facility will separately report the management practice for each biosolids or sewage sludge form and pathogen class. This data element will also identify the management practices used by surface disposal site owners/operators (see 40 CFR 503.24).This is the description of the Management Practice used for a volume of Biosolids or Sewage Sludge.
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.

(NEW) REF_BIO_MGMT_PRACTICE_SUBTYPE

Table Comment: This table stores additional detail about the type of Management Practice used for a volume of Biosolids or Sewage Sludge.

Column Name / Data Type / Length / Nullable / Constraint / DED Comment /
Bio_Mgmt_Practice_SubType_Code / VARCHAR2 / 3 / N / PK / This is the code indicating additional detail about the type of Management Practice used for a volume of Biosolids or Sewage Sludge.
Bio_Mgmt_Practice_SubType_Desc / VARCHAR2 / 100 / N / This is the description of additional detail about the type of Management Practice used for a volume of Biosolids or Sewage Sludge.
Bio_Mgmt_Practice_Type_Code / VARCHAR2 / 3 / N / FK to Ref_Bio_ Mgmt_Practice_Type / This is the code indicating the Management Practice Type about which the additional detail is being provided.
Mgmt_Practice_Text_Indicator / VARCHAR2 / 1 / N / CK con- Y/N / This flag indicates that if a Management Practice Sub Type Code is selected the user will be required to enter the Other Management Practice Detail field.
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.

(NEW) REF_BIO_ANALY_METHOD_CATEGORY

Table Comment: This table stores the unique codes/descriptions for the categories of the analytic methods used by the facility to analyze regulated parameters at the facility.

Column Name / Data Type / Length / Nullable / Constraint / DED Comment /
Bio_Analy_Method_Category_Code / VARCHAR2 / 3 / N / PK / The unique code for the category of the analytic methods used by the facility to analyze regulated parameters at the facility.
Bio_Analy_Method_Category_Desc / VARCHAR2 / 100 / N / The description of the category of the analytic methods used by the facility to analyze regulated parameters at the facility.
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.

(NEW) REF_BIO_ANALYTICAL_METHOD

Table Comment: This table stores the unique codes/descriptions that identify each of the analytic methods used by the facility to analyze enteric viruses, fecal coliforms, helminth ova, Salmonella sp., and other regulated parameters. For example, EPA requires facilities to monitor for the certain parameters, which are listed in Tables 1, 2, 3, and 4 at 40 CFR 503. 13 and Tables 1 and 2 at 503.23. This data element stores each analytic methods used by the facility only once for each annual report (not for each parameter measurement).

Column Name / Data Type / Length / Nullable / Constraint / DED Comment /
Bio_Analytical_Method_Code / VARCHAR2 / 3 / N / PK / The unique code that identifies the analytic method used by the facility to analyze enteric viruses, fecal coliforms, helminth ova, Salmonella sp., and other regulated parameters as reported on the Biosolids Program Report. For example, EPA requires facilities to monitor for the certain parameters, which are listed in Tables 1, 2, 3, and 4 at 40 CFR 503. 13 and Tables 1 and 2 at 503.23.
Bio_Analytical_Method_Name / VARCHAR2 / 100 / N / The analytical method number or author or other reference name of the analytic method used by the facility to analyze enteric viruses, fecal coliforms, helminth ova, Salmonella sp., and other regulated parameters as reported on the Biosolids Program Report. For example, EPA requires facilities to monitor for the certain parameters, which are listed in Tables 1, 2, 3, and 4 at 40 CFR 503. 13 and Tables 1 and 2 at 503.23.
Bio_Analytical_Method_Desc / VARCHAR2 / 300 / N / The description of the analytic method used by the facility to analyze enteric viruses, fecal coliforms, helminth ova, Salmonella sp., and other regulated parameters as reported on the Biosolids Program Report. For example, EPA requires facilities to monitor for the certain parameters, which are listed in Tables 1, 2, 3, and 4 at 40 CFR 503. 13 and Tables 1 and 2 at 503.23.
Bio_Analytical_Parameter_Name / VARCHAR2 / 100 / N / The name of the parameter to which the analytic method used by the facility to analyze enteric viruses, fecal coliforms, helminth ova, Salmonella sp., and other regulated parameters applies as reported on the Biosolids Program Report. For example, EPA requires facilities to monitor for the certain parameters, which are listed in Tables 1, 2, 3, and 4 at 40 CFR 503. 13 and Tables 1 and 2 at 503.23.
Method_Text_Indicator / VARCHAR2 / 1 / N / CK con- Y/N / The column indicating whether the user must enter an additional text description of the analytic method used at the facility. (Y = additional text required, N = no additional text).
Status_Flag / VARCHAR2 / 1 / N / CK con- A/I / The flag indicating whether the record is active or inactive.
Bio_Analy_Method_Category_Code / VARCHAR2 / 3 / N / Fk to Ref_Bio_ Analy_Method_Category / The unique code for the category of the analytic methods used by the facility to analyze regulated parameters at the facility.

(NEW) REF_BIO_PATHOGEN_CLASS