Implement inventory and program change logic for MP reporting frequency and eval status

Certain facility/unit inventory changes made via CBS may have a direct impact on the evaluation status of existing monitoring plans in ECMPS 2.0 and/or the reporting frequency associated with existing monitoring plans in ECMPS 2.0. In some cases, changes in CBS may require sources to make changes to affected monitoring plans prior to making future submissions.

On the CBS side, when these changes are made, the affected facility and/or unit is logged in the CAMDAUX.INVENTORY_STATUS_LOG table along with a code the indicates the type of change that was made. This table is included in the CDC process that syncs data between the NCC Oracle database and the cloud.gov PG database.

CBS data changes that only affect values displayed in ECMPS 2.0:

PLANT - Updates to record in the CAMD.PLANT table (e.g., facility name change)

CBS data changes that may affect monitoring plan evaluation status:

INVENTORY - Inserts, updates, or deletions of records in the CAMD.UNIT table (or records in child tables)
UNIT_PROGRAM - Inserts, updates, or deletions of records in the CAMD.UNIT_PROGRAM table.

CBS data changes that may affect monitoring plan reporting frequency:

UNIT_PROGRAM - Inserts of records in the CAMD.UNIT_PROGRAM table for programs that require annual reporting
- only affects existing monitoring plans that have an Ozone Season reporting frequency

CBS data changes that affect the facility to which a monitoring plan is associated:

MOVED_UNIT - Inserts of records into UNIT_LOGICAL_MOVE
- will be handled separately by #5706
- affects FAC_ID on MONITOR_PLAN and STACK_PIPE records

Developer Notes:

CAMDAUX.INVENTORY_STATUS_LOG table already exists
- will be added to the CDC process that syncs data from the NCC Oracle database via #5575
Create a new quartz-scheduler job to process inventory changes from CBS
- run every 5 minutes (unless previous execution is still running)
- log execution details in CAMDAUX.JOB_LOG
- job should perform the following logic:
- Retrieve all CAMDAUX.INVENTORY_STATUS_LOG records with an INVENTORY_STATUS_LOG_ID value greater than the LAST_PROCESSED_INVENTORY_STATUS_LOG_ID from the previous execution of the job (stored in JOB_LOG.ADDITIONAL_DETAILS) and a DATA_TYPE_CD of INVENTORY or UNIT_PROGRAM
  - sort records by INVENTORY_STATUS_LOG_ID (i.e., process records in the same order that CBS added them)
- For each INVENTORY_STATUS_LOG record
  - Call stored procedure "camdecmpswks.update_mp_eval_status_and_reporting_freq", passing the UNIT_ID and DATA_TYPE_CD from the INVENTORY_STATUS_LOG record
- after processing all available INVENTORY_STATUS_LOG records
  - update JOB_LOG.ADDITIONAL_DETAILS to set LAST_PROCESSED_INVENTORY_STATUS_LOG_ID to the last INVENTORY_STATUS_LOG_ID that was processed.
Modify stored procedure "camdecmpswks.update_mp_eval_status_and_reporting_freq" as follows:
- Add parameters V_UNIT_ID and V_DATA_TYPE_CD
- If V_DATA_TYPE_CD is 'UNIT_PROGRAM'
- execute query to determine if any reporting frequency changes are needed
- query should find all MONITOR_PLAN_REPORT_FREQ records that match the following criteria:
  - associated with V_UNIT_ID
  - locate via MONITOR_LOCATION and MONITOR_PLAN_LOCATION
  - monitoring plan is active (MONITOR_PLAN.END_RPT_PERIOD_ID is NULL)
  - V_UNIT_ID is associated with at least one UNIT_PROGRAM record that:
  - requires annual reporting (PROGRAM_CODE.OS_IND <> 1)
  - ends after the quarter of UNIT_PROGRAM.UNIT_MONITOR_CERT_BEGIN_DATE
