Submission Confirmation Emails and Feedback Reports

[esaber76]

During the 1/25/2024 weekly ECMPS Support meeting with EPA, there was a discussion on what to do with Feedback Report Maintenance in CAT.

The PDF feedback report that is included with 1.0 submission confirmation emails is not currently included in 2.0. This could be due to the new CDX process.

The only users that would have access to submission related information from CDX - Submission History would be the submitter themselves.

Need to determine if:

• It's even possible to include something similar to the PDF feedback report attachment in the submission confirmation emails.
• Or, just expand the data currently shown in the emailed Submission Confirmation Report.

[annalbrecht]

Chris would like to not have PDF attachments. Option 2 is preferred:

• expand the data currently shown in the emailed Submission Confirmation Report.

Next steps is to look at the current feedback reports & document what is there. That info should be added to the email (if possible)-- may need to talk to Mike/CDX people to see what is possible.

[alangmaid]

Chris changed his mind. He would like to keep the attachments as HTML using existing report generation when possible.

[annalbrecht]

Discussed emails with Chris on 4/9. Ann will write up the requirements. The run book may also need to be updated in association with this ticket. https://github.com/US-EPA-CAMD/easey-docs/blob/develop/CAMDRunbook.md#112-reports

Feedback Letters.zip

Discussed the recipients list, EASEY-255, and Chris had no edits.

[annalbrecht]

This is an example of the email that currently gets sent out in 2.0. This info should be shown in the attachment, not in the email itself.

[annalbrecht]

The language for the email to which the feedback reports are attached.

Feedback Email Text.docx

[annalbrecht]

See attached feedback report templates. All the header and table information should update based on the specific submission (e.g., facility name, Feedback Status Level, date, etc.).

The Feedback Report text should update based on the file type submitted (MP, QA, EM) and the Feedback Status Level (No Errors, Crit 1, etc.).

The report layout/formatting doesn't have to look exactly like the attachments-- it should be based on the existing 2.0 layout/formatting.

Feedback Report Templates.zip

[annalbrecht]

Acceptance Testing: Make submissions, or find recent ones, that fulfill the following criteria. Ensure the email text and Feedback Report attachment text is correct based on the specific submission information.

• MP file with: -- Crit 1 error -- Crit 2 error -- Informational Message -- Non Critical Errors -- Administrative Override -- No Errors
• QA file with: -- Crit 1 error -- Crit 2 error -- Informational Message -- Non Critical Errors -- Administrative Override -- No Errors
• EM file with: -- Crit 1 error -- Crit 2 error -- Informational Message -- Non Critical Errors -- Administrative Override -- No Errors

[yonatan-dp]

Questions/Confirmations

1. The feedback email template states that the names and email address of people the email was sent to AND Cc’ed to should be included in the email. Currently, only the 'to' email address is available in the submission set.
2. This is for confirmation: I am retrieving the data for the [Unit/Stack/Pipe] value in the email subject from the camd.plant table that is referenced in camdecmps.monitor_plan, based on submission_set.mon_plan_id. Is this correct?
3. Table 1 of the feedback attachment lists the following values to be included. Not sure where the data for these come from:
   • Submission Type in the format of (EM for 2021 QTR 1). Reporting period not available on the submission set (only in submission queue) and the table lists submission set level information.
   • How does one identify if Resubmission Required. Is this based on on eval_score_code? If so, which codes require a resubmission?
   • Where do we get EPA Analyst information?
4. The email templates only specify templates to use when the submission is successful (whether there are errors or not). However, there is a situation where the submission itself fails (for instance if an error occurs when copying data from workspace to official). In that case, should the subject and the content of the email change? Will there by any attachments generated in this case as the submitted data is probably in an inconsistent or invalid state (or may not be any data in official)?

[djw4erg]

Answers to Questions/Confirmations comment:

