Excluding Bill Line Items (Taxes) from Power View Average Unit Cost

Utility bills are entered into Energy CAP to facilitate energy management.  Bills may be entered manually or electronically via file import (EDI or flat file).

A bill can only be created for a bill-enabled account--that is, an account that has been correctly set up with all required information and associations with meters/vendors/bill templates/places, etc.  Using the Account Wizard for account setup helps ensure that all information required for successful bill entry has been entered into the EnergyCAP software.

Associated bill template(s) must be effective for the bill start and end dates. Templates assure that all desired billing information is entered into the EnergyCAP database.

Bills are usually entered from the Account Manager (Accounting/Accounts) via the New Bills button from the Account Manager title bar. however, there are several other ways and places to open a Bill Entry window. Bills can be opened in Create (data can be entered) View (read-only) or Edit (data can be changed) modes. The remaining topics in this section of the User Manual will explain the bill entry module in detail.

Current Bills

A current bill is the next or most recent usage or cost data to be entered in the system.  When creating a current bill, the start date defaults to the end date for the most recent bill in the system.

Historical Bills

A historical bill is any usage or cost data entered in the system prior to the most current data already in the system.  When creating a historical bill, the system prompts you for a start and end date instead of using the default start and end dates of the most recent bill in the system.

This topic describes some basic terms, acronyms and definitions used for electronic payments. When possible, an online reference is provided for additional research and explanation.

NACHA (National Automated Clearing House Association)
http://www.nacha.org/

• NACHA is a banking industry association that develops electronic solutions to improve electronic payment systems. NACHA develops operating rules and business practices for the ACH Network, which enables banks to reconcile Debits and Credits between themselves automatically.
• Federal law requires that banks reconcile every 24 hours.

EFT (Electronic Funds Transfer) and Wire Transfer:
http://www.fms.treas.gov/eft/index.html

