Documentation for All Versions

Download a PDF Version

Current Documentation for EnergyCAP Online

This Documentation relates to 3.5.
If you are using a different version, please view the Documentation for All Versions and select the relevant version.

Skip to end of metadata
Go to start of metadata

Description

To ensure a successful import of utility bill data, the import process must be followed carefully. The Bill Import Wizard provides an interactive tool to guide the process. Additionally, data in the import file itself must conform to certain specifications. Each bill record must include certain types of data.

This topic will...

  • provide rules and specifications for a successful import of utility bill data.

Required Fields

Each bill record corresponds to a row of data in the spreadsheet file. In addition to Cost, each bill record must include data for three required fields: Account Code, Start Date and End Date.

Bill Start Date Adjustment 

Given that there are some EnergyCAP processes that rely on date-specific logic, such as any per day analysis, auditing, and data presentation, it is beneficial that billing start and end dates are entered in a consistent manner. EnergyCAP is designed to have sequential bills start on the same day that a previous bill ends to ensure that “gaps” between bills do not distort energy and cost analysis.  The bill import processor has been enhanced to identify bills in the import file where the begin date is one day after the end date of the previous bill.  Once identified, the start date of the new bill is adjusted such that it corresponds to the prior bill end date and a note is created on the bill as a record of the change.  Modifying the start date of the imported bill when there is only a one-day difference is the most efficient method for maintaining proper functionality of some date-specific logic in key EnergyCAP processes.  This enhancement was brought about as a result of utility vendors that do not list the start date of a new bill as the end date of the prior bill.

A/C/E and V/P/R Fields

The two columns provide specific instruction to the bill import processor regarding how bills should be processed especially when there is already an existing bill in the database for the same account, same vendor, same start and end dates (see the  A/C/E and V/P/R Rules section).

 

 

A/C/E Field

The A/C/E field is set for a bill using a column that contains an A, C or E for each bill record.

        • Actual - "A" is the standard code and indicates an actual, routine bill.
        • Estimated - "E" indicates an Estimated bill (the usage or meter reading has been estimated) and results in the bill's estimated flag being set to true.
        • Corrected - "C" indicates a corrected bill.
        • Any bill record rows having a blank value will be imported as "A".
        • If the field is not blank and has any character other than "A", "C", or "E" the record will be treated as invalid entries and the bill record will be kicked out of the import into an error file.

V/P/R Field

The V/P/R field is set for a bill using one of the three valid characters, V, P, or R. If this field is left blank the bill import processor will use the default handling based on the A/C/E field.

        • Void - "V" indicates the existing bill in EnergyCAP will be marked as "voided".
        • Preserve - "P" indicates an that we want to Preserve the existing bill in EnergyCAP (DO NOT overwrite it).
        • Reject - "R" indicates that the focus is to be placed on the existing bill in EnergyCAP.  This "R" value results in the bill record being kicked out of the import process.
        • Any bill record rows having a blank values will be imported using the logic based on the corresponding value A/C/E field.
        • If the filed is not blank and has any character other than V, P, or R the record will be treated as an invalid entry and the bill record will be kicked out of the import into an error file.


A/C/E and V/P/R Rules for a Bill Import Record that Matches an Existing Bill in EnergyCAP

What happens when a bill is imported that is already in the database? In other words, the vendor, account, start date and end date all match an existing bill. The existing bill could have been keyed or imported previously, or even imported moments ago as an earlier record in the same import file.  The table below provides a list of the various field combinations and the resulting process logic applied.

A/C/EV/P/RLogic applied to the Bill in the Import FileLogic applied to the Bill in EnergyCAP
  Creates bill in EnergyCAPExisting EnergCAP bill is Overwritten
A Creates bill in EnergyCAPExisting EnergyCAP bill is Overwritten
AVCreates bill in EnergyCAPExisting EnergyCAP bill is Voided
APCreates bill in EnergyCAPExisting EnergyCAP bill is Preserved
ARRejects bill during import processExisting EnergyCAP bill is Preserved
C Creates bill in EnergyCAPExisting EnergyCAP bill is Preserved
CVCreates bill in EnergyCAPExisting EnergyCAP bill is Voided
CPCreates bill in EnergyCAPExisting EnergyCAP bill is Preserved
CRRejects bill during import processExisting EnergyCAP bill is Preserved
E Creates estimated bill in EnergyCAPExisting EnergyCAP bill is Overwritten
EVCreates estimated bill in EnergyCAPExisting EnergyCAP bill is Voided
EPCreates estimated bill in EnergyCAPExisting EnergyCAP bill is Preserved
ERRejects bill during import processExisting EnergyCAP bill is Preserved

 

Meter Codes, Serial Number and Commodity Rules