1. Including the CC information in the body of the email should occur when ticket #6183 is handled.
2. I use the following to get facility info and location names for a monitoring plan.

   select  fac.Oris_Code,
       fac.Facility_Name,
       coalesce( unt.Unitid, stp.Stack_Name ) as Location_Name
   from  camcecmps.MONITOR_PLAN_LOCATION mpl
       join camdecmps.MONITOR_LOCATION loc
         on loc.Mon_Loc_Id = mpl.Mon_Loc_Id
       left join camd.UNIT unt
         on unt.Unit_Id = loc.Unit_Id
       left join camdecmps.STACK_PIPE stp
         on stp.Stack_Pipe_Id = loc.Stack_Pipe_Id
       join camd.PLANT fac
         on fac.Fac_Id in ( unt.Fac_Id, stp.Fac_Id )
   where  mpl.Mon_Plan_Id = @monPlanId

3. See Below
   1. Submission sets contain multiple submission files. I am not an expert on the 2.0 implementation, but SUBMISSION_SET table represents each whole set, the SUBMISSION_QUEUE table represents the individual files within a submission set, and the two tables are connected on SUBMISSION_SET_ID. The Submission Type is a combination of information derived from the two tables.
      • Monitoring Plans: SUBMISSION_QUEUE.PROCESS_CD.
      • Emissions: SUBMISSION_QUEUE.PROCESS_CD and through SUBMISSION_QUEUE.RPT_PERIOD_ID the REPORTING_PERIOD.PERIOD_DESCRIPTION.
      • QA Tests: SUBMISSION_QUEUE.PROCESS_CD (SUBMISSION_QUEUE.TEST_SUM_ID is not null)
      • QA Cert Events: SUBMISSION_QUEUE.PROCESS_CD (SUBMISSION_QUEUE.QA_CERT_EVENT_ID is not null)
      • Test Extensions and Exemptions: SUBMISSION_QUEUE.PROCESS_CD (SUBMISSION_QUEUE.TEST_EXTENSION_EXEMPTION_ID is not null)
   2. The Resubmission Required value is based on the SEVERITY_CD value for the CHECK_SESSION associated with the data. If the SEVERITY_CD is 'CRIT1' or 'CRIT2', resubmission is required.
      • Use the following to link to CHECK_SESSION.
      • Monitoring Plans: MONITOR_PLAN.CHK_SESSION_ID
      • Emissions: EMISSION_EVALUATION.CHK_SESSION_ID
      • QA Tests: TEST_SUMMARY.CHK_SESSION_ID
      • QA Cert Events: QA_CERT_EVENT.CHK_SESSION_ID
      • Test Extensions and Exemptions: TEST_EXTENSION_EXEMPTION.CHK_SESSION_ID
      • Note: I believe that you should use tables in the "Official/Submitted" schema, which means using CHECK_SESSION in the CAMDECMPSAUX schema instead of the CAMDECMPSWKS schema.
   3. Note: in ECMPS 1.0 non-CRIT1 QAT, QCE and TEE in a submission set were submitted in a single file and resulted in a single Submission Confirmation Feedback report for that file. CRIT1 QAT, QCE and TEE, when submitted, were also submitted in a single file with a corresponding Submission Confirmation Feedback report. The ECMPS 2.0 version of the Submission Confirmation Feedback report should group all non-CRIT1 QAT, QCE and TEE into a single report, and if they exist, group all CRIT1 QAT, QCE and TEE into a single report.
      • For the non-CRIT1 QAT, QCE and TEE Submission Confirmation Feedback report, the Feedback Status Level should match the highest severity level for the included QAT, QCE and TEE based on the Severity Level in the camdecmpsmd.SEVERITY_CODE table.
   4. Replace the EPA Analyst information in the tables with a link to the "Emissions Monitoring Contacts".
4. This email would not be produced if the submission fails.

[yonatan-dp]

