Unexporting Bills by Batch or AP Export Date Range

A stand-alone executable file called Administrator allows a number of functions to be performed on your EnergyCAP database that are not available from within EnergyCAP itself – such as editing historical bill assignment, creating user defined fields and assigning larger groups of places or meters to a group.

Before you can run Administrator and login to the database, you must have “Execute” user permission for “Run Administrator” set within your user record in EnergyCAP.

To grant the Execute user permission for Administrator:

1. Open the User Manager (Setup > Users) in EnergyCAP.
2. Click to select/highlight the desired user. Then either right-click OR click the Properties button to open the User Properties window.
3. Expand the Application list by clicking on the plus icon, and scroll down to Run Administrator.
4. Click the Execute checkbox and then click OK..

If user permissions have been obtained, open the Administrator application by double-clicking the administrator.exe file located in the EnergyCAP Enterprise program folder. The EnergyCAP login window will open. Login to the Administrator using  your EnergyCAP user ID, password and datasource to login.

Available functions:

In administrator, all navigation is done through the top menu bar. Selecting one of the menu options will display a list of the functions available. They include:

Record Counter:

Shows the number of records in key data tables. (You can also run report SU53 – Database Statistics for this information.)

Bill Editor:

Allows you to edit certain Bill fields that are not editable from within EnergyCAP (ex: bill image file name, bill payment data, export flags). You need to know the BillID in order to retrieve a bill, so the normal process is to view bill properties in EnergyCAP, get the BillID, and then pull it up in Administrator.

 

Flag Setter/Unsetter:

This allows you to set or unset bill flags for many bills at once. For example, if you want to “unexport” a batch of exported bills, this allows you to do so. The A/P Export and G/L Export flags determine the export status of a bill record. When they are exported, the flag changes from N to Y and the date and operator are recorded. If you need to re-export the bills (the most common reason is because the export file was erased or is missing) you need to change the export flag from Y back to N. This will cause the bills to re-appear in the Waiting to A/P Export list in Work Flow Manager, which is your proof that they have been un-exported. Typically, you will use the “A/P Export Date” filter option (below) because you want to un-export all bills exported on one particular day. To unexport, click the “Update A/P Export” checkbox and set the Exported flag to N.

Batch Editor:

Allows you to edit records in the Batch control table (one record per Batch). This data table is not accessible from within EnergyCAP. Batch records hold information about each bill entry batch (if used).

Historical Bills:

Change Place or Vendor Code:

Allows you to update past bills when the place code or vendor code is not correct. Example: An account was assigned to the wrong vendor and bills have been entered. Changing the vendor now will ensure that future bills are assigned to the proper vendor code, but historical bills are not automatically updated when a vendor is changed. This option lets you update history.  Note – If you move a meter from one building to another, EnergyCAP DOES automatically update all history to the new building (the new place code).

Historical Bills – Assign History to A Different Meter on Same Account:

This usually occurs when you have deregulated accounts. You set up two meters and two accounts and now want to retain the two accounts but share the same meter on both. Let’s say that account #1 is the LDC (distribution) account with MeterLDC and account #2 is the supply account with MeterSUP. Since you want to share one meter instead of two (so that the one meter reflects total usage and cost), you want to eliminate MeterSUP and use only MeterLDC on both accounts.

• In EnergyCAP, go to Account Properties for the supply account, click on meters tab, click on add a meter (+), show all meters, and add MeterLDC to the supply account. You then need to add a supply/generation template to this meter (one that is a supply/generation template and doesn’t cause usage to be counted twice).You can now use Administrator to assign the history from MeterSUP to Meter LDC.
• When done, MeterSUP should be unassigned from the supply account because it is no longer used. You can unassign it from Account Properties – Meters. (You can also delete this meter via the Facility Manager.)
• Please see the Tech Bulletin on Deregulated Accounts for further background information.

Accounts and Meters:

Assign Many Meters to a Rate Code:

You can select many meters at once (based on a selection of commodity and vendor) and assign them to a rate code. Rate Code must already be set up in Accounting – Rates.

Change All Accounts in a Cost Center to Active/Inactive:

This function provides a quick way to inactivate many accounts at once. Create a cost center to hold the inactive accounts, move the accounts to it (right click on an account to move it) and then run this option to set the active flag to inactive. You can re-run the option to quickly set all accounts back to active. In order to EXCLUDE inactive accounts from reports, set the report Status Filter to Active accounts only.

Delete Entire Cost Center:

Bill Payment Data

This option imports bill payment data (such as check number and date paid) in order to update EnergyCAP from your accounting system.

Required import file format is CSV, seven fields. Columns 1-4 are required; null is acceptable for column 5. If building the import file from MS Excel, a delimiter must be included in column 6 (space is OK). In column 7, data can be null and will be ignored.  The Billid is the key lookup field. A log file identifies any records that cannot be processed due to (1) BillID not found or (2) improper data format.

Log file:

The Log file lists the bad records and then in field 7 shows the error message, so user can fix bad records and re-import the log file. If anything exists in field #7, it gets overwritten with the error message when the log record is created. Example:

Original record: 12345,9876,20050128,paid,20050130,200501,optional comment

Format:                      a,       b,              c,     d,              e,          f,              g

Record written to log file: 12345,9876,20050128,paid,20050130,200501,Bill ID not found.

Possible error conditions are: Bill ID not found, invalid date format, invalid record format (not 7 data elements found before CR/LF), invalid accounting period format.

1. BillID
2. Check number (text, 32)
3. Check date, format yyyymmdd (datetime, 8)
4. Pay status (text, 10)
5. Cleared date, format yyyymmdd (datetime, 8) (may be null)
6. Accounting period, yyyypp (pp=period number, 01-13, see db table AccountPeriod); accounting period is optional and may be null. Use Tools – Work Flow Wizard to activate Accounting period/year fields on bill entry screen.
7. Comment field, ignored by import (commas not allowed)

Bill Image Data

Instructions for importing bill image records into the BillImage table:

Required import file format is CSV, three fields.

1. Control Code
2. Image filename
3. Comment field

How it works:

The Administrator importer looks for the Control Code field in the Bill table and uses that field to match the bill record with the specified bill image.

CAUTION: The Importer does not force the Control Code to be unique, so if the import file contains duplicate control codes for different bills, one bill image could be tagged to more than one bill record. The log file rules described in the previous section on Bill Payment Data also apply to the bill image import.

Possible error conditions are:

• Control Code not found in Bill table,
• Image filename format not allowed,
• Invalid record format (not 3 data elements found before CR/LF)

Budget Import

Budgets are imported through Administrator, a stand-alone executable file that allows a number of functions to be performed on your EnergyCAP database that are not available from within EnergyCAP itself.  For example,  Administrator imports budget data from an external budget creation process (usually Excel).

Prior to importing budget data, a new MASTER budget for the desired year must be created in EnergyCAP, for example, "FY2009."

1. Click Budget > New > Budget.
   
   The Budget Wizard window opens.
   
2. Complete the Budget Wizard forms as desired and click Generate.
3. Create and save the external budget file, using the current budget import template. Clients with deregulated accounts should use the template that includes Vendor Code, since Vendor Code is required to distinguish between supplier and distributor accounts.
• Budget data must be meter-by-meter, month-by-month.
• Required import file format is CSV with 38 fields. Make certain to save the file to csv. format before importing it. The first column is for the Meter Code; then Account Code; then a budgeted cost, use (expressed in COMMON  UNITS for the commodity being tracked) and rate (unit cost) for each of the 12 months. The first month must correspond to the first month of the fiscal year set up in EnergyCAP (usually January or July).
• Budget values must be formatted WITHOUT dollar signs and commas.
• The meter code is the EnergyCAP Meter Code value.
4. Import the new MASTER budget:
1. Open Administrator.
2. Click Import > Budget Import.
3. Select the Budget Code (the name of the Master budget you created) and Version fields, and click Import. The dropdown fields will be populated with however many versions are available.
4. Select the file and click Open. The budget data will be imported into EnergyCAP. During Import, the Administrator takes the information from the CSV file and replaces the values within EnergyCAP for the selected 12-month budget. Imported budget values for each budget month are stored in the BinData table (value,uValue and ucValue fields).
5. Modify the budget as desired.

Budget modifications are made through EnergyCAP Budget and Forecast Manager (Analysis > Budgets). For more information on budgets, see the Budget and Forecast Manager topic.

NOTE: Do not execute stored procedures unless you are a skilled user and have been advised to do so!

User Field Setup:

The User Field Setup option from the Administrator program (Setup > User Field Setup) adds new user-defined fields to Meter, Place, Vendor and Account tables.  Also can be used to edit or delete existing user-defined fields. Note: This is not used to add data values to existing fields. Use the “User Fields” tab of Properties within EnergyCAP to do that.

G/L Subcode Setup:

Assign Places/Meters to Groups:

The Export function is a specialty function for certain users incorporating meter-reading data obtained via the Itron MVRS. Contact EnergyCAP technical support if more information is required.

1. Launch the Administrator (C:\Program Files\EnergyCAP Enterprise\administrator.exe)
2. Click on Edit > Flag setter/unsetter.
   
   
   A warning window will open.
   
    
3. If your data is secure, click Yes to continue the unexport procedure. The Filter window will open.
   
   
4. Choose the desired filter. If you want to unexport a BATCH of bills, select Batch Code. To unexport ALL the bills that were exported to AP on a certain date, choose AP Export Date. Click the associated drop-down arrow to click-select the desired batch/date for the selected filter. 
   
   
   When done, click OK. The Flag Setter/Unsetter window will open.
   
   
5. Update the AP Export section by checking the AP Export checkbox and setting the flag position 
   to N[o] (see additional notes with the above image).
6. Click OK to update the bills. Or click Cancel to exit the Flag Setter/Unsetter window without making changes.

Bills that matched the selected filter options will be updated so it will appear that they were never exported to AP. They should become visible in the Waiting to AP Export folder in EnergyCAP Work Flow Manager.