2023-05-02 ========== * Update MeasDetailID to validate against InstallationDate instead of claim year - Add validation rule that InstallationDate must be greater than or equal to the MeasDetailID start date - Add validation rule that InstallationDate must be less than or equal to the MeasDetailID end date - Remove requirement that MeasDetailID is valid in the claim year 2022-06-13 ========== * ImplementationPA in the claim table is now required * Added a new HTR_Flag field to the claim table for hard to reach claims * Added TotalTechIncentive field to the claim table * Added MeasDetailID to the deemed measure table - Added MeasDetailID value list from eTRM * Changed claim field PriorYearClaimID to ParentClaimID - Removed rule that value must be from a prior year * Update EUL_Yrs and RUL_Yrs valid ranges - EUL_Yrs may now be zero - EUL_Yrs and RUL_Yrs for deemed measure upper range limit changed to 20 from 30 * Replace PII contact table ServiceAccountID field with two ID fields - ElectricServiceAccountID - GasServiceAccountID * Added seven new cost and benefit fields - UnitGasInfraBens - UnitRefrigCosts - UnitRefrigBens - UnitMiscCosts - MiscCostsDesc - UnitMiscBens - MiscBensDesc 2022-04-06 ========== * The two validation rules for CET electric avoided cost combinations were updated to use ImplementationPA instead of UploadPA 2021-05-19 ========== * Three validators added to the list of validation conditions in ReadMe to support new validation rules * Added validation rules - Moved 3 warning rules to validation rules for RUL_Yrs and 'AR' measures - Add a validation rule to ensure CCAREN_Flag is true for REN and CCA submissions * Removed warning rules - Removed 3 warning rules and moved to validation rules for RUL_Yrs and 'AR' measures * Added table fields - Claim.ETP_Flag * Changed table field Claim.REN_Flag to Claim.CCAREN_flag 2021-01-29 ========== * Revised energy (kWh and Therm) savings calculations for Fuel Substitution measures, per ED direction. Specific calculations addressed in the User Guide. * Removed validation rules - For Fuel Substitution measures, CEDARS now accepts positive savings values in both UnitkWh1stBaseline UnitTherm1stBaseline. * Revised Fuel Substitution savings calculations: - For Fuel Substitution measures where the uploaded row has positive values in both UnitkWh1stBaseline UnitTherm1stBaseline, CEDARS will now: 1. convert the Therm savings to kWh, 2. add the converted savings to the kWh savings, 3. zero the Therm savings. 2020-11-05 ========== * Replaced cet_good.zip to account for DEER changes. * Updated PII guidance for clarity. 2020-05-15 ========== * Revised energy (kWh and Therm) savings calculations for Fuel Substitution measures, per ED direction. Specific calculations addressed in the User Guide. * Removed table fields - DeemedMeasure.NTG_ID (already in Claim table and required field when DeliveryType does not equal 'C&S') - DeemedMeasure.DeliveryType (already in Claim table and required field) - Claim.MarketEffectsBenefits - Claim.MarketEffectsCosts * Added table fields - Claim.DEER_Sector - Claim.PriorYearClaimID - DeemedMeasure.DEER_MeasureID * Updated table fields - Change allowed NumRange for EUL_Yrs from [0,30] to [1, 30] * Added value lists - DEER_Sector - DEER_MeasureID * Added validation rules - DEER_MeasureID requirement for DEER measures - For Fuel Substitution measures, require either: -- Positive UnitkWh1stBaseline AND negative UnitTherm1stBaseline OR -- Positive UnitTherm1stBaseline AND negative UnitkWh1stBaseline * Updated Claim PII QC script, shown in Readme, to exclude Site and Contact data for Population-level NMEC * Rule added to the list of validation conditions in ReadMe to support new validation rule for Fuel Substitution measures * Added clarifying ProgramCost information to the Specification Notes of the Readme * Correction to ChangeLog entry on 2017-05-15: The line reading "DEER values remain valid in CEDARS for one year past expiration" is incorrect. The language should read, "DEER values are valid between the StartYear and EndYear, inclusively, as specified in the value list. The values are removed from the value list the year following the EndYear." * Correction to Date for ChangeLog entry on 2018-10-23: The 2019 Spec updates were actually made on 2019-05-01. 2019-12-12 ========== * Change email from "cedars@sound-data.com" to "cedars-help@sound-data.com" 2018-10-23 ========== * Value list updated - Updated Claim.Sector value list to use business plan sector values. * Update claim_spec.sql - Made Claim.DeliveryType required for all claims. * Added three new validation rules - Claim table NTG_ID field is required for all claims except C&S. - Made DeemedMeasure and CustomMeasure tables StdDesc field required for all claims that have savings. - Made DeemedMeasure and CustomMeasure tables PreDesc field required for all claims that have RUL greater than zero. * Update two warning rules - Update warnings to use new DEER MeasAppType 'AR' instead of 'ER' when checking consistency between RUL and MeasAppType. * Changed claim_good.zip file name to claim_good_YYYY.zip 2018-10-23 ========== * Added claim PII QC to the ReadMe.txt 2018-07-06 ========== * Changed good.zip file name to claim_good.zip * Changed combined source of truth zipfile name to Claim_SourceOfTruth.zip 2018-04-20 ========== * Updated table fields - DeemedMeasure.MeasCostID max size increased from 50 to 100 characters - ProgramCost.PA now validates against the uploader's PA 2017-05-15 ========== * Removed table fields - DeemedMeasure.VersionSource - DeemedMeasure.PreTechGroup - DeemedMeasure.PreTechType - DeemedMeasure.StdTechGroup - DeemedMeasure.StdTechType - DeemedMeasure.InstallationRateCost - DeemedMeasure.RealizationRateCost - Claim.ClaimDate * Updated table fields - Moved MeasImpactType to Claim from DeemedMeasure * Added table fields - DeemedMeasure.MeasInflation - CustomMeasure.MeasInflation - CustomMeasure.PreDesc - CustomMeasure.StdDesc - CustomMeasure.TechGroup - CustomMeasure.UseCategory - Claim.FinancingPrgID - Claim.WaterOnly_Flag - Claim.MarketEffectsBenefits - Claim.MarketEffectsCosts - Claim.RateScheduleElec - Claim.RateScheduleGas - Claim.Comments * Added table - WaterMeasure * Removed value list - VersionSource (DEER) * Added value lists - RateScheduleGas (CET) - RateScheduleElec (CET) - SourceDesc (DEER) * Removed validation rules - Removed validation rules associated with fields which were removed - PreTechGroup - PreTechType - StdTechGroup - StdTechType * Updated validation rules - Update SiteID is not null rule to remove exception for upstream claims * Added validation rules - To ensure water only claims do not have kW, kWh, or Therm savings in the claim - To ensure claims do not have both InstallationRate and RealizationRate values that are not 1. * Rule added to cross-table validation in ReadMe - Require at least one ContactClaim record for all non-C&S claims * DEER value list sync update: DEER values remain valid in CEDARS for one year past expiration. * Ensure only one ProgramCost record may be submitted per PrgID. * Ensure the ClaimYearQuarter cannot be after the submission ClaimYearQuarter, and that it belongs to the current submission year. * Ensure all Programs in ProgramCost table have at least one Claim record before allowing quarterly confirmation * Ensure all Programs in Claim table have at least one ProgramCost record before allowing quarterly confirmation * Non-ASCII characters will be stripped from submissions as part of the Tier 1 rules listed in the Readme 2017-01-16 ========== * Fix bug where ClaimID and SiteID were missing from claim_spec.sql 2016-12.01 ========== * Updated good.zip file for the Q4 quarter 2016-11-16 ========== * Added the cost equations to the Readme.txt file 2016-11-07 ========== * The gross savings calculations were updated to use net values for 'C&S' claims * Added `StartsWith` rule that enforces prefix use in fields * Added `MarginsOfWarningAndError (x,y)` rule that enforces limits on the difference between fields for warnings and error: - A record is accepted if the claimed field is within x of the CEDARS calculation - A record is accepted and a warning is returned if the claimed record is between x and y of the corresponding CEDARS calculation - A record is rejected if the claimed field is more than y different than the CEDARS calculation * Added `StartsWith PA` rule to claim_spec.sql fields: - Claim.ClaimID - Site.SiteID - ContactClaim.ContactID * Added `MarginsOfWarningAndError (0.01, 0.1)` rule to claim_spec.sql for all `Total*` fields to enforce that claimed total savings and costs are within 0.1% of CEDARS calculations for a claim to be accepted, and within 0.01% of CEDARS calculations for it to issue no warnings. * Added `NotEmpty` rule to `claim_spec.sql` for fields: - UnitEndUserRebate - UnitIncentiveToOthers - UnitDirectInstallLab - UnitDirectInstallMat * Updated warning that RUL <= EUL / 3 to RUL <= EUL / 2.75 * Removed warning rules: - NumUnits = 0 when three Unit_1stBaseline fields = 0 and WhySavingsZeroed is not null - WhySavingsZeroed is not null when three Unit_1stBaseline fields = 0 * Added warning rules: - WhySavingsZeroed is null when any of the three TotalFirstYearGross_ savings fields =! 0 - WhyCostsZeroed is null when any of the three TotalGross_ cost and incentive fields =! 0 - WhySavingsZeroed is not null when all 0f the three TotalFirstYearGross_ savings fields = 0 - WhyCostsZeroed is not null when all of the three TotalGross_ cost and incentive fields = 0 * Added warning rules: - RUL > 0 when MeasAppType = 'ER' - RUL = 0 when MeasAppType = 'ER' * The Readme.txt was updated to document the `MarginsOfWarningAndError` and `StartsWith` rules, and to update the gross savings equations for 'C&S' claims. * Note that, from this change set onwards, the dates given for each change will be the dates of publication to PA users on staging or production, not the dates on which the change was implemented in the codebase. 2016-08-22 ========== * This Changelog file and Source of Truth files updated for the June 1st changes. 2016-06-01 ========== * Fix typo in UnitTherm1stBaseline and UnitTherm2ndBaseline fieldnames in `validation_rules.csv`. Previously the rule said "Baselines", plural, which prevented the fields from validating correctly. * Adjust submission deadlines for 2017. 2016-05-29 ========== * SiteID "is not null" validation rule clarified. * `good.zip` file updated to latest validation/warning rules. 2016-05-22 ========== * 'C&S' exceptions added to several "is not null" validation rules: - E3MeaElecEndUseShape - E3GasSavProfile - AvoidedCostCombo 2016-05-17 ========== * Dates for all SoT files have been reset because of internal changes regarding the upcoming Filings module. * This Changelog has been updated to reflect those changes in the dateing. However, no other changes have occurred to the contents of the documents themselves. 2016-05-15 ========== * Updated the `good.zip` sample upload file. 2016-05-12 ========== * Removed `references Claim` directive from CustomMeasure and DeemedMeasure specs. This is just a technical adjustment. Uploads are still required to contain matching Claims/Measures for every ClaimID. * Corrected the `RUL_Yrs is less than EUL_Yrs` rule so it doesn't trigger when both values are zero. 2016-04-29 ========== * Removed rule on CombustionType from `validation_rules.csv`. The field is now optional for all PAs. * Updated `claims_metadata.csv` file. * Added `claims_spec_PII.sql` file. 2016-04-19 ========== * Removed PartialPaymentPercent warning. 2016-04-14 ========== * Formatting changes to `claims_spec.sql` 2016-04-10 ========== * UploadPA field deleted from `claim_spec.sql`. This is an automatically generated field, and was erroneously included in the SoT files. 2016-04-03 ========== * CombustionType now validates against the Uploader's PA, not against the ImplementationPA stated in the uploaded record 2016-04-02 ========== * AvoidedCostCombo now validates against the Uploader's PA, not against the ImplementationPA stated in the uploaded record. * good.zip file now matches the current Source of Truth. 2016-03-31 ========== * `DefaultFalse` modifier added to our SQLPlus dialect. See Readme.txt for more details. * Spec updated with new fields and new field names: - PII fields removed from Site table. - Contact table removed completely because of PII - Two new fields added to Claim: `CombustionType` and `TotalGrossMeasureCost_ER` - Some flags now have the `DefaultFalse` modifier applied instead of `NotEmpty` - Validation ranges for EUL/RUL are now uniformly [0, 30] - Some table names changed for clarity: - `DirectImplementation*` fields are now named `DI*` - `EUL`, `RUL` are now named `EUL_Yrs`, `RUL_Yrs`. - `*Flag` names normalized to be named `*_Flag`. - See versioned claims_spec.sql for the full picture. * Validation changes: - Validate `SiteState` to be 'CA' only. - Validate `SiteZipCode` to be valid California zipcode (in interval [90001, 96162] exclusively) * Correct first year savings equation for the case when both `EUL` and `RUL` are less than one. * Readme.txt file edited to reflect new validators. * WARNING: Test data benchmark good.zip NOT updated yet to the new column names/contents. 2016-03-25 ========== * Changes management implemented and deployed * Cosmetic fixes to claims_spec.sql (no new content)