In EnergyCAP, a bill is associated with an account and a meter. While Account Code, Start Date, and End Date are required, the import wizard/processor does not required Meter Code, Commodity, or Serial Number.  EnergyCAP can attempt to find the correct meter if the Meter Code is not specifically provided. Dependent upon the information provided for the given bill record, EnergyCAP uses the following logic to locate a meter connected to the specified Account Code:


Example 1:  Specific Meter Code is provided. 

AccountCodeStartDateEndDateMeterCodeCommoditySerialNumber
1235-6720160601201607011235-ELEC  


Example 2:  Specific Meter Code and Commodity are provided.
AccountCodeStartDateEndDateMeterCodeCommoditySerialNumber
1235-6720160601201607011235-ELECElectric 


Example 3:  Only Commodity is provided. The Serial Number field is blank and the Meter Code is either set to AUTO or is blank.

AccountCodeStartDateEndDateMeterCodeCommoditySerialNumber
1235-672016060120160701

 

 Electric 


Example 4:  Meter Code and Serial Number are provided.

AccountCodeStartDateEndDateMeterCodeCommoditySerialNumber
9987-9920160601201607019987-NG FN-2199

 

 


Example 5:  Meter Serial Number is provided.  Meter Code and Commodity are left blank (leaving the meter code blank is treated the same a AUTO).

AccountCodeStartDateEndDateMeterCodeCommoditySerialNumber
5641-772016060120160701

Left Blank

also considered to be

!AUTO!

 TK-421


Example 6:  Commodity and Serial Number are provided.  The Meter Code is left blank, which is treated the same a assigning AUTO to the Meter Code.

AccountCodeStartDateEndDateMeterCodeCommoditySerialNumber
5641-772016060120160701  Natural GasT-1000

NOTE:  Required fields for the bill import file are Account Code, Start Date and End Date.  The remaining fields provide information to better define the meter to receive the line item data.  In short, a Meter Code field that is left blank is the same as one that is filled with !AUTO!

 

Account-level Bill Charges

If a bill contains a charge that should be associated with the account only (a late fee for instance), this can be specified during the mapping processes. To accomplish this, add two additional columns to the import file; one for the charge (late fee) and one to signal that this line of the bill should be charged to the account, not a meter on the account. In the "service charge" column, place an "X" on any line that should be a service charge. This will cause the importer to ignore any Meter Code or Commodity provided.

Service Charge Example: This bill consists of two import lines, which the importer identifies because the lines are consecutive and have the same Account Code, Start Date and End Date. The first line is for an electric meter, the second for an account-level charge. Even though the Meter Code is given on the second line, it will be ignored because the Service Charge column has an "X" in it.

Account CodeStart DateEnd DateMeter CodeUse (kWh)CostLate FeeService Charge
123456-1200712012007123112345-ELEC2534254.24  
123456-1200712012007123112345-ELEC  50X

Suggestion: When using a new profile, test a sample of the import file format with a small number of bill records.

EnergyCAP Billing Period Logic

The goals are:

      • To avoid having gaps in Billing Periods when there are no gaps in the billing dates and
      • To avoid having more than one bill in the same Billing Period.
      • To provide the client with options for how the bill period is set (define a specific bill period, use Start date,  use Middle day, use End date, or use default EnergyCAP logic).

To accomplish these goals, EnergyCAP uses a series of logical rules to determine the billing period month of each bill, regardless of whether it is manually keyed OR imported:

    • The 'default' rule is that EnergyCAP 'splits the difference' between the Start Date and End Date for the billing period; the middle of the period marks the billing period month.  
      • IF the number of days in the billing period is greater than 45, THEN the 'default' logic is still used.However, IF the billing period is LESS than 45 days, THEN a second logical step is applied.

Second Logic Step Scenario 1:

            • IF the default rule results in a billing period month being skipped, and
              • IF the 'middle-of-the-bill' date is equal to or less than the 5th of the default month, and 
              • IF the bill Start Date is earlier than Day 1 of the 'default' month, THEN: The billing period month will be moved back one month.

Second Logic Step Scenario 2:

            • IF the default rule results in the bill being assigned a billing period when a bill ALREADY exists for THIS month and
              • IF the 'middle-of-the-bill' date is equal to or greater than the 26th of THIS month, and 
              • IF the billing period End Date is greater than Day 1 of the NEXT month, THEN the billing period will be moved forward a month.
    • The Bill Period Column can be used with bill import files to specify the bill period or to adjust the bill period logic applied to determine the billing period.

      Bill Period Column ValueBill Period Assignment logic
      MM:YYYYForces the bill import bill period logic to set a fixed billing period based on the month (MM) and year (YYYY) provided, such as 10:2016 would result in a bill period of October 2016
      !AUTO!The default logic for billing period assignment will be used
      !START!The bill will be assigned the billing period with the month and year of the bill start date
      !MIDDLE!The bill will be assigned the billing period with the month and year of the middle date between the bill start date and end date
      !END!The bill will be assigned the billing period with the month and year of the bill end date
       If the Bill Period Column is provided in the import file but the field is left blank for a given record, the !AUTO! logic will be used

      NOTE:  If the Billing Period column is NOT provided, the !AUTO! logic will be used.

