Virtual Bill Sample Script

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