Made the following changes as part of this ticket: Problem: When an MP, Test summary, TEE, and QCE are submitted together, only the MP seems to be successfully submitted (and the rest seem to be ignored). Observation: In easey-camd-services, when queueing the submission, it creates submission set and submission queue records for each file type that is submitted. The order of the processing is: MP, test summary, qce, tee, emissions, and mats. The queuing fails when processing test summary because the logic expects there to be data in the camdecmpswks.qa_supp_data table for the submitted testSumId. This causes an exception and the qce, tee, emissions, and mats submissions are never processed.
Solution Short term: I put in a check to prevent an exception if the expected test summary, qce, tee, emissions, or mats records are not found. Long term: @djw4erg will look into why the camdecmpswks.qa_supp_data table is missing an entry for that testSumId. An evaluation action should have created a record in the camdecmpswks.qa_supp_data table.

[djw4erg]

Yonatan Data Source Question from Teams Chat

I just noticed that the sample feedback reports have an additional table for QA/Cert and TEE. I had only noticed the Table 2 for emissions. Do you know if there is a stored procedure or view that has this data I can reuse?

Response

1. The attached ECMPS 1.0 stored procedure, GetFeedbackReportDataQA, returns the data for the report.
   • VW_RPT_FEEDBACK Section: Returns Table 1 data and Severity specific text.
   • VW_RPT_QATEST_FEEDBACK Section: Returns QAT Evaluation Results data.
   • VW_RPT_QA_CERT_EVENT_FEEDBACK Section: Returns QCE Evaluation Results data.
   • VW_RPT_TEST_EXTENSION_EXEMPTION_FEEDBACK Section: Returns TEE Evaluation Results data.
   • VW_RPT_QATEST_LIST Section: Returns QAT Table 2 data.
   • VW_RPT_QA_CERT_EVENT_LIST Section: Returns QCE Table 2 data.
   • VW_RPT_TEST_EXTENSION_EXEMPTION_LIST Section: Returns TEE Table 2 data.
2. The following ECMPS 1.0 feedback reports show versions of the feedback reports for submissions that contain at least one each of QAT, QCE and TEE.
   1. The attached QAT_QCE_and_TEE_Feedback_Report.pdf feedback report shows the format when the submission contains QAT, QCE, and TEE data, each with some errors.
   2. The attached QAT_Errors_With_QCE_and_TEE_Feedback_Report.pdf feedback report shows the format when the submission contains QAT with errors, and QCE and TEE without errors.
   3. The attached QCE_Errors_With_QAT_and_TEE_Feedback_Report.pdf feedback report shows the format when the submission contains QCE with errors, and QAT and TEE without errors.
   4. Note: The situation to produce the first was created, but the latter two were existing reports. I did not find an example for TEE with errors, and QAT and QCE without errors.

Stored Procedure GetFeedbackReportDataQA

ECMPS.Reports.GetFeedbackReportDataQA.sql.txt

Example Feedback Reports

QAT_QCE_and_TEE_Feedback_Report.pdf

QAT_Errors_With_QCE_and_TEE_Feedback_Report.pdf

QCE_Errors_With_QAT_and_TEE_Feedback_Report.pdf

[djw4erg]

Yonatan MATS Question from Teams Chat

