Add Additional Date Fields to Evaluation Queue Table

[mark-hayward-erg]

Overview

Current Evaluation Process Steps

1. The ECMPS UI calls the [camd-services]/evaluate endpoint.
2. The [camd-services]/evaluate endpoint creates a EVALUATION_SET row for the call and populates EVALUATION_QUEUE with a child row for the MP if the payload's submitMonPlan (misnamed) is true, and a row for each QAT, QCE, TEE, and EM in the payload.
   • EVAL_STATUS_CD in the EVALUATION_QUEUE rows are set to INQ.
   • STATUS_CD in the EVALUATION_QUEUE rows are set to the following:
     • For MP, QUEUED.
     • For QAT, QCE and TEE, PENDING if submitMonPlan (misnamed) is true, otherwise QUEUED.
     • For EM, PENDING if submitMonPlan (misnamed) is true or QAT, QCE or TEE are part of the evaluation set, otherwise QUEUED.
   • SUBMITTED_ON in the EVALUATION_QUEUE rows are set to the current time.
   • Other column values are initialized.
3. The "Evaluation Queue" Quartz job does the following:
   1. Determine the capacity to start another evaluation and proceeds if the capacity and another 'QUEUED' EVALUATION_QUEUE row exists, and proceeds with additional steps if both are true.
   2. Creates a Quartz job for the next EVALUATION_QUEUE row ordered by MPs, QAs and EMs and then by SUBMITTED_ON in each group.
   3. The "Evaluation Queue" Quartz job does the following:
      1. Sets the EVALUATION_QUEUE row's STATUS_CD to 'WIP'.
      2. Performs the evaluation processing for each file type.
         • MP
         • Updates the MONITOR_PLAN EVAL_STATUS_CD to "WIP".
         • Performs the MP evaluation.
         • Updates the MONITOR_PLAN EVAL_STATUS_CD based on the Check Session for the MP.
         • Updates the STATUS_CD to "QUEUED" for any EVALUATION_QUEUE for the SUBMISSION_SET_ID with a PROCESS_CD of "QA".
         • If no EVALUATION_QUEUE for the SUBMISSION_SET_ID with a PROCESS_CD of "QA" existed, updates the STATUS_CD to "QUEUED" for any EVALUATION_QUEUE for the SUBMISSION_SET_ID with a PROCESS_CD of "EM" and a STATUS_CD of "PENDING".
         • QA (For one of QAT, QCE or TEE based on which one of testSumId, qaCertId or testExtensionExemption is populated)
         • Updates the TEST_SUMMARY, QA_CERT_EVENT or TEST_EXTENSION_EXEMPTION EVAL_STATUS_CD to "WIP".
         • Performs the QAT, QCE or TEE evaluation.
         • Updates the TEST_SUMMARY, QA_CERT_EVENT or TEST_EXTENSION_EXEMPTION EVAL_STATUS_CD based on the Check Session for the QAT, QCE or TEE.
         • EM
         • Resets EVAL_STATUS to "PENDING" in EVALUATION_QUEUE for the EM if an EM EVALUATION_QUEUE row exist for the MON_PLAN_ID and a prior reporting period. No additional processing occurs.
         • Updates the EMISSION_EVALUATION EVAL_STATUS_CD to "WIP".
         • Performs the EM evaluation.
         • Updates the EMISSION_EVALUATION EVAL_STATUS_CD based on the Check Session for the EM.
         • Performs refreshes needed based on the evaluation occurring.
         • Updates the the EVALUATION_QUEUE EVAL_STATUS_CD to "QUEUED" for the EM EVALUATION_QUEUE row for the MP with the earliest reporting period.
      3. Sets the EVALUATION_QUEUE row's STATUS_CD to 'COMPLETE'.

CAMDECMPSAUX Database Changes

Table	Column	Type	Update
EVALUATION_QUEUE	QUEUED_TIME	Timestamp w/o TZ	Renamed from SUBMITTED_ON
EVALUATION_QUEUE	STARTED_TIME	Timestamp w/o TZ	Add
EVALUATION_QUEUE	COMPLETED_TIME	Timestamp w/o TZ	Add
EVALUATION_QUEUE	NOTE	Timestamp w/o TZ	Add
EVALUATION_QUEUE	NOTE_TIME	varchar(1000)	Add

Notes

• The NOTE and NOTE_TIME columns are populated when an issue occurred and the submission was not completed.

New Emission Process Steps