• This payment type transfers monies from one bank account to another. This does not include any Remittance Data (Account and or Service Agreement Identifiers.
• Customers that have multiple accounts and or service agreement Identifiers and remit payment in this manner may require manual allocate of payments.

ACH (Automated Clearing House)
http://www.fms.treas.gov/ach/index.html

• Electronic payments sent through the banking industries (NACHA) network for reconciliation between banks for Debits and Credits
• Managed almost exclusively by Federal Reserve banks and the Electronic Payments Network (EPN)
• ACH payments without remittance data (Account and or Service Agreement Identifiers) are similar to EFT and Wire transfers (Monies Only)

ACH /CCD (Automated Clearing House/Cash Concentration and Disbursement)
http://www.allbusiness.com/glossaries/automated-clearing-house-ach/4943617-1.html

• This payment type transfers monies from one bank account to another bank account. This does not include any Remittance Data (Account and or Service Agreement Identifiers).
• Customers with multiple Accounts and or Service Agreement Identifiers and remit payment in this manner may require manual allocation of payments.

ACH/CCD + (Automated Clearing House/Cash Concentration and Disbursement + Addenda Record)

• This type of payment is ACH/ CCD monies and the + is the addenda record (Remittance Data, PG&E’s Account and or Service Agreement Identifier).
• This type of payment requires one electronic payment for each Account Identifier and or Service Agreement Identifier being paid.
• The banking industry typically charges a fee for each electronic payment that is initiated. This could be costly. Example: 10 Accounts and 10 electronic Payments. Banking fee for ten payments.

ACH /CTX(Automated Clearing House/Corporate Trade Exchange)

• This type of payment is ACH/ CTX monies and provides addenda records (Remittance Data, Account and or Service Agreement Identifier).
• This type of payment requires one electronic payment for multiple Accounts and or Service Agreement Identifiers being paid.
• This type of payment provides the ability to make one electronic payment for up 9,999 addenda records (Account and or Service Agreement Identifiers).
• The banking industry typically charges a fee for each electronic payment that is initiated. In this case, the fee is for one electronic payment and the allocation of the payment is for multiple Account and or Service Agreement Identifiers.

EDI (Electronic Data Interchange) 820 Payment and Remittance Order
http://www.amosoft.com/articles/edi-overview.html

• The EDI 820 Payment and Remittance Order is a ANSI (American National Standards Institute), UIG (Utility Industry Group) approved data format for electronic payments. This format includes Monies and Remittance data (Account and/or Service Agreement Identifiers.
• The EDI 820 is generated by the initiator of the payment and instructs their bank to take monies from their account and deposit the monies into PG&E’ bank account. The remittance data (Account and/or Service Agreement Identifiers are included inside the EDI 820 structured format.
• This is one electronic payment, and the remittance data (Account and/or Service Agreement Identifiers) defines the amount of monies, the Accounts to which monies are transferred, and/or the Service Agreements referenced.

To correct or update this document, please add your comment using the comments field available below.

The auditing setup process is discussed in detail in the comprehensive HELP topic group on the subject.

This topic provides information on bill audit options that may be part of the work flow process for bill entry.

Bills can be audited in a number of ways:

• by Bill (see below)
• by Batch (see below)
• From the Audit Manager via the Account menu option (see below)
• by Account (right-click the account from the Account tree and select the Audit option)

Auditing a Single Bill

To audit a single bill in EnergyCAP, open the bill in the Create, View or Edit mode and click the Audit button at the bottom of the Bill window. 



The Quick Check Audit Group that was pre-selected in the Work Flow Wizard will run automatically for the bill, including any and all audits associated with the group.

After the Quick Check is complete, audit results (if any) will be displayed in the Audit Results window.

• If the Save Bill w/Problem Flag option is the selected response to the Quick Check, the bill will be saved but will appear in the Work Flow Manager Problem folder. The Select Audits window will open, permitting user selection of additional audit groups for that bill.
• If the Save the bill and set to the alert status to Ignore ... option is the selected response to the Quick Check, the bill will be saved and will NOT appear in the Work Flow Manager Problem folder. The Select Audits window will open, permitting user selection of additional audit groups for that bill.
• If the Return to the bill for further review or editing ... option is the selected response to the Quick Check, the bill will not be saved and the Select Audit Groups window will open, permitting user selection of additional audit groups for that bill.

To run additional audits, click the check box corresponding to the desired audit group. Then click the OK button.

Auditing a Bill Batch

To audit a bill batch, complete bill entry for all bills in the batch. Then click the Work Flow Manager icon from the Accounting navigation icons. The Work Flow Manager will open.

Locate the Batch folder in the Work Flow Manager Tree View and then find the desired batch in the folder.

 

Once the bills are displayed, use the Shift-click or Ctrl-click functionality to highlight/select the bills in the batch. then RIGHT-click and select Audit from the popup menu options.

If the batch has already been closed, click the Options button from the Work Flow Manager title bar to open the Work Flow Options window.

From the General tab, select the desired display option in the drop-down list and then click the checkbox for the Show Bills in Batch filter option. Input the Batch Code, then click OK.

Once the bills are displayed, use the Shift-click or Ctrl-click functionality to highlight/select the bills in the batch. then RIGHT-click and select Audit from the popup menu options.

Audit results will be displayed in the Audit pane of the Work Flow Manager, directly below the bill list. Audits are listed individually for each bill. to view the audits, scroll down the bill list and highlight individual bills. You can also use the keyboard up/down arrow keys to select the next bill in the list.

Auditing from the Audit Manager

To audit from the Audit Manager, click on the Bill Audits folder for a desired Audit Batch. Then click the Audit menu item and select Execute/All Data or Execute/Selected.

• The All Data option will perform the audits in the selected audit group for all available bills in the database.

NOTE: This option may take quite some time, and is generally not recommended. If the Audit procedure is initiated using this option, the audits may be cancelled by clicking the Cancel Audit button that will appear in the Audit Manager title bar during audit execution.

• The Execute Selected option will open the Use Accounts and Dates to Select Bills window, which provides filter options to perform the audits in the selected audit group for only the desired vendors, commodities, cost centers, places or accounts (see graphic, below).

NOTES: Use the Drop-Down arrows to select the desired Vendor/Commodity/Cost Center/Place/Account. Use the horizontal "Add" arrow to add the accounts associated with the filter to the Selected Accounts list box. 
The Remove Selection arrow removes a highlighted account from the Selected Accounts list box. 
Multiple accounts can be added individually by selecting the individual account from the drop-down list, and then clicking the "Add" arrow to populate the Selected Accounts list box with the currently-selected account.

Re-Audit Notes

Audits may be run multiple times. The same audit may be part of multiple audit groups. Running an audit more than once on a bill will cause the bill to be re-audited, regardless of the results of the previous audit.

If the bill failed previously, the audit result record will be updated with the new result.

The audit result table (called BillMessageLog) does not record the Audit Group; i.e. the Audit Group is irrelevant.

For additional information on auditing in EnergyCAP, see the Audit Overview and Installation.

Overview

In the EnergyCAP database, there are three tables that maintain Bill details:

• Bill table
• Bodyline table
• BillAccountMeter (BAM) table

This topic provides detailed information about each table, including:

• Database table contents
• Database table relationships

A thorough understanding of these three tables will be helpful in maintaining the database and extracting meaningful data in EnergyCAP reports and custom spreadsheets.

The Bill Table

The purpose of the Bill Table is to store summary bill information. The Bill table contains all of the optional Bill Header data available through the Workflow Wizard setup process: The following table lists each data element column in the Bill table, along with the input Data Type, NULL information, and a Description of the element.

Relationships to Bodyline and BillAccountMeter

The Bill table contains the TOTAL COST of the bill – the sum total of the Bodylines of the bill. Unlike BillAccountMeter, no meter-specific information is included. When the Observation Type rules (Ignored, Credit/Provided, Debit/Consumed) are applied the Bodylines of a bill, the total amount should equal the TotalCost in the Bill table.

The Bodyline Table

The Bodyline table contains the line by line information that is entered on the Bill Template. Each line of the Bill Template – the value and the cost and even the caption – are stored in the Bodyline table. It contains the most detailed view of a bill as it contains each and every template line that makes up a bill.

The following table lists each data element column in the Bodyline table and what it refers to.

Relationships to Bill and BillAccountMeter

Bodyline cost, when the Observation Type rules are taken into account, should total up to be the Bill totalcost. The stored procedure cleanbill will read the bodylines of a particular bill and then summarize them to the BillAccountMeter table according to the Observation type rules. When a Bill is marked as dirty, cleanbill will read the bodylines that match the BillID of the dirty bill and then UPDATE the BillAccountMeter table rows that have the same BillID value. In this way, if the Common Roll-up unit is changed, the Bills in EnergyCAP can be marked as dirty so that cleanbill can re-calculate the proper Common and Global values in the BillAccountMeter table for each of the bills.

About Observation Types and the Observation Type Table

The Bodyline table contains an ObservationTypeID value which corresponds to a particular Observation Type. The master list of Observation Types can be referenced from within the EnergyCAP application by clicking on Tools then Unit System from the menu bar. The Types tab displays each Observation Type and the color indicates how the Observation Type is interpreted/calculated by EnergyCAP. The bodyline table contains the link to this Observation Type for each and every utility bill value that is entered on a bill. It is essentially the building blocks to determine the total cost, consumption, and demand for a bill.

The BillAccountMeter Table

The BillAccountMeter table contains summarized totals by meter (and where MeterID is NULL, a summary of the account charges) by account for each bill. So, as an example, if an account has two meters on it, and a bill is saved, there would be TWO BillAccountMeter records created – one for each meter UNLESS there were account charges entered as well in which case there would be THREE records created. This is the table which the majority of EnergyCAP Custom Spreadsheets, Reports, and PowerViews are based from. The true power of the BillAccountMeter table is its native, Common, and Global roll-up values. The cleanbill process utilitzes the Unit System conversions and setup to transform the bodyline value to Common and Global values. In other words, Bodyline would contain the value of 100 if a Bill were to have 100 THERMS of gas on it. BillAccountMeter would have 100 as the NativeUse, 10 as the Common Use (assuming the Common Roll-up for Natural Gas is DKTHERM), and 1 as the GlobalUse (assuming the Global Roll-up is MMBTU). So BillAccountMeter is critical to reporting on accounts of the same or different commodities that use various units of measure.

The following table lists each data element column in the Bodyline table and what it refers to.

Relationships to Bill and Bodyline

The sum total of the BillAccountMeter propcost should sum to be the Bill totalcost. The Observation Type, value, unitid, cost, and costunitid from bodyline are all used by cleanbill to determine the nativeuse, commonuse, globaluse, nativebilldem, nativeactdem, nativedem, commonbilldem, commonactdem, and commondem in BillAccountMeter. Every time a change is made to the Unit System in EnergyCAP such as changing the Common Roll-up for natural Gas from DKTHERM to MCF, the bill need to be marked as dirty and then the cleanbill process needs to be run so that the Common Use, Common Demand, and Global Use values can be re-calculated and updated to billAccountMeter so the reports will report the correct values according to the new Unit System settings.

Extracting billing data via a Custom Spreadsheet

A Custom Spreadsheet has been created as a tool for reporting on, and extracting billing data. The Custom Spreadsheet is not part of the standard EnergyCAP installation. To obtain a copy of this Custom Spreadsheet, download the Tech Support Custom Spreadsheets file (TechSupportCS.xml) from our Support Suite technical support website.

When the contents and the relationship between EnergyCAP’s three bill tables is understood, the Tech Support Custom Spreadsheets will provide an excellent tool for reporting on, and extracting billing data.

There are three primary billing tables in EnergyCAP Enterprise:

• Bodyline (Repository for bill details)
• Bill (Transaction-based summary table maintaining bulk of AP process information.)
• BillAccountMeter (Reporting table maintaining a consolidated meter view with calculated Use, Demand, Actual Demand, Billed Demand and cost per meter per bill)

Bodyline Table

The Bodyline table in EnergyCAP Enterprise contains utility bill data. Bodyline table design philosophy is that each bodyline essentially describes an element of a transaction or sale.  Something is being sold, and the bodyline describes the sale.  There are five data elements relating to bodyline data:

1. The commodity being sold (gas, electric, etc.)
2. The property of the commodity being measured (observation type—see below)
3. The unit of measure (CCF, kWh, etc.)
4. The cost of the sale (a value)
5. The unit of cost (US dollars)

EXAMPLE: If the purchased commodity was natural gas, it might be purchased by volume or energy content (heat).  If it were by volume the observation type would be usage and the unit of measure might be CCF or MCF.

Observation Types

In EnergyCAP, the property of the commodity being sold is called an “Observation Type”, since it is a measured or observed aspect of the commodity.  An observation type in EnergyCAP is defined as a noun (also called “class”) plus modifiers (three different types—see below).  The most common noun is “Usage.”  To view the list of observation types in EnergyCAP, click Tools>-Unit System>Types tab.  Note that there are several possible modifiers to Observation Type.  From the Tools>Unit System>Types tab, click on any observation type and then click Properties. The Class (noun) and Details (modifiers) for the Observation Type will be displayed.

- Class
A class is a ‘category’ that describes the Observation Type. There are a limited number of fixed Classes in EnergyCAP Enterprise.

- Time of Use Modifier
Instead of creating a noun for each time of use period, the observation type is set up to allow selection of one of several possible Time of Use periods. In the example, ONPEAKUSE, USE is the noun; ON PEAK is the modifier. There are a limited number of fixed Time of Use modifiers in EnergyCAP Enterprise.

- Origin Modifier
The Origin modifier enables the processing of deregulated bills by permitting tracking of both the retailer and local distribution company usage without it twice. Observation Types with an Origin set to anything other than Null have use ignored in the BAM table (described below) and therefore do not appear on standard reports.  Available Origins include: contracted, distribution, generation, standby, storage, transmission and stranded.  Custom reports can include bodylines that use these origin modifiers. There are a limited number of fixed Origin modifiers in EnergyCAP Enterprise.

- Applied Modifier
The Applied modifier was created to handle rebates and “negative” value situations.   The normal value is “credit/provided.”  Debit/consumed came about because of rebates which for accounting purposes offset expenditures, but were recorded as positive expenditures (all entries in the cost column are positive expenditures).  See the section below on Cost for the rule on using the “ignored” option for Applied. There are a limited number of fixed Applied modifiers in EnergyCAP Enterprise.

BAM (BillAccountMeter) Table

The BillAccountMeter table (BAM) is used to create most EnergyCAP reports. It is a consolidated meter view with calculated Use, Demand, Actual Demand, Billed Demand and cost per meter per bill. The BAM table accelerates report processing, because without it the system would have to evaluate all bodylines and apply various logic to each. The BAM table is maintained as bills are entered, thereby serving as a report ‘pre-processor.’

How BAM works

To calculate the BAM entries for a bill, EnergyCAP first determines that it is a valid bill.  It cannot be unapproved, void, or a recharge bill (billObservationMethodID = 3).

Then, EnergyCAP decides whether to accumulate the use, actual demand, billed demand, and cost in the BAM table for the appropriate account, meter and bill.  It does this by examining the noun (class) first, and then the modifiers.  Below is an explanation of the logic EnergyCAP uses to deciding whether to include each data element:

Use
Logic:  Include Use in BAM if (ObservationType.noun = 26) AND (ObservationType.creation IS NULL) AND (ObservationType.credit <> 3)

The Noun must be Usage (ID=26, see Noun table).  Note that “Billed Use”, use that is billed to others (tenants, etc) has a Noun ID = 3 and is therefore excluded.

The Origin modifier must be NULL; BAM does not include use for any deregulated bill type (generation, transmission, etc)

The Applied modifier must not be ignore (ID=3). (The Rebate credit is handled by this formula:  (abs(observationtype.credit - 2) * 2 - 1) * value.  The Applied modifier is 2, so (2-2)*2-1= -1)

By looking in the ObservationType table, it is straightforward to determine if any particular Observation Type will qualify based on these three criteria.

Actual Demand
Logic:  Include Actual Demand in BAM if (ObservationType.noun = 10) AND (ObservationType.credit) <> 3

The noun must be Demand(10)
The credit must not be ignore(3)

Billed Demand
Logic:  Include Billed Demand in BAM if (ObservationType.noun = 2) AND (ObservationType.credit <> 3)

The noun must be Billed Demand(2)
The credit must not be ignore(3)

Cost
Logic:  Include Cost in BAM if (observationtype.credit <> 3 or (observationtype.credit = 3 and (bodyline.cost is not null and (bodyline.unitid <> bodyline.costunitid))))

The Applied must not be ignore(3) UNLESS there is a cost on the bodyline, and the bodyline is ignored and the value being observed is not the cost.

This rather odd rule come from the fact that EnergyCAP is recording two pieces of data on every bodyline (use/demand/etc and cost), but the observationtype applies to only one of them (use/demand/etc).

To ignore cost on reports, place the cost on its own bodyline, and set the observation type Applied modifier to ignore(3).

Once the bodyline determinations for a meter on an account are done, EnergyCAP sums the uses and costs for the meter, and chooses the maximum actual and billed demand.  The Demand for the bill is calculated by examining those maximums and following the rule: if there is an actual demand, use that value; if not, use the billed demand.  Use and cost are also divided by the number of days in the bill to derive use per day and cost per day.  The BAM record is then created.

Reports

Most EnergyCAP installed reports which include Use follow the same rules as above, since report data is pulled from the BAM table and NOT from Bodyline (exceptions are AN21, BL07, BL16, BL16A, and BL19--data for these reports is taken from the Bodyline table).

The same is true for actual demand and billed demand.  Reports based on the demand_trend view also use the Demand rule of actual, then billed Demand. Cost follows the above rules, and reports based on the energy_cost_trend view use the prorated cost per meter which assigns account-only cost to each meter in proportion to its direct cost.

Custom reports, however, can be created to key off (or ignore) any of the fields discussed above.  As long as the data is captured in the Bodyline table, it is possible to report on it.

EnergyCAP offers several options for creating accounts and bills for internal and/or tenant billing (chargebacks/recharge billing):

• Bill Splits--Provides a method for dividing costs from a vendor bill among several internal/tenant accounts using a percentage basis.
• Calculated Accounts--Provides greater flexibility than Bill Splits by enabling bill creation based on a rate schedule or unit cost of a bill.
• Virtual Accounts--Provides greatest flexibility for accessing source account data and manipulating that data using Virtual Basic scripting language.

The diagrams below provide insights into each option, and how the various options may be blended to accommodate a variety of potential scenarios.

Bill Splits

Calculated Accounts

Virtual Accounts

Blended Account Scenario

NOTE: Once appropriate accounts are set up and and processes are in place, the IN01 report provides a means of generating invoice-like documents for internal and/or tenant billing. Other available invoice-related reports include IN02--Account History and IN03--Customer History. See the Sample Reports web page for more information.

Setting Up Special Charges

The Special Charges Editor allows you manage a list of special charges, any of which can be added to the bill entry template for a specific bill during the bill entry process. Special Charges enable data entry of one-time, occasional or periodic charges that do not exist on the bill entry template that has been associated with the account.

To manage the list of Special Charges available to bill entry personnel:

1. Select Accounting > Accounts. The Account Manager appears.

2. From the Account menu, select Editors> Special Charges. The Special Charges Editor window will open.

3. You can:

   • Create special charges

   • Modify special charges

   • Delete special charges

4. Exit the Special Charges Editor window by clicking Close.

Assigning Special Charges

Special charges are assigned during bill entry, and may be added to a bill whenever the pay amount total does not equal the sum of the template charges/costs (i.e., whenever there is a balance) at the time the Save button is clicked.

EnergyCAP will prompt bill entry personnel to allocate any balance (positive or negative) through the Remaining Balance window.

To assign a Special Charge:

1. Complete bill entry. The remaining balance, as displayed in the bottom right corner of the bill window, should equal the special charge.
2. Click the Save button for the bill. When the bill is out of balance, the Remaining Balance window will open automatically.
   
   
3. Select the radio button corresponding to the appropriate option for handling the balance, as follows:
• Click Assign to Meter(s), Prorate if Multiple Meters if all charges are to be associated with the meter or distributed among the several meters associated with the account. Charge will be prorated based on the percentage of cost that each meter contributes to the bill. 
• Click Assign Special Charge to Account if the balance is to be associated with the account and NOT the meter (appropriate in cases such as late fees where the costs are sporadic and/or not associated with energy consumption).
4. If assigning the special charge to an Account, select the desired Special Charge from the list of available special charges by clicking it's Caption.
   NOTE: If the appropriate charge does not exist, you can create it 'on the fly' by clicking the "+" button to open the Create Special Charge window.
5. Click the Select button from the Remaining Balance window to add the selected Special Charge. The Remaining Balance window will close and EnergyCAP will finish saving the bill.

The best practice for handling refund checks in EnergyCAP is to identify the account(s)/meter(s) and cost/usage associated with the refund and to apply the refund to the associated account(s)/meter(s) in the relevant billing period(s). This helps ensure the integrity of EnergyCAP data, which will enhance the accuracy of related calculations including total use and cost, cost per unit, budgets, cost avoidance, and accruals.

Refunds can be handled by either entering a refund “bill” with negative values for the relevant billing period (see image below) or by adjusting the use and cost of the original bill to take the refund into account.

[A refund “bill”—note negative values for use and cost.]

The appropriate choice will depend on the relationship between EnergyCAP and the organization Accounts Payable department. If EnergyCAP is serving as a ‘front end’ for A/P and exporting billing data to AP directly for payment, special arrangements may need to be made for refund processing, especially if the entire A/P billing cycle is tracked in EnergyCAP. In this case, consult with the A/P department to determine the best refund process.

QUESTION: What if the refund statement does not include any usage data?

ANSWER: If the statement includes enough information to determine a time period to which the refund should be applied, use the historical cost/day and use/day information available in EnergyCAP to determine usage. If neither usage nor time period is available, contact the utility vendor to obtain additional information.

QUESTION: What if the refund is due to a long-term running overcharge?

ANSWER: If the time period can be determined, then individual bills can still be edited, or a series of “refund bills” can be applied.

QUESTION: How important is it that ECAP agrees exactly with A/P with respect to refund tracking?

ANSWER: This depends upon the organization use of EnergyCAP, and the processes tying EnergyCAP to A/P.

QUESTION: How will refunds appear in PowerViews/reports?

ANSWER: If handled using the best practice described above, refunds will balance with the initial overcharge, providing an accurate graphical representation of use and cost for all PowerViews™. Columnar reports will display refund bills separately when refunds are recorded in that manner, and it would also be possible to create a Bill Message and Key called REFUND (RF) that could be used to flag all refund bills. The BL20 report could then be used to generate monthly cost totals for refunds. Another solution is to create an Issue (Cost Avoidance/Issue Tracker) and document the refund with an Issue Type. CAP14 reports on Issue Cost.

CAUTION: The Groups Manager uses different logic than the other PowerViews (Bills/Facility) and will produce different cost/day and use/day results when determining these values for accounts/meters for refunds handled with a separate, negative refund transaction.

QUESTION: How will refunds impact budget creation and audits, and is that a problem?

ANSWER: Again, if best practices are followed, budgets (which are based on historical billing data) will be more accurate. EnergyCAP may flag an overcharge during auditing. The “refund” bill may also be flagged, depending on the use/cost values associated with each.

Question: Is there a way to have a tax bill item included in the bill total, but excluded from the Average Unit Cost (AUC) in the Power Views? For example, a bill for 100 kWh has a total charge of $110, where $100 is the cost for the kWh consumed and $10 is Tax. Currently, the Power View shows an AUC of $1.10, rather than an AUC of $1.00.

Answer: Yes. However, it is important to consider that many reporting organizations may consider taxes as part of the cost of doing business. Therefore, it is recommended that taxes be included in the AUC calculations. That being said, if it is necessary to exclude taxes from the AUC calculations, those taxes will need to be assigned to the Account Level, rather than the Meter Level, during EnergyCAP Bill Entry. There are two methods that can be used to have the taxes assigned to the Account Level.

1. Set up a Special Charge for the tax. Do not include a Bill Line Item for the tax on the Bill Entry template. During bill entry, enter the Pay Amount, consumption, the cost associated with consumption, and other bill information and related costs. The remaining balance for the bill will be the Tax Amount. Save the bill. During the save process, the EnergyCAP software will prompt to determine how to distribute the remaining balance. Select the “Assign Special Charge to Account” option and then select the appropriate Special Charge (for example, “Canadian PST Tax”).
2. Create a ‘Tax’ Template (commodity = Money) to track the Tax Bill Line Item. Assign this ‘Tax’ Template to the Account and have another commodity Template assigned to the Meter. The Bill Entry Screen will show a Bill Line Item for the User to enter the Tax data allocated to the Account, and then it will show the Bill Line Items for the User to enter the commodity data allocated to the Meter.

Disadvantages with moving Taxes to the Account Level:

• The General Tab Power View numbers for the Facility Tree are Meter-Based and will not match the General Tab Power View numbers for the Cost Center Tree, which are Account-Based.
• The General Tab Power View numbers for the Cost Center Tree will not match the Commodity Tab and Monthly Tab Power View numbers, because they are Meter-Based.
• Report Totals between the Analysis Reports (meter-based) and the Billing Reports (account-based) will not match.
• Budgets and Cost Avoidance calculations are meter-based. Therefore, they will not include taxes.

It's important in EnergyCAP to accurately DATE cost and consumption data. This ensures that expenses and use are tracked correctly in time-related graphs and charts. For most utility bills this is easy to do because the Start Date and End Date defined for the utility billing period clearly fall in one month or another.

Many EnergyCAP graphs, charts, and reports display utility billing data based on the Billing Period. When you have a bill that is dated, say, June 15 to July 15, selecting the proper Billing Period can be complicated. In terms of monthly reporting, is it a June bill or a July bill? And what about quarterly billing, and so on. How does EnergyCAP decide?

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 accomplish these two 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.

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

Similarly, when there ALREADY exists a bill for THIS month AND the 'middle-of-the-bill' date is equal to or greater than the 26th of THIS month, AND the billing period End Date is greater than Day 1 of the NEXT month, then the billing period will be moved forward a month.

To enter bills, first locate and select the account to which the biill is to be assigned.

To select a specific account for bill entry:

1. Open the Account Manager (Accounting > Accounts) and click the Accounts icon.
2. Bowse the account Tree View and click the desired account to highlight it.
   1. If the account location in the Tree View is not known, click the binoculars icon   from the menu bar to open the Account Selection window:
      
      
   2. In the Account Code field, type any or all of the account's unique, self-defined identifier. All potential matches are dynamically displayed in the Matches list.
      NOTE: To include inactive accounts in the search, click the Show Inactive checkbox from the Account Selection window..
   3. Double-click the account ID from the displayed list  (or select it and click OK). The Account Tree View will automatically open to that account.

NOTE: For a more advanced account search, use the Find Account button  from the Account Manager title bar to open a Search window. With the advanced Search window, multiple search criteria can be selected:

A bill can only be created for a bill-enabled account with an associated template effective for the period relevant to the bill start date. A current bill is the next or most recent usage or cost data to be entered in the system. When using the Create Current Bill Process, the start date defaults to the end date of the most recent bill for that account in the system. If you need to enter a bill with a different start date, go to Creating a Historical Bill.

You can create current bills from the EnergyCAP Work Flow Manager or the Account Manager.

1. To create a current bill from the Work Flow Manager:

   1. Select the Bill folder.

   2. From the Bill menu, select New > Current. The Account Selection window appears.

   3. In the Account code field, enter any or all of the account's unique, self-defined identifier. Matches are displayed in the Matches list.

   4. Select the appropriate account.

   5. Click OK. The Create Bill window appears.

   6. Enter bill information.

   7. When done, click Save to save the bill information. Or use the Alt-N or Alt-S keyboard combination. Alt-S saves the current bill and does nothing else. Alt-N and the Save button saves the current bill and moves forward per the user's work flow manager option settings (Tools > Options-This Manager > Bill Entry tab).

2. To create a current bill from the Account Manager:

   1. Click to navigate to the cost center under which the account exists.

   2. Select the account to add the bill.

   3. From the Bill menu, select New > Current. The Create Bill window appears.

   4. Enter bill information.

   5. When done, click Save to save the bill information.

A bill can only be created for a bill-enabled account with an associated template effective for the period relevant to the bill start  date. A historical bill is any usage or cost data to be entered in the system relevant to a period in time prior to the most current data already in the system.

When creating a historical bill, the system prompts you for a start date instead of using the default start date of the most recent bill in the system. If you need to enter a bill with a start date that is more recent than the most current bill in the system, go to Creating a Current Bill.

You can create historical bills in the Work Flow Manager or the Account Manager.

1. To create a historical bill in the Work Flow Manager:

1. Select the Bill folder.

2. From the Bill menu, select New > Historical. The Account Selection window appears.

3. In the Account code field, enter any or all of the account's unique, self-defined identifier. Matches are displayed in the Matches list.

4. Select the appropriate account.

5. Click OK. The Assign Bill Dates window appears.

6. Go to step 3.

2. To create a historical bill in the Account Manager:

1. Click to navigate to the cost center under which the account exists.

2. Select the account to add the bill.

3. From the Bill menu, select New > Historical. The Assign Bill Dates window appears.

4. Go to step 3.

3. In the Assign Bill Dates window Start date field, enter the date or use the drop-down calendar to select the date.

   The calendar day signifying the beginning of the specific billing interval.

4. In the Assign Bill Dates window End date field, enter the date or use the drop-down calendar to select the date.

   The calendar day signifying the conclusion of the specific billing interval.

5. Click OK. The Create Bill window appears.

6. Go to Creating a Bill.

During bill entry, complete or edit any line items associated with the bill entry template. Depending on the nature of the bill entry template used, some available fields may require a value and others may not. It is impossible to address the bill creation process with specificity. Many of the displayed labels and inputs are components of one or more templates assigned to the account or the associated meter(s). Others are part of the bill entry work flow as defined in the Work Flow Wizard.

1. Enter or edit the following fields:
   

   • Start date: The calendar day signifying the beginning of the specific billing interval. The default is the end date of the most recent bill entered in E2is or the current date if no bills are currently entered.

     • To change the date, type the date or use the drop-down calendar to select the date.

   • End date: The calendar day signifying the conclusion of the specific billing interval. The default is the end date based on the account billing cycle.

     • To change the date, type the date or use the drop-down calendar to select the date.

   • Billing period: The user defined chronological division used to group bill data for accounts payable purposes.

     1. Select the month from the drop-down list. This field is auto-populated based on the midpoint between bill Start and End dates but can be changed by the user.

     2. Select the year from the drop-down list. This field is auto-populated based on the midpoint between bill Start and End dates but can be changed by the user.

   • Pay amount: The monetary figure representing the balance of payment due.

2. If applicable, complete or edit options including the following:
   

   • Batch number: The bill batch that includes the current bill.

   • Invoice Number: The unique vendor identifier for the bill.

      

   • Account Period: The chronological division used to group bill data for financial accounting and/or budgeting purposes.

   • Next Reading: The calendar day of the subsequent scheduled meter/counter examination.

   • Control Code: File or document scan identifier distinguishing the storage location of the physical bill.

   • Statement Date: The calendar day the account record was generated.

   • Due Date: The calendar day payment is expected.

   • Account Year: The specific twelve-month period with regards to your organization’s financial accounting system.

   • Estimated checkbox: Indicates that the bill values are estimated, not actual, values.

3. Complete the unique template information for the commodity associated with the account/meter, including:

• Use: The amount of the commodity consumed, as indicated on the billing statement.

• Cost: The cost for purchasing the commodity (may be the same as pay amount or may be different).

• Other: Unique items required for energy management and utility bill tracking. Will vary from bill template to bill template

• Special Charges: Additional bill elements that are customarily not associated with usage, such as late fees, etc.

1. Click in the Bill Entry Grid (the 'yellow' portion of the Bill Entry window) to activate the Special Charge toolbar icons  below the bill entry window title bar.
   
   NOTE: The toolbar icons are only active when you are creating or editing a bill. The icons will not be active from the View Bill window.

2. To add a line for the special charge to the bill entry window, click the Add Special Charge icon  . The Remaining Balance window will open. Use the radio buttons to assign the charge(s) to the account or meter. Then select the desired charge from the list box.

   1. Special Charges are listed by caption and unit of measure. Sort the list of available Special Charges by clicking a column heading. To reverse sort, click the column heading again.

   2. If the Special Charge list does not contain an appropriate charge for the specific situation, the list can be modified from the Account Manager (Account/Editors/Special Charges). Or a new special charge can be created 'on the fly' from the Remaining Balance window by clicking the 'plus' button and completing the required information in the Create Special Charge window. For more information, see Setting Up and Assigning Special Charges.

3. To remove a line from the bill entry window, click the specific line data entry field and then click the Remove Line icon   from the bill menu bar. The Confirm window will open. Click Yes to remove the line or No to cancel the action.

The Edit Bill toolbar contains a number of shortcut tools you can use to access bill edit functions.

Toolbar Icon	Shortcut	Description
	Previous/Next	View the Previous/Next bill in the sequence.
	Properties	View the bill properties
	Override G/L Code	Override the G/L code. This icon becomes active when the focus of the application is on a bill entry field. See the topic on Overriding a G/L Code for a specific bill.
	Add Special Charge (left icon)	Add a special charge
	Remove Line (right icon)	Remove a line
	New Message	Create a new bill message
	Bill Messages	View bill messages
	Account Alerts	View Account Alerts (Account Alerts can be set in the Account Properties window from the Alerts tab).
	Account Memos	View Account Memos (Account Memos can be set in the Account Properties window from the Memos tab).
	Bill Image	View the scanned bill image (icon is active only if an image is available to view)
	Issue Tracker	Create a bill issue using the Cost Avoidance Issue Tracker tool. Issues can be tracked, shared, and reported on.
	View Issue	View existing bill Issue.
	Find	Locate a particular Account using the Find Account window.
	Cancel Edit	Exits the Edit window and opens a Create Bill window for the current account. CAUTION: If used from the Edit window for an historical bill, the historical bill dates will be the default date values.
	Calculator	NOTE: This icon is only active when the cursor/focus is in a numeric field in the bill enty grid ('yellow' portion of the bill entry screen).  Permits entry of mathematical formulae. The result will automatically be entered in the current field.
	Run Rate Processor	NOTE: This icon is only active when the current account/meter has an associated rate.  Runs the rate processor for the rate associated with the current account/meter.
	Assign Template	Opens the Assign Templates and G/L Records window for the current account.
	Refresh Templates on Screen	Updates the Bill window with the current bill template.
	Void/UnVoid	Voids or UnVoids the bill (toggle) while maintaining billing data in the system. Void bills are not counted in reports or PowerViews. They will not display in the bill entry window. They will display in the Bills tab, and may be unvoided using the right-click popup menu, or by editing the bill, clicking on the Void/UnVoid icon, and selecting the UnVoid option. NOTE: Voided bills can be hidden in the Account Manager Bill list if the Hide Non-Standard Bills checkbox is checked (see Bills tab). Although a change is made to the Bill History List in the Account Manager as soon as the the box is checked/unchecked, there is no communication between the Account Manager and the Bill Entry window so the state of the checkbox is not recorded by the EnergyCAP application until the application shuts down and is restarted. If it is necessary to see/hide voided bills quickly, set the checkbox appropriately and restart the EnergyCAP application.
	Edit this Bill	Opens an Edit window for the currently-viewed Bill.
	Split Bill	Splits the current bill by running the Bill Split Processor for the current bill. This icon is only active from the Bill Edit window for bills that were not split during data entry
	Unsplit Bill	Reverses/unsplits the bill split for the current bill. This icon is only active from the Bill edit window for a bill that has already been split.
	Line Item Adjustment	Opens the Line Item Adjustments window to permit Adjustments for all or part of any bill line item. Adjustment Bills will be created for any adjusted line item. If exporting bills, consult with your A/P department regarding an appropriate adjustment process.

Accessing the Edit Bill window

Access the Bill Edit window from the Work Flow Manager. Click Accounting>Work Flow Manager and click to open one of the Bill or Batch folders. Then click to select the bill from the bill list, right-click, and select the Edit option from the popup menu. The Edit Bill window will open.

It is also possible to access the Bill Edit window from the historical bill list in the Create Bill and View Bill windows.

Double-click the bill from the list. The Edit Bill window will open.

It is also possible to access the Bill Edit window from the Account Manager. Click Accounting>Accounts to open the Account Manager. Then click the Bills tab.

Highlight the historical bill in the list, right-click the bill line, and select the Edit option from the popup menu. The Edit Bill window will open.

Bill Editing Options

Once the Bill Edit window is open, you can:

1. View the bill's properties

1. View the bill's scanned image

2. View, add, or print bill messages

3. View account alerts

4. View account memos

5. Edit bill entry fields

6. Edit or remove line items

7. Access the Bill Grid calculator

8. Assign a special charge

Bill Auditing Options

To audit the bill, click the  button at the bottom of the Edit Bill window. The bill will be audited using the current Work Flow settings for the quick check audit. After the audit is complete, audit results will be posted in a popup window. When the window is closed, additional audit groups may be selected from the Select Audit Groups window.

After making your selections, click Save to save the edited bill and close the Edit Bill window.

To close the Edit Bill window WITHOUT saving changes, click .

To override the G/L Code for a specific bill:

1. Create the bill requiring the G/L change OR open an existing bill for editing.
• For bills with multiple meters: Click a bill line item associated with the G/L change. The G/L Override icon on the bill entry toolbar will become active. Click the icon. 
  
  The Override G/L Record ... window will open.
• For bills with a single meter: Click the G/L Link from the bill entry template.
  
  
  The Override G/L Record ... window will open.
2. Input the first characters of the desired G/L code into the Filter: field. EnergyCAP will display a list of matching G/L Codes in the text box.
   
   
3. Click to select/highlight the desired G/L Code from the list.
   NOTE: If desired,  use the PLUS button to create and save a new G/L code "on the fly".
4. After the correct G/L Code has been highlighted from the text box, click the Select button. The Override G/L Record ... window will close, and the highlighted G/L Code will be inserted into the bill.
   
5. Save and process the bill using your normal procedure.

The View Bill toolbar contains a number of shortcut tools you can use.

You can view bills in the Work Flow Manager or the Account Manager.

1. View a bill in the Work Flow Manager by selecting Accounting > Work Flow Manager. The Work Flow Manager appears.

   1. Click to navigate the Bill folders and select the appropriate bill.

   2. Right-click and select View. The View Bill window appears.

   3. Go to step 3.

2. View a bill in the Account Manager by selecting Accounting > Accounts. The Account Manager appears.

   1. Navigate in the Account Manger Tree View to the cost center under which the account exists.

   2. Select the account under which the bill exists.

   3. Select the specific bill to view.

   4. From the Bill menu, select View. The View Bill window appears.

   5. Go to step 3.

3. If there is an alert associated with the account, the Account Alerts window appears. Account alerts are listed by severity and message. You can sort the lists by clicking a column heading. To sort the lists in reverse order, click the column heading once more. Close the Account Alerts window by clicking the X in the upper right-hand corner of the window.

4. From the View Bill window, it is possible to:

   • View bill properties

   • View and print bill messages

   • View account alerts

   • View account memos

   • View bill entries

   • View the bill's scanned image (see below)

   • View the account's previous bill by clicking

   • View the account's next bill by clicking

5. Close the View Bill window by clicking .

Viewing Bill Properties

1. View the bill's properties by clicking . The Bill Properties window appears. See Viewing a Bill's Properties.

2. Exit the Bill Properties window by clicking OK.

Viewing a Bill's Scanned Image

it is possible to view the scanned bill image if bill image files are available to view and have been correctly linked in EnergyCAP. For more information on linking bill images in EnergyCAP, see the topic on setting Global Options.

To view a scanned bill image associated with the current bill, click the Bill Image icon . The Bill Image view window will be displayed. Click the "X" to exit the Bill Image window.

Viewing and Printing Bill Messages

1. View bill messages by clicking . The Bill Messages window appears. 
   Messages are listed by severity, key, message, and status. You can sort the lists by clicking a column heading. Sort the lists in reverse order by clicking the column heading once more.

2. Print a message by selecting the appropriate message then clicking . The message text is printed to your default printer.

3. Exit the Bill Messages window by clicking Cancel.

Viewing Account Alerts

1. View the account alerts by clicking . The Account Alerts window appears. Account alerts are listed by severity and message. You can sort the lists by clicking a column heading. Sort the lists in reverse order by clicking the column heading once more.

2. Close the Account Alerts window by clicking the X in the upper right-hand corner of the window.

Viewing Account Memos

1. View the account memos by clicking . The Account Memo window appears.

2. Close the Account Memo window by clicking the X in the upper right-hand corner of the window.

Viewing Bill Entries

Bill entry fields displayed are:

• Account The associated account.

• Description Any description that was entered.

• Vendor The associated vendor's display.

• Address The account's service address.

• Start date The start date of the specific billing interval.

• End date The end date of the specific billing interval.

• Billing period

• Pay amount The dollar amount of the payment.

Additional bill entry fields that may be displayed:

• Account Period The chronological division used to group bill data for financial accounting and/or budgeting purposes.

• Invoice Number The unique vendor identifier for the bill.

• Control Code File or document scan identifier distinguishing the storage location of the physical bill.

• Statement Date The calendar day the account record was generated.

• Due Date The calendar day payment is expected.

• Account Year The specific twelve-month period with regards to your organization’s financial accounting system.

• Estimated Indicates the bill values are not actual values.

• Next Reading The calendar day of the subsequent scheduled meter/counter examination.

• Vendor address The vendor's remitting address.

The Bill Grid area displays any line items and special charges.

1. Select Accounting > Accounts. The Account Manager appears.

2. Click to navigate to the cost center under which the account exists.

3. Select the account under which the bill exists.

4. Select the appropriate bill.

5. From the File menu, select Properties. The Bill Properties window appears.

6. Select the General tab to view general bill properties.

   • Created by: The creator's user ID.

   • Creation date: The date the bill was created.

   • Modified by: The user ID of the last individual that modified the bill.

   • Modified date: The date of the last modification.

   • Approval date: The date the bill was approved.

   • Export date: The date the bill was exported.

   • Batch number: Displays the batch number if the bill was created through batch entry.

   • Creation: Indicates whether the bill was created manually or through batch entry.

   • Estimated: Indicates the bill values are not actual values.

7. Select the Headers tab to view the optional header fields (optional data entry values) and their default values (data automatically populated) associated with the bill.

   • Batch Number: Displays the batch number if the bill was created through batch entry.

   • Statement Date: The calendar day signifying when an account record was generated.

   • Due Date: The calendar day signifying when payment is expected.

   • Next Reading: The calendar day signifying the subsequent scheduled meter/counter examination.

   • Control Code: File or document scan identifier distinguishing the storage location of a physical bill. Input values up to 255 characters.

   • Invoice Number: The unique vendor defined identifier for a bill.

   • Account Period: The user defined chronological division used to group bill data for financial accounting and/or budgeting purposes.

   • Account Year: The specific twelve-month period with regards to your organization's financial accounting system.

8. Exit the Bill Properties window by clicking OK.

EnergyCAP, Inc. typically recommends saving bills in PDF format for easy viewing on most computers since most modern computers have Adobe Acrobat Reader installed. Some clients, however, have TIFF files because older bill images were saved that way, or their current scanning software prefers TIFF. Although the bill can still be viewed when using EnergyCAP, the bill image opens in an second, external program - typically Microsoft Document Viewer. The downside is that EnergyCAP is left with a blank open window (which usually has an erroneous error message in it). Although this presents no real functionality problems, it can be annoying to have to always close two windows when viewing bill images.

In researching an acceptable plug-in solution for clients, EnergyCAP, Inc. has only found one viable (and free) option: AlternaTIFF. Once installed, AlternaTIFF works very well and has some very nice features that other fee-based plug-ins do not. EnergyCAP, Inc. does not provide support for the plug-in itself, but offers this information as an option for clients who may often need to view TIFF format bill images.

To install the AlternaTIFF software:

1. Obtain Administrator rights to your computer (required for installation of ActiveX components).
   NOTE: If you do not have administrator rights, AlternaTIFF provides a ZIP file with an installer your network administrator could use to install the plug-in.
2. Browse to http://www.alternatiff.com/ with Internet Explorer.
3. Click the first of the three options (ActiveX control, auto-install) and follow the instructions.
4. Once the software is installed, the user will be prompted to click the component in a browser window, opening a registration window. Name, Company and email are required. Once registered, click to see the test page to ensure that the Plug-In is functioning correctly.
5. After confirming that the plugin is working, open a My Computer window.
6. Go to Tools > Folder Options
7. Click the File Types tab
8. Find TIF as a file type
9. Click the Change button, and then select the Internet Explorer file type for the association (Yes, Internet Explorer!).

This association will cause a browser to open to view any TIFF image. The browser will use the new plug-in.

1. Add a bill message by clicking the Bill Mesage icon  . The New Message window will open.

   NOTE: Once a bill message has been created, it has the potential of creating an audit trail, and cannot be deleted!

2. You can either:

   • Select a pre-defined message

   • Select and edit a pre-defined message

   • Create a message

3. To select a predefined message:

   1. Select the Pre-defined check box.

   2. Select the appropriate message.

4. To edit a pre-defined message:

   1. Select the Pre-defined check box.

   2. Select the appropriate message.

   3. Select the User defined check box.

   4. If applicable, in the Key field edit the message key.

   5. If applicable, select a new severity level from the drop-down list.

   6. In the Message field, edit the message. You are limited to 255 characters.

5. To create a message:

   1. Select the User defined check box.

   2. In the Key field, type a message key.

   3. Select a severity level from the drop-down list.

   4. In the Message field, enter the text to be displayed. You are limited to 255 characters.

6. After making your selections, click OK. The New Message window closes.

1. View bill messages by clicking the View Message icon  . The Bill Messages window will open.

   • Messages are listed by audit severity, key, message, and status. You can sort the lists by clicking a column heading. Sort the lists in reverse order by clicking the column heading once more.

2. Print the messages by clicking the Print icon  . The message list will be printed using your default printer.

3. Exit the Bill Messages window by clicking Close.

Account Alerts are account-based bill messages that are accessible from the Bill Entry window in EnergyCAP. Account Alerts are valuable for several reasons:

• They can be limited to time parameters set by the user.
• By their presence they can 'trigger' EnergyCAP audits (depending on the audits run and the audit severity settings determined by the user)
• They are highly visible messages that will appear in a popup window each time an editable window for the account is opened when the user's computer clock date falls within the Alert parameters.

NOTE: Account alerts are triggered during manual bill entry in EnergyCAP. They will not be triggered during bill import processes.

REAL-LIFE SCENARIO:

QUESTION: My real estate department has informed me that we are selling building #505 on December 1 and will no longer be responsible for the utility bills after that date. There are two electric accounts, a gas account and two water accounts. How can this EnergyCAP feature help me?

ANSWER: Set up a Bill Account Alert for each of the accounts associated with the building. The message text could be “Do not pay for services provided after December 1—Building Sold” The Begin Date for the Alert could be set for December 1. No End Date would be required. This is the date that the Alert would begin displaying from the Edit/Create bill windows. The initial severity level could be fairly low (PASS) to ensure that final utility bills and payment data could be recorded in EnergyCAP. After the final bills had been processed, the severity level could be increased to a “Fail” status, preventing further entry of billing data for that meter. Alternatively, you could set two Alerts; the first with a Begin Date of 12/1/08 (“Pass” status) and the second with a Begin Date of 1/1/09 (“Fail” status) and appropriate Alert messages.

Adding an Account Alert

To add an Account Alert:

1. Navigate to the Account Manager (Accounting/Accounts) and select the desired account.
2. Right-click the account and select Properties from the popup menu. The Account Properties window will open.
3. Click the Alerts tab. Current Alerts will be displayed in the Alerts: text box.
4. Click the "+" button to add a new Alert. Then double-click in any of the cells (Severity, Begin date, End Date, and/or Message) to input desired information. Click the drop-down arrows to reveal pre-existing options (including a calendar selection feature for Begin/End Date) or input information using the computer keyboard.
   
   
   
   NOTE: Click the minus button to remove the currently-selected Alert from the list.
5. When done, click OK to save the Alert(s) and close the Account Properties window.

For information on creating an Account Alert for multiple accounts, see: Modifying an Account.

Bill Account Alert FAQs:

How do I edit an alert?

ANSWER: You can modify Alerts at any time in a manner similar to the way you created them. From the Alerts tab, instead of clicking the “+” icon to create a NEW Alert, position your cursor in the field(s) you want to change in the Alert you want to change. Then update the values and click OK to save the Alert and close the Account Properties window.

What user permissions do I need to create, edit or delete an alert?

ANSWER: You will need View/Modify/Delete/Create access to the Account Properties window (Application/Accounts from the Permissions tab in User Properties).

Can I create, edit or delete an Alert from the Bill Entry screen?

ANSWER: Yes, by clicking on the Account Code link (highlighted in blue).The Account Code link will open the Account Properties window for the account; alerts can be created/modified from the Alerts tab in Account Properties.

1. View the account alerts by clicking the Account Alert icon  . The Account Alerts window will open.

   • Account alerts are listed by severity and message. Sort the lists by any column heading by clicking on the heading. Reverse the sort order by clicking the column heading again.

2. Close the Account Alerts window by clicking the Close button or the X in the upper right-hand corner of the Account Alerts window.

Account Memos are account-associated messages that can be viewed, edited and retrieved from the Bill Entry window as well as the Account Properties window in EnergyCAP.

Since Account Memos are associated with the ACCOUNT rather than the bill, and because the messages are not associated with a particular timeframe, Account Memos are useful for adding 'informal' account-related information that can and should be seen and edited by both bill entry and supervisory personnel.

Adding an Account Memo from the Bill Entry window

1. To add an Account Memo from the Bill Entry window, click the Account Memo icon  . The Account Memo window will open.
   
   NOTE: A zero to the right of the icon indicates no pre-existing memo is available for that account. A "1" indicates that a memo is present..
2. Click the Edit button to add the timestamp and render the window editable.
3. Input the desired Account Memo information.
4. Click Save to save the Memo and close the Account Memo window. Or click Cancel to exit the Account Memo window without saving the changes.

Adding/Editing an Account Memo from the Account Properties window

1. To add an Account Memo from the Account Properties window, first select the account from the Acccounting Tree View and right-click to select Properties from the popup menu options. The Account Properties window will open.
2. Click the Memo tab to display any existing memos.
3. Click the Edit Memo button to render the Memo: window editable
4. Input the desired Account Memo information.
5. Click OK to save the Memo and close the Account Memo window. Or click Cancel to exit the Account Memo window without saving the changes.

Viewing and Editing an Account Memo

1. View the account memos from the Bill Entry window for the associated account by clicking the Account Memo icon  . The Account Memo window will open.

2. Edit the Account memo by clicking the Edit button.

3. Close the Account Memo window by clicking the Close button or the X in the upper right-hand corner of the Account Memo window.

Bill Entry Notes are meter-based one-line annotations that can be added to the Bill Entry grid via Meter Properties. They will appear on the Bill tab for all current or historical bills for the account associated with the meter. Bill Entry Notes are a good way to provide meter-specific information that might be valuable for bill entry personnel or Energy Managers to know when viewing utility bills.

Adding a Bill Entry Note

To add a Bill Entry Note:

1. Navigate to the Facility Manager (Setup > Facilities) and select the meter with which the Note will be associated.
2. Right-click the meter and select Properties from the popup menu. The Meter Properties window will open.
3. Click the Bill Entry Note tab.
4. Input the desired note in the Bill Entry Note field and then click OK. The Bill Entry Note will be saved and the Properties window will close.

Viewing a Bill Entry Note

Bill Entry Notes are viewable from the Bill Entry grid for all current or historical bills associated with the meter with which the Note is associated.

To view a Bill Entry Note:

There are many ways to view a bill with which a Bill Entry Note has been associated. One way is to navigate to the Account Manager (Accounting>Accounts) and select the account with which the meter with the note has been associated. Then click the Bills tab and double-click any of the historical bills associated with the account. The Bill Entry Note will be displayed in the Bill Entry grid.

The Bill Entry windows (Create Bill/View Bill/Edit Bill) provide an icon for creating a new Bill Issue 'on the fly' using the Cost Avoidance Issue Tracker. See the Issue Tracker topic in Cost Avoidance for more information on this energy management tool.

In addition to providing a bill entry template, the View/Create/Edit Bill windows provide an abundance of contextual historical data for the selected account and/or meter.

The view pane in the upper right portion of the Bill window displays a year of utility billing information in a spreadsheet format, including:

• Billing Period Month

• Billing Period Year

• Bill Start Date

• Bill End Date

• Bill Pay Amount

The tabbed pages at the bottom of the View/Create/Edit Bill windows display more extensive historical utility billing information drawn from the EnergyCAP database for the selected account and the associated meter(s):

• Bill Tab--Displays the bill entry template and associated with the account/meter, and associated values for the selected bill.
  

  

• Meter History Tab--Provides a spreadsheet view of all historical information for the selected meter. Bills highlighted in blue are yearly 'markers' corresponding to the month of the most current bill. The bill highlighted in green is the bill currently being viewed.

• Account Cost Tab--Displays a monthly comparative line graph with 12 data points to produce a year of cost history for the account.
  

• Account Cost/Day Tab--Displays a monthly comparative line graph with 12 data points to produce a year of cost per day history for the account.
  

• Meter Cost/Day Tab--Displays a monthly comparative line graph with 12 data points to produce a year of cost per day history for the currently-selected meter.
  

• Meter Use/Day Tab--Displays a monthly comparative line graph with 12 data points to produce a year of usage history for the currently-selected meter.
  

• Unit Cost Tab--Displays a monthly comparative line graph with 12 data points to produce a year of unit cost history for the currently-selected meter.
  

• Demand Tab--Displays a monthly comparative line graph with 12 data points to produce a year of demand history for the account.
  

• Load Factor Tab--Displays a monthly comparative line graph with 12 data points to produce a year of load factor history for the account.
  
  NOTE: With Release 6.0 and subsequent, accrual bills are no longer included in this graph view.
  

The chart data for both the ACCOUNT and METER graphs is drawn from the utility billing information in the EnergyCAP database.

NOTE: The ACCOUNT graphs will be a rollup of all associated account and meter charges/usage. The METER, DEMAND, LOAD FACTOR and UNIT graphs will display the currently-selected meter information, as archived in the Meter History. For multi-meter accounts, you can change the meter focus using the drop-down box at the bottom of the Bill window.

Viewing Bill Problem Messages

1. Select Accounting > Work Flow Manager. The Work Flow Manager is displayed.

2. Click to navigate to the folder where the bill resides.

3. Click in the bill list to higlight/select the appropriate bill. Associated problem messages will be displayed in the bottom pane of the View window, including data for:

   Severity
   Status
   Message
   Date Created
   Created By
   Comment

Editing Bill Problem Messages

Once created, Problem Messages can be edited from the Work Flow Manager. To maintain the audit trail, only two fields are editable: 

The Status can be updated. There are three Status options:

1. Problem
2. Fixed
3. Ignored

The Comment: field may also be edited to include a text comment of up to 255 characters in length.

To edit a Bill Problem Message:

1. Select the associated bill from the Work Flow Manager and double-click the message line. The Problem Properties window will open.
2. Use the Status: drop-down arrows to select the desired status.
3. Input the desired text in the Comment: field.
4. Click OK to save changes and close the Problem Properties window. Or click Cancel to exit the Problem Properties window without saving changes.

1. Select Accounting | Work Flow Manager. The Work Flow Manager appears.

2. Click to open the Bill folder and navigate to the appropriate bill.

3. From the Message menu, select Show Ignored Messages. The Message view displays bill messages with the status of Ignored.

A calculator is available during bill entry (creating or editing bills). The total calculated can populate the cost field.

The auto-decimal option also applies to the bill calculator. For auto-decimal, each numeric entry is divided by 100 before calculating the resulting value and inserting it into the grid.

1. Select the appropriate line in the Bill Grid. The Calculator icon  will become active.

2. Click the Calculator icon or press F4 on your keyboard. The Calculator window will open.

3. Enter a mathematical expression by using the numeric keys, and the [+], [-], [*], [/] keys.

4. Once the expression is entered, click OK to evaluate the expression and insert the result to the grid.

If an invalid expression is entered, an error message appears. Click OK to close the message window.

Problem:  The vendor has revised a prior utility bill and has included the adjustments in the current month's bill.  What’s the best way to handle this in EnergyCAP?

Solution:  There is no single method; the best way to revise prior bills depends upon several factors.  Three methods are discussed below.

1. Edit the original bill:

The quickest and simplest way to revise a prior bill is just to edit it.  When doing so, you can:

1. Add a one line bill message (yellow icon at upper left).
2. Add an explanation using the account memo (white icon at top of bill entry screen).
3. Create an explanatory issue tracker record (yellow icon at upper center).
   
   

Advantage of editing the original bill:  

The revision is made in the proper month, so bill history is accurate and therefore there will be no adverse impact on the accuracy of future forecasting, budgeting, audits or cost avoidance – all of these functions use bill history as a basis for ‘expected’ consumption and cost.

Disadvantages of editing the original bill:  

1. Reports that you have already prepared and distributed will be out-of-date. 
2. If you interface EnergyCAP with accounts payable, the edited bill no longer reflects what was actually sent to A/P at the time (hence the need for a memo as discussed above). More importantly, the bill that will flow from EnergyCAP to A/P today has to reflect the net amount due to the vendor today.  For example, if today’s bill is for a net of $10,000 ($15,000 of electricity consumed this month and a $5,000 credit for a prior month) you want to pass a $10,000 transaction to A/P, not a $15,000 transaction.  The vendor has “trued up” your account on today’s bill so you have to do likewise.

2. Make the correcting entry in the current bill.  

Leave the original bill (last month) untouched; true up the account by making an adjustment in the current bill. A good way to do this is to build a cost and usage adjustment line item in each template.  The adjustment may be recorded using this line item whenever you receive a cancel/rebill or other vendor adjustment.

Advantages of correcting the current bill:  

The correct transaction will flow to A/P and EnergyCAP will be ‘synchronized’ with vendor records.  Year-to-date energy use and cost will be correct.

Disadvantages of correcting the current bill: 

Individual monthly energy use and cost will not be correct.  (Example:  Last month shows values that are too high due to vendor overcharge; this month shows values that are too low due to correction.)  Future forecasts, budgets, audits and cost avoidance reports that use this data will be skewed although year-to-date values will normally be accurate.

3. Create an adjustment bill.  

Instead of showing the adjustment on today’s bill, enter today’s bill as the gross amount (the actual cost and consumption not including the adjustment) and then create a second bill that includes just the adjusted cost and consumption.  If a credit, these values will be negative.  This adjustment bill can use the dates and billing period of today’s bill (if you want to track the adjustment this month) or you can use prior dates and billing periods in order to reflect the adjustment in the proper month.  Click here for How to do Bill Adjustments.

Advantages of an adjustment bill:  

1. Good audit trail
2. Flexibility as to which billing period will reflect the adjustment
3. If you interface with A/P, both bills will flow to A/P and the net amount due to the vendor will be correct. However,be sure your A/P system can handle two transactions for the same vendor and create payment for the net amount.

Disadvantages of an adjustment bill:  

A bit trickier to do since two bills will be created. 

The bill adjustment feature in EnergyCAP is designed to help make adjustments to bills which have already been entered and saved.  The process involves the creation of two transactions as a result of the adjustment – a credit bill and a debit bill.  The adjustment can include one or all of the line items from the bill being adjusted.  Also, the GL, Accounting Period, and Control Code values can be modified for the adjustment bills.

How to Create Adjustments for a Bill

! Before making bill adjustments, please contact your EnergyCAP System Administrator to determine if creating Bill Adjustments will have any impact on financial system interfaces such as accounts payable.  You will need a good understanding of how these interfaces are set up in order to complete a successful bill adjustment. !

• If you use Batch Mode you will need to create an open batch to use the Bill Adjustment option (See Work Flow Manager for more information on Batch Mode).

• Go to Work Flow Manager and click on the Open/Close Batch button  to create a batch if you do not have one currently open.

! The name of the batch may be important since some financial system interfaces require a certain naming convention for bills intended to be paid (for accounting system purposes external to EnergyCAP) and those intended for internal adjustments (for reporting purposes in EnergyCAP) !

NOTE: You can only create bill adjustments for SAVED bills.

• Edit the bill, either from the Account Manager or the Work Flow Manager as follows:

• Highlight the bill to be adjusted from the Account Manager > Bills tab and access the edit window (Bill/Edit or right-click popup menu) OR

• highlight the bill to be adjusted in the Work Flow Manager module and access the edit window (Bill/Edit or right-click popup menu)

• From the Bill Entry / Edit window, click on the wrench icon  in the upper-right corner of the edit window. the Line Item Adjustments window will open.

• The Line Item Adjustments window is divided into four different regions:

1. Source Bill Information: Contains details about the source bill being edited.

2. Target Account / Meter: Used to select a target account and meter OTHER than the current Account / Meter to create the adjustments to.

3. Modify GL, Accounting Period, and Control Code on Adjustments: Contains the options for changing the adjusted bills’ values for these fields.

4. Source (original bill) and Target (adjusted) Bill Line Items: Contains the line items that will be used to create the adjusted bills.

• If necessary, update the values in the Target Account / Meter section to reflect the changes in the new bill.  If the adjustments need to be made to an account other than the current account, click on the Find Account button.  If the adjustments need to be made on the same account, but a different meter, click on the drop-down arrow and select the meter the adjustments are to be made on.  If the adjustments are being made to the same account /meter, leave these values alone.

• If necessary, update the G/L Record or Accounting Period Options.
  NOTE: G/L Record Options affect ONLY the adjustment bill for the target Account/Meter.
  

• Use Target Meter G/L: This option will use the G/L Record (if any) assigned to the target Account/Meter as the G/L for the adjustment bill created for the target Account/Meter.

• Same as Original Bill: This option will use the G/L Record (if any) from the source bill as the G/L for the adjustment bill created for the target Account/Meter.

• Leave G/L Record Blank: This option will simply not assign a G/L Record for the adjustment bill created for the target Account/Meter.

• Use this G/L Record: This option allows for ANY G/L Record in EnergyCAP to be assigned to the adjustment bill created for the target Account/Meter.

• Account Period Options: Affects the adjustment bills for the target and source Account / Meter

• Leave Account Period Blank: This option will leave the Account Period blank for the adjustment bill created for the target Account/Meter as well as the adjustment bill created for the source Account/Meter.

• Same as Original Bill: This option will use the Account Period (if any) from the source bill for the target Account/Meter as well as the adjustment bill created for the source Account/Meter.

• Use this Period and Year: This option allows for ANY combination of Account Period and Year to be used for the target Account/Meter as well as the adjustment bill created for the source Account/Meter.

• Control Code: Affects the adjustment bills for the target and source Account / Meter as well as updates the Control Code on the original source bill

• New Control Code / Comment: The value placed here will be used for the target Account/Meter as well as the adjustment bill created for the source Account/Meter.  Changes here will also update the value(s) in the original source bill.

• In the Source and Target Bill Line Items section, select the line items from the source bill that need to be adjusted.

  • To move all line items from Source Bill Line Items to Target Bill Line Items, click on the   button.

  • To clear all line items from Target Bill Line Items, click on the   button.

  • To move one line item at a time from Source Bill Line Items to Target Bill Line Items, click on the value you want to move and then click on the  button.

    
    NOTE: Any line item can be "copied" multiple times to the Adjustment Values pane.

• As values are moved into Target Bill Line Items, the values for each item will appear in red text.  By default, the adjustment will include the entire amount, therefore effectively zeroing out the original source bill.  Be sure to edit the values to the correct amounts.

• Verify all the values you have entered in the Line Items Adjustments dialog box, and click the Create Adjustment Bills button...

  

  ... to save the Adjustment Bills (the credt bill for the original bill and the debit bill reflecting the
  Adjustment(s).

• If you wish to cancel your changes instead, click on the Exit Without Saving button. 

• You will be returned to the Bill Edit window.  Close this window by clicking on the Close button.

Bill approval processes vary from organization to organization. Processes for batch bill entry, bill approval, data collection options, bill problem messages and bill export options are configured by the EnergyCAP Work Flow Wizard. The bill approval process is only applicable when the Use bill approval system option has been selected from the Work Flow Wizard.

To approve a bill:

1. Select Accounting > Work Flow Manager. The Work Flow Manager will be displayed.

2. Click to open the Bill folder and navigate to the Unapproved folder.

3. Select the bill to approve.
   NOTES: If bill batches are being used, a bill will not appear in the Unapproved folder UNTIL the batch is closed. Bills may also be approved from the Holding for Export folder.

4. Right-click and select Approve. The Confirm window appears.

   • Select Yes to approve the bill.

   • Select No to cancel the operation.

Energy CAP Enterprise allows you to change bill statuses by either selecting a bill or selecting a status associated with a bill.

1. Select Accounting > Work Flow Manager. The Work Flow Manager appears.

2. Click to open the Bill folder and navigate to the appropriate bill. The associated statuses for the bill are displayed in the bottom pane.

3. Select the appropriate bill or status.

4. From the Message menu, select either:

   • Status > Problem: A problem was identified during the audit process.

   • Status > Fixed: The problem is resolved and the bill is removed from the Problem folder.

   • Status > Ignore: The problem is not being addressed at this time. The status message is removed from the view. See Viewing Ignored Bill Messages to display any bills whose status has been changed to Ignore.

1. Delete a bill in the Work Flow Manager by clicking  to navigate the display of bill folders.
1. Go to step 3.
2. Delete a bill in the Account Manager by clicking to navigate the display of cost centers and accounts.
1. Go to step 3.
3. Select the bill to delete.
4. From the Bill menu, select the Delete option. The Confirm window appears.
• Select Yes to delete the bill and continue.
• Select Yes to all to delete all selected bills.
• Select No to cancel this deletion and continue.
• Select No to all to cancel the operation.

Bill Splits in EnergyCAP provide a way of splitting a source bill (usually an incoming vendor bill) into one or more sub-account/meter bills via a simple percentage formula. This feature is most commonly used in shared facilities and campus situations where you need to divide a metered area into two or more separate areas, usually for energy management and/or accounting reasons. Since the “submeters” represented by the bill split do not exist, they are virtual or “phantom” meters.

Bill Splits allow the EnergyCAP user to allocate a single vendor bill to multiple accounts for bill payment, such as is common in “charge-back” situations. This also enables the facility energy manager to track energy costs associated with specific accounts/areas more precisely.

Since energy use and cost is associated with the actual or “Master” meter (for vendor payment) as well as the associated phantom submeters (for the “charge-back” situation), care must be taken in the organization setup to avoid “double-counting” the resultant bills. This is usually done by placing the virtual meters in the Facility tree in the most logical “place” corresponding to their presumed location. The “master” meter is then placed in a separate Facility tree that is reserved for similar types of chargeback or other scenarios (see example below) .

A similar type of arrangement should be followed on the Accounting tree, with the master account associated with the vendor and the sub-accounts associated with the charge-back entities as appropriate for the organization accounting system (see example below).

When setting up a bill split scenario, consider how the setup will impact the resultant reports for facilities (esp. Analysis reports) , cost centers (BL03, BL04, BL08 and others) and/or vendors (BL11, BL31 and others) associated with the bill splits.

NOTE: Invoice reports based on split bills can be used for handling chargebacks for tenant and/or internal billing.

There are several steps involved in setting up bill splits. The first is to create the master/vendor account. Then the bill split accounts must be created. Then the master must be configured to properly allocate cost/use to the subordinate accounts.

Setting up the master/vendor account

Create the master (vendor) account like any other account, using the Account/Meter Setup Wizard. See the Bill Splits Introduction topic for tips on organizing your Facility and Accounts trees to accommodate the split.

NOTE: Make sure that the account properties Billing tab for the master/vendor account indicates that the account “receives” bills.

Setting up the bill split account/meters

Create the virtual submeter accounts and meters (only one meter per account) like any other account, using the Account/Meter Setup Wizard.

NOTE: If you would like to be able to view the submeter billing information that will be generated automatically from the master meter, you should assign the sub-accounts the same bill entry template as the “master” (vendor) account. For charge-back situations, make certain that the account properties Billing tab indicates that the account “creates” bills.

Configuring the bill splits

After the master and bill split accounts/meters have been created, configure the master account as the bill split master as follows:

1. Click Accounting > Accounts to open the Account Manager.
2. Locate the desired master account in the Accounting TreeView.
3. Right-click on the master account and select Setup Split Dependents from the popup menu.
   
   
   The Split Account Dialog (window) will open.
   
   
4. Click the Create New Version button and input the Change Version’s Effective Date using the drop-down calendar provided.
   NOTE: When setting or editing the version Effective Date, avoid overlapping dates with other versions.
5. Verify/select the Source (master account) meter using the drop-down box provided.
6. Click the Add a Split to Source button.
   
   The Select Target Account and Meter window will open.
   
   NOTE: Only accounts associated with a single meter of the same commodity as the master meter will display in the selection drop-down list.
7. Click the arrow on the drop-down box and use the mouse to select an existing submeter account/meter. Then click OK to close the window. The Source Meter and Target Account and Meter information you selected will be added automatically to the meter list box in the Split Account Dialog (window).
   
8. Click in the meter list box under the Factor (%) heading in the Split Account Dialog and use the keyboard to input the percentage of the source meter’s cost/usage that will be split to the “Target” account/submeter.
   
   NOTE: EnergyCAP Enterprise will accept a percentage split in the format xxx.xxxxxxxx. Up to eight decimal places may be used, although only two decimal places will displayed outside the Split Account Dialog. If uncertain of the bill split percentage when setting up meters, (or if you prefer to manually enter percentages during bill data entry) just enter 0.00% for all meters.
9. Repeat the previous three steps for each sub-account/meter associated with the master meter.
   NOTES: The unlabeled field at the bottom of the Split Account Dialog maintains a running total of the percentages associated with the bill split.
   When editing the Source Meter Factor (%), the Up/Down arrow keys on the keyboard are enabled. This is convenient for adjusting the Factor (%) for a long list of submeters without using a mouse.
   It is not necessary for the percentages for all sub-accounts to total 100.00 percent.
10. When done, click OK to close the Split Account Dialog.
    NOTE: When bills are entered for the master meter, data entry personnel may be prompted to split the bill whenever a new bill is saved. When percentages have been entered for the submeters, splitting bills is an automated process accomplished by the Split Bill Processor. If 0.00% has been entered for all submeters, the Split Account Dialog will open during processing, permitting data entry of appropriate percentages for the bill split.

Revising a Bill Split Percentage

If it is necessary to change the percentage for the split(s), the user is encouraged to set up another version of the bill split, and assign a new Effective Date. If more than one version of the splits is saved, the Effective Date determines which version will be used. Note that the source bill’s Start Date must be on or after the Effective Date of the new version. If you edit a split percentage, a new version is not required. However, for accounting/audit purposes, creation of a new version is recommended whenever the bill split percentage changes.

Additional Notes on Bill Splits

• If you add or delete a sub-account/meter, you must create a new Bill Split version.
• When filtering for REPORTS, make certain to use the Topmost Place Name/Code filter(s) to ensure that you are not double-counting bills for the master and submeters.
• Remember that a sub-account for a bill split can have only one meter associated with it.
• The COMMODITY for master and split accounts MUST be identical.
  • EXAMPLE: If the source bill is a water bill, then it can only be split to another account that has water.  You CANNOT take charges from one commodity and split them to a different commodity account/meter.
  • EnergyCAP provides a Visual Basic programming interface for setting up more advanced bill split scenarios.

QUESTION: How can I know if the bill split processor has successfully split the bill?

ANSWER: Open the View Bill window and see Bill Properties. Included in the properties list (when applicable) is the Was Split Date. 

QUESTION: Why would a bill NOT be split when the Bill Split Processor was run?

ANSWER: If the version effective date of the bill split is later than the billing period, the bill will not be split. The Version Effective Date is input by the user during setup of the Bill Split (see the Bill Split Dialog for the suspect bill). 

QUESTION: How do I know if a bill has been Unsplit?

ANSWER: Unsplit bills have the bill split date set back to the 19th century. Open the View Bill window and check the Was Split Date. A year date of 1899 is an indication that the bill has been unsplit:

In some cases (particularly when the number of submeters is very large, or the number of bills processed is very high), it is advantageous to postpone the bill split process by running the bill split processor for all bills (or a filtered subset you select) at a later time, rather than splitting the bills individually during the bill data entry process.

! If your organizational workflow is set up for bill approval, the Bill Split processor will not split bills that are Unapproved. !

To Split Bills outside of the normal bill entry process, click the No button when initially prompted to split bills from the bill entry window.

NOTE: Work flow options may also be adjusted to prevent the Bill Splits prompt from appearing during bill entry. To do this, click the Options button from the Work Flow Manager. The Work Flow Options window will open. Then click the Bill Entry tab and uncheck the Bill Splits checkbox . Click OK to save Work Flow changes.

There are two ways to split existing bills outside of the Bill Entry window—from the Account Manager or from the Tools menu (all Managers).

To split bills from the Account Manager (single account bill split procedure):

1. Click Accounting from the Navigation bar and then click the Accounts icon to open the Account Manager.
2. Locate the desired master account from the Accounting tree. Then right-click the account ID and select Split Bills from the popup menu.
   
   
   The Bill Split Processor window will open.
   
3. Choose the desired bill filter information and click Start. The processor will run and split only the filtered bills associated with the selected account.

To split bills for all accounts (global bill split procedure):

1. From any Manager, click Tools > Processor > Split Bills. The Bill Split Processor window will open.
2. Select the desired bill filter information and click Start.  The Bill Split Processor will split all unsplit bills fitting the filter criteria.
3. When done, click Close to close the Bill Split Processor window.

To void (undo) a bill split for a particular bill:

1. Locate and select the master account from the Account Manager and double-click the desired bill from the bill window. The View Bill window will open.
2. Click the Edit Bill icon from the View Bill window.
   
   The Edit Bill window will open. Note that the Void Bill Splits icon is now active.
   

3. Click the Void Bill Splits icon to delete the split bill transactions. A warning window will open. Click OK to confirm the deletion, or click No or Cancel to cancel the deletion.

NOTE: Once you delete the bill split, the Bill Split icon will become active.

Then you can “re-split” the bill as follows:

1. Click the Bill Split icon. The Split Account Dialog will open.
2. Verify/adjust the Bill Split percentages, using the same procedure as when you set up the bill split originally. Click OK to split or Cancel to cancel the split. The Confirm window will open.
3. Click Yes to split the bill. Or click No or Cancel to cancel the split.

QUESTION: How are G/L assignments handled for ACCOUNT-LEVEL charges when the Bill Processor splits bills?

ANSWER: There are two types of Account-level charges:

1. Charges associated with a bill entry template which is assigned to the ACCOUNT. This is a very unusual scenario. The account MAY or MAY NOT have a G/L association depending on how it was set up.
2. Special Charges that are associated with the bill during the bill entry process. The Special Charges MAY or MAY NOT have a G/L association depending on how each Special Charge was set up.

Both types of charges will be handled the same way during the bill split process. The table below explains the way G/L associations work with account-level charges when the Bill Split Processor is run:

QUESTION: I have an account with multiple meters but I only want to split bills for one/some of the meters. What is the best way to set this up? And how are the ACCOUNT-LEVEL charges distributed?

ANSWER: Beginning with EnergyCAP Release 6.0, all split bills must reconcile/balance with the master account bill totals. For bill splits totalling 100 percent, it will be necessary to set up bill split percentages for every meter associated with the account, regardless of whether the charges associated with that meter are to be split. This procedure is necessary to accurately split and reconcile account-level charges (see previous topic). The 'unneeded' Bill Splits (sub-accounts and sub-meters) should be located in a 'dummy' Cost Center and Place respectively, in order to separate the unneeded billing information from the 'regular' accounting and facilities hierarchies for your organization.

An important difference between this type of bill split setup and the setup procedure recommended in the normal bill split topic is that the unneeded bill split meters will be located in a 'dummy' organization and NOT in the 'regular' organization in the Facilities tree, since these bill splits are not an organizational requirement but a software/functionality requirement. Here's what the dummy meter organization could look like:

The associated accounts will also be located in a 'dummy' cost center, unrelated to any "Master Account" or organization cost centers. Here's what the dummy cost center could look like:

Distributing Account-Level Charges

During the Bill Split process for bill splits totalling 100 percent, account-level charges with the master account are allocated to the subaccounts according to the percentages input for each account during Bill Split Setup. At the conclusion of the split, EnergyCAP checks to make certain that the total dollar amounts for all sub-accounts add up to the total amount for the master account bill.

With the setup described above, the Account-level charges associated with the master account can be correctly and accurately 'filtered down' to their related sub-accounts (and associated sub-meters) in EnergyCAP when the Bill Split Processor is run on the master account.

QUESTION: I have an account with multiple meters but data is only available for one/some of the meters. How will the Bill Split Processor handle that?

ANSWER: If you run the Bill Split Processor for a multiple-meter account, the Processor will split the bill for any meters for which split data is available. You will receive an error message (Workflow Manager Problems folder) noting that there may be missing data associated with the bill split.

A bill batch is a collection of related bills. Batches allow data entry personnel to organize their work by keeping related bills together in order to better manage bill entry work flow. Using bill batches also helps the supervisor during the verification and approval process.

A batch can only be created if the Enter bills in batches option is enabled in the Work Flow Wizard. If the option is not enabled, it is impossible to enter batch bills (see Work Flow Wizard Batch Window). EnergyCAP permits only one open batch at a time per user.

If the Close Batch option is displayed on the Bill menu selections in place of the Create Batch option, this indicates that the current user already has a batch open and cannot open another batch until the open batch is closed.

1. Select Accounting > Work Flow Manager. The Work Flow Manager appears.

2. From the Bill menu, select Create Batch. The Batch Setup window appears.

3. In the Batch number field, enter the unique, user-defined identifier for the collection of related bills.

4. The Batch Setup Grid displays any fields available to automatically populate into the specified bill entry field. This may or may not include the following fields:

   • Statement Date: The calendar day signifying when the account record was generated.
   • Due Date: The calendar day signifying when payment is expected.
   • Next Reading: The calendar day signifying the subsequent scheduled meter/counter examination.
   • Control Code: Can be used for anything; is often used as a file or document scan identifier distinguishing the storage location of the physical bill or its scanned image.
   • Invoice Number: The unique vendor defined identifier for the bill.
   • Account Period: The user defined chronological division used to group bill data for financial accounting and/or budgeting purposes.
   • Account Year: The specific twelve-month period with regards to your organization's financial accounting system.

5. Enter any comments in the Note field.

   Any related comments specific to this bill batch up to 255 characters.

6. After making your selections, click OK.

NOTE: Only users who have created the batch, or administrators with Modify permissions for Other Settings - Workflow management, may close an open batch.

To close a bill batch:

1. Select Accounting > Work Flow Manager. The Work Flow Manager appears.
2. Click to open the Batches folder.
3. View and select the batch to close.
4. From the Bill menu, select Close Batch.
   
   
   (Administrators may right-click the batch from the batch folder and select Close Batch).
   
   
   
   For users closing their own batches, the Confirm window appears.
5. Select Yes to close the batch. (Click No to cancel the operation.) The Close Batch window appears, displaying various batch options and inputs depending on Work Flow, including the following:
   EnergyCAP Batch #: (for internal use only)
   Statement Date: (from Batch bills)
   Due Date: (from Batch bills)
   Vendor Remit Address: (from Batch bills)
   Page Count: Optional input (click the Batch Value text box and enter desired number).
   Bar Code Count: Optional input (click the Batch Value text box and enter desired number).
   Report Pages: Optional input (click the Batch Value text box and enter desired number).
   
6. Edit and/or enter any specific batch-related comments in the Note field, up to a total of 255 characters.
7. Click Close Batch.

1. Select Accounting > Work Flow Manager. The Work Flow Manager appears.
2. Click  to navigate the Batches folders.
3. Select the appropriate bill batch.
4. Right-click and select Properties. The Batch Properties window appears displaying the following:
   Total cost: A running total of the monetary value of the bills entered for this batch.
   User ID: The user ID of the individual who created the batch.
   Number of bills: A running total of the number of bills entered for this batch.
   Note: Any batch-related comments.
5. Exit the Bill Properties window by clicking Close.

The setup process below enables automatic incrementing of batch numbers for bill entry purposes. A similar approach can be used for Batch Values in the Batch Setup window (Bill/Open Batch), such as StatementDate and DueDate. The advantage to auto-incrementing is that Data Entry operators do not have to manually select and input this information during bill entry. A restriction of the auto-incrementing option using the procedure below is that the batch number cannot contain any alpha characters--it must be comprised solely of numbers.

Batch Number Auto-Incrementing Setup

1. From the Tools menu, select the Work Flow Wizard… option.
2. Click on the Next button to start the Work Flow Wizard.
3. On the second page of the Wizard, click in the box to select to Enter bills in batches. We also recommend that you select the Confirm Edit/Delete of bills in closed batches option.
4. Click on the Next button twice to get to the Work Flow Wizard – Header window.
5. Click in the box to Enable bill headers.
6. One of the Bill Header choices in the list is for Batch Number. Select Use Field to have the Batch Number appear on the Bill Entry screen. You may also want to select Require so that the Batch Number must be present on the Bill Entry screen in order for the bill to be saved.
7. In the Generate Default column, enter the following:
    =lastbatchnumber + 1 
   This equation will instruct EnergyCAP to generate the Batch Number by auto-incrementing the latest Batch Number in the system by one.
8. Select any of the remaining Bill Headers if it is necessary for them to appear on the Bill Entry screen.
9. Click the Next button until any additional the Work Flow Wizard changes have been made.
10. Click the Finish button to save the changes.
11. Have all users exit the EnergyCAP software and then log back into EnergyCAP in order for the Work Flow changes to take effect.
12. Operation the Software with Auto-Incrementing Enabled
13. The first time that someone enters a bill into EnergyCAP, they will need to create a new Batch. When  the first Batch is created, EnergyCAP will generate an error, indicating that the software is not able to automatically generate the Batch Number, because no Batch Numbers exist.
14. Click the OK button to proceed with creating the Batch.
15. Enter a Batch Number in the field provided. This can be a predetermined number, such as 1 or 1000, or it can be your favorite number. ! Remember: The Batch number must not contain alpha characters.! 
16. Click the OK button to finish creating the Batch. Once the initial Batch has been created with a Batch Number, subsequent Batches will have their Batch Number calculated, based on the Generate Default equation entered into the Work Flow Wizard.
17. Enter any bills that need to be entered into the Batch, then Close the Batch.
18. Create a new Batch. The Batch number will be populated with the value calculated using the Generate Default equation established in the Work Flow Wizard.
19. Enter any Batch Values, or enter a Note. Then, click on the OK button to finish creating the Batch.
20. Close the Batch when all of the bills have been entered.

NOTES: If you need to change the sequential Batch Numbering in the future, you may enter a new Batch Number when Creating a Batch, rather than using the default value shown. This new Batch Number must be greater than the default value shown to avoid any Batch Number conflicts within EnergyCAP, as each Batch Number must be unique. For example, if the default Batch Number value shown is 123, and it is necessary to change the sequential Batch Number, simply replace the 123 value with a larger number. Note that EnergyCAP will not create any Batches with Batch Numbers between 123 and the new number.

The Auto-Increment capability of the Workflow Wizard has not yet been fully documented. Users familiar with script language may derive additional ideas from the examples below:

EXAMPLE 1:

=(cstr(year(date())&"-"&month(date())&"-"&day(date())&"_"&CSTR(AutoInc+1)))

Output: YEAR-MONTH-DAY_AUTOINCREMENTNUMBER 

EXAMPLE 2: (Batch Setup Window)

StatementDate Batch Value: =date - 4

DueDate Batch Value: =date + 14

Virtual Bill Scripts (VBS) are powerful and flexible tools for bringing together cost information and interval usage data from varied sources. VBS are intended to provide additional data processing capability when the Calculated Account and Bill Splits features in EnergyCAP are insufficient. However, scripting expertise and a programming background are required to take advantage of possibilities offered by the VBS functionality.

Virtual Bill Creation Process

The scripting process is relatively straightforward:

First, determine the source for consumption data. This data must be:

• stored in the EnergyCAP database as channel data OR
• pulled from existing consumption data from a master meter in EnergyCAP OR
• defined as a script-based formula.

Then determine the source for cost data (formula, master meter, or other supported source.

Determine what information needs to appear on destination bill.

• Use (required)
• Cost (required)
• Start Date (required)
• End Date (required)
• optional?) Demand
• optional?) Accounting period
• optional?) Accounting period year
• optional?) Other desired data items

Determine how start and end dates of destination bill will be determined.

Once the scripts have been created, the user can generate virtual bills for all accounts by using the Virtual Bill Processor from the Tools menu or for a single account by selecting the applicable account in the Account Manager.

The Virtual Bill Processor executes VBS to create virtual bills. You must create a bill script version for an account before generating virtual bills. Energy CAP Enterprise maintains a history of an account's bill scripts in order to match its bill period to the applicable bill script version when creating virtual bills.

Examples: Source Account A with a monthly billing cycle has bill script versions effective 01/01/01-03/31/01, 04/01/01-09/30/01, and 10/01/01-12/31/01. When creating a virtual bill for the bill period June 2001, Energy CAP Enterprise looks at all bill versions for the account and selects the bill script version effective June 30, 2001, version 04/01/01-09/30/01.

You can create virtual bills by billing period (GetBillsByPeriod function) or for a specific creation date (GetBillsByDate function).

When using the billing period function, Energy CAP Enterprise recommends that you create virtual bills using the same billing cycle as the source account. If the source account billing cycle differs, Energy CAP Enterprise creates the virtual bill based on the availability of the source account's bills for the virtual bill period. Click here to view an example. When creating virtual bills, Energy CAP Enterprise reviews the source account bills at the end of its bill period (i.e., quarterly, the end of the third month) and if a source bill is available, EnergyCAP creates the virtual bill. If the source account does not have a bill for that bill period, an error is generated and logged in the Work Flow Manager Virtual Bill Processor Problem folder. Click here to view an example.

When using the creation date function, Energy CAP Enterprise looks for source account bills with creation dates between the start and end dates entered in the Bill Equation Processor.

To review a bill script sample, see the Virtual Bill Sample Script topic.

• Source account A has a monthly billing cycle

• Source account B has a bimonthly billing cycle

You wish to create a virtual bills for an account that has a monthly or bi-monthly billing cycles. EnergyCAP Enterprise reviews each account's bill period to create the monthly virtual bills. The following virtual bills are created:

For further explanation, click Virtual Bill Example 2.

• Source account A has a monthly billing cycle

• Source account B has a bimonthly billing cycle

You wish to create a virtual bills for an account that has a monthly billing cycle. EnergyCAP Enterprise reviews the source account bills at the end of its bill period:

• End of each month for source account A

• End of every second month for source account B

If a source bill is available, EnergyCAP Enterprise creates the virtual bill. If the source account does not have a bill for that bill period, an error is generated and logged in the Work Flow Manager Virtual Bill Processor Problem folder.

• January A virtual bill cannot be created since a bill for a source account (in this case, A and B) is not available. An error is generated and logged in the Virtual Bill Processor Problem folder.

• February A virtual bill is created containing two bills; one bill for source account A and one bill for source account B.

• March A virtual bill was created from source account A.

• April A virtual bill is created containing two bills; one bill for source account A and one bill for source account B.

• May A virtual bill is created using the bill from source account A, the only bill eligible.

• June A virtual bill is created using bills from both source accounts (A & B).

' Sample Virtual Account Script
' This Virtual Account Script must be assigned to the Account having its bills calculated.
' This script uses interval data from a Meter Use Channel to determine the bill consumption.
' This script uses an EnergyCAP Rate to calculate the bill cost.
'
'=======
' Declare the Source Meter and Rate
'=======
DIM ChannelMeterCode
ChannelMeterCode = "METER123"
'
DIM RateCode
RateCode = "RATE_01"
'
'=======
' Define any Customer Charge, Line Loss Factor, or Bill Factor
'=======
'
' Monthly Customer Charge in addition to any calculated charges.
' If none, or if Customer Charge included in Rate, set equal to zero.
DIM CustomerCharge
CustomerCharge = 0.00
'
' Line Loss within internal delivery network.
' A Line Loss Factor of 1.05 corresponds to an assumption of a 5 percent line loss.
' If Line Losses not being included, set equal to 1.00.
DIM LineLossFactor
LineLossFactor = 1.00
'
' Bill Factor is used to adjust the consumption determined by the interval data or meter readings.
' A Bill Factor of 0.20 will result in the consumption being 20 percent of the metered consumption.
' If consumption is not being adjusted, set equal to 1.00.
DIM BillFactor
BillFactor = 1.00
'
'=======
' Get the consumption from a Use Channel
'=======
'
' Adjust the FormTemplate Dates to be the Dest.StartDate and Dest.EndDate
' Not necessary to AdjustDates when using Interval Data (15-minute, hourly, or daily)
' Must add 1 to obtain Meter Reading Channel Data for the Destination Bill Period
'AdjustDates Dest.StartDate + 1, Dest.EndDate + 1
'
Dim ChannelUse
ChannelUse = TotalUse(ChannelMeterCode)
'
' Use this Msgbox to check for returned data while debugging code
'Msgbox ("Channel for Source Meter Code " & ChannelMeterCode & " Returned Use of " & ChannelUse)
'
' A Channel Use less than or equal to zero may indicate a problem with the interval data
If ChannelUse = 0.0 Then
Msgbox ("Channel Data for Source Meter Code " & ChannelMeterCode & " Returned Zero Use!")
End If
If ChannelUse < 0.0 Then
MsgBox ("Channel Data for Source Meter Code " & ChannelMeterCode & " returned NEGATIVE Use of " & ChannelUse)
End If
'
'=======
' Declare and initialize temporary variables to store the Usage and Cost
' The Cost variable will trap the calculated cost returned by the Rate (below)
'=======
'
DIM tmpUse, tmpCost
' Initialize tmpUse and incorporate the usage factors from above
tmpUse = (ChannelUse * (LineLossFactor * BillFactor))
'
' Initialize tmpCost to zero
tmpCost = 0.00
'
' Adjust the FormTemplate Dates back to the original dates, IF adjusted above.
'AdjustDates Dest.StartDate - 1, Dest.EndDate - 1
'
'=======
' Initialize the Rate and create a dummy bill to pass the Inputs needed for the Rate Calculation
' The Rate.CreateBillEx(StartDate, EndDate) function creates a bill with Line Items that correspond
' to the Inputs needed for the Rate.
'=======
'
DIM Rate
Set Rate = GetRate(RateCode)
'
Dim DummyBill
' THE STARTDATE AND ENDDATE ARE SET TO THE MANUAL PROCESS STARTDATE AND ENDDATE, UNLESS AN ADJUSTDATE IS USED ABOVE.
' Use this MsgBox to check data while debugging code
'MsgBox("StartDate = " & StartDate & " EndDate = " & EndDate)
Set DummyBill = Rate.CreateBillEx(StartDate, EndDate)
'
'=======
' Set the DummyBill Line Item values to be used as Inputs for the Rate
' There may be many more than just the Usage, depending on the complexity of the Rate
' To see how many Line Items exist, use the following:
' MsgBox("Number of DummyBill Line Items = " & DummyBill.Lines.Count)
' To see what the Line Item Captions are, edit and use the following as many times as needed (up to the count of line items):
' MsgBox("Line Item 1 Caption = " & DummyBill.Lines(1).Caption)
'=======
'
DummyBill.Lines("Usage").value = tmpUse
'
'=======
' Now have the Rate perform the cost calculations and return the Outputs
' There are several Outputs including Total Cost, Taxes, and Customer Charges
'=======
'
DIM Outputs
Set Outputs = Rate.Calculate(DummyBill)
' Set the cost variable equal to the TotalCost Output from the Rate Calculation
tmpCost = Outputs("TotalCost").value
'
' Use these MsgBoxes to check the data while debugging code
' What cost value was calculated by the Rate?
'MsgBox ("Cost returned from the Rate = " & tmpCost)
' Is the tmpCost value being returned correct?
' Double check that usage value is retained
'MsgBox ("Current Usage value = " & tmpUse)
'
'=======
' Calculate the Destination Bill Charges
'=======
'
DIM DestBillCost
' Include any additional monthly Customer Charges from above.
DestBillCost = tmpCost + CustomerCharge
'
' Use this MsgBox to check the data while debugging code
'MsgBox ("Destination Bill Cost = " & DestBillCost)
'
'=======
' Generate the Destination Bill
' Destination bill has Template "KWH_01" Assigned
' Line items are for "Usage" and "Total Cost"
'=======
'
Dest.Lines("Usage").Value = tmpUse
Dest.Lines("Total Cost").Cost = DestBillCost
Dest.Cost = DestBillCost
'
'=======
' end script

The Bill Script Editor is a tool for quickly generating bill scripts in EnergyCAP. Bill scripts are written in Visual Basic programming language. The virtual bill creation process uses bill scripts to supply values to a virtual bill. EnergyCAP Enterprise maintains a history of the account's bill script versions.

When editing a bill script, the modified script applies to the current version and all future versions of this bill.

Editing scripts should only be attempted by advanced users with Visual Basic programming skills.

1. Select Setup > Contacts. The Account Manager appears.

2. Browse the Accounting tree to locate the desired account.

3. Highlight the account and right-click; then select View Script from the popup menu options. The Bill Script Editor window will open.
   
   NOTE: The View Script option will not be available for Calculated Accounts or Bill Split accounts.

4. Once the Bill Script Editor is open, it is possible to:

   • Create a bill script version

   • Edit a bill script version

   • Delete a bill script version

To open a specific script file:

1. Click . The Open window appears.

2. Input, or navigate to, the desired File name and path for the script file.

3. Click Open. The opened script file text replaces the existing script text.

To save a script file:

1. Click . The Save As window appears.

2. In the Save in field, select the directory location for the file.

3. In the File name field, enter a name for the file.

4. After making your selections, click Save. The script is saved as a file to the location you specified.

To search the script text:

1. Click . The Find window appears.

2. In the Find what: text box, enter the text you want to find.

3. Click the associated checkboxes to Select/Deselect any search options:

   • Match whole word only: Finds only whole words instead of searching for your text inside longer words.

   • Match case: Finds only text that has the same pattern of upper and lower case as the text you specified in the Find What field.

4. Click Find Next. A search begins for the instance of the text you specified in the Find What field. Matching text is highlighted in the Script Editor.

5. To search for the next occurrence (if any), click Find Next.

6. Exit the Find window by clicking Cancel.

    

To print a script:

• Click . The script text is printed to your default printer.
  
  To exit the Bill Script Editor: 

• Exit the Bill Script Editor by clicking Cancel.

Bill scripts are written in Visual Basic programming language. The virtual bill creation process uses bill scripts to supply values to a virtual bill.

1. Select Accounting > Accounts. The Account Manager appears.

2. Click to navigate the display of cost centers and accounts.

3. Select the applicable account.

4. From the Account menu, select View Script. The Bill Script Editor window appears.

5. Enter the script effective date by clicking . The Choose a date window appears.

   The script effective date must be greater than or equal to the template effective date.

   1. Enter an effective date or use the drop-down arrow to select the date from a calendar.

   2. Click OK. The Choose a date window closes and the text area of the Bill Script Editor displays <enter script>.

6. Clear the text area of the Bill Script Editor.

7. Enter your script text.

8. When complete, click OK.

When editing a bill script, the modified script applies to the current version and all future versions of this bill.

Editing scripts should only be attempted by advanced users with Visual Basic programming skills.

1. Select Accounting > Accounts. The Account Manager appears.

2. Click to navigate the display of cost centers and accounts.

3. Select the applicable account.

4. From the Account menu, select View Script. The Bill Script Editor window appears.

5. Place the cursor in the area of the script text that you wish to edit.

6. Make the necessary modifications.

7. Click OK. The modified bill script is saved and the Bill Script Editor window closes.

1. Select Accounting > Accounts. The Account Manager appears.

2. Navigate the display of cost centers and accounts until the desired account is located.

3. Select the applicable account.

4. From the Account menu, select View Script. The Bill Script Editor window appears.

5. Select the appropriate bill script version in the Version drop-down list.

6. Click  .

7. Repeat steps 5 and 6 to delete other bill script versions for this account.

8. Click OK to save your changes. The Bill Script Editor window closes.

1. Select Accounting | Accounts. The Account Manager appears.

2. Click to navigate the display of cost centers and accounts.

3. Select the applicable account.

4. From the Account menu, select Run Script. The Bill Equation Processor window appears.

5. Select either:

   • Automatic

     Select Automatic to begin processing based on previously specified conditions.

     The start date is the "last run" date of the processor.

     The end date is the first day of the month after the current month.

   • Manual

     Select Manual to set a start and end date for the processor to run. The start date must be greater than or equal to the template effective date.

     1. Set the start date by entering the date or use the drop-down arrow to select the date from a calendar.

     2. Set the end date by entering the date or use the drop-down arrow to select the date from a calendar.

   • Reprocess from log

     Select Reprocess from log to process all records from the Virtual Bill Processors Problem log which are not flagged as "Hold". During reprocessing:

     • If there is already a bill in the period and year with matching start and end dates to one that would be created AND it is marked as approved, nothing happens.

     • If the log message is removed from the Problem folder, the bill has been successfully created.

     • If the log message is marked as Hold, the processor has failed to create a bill.

6. Click Start.

   • If any errors occur, a message window appears. Close the message window by clicking OK then go to the Virtual Bill Processor Problems folder in the Work Flow Manager to view the details.

7. When the process is complete, click Close. The Bill Equation Processor window closes.

The Virtual Bill Processor executes user-defined formulas to create calculated bill line item data. The Processor is designed to assist in generating monthly bills.

You can create virtual bills for ALL accounts by using the Virtual Bill Processor from the Tools menu or for a SINGLE account by selecting the applicable account in the Account Manager.

1. To create virtual bills for all accounts, from the Tools menu, select Processor | Virtual Bills. The Bill Equation Processor window appears.

   • Go to step 3.

2. To create virtual bills for a single account, select Accounting | Accounts. The Account Manager appears.

   1. Navigate the Accounting Tree to select the appropriate account.

   2. From the Account menu, select Run Script. The Bill Equation Processor window appears.

   3. Go to step 3.

3. Select either:

   • Automatic: Select Automatic to begin processing based on previously specified conditions.

     The start date is the "last run" date of the processor.

     The end date is the first day of the month after the current month.

   • Manual: Select Manual to set a different start and end date for the processor to run. Note that the start date must be greater than or equal to the bill template effective date or bills will not be generated.

     1. Set the start date by entering the date or use the drop-down arrow to select the date from a calendar.

     2. Set the end date by entering the date or use the drop-down arrow to select the date from a calendar.

   • Reprocess from log: Select Reprocess from log to process all records from the Virtual Bill Processors Problem log which are not flagged as "Hold". During reprocessing:

   • If there is already a bill in the period and year with matching start and end dates to one that would be created AND it is marked as approved, nothing happens.

   • If the log message is removed from the Problem folder, the bill has been successfully created.

   • If the log message is marked as Hold, the processor has failed to create a bill.

   • Use Batch Variables: If desired, select Use Batch Variables to have the Virtual Bills inherit some of the variables from the batch of the Source bill:

     • Variables that can be inherited are Batch Number, Invoice Number, Account Period, and Control Code

     • Variables that cannot be inherited are Next Reading, Statement Date, and Due Date.

     • All values are derived from the source bill, rather than the batch.

4. Click Start.

   • Cancel the operation by clicking Abort.

   • If any errors occur, a message window appears. Close the message window by clicking OK then go to the Virtual Bill Processor Problems folder in the Work Flow Manager to view the details.

5. When the process is complete, click Close. The Bill Equation Processor window will close.

NOTE: When the Virtual Bill Processor runs, the Processor automatically checks Account Properties for the associated account to determine if the account Receives bills (FromVendor flag=1) or Creates bills (FromVendor flag=0). This account property should be set from the Account Properties/Billing tab. The script-created bill will reflect the current Billing setting.

1. Select Accounting > Work Flow Manager. The Work Flow Manager appears.

2. Click to open the Virtual Bill Processor folder and navigate to the Problem folder.

3. Select the appropriate bill.

4. From the File menu, select Properties. The Virtual Bill Properties window will open and display the following information:

   • Account

   • Billing period

   • Billing period year

   • Status

   • Category

   • Estimation method

   • Message

5. Exit the Virtual Bill Properties window by clicking Close.

1. Select Accounting > Work Flow Manager. The Work Flow Manager appears.

2. Click to open the Virtual Bill Processor Problem folder.

3. Select the appropriate account.

4. Right-click and from the Status menu select any of the following:

   • Hold: There is still a problem with this bill.

   • Try with Real: The bill is flagged to reprocess using real data. Go to Creating Virtual Bills.

   • Try with Estimate: The bill is flagged to reprocess using estimated data. Go to Creating Virtual Bills.

To reprocess bills for ALL accounts, run the Virtual Bill Processor from the Tools menu. See details below.

To reprocess bills for a SINGLE account, navigate the Account Manager to select only the appropriate account. See details below. 

1. To reprocess virtual bills for all accounts, from the Tools menu, select Processor > Virtual Bills. The Bill Equation Processor window appears.

   • Go to step 3.

2. To reprocess virtual bills for a single account, select Accounting > Accounts. The Account Manager appears.

1. Navigate the Accounting tree to select the appropriate account.

2. From the Account menu, select Run Script. The Bill Equation Processor window appears.

3. Go to step 3.

3. Select Reprocess from log. All records from the Virtual Bill Processors Problem log which are not flagged as "Hold" are reprocessed. During reprocessing:

• If there is already a bill in the period and year with matching start and end dates to one that would be created AND it is marked as approved, nothing happens.

• If the log message is removed from the Problem folder, the bill has been successfully created.

• If the log message is marked as Hold, the processor has failed to create a bill.

4. Click Start.

5. When the process is complete, click Close. The Bill Equation Processor window closes.

TASK: Apply a rate to usage and demand (that would come from submeter readings/channel):

Getting usage from a reading channel is supported via the ReadingDelta function. If there is a Demand channel, the Max demand value for the date range determined by the bill start and end dates can be retrieved from there.

Getting those values into a Rate and calculating the cost is where things get tricky. As long as you know the names of the rate inputs for the Usage and Demand for the rate and know the names of the rate outputs that will have the values you want, it can be done. Since these values are available to the person writing the script, it's not a huge task and once it is done for the first one, most of it becomes a cut/paste for any others using the same rate and bill template. And since most of the rates will be using the standard names from the Wizard, it's mostly a matter of choosing the right values.

First you need a way to do the rate calculation:

Function DoRateCalculation(RateTariffID, StartDate, Usage, Demand)
Dim Tariff
Set Tariff = CreateObject("EnergySense.Tariff")
Tariff.Load(RateTariffID)
' Load the rate
Dim Rate
Set Rate = Tariff.GetRate(StartDate)
' create a bill that matches the rate
Dim RateBill
Set RateBill = Rate.CreateBill
' populate its use (and dem)
For Each Line in RateBill.Lines
If Line.Caption="Usage" Then
Line.Value = Usage
ElseIf Line.Caption="BilledDemand" Then
Line.Value = Demand
ElseIf Line.Caption="ActualDemand" Then
Line.Value = Demand
End If
Next
Set DoRateCalculation = Rate.Calculate(RateBill)
End Function

If that is done, the return value from this Function is a Collection of Rate Outputs so the whole thing looks something like this:

Dim outputs
Set outputs = DoRateCalculation(1024, Dest.StartDate, Usage, Demand)
If IsObject(outputs) Then
Dim RateEnergyCost
RateEnergyCost = 0.0
Dim RateDemandCost
RateDemandCost = 0.0
Dim RateTotalCost
RateTotalCost = 0.0
For Each Element in Outputs
If Element.Name = "TotalCost" Then
RateTotalCost = Element.Value
ElseIf Element.Name = "EnergyCost" Then
RateEnergyCost = Element.Value
ElseIf Element.Name = "DemandCost" Then
RateDemandCost = Element.Value
End If
Next

' put values into created bill (Dest)
If RateEnergyCost <> 0.0 Then
For Each Line In Dest.Lines
If Line.Caption="EnergyCost" Then
Line.Cost = RateEnergyCost
ElseIf Line.Caption = "TotalCost" Then
Line.Cost = RateTotalCost
ElseIf Line.Caption = "DemandCost" Then
Line.Cost = RateDemandCost
End If
Next
End If
End If

TASK:  Create a Virtual Bill using Interval Data to calculate the Use and Demand (from Channel Data).

The ReadingDelta Helper function works well for monthly readings, but does not work well for more frequent intervals of data (15 Minute or Hourly), since it subtracts the current reading from the next most previous reading.  For more frequent intervals, the MeterSummary Helper function should be used. 

The MeterSummary Helper function is a programming Object available to Virtual Account Scripts.  It is used to obtain summary information regarding interval data for a defined period of time, from a defined Meter Channel.  MeterSummary Helper returns the:

• Maximum – The maximum data point value.
• Minimum – The minimum data point value.
• Average – The average of all data point (values).
• Total – The sum of all data point (values).
• Count – The number of data points included in the summary.

Here is an example Virtual Account Script for a meter with a 15-minute Demand Channel, using the MeterSummary Helper function to generate a Virtual Bill containing Use, Demand, and Cost.

' Sample Virtual Account Script using MeterSummary Helper Function
' set a Debug variable
Dim Debug
' Debug = 1 for True, Debug = 0 for False
Debug = 1
'
' declare variables
Dim UnitCost, MeterCode, ChannelCode
' initialize variables
UnitCost = 0.15
MeterCode = "Meter123"
ChannelCode = "DEMAND:KW"
'
' =======
' MeterSummary helper function should return Maximum, Minimum, Average, Total, and Count
'
' declare variables
Dim MSMax, MSMin, MSAvg, MSTotal, MSCount
' initialize variables
MSMax = 0.0
MSMin = 0.0
MSAvg = 0.0
MSTotal = 0.0
MSCount = 0.0
'
If Debug = 1 Then
   MsgBox ("MSMax = " & MSMax & ", MSMin = " & MSMin & ", MSAvg = " & MSAvg)
   MsgBox ("MSTotal = " & MSTotal & ", MSCount = " & MSCount)
End If
'
' Now use MeterSummary helper function to retrieve the data
Dim MSHF
Set MSHF = MeterSummary(MeterCode, ChannelCode, 0)
'
MSMax = MSHF.Maximum
MSMin = MSHF.Minimum
MSAvg = MSHF.Average
MSTotal = MSHF.Total
MSCount = MSHF.Count
'
If Debug = 1 Then
   MsgBox ("MSMax = " & MSMax & ", MSMin = " & MSMin & ", MSAvg = " & MSAvg)
   MsgBox ("MSTotal = " & MSTotal & ", MSCount = " & MSCount)
End If
'
' generate the destination bill
'=======
' The kWh consumption is equal to the sum of the 15-minute KW values, divided by 4
Dest.Lines("Usage").value = (MSTotal/4.0)
Dest.Lines("Demand").value = MSMax
Dest.Lines("Total Cost").cost = ((MSTotal/4.0) * UnitCost)
Dest.Cost = (MSTotal * UnitCost)
'
' =======
' end of script

Electronic Data Interchange (EDI) is a standard method used by utility vendors to transmit electronic billing data.  In EnergyCAP, the EDI Processor executes user-defined formulas (scripts) associated with the account to create bills from imported data. 

EnergyCAP’s part in the EDI process is simply to receive the EDI 810 v 4010 (X.12 compliant) transaction and to import specified billing invoice data into the database.  EnergyCAP does not produce any EDI compliant response transaction such as an EDI 997 acknowledgement.  EnergyCAP will not generate an EDI 820 Remittance. 

Each utility company’s implementation of EDI is different and each has rules and guidelines which govern the arrangement between the customer and the utility.  Most arrangements require that a VAN (Value Added Network) partner is in place to handle the distribution of the EDI transactions.  In addition, some utilities mandate a paperless invoice process with payments made via ACH/EFT methods through an EDI 820 Remittance transaction.

Contact your vendor(s) to determine the specific rules for their EDI offerings.  In most cases, there will be a trial period for a test group of accounts prior to switching from paper invoices to EDI transactions only.  The trial period helps to ensure that the bill detail is being captured properly and that the payment methods are working as expected.

If you are interested in implementing EDI for your organization, first contact your utility companies and begin discussions with them regarding EDI requirements.  Then contact EnergyCAP Sales (sales@energycap.com) to determine pricing for implementing EDI transactions in EnergyCAP.  You may also find the following online resources helpful as you investigate the benefits of EDI for your organization.

Southern California – Edison EDI Overview:
http://en.wikipedia.org/wiki/Electronic_Data_Interchange

A Bit About Scripts

EnergyCAP Enterprise maintains a history of a vendor's bill scripts in order to match the bill period to the applicable EDI script version when importing bills.

Before importing EDI data, use the EDI Script Editor (accessible from the Contact Manager via the Contact menu item, or by right-clicking the desired account-View Script option) to create a script for the desired vendor.

Import Prerequisites

In order to import EDI data into EnergyCAP Enterprise, first verify the following:

• The data in the import file matches your EnergyCAP Enterprise dataset (account code, commodity, and meter code).
• The vendor’s EDI import ID matches the ID used in the import file. This ID is the concatenation of the vendor’s two-character ISA sender ID qualifier and the fifteen-character ISA sender ID code.

• The vendor is associated with an active, bill-enabled account for which the effective date is the same as or earlier than the effective date of the data to be imported.

NOTE: EnergyCAP Enterprise lists any errors that occur during the import process in the EDI Processor Problem folder of the Work Flow Manager.

The ediprofile.ini file, located in the Local Settings\Application Data\EnergyCAP Enterprise directory under the Documents and Settings directory for your Windows login profile, allows you to toggle global settings for the bill import processor:

• ReadDuplicates, for allowing the import of bills with duplicate bill dates for the same account (1=Yes, the default, or 0=No).
• UseTemplates, for specifying if an import bill mirrors the details that a manually-entered bill would inherit from the assigned bill entry template (1=Yes, the default, or 0=No); additional details which do not correspond to template details still get added to the end of the bill, while template details with no corresponding import details will not have values assigned.

EDI scripts are written in Visual Basic programming language. The EDI import process uses the script to supply values to bills. Energy CAP Enterprise maintains a history of the vendor's EDI script versions.

When editing a bill script, the modified script applies to the current version and all future versions of this bill.

Editing scripts should only be attempted by advanced users with Visual Basic programming skills.

1. Select Setup > Contacts. The Contact Manager appears.

2. From the Contact menu, select View > Vendors.

3. Select the applicable vendor.

4. From the Contact menu, select View Script. The EDI Script Editor window appears.

5. You can:

   • Create an EDI script version

   • Edit an EDI script

   • Delete an EDI script version

To save a script file:

1. Click . The Save As window appears.

2. In the Save in field, select the directory location for the file.

3. In the File name field, enter a name for the file.

4. After making your selections, click Save. The script is saved as a file to the location you specified.

To open a script file:

1. Click . The Open dialog box appears.

2. In the Look in field, select the directory location of the script file.

3. In the File List section, select the appropriate script file.

4. Click Open. The opened script file text replaces the existing script text.

To search the script text:

1. Click . The Find window appears.

2. Enter the text you want to find.

3. Select any search options:

   • Match whole word only Finds only whole words instead of searching for your text inside longer words.

   • Match case Finds only text that has the same pattern of upper and lower case as the text you specified in the Find What field.

4. Click Find Next. A search begins for the instance of the text you specified in the Find What field. Matching text is highlighted in the Script Editor.

5. To search for the next occurrence, click Find Next.

6. Exit the Find dialog by clicking Cancel.

To print a script:

• Click . The script text is printed to your default printer.

EDI scripts are written in Visual Basic programming language. The EDI import process uses the script to supply values to bills. Energy CAP Enterprise maintains a history of the vendor's EDI script versions.

1. Select Setup | Contacts. The Contact Manager appears.

2. From the Contact menu, select View | Vendors.

3. Select the applicable vendor.

4. From the Contact menu, select View Script. The EDI Script Editor dialog box appears.

5. Enter the script effective date by clicking . The Choose a date dialog box appears.

1. Enter an effective date or use the drop-down arrow to select the date from a calendar.

2. Click OK. The Choose a date dialog box closes and the text area of the Bill Script Editor displays <enter script>.

6. Clear the text area of the Bill Script Editor.

7. Enter your script text.

8. When complete, click OK.

When editing a script, the modified script applies to all imported bills transmitted between the version begin and end dates.

Script editing should only be attempted by advanced users with Visual Basic programming skills. EnergyCAP, Inc. does not support or troubleshoot user-edited custom scripts without previous contractual agreement.

1. Select Setup > Contacts. The Contact Manager appears.

2. From the Contact menu, select View > Vendors.

3. Select the applicable vendor.

4. From the Contact menu, select View Script. The EDI Script Editor window appears.

5. Place the cursor in the area of the script text to edit.

6. Make the necessary modifications.

7. Click OK. The modified bill script is saved and the EDI Script Editor window closes.

1. Click Setup > Contacts. The Contact Manager appears.
2. From the Contact menu, click View > Vendors.
3. Select the applicable vendor.
4. From the Contact menu, select View Script. The EDI Script Editor window appears.
5. Select the appropriate EDI script version in the Version drop-down list.
6. Click  .
7. Repeat steps 5 and 6 to delete other EDI script versions for this vendor.
8. Click OK to save your changes. The EDI Script Editor window closes.

When importing EDI data, the data used is available to all vendors having the same import ID (Vendor Properties / Other tab) as the EDI ID used in the EDI file. The EDI ID is a concatenation of elements 5 and 6 from the ISA envelope segment.

1. Select Accounting > Accounts. The Account Manager is displayed.
2. From the File menu, select Import. The Import File window appears.
3. In the Look in field, select the directory location of the EDI file.
4. In the File List section, select the appropriate EDI file.
5. Click Open. A progress indicator appears (If necessary, it is possible to cancel the import operation by clicking Abort). When the process is complete, the Log window appears, displaying the status of the import process, the file import path, and any errors that occurred during the process. If errors did occur, they are listed, and will need to be corrected in order to successfully import the file into the database. If the import was successful, this will be indicated in the log.
6. Save the log file by clicking . The Save As window appears.
1. In the Save in field, select the directory location for the file.
2. Print the log file by clicking . The log file display is printed to the default printer.
3. Exit the Log window by clicking Close.
7. View EDI Processor problem messages from the Work Flow Manager. If necessary, correct problems and reimport.

1. Select Accounting > Work Flow Manager. The Work Flow Manager appears.

2. Click to open the Bill Import Processor folder and navigate to the Problem folder.

3. Select the appropriate bill. Problems resulting from running the EDI import processor have a message beginning with "ED".
   
   
   

4. From the File menu, select Properties. The Kickout Properties window appears and displays the following information:

• Account

• Kickout code

• Information

• Kickout text

5. Exit the Kickout Properties window by clicking Close.

EDI Import Error Messages

Could not open EDI file filename.
Error reading from EDI file filename.
Could not open translated EDI file.
Error reading from translated EDI file.
File is unreadable. ISA delimiter not found.
File is unreadable. End-of-line delimiter not found.
File is unreadable. GS segment not found. Unable to process consolidated invoice. Invoiced discarded.
Unable to process credit/debit memo. Invoice discarded.
Invoice is of an unknown type. Invoice discarded.
Account 'EDI account number' is not found. Invoice discarded.
Account 'EDI account number' is not a billing account. Invoice discarded.
Account 'EDI account number' is not in service during this service period (start date to end date).  Invoice discarded.
Vendor 'EDI vendor code' account 'EDI account number' is now assigned to vendor ‘account vendor code’. Invoice discarded.
Service period start date (start date) must be less than end date (end date). Invoice discarded.
'EDI commodity code' meter count (EDI meter count) does not match account meter count (account meter count). Invoice discarded.
'EDI commodity code' meter 'EDI meter code' is not found. Invoice discarded.
Meter 'EDI meter code' commodity 'EDI commodity code' does not match account meter commodity 'account meter commodity'. Invoice discarded.
'EDI commodity code' meter 'EDI meter code' is not assigned to account 'EDI account number'. Invoice discarded.
'EDI commodity code' meter 'EDI meter code' is not in service during the service period from start date to end date. Invoice discarded.
'EDI commodity code' meter 'EDI meter code' is not in service on account 'EDI account number' during the service period from start date to end date. Invoice discarded.
Mandatory invoice detail 'observation type' is not set. Invoice discarded.
Invoice total cost is not set. Invoice discarded.
Test import. Invoice discarded.
This is an adjustment invoice. Please verify processed invoice.
This is a corrected invoice. Please verify processed invoice.
This is a duplicate invoice. Please verify processed invoice.
This is a final invoice. Please deactivate account.
Account number 'Old EDI account number' has changed to 'EDI account number'. Please update database.
'EDI commodity code' meter 'EDI meter code' could not be matched using meter code; meter matched by commodity only.
Multiple 'EDI commodity code' meter matches (account meter count) occurred based on EDI import code; first meter selected.
Multiple 'EDI commodity code' meter matches (account meter count) occurred based on serial number; first meter selected.
Multiple 'EDI commodity code' meter matches (account meter count) occurred based on device code; first meter selected.
Multiple 'EDI commodity code' meter matches (account meter count) occurred based on commodity; first meter selected.
Commodity 'EDI commodity code' is not found. Invoice detail discarded.
Observation type 'observation type' is not found. Invoice detail discarded.
Observation method 'observation method' is not found. Invoice detail discarded.
Unit code 'unit code' is not found. Invoice detail discarded.

EDI bills are reimported using the Bill Reimport Processor.

The Bill Reimport Processor attempts to reimport ALL bills that were successfully read but were NOT successfully imported. These bills can be viewed in the Bill Import Processor problem folder of the Work Flow Manager.

Kickout messages beginning with "ED" are produced by the EDI import processor. Only kickout records with entries in the kickout text field are eligible for reimport. Currently, it is impossible to edit EDI transactions in EnergyCAP.

1. From the Tools menu, select Processor > Bill Reimport. A progress indicator appears.

• If necessary, it is possible to cancel the operation by clicking Abort.

2. When the process is complete, the Log window appears the status of the import process. If errors did occur, they are listed in the log, and it will be necessary to correct these errors in order to successfully reimport the file into the database. If the reimport was successful, this will be indicated in the log.

   1. To save the log file, click . The Save As window appears.

      1. In the Save in field, select the directory location for the file.

      2. In the File name field, enter a name for the file.

      3. Click Save.

   2. Print the log file by clicking . The log file display is printed to the default printer.

   3. Exit the Log window by clicking Close.

To import EDI files into EnergyCAP:

1. Select File > Import from the Account Manager. The Import File window will appear.
2. Click to browse/select the import file. EDI 810 files should have a file type of .EDI or .DAT. Then click Open.
3. All imported bills are listed in the Waiting to Verify folder. They can be audited and edited. Use the control totals at bottom to validate record count and total amount. If necessary, all can be deleted at once (select bills, then right-click, and select Delete Bill from Database from the right-click menu) if it is desirable to re-run the import.
4. To ‘accept’ the bills, highlight and click Verify.

NOTES: Any ‘kickouts’ are shown in the Bill Import Processor Problem folder. Kickouts can be edited and re-imported using the Reimport Processor.

EnergyCAP Enterprise uses Visual Basic scripts to interpret incoming EDI invoices from utility company vendors. Following is a sample of an EDI 810 v4010 import script for PG&E.

______________________________

' -- Start Main EDI Import Script --

'

' EDI Version Check

'

Select Case EdiTransaction.TransactionSet

Case "810"

Select Case EdiTransaction.Version

Case "002040"

Import810Version2040

Case "003030"

Import810Version3030

Case "003040"

Import810Version3040

Case "004010"

Import810Version4010

Case Else

AddMessage("Cannot read this version of 810 transaction.\r\nInvoice discarded.")

ExitScript

End Select

Case Else

'

' Do nothing; just ignore data

'

End Select

' -- End Main EDI Import Script --

' -- Start EDI Version Import Scripts --

Sub Import810Version2040

AddMessage("Script to read version 2040 810 transactions not yet implemented.\r\nInvoice discarded.")

ExitScript

End Sub

Sub Import810Version3030

AddMessage("Script to read version 3030 810 transactions not yet implemented.\r\nInvoice discarded.")

ExitScript

End Sub

Sub Import810Version3040

AddMessage("Script to read version 3040 810 transactions not yet implemented.\r\nInvoice discarded.")

ExitScript

End Sub

Sub Import810Version4010

Dim Seg1

Dim Seg2

Dim Elem1

Dim Elem2

Dim AccountCode

Dim OldAccountCode

Dim InvoiceNumber

Dim InvoiceType

Dim DocumentCode

Dim StartDate

Dim EndDate

Dim InvoiceDate

Dim DueDate

Dim Estimated

Dim MeterCode

Dim CommodityCode

Dim ObservationType

Dim ValueUnitCode

Dim Value

Dim CostUnitCode

Dim Cost

Dim RoundoffError

Dim Caption

Dim Bill

Dim FoundUseMEA

Dim FoundDemandMEA

Dim MeterMultiplier

'

' Importer settings

'

RoundoffMethod = rmAmount ' do a fixed-amount roundoff compensation

RoundoffAmount = 2 ' allow two cents per detail

'

' Find transaction account number

'

Set Seg1 = EdiTransaction.FindSegment("BIG", "", sdFirst)

If (IsObject(Seg1)) Then

Set Elem1 = Seg1.FindElement("2")

' Begin method one

' AccountCode = Elem1.Value

' End method one

' Begin method two

If (Len(Elem1.Value) > 6) Then

AccountCode = Left(Elem1.Value, Len(Elem1.Value) - 6)

Else

AccountCode = ""

End If

' End method two

Else

AccountCode = ""

End If

' Set Seg1 = EdiTransaction.FindSegment("REF", "REF:01='11'", sdFirst)

' If (IsObject(Seg1)) Then

' AccountCode = Seg1.FindElement("2").Value

' End If

'

' Find transaction invoice number

'

' Set Seg1 = EdiTransaction.FindSegment("BIG", "", sdFirst)

If (IsObject(Seg1)) Then

InvoiceNumber = Seg1.FindElement("2").Value

Else

InvoiceNumber = ""

End If

'

' Find invoice type

'

' Element 7 of the BIG segment contains a code indicating

' what kind of invoice is being processed. The types of invoices

' to expect will vary by vendor. These codes are

' typically one of the following:

'

' Invoice type code Invoice type

' --------------------------------------- -------------------------

' CI (consolidated invoice) itConsolidated

' CO (corrected invoice) itCorrected

' DU (duplicate invoice) itDuplicate

' FB (final invoice) itFinal

' FE (memorandum, final invoice) itFinal

' ME (memorandum) itMemo

' PR (original invoice) itOriginal

' <other> itUnknown

'

' Element 8 of the BIG segment may contain a code additionally indicating

' what kind of invoice is being processed. These codes are

' typically one of the following:

'

' Invoice action code Invoice type

' --------------------------------------- -------------------------

' 00 (original invoice) itOriginal

' 01 (cancel invoice) itCancelled

' 05 (replace invoice) itCorrected

' 07 (duplicate invoice) itDuplicate

' CO (corrected invoice) itCorrected

' <other> <ignore code>, or itUnknown

'

' Invoice type itAdjustment is also available for invoices which

' contain only adjustments to a prior invoice, rather than

' an entirely new invoice to replace a prior one. There are currently

' no known EDI codes to indicate this type of invoice.

'

If (IsObject(Seg1)) Then

Select Case Seg1.FindElement("7").Value

Case "PR"

InvoiceType = itOriginal

Case Else

' Don't know how to implement other types of invoices at this time

InvoiceType = itUnknown

End Select

Else

InvoiceType = itUnknown

End If

'

' Find control code

'

DocumentCode = Right("000000000" & EdiTransaction.IchgCtrlNumber, 9)

DocumentCode = DocumentCode & Right("000000000" & EdiTransaction.FcnlGrpCtrlNumber, 9)

DocumentCode = DocumentCode & Right("000000000" & EdiTransaction.TrxCtrlNumber, 9)

'

' Find invoice date

'

If (IsObject(Seg1)) Then

Set Elem1 = Seg1.FindElement("1")

InvoiceDate = CDate(Mid(Elem1.Value, 5, 2) & "/" & Mid(Elem1.Value, 7, 2) & "/" & Mid(Elem1.Value, 1, 4))

Else

InvoiceDate = 0

End If

'

' Find payment due date

'

Set Seg1 = EdiTransaction.FindSegment("ITD", "", sdFirst)

If (IsObject(Seg1)) Then

Set Elem1 = Seg1.FindElement("6")

DueDate = CDate(Mid(Elem1.Value, 5, 2) & "/" & Mid(Elem1.Value, 7, 2) & "/" & Mid(Elem1.Value, 1, 4))

Else

DueDate = 0

End If

'

' Find transaction old account number (if it exists)

'

Set Seg1 = EdiTransaction.FindSegment("REF", "REF:01='45'", sdFirst)

If (IsObject(Seg1)) Then

OldAccountCode = Seg1.FindElement("2").Value

Else

OldAccountCode = ""

End If

'

' Find service period start date

'

Set Seg1 = EdiTransaction.FindSegment("DTM", "DTM:01='150'", sdFirst)

If (Not IsObject(Seg1)) Then

Set Seg1 = EdiTransaction.FindSegment("DTM", "DTM:01='186'", sdFirst)

End If

If (IsObject(Seg1)) Then

Set Elem1 = Seg1.FindElement("2")

If (Elem1.Value = "") Then

Set Elem1 = Seg1.FindElement("6")

End If

StartDate = CDate(Mid(Elem1.Value, 5, 2) & "/" & Mid(Elem1.Value, 7, 2) & "/" & Mid(Elem1.Value, 1, 4))

Else

StartDate = 0

End If

'

' Find service period end date

'

Set Seg1 = EdiTransaction.FindSegment("DTM", "DTM:01='151'", sdFirst)

If (Not IsObject(Seg1)) Then

Set Seg1 = EdiTransaction.FindSegment("DTM", "DTM:01='187'", sdFirst)

End If

If (IsObject(Seg1)) Then

Set Elem1 = Seg1.FindElement("2")

If (Elem1.Value = "") Then

Set Elem1 = Seg1.FindElement("6")

End If

EndDate = CDate(Mid(Elem1.Value, 5, 2) & "/" & Mid(Elem1.Value, 7, 2) & "/" & Mid(Elem1.Value, 1, 4))

Else

EndDate = 0

End If

'

' Set "estimated bill" flag

'

Set Seg1 = EdiTransaction.FindSegment("MEA", "MEA:01='AE' OR MEA:01='EE'", sdFirst)

If (IsObject(Seg1)) Then

Estimated = True

Else

Estimated = False

End If

'

' Create an EnergySense bill object to populate;

' set the bill properties for the data that we've read

'

Set Bill = CreateBill(AccountCode, StartDate, EndDate)

Bill.OldAccountNumber = OldAccountCode

Bill.InvoiceNumber = InvoiceNumber

Bill.InvoiceType = InvoiceType

Bill.DocumentCode = DocumentCode

If (InvoiceDate > 0) Then

Bill.StatementDate = InvoiceDate

End If

If (DueDate > 0) Then

Bill.DueDate = DueDate

End If

Bill.Estimated = Estimated

'

' Read account balance data and store it as information-only line items

'

' The ToNumber function used below is defined in the utility subroutine

' section of this script.

'

MeterCode = ""

CommodityCode = "MONEY"

Value = 0 ' these are all cost details

ValueUnitCode = "" ' these are all cost details

CostUnitCode = "USDOLLARS"

RoundoffError = False

Set Seg1 = EdiTransaction.FindSegment("BAL", "", sdFirst)

While (IsObject(Seg1))

Cost = CDbl(ToNumber(Seg1.FindElement("3").Value))

Select Case Seg1.FindElement("2").Value

Case "PB"

ObservationType = "PRIORBALANCE"

Caption = "Balance From Previous Period"

Case "YB" ' not sure what this is meant to convey

ObservationType = "PRIORBALANCE"

Caption = "Unknown Prior Balance"

Case Else ' don't know what to do

ObservationType = "PRIORBALANCE"

Caption = "Unknown Prior Balance"

Caption = UCase(Caption) ' make it stand out on screen

End Select

'

' Add cost detail to bill and find next BAL segment

'

If ((Cost <> 0) And (ObservationType <> "")) Then

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

Set Seg1 = EdiTransaction.FindSegment("BAL", "", sdNext)

Wend

'

' Find account charge section and assign bill with details

'

Set Seg1 = EdiTransaction.FindSegment("IT1(TDS)", "IT1:09='ACCOUNT'", sdFirst)

If (IsObject(Seg1)) Then

'

' Read account cost details inside the current IT1 segment loop.

'

MeterCode = ""

CommodityCode = "MONEY"

Value = 0 ' these are all cost details

ValueUnitCode = "" ' these are all cost details

RoundoffError = False

Set Seg2 = Seg1.FindSegment("SAC", "", sdNext)

While (IsObject(Seg2))

Cost = GetSACCostNoCharge(Seg2)

GetSACObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Add cost detail to bill and find next SAC segment

'

If ((Cost <> 0) And (ObservationType <> "")) Then

If (Seg2.FindElement("1").Value = "N") Then

ObservationType = "NOCHARGE"

End If

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, "", 0, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

Set Seg2 = Seg1.FindSegment("SAC", "", sdNext)

Wend

'

' Read TXI segments now

'

Value = 0 ' these are all cost details

ValueUnitCode = "" ' these are all cost details

Set Seg1 = EdiTransaction.FindSegment("IT1(TDS)", "", sdCurrent) ' get another IT1 segment object positioned at beginning of loop to use for TXI search

Set Seg2 = Seg1.FindSegment("TXI", "", sdNext)

While (IsObject(Seg2))

Cost = CDbl(ToNumber(Seg2.FindElement("2").Value))

GetTXIObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Add cost detail to bill and find next TXI segment

'

If ((Cost <> 0) And (ObservationType <> "")) Then

If (Seg2.FindElement("7").Value = "O") Then

ObservationType = "NOCHARGE"

End If

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

Set Seg2 = Seg1.FindSegment("TXI", "", sdNext)

Wend

End If

'

' Find meter charge section and assign bill with details

'

CommodityCode = ""

Set Seg1 = EdiTransaction.FindSegment("IT1(TDS)", "IT1:09='METER' OR IT1:09='UNMET'", sdFirst)

While (IsObject(Seg1))

'

' Read meter commodity

'

Set Elem1 = Seg1.FindElement("7")

If (CommodityCode <> Elem1.Value) Then ' only reading one meter code per commodity

MeterCode = ""

End If

CommodityCode = Elem1.Value

'

' Find meter code

'

' There is no requirement that meter serial numbers

' be provided in the invoice. If there is one here, read it.

'

Set Seg2 = Seg1.FindSegment("IT1(TDS)", "", sdCurrent, False)

Set Seg2 = Seg2.FindSegment("REF", "REF:01='MG'", sdNext)

If (IsObject(Seg2)) Then

If (MeterCode = "") Then

MeterCode = Seg2.FindElement("2").Value

End If

End If

'

' Spin through all measurement segments within the current IT1 segment loop

' to see if there are any "use" or "demand" segments, and find meter multiplier

'

FoundUseMEA = 0

FoundDemandMEA = 0

MeterMultiplier = 1

Set Seg2 = Seg1.FindSegment("MEA", "", sdNext)

While (IsObject(Seg2))

GetMEAObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Set "found" flags and/or meter multiplier

'

If (Seg2.FindElement("2").Value = "MU") Then

MeterMultiplier = CDbl(ToNumber(Seg2.FindElement("3").Value))

ElseIf (ObservationType <> "") Then

Select Case ValueUnitCode

Case "KWH"

FoundUseMEA = FoundUseMEA + 1

Case "KW"

FoundDemandMEA = FoundDemandMEA + 1

Case Else ' don't know what type of measurement

End Select

End If

Set Seg2 = Seg1.FindSegment("MEA", "", sdNext)

Wend

'

' Read all measurement segments within the current IT1 segment loop;

'

Set Seg1 = EdiTransaction.FindSegment("IT1(TDS)", "", sdCurrent) ' get another IT1 segment object positioned at beginning of loop to use for SAC search

Set Seg2 = Seg1.FindSegment("MEA", "", sdNext)

While (IsObject(Seg2))

Value = CDbl(ToNumber(Seg2.FindElement("6").Value))

GetMEAObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Add value detail to bill and find next MEA segment

'

If ((FoundDemandMEA > 1) And (ValueUnitCode = "KW") And (Seg2.FindElement("7").Value = "51")) Then

ObservationType = "" ' don't add this detail to bill - it is a duplicate

End If

If ((Value <> 0) And (ObservationType <> "")) Then

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, 0, False, ctDebit, Caption

Else

'

' See if we need to add demand detail

'

Value = (Value - CDbl(ToNumber(Seg2.FindElement("5").Value))) * MeterMultiplier

If ((FoundDemandMEA = 0) And (Value <> 0) And (ObservationType <> "") And (ValueUnitCode = "KW")) Then

ObservationType = "DEMAND"

End If

If ((Value <> 0) And (ObservationType <> "")) Then

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, 0, False, ctDebit, Caption

End If

End If

Set Seg2 = Seg1.FindSegment("MEA", "", sdNext)

Wend

'

' Read all invoice consumption/cost details inside

' the current IT1 segment loop

'

RoundoffError = False

Set Seg1 = EdiTransaction.FindSegment("IT1(TDS)", "", sdCurrent) ' get another IT1 segment object positioned at beginning of loop to use for SAC search

Set Seg2 = Seg1.FindSegment("SAC", "", sdNext)

While (IsObject(Seg2))

Value = CDbl(ToNumber(Seg2.FindElement("10").Value))

Cost = GetSACCostNoCharge(Seg2)

Select Case Seg2.FindElement("9").Value

Case "KH"

ValueUnitCode = "KWH"

Case "K1"

ValueUnitCode = "KW"

Case "EA"

Value = 0 ' don't use value

ValueUnitCode = ""

Case Else ' don't know what unit to assign if not one of the codes above

ValueUnitCode = ""

End Select

GetSACObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Add value and cost details to bill and find next SAC segment

' (add value and cost as seperate details so that "no charge"

' details can be captured correctly regardless of template setup.

'

If ((FoundUseMEA = 0) And (ObservationType = "GENERATIONUSE")) Then

ObservationType = "USE" ' no generation use, just normal use

Caption = "Use"

End If

If (ObservationType <> "") Then

If (Seg2.FindElement("1").Value = "N") Then

If (Value <> 0) Then

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, 0, False, ctDebit, Caption

End If

If (Cost <> 0) Then

ObservationType = "NOCHARGE"

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, "", 0, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

Else

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

End If

Set Seg2 = Seg1.FindSegment("SAC", "", sdNext)

Wend

'

' Read TXI segments now

'

Value = 0 ' these are all cost details

ValueUnitCode = "" ' these are all cost details

Set Seg1 = EdiTransaction.FindSegment("IT1(TDS)", "", sdCurrent) ' get another IT1 segment object positioned at beginning of loop to use for TXI search

Set Seg2 = Seg1.FindSegment("TXI", "", sdNext)

While (IsObject(Seg2))

Cost = CDbl(ToNumber(Seg2.FindElement("2").Value))

GetTXIObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Add cost detail to bill and find next TXI segment

'

If (ObservationType <> "") Then

If (Seg2.FindElement("7").Value = "O") Then

ObservationType = "NOCHARGE"

End If

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

Set Seg2 = Seg1.FindSegment("TXI", "", sdNext)

Wend

Set Seg1 = EdiTransaction.FindSegment("IT1(TDS)", "", sdNext)

Wend

'

' Read the total invoice cost

'

Set Seg1 = EdiTransaction.FindSegment("TDS", "", sdNext)

If (IsObject(Seg1)) Then

Bill.Cost = CDbl(ToNumber(Seg1.FindElement("1").Value) / 100)

'

' Read all invoice consumption/cost details

' that follow the TDS segment

'

MeterCode = ""

CommodityCode = "MONEY"

Value = 0 ' these are all cost details

ValueUnitCode = "" ' these are all cost details

RoundoffError = False

Set Seg2 = Seg1.FindSegment("SAC", "", sdNext)

While (IsObject(Seg2))

Cost = GetSACCostNoCharge(Seg2)

GetSACObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Add cost detail to bill and find next SAC segment

'

If ((Cost <> 0) And (ObservationType <> "")) Then

If (Seg2.FindElement("1").Value = "N") Then

ObservationType = "NOCHARGE"

End If

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

Set Seg2 = Seg1.FindSegment("SAC", "", sdNext)

Wend

'

' Read TXI segments now

'

Set Seg1 = EdiTransaction.FindSegment("TDS", "", sdCurrent) ' get another TDS segment object positioned at beginning of summary section to use for TXI search

Set Seg2 = Seg1.FindSegment("TXI", "", sdNext)

While (IsObject(Seg2))

Cost = CDbl(ToNumber(Seg2.FindElement("2").Value))

GetTXIObsTypeAndCaption Seg2, ObservationType, ValueUnitCode, Value, Caption

'

' Add cost detail to bill and find next TXI segment

'

If ((Cost <> 0) And (ObservationType <> "")) Then

If (Seg2.FindElement("7").Value = "O") Then

ObservationType = "NOCHARGE"

End If

Bill.AddLineItem MeterCode, CommodityCode, ObservationType, ValueUnitCode, Value, CostUnitCode, Cost, RoundoffError, ctDebit, Caption

End If

Set Seg2 = Seg1.FindSegment("TXI", "", sdNext)

Wend

End If

'

' Save the bill into ES

'

Bill.Save

End Sub

' -- End EDI Version Import Scripts --

' -- Start Utility Subroutines --

'

' This function performs the operations required to extract

' cost data correctly from ITA segments. Cost value

' is returned normally if "no charge" indicator is set.

'

' Element 1 contains a debit/credit/no charge indicator.

' Element 4 may contain another debit/credit/no charge indicator.

' Element 7 contains the cost value in cents.

'

Function GetITACostNoCharge(Seg)

If ((Seg.FindElement("1").Value = "A") Or (Seg.FindElement("4").Value = "04")) Then

GetITACostNoCharge = -Abs(CDbl(ToNumber(Seg.FindElement("7").Value)) / 100)

Else

GetITACostNoCharge = CDbl(ToNumber(Seg.FindElement("7").Value)) / 100

End If

End Function

'

' This function performs the operations required to extract

' cost data correctly from ITA segments.

'

' Element 1 contains a debit/credit/no charge indicator.

' Element 4 may contain another debit/credit/no charge indicator.

' Element 7 contains the cost value in cents.

'

Function GetITACost(Seg)

If (Seg.FindElement("1").Value = "N") Then

GetITACost = 0

Else

GetITACost = GetITACostNoCharge(Seg)

End If

End Function

'

' This function performs the operations required to extract

' cost data correctly from SAC segments. Cost value

' is returned normally if "no charge" indicator is set.

'

' Element 1 contains a debit/credit/no charge indicator.

' Element 5 contains the cost value in cents.

' Element 12 may contain another debit/credit/no charge indicator.

'

Function GetSACCostNoCharge(Seg)

If ((Seg.FindElement("1").Value = "A") Or (Seg.FindElement("12").Value = "04")) Then

GetSACCostNoCharge = -Abs(CDbl(ToNumber(Seg.FindElement("5").Value)) / 100)

Else

GetSACCostNoCharge = CDbl(ToNumber(Seg.FindElement("5").Value)) / 100

End If

End Function

'

' This function performs the operations required to extract

' cost data correctly from SAC segments.

'

' Element 1 contains a debit/credit/no charge indicator.

' Element 5 contains the cost value in cents.

' Element 12 may contain another debit/credit/no charge indicator.

'

Function GetSACCost(Seg)

If (Seg.FindElement("1").Value = "N") Then

GetSACCost = 0

Else

GetSACCost = GetSACCostNoCharge(Seg)

End If

End Function

'

' This function is basically here to

' convert empty text strings into numeric values of zero.

'

Function ToNumber(Value)

If (Trim(Value) = "") Then

ToNumber = 0

Else

ToNumber = Trim(Value)

End If

End Function

'

' This subroutine interprets elements 4 and 15 of SAC segments

' and translates them into observation types and captions

'

Sub GetSACObsTypeAndCaption(Seg, ObservationType, ValueUnitCode, Value, Caption)

Dim SAC04Code, SAC15Code

SAC04Code = Seg.FindElement("4").Value

SAC15Code = Seg.FindElement("15").Value

Select Case SAC04Code

Case "SER000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Service Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "SER001"

ObservationType = "CHARGE"

Caption = "Service Charge"

Case "SER002"

ObservationType = "CHARGE"

Caption = "Cost of Service"

Case "SER003"

ObservationType = "CHARGE"

Caption = "Customer Switching Charge"

Case "SER004"

ObservationType = "CHARGE"

Caption = "Service Charge (Off-Peak Demand)"

Case "SER005"

ObservationType = "CHARGE"

Caption = "Third Party Adminstrative Fee"

Case "SER006"

ObservationType = "CHARGE"

Caption = "Schedule Service Charge"

Case "SER007"

ObservationType = "CHARGE"

Caption = "Change of Account Info Charge"

Case "SER008"

ObservationType = "CHARGE"

Caption = "Special Metering Fee"

Case "ADJ000"

Select Case SAC15Code

Case "ELECTRIC REFUND ADJUSTMENT"

ObservationType = "CHARGE"

Caption = "Electric Refund Adjustment"

Case "REBATE-NON TAXABLE"

ObservationType = "CHARGE"

Caption = "Rebate"

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Adjustment"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "ADJ001"

ObservationType = "CHARGE"

Caption = "Cost Recovery Adjustment"

Case "ADJ002"

ObservationType = "CHARGE"

Caption = "Adjustment"

Case "ADJ003"

ObservationType = "CHARGE"

Caption = "Bank Adjustment"

Case "ADJ004"

ObservationType = "CHARGE"

Caption = "Baseline Adjustment"

Case "ADJ005"

ObservationType = "CHARGE"

Caption = "Collection Charge Adjustment"

Case "ADJ006"

ObservationType = "CHARGE"

Caption = "Franchise Fee Adjustment"

Case "ADJ007"

ObservationType = "CHARGE"

Caption = "Metering Adjustment"

Case "ADJ008"

ObservationType = "CHARGE"

Caption = "Policy Adjustment"

Case "BAS000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Account Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "BAS001"

ObservationType = "CHARGE"

Caption = "Basic Customer Charge"

Case "BAS002"

ObservationType = "CHARGE"

Caption = "Special Billing Charge"

Case "BAS003"

ObservationType = "CHARGE"

Caption = "Delivery Point Charge"

Case "BAS004"

ObservationType = "CHARGE"

Caption = "Field Service Charge"

Case "BAS005" ' this code is supposed to be obsolete

ObservationType = "CHARGE"

Caption = "Baseline Energy Charge"

Case "BAS006" ' this code is supposed to be obsolete

ObservationType = "CHARGE"

Caption = "Above Baseline Energy Charge"

Case "BUD000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Budget Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "BUD001"

ObservationType = "CHARGE"

Caption = "Budget Billing Amount"

Case "BUD002"

ObservationType = "CHARGE"

Caption = "Budget Billing Settlement"

Case "BUD003"

ObservationType = "CHARGE"

Caption = "Budget Billing Interest"

Case "COL000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Collection Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "COL001"

ObservationType = "CHARGE"

Caption = "Collection Charge"

Case "CRE000"

Select Case SAC15Code

Case "ANNUAL MERGER SAVINGS CREDIT"

ObservationType = "CHARGE"

Caption = "Annual Merger Savings Credit"

Case "DIRECT ACCESS FRANCHISE FEE CREDIT"

ObservationType = "CHARGE"

Caption = "Direct Access Franchise Credit"

Case "REVENUE CYCLE SERVICE CREDIT"

ObservationType = "CHARGE"

Caption = "Revenue Cycle Service Credit"

Case "UTILITY SERVICE CREDIT"

ObservationType = "CUSTOMERCHARGE"

Caption = "Utility Service Charge"

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Credit"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "CRE001"

ObservationType = "CHARGE"

Caption = "Credit"

Case "CRE002"

ObservationType = "CHARGE"

Caption = "Bill Limiter Credit"

Case "CRE003"

ObservationType = "CHARGE"

Caption = "Cash Discount Credit"

Case "CRE004"

ObservationType = "CHARGE"

Caption = "Transformer Credit"

Case "CRE005"

ObservationType = "CHARGE"

Caption = "Generation Credit"

Case "CRE006"

ObservationType = "CHARGE"

Caption = "Equipment Credit"

Case "CRE007"

ObservationType = "CHARGE"

Caption = "Energy Assistance Credit"

Case "CRE008"

ObservationType = "CHARGE"

Caption = "Master Meter Credit"

Case "CRE009"

ObservationType = "CHARGE"

Caption = "Interruptible Credit"

Case "CRE010"

ObservationType = "CHARGE"

Caption = "Off Peak Credit"

Case "CRE011"

ObservationType = "CHARGE"

Caption = "Service Guarantee Credit"

Case "CRE012"

ObservationType = "CHARGE"

Caption = "Zone Credit"

Case "CRE013"

ObservationType = "CHARGE"

Caption = "NGV 3rd Party Credit"

Case "CRE014"

ObservationType = "CHARGE"

Caption = "Direct Access Energy Credit"

Case "CRE015"

ObservationType = "CHARGE"

Caption = "Hourly PX Pricing Optn Energy Cr"

Case "CRE016"

ObservationType = "CHARGE"

Caption = "CTC Exemption Credit"

Case "CRE017"

ObservationType = "CHARGE"

Caption = "Baseline Credit"

Case "CRE018"

ObservationType = "CHARGE"

Caption = "One-Phase Service Credit"

Case "CRE019"

ObservationType = "CHARGE"

Caption = "PX Energy Credit"

Case "CRE020"

ObservationType = "CHARGE"

Caption = "Meter Owner Credit"

Case "CRE021"

ObservationType = "CHARGE"

Caption = "Meter Service Credit"

Case "CRE022"

ObservationType = "CHARGE"

Caption = "Meter Reading Credit"

Case "CRE023"

ObservationType = "CHARGE"

Caption = "Billing and Payment Credit"

Case "DMD000"

Select Case SAC15Code

Case Else

ObservationType = "DEMAND"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Demand"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "DMD001"

ObservationType = "DEMAND"

Caption = "Demand"

Case "DMD002"

ObservationType = "CONTRACTDEMAND"

Caption = "Contract Demand"

Case "DMD003"

ObservationType = "DEMAND"

Caption = "Baseline Demand"

Case "DMD004"

ObservationType = "REACTIVEDEMAND"

Caption = "Reactive Demand"

Case "DMD005"

ObservationType = "DEMAND"

Caption = "Summer Demand"

Case "DMD006"

ObservationType = "OFFPEAKDEM"

Caption = "Off-Peak Demand"

Case "DMD007"

ObservationType = "ONPEAKDEM"

Caption = "On-Peak Demand"

Case "DMD008"

ObservationType = "MIDPEAKDEM"

Caption = "Mid-Peak Demand"

Case "DMD009" ' not sure if this is an adjusted demand or a demand adjustment...

ObservationType = "DEMAND"

Caption = "Demand Adjustment"

Case "DMD010"

ObservationType = "DEMAND"

Caption = "Prior Period Demand"

Case "DMD011"

ObservationType = "OFFPEAKDEM"

Caption = "Summer Off-Peak Demand"

Case "DMD012"

ObservationType = "ONPEAKDEM"

Caption = "Summer On-Peak Demand"

Case "DMD013"

ObservationType = "MIDPEAKDEM"

Caption = "Summer Mid-Peak Demand"

Case "DMD014"

ObservationType = "SUPPEAKDEM"

Caption = "Summer Super On-Peak Demand"

Case "DMD015" ' no valid codes for super off-peak measurements

ObservationType = "OFFPEAKDEM"

Caption = "Summer Super Off-Peak Demand"

Case "DMD016"

ObservationType = "ONPEAKDEM"

Caption = "Winter On-Peak Demand"

Case "DMD017"

ObservationType = "OFFPEAKDEM"

Caption = "Winter Off-Peak Demand"

Case "DMD018"

ObservationType = "MIDPEAKDEM"

Caption = "Winter Mid-Peak Demand"

Case "DMD019" ' no valid codes for super off-peak measurements

ObservationType = "OFFPEAKDEM"

Caption = "Winter Super Off-Peak Demand"

Case "DMD020" ' no specific codes for daytime measurements

ObservationType = "ONPEAKDEM"

Caption = "Summer Daytime Demand"

Case "DMD021" ' no specific codes for nighttime measurements

ObservationType = "OFFPEAKDEM"

Caption = "Summer Nighttime Demand"

Case "DMD022" ' no specific codes for daytime measurements

ObservationType = "ONPEAKDEM"

Caption = "Winter Daytime Demand"

Case "DMD023" ' no specific codes for nighttime measurements

ObservationType = "OFFPEAKDEM"

Caption = "Winter Nighttime Demand"

Case "DMD024"

ObservationType = "DEMAND"

Caption = "Summer Demand"

Case "DMD025"

ObservationType = "DEMAND"

Caption = "Winter Demand"

Case "DMD026" ' no specific codes for daytime measurements

ObservationType = "ONPEAKDEM"

Caption = "Daytime Demand"

Case "DMD027" ' no specific codes for nighttime measurements

ObservationType = "OFFPEAKDEM"

Caption = "Nighttime Demand"

Case "DMD028"

ObservationType = "DEMAND"

Caption = "Demand (Gas)"

Case "DMD029"

ObservationType = "SHDPEAKDEM"

Caption = "Shoulder Demand"

Case "DAB000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Deposit"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "DAB001"

ObservationType = "CHARGE"

Caption = "Deposit"

Case "DAB002"

ObservationType = "CHARGE"

Caption = "Deposit Interest"

Case "DAB003"

ObservationType = "CHARGE"

Caption = "Deposit Applied"

Case "DAB004"

ObservationType = "CHARGE"

Caption = "Deposit Reduction"

Case "DCS000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Disconnect Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "DCS001"

ObservationType = "CHARGE"

Caption = "Disconnect Charge"

Case "DCS002"

ObservationType = "CHARGE"

Caption = "Disconnect Visit Charge"

Case "DCS003"

ObservationType = "CHARGE"

Caption = "Land Owner Disconnect Charge"

Case "DSC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Discount"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "DSC001"

ObservationType = "CHARGE"

Caption = "Discount"

Case "DSC002"

ObservationType = "CHARGE"

Caption = "Equipment Cycle Discount"

Case "DSC003"

ObservationType = "CHARGE"

Caption = "Rate Limit Discount"

Case "DSC004"

ObservationType = "CHARGE"

Caption = "Economic Development Discount"

Case "DSC005"

ObservationType = "CHARGE"

Caption = "Energy Discount"

Case "DSC006"

ObservationType = "CHARGE"

Caption = "Church Discount"

Case "DSC007"

ObservationType = "CHARGE"

Caption = "Government Service Discount"

Case "DSC008"

ObservationType = "CHARGE"

Caption = "School Discount"

Case "DSC009"

ObservationType = "CHARGE"

Caption = "Energy Assistance Discount"

Case "DSC010"

ObservationType = "CHARGE"

Caption = "Multi-Family Discount"

Case "DSC011"

ObservationType = "CHARGE"

Caption = "Legislated Rate Reduction Discnt"

Case "DSC012"

ObservationType = "CHARGE"

Caption = "Demand Voltage Discount"

Case "DSC013"

ObservationType = "CHARGE"

Caption = "Energy Voltage Discount"

Case "DIS000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Distribution Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "DIS001"

ObservationType = "CHARGE"

Caption = "Distribution Charge"

Case "DIS002"

ObservationType = "CHARGE"

Caption = "Gas Delivery Charge"

Case "DIS003"

ObservationType = "CHARGE"

Caption = "Interstate Transportation Charge"

Case "ENC000"

Select Case SAC15Code

Case Else

ObservationType = "USE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Use"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "ENC001"

ObservationType = "GENERATIONUSE"

Caption = "Generation Use"

Case "ENC002"

ObservationType = "USE"

Caption = "Use Tier"

Case "ENC003"

ObservationType = "OFFPEAKUSE"

Caption = "Off-Peak Use"

Case "ENC004"

ObservationType = "OFFPEAKUSE"

Caption = "Summer Off-Peak Use"

Case "ENC005"

ObservationType = "ONPEAKUSE"

Caption = "Summer On-Peak Use"

Case "ENC006"

ObservationType = "MIDPEAKUSE"

Caption = "Summer Mid-Peak Use"

Case "ENC007"

ObservationType = "SUPPEAKUSE"

Caption = "Summer Super On-Peak Use"

Case "ENC008" ' no valid codes for super off-peak measurements

ObservationType = "OFFPEAKUSE"

Caption = "Summer Super Off-Peak Use"

Case "ENC009"

ObservationType = "ONPEAKUSE"

Caption = "Winter On-Peak Use"

Case "ENC010"

ObservationType = "MIDPEAKUSE"

Caption = "Winter Mid-Peak Use"

Case "ENC011"

ObservationType = "OFFPEAKUSE"

Caption = "Winter Off-Peak Use"

Case "ENC012" ' no valid codes for super off-peak measurements

ObservationType = "OFFPEAKUSE"

Caption = "Winter Super Off-Peak Use"

Case "ENC013" ' no specific codes for daytime measurements

ObservationType = "ONPEAKUSE"

Caption = "Summer Daytime Use"

Case "ENC014" ' no specific codes for nighttime measurements

ObservationType = "OFFPEAKUSE"

Caption = "Summer Nighttime Use"

Case "ENC015" ' no specific codes for daytime measurements

ObservationType = "ONPEAKUSE"

Caption = "Winter Daytime Use"

Case "ENC016" ' no specific codes for nighttime measurements

ObservationType = "OFFPEAKUSE"

Caption = "Winter Nighttime Use"

Case "ENC017"

ObservationType = "USE"

Caption = "Summer Use"

Case "ENC018"

ObservationType = "USE"

Caption = "Winter Use"

Case "ENC019" ' no specific codes for daytime measurements

ObservationType = "USE"

Caption = "Daytime Use"

Case "ENC020" ' no specific codes for nighttime measurements

ObservationType = "USE"

Caption = "Nighttime Use"

Case "ENC021"

ObservationType = "USE"

Caption = "Commodity Charge"

Case "ENC022"

ObservationType = "USE"

Caption = "Interconnect Access Transmit Ch"

Case "ENC023"

ObservationType = "USE"

Caption = "Volume Tier 1 Charge"

Case "ENC024"

ObservationType = "USE"

Caption = "Volume Tier 1 Negotiated Rate"

Case "ENC025"

ObservationType = "USE"

Caption = "Volume Tier 2 Charge"

Case "ENC026"

ObservationType = "USE"

Caption = "Volume Tier 3 Charge"

Case "ENC027"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 1 Charge"

Case "ENC028"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 1 Negot Rate"

Case "ENC029"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 2 Charge"

Case "ENC030"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 2 Negot Rate"

Case "ENC031"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 3 Charge"

Case "ENC032"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 3 Negot Rate"

Case "ENC033"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 4 Charge"

Case "ENC034"

ObservationType = "USE"

Caption = "Volume G30 Size Cat 4 Negot Rate"

Case "ENC035"

ObservationType = "USE"

Caption = "3rd Party Procurement Charge"

Case "ENC036"

ObservationType = "USE"

Caption = "ESP Charge"

Case "ENC037"

ObservationType = "USE"

Caption = "Baseline Energy Charge"

Case "ENC038"

ObservationType = "USE"

Caption = "Above Baseline Energy Charge"

Case "ENC039"

ObservationType = "ONPEAKUSE"

Caption = "On-Peak Use"

Case "ENC040"

ObservationType = "USE"

Caption = "Block 1 Use"

Case "ENC041"

ObservationType = "USE"

Caption = "Block 2 Use"

Case "FAC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Facility Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "FAC001"

ObservationType = "CHARGE"

Caption = "Equipment and Service Charge"

Case "FAC002"

ObservationType = "CHARGE"

Caption = "Facilities Rental Charge"

Case "FAC003"

ObservationType = "CHARGE"

Caption = "Facilities Installation Charge"

Case "FAC004"

ObservationType = "CHARGE"

Caption = "Facilities Removal Charge"

Case "FFR000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Flat-Rate Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "FFR001"

ObservationType = "CHARGE"

Caption = "Unmetered Service Charge"

Case "FUE000"

Select Case SAC15Code

Case Else

ObservationType = "FUELADJUSTCHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Fuel Adjustment Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "FUE001"

ObservationType = "FUELADJUSTCHARGE"

Caption = "Fuel Adjustment Charge"

Case "FUE002"

ObservationType = "FUELADJUSTCHARGE"

Caption = "On-Peak Fuel Adjustment Charge"

Case "FUE003"

ObservationType = "FUELADJUSTCHARGE"

Caption = "Off-Peak Fuel Adjustment Charge"

Case "FUE004"

ObservationType = "FUELADJUSTCHARGE"

Caption = "In-Kind Fuel Charge"

Case "FUE005"

ObservationType = "FUELADJUSTCHARGE"

Caption = "GITC Interconnect Access Fuel Ch"

Case "GEN000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Generation Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "GEN001" ' don't know if this is use or demand

ObservationType = "GENERATIONUSE"

Caption = "Generation Use"

Case "GEN002" ' don't know if this is use or demand

ObservationType = "GENERATIONUSE"

Caption = "Measured Generation Use"

Case "GEN003" ' don't know if this is use or demand

ObservationType = "GENERATIONUSE"

Caption = "Adjusted Generation Use"

Case "GEN004" ' don't know if this is use or demand

ObservationType = "GENBILLEDUSE"

Caption = "Billed Generation Use"

Case "GEN005" ' don't know if this is use or demand

ObservationType = "GENERATIONUSE"

Caption = "On-Peak Generation Use"

Case "GEN006" ' don't know if this is use or demand

ObservationType = "GENERATIONUSE"

Caption = "Off-Peak Generation Use"

Case "GTC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Gen/Trans Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "GTC001"

ObservationType = "CHARGE"

Caption = "Gen/Trans Charge"

Case "GTC002"

ObservationType = "CHARGE"

Caption = "Measured Gen/Trans Charge"

Case "GTC003"

ObservationType = "CHARGE"

Caption = "Adjusted Gen/Trans Charge"

Case "GTC004"

ObservationType = "CHARGE"

Caption = "Billed Gen/Trans Charge"

Case "GTC005"

ObservationType = "CHARGE"

Caption = "On-Peak Gen/Trans Charge"

Case "GTC006"

ObservationType = "CHARGE"

Caption = "Off-Peak Gen/Trans Charge"

Case "HOC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Service Connect Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "HOC001"

ObservationType = "CHARGE"

Caption = "Service Connect Charge"

Case "IBC000"

Select Case SAC15Code

Case Else

ObservationType = "IMBALANCEUSE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Imbalance Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "IBC001"

ObservationType = "IMBALANCEUSE"

Caption = "Core Standby Service Charge"

Case "IBC002"

ObservationType = "IMBALANCEUSE"

Caption = "Non-core Retail Standby Svc Ch"

Case "IBC003"

ObservationType = "IMBALANCEUSE"

Caption = "Wholesale Standby Service Ch"

Case "IBC004"

ObservationType = "IMBALANCEUSE"

Caption = "Core Buyback Charge"

Case "IBC005"

ObservationType = "IMBALANCEUSE"

Caption = "Daily Bal WS Standby Proc Ch"

Case "IBC006"

ObservationType = "IMBALANCEUSE"

Caption = "Non-core Buyback Charge"

Case "IBC007"

ObservationType = "IMBALANCEUSE"

Caption = "Wholesale Buyback"

Case "IBC008"

ObservationType = "IMBALANCEUSE"

Caption = "Retail Buyback"

Case "IBC009"

ObservationType = "IMBALANCEUSE"

Caption = "Daily Bal Core Standby Proc Ch"

Case "IBC010"

ObservationType = "IMBALANCEUSE"

Caption = "Daily Bal N-Core Standby Proc Ch"

Case "IBC011"

ObservationType = "IMBALANCEUSE"

Caption = "Over-Nominations Buyback"

Case "IBC012"

ObservationType = "IMBALANCEUSE"

Caption = "Standby Curtailment Charge"

Case "IBC013"

ObservationType = "IMBALANCEUSE"

Caption = "Curtailment Diversion Credit"

Case "IBC014"

ObservationType = "IMBALANCEUSE"

Caption = "Curtailment Violation Charge"

Case "IBC015"

ObservationType = "IMBALANCEUSE"

Caption = "Op Flow Order Curtail Pd Penalty"

Case "IBC016"

ObservationType = "IMBALANCEUSE"

Caption = "SL2 Surcharge"

Case "IBC017"

ObservationType = "IMBALANCEUSE"

Caption = "SL345 Credit"

Case "INT000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Interest Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "INT001"

ObservationType = "CHARGE"

Caption = "Interest on Sale"

Case "INT002"

ObservationType = "CHARGE"

Caption = "Interest on Loan"

Case "INT003"

ObservationType = "CHARGE"

Caption = "ITC Adjustment Interest"

Case "LAA000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Labor Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "LAA001"

ObservationType = "CHARGE"

Caption = "Billing for Work Done"

Case "LPC000"

Select Case SAC15Code

Case Else

ObservationType = "LATECHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Late Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "LPC001"

ObservationType = "LATECHARGE"

Caption = "Late Payment Charge"

Case "ODL000"

Select Case SAC15Code

Case Else

ObservationType = "USE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Unmetered Lighting"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "ODL001"

ObservationType = "USE"

Caption = "Outdoor Lighting"

Case "ODL002"

ObservationType = "USE"

Caption = "Street Lighting"

Case "ODL003"

ObservationType = "USE"

Caption = "Traffic Signals"

Case "MAD000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Minimum Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "MAD001"

ObservationType = "CHARGE"

Caption = "Minimum Bill"

Case "MAD002"

ObservationType = "CHARGE"

Caption = "Minimum Energy Charge"

Case "MAD003"

ObservationType = "CHARGE"

Caption = "Minimum Contract"

Case "MAD004"

ObservationType = "CHARGE"

Caption = "Minimum Demand Charge"

Case "MSC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Misc Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "MSC001"

Select Case SAC15Code

Case "LOOP SUBTOTAL" ' do nothing for now - this contains a cost subtotal for all charges in this IT1 loop

ObservationType = ""

Caption = ""

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Misc Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "MSC002"

ObservationType = "CHARGE"

Caption = "Set-time Appointment Charge"

Case "MSC003"

ObservationType = "CHARGE"

Caption = "Meter Seal Replacement Charge"

Case "MSC004"

ObservationType = "CHARGE"

Caption = "Rescheduled Appointment Charge"

Case "MSC005"

ObservationType = "CHARGE"

Caption = "Benefits/Insurance Charge"

Case "MSC006"

ObservationType = "CHARGE"

Caption = "Diversion Gas"

Case "MSC007"

ObservationType = "CHARGE"

Caption = "Property Damage Charge"

Case "MSC008"

ObservationType = "CHARGE"

Caption = "Punitive Damage Charge"

Case "MSC009"

ObservationType = "CHARGE"

Caption = "Preferred Due Date Setup Fee"

Case "MSC010"

ObservationType = "CHARGE"

Caption = "Select Read Cycle Monthly Fee"

Case "MSC011"

ObservationType = "CHARGE"

Caption = "Wrap and Strap Charge"

Case "MSC012"

ObservationType = "CHARGE"

Caption = "Water Heater Strapping Charge"

Case "MSC013"

ObservationType = "CHARGE"

Caption = "Water Heater Wrapping Charge"

Case "MSC014"

ObservationType = "CHARGE"

Caption = "Appliance Connection Charge"

Case "MSC015"

ObservationType = "CHARGE"

Caption = "Injection O&M Charge"

Case "MSC016"

ObservationType = "CHARGE"

Caption = "Storage Procurement Charge"

Case "MSC017"

ObservationType = "CHARGE"

Caption = "Storage Transmission Charge"

Case "MSC018"

ObservationType = "CHARGE"

Caption = "Withdrawal O&M Charge"

Case "MSC019"

ObservationType = "CHARGE"

Caption = "Base EBB Charge"

Case "MSC020"

ObservationType = "CHARGE"

Caption = "Manual Imbalance Processing Fee"

Case "MSC021"

ObservationType = "CHARGE"

Caption = "EBB Use Charge"

Case "MSC022"

ObservationType = "CHARGE"

Caption = "Competition Transition Charge"

Case "MSC023"

ObservationType = "CHARGE"

Caption = "Trust Transfer Amount"

Case "MSC024"

ObservationType = "CHARGE"

Caption = "Public Purpose Program"

Case "MSC025"

ObservationType = "CHARGE"

Caption = "Nuclear Decommissioning Charge"

Case "MSC026"

ObservationType = "CHARGE"

Caption = "Interruptible Penalty Charge"

Case "PAY000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Payment Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "PAY001"

ObservationType = "CHARGE"

Caption = "Payment Plan Amount"

Case "PAY002"

ObservationType = "CHARGE"

Caption = "Payment Plan Settlement"

Case "PRB000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Prior Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "PRB001"

ObservationType = "CHARGE"

Caption = "Past Due Balance"

Case "PRB002"

ObservationType = "CHARGE"

Caption = "Unpaid Balance"

Case "PFA000"

Select Case SAC15Code

Case Else

ObservationType = "POWERFACTOR"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Power Factor Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "PFA001"

Value = CDbl(ToNumber(Seg.FindElement("7").Value)) ' value is in element 7

ValueUnitCode = "PERCENT" ' couldn't tell correct unit from SAC09 because it wasn't supplied

ObservationType = "POWERFACTOR"

Caption = "Power Factor Adjustment"

Case "PFA002"

Value = CDbl(ToNumber(Seg.FindElement("7").Value)) ' value is in element 7

ValueUnitCode = "PERCENT" ' couldn't tell correct unit from SAC09 because it wasn't supplied

ObservationType = "POWERFACTOR"

Caption = "Winter Power Factor Adjustment"

Case "PFA003"

Value = CDbl(ToNumber(Seg.FindElement("7").Value)) ' value is in element 7

ValueUnitCode = "PERCENT" ' couldn't tell correct unit from SAC09 because it wasn't supplied

ObservationType = "POWERFACTOR"

Caption = "Summer Power Factor Adjustment"

Case "RSC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Reconnect Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "RSC001"

ObservationType = "CHARGE"

Caption = "Reconnect Charge"

Case "RRR000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Regulatory Refund"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "RRR001"

ObservationType = "CHARGE"

Caption = "State/Intrastate Refund"

Case "RRR002"

ObservationType = "CHARGE"

Caption = "Local Refund"

Case "RRR003"

ObservationType = "CHARGE"

Caption = "Municipal Refund"

Case "RRR004"

ObservationType = "CHARGE"

Caption = "Federal/Interstate Refund"

Case "RRR005"

ObservationType = "CHARGE"

Caption = "Targeted Sales Refund"

Case "RRR006"

ObservationType = "CHARGE"

Caption = "Reg Commission Mandated Refund"

Case "RRR007"

ObservationType = "CHARGE"

Caption = "Interest on Refund"

Case "RES000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Reservation Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "RES001"

ObservationType = "CHARGE"

Caption = "Core Subscription Reserve Charge"

Case "RES002"

ObservationType = "CHARGE"

Caption = "Core Subscr Reserve Charge Adj"

Case "RES003"

ObservationType = "CHARGE"

Caption = "Injection Reservation Charge"

Case "RES004"

ObservationType = "CHARGE"

Caption = "Withdrawal Reservation Charge"

Case "RES005"

ObservationType = "CHARGE"

Caption = "Inventory Reservation Charge"

Case "RES006"

ObservationType = "CHARGE"

Caption = "Storage Reserve Charge Prepaymnt"

Case "RES007"

ObservationType = "CHARGE"

Caption = "Interconnect Assess Firm Res Ch"

Case "RES008"

ObservationType = "CHARGE"

Caption = "ITC Reservation Charge"

Case "RTC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Return Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "RTC001"

ObservationType = "CHARGE"

Caption = "Return Check Fee"

Case "RTC002"

ObservationType = "CHARGE"

Caption = "Return Check Amount"

Case "SMD000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Sales/Merchandise Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "SMD001"

ObservationType = "CHARGE"

Caption = "Appliance Contract Installment"

Case "SMD002"

ObservationType = "CHARGE"

Caption = "Cash Sales/Stores Purchase"

Case "SMD003"

ObservationType = "CHARGE"

Caption = "Comm Heat Pump/Water Heater Ch"

Case "SMD004"

ObservationType = "CHARGE"

Caption = "Contract Installment"

Case "SMD005"

ObservationType = "CHARGE"

Caption = "Grain Drying Service Charge"

Case "SMD006"

ObservationType = "CHARGE"

Caption = "Heat Pump/Comm Equip Charge"

Case "SMD007"

ObservationType = "CHARGE"

Caption = "Heat Pump Installment Charge"

Case "SMD008"

ObservationType = "CHARGE"

Caption = "Home Wiring Inspection Charge"

Case "SMD009"

ObservationType = "CHARGE"

Caption = "Delinquent Merchandise Chargeoff"

Case "SMD010"

ObservationType = "CHARGE"

Caption = "Miscellaneous Materials Charge"

Case "SMD011"

ObservationType = "CHARGE"

Caption = "Other Services/Merch Charge"

Case "SMD012"

ObservationType = "CHARGE"

Caption = "Power Protection Equip Charge"

Case "SMD013"

ObservationType = "CHARGE"

Caption = "Purchased Parts Charge"

Case "SMD014"

ObservationType = "CHARGE"

Caption = "Meter Installation Charge"

Case "SMD015"

ObservationType = "CHARGE"

Caption = "Meter Maintenance/Repair Charge"

Case "SMD016"

ObservationType = "CHARGE"

Caption = "Meter Read Charge"

Case "SMD017"

ObservationType = "CHARGE"

Caption = "Meter Removal Charge"

Case "SMD018"

ObservationType = "CHARGE"

Caption = "Other Meter Services"

Case "SMD019"

ObservationType = "CHARGE"

Caption = "Special Meter Read Charge"

Case "SMD020"

ObservationType = "CHARGE"

Caption = "Meter Changeout Charge"

Case "SUR000"

Select Case SAC15Code

Case "ELECTRIC EMERGENCY PROCUREMENT SURCHARGE"

ObservationType = "CHARGE"

Caption = "Emergency Procurement Charge"

Case "ENERGY SURCHARGE: 131% - 200% OF BASELINE"

ObservationType = "CHARGE"

Caption = "Excess Use Charge (131%-200%)"

Case "ENERGY SURCHARGE: 201% - 300% OF BASELINE"

ObservationType = "CHARGE"

Caption = "Excess Use Charge (201%-300%)"

Case "ENERGY SURCHARGE: OVER 300% OF BASELINE"

ObservationType = "CHARGE"

Caption = "Excess Use Charge (>300%)"

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Surcharge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "SUR001"

ObservationType = "CHARGE"

Caption = "State/Intrastate Surcharge"

Case "SUR002"

ObservationType = "CHARGE"

Caption = "Local Surcharge"

Case "SUR003"

ObservationType = "CHARGE"

Caption = "Federal/Interstate Surcharge"

Case "SUR004"

ObservationType = "CHARGE"

Caption = "Mobile Home Park Surcharge"

Case "SUR005"

ObservationType = "CHARGE"

Caption = "Imputed Transportation Charge"

Case "SUR006"

ObservationType = "CHARGE"

Caption = "GITC Interconnect Cost Surch"

Case "SUR007"

ObservationType = "CHARGE"

Caption = "Low Income Surcharge"

Case "SUR008"

ObservationType = "CHARGE"

Caption = "Interstate Transition Cost Surch"

Case "SUR009"

ObservationType = "CHARGE"

Caption = "Interstate Transition Surch Cr"

Case "TEM000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Surcharge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "TEM001"

ObservationType = "CHARGE"

Caption = "Temporary Service Charge"

Case "TPI000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown 3rd Party Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "TPI001"

ObservationType = "CHARGE"

Caption = "Utility-Initiated Line Item"

Case "TPI002"

ObservationType = "CHARGE"

Caption = "ESP-Initiated Line Item"

Case "TRS000"

Select Case SAC15Code

Case "TRANSFER DEBIT"

ObservationType = "CHARGE"

Caption = "Transfer Debit"

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Transfer Amount"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "TRS001"

ObservationType = "CHARGE"

Caption = "Transfer From Other Account"

Case "TRS002"

ObservationType = "CHARGE"

Caption = "Transfer To Deposit Amount"

Case "TRN000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Transmission Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "TRN001"

ObservationType = "CHARGE"

Caption = "Transmission Charge"

Case "VOC000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Voltage Charge"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "VOC001" ' this code is supposed to be obsolete

ObservationType = "CHARGE"

Caption = "Demand Voltage Discount"

Case "VOC002" ' this code is supposed to be obsolete

ObservationType = "CHARGE"

Caption = "Energy Voltage Discount"

Case "VOC003"

ObservationType = "CHARGE"

Caption = "General Primary Voltage Charge"

Case "VOC004"

ObservationType = "CHARGE"

Caption = "General Secondary Voltage Charge"

Case "VCR000"

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Contribution"

End If

Caption = UCase(Caption) ' make it stand out on screen

End Select

Case "VCR001"

ObservationType = "CHARGE"

Caption = "Charitable Contribution"

Case Else ' any other code is an unknown

Select Case SAC15Code

Case Else

ObservationType = "CHARGE"

If (Len(SAC15Code) > 0) Then

Caption = SAC15Code

Else

Caption = "Unknown Charge"

End If

End Select

Caption = UCase(Caption) ' make it stand out on screen

End Select

End Sub

Sub GetTXIObsTypeAndCaption(Seg, ObservationType, ValueUnitCode, Value, Caption)

Select Case Seg.FindElement("1").Value

Case "CA"

ObservationType = "CITYTAX"

Caption = "City Tax"

Case "ET" ' some kind of energy tax; just use generic tax code

ObservationType = "TAX"

Caption = "Energy Comm. Tax"

Case "MP"

ObservationType = "MUNICIPALTAX"

Caption = "Municipal Tax"

Case Else ' don't know what kind of taxes to expect, so just make a general tax line

ObservationType = "TAX"

Caption = "Unknown Tax"

Caption = UCase(Caption) ' make it stand out on screen

End Select

End Sub

Sub GetMEAObsTypeAndCaption(Seg, ObservationType, ValueUnitCode, Value, Caption)

Select Case Seg.FindElement("4").Value

Case "KH"

ValueUnitCode = "KWH"

Case "K1"

ValueUnitCode = "KW"

Case "K3"

ValueUnitCode = "KVARH"

Case "TD"

ValueUnitCode = "THERM"

Case Else ' don't know what unit to assign if not one of the codes above

ValueUnitCode = ""

End Select

Select Case Seg.FindElement("7").Value

Case "41"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "OFFPEAKUSE"

Caption = "Off-Peak Distribution Use"

Case "KW"

ObservationType = "OFFPEAKDEM"

Caption = "Off-Peak Distribution Demand"

Case "KVARH"

ObservationType = "OFFPKREACTIVEUSE"

Caption = "Off-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "42"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "ONPEAKUSE"

Caption = "On-Peak Distribution Use"

Case "KW"

ObservationType = "ONPEAKDEM"

Caption = "On-Peak Distribution Demand"

Case "KVARH"

ObservationType = "ONPKREACTIVEUSE"

Caption = "On-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "43"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "MIDPEAKUSE"

Caption = "Mid-Peak Distribution Use"

Case "KW"

ObservationType = "MIDPEAKDEM"

Caption = "Mid-Peak Distribution Demand"

Case "KVARH"

ObservationType = "MIDPKREACTIVEUSE"

Caption = "Mid-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "45"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "ONPEAKUSE"

Caption = "Summer On-Peak Distribution Use"

Case "KW"

ObservationType = "ONPEAKDEM"

Caption = "Summer On-Peak Distribution Dem"

Case "KVARH"

ObservationType = "ONPKREACTIVEUSE"

Caption = "Summer On-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "49"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "ONPEAKUSE"

Caption = "Winter On-Peak Distribution Use"

Case "KW"

ObservationType = "ONPEAKDEM"

Caption = "Winter On-Peak Distribution Dem"

Case "KVARH"

ObservationType = "ONPKREACTIVEUSE"

Caption = "Winter On-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "50"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "MIDPEAKUSE"

Caption = "Witner Mid-Peak Distribution Use"

Case "KW"

ObservationType = "MIDPEAKDEM"

Caption = "Witner Mid-Peak Distribution Dem"

Case "KVARH"

ObservationType = "MIDPKREACTIVEUSE"

Caption = "Witner Mid-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "51"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "USE"

Caption = "Distribution Use"

Case "KW"

ObservationType = "DEMAND"

Caption = "Distribution Demand"

Case "KVARH"

ObservationType = "REACTIVEUSE"

Caption = "Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "73"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "OFFPEAKUSE"

Caption = "Summer Off-Peak Distribution Use"

Case "KW"

ObservationType = "OFFPEAKDEM"

Caption = "Summer Off-Peak Distribution Dem"

Case "KVARH"

ObservationType = "OFFPKREACTIVEUSE"

Caption = "Summer Off-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "74"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "MIDPEAKUSE"

Caption = "Summer Mid-Peak Distribution Use"

Case "KW"

ObservationType = "MIDPEAKDEM"

Caption = "Summer Mid-Peak Distribution Dem"

Case "KVARH"

ObservationType = "MIDPKREACTIVEUSE"

Caption = "Summer Mid-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case "75"

Select Case ValueUnitCode

Case "KWH"

ObservationType = "OFFPEAKUSE"

Caption = "Winter Off-Peak Distribution Use"

Case "KW"

ObservationType = "OFFPEAKDEM"

Caption = "Winter Off-Peak Distribution Dem"

Case "KVARH"

ObservationType = "OFFPKREACTIVEUSE"

Caption = "Winter Off-Peak Reactive Use"

Case Else ' don't know what type of measurement

ObservationType = ""

Caption = ""

End Select

Case Else ' don't know what unit to assign if not one of the codes above

ObservationType = ""

Caption = ""

End Select

End Sub

' -- End Utility Subroutines --

EnergyCAP provides two methods for importing billing data into the EnergyCAP database using a flat file format.

• Import from the EnergyCAP Work Flow Manager (preferred) or Account Manager using the Import Processor (File > Import)
• Import externally using an external task file (imptsk.exe)

This topic describes the import process from the EnergyCAP Work Flow Manager. For more detailed information on flat file preparation than is provided in this topic (including acceptable data formats, observation pairings, etc.), see the topic on bill import rules and options.

The Bill Import Process

The actual bill import is part of an import process that may vary from organization to organization, but will generally include all or most of the following steps:

1. Prepare import flat file
2. Create a bill batch
3. Import bills
4. Reimport bills if necessary
5. Verify imported bills
6. Audit bills
7. Edit/fix problems
8. Close bill batch
9. Approve bills
10. Export bills

Below, each of these steps are explained in more detail.

1. Prepare the import flat file. 
   
   Regardless of the bill import method, the same flat file must be prepared. EnergyCAP has rigid requirements for the acceptable flat file format. Each line of the file corresponds to a complete bill record.
   
   NOTE: For summary bills, the Bill Import Processor recognizes all records with the same account number, start date and end date as belonging to the same bill. 
   
   

REQUIRED file information/formats:

1. The import file must include a header line.
2. The import file must include the following as the first five columns for each bill record, in the order indicated:
   1. ACCOUNT—the alpha-numeric Account Code for the bill record
   2. STARTDATE (YYYYMMDD) —The billing period Start Date
   3. ENDDATE (YYYYMMDD) —The billing period End Date
   4. ACE—The bill type (A=Actual, C=Corrected, E=Estimated)
   5. METERCODE—the Meter Code of the meter associated with the bill.
   6. (OPTIONAL)VENDORCODE—the Vendor Code is not required, but if it is used, the VENDOR CODE must immediately follow the METERCODE.
3. The import file records MUST be sorted in the following sequence:
   1. 1. Ascending Account Code
   2. 2. Ascending Vendor Code (if included)
   3. 3. Ascending Start Date
   4. 4. Ascending End Date.

For additional information, see the topic on import rules and options.

1. Create a bill batch.
2. Import the flat file created in step 1 by clicking File>Import from the Work Flow Manager and selecting the import file using the Import File window functions. 
   
   

After the import, check for problem bills from the batch in the Bill Import Processor Problem folder. 



Bills appearing there should be edited and then re-imported (see next step).

1. If necessary, correct and reimport corrected bills from the Bill Import Processor Problem folder as follows:
   1. Navigate to the Work Flow Manager (Accounting > Work Flow Manager).
   2. Open the Bill Import Processor Problem Folder.
   3. Update/edit individual bill records as necessary by double-clicking the bill record line (cut/paste Windows functions do operate in the kickout).
   4. When corrections have been completed, click Tools > Processor > Bill Reimport. 
      
      
      
      The Bill Reimport window will open.
   5. Click Start. When the reimport is complete, the Log window will appear.
   6. Review the log and click the Close button(s).
2. Verify imported bills. As a user convenience, all imported bills are displayed in the Waiting to Verify folder. They can be audited and edited, or they can all be deleted at once and imported afresh. Verify the imported bills as follows:
   1. Set Work Flow Options (Options button from the Work Flow Manager title bar) to display only the imported bills for the relevant batch.
      
      
   2. Open the Waiting to Verify folder in the Work Flow Manager and check the control totals for the number of bills and the total dollar amount.
      
      
   3. Compare the totals with those from the original flat file. After control totals have been matched successfully, select the entire bill batch from the bill list in the Waiting to Verify folder. Then right-click and select the Verify option. The bills will be removed from the Waiting to Verify folder.
      
      NOTE: Verified imported bills will not be displayed in the Unapproved folder until the batch has been closed (See step 8).
3. Audit the imported bill batch from the green folder using your organization’s approved audit procedure.
4. Edit/fix any problems revealed in the audit through the customary bill edit process.
5. Close the bill batch. Imported bills will then be visible in the Unapproved folder in the Work Flow Manager.
6. Approve the bill batch using established organization procedure(s). 
7. If interfacing with A/P, export the approved bill batch.

Click here for an example Excel file with some common errors.

Sort Order:

Typically, each line in the file will be for an Account-Bill Start Date-Meter group. Sort Order must be:

1. Ascending Account Code
2. Ascending Vendor Code (if included)
3. Ascending Start Date
4. Ascending End Date.

Account Code

The Account Code cannot be NULL.

Estimated/Actual/Corrected Bill Flag:

• Estimated: If "E," any existing bills on the same account with the same dates will be deleted and the 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.
• Corrected: If "C," the bill is treated as a correction and existing bills on the same account with the same dates are NOT deleted.

Any other value, or no value, is interpreted as if it were an "A."

Meter Code

• If the Meter Code is NULL, the values will be applied to the Account Code ONLY
• If the Meter Code is NULL, the bill line item is assumed to be MONEY since no usage information can be applied to the account
• If the Meter Code is NOT NULL and the Meter Code exists on that account, then the values are associated with that meter’s commodity
• If the Meter Code is NOT NULL and the Meter Code = !AUTO!, then the importer will automatically assign the imported values to the meter that is assigned to this account PROVIDED that there is a usage value and only one meter on the account. If there is more than one meter assigned to the account OR the meter code does not exist, the record will be skipped and a log entry will be created. If there is no usage, the cost value will be associated with the account and not the meter.

In the above example, the importer will look for the meter on account 123456-1.  If it has JUST ONE METER, then the importer will assign all charges in this row to that meter.  If it has more than one meter, then it will not import the row.

• If the account has more than one meter on it  OF DIFFERENT COMMODITIES you can use !AUTO!:COMMODITY where COMMODITY is the commodity code of the meter in EnergyCAP.  This allows you to use the !AUTO! lookup feature for accounts with multiple meters of different commodity types.  If the account has more than one meter of the commodity type in the !AUTO!:COMMODITY value, then the importer will generate an error message indicating "Cannot determine meter code."
  
  Example:

In the above example, the importer will look for the ELECTRIC meter on account 123456-1.  If it has JUST ONE METER, then the importer will assign all charges in this row to that meter.  If it has more than one ELECTRIC meter, then it will not import the row.

To view the commodities in EnergyCAP, click Tools > Unit System

The full list of available commodities can be see from the Commodities tab in the Unit System Editor window.

Bill Data ("OBSERVATION TYPE:UNIT:CAPTION" Pairs)

Typically, each line in the file will be for an Account-Bill Start Date-Meter group. Thus, the file should be sorted by ascending Account Code, ascending Vendor Code (if included), ascending Start Date, ascending End Date.

The headers for the first five columns described above (Account code, start date, end date, actual/estimated, meter code) are mandatory columns. Every column after meter code is optional and therefore the column heading is what defines the column contents. These column headings must be in ALL CAPS except for the CAPTION portion.

• Data must be grouped by account code, bill start date, and bill end date if it is to be treated as multiple body lines on the same bill.
• Separating body lines in the same file may result in previously entered bills being replaced.
• If there are multiple meters on the same account, all meters MUST appear on consecutive lines and all meters MUST have the same bill start and end dates.
• For Observation type pairs, BOTH need to have a value, even if it is 0. Neither can be NULL.

Example of Bodylines in a Bill

To achieve the above results, the import file should be formatted in the following manner (Note: the first five columns have been removed from this example):

Since each row represents charges for a specific meter on a specific account, there will be occurrences where the row of data will not have values for some columns (ex. You have water and electric on a particular account.  The OBSERVATION TYPE:UNIT:CAPTION for water will be different than the electric - and so you need to just put values in the columns corresponding to that row’s meter).

The "OBSERVATION TYPE:UNIT:CAPTION" pair can appear multiple times in each row.  It is valid to define multiple Value, Cost, or Value immediately followed by Cost fields in the file header. For example, a file containing data for accounts with Electric Use and Cost, Electric Demand and Cost, and Natural Gas Use and Cost would have a header record of: "USE:KWH", "USE:USDOLLARS", "BILLEDDEMAND:KW", "BILLEDDEMAND:USDOLLARS", "USE:CCF", "USE:USDOLLARS". The body lines for Electric Use/Cost and Demand/Cost are expected to be associated with an electric meter and the body lines for Natural Gas Use/Cost are expected to be associated with a natural gas meter. The Natural Gas Use/Cost fields should not be populated in the electric meter line and vice versa.

To obtain bills with both Value and Cost entries on a single body line, you must enter the value first and immediately follow it with the cost. For example, a body line with usage in KWH and a cost in dollars would need the columns to be defined as "USE:KWH", "USE:USDOLLARS". ! Beginning with Release 6.0, NO NULLS are permitted in any observation type pair (such as USE/USE)! If a NULL is present, the record will not be imported and an error will be generated. !

If two consecutive columns have the same OBSERVATION TYPE, and the second column has a UNIT of cost, a single line item is created on the bill using both the value and the cost. For example, consecutive columns of "USE:KWH", "USE:USDOLLARS" will result in a single line on the bill having KWH and Cost (in US Dollars).
To obtain bills with a Cost Only body line, use one of the NOUN observation types that are associated with MONEY (Charge, SubtotalCost, Tax, etc.). For example, "CHARGE:USDOLLARS".

Example: 
In this example, the consecutive columns share the same observation type of USE.  Since the first column reflects the UOM of KWH and the second column shows a UOM  of USDOLLARS (which is associated with MONEY).

This will produce a single line in the bill view.

To import bills with a Value Only body line, use any of the NOUN observation types that are NOT associated with MONEY. For example, "DEMAND:KW".

This screenshot shows bodylines (underlined in red) which are Value Only.

Optional Caption Feature

Normally, the caption (the text label shown on the left side of the bill entry screen for each line item on a bill) is set equal to the observation type display name. You can set your own captions by making a third entry in the heading. Example: "USE:KWH:ON PEAK TIER 1 USAGE" is the column heading to insert the desired bill bodyline heading. The caption cannot have a colon in it.  If you choose to use a caption in situations such as USE:KWH and USE:COST, you want to specify a caption JUST FOR THE FIRST ITEM IN THE PAIR, and not the second.  So in the example, this is the correct way to modify the caption and still end up with the information being represented in the bill as one line and not two:

Correct:      USE:KWH:Electric Usage     and    USE:USDOLLARS
Incorrect:    USE:KWH:Electric Usage    and    USE:USDOLLARS:Electric Usage

Pay Amount

The "PAYAMOUNT:USDOLLARS" column specifies the pay amount for the bill. In earlier versions of EnerygCAP, if this column was not included in the import file, the bill pay amount would be the sum of all costs of all bodylines on the bill subject to the rules indicated in the Unit System for each observation type specified.  If PayAmount is included, but the sum of the charges on the bill calculated to a different total, the bill was saved but with a + or - balance. This was not good.  Therefore, beginning with Release 6.0, the PayAmount column is simply ignored by the Importer. This assures that all imported bills will balance.

EndOfRecord

IMPORTANT:  It is HIGHLY recommended to add an additional column called ‘*EndOfRecord’, that will be ignored in the import, so that the last column of every row in the import file has a value in it.  The value for that column can be anything, but is generally set to the letter ‘X’.  Again, this data is NOT imported to EC, but is used to indicate to the import utility that there are no further values to be processed in the current row and to proceed with the next row in the import file.  Every row in the import file should have a value of ‘X’ in this column.

Example Header:

ACCOUNT, STARTDATE, ENDDATE, ACE, METER, USE:KWH, USE:COST, *END_OF_RECORD_MARKER

The * in the column header ‘*END_OF_RECORD_MARKER’ tells the importer to ignore the contents of this column.  It is then recommended to give every row in the import file a value of ‘X’ for this column.  In that way each row is terminated and will be ensured to process correctly.

Suggestions

WHEN IN DOUBT, be CONSERVATIVE and EXPLICIT and test a sample of the file format with a small number of records.

Any column that has a header which begins with an * will be ignored. This allows you to minimize the reformatting of non-comforming spreadsheets by simply ignoring non-conforming columns.  See Pay Amount section for another suggestion of how this can 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.

• BILLPERIOD (format MM:yyyy, example: 09:2005 for Sep 05). This forces a bill to be assigned to a particular billing period; otherwise, the billing period is determined by the month in which the mid-point of the bill falls. MM is the calendar month, not the fiscal month, in the event that they are different for your organization. Column header is BILLPERIOD.
• DUEDATE (format YYYYMMDD)
• STATEMENTDATE (format YYYYMMDD)
• CONTROLCODE (format alphanumeric) This is to allow the inclusion of bill image location where EnergyCAP is set up to look in this field for that information. Maximum number of characters is 255.
• ACCTPERIOD (format MM:yyyy, example: 09:2005 for Sep 05). The Accounting Period associated with the bill. MM is the calendar month, not the fiscal month, in the event that they are different for your organization. Maximum number of characters is 10.
• VENDORCODE (format alphanumeric) The Vendor Code associated with the bill. The Vendor Code is not required, but if it is used, the VENDOR CODE must immediately follow the METERCODE.
• INVOICENUMBER

Reimporting bills with the Reimport Processor

In the event that a file aborts partially through processing, the bills which have been successfully entered will remain in EnergyCAP Enterprise. As long as the file does not contain Corrected "C" records, it can be corrected and processed again. If the file does contain Corrected "C" records and it is processed again, additional bills for the "C" records will be created and manual steps will need to be taken to remove/prevent duplicate bills from causing difficulties.  As an alternative to this, rows that generated errors can be re-imported through the Bill Reimport Processor which is available from within Workflow Manager.

Example: The problems listed in the Bill Import Processor folder are eligible for re-import by using the Bill Reimport Processor.  Unless the issues indicated in the Message column are addressed, the bills will not import successfully.

To run the Bill Reimport Processor, navigate to the Account Manager in EnergyCAP (Accounting/Accounts)and click Tools/Processor/Bill Reimport.

The Bill Reimport window will open. Click the Start button to initiate the Reimport. When done click Close.

Bill Work Flow for Electronic Bills

After bills are imported into EnergyCAP, they are given a status of Unverified.  In order for a bill to be eligible for export, it must have a status of Verified.  To verify bills you have imported, go to Work Flow Manager and verify bills contained in the Waiting to Verify folder.

The import results are reported in an on-screen log. You can save or print this log in order to maintain a record.  Additionally, any issues logged will be viewable from within Work Flow Manager in the Bill Import Problem folder.

Flat File Bill Import Using imptsk.exe

Bill Import external to EnergyCAP using the imptsk.exe program

It is also possible to import bills from the command line. See also the topic on External Tasks. Here is an example and explanation of each element:

The usage is:

imptsk -d [dsn] -u [user] -p [pwd] -c [converter] -f [path] -x -a [path] -l [path] -s [section] -v [verbose messages] -remote -t [traceID]

Where:

• -d [dsn] is the ODBC data source name
• -u [user] is the system user id
• -p [pwd] is the system password
• -c [converter] is the name of the converter
• -f [path] is the file to be imported
• -x will delete the input file after importing the data
• -a [path] will archive the data to the path specified
• -l [path] is the log file
• -s [section] is the profile section name
• -v [message text] when used, will add additional detail to the import log file.
• -remote is the command to connect to the HTTP data provider (use in conjunction with the -d switch, which identifies the Datasource from the Catalog Server (as displayed in the EnergyCAP Login window)
• -t [traceID] is the SQL server trace to initiate during verification

Usage Example: