tlageman-epa
commented
3 years ago @JanellC to your question above. We already answered that and I believe it is accurately reflected in the rest of the requirements in this ticket. Yes, transaction begin and end date will be separate fields like in the hourly emissions api.
I noticed that there is a mistake under the transaction end date validation: "Validate End date is not equal to or greater than start date" should be "Validate End date is equal to or greater than start date" which matches the error message.
Lastly, I noticed there were some questions above about confirming the transaction types but also see that information is provided later in the ticket.
JanellC
Original estimate: 40 PI 6 Estimate: 5
System Increment Definition of Ready (DoR) Before being accepted into a Program Increment each feature:
[x] Meets the INVEST criteria (Independent, Negotiable, Valuable, Estimate-able, Small, Testable)
[x] Has a clearly defined user
[x] Has an estimate
[x] Has acceptance criteria
[x] Has sufficient context, such as
User Research (not needed at this time)
Screen Mockups/UI Design (re-use AH, review for differences)
System requirement's (in progress)
Data Conversion (completed)
[x] Traceable to feature, epic or PI
[x] Dependencies are identified
[x] Component reusability assessed
Feature Benefit
Validate Allowance Transactions API parameters to ensure that the request is valid
Provide front-end user interface to access Allowance Transactions data
Context
Refer to EASEY Out Production List
Allowance Transactions System Requirements
Assumptions:
Public API Requirements
API Parameters
Program: Optional, multiple values allowed, retired/active allowance programs, values: ARP, CAIRNOX, CAIROS, CAIRSO2, CSNOX, CSNOXOS, CSOSG1/2, CSSO2G1/2, NBP, OTC, TXSO2
Account Type: Optional, multiple values allowed, values: Facility Account, Unit Account, General Account, Reserve Account, Surrender Account, ALL, searches both buyer and seller account types
Account Number: Optional, multiple values allowed, searches both buyer and seller Account numbers
Facility ID (ORIS code): Optional, multiple values allowed, searches both buyer and seller ORIS codes
Owner/ Operator: Optional, multiple values allowed, query will return all records that match owner OR operator specified, searches both buyer and seller Owner/Op
State: Optional, multiple values allowed, 2 letter postal abbreviation, searches both buyer and seller states
Vintage Year: Optional, multiple values allowed, Format: YYYY, 1995 or greater
Transaction Begin Date: Required, single value, Range: 03/23/1993-current date Format: YYYY-DD-MM
Transaction End Date: Required, single value, Range: 03/23/1993-current date, Format: YYYY-DD-MM
Transaction Type: Optional, multiple values allowed, Value: Initial Allocation, Emissions Deduction, Private Transfer, New Unit Aside Allocation, etc. - VAUES NEED CONFIRMATION FROM EPA
UI Requirements
UI Parameters Data Type Required Button/ Drop down Value: Emissions, Allowance, Compliance, default blank Single Select
Data Sub-Type Required Drop down Value:Allowance Holdings, Allowance Transactions, Account Information, default blank Single Select
Program Optional Drop down, default blank Multi select Value: ARP, CAIRNOX, CAIROS, CAIRSO2, CSNOX, CSNOXOS, CSOSG1/2, CSSO2G1/2, NBP, OTC, TXSO2 (retired/active allowance programs), default blank
Account Type Optional Drop down, default blank Multi Select Value: Facility Account, Unit Account, General Account, Reserve Account, Surrender Account, ALL
Account
Account Number Dynamic search Search is a contains search on buyer and seller accounts Results are displayed in Account Name (Account Number) format Results populate in a list, parameters populates when a value is selected from the list (results list should stay at top of page/visible in current view, possible use pagination to keep window small) Search is not case sensitive If no results are found, the system will indicate so. “No accounts match your search.” Multi select
Account Name Dynamic search Search is a contains search on buyer and seller accounts Results are displayed in Account Name (Account Number) format Results populate in a list, parameters populates when a value is selected from the list (results list should stay at top of page/visible in current view, possible use pagination to keep window small) Search is not case sensitive If no results are found, the system will indicate so. “No accounts match your search.” Multi select
Facility
Facility ID (ORIS Code) Dynamic search Search is a contains search Results populate in a list, parameters populates when a value is selected from the list Results are displayed in Facility Name (ORIS Code) format If no results are found, the system will indicate so. “No facilities match your search.” Multi Select
Facility Name Dynamic search Search is a contains search Results are displayed in Facility Name (ORIS Code) Results populate in a list, parameters populates when a value is selected from the list (results list should stay at top of page/visible in current view, possible use pagination to keep window small) Search is not case sensitive If no results are found, the system will indicate so. “No facilities match your search.” Multi select
Owner/ Operator
Owner Dynamic search Search is a contains search on buyer and seller accounts Results populate in a list, parameters populates when a value is selected from the list (results list should stay at top of page/visible in current view, possible use pagination to keep window small) Search is not case sensitive If no results are found, the system will indicate so. “No accounts match your search.” Multi select
Operator Dynamic search Search is a contains search on buyer and seller accounts Results populate in a list, parameters populates when a value is selected from the list (results list should stay at top of page/visible in current view, possible use pagination to keep window small) Search is not case sensitive If no results are found, the system will indicate so. “No accounts match your search.” Multi select
State Dynamic search Search is a “begins with” search Results populate in a list, parameters populates when a value is selected from the list
Results display the full state name (Texas, not TX) Multi Select
Vintage Year: Text field. Allows ranges and individual years (YYYY-YYYY, YYYY, YYYY) Format: YYYY Optional Valid date range: 1995 to current year Multi select (multiple 2019,2020, range 2017-2020, and indiv. Years 2015) Breadcrumb display: Vintage year (no years displayed)
Transactions
Transaction Begin Date Required Single select Format: MM/DD/YYYY Valid date range:03/23/1993-current date
Transaction End Date Required Single Select Format: MM/DD/YYYY Valid date range: 03/23/1993-current date
Transaction Type Optional combo box contains search Value: Initial Allocation, Emissions Deduction, Private Transfer, New Unit Aside Allocation, etc.
Change Criteria The user will have the ability to clear one, many, or all filter criteria If the user changes data type or sub data type, then:
The filter criteria displayed updates accordingly No previously entered filter criteria is retained
If a user changes data type or sub data type, then filter criteria is cleared
If the user chooses to clear one or more filter criteria before preview is selected, then the system clears the criteria from the filter and updates any remaining dependent criteria accordingly.
If the user chooses to clear one or more filter criteria after preview is selected, then the system clears the criteria from the filter AND download is disabled until data is previewed.
Data Preview
Trigger: A user has entered, at a minimum, the required parameters, and selected the option to preview data. When the system identifies at least one record that matches the criteria, then for each record the system will display the data below and ordered by PRG_CODE> TRANSACTION_ID > VINTAGE_YEAR > START_BLOCK
PRG_CODE TRANSACTION_ID TRANSACTION_TOTAL TRANSACTION_TYPE SELL_ACCT_NUMBER SELL_ACCT_NAME SELL_ACCOUNT_TYPE SELL_FACILITY_NAME SELL_ORISPL_CODE SELL_STATE SELL_EPA_REGION SELL_SOURCE_CAT SELL_OWN_DISPLAY_NAME BUY_ACCT_NUMBER BUY_ACCT_NAME BUY_ACCOUNT_TYPE BUY_FACILITY_NAME BUY_ORISPL_CODE BUY_STATE BUY_EPA_REGION BUY_SOURCE_CAT BUY_OWN_DISPLAY_NAME TRANSACTION_DATE VINTAGE_YEAR START_BLOCK END_BLOCK TOTAL_BLOCK
Results will display record count Results are limited to 100 records Results in preview will have a scroll functionality to show all columns/rows NOTE: NEED TO ID COLUMNS FOR HOVER TEXT AND DISPLAY TEXT OUT OF SCOPE: Results may be sorted in alphabetical ascending and descending order CAN THEY BE FILTERED? IF SO BY WHAT CRITERIA? - no additional filtering OUT OF SCOPE: Results are paginated
Data Labels- Refer to the CAMPD_ DataLabels_AllowanceCompliance file here
File Selection Trigger: The user has at least one record that matches the search criteria, and has previewed the results The user has the option to select the file output in JSON or CSV format. CSV is the default file type
Download Trigger: The user has selected a file output type When the user selects the option to download, the system creates a file in the format specified containing all records that match the parameters and downloads the file After a user has chosen to download data, then the system will display the preview of the downloaded results.
Validations:
Program:
Error Message: One or more < insert property > are not valid. Refer to the list of available < insert property > for valid values [placeholder for link to endpoint] FV-100
Account Type:
Error Message: Error Message: One or more < insert property > are not valid. Refer to the list of available < insert property > for valid values [ placeholder for link to endpoint ] FV-100
Account Number:
Error Message: Error Message: One or more < insert property > are not valid. Refer to the list of available < insert property > for valid values [ placeholder for link to endpoint ] FV-100
Facility ID (ORIS code): Validate that the value is a number If the value is a number, validate that the format is correct positive integer (no negative numbers, not 0) whole numeric value (no letters or special characters) no more than 6 digits
Error Message: Error Message: One or more < insert property > are not valid. Refer to the list of available < insert property > for valid values [placeholder for link to endpoint] FV-100
Owner/ Operator: No Validations
State: Validate that the value matches a valid 2 letter postal abbreviation for at least 1 of the 50 states, PR, or DC
Error Message: One or more states are not valid. Use the two letter postal abbreviation (use TX, not Texas). FV-101
Vintage Year: Validate that the date is in the format YYYY Validate that all years are equal to or greater than 1995
Error Message: One or more < insert property > are not in the < insert format >. Ensure all < insert property > are in the < insert format > FV-102
Error Message: Update one or more < insert property > to < insert valid range > FV-105
Transaction Begin Date: Validate that one, and only one, value exists for the parameter Validate that the date is in the format YYYY-MM-DD Validate that the date is valid Range: 03/23/1993-current
Error Message 1: should not be null or undefined FV-106 Error Message: Ensure that < insert property > is in the FV-108
Error Message: Update the < insert property > to < insert valid range > FV-104
Transaction End Date: Validate that one, and only one, value exists for the parameter Validate that the date is in the format YYYY-MM-DD Validate that the date is valid Range: 03/23/1993-current Validate End date is equal to or greater than start date
Error Message 1: should not be null or undefined FV-106 Error Message: Ensure that is in the FV-108
Error Message: Error Message: Update the < insert property > to < insert valid range > FV-104
Error Message Enter an < insert property > that is greater than or equal to the < constraint > FV-103
Transaction Type:
Error Message: One or more < insert property > are not valid. Refer to the list of available < insert property > for valid values [placeholder for link to endpoint] FV-100
Results (applicable to API and UI): When the system identifies at least one record that matches the criteria, then for each record the system will display the data below and ordered by PRG_CODE> TRANSACTION_ID > VINTAGE_YEAR > START_BLOCK
PRG_CODE SELL_FACILITY_NAME BUY_FACILITY_NAME BUY_OWN_DISPLAY_NAME TRANSACTION_ID SELL_ORISPL_CODE BUY_ACCT_NAME TRANSACTION_DATE TRANSACTION_TOTAL SELL_STATE BUY_ACCOUNT_TYPE VINTAGE_YEAR TRANSACTION_TYPE SELL_EPA_REGION BUY_ORISPL_CODE START_BLOCK SELL_ACCT_NUMBER SELL_SOURCE_CAT BUY_STATE END_BLOCK SELL_ACCT_NAME SELL_OWN_DISPLAY_NAME BUY_STATE_REGION TOTAL_BLOCK SELL_ACCOUNT_TYPE BUY_ACCT_NUMBER BUY_SOURCE_CAT
New FC = 3 (1) Existing FC = 1 (8) Preview = 2 Filter Tags = 1 Download = 3 API = 13
Travis,
CAMDNATS.TTRANTYPE lists the possible transaction types for transactions recorded in the CAMDNATS.TTRANSACT table. CAMDNATS only includes legacy OTC and NBP data.
CAMDAMS.TRANSACTION_TYPE_CODE lists the possible transaction types for transactions recorded in the CAMDAMS.TRANSACTION table. This covers all transactions for all other programs (ARP, CAIR, CSAPR, any new program that might come along).
ALLOCATION_IND in CAMDAMS.TRANSACTION_TYPE_CODE has 3 possible values.
• 0 – transaction type that is not an allocation • 1 – transaction type that is an allocation • -1 – transaction type that acts as a reversal or surrender of an allocation o These are exclusively ARP transaction types that were used during ARP Phase 1 DI Reallocation Surrender RP Phase 2 Early Reduction Payback RS Reduced Utilization Payback SP State Cap Payback SS Substitution Payback ST Substitution Termination TM Reduced Utilization Termination
The ALLOCATION_IND field is used during the AMPD data mart loading on CAMDPUB when populating the “Compliance Year Allowances Allocated” for compliance query results.
It is also used during the running of compliance in CBS and CSA when determining which allowances to deduct using FIFO.
Mark
From: Lageman, Travis Lageman.Travis@epa.gov Sent: Monday, April 12, 2021 2:03 PM To: Mark Hayward Mark.Hayward@erg.com; Ann Albrecht Ann.Albrecht@erg.com Cc: Desantis, Laurel Desantis.Laurel@epa.gov; Kuhns, Jason Kuhns.Jason@epa.gov; Stetsyuk, Karina Stetsyuk.Karina@epa.gov; Gill, Sandeep Gill.Sandeep@epa.gov Subject: Lookup code table for Allowance Transactions
CAUTION: Don't open links or attachments unless you recognize the sender and know they are safe. Hi Mark & Ann,
Hope you had a nice weekend. I have another database question. I was looking for the transaction type code table and realized there are two different tables, one under the TRANSACTION_TYPE_CODE in CAMDAMS (which I believe is the current) and then one under TTRANTYPE in CAMDNATS (the older). I believe these are both necessary to get the full list of transaction types in the database. Is this correct or is there additional context I should be aware of?
I also noticed that there are some -1 values in the ALLOCATION_IND field for TRANSACTION_TYPE_CODE which I am confused as to what that identifies as opposed to 0 or 1, which make sense.
Thanks in advance,
Travis Lageman Environmental Policy Analyst Clean Air Markets Division U.S. Environmental Protection Agency P: 202-564-0520