LCFS - Migrate Exclusion Report Records from TFRS to Allocation Agreement in LCFS with Version Chaining

[airinggov]

Description:
Develop a Groovy ETL script to migrate Exclusion Report data from the exclusion_agreement table in TFRS to the allocation_agreement table in LCFS. The script will transform and map specific fields while ensuring compatibility with LCFS table requirements, including handling overrides for mandatory fields.

Key tasks include:

Purpose and Benefit to User:

Acceptance Criteria:

Development Checklist:

1. Data Extraction:

   • Query TFRS exclusion_agreement table to extract Exclusion Report data.

2. Mapping and Transformation:

   • Map TFRS transaction_type to LCFS responsibility:
     • Purchased → Allocated from
     • Sold → Allocated to
   • Map TFRS fields to LCFS fields:
     • fuel_type → fuel_type
     • legal_name_of_transaction_partner → legal_name_of_transaction_partner
     • address_of_transaction_partner → address_for_service
     • quantity → quantity
     • units_1 → units
   • Add and populate new fields in LCFS:
     • quantity_not_sold_or_supplied_within_compliance_period
     • units_2

3. Field Overrides:

   • Override mandatory field validations in LCFS for the following:
     • email → allow null
     • phone → allow null
     • fuel_type_other → allow null
     • fuel_category → allow null
     • determining_carbon_intensity → allow null
     • fuel_code → allow null
     • ci_of_fuel → allow null

4. Data Insertion:

   • Insert transformed records into the allocation_agreement table in LCFS.

5. Error Handling and Logging:

   • Implement error handling to log issues without halting the process.
   • Log field mapping transformations and overrides for transparency.

6. Testing and Validation:

   • Verify that all records are migrated accurately, including field mappings and null overrides.
   • Validate that new fields are added and populated correctly in LCFS.
   • Test edge cases, such as missing fields or invalid data in TFRS.

7. Documentation:

   • Document the script’s purpose, logic, and field mappings for future reference.
   • Provide guidance for running and verifying the ETL process.

Notes:

• Overrides for mandatory fields are crucial to align with LCFS requirements while handling incomplete data from TFRS.
• New fields (quantity_not_sold_or_supplied_within_compliance_period, units_2) need proper handling for insertion in LCFS.
• Use existing ETL scripts for reference to maintain consistency in implementation.

[airinggov]

@airinggov Create new card to display quantity_not_sold_or_supplied_within_compliance_period, units_2 in legacy reports.