Research: Interface with RGGI

[MaeganWood64]

Original Description

• Public, non authenticated API's if possible -> pushing towards nothing custom
• Potentially be able to modify the existing facilities and emissions API to include the required information (DR and ADR Contractor Information)
• Need to pull the tables over to add to endpoints

Link to Draft RGGI Requirements: https://usepa.sharepoint.com/:w:/r/sites/CAMDCVPTeam/Shared%20Documents/Integration%20Coordination/RGGI/RGGI%20ECMPS%202.0/Draft%20RGGI%20Requirements.docx?d=wdc7a1b1f4a924fbeb76dc0a6e330438e&csf=1&web=1&e=Mc7gCF

Overview

1. Develop the JSON schema for the Facility Data Summary Data Endpoint endpoint payloads.
2. Develop the JSON schema for the Facility Data Specific Data Endpoint endpoint payloads.
3. Develop the JSON schema for the Quarterly Apportioned Emissions Data Specific Data Endpoint endpoint payloads.
4. Develop the JSON schema for the Quarterly Apportioned Emissions Data Last Updated Endpoint endpoint payloads.
5. Develop a CAMDBS API based on the CAMDBSP Facility Data Summary Data Endpoint endpoint described below.
6. Develop an EASEY API that uses the CAMDBS API for the CAMDBSP Facility Data Summary Data Endpoint endpoint described below.
7. Develop a CAMDBS API based on the CAMDBSP Facility Data Specific Data Endpoint endpoint described below.
8. Develop an EASEY API that uses the CAMDBS API for the CAMDBSP Facility Data Specific Data Endpoint endpoint described below.
9. Develop a EASEY API based on the Quarterly Apportioned Emissions Data Specific Data Endpoint endpoint described below.
10. Develop a EASEY API based on the Quarterly Apportioned Emissions Data Last Updated Endpoint endpoint described below.

Notes

1. Move NAICS codes from facility level to unit level.

CAMDBSP Facility Data

Summary Data Endpoint:

• https://api.epa.gov/easey/dev/facilities-mgmt/facilities?programCodeInfo=RGGI&lastUpdate=2023-11-21T17:04:11.827Z
• Accepts one or more ORIS Codes.
• Accepts one or more two character state codes.
• Accepts one or more two character program codes.
• Accepts a Last Updated Date-Time that indicates the time of the last call to the endpoint.
• When two or more of the four accepted parameters are selected, the search criteria ANDs the conditions from each selected parameter. ORs are used for multiple values of an individual parameter.
• Returns payload containing summary Facility data that matches the ORIS Code selections.
• When Last Updated Date-Time is specified, returns a Next Updated Date-Time to use in the next call to the endpoint.
• Note that RGGI should use this endpoint with "RGGI" as a program code, and with a Last Updated Date-Time.

Specific Data Endpoint:

• https://api.epa.gov/easey/dev/facilities-mgmt/facilities/3
• Accepts one or more ORIS Codes.
• Returns payload containing Facility and Unit data, needed for RGGI, that matches the ORIS Code selections.

General Information

Use CAMDECMPSMD.RGGI_XML_QRY data to determine returned data.

_CDXUSER

• Join to FAC_ID: PPL_ID, PLANT_PERSON -> FAC_ID
• Return Columns: CDX_USER_ID
• Possibly only return facility if CDX_USER row exists

COMPANY

• Join to FAC_ID: COMPANY_ID -> UNIT_OWNER -> UNIT -> FAC_ID
• ~Join to FAC_ID: COMPANY_ID -> PEOPLE -> PLANT_PERSON -> FAC_ID~
• Return Columns: COMPANY_NAME

PLANT

• Return Columns: COUNTY_CD, FAC_ID, FACILITY_NAME, LATITUDE, LONGITUDE, NAICS_CD, ORIS_CODE, STATE, STATE_ID

_PLANTPERSON (VW_RGGI_PLANT_PERSON)

• Contains FAC_ID
• Return Columns: BEGIN_DATE, END_DATE, FAC_PPL_ID, RESPONSIBILITY_ID
• Consult CAMDECMPSAUX.VW_RGGI_PLANT_PERSON for CAMDBSP to determine additional logic

PERSON

• Return Columns: ADD_DATE, FAC_PPL_ID, PPL_ID
• Columns No Longer Returned: ~ADDRESS1, ADDRESS2, CITY, COMPANY_ID, COUNTRY_CD, EMAIL_ADDRESS, EXTENSION, FAX_NUMBER, FIRST_NAME, LAST_NAME, MIDDLE_INITIAL, PHONE_NUMBER, STATE, SUFFIX, ZIP_CODE~
• Columns no longer returned are replaced by CDX_USER.CDX_USER_ID

UNIT

• Contains FAC_ID
• Return Columns: COMM_OP_DATE, COMR_OP_DATA, NAICS_CD (max), UNIT_ID, UNITID

_UNIT_BOILERTYPE

• Join to FAC_ID: UNIT_ID -> UNIT -> FAC_ID
• Return Columns: UNIT_TYPE_CD

_UNIT_OPSTATUS

• Join to FAC_ID: UNIT_ID -> UNIT -> FAC_ID
• Return Columns: BEGIN_DATE, OP_STATUS_CD

_UNITOWNER

• Join to FAC_ID: UNIT_ID -> UNIT -> FAC_ID
• Return Columns: BEGIN_DATE, COMP_ID, END_DATE, OWNER_TYPE_CD

_UNITPROGRAM

• Join to FAC_ID: UNIT_ID -> UNIT -> FAC_ID
• Condition: PRG_CD = ‘RGGI’
• Used to limit data to facility or unit that is RGGI affected.

_PROGRAMPARAMETER

• Used to limit data to facilities with RGGI program that expects CO2.
• This does not seem to add anything since all RGGI require CO2.
• Possibly needs to limit to active PROGRAM_PARAMETER instead of any. This would have affected New Jersey at one point.

Tables that trigger last updated pull on ADD_DATE/UPDATE_DATE:

• _CDXUSER
• COMPANY
• PERSON
• PLANT
• PLANT_PERSON
• UNIT
• UNIT_BOILER_TYPE
• UNIT_OP_STATUS
• UNIT_OWNER
• UNIT_PROGRAM

Quarterly Apportioned Emissions Data

Specific Data Endpoint:

• https://api.epa.gov/easey/dev/streaming-services/emissions/apportioned/quarterly?facilityId=3&year=2022&quarter=3&exclude=co2Rate
• Accepts one or more ORIS Codes.
• Accepts one or more Years, with one required.
• Accepts one or more Quarters, with one required.
• May need ability to specify quarterly data associated with a program.
• Returns payload containing Quarterly Unit Emissions data that matches the ORIS Code, Year, and Quarter selections.

Last Updated Endpoint:

• https://api.epa.gov/easey/dev/streaming-services/emissions/apportioned/quarterly/last-updated?date=2023-08-01T00:00:00Z
• Accepts Input (Last Pull) Timestamp in 'yyyy-mm-dd"T"hh24:mi:ss.fff"Z"' format.
• May need ability to specify quarterly data associated with a program.
• Returns Output (Next Pull) Timestamp in 'yyyy-mm-dd"T"hh24:mi:ss.fff"Z"' format.
• Returns payload containing Quarterly Unit Emissions data added on or after the Input Timestamp.

General Information:

• Pull Op Time, Op Hours, Heat Input Total, and CO2 Mass (lbs) from CAMDDMW.QUARTER_UNIT_DATA.
• ECMPS 1.0 used CAMDECMPS.UNIT_QUARTER instead of CAMDDMW.QUARTER_UNIT_DATA, which is an option source.
• Ensure that apportionment process sets the CAMDDMW.QUARTER_UNIT_DATA (or CAMDECMPS.UNIT_QUARTER) ADD_DATE to the system_timestamp, possibly as the default.
• Use the ADD_DATE to determine newly added rows for the last-updated endpoint.

Answered Questions

Requirements Outstanding Issues and Questions

1. The existing RGGI API provides the contact information for the DR/ADRs associated with the facility. The current plan for ECMPS 2.0 is to provide the CDX user ID in the API call and have RGGI query CDX for the contact information for the DR/ADR. This needs to be validated with the CDX folks. Note: we may need to move from CDX user ID to email address. Only include CDX User ID for authenticated calls and continue to provide the Person ID (Rep ID) to unauthenticated users.
2. How much additional information should we add to the units described in facilities call? The approach we are taking here mimics what is supplied on the Certificate of Representation form. For now, do not add information beyond what RGGI needs.
3. RGGI will require the concept of a lastUpdatedDateTime parameter on the both the facilities endpoint and the apportioned quarterly emissions endpoint. Explore feasibility of adding this parameter to the other emissions endpoints. Implement as specified in the requirements document except return the next last updated timestamp in the response header.

CAMD Questions

1. The programCodeInfo input parameter, when selected, currently requires an exact match with the programCodeInfo in each data object instead of matching a program code in the data object. So does not filter to RGGI easily. Should we change this, create a new program selection, or let RGGI filter the data? This appears to work correctly. Check again.
2. The Draft RGGI Requirements document proposes adding a lastUpdatedDateTime parameter to the "/facilities-mgmt/facilities" endpoint. ERG would typically implement a last-updated endpoint by returning the timestamp to use during the next call to the endpoint. Does CAMD have an issue with implementing a separate last-updated endpoint. If implemented as a separate endpoint, should the endpoint return full facility data or just facility summary information? Should the endpoint accept a Program Code parameter? Based on Dwayne's memory (not notes) CAMD preferred using a combined endpoint to return facility summary data, and another endpoint to return complete facility data.
3. Does CAMD have any issues with having the Facility Details API call a CBS API to get CAMDBSP detailed facility data instead of pulling the tables into the Cloud DB? CAMD (Mike) is okay with the API called by RGGI will call a CBS API.

RGGI Questions from ERG

1. The CAMD Business System currently stores NAICS codes at the unit level not the facility level. The RGGI facility web services return the max NAICS code from the RGGI units at the facility. Should the new facility API continue to return max value, or should it return all NAICS codes at the unit level? CAMD indicate to return NAICS at the unit level and inform RGGI about the change.

[MaeganWood64]

• Pushed out till June post ECMPS Beta March and May