1. The ECMPS UI calls the [camd-services]/evaluate endpoint.
2. The [camd-services]/evaluate endpoint creates a EVALUATION_SET row for the call and populates EVALUATION_QUEUE with a child row for the MP if the payload's submitMonPlan (misnamed) is true, and a row for each QAT, QCE, TEE, and EM in the payload.
   • EVAL_STATUS_CD in the EVALUATION_QUEUE rows are set to INQ.
   • STATUS_CD in the EVALUATION_QUEUE rows are set to the following:
     • For MP, QUEUED.
     • For QAT, QCE and TEE, PENDING if submitMonPlan (misnamed) is true, otherwise QUEUED.
     • For EM, PENDING if submitMonPlan (misnamed) is true or QAT, QCE or TEE are part of the evaluation set, otherwise QUEUED.
   • _QUEUED_TIME in the EVALUATION_QUEUE rows are set to the current time._
   • Other column values are initialized.
3. The "Evaluation Queue" Quartz job does the following:
   1. Determine the capacity to start another evaluation and proceeds if the capacity and another 'QUEUED' EVALUATION_QUEUE row exists, and proceeds with additional steps if both are true.
   2. Creates a Quartz job for the next EVALUATION_QUEUE row ordered by MPs, QAs and EMs and then by SUBMITTED_ON in each group.
   3. The "Evaluation Queue" Quartz job does the following:
      1. Sets the following EVALUATION_QUEUE row's values.
         • STATUS_CD to 'WIP'.
         • _STARTED_TIME to the current time._
      2. Performs the evaluation processing for each file type.
         • MP
         • Updates the MONITOR_PLAN EVAL_STATUS_CD to "WIP".
         • Performs the MP evaluation.
         • Updates the MONITOR_PLAN EVAL_STATUS_CD based on the Check Session for the MP.
         • Updates the STATUS_CD to "QUEUED" for any EVALUATION_QUEUE for the SUBMISSION_SET_ID with a PROCESS_CD of "QA".
         • If no EVALUATION_QUEUE for the SUBMISSION_SET_ID with a PROCESS_CD of "QA" existed, updates the STATUS_CD to "QUEUED" for any EVALUATION_QUEUE for the SUBMISSION_SET_ID with a PROCESS_CD of "EM" and a STATUS_CD of "PENDING".
         • QA (For one of QAT, QCE or TEE based on which one of testSumId, qaCertId or testExtensionExemption is populated)
         • Updates the TEST_SUMMARY, QA_CERT_EVENT or TEST_EXTENSION_EXEMPTION EVAL_STATUS_CD to "WIP".
         • Performs the QAT, QCE or TEE evaluation.
         • Updates the TEST_SUMMARY, QA_CERT_EVENT or TEST_EXTENSION_EXEMPTION EVAL_STATUS_CD based on the Check Session for the QAT, QCE or TEE.
         • EM
         • Resets EVAL_STATUS to "PENDING" in EVALUATION_QUEUE for the EM if an EM EVALUATION_QUEUE row exist for the MON_PLAN_ID and a prior reporting period. No additional processing occurs.
         • Updates the EMISSION_EVALUATION EVAL_STATUS_CD to "WIP".
         • Performs the EM evaluation.
         • Updates the EMISSION_EVALUATION EVAL_STATUS_CD based on the Check Session for the EM.
         • Performs refreshes needed based on the evaluation occurring.
         • Updates the the EVALUATION_QUEUE EVAL_STATUS_CD to "QUEUED" for the EM EVALUATION_QUEUE row for the MP with the earliest reporting period.
      3. _If the evaluation completed successfully, sets the following EVALUATION_QUEUE row's values._
         • STATUS_CD to 'COMPLETE'
         • _COMPLETED_TIME to the current time._
      4. _If the evaluation did not complete successfully, sets the following EVALUATION_QUEUE row's values._
         • NOTE with a description of the problem.
         • _NOTE_TIME to the current time._

Acceptance Criteria

1. The EVALUATION_QUEUE QUEUED_TIME, STARTED_TIME and COMPLETED_TIME are updated, and NOTE and NOTE_TIME are null for any successfully evaluated file of the following:

   • MP
   • QAT
   • QCE
   • TEE
   • EM

2. The EVALUATION_QUEUE QUEUED_TIME, STARTED_TIME, NOTE and NOTE_TIMEare updated, and COMPLETED_TIME is null for any unsuccessfully evaluated file of the following:

   • MP
   • QAT
   • QCE
   • TEE
   • EM

Evaluation Payload JSON Schema

For [camd-services]/evaluate endpoint

{
  "items": [
    {
      "monPlanId": "string",
      "submitMonPlan": true,
      "testSumIds": [
        "string"
      ],
      "qceIds": [
        "string"
      ],
      "teeIds": [
        "string"
      ],
      "emissionsReportingPeriods": [
        "string"
      ],
      "matsBulkFiles": [
        0
      ]
    }
  ],
  "userId": "string",
  "userEmail": "string"
}

[djw4erg]

Additional Description Changes

• Make the 5 second sleep configurable and set it to 1 second for now.
• Determine why the check engine jobs all appear to be running on just one of the two quartz-scheduler app instances