Hi Dwayne, while you are looking at my question above, I have one more The ticket and your notes all imply that we should only have MP, EM, and QA (test, cert, and tee) in the submission queue table. MATS is not mentioned anywhere (and also the feedback email samples also only refer to MP, EM, and QA submissions. However, I see that there are MATS entries in the submission queue table (select * from camdecmpsaux.submission_queue sq where sq.process_cd = 'MATS').

So, the question is, should I be ignoring MATS records when processing for feedback emails? If I should include them (meaning send a separate email for MATS records just like the others), then where can I get the feedback report examples for those?

Response

The ticket only talks about MP, EM, and QA in the Submission Queue table because they are the only types of submissions included on the report. That does not mean that the Submission Queue should not contain the MATS submissions. In fact the Submission Queue table definitely should include MATS and the report should ignore MATS. MATS submissions are treated differently from Part 75 submissions. Part 75 is the CAPD (Clean Air and Power Division) rule, while MATS is an OAQPS (Office of Air Quality Planning and Standards) rule. OAQPS pulls our MATS submissions and they are responsible for any notifications.

[yonatan-dp]

Yonatan Data Source Question from Teams Chat

I just noticed that the sample feedback reports have an additional table for QA/Cert and TEE. I had only noticed the Table 2 for emissions. Do you know if there is a stored procedure or view that has this data I can reuse?

Response

1. The attached ECMPS 1.0 stored procedure, GetFeedbackReportDataQA, returns the data for the report.

   • VW_RPT_FEEDBACK Section: Returns Table 1 data and Severity specific text.
   • VW_RPT_QATEST_FEEDBACK Section: Returns QAT Evaluation Results data.
   • VW_RPT_QA_CERT_EVENT_FEEDBACK Section: Returns QCE Evaluation Results data.
   • VW_RPT_TEST_EXTENSION_EXEMPTION_FEEDBACK Section: Returns TEE Evaluation Results data.
   • VW_RPT_QATEST_LIST Section: Returns QAT Table 2 data.
   • VW_RPT_QA_CERT_EVENT_LIST Section: Returns QCE Table 2 data.
   • VW_RPT_TEST_EXTENSION_EXEMPTION_LIST Section: Returns TEE Table 2 data.

2. The following ECMPS 1.0 feedback reports show versions of the feedback reports for submissions that contain at least one each of QAT, QCE and TEE.

   1. The attached QAT_QCE_and_TEE_Feedback_Report.pdf feedback report shows the format when the submission contains QAT, QCE, and TEE data, each with some errors.
   2. The attached QAT_Errors_With_QCE_and_TEE_Feedback_Report.pdf feedback report shows the format when the submission contains QAT with errors, and QCE and TEE without errors.
   3. The attached QCE_Errors_With_QAT_and_TEE_Feedback_Report.pdf feedback report shows the format when the submission contains QCE with errors, and QAT and TEE without errors.
   4. Note: The situation to produce the first was created, but the latter two were existing reports. I did not find an example for TEE with errors, and QAT and QCE without errors.

Stored Procedure GetFeedbackReportDataQA

ECMPS.Reports.GetFeedbackReportDataQA.sql.txt

Example Feedback Reports

QAT_QCE_and_TEE_Feedback_Report.pdf

QAT_Errors_With_QCE_and_TEE_Feedback_Report.pdf

QCE_Errors_With_QAT_and_TEE_Feedback_Report.pdf

[yonatan-dp]

Hi @djw4erg, ECMPS.Reports.GetFeedbackReportDataQA.sql.txt stored procedure references vw_rpt_feedback_check_log which I am not able to locate in the dev database or in easey-db-scripts. I am suspecting this has not been ported to postgres yet. Can you please attach this file and any referenced by it?

[yonatan-dp]

From Dwayne:

Here is the SQL Server script for VW_RPT_FEEDBACK_CHECK_LOG.

USE [ECMPS] GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_RPT_FEEDBACK_CHECK_LOG]')) DROP VIEW [dbo].[VW_RPT_FEEDBACK_CHECK_LOG] GO