- for each found MONITOR_PLAN_REPORT_FREQ record
  - if the quarter of the relevant UNIT_PROGRAM.UNIT_MONITOR_CERT_BEGIN_DATE is prior to the begin quarter of the MONITOR_PLAN_REPORT_FREQ record
  - the existing ozone season reporting frequency record exists entirely after the UMCBD
  - delete the MONITOR_PLAN_REPORT_FREQ record
    - CAMDECMPS.MONITOR_PLAN_REPORT_FREQ table
    - CAMDECMPSWKS.MONITOR_PLAN_REPORT_FREQ table
  - otherwise
  - the existing ozone season reporting frequency record overlaps the UMCBD
  - end the MONITOR_PLAN_REPORT_FREQ record with the 4th quarter of the year preceding the year of the UMCBD
    - CAMDECMPS.MONITOR_PLAN_REPORT_FREQ table
    - CAMDECMPSWKS.MONITOR_PLAN_REPORT_FREQ table
  - add a new MONITOR_PLAN_REPORT_FREQ with a REPORT_FREQ_CD of 'Q' and a begin quarter of the UMCBD
  - CAMDECMPS.MONITOR_PLAN_REPORT_FREQ table
  - CAMDECMPSWKS.MONITOR_PLAN_REPORT_FREQ table
- Always perform evaluation status update for any active monitoring plans linked to V_UNIT_ID (locate via MONITOR_LOCATION and MONITOR_PLAN_LOCATION)
- set NEEDS_EVAL_FLG to 'Y'
- set EVAL_STATUS_CD = 'EVAL'
- SUBMISSION_AVAILABILITY_CD = 'REQUIRE'
- Update both CAMDECMPS.MONITOR_PLAN and CAMDECMPSWKS.MONITOR_PLAN

Acceptance Criteria:

NOTE 1: CBS testing to confirm that the NCC Oracle table is being updated correctly has already been done (see https://camddevelopment.atlassian.net/browse/EASEY-254 and related tickets).
NOTE 2: Testing to confirm that the CDC job is correctly updating the cloud.gov PG table will be covered by #5575.
Add records to CAMDAUX.INVENTORY_STATUS_LOG on cloud.gov PG database (either via a script or via CBS actions)
- INVENTORY records (e.g., UNIT table changes) to test that monitoring plan evaluation status is correctly updated
- update UNIT (or child table) data to test scenarios that would require changes to the monitoring plan (i.e., CCO date change)
- test for a monitoring plan that is currently checked out and a monitoring plan that is not currently checked out
- UNIT_PROGRAM records to test that monitoring plan reporting frequency is correctly updated AND hat monitoring plan evaluation status is correctly updated
- add new UNIT_PROGRAM record that requires annual reporting (e.g., ARP) for a unit that is currently linked to a monitoring plan that only requires ozone season reporting
  - test with UMCBD that will cause a deletion of a MONITOR_PLAN_REPORT_FREQ record
  - UMCBD falls before the MPRF record
  - test with UMCBD that will cause the ending of a MONITOR_PLAN_REPORT_FREQ record
  - UMCBD falls during the MPRF record
- update UNIT_PROGAM data to test scenarios that would require changes to the monitoring plan (i.e., UMCBD or ERBD date change)
- test both for a monitoring plan that is currently checked out and a monitoring plan that is not currently checked out

Original Description:

Context

Add inventory status log table
Create a procedure that will execute logic to handle MP reporting frequency and eval status based on records inserted into the table

The logic will need to be collected from Mark

The camdecmpswks.update_mp_eval_status_and_reporting_freq procedure should be updated before it is used. The last block of the procedure attempts to update `camdecmpswks.monitor_plan with the following logic:

    UPDATE camdecmpswks.MONITOR_PLAN
    SET needs_eval_flg = 'Y',
        eval_status_cd = 'EVAL',
        submission_availability_cd = 'GRANTED',
        userid = V_USERID,
        update_date = V_CURRENT_DATETIME
    WHERE mon_plan_id IN (
        SELECT mon_plan_id FROM (
            SELECT DISTINCT unit_id
            FROM camdaux.inventory_status_log
            WHERE data_type_cd IN ('INVENTORY')
        ) AS d
        JOIN camdecmps.monitor_location AS ml USING(unit_id)
        JOIN camdecmps.monitor_plan_location AS mpl USING(mon_loc_id)
        JOIN camdecmps.monitor_plan AS mp USING(mon_plan_id)
        WHERE mp.end_rpt_period_id IS null
    );

The first WHERE condition is filtering to plans with a mon_plan_id in a list of unit_ids, which will return zero rows.

Additionally, the submission_availability_cd is set to REQUIRE in version 1.0, rather than GRANTED.

US-EPA-CAMD / easey-ui

Implement inventory and program change logic for MP reporting frequency and eval status #5576

Context