Optional Fields

This is a list of optional fields which can be used in the bill import process. These fields can provide additional information for a bill that may be useful or necessary information for specific data needs.

      • A/C/E and V/P/R fields:  provide specific instruction to the bill import processor regarding how bills should be processed especially when there is already an existing bill in the database for the same account, same vendor, same start and end dates (see the  A/C/E and V/P/R Rules section).
      • Specifying a Meter for the bill record:  Although "meter code" is not a required field in the import file, the importer will need to look up a meter code in order for the import to work.  There are a few options to help the importer make the best choice when it performs the lookup.
          • Meter Code:  If the meter code is specified (the actual meter code found on the account "general" tab for each meter of an account), the importer has the best chance of mapping the billing line to the applicable meter.
          • Commodity: If the meter code is not known the commodity field can be used to specify the commodity of the meter associated with each line item tied to the account.  This option works only if there is only one meter for the commodity associated with the given account.  If, however, there is more than one meter of the same commodity for an account, the importer will not be able to determine which of the meters the charge is for and will fail to import the bill.
          • Specifying BOTH Meter Code and Commodity:  The meter code will take precedence, and commodity will be used for rows that have no meter code provided as described above.
          • Specifying NOTHING:  If neither meter code or commodity is specified the importer will attempt to find the meter based on the account.  If the account has more than one meter, the import of that row will fail as the charge(s) cannot be associated with a specific meter.
      • Bill Period: (format MM:YYYY): Providing this information forces a bill to be assigned to a particular calendar month billing period; otherwise, the billing period is determined using the default Bill Period logic or the designated logic listed. 
          • Example: 10:2012 = October 2012, !AUTO!, !START!, !MIDDLE!, or !END! i
          • If the Bill Period Column exists and is left blank it implies the default bill period logic (!AUTO!) is to be applied.
      • Due Date (same format as start and end date fields)
      • Statement Date (same format as start and end date fields)
      • Accounting Period (format MM:YYYY): The calendar month Accounting Period associated with the bill. 
          • Example: 10:2012 = October 2012. 
          • If October is month #1 of fiscal year 2017, use 10:2017 for Accounting Period of Oct 12. 
      • Vendor Code (format alphanumeric): The Vendor Code associated with the bill.
      • Invoice Number (format alphanumeric)
      • Bill Notes:  A column available for importing notes associated with a bill.  These notes can be seen in the Bill Entry Window, displayed in all bill lists, and/or viewed on the generated bill PDF file
      • Meter Serial Number (format alphanumeric): The Serial Number of the meter associated with the bill. A mismatch between the value of the import sheet and an EnergyCAP stored value (effective for the begin date of the imported bill) will not prevent a successful import, but a bill message will be associated with the bill record in EnergyCAP. Useful for Bill Message reports.
      • Rate Code (format alphanumeric): The Rate Code of the meter associated with the bill. A mismatch between the value of the import sheet and an EnergyCAP stored value (effective for the begin date of the imported bill) will not prevent a successful import, but a bill message will be associated with the bill record in EnergyCAP. Useful for Bill Message reports.

Rules & Restrictions

  • Accounts status MUST be Active in order for bills to be imported or entered on the given account.  As a secondary check the bill import processor verifies the bill dates fall between the Account Service Begin and End dates.  Bills will be rejected and kicked out of the import if the associated account status is inactive or if the bill dates do not fall between the Service Begin and End dates for the active account.
  • Import file records are required to contain Account Code, Bill Start Date, and Bill End Date.
  • If there are accounts for different vendors that have the same Account Code in EnergyCAP, THEN the Vendor Code column must be included in the bill import file.
  • All dates in the import file must use the same date format.
  • To ensure bill records are imported in a chronological order and to minimize the risk of poor data import, it is required that the bill import file be sorted in the following manner, Account Code, Start Date, End Date, Meter (if applicable), Commodity (if applicable).

Additional Information

  • Account Alert messages are honored for imported bills:
    • If a bill is being imported to an account having an active alert, a Bill Message will be added to the bill.  If the account alert is set to a "failed" status, the bill will NOT be imported and the kickout record will mention the active alert.
  • Bill import profiles are used to quickly import files that have the same format as a previously-imported file. Once columns of the import file have been matched with the bill template fields, the same mapping will be applied each time the profile is used. If using an existing profile, click to select its name from drop-down list of available profiles. If creating a new profile, input the new profile name in the field provided.
  • Bill Import Logging:  A detailed activity log is provided to show the history of all bill import activities.
  • No labels