CREATE VIEW [dbo].[VW_RPT_FEEDBACK_CHECK_LOG] AS SELECT DISTINCT cs.CHK_SESSION_ID, cs.SESSION_BEGIN_DATE, cs.SESSION_END_DATE, cl.CHK_LOG_ID, cl.RULE_CHECK_ID, cs.PROCESS_CD, rc.CATEGORY_CD, catcd.CATEGORY_CD_DESCRIPTION, IsNull(cl.RESULT_MESSAGE, cl.CHK_LOG_COMMENT) AS [MESSAGE], cl.TEST_SUM_ID, cl.MON_LOC_ID, cs.MON_PLAN_ID, cs.RPT_PERIOD_ID, dbo.ConcatMonitorPlanLocations(cs.MON_PLAN_ID) AS MP_LOCATIONS, cc.CHECK_TYPE_CD + '-' + CAST(cc.CHECK_NUMBER AS varchar) + '-' + cl.CHECK_RESULT AS CHECK_IDENTIFIER, cl.CHECK_RESULT, cc.CHECK_TYPE_CD, cc.CHECK_NUMBER, cl.SEVERITY_CD, sc1.SEVERITY_CD_DESCRIPTION, cs.SEVERITY_CD as SESSION_SEVERITY_CD, sc2.SEVERITY_CD_DESCRIPTION as SESSION_SEVERITY_CD_DESCRIPTION, cs.EVAL_SCORE_CD, cl.BEGIN_DATE, cl.OP_BEGIN_DATE, cl.OP_BEGIN_HOUR, cl.OP_END_DATE, cl.OP_END_HOUR, rc.CHECK_CATALOG_ID FROM CHECK_SESSION cs LEFT OUTER JOIN CHECK_LOG cl ON cl.CHK_SESSION_ID = cs.CHK_SESSION_ID LEFT OUTER JOIN SEVERITY_CODE sc1 ON sc1.SEVERITY_CD = cl.SEVERITY_CD LEFT OUTER JOIN SEVERITY_CODE sc2 ON sc2.SEVERITY_CD = cs.SEVERITY_CD LEFT OUTER JOIN RULE_CHECK rc ON rc.RULE_CHECK_ID = cl.RULE_CHECK_ID LEFT OUTER JOIN CATEGORY_CODE catcd ON catcd.CATEGORY_CD = rc.CATEGORY_CD
LEFT OUTER JOIN CHECK_CATALOG cc ON cc.CHECK_CATALOG_ID = rc.CHECK_CATALOG_ID
GO

Here is the SQL Server script for the ConcatMonitorPlanLocations function.

USE [ECMPS] GO

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[ConcatMonitorPlanLocations]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[ConcatMonitorPlanLocations] GO

SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO

CREATE FUNCTION [dbo].[ConcatMonitorPlanLocations](@Mon_Plan_Id [dbo].[DB_ID_KEY]) RETURNS varchar WITH EXECUTE AS CALLER AS begin declare @Units varchar(max) declare @StackPipes varchar(max) declare @result varchar(max)

select @StackPipes = ltrim(rtrim(dbo.ConcatMonitorPlanStackPipes(@Mon_Plan_Id))) select @Units = ltrim(rtrim(dbo.ConcatMonitorPlanUnits(@Mon_Plan_Id)))

if (@StackPipes <> '' and @Units <> '') select @result = @StackPipes + ', ' + @Units else if( @StackPipes <> '' ) select @result = @StackPipes else select @result = @Units

return @result end GO

-- Permissions GRANT REFERENCES ON [dbo].[ConcatMonitorPlanLocations] TO [ECMPS_DBUser] GRANT EXECUTE ON [dbo].[ConcatMonitorPlanLocations] TO [ECMPS_DBUser] GO

Note that ConcatMonitorPlanLocations puts the Stack/Pipes before the Units. I believe that ECMPS 2.0 tends to put the units before the stack/pipes.

The following is what I usually use for the same thing when looking at data. It puts the stack/pipes before the units.

MP Locations Example

SQL select fac.oris_code, fac.facility_name, ( select string_agg( coalesce( unt.unitid, stp.stack_name ), ', ' order by case when unt.unitid is null then 0 else 1 end, coalesce( unt.unitid, stp.stack_name ) ) from camdecmps.MONITOR_PLAN_LOCATION mpl join camdecmps.MONITOR_LOCATION loc on loc.mon_loc_id = mpl.mon_loc_id left join camd.UNIT unt on unt.unit_id = loc.unit_id left join camdecmps.STACK_PIPE stp on stp.stack_pipe_id = loc.stack_pipe_id where mpl.mon_plan_id = pln.mon_plan_id ) as locations, pln.mon_plan_id, pln.fac_id from camd.PLANT fac join camdecmps.MONITOR_PLAN pln on pln.fac_id = fac.fac_id order by oris_code, locations

Change: order by case when unt.unitid is null then 0 else 1 end, coalesce( unt.unitid, stp.stack_name )
    To: order by case when unt.unitid is null then 1 else 0 end, coalesce( unt.unitid, stp.stack_name )

In the string_agg's order by to list Units first.

[lgiannini1]

• Submission Confirmation Emails are sent with the correct subject line, email text, and HTML Feedback Report attachments for the following submission types:
  • MP with no errors
  • QA with no errors
  • EM with no errors
  • MP with informational message
  • QA with informational message
  • EM with informational message
  • MP with critical errors level 1
  • QA with critical errors level 1
  • EM with critical errors level 1
  • MP with critical errors level 2
  • QA with critical errors level 2
  • MP with non-critical error
  • QA with non-critical error
  • EM with non-critical error
  • MP with administrative override
  • EM with administrative override
• When a QA test with an evaluation status of No errors and a QA Cert Event with an evaluation status of Informational/ critical errors are submitted together, the QA submission confirmation email correctly lists the higher severity in the email subject.

Issues

Monitoring Plan Submissions

• Submitting a MP with critical 2 errors returns submission emails with inconsistent results in the subject lines and text in the feedback report related to the error severity level.
  • For ORIS 628, 2GTA a CAPAC-3-C crit 2 error was generated and submitted. The subject of the submission email says "No errors". The text in the "Explanation of Your Feedback Status Level Listed in Table 1" also has the text for "No errors" submissions.
  • The same CAPAC-3-C error was generated for ORIS 50900, 001 and submitted. The subject of the submission email says "Critical Error Level 1". The text in the "Explanation of Your Feedback Status Level Listed in Table 1" also has the text for "critical error level 1" submissions.
  • A subsequent MP submission with crit 2 errors for ORIS 628, 2GTA did return an email with the correct subject line and text in the feedback report.
• MP feedback reports for submissions with no errors include the Monitoring Plan Evaluation Report section. The text in the Submission Status section also mentions the evaluation results which should be removed.

QA Submissions

• Table 2 in the feedback report for QA submissions is not populated with the tests/ QA cert events/ TEEs that were submitted for no errors, informational message, non-critical error, and critical error submissions
• Table 2 in the feedback report for QA submissions shows duplicate rows for the same test for administrative override submissions
• Submissions made for QA tests with administrative override submissions returns emails with the incorrect subject line and text in the feedback report related to the error severity level. For ORIS 3470, WAP8, MS1, MS2 RATA test number 2018-SO2-1 has administrative overrides, but the email/ feedback report text is for informational message submissions.
• The QA/Certification Events Evaluation Report section of QA feedback reports is not populating for informational message submissions
• QA feedback reports for submissions with no errors include the QA/Certification Tests Evaluation Report section. The text in the Submission Status section also mentions the evaluation results which should be removed.

EM Submissions

• EM feedback reports for submissions with no errors include the Emissions Evaluation Report. The text in the Submission Status section also mentions the evaluation results which should be removed.
• EM feedback reports for submissions with informational messages do not display the check results table under the Emissions Evaluation Report section
• Submitting an EM file with critical 2 errors returns submission emails with the incorrect subject line and text in the feedback report related to the error severity level. For ORIS 628, 2GTA, a DAYCAL-17-D crit 2 error was generated and submitted, but the submission email says "Critical Error Level 1". The text in the "Explanation of Your Feedback Status Level Listed in Table 1" also has the text for "critical error level 1".
• The Facility Information table for EM feedback reports looks odd