Rules for Bill Import
Required Fields for Every Import File
- Account Code
- Start Date
- 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.
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 overwritten.)
- 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 UtilityTrac Plus, a bill is associated with an account and a meter. While Account Code is required, UtilityTrac Plus can attempt to find the correct meter if the Meter Code is not specifically provided. To find the meter for this bill, UtilityTrac Plus 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 UtilityTrac Plus 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.
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.
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.
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|
When using a new profile, test a sample of the file format with a small number of records.
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)