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.

[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

• Resolved -- 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.
• Resolved -- 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

Data seems to be missing from core tables. 1) for test summary, chk_session_id values in check_log do not match those in the test_summary 2) for QCE, the qa_cert_event_id value in submission queue is missing from qa_cert_event table. 3) for TEE, the test_extension_exemption_idvalue in submission queue is missing from test_extension_exemption table.

• 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
  
• Resolved -- Table 2 in the feedback report for QA submissions shows duplicate rows for the same test for administrative override submissions
  
• Unable To Reproduce: The test sum id ('SPBGREEN-L-7D3555BA7ABA44E7BB4E1611382617D4') has a severity code of INFORM in the submission queue table and hence the feedback report has informational text. Can we retest this together?-- 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.
• Unable To Reproduce: Can we retest this together?-- The QA/Certification Events Evaluation Report section of QA feedback reports is not populating for informational message submissions
• Resolved -- 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

• Resolved -- 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
• Resolved -- 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".
• Resolved. The Evaluation Report section of the Feedback report uses existing evaluation functionality to generate the Facility Information table. The table looks exactly the same as it does currently for evaluation reports. Please create a separate ticket to correct both evaluation results and feedback reports. -- The Facility Information table for EM feedback reports looks odd
  

[lgiannini1]

@yonatan-dp Just resubmitting a QA test with admin overrides using RATA test 2017-SO2 for ORIS 3470 MS1, MS2, WAP8 at 9:17. The test evaluated with the following check results

ECMPS shows Informational Message as the Eval Status which looks like a separate issue.

Still waiting on the submission confirmation email

[yonatan-dp]

@lgiannini1, these changes have not yet been merged into the code base and deployed. There are further changes to be made yet based on Dwayne's investigations. He will update these ticket with his findings and the changes to be made.

[djw4erg]

ECMPS 1.0 Notes

ECMPS 1.0 generates Feedback Reports on the ECMPS Client pre-submission, not the ECMPS Host post-submission. This bypasses the fact that CRIT 1 submissions are not actually loaded into the Host tables for ECMPS 1.0, which would make it difficult to pull data on the Host for CRIT 1 submissions.

The ECMPS 1.0 Process is:

1. The submission process on the ECMPS Client produces the "Submission Feedback" report before the associated MP, QA or EM XML is submitted.
2. In the same Webservice call that submits the MP, QA or EM XML file, ECMPS 1.0 submits the Feedback Report to the ECMPS Host where the Feedback Reports are stored in the FEEDBACK_ARCHIVE_LOG table.
3. The Host generates the Submission Confirmation emails, pulling the Feedback Report attachment from the FEEDBACK_ARCHIVE_LOG.

ECMPS 2.0 Note

ECMPS 2.0 should produce the Feedback Report from CAMDECMPSWKS data as part of the submission process.

[lgiannini1]

@yonatan-dp

Monitoring Plan

QA

• ORIS 147, K-7 A 2023 Q3 linearity was submitted (ND7-Q3-2023-1) with no errors and does not appear in table 2 of the feedback report
  

• ORIS 628, 2GTA a 2023 Q1 RATA (CI2A-RNOX-0223) and QCE were submitted with informational messages and no check results are displayed in the evaluation results. Also, the RATA and QCE do not show up in table 2.
  

• ORIS 147, K-7 a 2024 Q1 TEE was submitted with a critical 1 error. The TEE does not show up in table 2.
  

• ORIS 628, 2GTB a 2023 Q3 Linearity was submitted with a critical 2 error, but the email text and feedback report are for critical 1 errors.
  
  

• ORIS 147, K-7 A 2023 Q3 linearity was submitted (OD7-Q3-2023-1) with admin overrides and does not appear in table 2 of the feedback report. The admin override LINEAR-23-A check result does not appear in the evaluation results
  

Emissions

• ORIS 147, K-7 2023 Q3 emissions were submitted with no errors and table 2 does not display the cumulative data summary values.

• ORIS ORIS 55409, CT1 2023 Q3 emissions were submitted with informational messages. Table 2 does not display the cumulative data summary values or check results in the evaluation results.
  

• ORIS 55409, CT1 2023 Q3 emissions were submitted with a critical 2 error, but the email text and feedback report are for critical 1 errors
  
  

• ORIS 7900, SH2 2023 Q3 emissions were submitted with admin overrides (HOURCV-37-A and HOURDHV-14-B). Table 2 does not display the cumulative data summary values or the check results in the evaluation results.