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
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.
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.
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.
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.