Knowledgebase:
EC Express: Rules For Bill Import
Posted by Matthew Heinz on 06 June 2012 02:51 PM

Rules For Bill Import

Required Fields for Every Import File

  1. Account Code
  2. Start Date
  3. End Date

NOTE: An additional field, "A/C/E" is required for the importer to work.  If you do not provide this column in your file, all bills will be treated as "A" as described below.

A/C/E Rules

The A/C/E flag is a column that contains an A, a C or an E for each record. "A" is the standard code and indicates an actual, routine bill.  "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.  "C" indicates a corrected bill.  The A and C codes are of particular importance when there is already an existing bill in the database for the same account, same vendor, same start and end dates.

A/C/E Rules for a Bill Import Record that Matches an Existing Bill in the Database

What happens when a bill is imported that is already in the database?  In other words, the 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 in an earlier record in the same import file.

  • Estimated: If "E," any existing bills on the same account with the same dates will be deleted and the new bill will be entered with the estimated flag set to "True."
  • Actual: If "A," any existing bills on the same account with the same dates will be deleted and the new bill entered.  (In essence, the prior bill is overwirtten.)
  • Corrected: If "C," the bill is treated as a correction (a "true-up" transaction); existing bills on the same account with the same dates are NOT deleted, and the new bill is entered.
  • Any other A/C/E column value, or no value, is interpreted as if it were an "A."

Meter and Commodity Rules

In EnergyCAP, a bill is associated with an account and a meter.  While Account Code is required, EnergyCAP can attempt to find the correct meter if the Meter Code is not specifically provided.  To find the meter for this bill, EnergyCAP uses the following logic to locate a meter connected to the specified Account Code.

  • If METER CODE is supplied, it used as provided.  If the provided Meter Code could not be found, an Import Error message will tell you.
  • If METER CODE is not provided, specifying a COMMODITY column is recommended.  This causes the importer to look up meters of that commodity connected to the specified account.  If no meters could be found OR more than one of that commodity, the importer will not be able to import that bill.  A Import Error message will be logged giving you details. 
  • If no COMMODITY or METER CODE is specified in column mapping, the import wizard will ask if all bills are for the same commodity and ask you to select a commodity from the list provided. If YES, the commodity code will be applied to all bills in the import file. Otherwise, the importer will attempt to locate the meter based on the account code and assumes there will be only one meter.  If there is more than one meter on the account, the bill will be processed as an import error, since EnergyCAP will not be able to determine which meter usage should be applied
Example 1:  Both Account and Meter Code are provided.  Assuming they are correct, this bill will get imported to the correct account with no guess work by the importer.

Account Code

START DATE

END DATE

Meter Code

123456-1

20071201

20071231

12345-ELEC


Example 2: Account Code and Commodity Code are provided . The importer will look for the Electric meter on this account.  If only ONE is found, the bill will import correctly.  If more than one electric meter is found, the bill will fail to import because it cannot correctly determine which electric meter this bill is associated with.

Account Code

STARTDATE

END DATE

COMMODITY

123456-1

20071201

20071231

ELECTRIC

 
Example 3:  Only Account Code is provided.  The import wizard will ask you if all lines in the file are for the same commodity (eg. all electric bills).  If they are, select the commodity and then each bill will be treated like Example 2 above.  If they are not all for the same commodity, the importer will attempt to locate the meter based on the Account Code.  If more than one meter exists for this account, the import will fail because it cannot correctly determine which meter the bill is associated with.

AccountCode

STARTDATE

ENDDATE

123456-1

20071201

20071231

Account-level Bill Charges

If you have a charge on a bill that should be reflected only on the account (a late fee for instance), you can specify this during the mapping processes.  To accomplish this, you should 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 "account charge" column, place an "X" on any line that should be an account charge.  This will cause the importer to ignore any Meter Code or Commodity provided.

Account 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 Account Charge column has an "X" in it.

Account Code Start Date End Date Meter Code Use (kWh) Cost Late Fee Account Charge
123456-1 20071201 20071231 12345-ELEC 2534 254.24    
123456-1 20071201 20071231 12345-ELEC     50 X

 Suggestions

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

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.

  • Billing 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 by the month in which the mid-point of the bill falls.  Example: 10:2012 = October 2012
  • 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 2013, you still use 10:2012 for Accounting Period of Oct 12. 
  • Vendor Code (format alphanumeric) The Vendor Code associated with the bill.
  • Invoice Number (format alphanumeric)


(3 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: