Please send any question or feedback to: cedars-help@sound-data.com ------------------------------------------------------------------ Table of Contents ------------------------------------------------------------------ Introduction Specification notes Data file preparation Data submissions Data change management QC Tiers Tier 1 Tier 2 Tier 3 Source of Truth files claim_spec validation_rules warning_rules CEDARS cost equations CEDARS savings equations First year gross First year net Lifecycle gross Lifecycle net Claim PII and QC script ------------------------------------------------------------------ Specification notes ------------------------------------------------------------------ Cedars requirements are captured in three source of truth files: claim_spec.sql, validation_rules.csv, and warning_rules.csv. These files define: 1. The table specifications (table names, field names, data types, primary and foreign keys) 2. The QC rules for claims data being submitted to Cedars. QC rules are broken out into categories: a. Single field QC rules: found in claim_spec.sql b. QC rules that require evaluation of more than on field are found in validation_rules.csv and warning_rules.csv. These rules are split into two categories: i. Rules: if a record does not comply with the rule it is rejected ii. Warnings: if a record triggers a warning the data are accepted and the warning is provided back to the PA for review. Some notes about these source of truth files: 1. Additionally, there is a 'claims_metadata.csv' file that explains the semantics attached to each field in each of the files in a claims upload. 2. The three source of truth files support three tiers of QC; the tiers are additive and evaluated in sequence. 3. Being a primary key constrains a field to be both not null and unique. 4. If you are resubmitting data for a ClaimID that already exists in CEDARS, and your new data for that ClaimID passes QC, CEDARS will overwrite the data which already exists for that ClaimID. Additionally, the ProgramCost table has some notable features: 1. The ProgramCost table has a compound primary key. That is, the primary key is made up by two fields instead of one; the ProgramCost table primary key fields are ProgramID and Year. 2. By nature of the primary key, there can only be one ProgramCost record per year per program. 3. Therefore, ProgramCost data overwrite previously submitted ProgramCost data for that year for each program submitted each time they are submitted. 4. ProgramCost data should always be submitted as year-to-date total expenditures for each program. For programs that have only program costs and no savings, program administrators must submit a single claim record for the program with zero savings as a placeholder for the CET to correctly calculate the program and portfolio accomplishments and cost effectiveness. The CET needs both program cost and claim data for every program submitted to run correctly. ------------------------------------------------------------------ Data file preparation ------------------------------------------------------------------ For the purpose of this section, an 'upload' is both the file format for claiming under CEDARS, and the act of uploading one of such files. * Claim data uploads consist of seven .csv data tables as defined in the spec. * These seven .csv data tables will be zipped together, in a file name of PA choosing, into a .zip archive. A Readme.txt may optionally be included in the .zip archive. * CEDARS requires data files be prepared using the pipe or up-bar character "|" as the delimiter with unquoted text values. * Files must contain the correct field names, using the specified casing (e.g. CamelCaps), and fields must be in the correct order. * All seven data files must be uploaded in each .zip upload. * The maximum precision allowed is 15 digits past the decimal. ------------------------------------------------------------------ Data submissions ------------------------------------------------------------------ For the purpose of this section, a 'submission' is a quarterly or yearly submission period for CEDARS claims/costs. Each submission can be composed of one or more uploads as defined in the previous section. Data submissions are confirmed for each PA by a PA admin five times a year: * once per quarter, all year-to-date uploads are confirmed, with a focus on reviewing the current quarter's uploads. * once a year, at the end of year, the yearly submission is confirmed. Confirmation of a submission period (quarterly or yearly) requires certain conditions: * A quarterly submission can be confirmed only if it has at least one completed (validated and accepted) upload for the present quarter. * A quarterly submission can't be confirmed if it contains ProgramCosts for a future quarter. Example: the confirmation button for the 2016Q3 submission won't be available if CEDARS contains ProgramCost records for the 2016Q4 quarter. This can happen in the case of reopened submission periods (more on this below). * A yearly submission can be confirmed even if no uploads were made in the current yearly review period. This can happen if a review confirms that uploads for Q1 through Q4 were correct. * A quarterly submission will not be allowed to be confirmed if there exist ProgramIDs in Claims for which there isn't a corresponding ProgramCost record for that ProgramID. This is a CET rule. * A quarterly submission will not be allowed to be confirmed if there exist ProgramIDs in the ProgramCost table for which there isn't a corresponding Claim for that ProgramID. Again, this is a CET rule. During review, CPUC might find that a confirmed submission period has issues that need fixing. In this case, a submission period can be re-opened by a CPUC ED. Here are the conditions under which the CEDARs system will enable the re-opening of a submission period: * The submission period is the latest confirmed. CEDARS will allow re-opening Q3 if the currently active submission period is Q4, but it won't allow re-opening Q2 if Q3 has also been confirmed. * The current submission period hasn't been re-opened. In the previous case, if a CPUC ED reviews a confirmed 2016Q3 and re-opens it, the 2016Q3 submission period will move from "confirmed" to "re-opened". Re-opening 2016Q2 won't be possible. In short, it's only possible to go back only one period, not two or more. ------------------------------------------------------------------ QC Tier 1: High level specification compliance ------------------------------------------------------------------ Table level tests 1. Do the data have the right table and field names? 2. Do the data comply with the table primary key requirements? 3. Is there one record in CustomMeasure or DeemedMeasure for every claim? 4. Is there at most one record in WaterMeasure for every claim? If the answer to any question is no, the entire data submission is refused. Record level tests 1. Do the data have the correct data types? If the answer is no, the record is refused. 2. Are the data free from line feeds (LF) and carriage returns (CR) between records? If the answer is no, the LF and CR are stripped and the data are accepted. 3. Are the data free from non-ASCII characters? If the answer is no, the whole record is rejected. ------------------------------------------------------------------ QC Tier 2: Cross table validation ------------------------------------------------------------------ We use the SQL syntax of TableName.FieldName to explicitly specify the table and field being compared. These rules are evaluated at the record level. 1. Is ContactClaim.ClaimID present in Claim.ClaimID? If the answer is no, reject the ContactClaim record. 2. Is Claim.SiteID present in Site.SiteID? If the answer is no, reject the Claim record. 3. Is coalesce(CustomMeasure.ClaimID, DeemedMeasure.ClaimID) present in Claim.ClaimID? If the answer is no, reject the DeemedMeasure or CustomMeasure record. 4. Is the same ClaimID present in both DeemedMeasure and CustomMeasure? If the answer is yes, reject both DeemedMeasure and CustomMeasure records. 5. Is Claim.ClaimID present in coalesce(CustomMeasure.ClaimID, DeemedMeasure.ClaimID)? If the answer is no, reject the Claim record. 6. Is WaterMeasure.ClaimID present in Claim.ClaimID? If the answer is no, reject the WaterMeasure record. In addition, the following cross table validation rules are enforced as Tier 3 field rules for implementation reasons: 7. Is Claim.ClaimID present in ContactClaim.ClaimID for all claims except where Claim.DeliveryType = ‘C&S’? If the answer is no, reject the Claim record. 8. Is Claim.ClaimID present in WaterMeasure.ClaimID for all claims where Claim.WaterOnlyFlag = 1? If the answer is no, reject the Claim record. ------------------------------------------------------------------ QC Tier 3: Field rules ------------------------------------------------------------------ In Tier 3 we perform record-level validation of claims using the CEDARS source of truth (SoT) files. Tier 3 has two categories of validation: single field and multiple field. Single field validations are in the claim_spec.sql file. Multi-field rules are in the validation_rules.csv and warnings_rules.csv files. Please see the information below on the Source of Truth files. ------------------------------------------------------------------ Source of Truth File: claim_spec.sql ------------------------------------------------------------------ The claim_spec file is written as an enhanced SQL create table script that includes all single field constraints, table definitions, field names, and data types. We are calling our enhanced SQL dialect SQLPlus. Our SQLPlus dialect includes the usual data types (i.e. bit, nvarchar, numeric), plus some extra types and modifiers: CedarsDate: A date field that is validated to confirm that the given data parses to a date between nine years before present and the date of the submission. WarningCedarsDate: Date constraints that raise warnings instead of causing the records to be rejected. Uses the same rules as CedarsDate, that is, the date must be within nine years of date of submission. CedarsYearQuarter: A six character text field which is validated to confirm that the given data parses to a quarter of the form 2015Q3, is within five years before the date of the submission and is not in the future. Note that, for Claim uploads, "present" means the year and quarter of the PA's currently active submission, not the calendar year and quarter. Therefore, if a PA is uploading records for the 2016Q3 submission in early January 2017, the "date of the submission" is the current submission's quarter: 2016Q3 CedarsFundingCycle: The funding cycle of the claim, must be a prior funding cycle or the current program cycle. Example values: '2010-2012', '2013-2015', '2016' CedarsNumRange: Numeric types that are constrained. Square brackets are inclusive [greater/less than or equal to], parenthesis are exclusive (greater/less than). That is: CedarsNumRange (0,1) means greater than zero and less than one CedarsNumRange [0,1] means greater than or equal to zero and less than or equal to one CedarsNumRange (0,1] means greater than zero and less than or equal to one WarningCedarsNumRange: Numeric constraints that raise warnings instead of causing the records to be rejected. Uses the same notation conventions as CedarsNumRange. CedarsValueList: Text types that are constrained to certain value lists as specified by Commission Staff. MarginsOfWarningAndError: Compares uploaded values of savings and costs to ex-ante calculated values. This takes a tuple of values with the percentage that uploaded values will be allowed to deviate from the ex-ante calculation. It generates one warning validator and one error validator. We'll use the following example: MarginsOfWarningAndError (0.01, 0.1) Passing with no errors nor warning: The uploaded value will be accepted without either warning or error if the difference with the ex-ante value is lower than 0.01% Passing, with a warning: The uploaded value will be accepted, but a warning will be issued, if the difference with the ex-ante value is between 0.01 and 0.1% Rejected, with an error: The uploaded value will be rejected, and an error will be issued, if the difference with the ex-ante value is higher than 0.1% NotEmpty: usually in final position, it means the field is never allowed to be empty. DefaultFalse: used only for boolean flag, usually in final position, it means providing this flag is optional, but the value of the field will be autofilled with False. StartsWith: used with textual fields, compares two fields in the same table. This works as in English: `ClaimID startswith PA` is true for "PGE-3" and "PGE" Matches: used with textual fields, compares two fields in the same table. This works as in English: `PA matches UploadPA` is true for "SDGE" and "SDGE" ------------------------------------------------------------------ Source of Truth File: validation_rules.csv ------------------------------------------------------------------ The validation_rules file provides are rules that, if violated, will cause the claim record to be rejected. Data that fail validation rules are rejected on a record-level basis. The QC rules in this file require more than one field to evaluate. Some rules are conditionally applied to the data; conditional rules will have one or more set of where clause columns populated in the csv. Validation rules have up to two where clauses; warnings have up to five where clauses. The rules used to specify validation conditions: in [a given collection of values] not in [a given collection of values] is CCA_REN is null or zero is not null less than less than or equal to greater than greater than or equal to equals does not equal ends with string is_california_zipcode combo_value_list matches_year_quarter All those are self-explanatory, except the last two: - a 'combo_value_list' rule means that all the values in the listed fields in a given record must be an existing combination in the corresponding combo according to the cedars specification. - a 'matches_year_quarter' rule is used to ensure that a given ProgramCost's PrgYear matches its ProgramYearQuarter. Additionally, because every claim record has one corresponding record in either the CustomMeasure or DeemedMeasure table, in the table field in the spreadsheet we use the SQL syntax: coalesce (DeemedMeasure, CustomMeasure) for rules that require the claim table to be compared to either the CustomMeasure or DeemedMeasure table - whichever is populated for that claim. In SQL, coalesce means to take the first not null value from the options listed in the parenthesis. All rules define the expected state of the data; that is, when data do not comply with the requirement specified, the data are rejected. ------------------------------------------------------------------ Source of Truth File: warnings_rules.csv ------------------------------------------------------------------ The warnings_rules file provides are rules that, if violated, returns a warning to the user and the data are accepted. Data are tested against warnings on record-level basis. Some of these rules are conditionally applied to the data; conditional rules will have one or more set of where clause columns populated in the spreadsheet. Warnings have up to five where clauses. The rules used to specify warning conditions: is null or zero is not null less than less than or equal to greater than greater than or equal to equals does not equal For "coalesce (DeemedMeasure, CustomMeasure)", see above. All rules define the expected state of the data; that is, when data do not comply with the requirement specified by the rule, the data are accepted, but a warning is returned to the user. ------------------------------------------------------------------ Cedars cost equations ------------------------------------------------------------------ TotalGrossIncentive = (UnitEndUserRebate + UnitIncentiveToOthers + UnitDirectInstallLab + UnitDirectInstallMat) * NumUnits TotalGrossMeasureCost = UnitMeaCost1stBaseline * NumUnits TotalGrossMeasureCost_ER = UnitMeaCost2ndBaseline * NumUnits ------------------------------------------------------------------ Cedars savings equations First year, Lifecycle, Gross, Net ------------------------------------------------------------------ --------------------------------------- First year gross --------------------------------------- sum (case when Measure.RUL >= 1 then 1 when Measure.RUL > 0 and Measure.RUL < 1 then Measure.RUL when Measure.RUL = 0 and Measure.EUL > 1 then 1 else Measure.EUL end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkW else 1 end * Claim.NumUnits * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW1stBaseline + case when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL >= 1 then 1 - Measure.RUL when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL < 1 then Measure.EUL - Measure.RUL else 0 end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkW else 1 end * Claim.NumUnits * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW2ndBaseline) as TotalFirstYearGrosskW, sum (case when Measure.RUL >= 1 then 1 when Measure.RUL > 0 and Measure.RUL < 1 then Measure.RUL when Measure.RUL = 0 and Measure.EUL > 1 then 1 else Measure.EUL end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkWh else 1 end * Claim.NumUnits * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh1stBaseline + case when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL >= 1 then 1 - Measure.RUL when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL < 1 then Measure.EUL - Measure.RUL else 0 end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkWh else 1 end * Claim.NumUnits * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh2ndBaseline) as TotalFirstYearGrosskWh, sum (case when Measure.RUL >= 1 then 1 when Measure.RUL > 0 and Measure.RUL < 1 then Measure.RUL when Measure.RUL = 0 and Measure.EUL > 1 then 1 else Measure.EUL end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRTherm else 1 end * Claim.NumUnits * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm1stBaseline + case when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL >= 1 then 1 - Measure.RUL when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL < 1 then Measure.EUL - Measure.RUL else 0 end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRTherm else 1 end * Claim.NumUnits * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm2ndBaseline) as TotalFirstYearGrossTherm, --------------------------------------- First year net --------------------------------------- sum (case when Measure.RUL >= 1 then 1 when Measure.RUL > 0 and Measure.RUL < 1 then Measure.RUL when Measure.RUL = 0 and Measure.EUL > 1 then 1 else Measure.EUL end * Claim.NumUnits * Claim.NTGRkW * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW1stBaseline + case when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL >= 1 then 1 - Measure.RUL when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL < 1 then Measure.EUL - Measure.RUL else 0 end * Claim.NumUnits * Claim.NTGRkW * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW2ndBaseline) as TotalFirstYearNetkW, sum (case when Measure.RUL >= 1 then 1 when Measure.RUL > 0 and Measure.RUL < 1 then Measure.RUL when Measure.RUL = 0 and Measure.EUL > 1 then 1 else Measure.EUL end * Claim.NumUnits * Claim.NTGRkWh * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh1stBaseline + case when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL >= 1 then 1 - Measure.RUL when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL < 1 then Measure.EUL - Measure.RUL else 0 end * Claim.NumUnits * Claim.NTGRkWh * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh2ndBaseline) as TotalFirstYearNetkWh, sum (case when Measure.RUL >= 1 then 1 when Measure.RUL > 0 and Measure.RUL < 1 then Measure.RUL when Measure.RUL = 0 and Measure.EUL > 1 then 1 else Measure.EUL end * Claim.NumUnits * Claim.NTGRTherm * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm1stBaseline + case when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL >= 1 then 1 - Measure.RUL when Measure.RUL > 0 and Measure.RUL < 1 and Measure.EUL < 1 then Measure.EUL - Measure.RUL else 0 end * Claim.NumUnits * Claim.NTGRTherm * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm2ndBaseline) as TotalFirstYearNetTherm, --------------------------------------- Lifecycle gross --------------------------------------- sum (case when Measure.RUL > 0 then Measure.RUL else Measure.EUL end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkW else 1 end * Claim.NumUnits * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW1stBaseline + case when Measure.RUL > 0 then Measure.EUL - Measure.RUL else 0 end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkW else 1 end * Claim.NumUnits * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW2ndBaseline ) as TotalLifecycleGrosskW, sum (case when Measure.RUL > 0 then Measure.RUL else Measure.EUL end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkWh else 1 end * Claim.NumUnits * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh1stBaseline + case when Measure.RUL > 0 then Measure.EUL - Measure.RUL else 0 end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRkWh else 1 end * Claim.NumUnits * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh2ndBaseline ) as TotalLifecycleGrosskWh, sum (case when Measure.RUL > 0 then Measure.RUL else Measure.EUL end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRTherm else 1 end * Claim.NumUnits * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm1stBaseline + case when Measure.RUL > 0 then Measure.EUL - Measure.RUL else 0 end * case when Claim.DeliveryType = 'C&S' then Claim.NTGRTherm else 1 end * Claim.NumUnits * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm2ndBaseline ) as TotalLifecycleGrossTherm, --------------------------------------- Lifecycle net --------------------------------------- sum (case when Measure.RUL > 0 then Measure.RUL else Measure.EUL end * Claim.NumUnits * Claim.NTGRkW * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW1stBaseline + case when Measure.RUL > 0 then Measure.EUL - Measure.RUL else 0 end * Claim.NumUnits * Claim.NTGRkW * Measure.InstallationRatekW * Measure.RealizationRatekW * Measure.UnitkW2ndBaseline ) as TotalLifecycleNetkW, sum (case when Measure.RUL > 0 then Measure.RUL else Measure.EUL end * Claim.NumUnits * Claim.NTGRkWh * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh1stBaseline + case when Measure.RUL > 0 then Measure.EUL - Measure.RUL else 0 end * Claim.NumUnits * Claim.NTGRkWh * Measure.InstallationRatekWh * Measure.RealizationRatekWh * Measure.UnitkWh2ndBaseline ) as TotalLifecycleNetkWh, sum (case when Measure.RUL > 0 then Measure.RUL else Measure.EUL end * Claim.NumUnits * Claim.NTGRTherm * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm1stBaseline + case when Measure.RUL > 0 then Measure.EUL - Measure.RUL else 0 end * Claim.NumUnits * Claim.NTGRTherm * Measure.InstallationRateTherm * Measure.RealizationRateTherm * Measure.UnitTherm2ndBaseline ) as TotalLifecycleNetTherm; ------------------------------------------------------------------ Claim PII and QC script ------------------------------------------------------------------ PII data is submitted quarterly outside of CEDARS via SFTP, and each quarterly PII submission should align with the cumulative, current collection of all Claims stored in CEDARS. Incremental PII submissions will be rejected, so please always provide cumulative PII data matching the Claim data stored in CEDARS after each quarterly confirmation. Claim PII are processed and QCed outside of CEDARS on EDCS. Below we provide the SQL QC script that is run on PII submissions to identify any QC issues in the data. select distinct contact.contactID as PrimaryKey, 'contact' as TableName, 'Warning' as MessageType, 'contactEmail does not contain an @ symbol' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contactemail not like '%@%' and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Warning' as MessageType, 'Placeholder contactPhoneNumber value submitted' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where (contactphonenumber like '%999%999%9999%' or contactphonenumber like '%123%456%7890%') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Error' as MessageType, 'contactPhoneNumber is non-numeric' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where isnumeric(contactphonenumber) = 0 and contactphonenumber is not null and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Error' as MessageType, 'contactPhoneNumberAlt is non-numeric' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where isnumeric(contactphonenumberalt) = 0 and contactphonenumberalt is not null and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Error' as MessageType, 'contactType is null' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contacttype is null and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Error' as MessageType, 'contactName is null' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contactname is null and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Error' as MessageType, 'contactPhoneNumber is null' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contactphonenumber is null and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Error' as MessageType, 'Duplicate contactID' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contact.contactid in (select distinct contactid from conf.contact group by contactid having count(*) >1) and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Warning' as MessageType, 'Placeholder contactType value submitted' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contacttype in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Warning' as MessageType, 'Placeholder contactName value submitted' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contactname in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Warning' as MessageType, 'Placeholder contactPhoneType value submitted' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contactphonetype in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Warning' as MessageType, 'Placeholder contactPhoneTypeAlt value submitted' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contactphonetypealt in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contact.contactid as PrimaryKey, 'contact' as TableName, 'Error' as MessageType, 'contactID not in Claim.contactClaim table' as QC_Message from conf.contact join contactclaim on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contact.contactid not in (select distinct contactid from contactclaim) and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct contactclaim.contactid as PrimaryKey, 'Claim.contactClaim' as TableName, 'Error' as MessageType, 'Claim.contactClaim.contactID not in PII contact table' as QC_Message from contactclaim join conf.contact on contactclaim.contactid = contact.contactid left join claim on contactclaim.claimid = claim.claimid where contactclaim.contactid not in (select distinct contactid from conf.contact) and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Error' as MessageType, 'siteAddress is null' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where siteaddress is null and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Error' as MessageType, 'Duplicate siteID' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where site.siteid in (select distinct siteid from conf.site group by siteid having count(*) >1) and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Warning' as MessageType, 'ServiceAccountID does not start with a number' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where (serviceaccountid) not like '[0-9]%' and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Warning' as MessageType, 'siteAddress does not begin with a number or PO Box' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where ltrim(siteaddress) not like '[0-9]%' and ltrim(siteaddress) not like 'P%O%box%' and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Warning' as MessageType, 'Placeholder contactPhoneTypeAlt value submitted' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where siteaddress in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Warning' as MessageType, 'Placeholder contactPhoneTypeAlt value submitted' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where siteunitnumber in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Warning' as MessageType, 'Placeholder contactPhoneTypeAlt value submitted' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where serviceaccountid in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Warning' as MessageType, 'Placeholder contactPhoneTypeAlt value submitted' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where serviceaccountname in ('UNKNOWN', 'N/A', '0', 'NONE') and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct site.siteid as PrimaryKey, 'site' as TableName, 'Error' as MessageType, 'siteID not in Claim.Claim table' as QC_Message from conf.site left join claim on claim.siteid = site.siteid where site.siteid not in (select distinct siteid from claim) and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance')) and deliverytype <> 'C&S' and measimpacttype <> 'Cust-NMEC-Pop' union select distinct claim.siteid as PrimaryKey, 'Claim.site' as TableName, 'Error' as MessageType, 'Claim.siteID not in PII site table' as QC_Message from claim join conf.site on claim.siteid = site.siteid where claim.siteid not in (select distinct siteid from conf.site) and prgid in (select prgid from program where programcategory not in ('Market Education Outreach', 'Energy Savings Assistance'));