Bill Processing Exceptions Report

For all bills with a Prorated / Aggregated? value of ‘PRORATED-TIME’ you are able to review the details of the original bill from the document associated with the original bill. The original bill includes more than one month for the Billing Period.

For bills with a Prorated / Aggregated? value of ‘AGGREGATED’, you are able to review the details of the involved bills using the Reference Bill Code. The Bill ID for the new record (the result of the aggregations) is equal to the Reference Bill ID for the records that were aggregated.

The Bill Processing Exceptions report shows bills in three panes.

The Non-Monthly Periods to Prorate and Non-Monthly Periods to Aggregate panes show bills from the Bills table, while the Energy reports use records in the Archived Bills table. Reviewing bills in these two panes enables you to evaluate the accuracy of the bill data before approving and archiving them, so that they can be used in reports. So, for these bills, the process is to review the bills, prorate or aggregate them, and then approve them using the Accounting Supervisor / Review and Approve Bills task.

The Missing Periods pane shows bills in the Bill Archive table. When the application generates bills for the gaps, they are generated into the Bills (bill) table, and not the Archived Bills tables, so you will need to approve the generated bills so they are also archived.

When you prorate or aggregate a bill, records are generated in the Bills table, but not in the Archived Bills table. Once bills are archived there are some values that can’t be modified.

If you need to modify an archived bill, you can use the Edit Archived Bill task that enables you to 'unarchive' the bill so that you can edit it. You will then have the ability to process the record in the Bill Processing Exception report.

When you use the Bill Processing Exceptions task to prorate bills by time, or to aggregate bills, the application uses the original bill data for the weather model analysis, not the bills that are the result of prorating or aggregating the bill. The application only uses records where the Prorated / Aggregated ? value is NO.

For existing reports, the following bill records are included:

• Records that did not need to be prorated are included, and also
• If a bill needed to be prorated or aggregated, the generated records for those bills are included. The original bills are not included.

Note, also, that only bills that are approved / archived are included.

The weather model regression analysis requires bill data that is entered in for complete months with no gaps in Date Start and Date End, and Billing Periods.

To display the bills that do not have this format:

1. Select the Energy Manager / Facilities Director / and under Weather Regression Analysis, select Bill Processing Exceptions. task.

   The Bill Processing Exceptions filter appears.

2. Use the Filter console to specify the Bill Type and Building Code for which you want to verify your bill data entry. Optionally, you can select a Vendor Code and Account to limit the bills shown to only those vendors and accounts.
3. Click Verify Model.

   The application checks for bills for the selected building and bill type and then verifies if:

   • Any bills in the Bills or Archived Bills tables have gaps (that is, missing months between existing periods ) These bills are shown in the Missing Periods pane.
   • Any bills that correspond to a period that is bigger than a month (shown in the Non-Monthly Periods to Prorate pane)
   • Any bills that are smaller than a month (shown in the Aggregating Non-Monthly Periods pane).

   The application searches for anomalies in the bills meeting the filter restriction you entered, and displays the bills in the Missing Periods, Non-Monthly Periods to Prorate, and Non-Monthly Periods to Aggregate panes. Records in the Bills or Archived Bills tables that have already been prorated or aggregated are not shown in the panes, as these bills have already been reconciled

   You can now generate bills that fix these anomalies as described in the following sections.

The Non-Monthly Periods to Prorate pane shows bill periods that were found in the Bills table that don't correspond to a monthly bill, and that include more than one month. For example, a bill for a two-month period , or one bill for a quarter. The system presents bills to prorate if :

• the year-month for the Date Service Start is different than the year-month for the Date Service End for the bill,
• the difference between the date service start and end is greater than 33 days.

The Non-Monthly Periods pane shows only bill records that have not already been prorated by time. This means that bills that have the Reference ID for a parent bill that has the prorated_aggregated value of PRORATED_TIME are not shown, as these bills are the result of a previous proration by time action.

For each of these bills, the pane shows the stored values for the bill for your review.

To prorate bills for non-monthly periods:

1. Generate the Bill Processing Exceptions report as described in the previous section.
2. In the Non-Monthly Periods to Prorate pane, select the check boxes for the non-monthly periods you want to prorate.

3. Click Prorate Bill.

   The application calculates how many time periods were involved in this initial bill, and how many bill records should be generated to create monthly bills. For example, if a bill has a Date Start of the 10th of June and a Date End of the 10th of August, it would be prorated to three bills, one for each month that the bills span. For each of these bills the application calculates the corresponding amounts, and generates the bill records in the Bills table.

   For each of the new records the application saves the following data:

   Billing Period . This is the month and year that the bill covers. The application uses the date_service_start of the original bill for the first generated bill, and increases to the next month for each of the following bills it generates.

   Bill Code (bill_id) . This is an automatically generated by the application, with the structure: “PRO-” to indicate it’s a prorated bill, followed by the time period value, and followed by “-“ and the building code)

   Date Due. This is set to the current date. You can modify this if needed.

   Date Service Start. For the first period generated, the application sets this to the date_service_start from the original bill. For subsequent bills, this is set to the first day in the month.

   Date Service End . The application sets this to the last day of the month for the first bill generated, and for intermediate bills. For the last period generated, it sets this to the date_service_end of the original bill.

   Description. This is set to the description from the original bill.

   Amount Income, Amount Expense, Volume CCF, Demand / Power, Quantity (Kwh), Energy / Consumption. The application sets these values for each generated bill after calculating the proration based on the original bill days, and the number of days for the generated period(s).

   For the following, the application uses the same value as the original bill:

   • Vendor Code and Vendor Account Code.
   • Cost per Kwh.
   • Cost per MMBT

   Reference Bill Code. This is set to the original Bill Code.

   Prorated / Aggregated? . This is set to ‘PRORATED-TIME.'

Note: The existing bill_lines records, will continue to be associated with the original bill, and not with the generated ones.

Once you prorate a bill by time, the screen refreshes, so that the bill is no longer shown in the list.

You can now approve the bill which also archives the bill so that it can be used in reports. See Review and Approve Bills.

The following table shows an example of bill records before and after the proration action runs:

Bill record field	Initial record	First new record	Second new record
time_period	2015-07	2015-07	2015-08
date_due	2015-08-31	2015-07-31	2015-08-31
date_service_start	2015-07-01	2015-07-01	2015-08-01
date_service_end	2015-08-31	2015-07-31	2015-08-31
bill_id	E_JFKA_2015_07	PRO-2015-07-JFK A	PRO-2015-08-JFK A
bl_id	JFK A	JFK A	JFK A
vn_id	ELECTRICAL CO-OP	ELECTRICAL CO-OP	ELECTRICAL CO-OP
vn_ac_id	34-567-89	34-567-89	34-567-89
bill_type_id	ELECTRIC	ELECTRIC	ELECTRIC
amount_income	0	0	0
amount_expense	27245	13622.5	13622.5
qty_volume	0	0	0
qty_power	292	146	146
qty_kwh	0	0	0
qty_energy	1151	575.5	575.5
cost_kwh	0	0	0
cost_mmbtu	0	0	0
prorated_aggregated	NO	PRORATED-TIME	PRORATED-TIME
reference_bill_id		E_JFKA_2015_07	E_JFKA_2015_07

The Non-Monthly Periods to Aggregate pane shows the bill periods that were found in the Bills table that don't correspond to a full month's bill. For example, this would include bills for the middle of the month and for any time periods that is less than a month.

The bills that are shown meet both of the following conditions:

• Have the same year-month value for their date service start and end ; that is, they are for the same month
• There are other bills in the same time period; that is, there is another bill record with which to aggregate.

Note: Records that have already been aggregated with others are not shown. These records are child records that have a reference_bill_id pointing to a parent bill that has a prorated_aggreated value of ‘AGGREGATED’.

To aggregate bills:

1. Working from the Energy Manager / Billing Processing Exceptions task, enter a Billing Type and Building Code in the Filter and click Verify Model. The Non-Monthly Periods pane show bills from the Bills table that can be aggregated.

   The Non-Monthly Periods to Aggregate pane shows the stored values for the bills meeting this criteria.

2. Review these bills to determine which bills you want to aggregate. You should select bills from the same vendor or the same time period (same month and year.)
3. Use the Smart Search Console to restrict the bills to the same time period and vendor account.

   Note: You should select all bills corresponding to the same time period at the same time, so that the aggregation can be done in just one step.

4. Select the check boxes for the bills to be aggregated together, and click Aggregate Bills.

   If all selected bills belong to the same period and vendor account, then the application calculates the total amounts after aggregating all the selected bills.

   The application generates a new record with the following information:

   Time Period. This corresponds to the time period for the selected bills.

   Bill ID. The application generates the new Bill Code by adding first a “AGG” prefix to indicate that it’s an aggregated bill. The already existing bill_lines records continue to point to the original bill, and not to the new generated one.

   Building Code, Vendor Code, Vendor Account Code, Bill Type, Date Due, Description. These values are taken from the first selected bill.

   Date Service Start. This is the earliest of the date services starts for the bills to be aggregated.

   Date Service End. This is the latest of the date services ends for the bills to be aggregated.

   Amount Income, Amount Expense, Volume CCF, Demand / Power, Quantity (Kwh), Energy / Consumption. The application calculates these values when you run the Aggregate Bills action.

   The following values are taken from the original bill:

   • Cost per Kwh
   • Cost per MMBT

   Prorated / Aggregated? The application sets this to ‘AGGREGATED’

   Reference Bill Code. In the case of aggregation, the Reference Bill Code for the newly created record is stored in the original bills after the aggregation occurs.

   After generating the new record, the application updates the records you selected to be aggregated by setting the following two values:

   • Prorated / Aggregated? (set to ‘NO’)
   • Reference Bill Code (set to the Bill Code for the newly generated bill.)

   Finally, the application refreshes all the panes, so that bills that it just processed are removed from the view.

   After aggregating the bills, you can review the newly generated bill or the aggregation in the Review and Approve Bills task. Once you approve the bill, it is archived and included in reports.

Example of the bill records before the aggregation action is executed:

Bill Record Field	First Initial Record	Second Initial Record
time_period	2015-07	2015-07
date_due	2015-08-31	2015-08-31
date_service_start	2015-07-01	2015-07-15
date_service_end	2015-07-31	2015-07-31
bill_id	E_JFKA_2015_07	E_JKFA_2015_07_2
bl_id	JFK A	JFK A
vn_id	ELECTRICAL CO-OP	ELECTRICAL CO-OP
vn_ac_id	34-567-89	34-567-89
bill_type_id	ELECTRIC	ELECTRIC
amount_income	0	0
amount_expense	27245	10000
qty_volume	0	0
qty_power	292	140
qty_kwh	0	0
qty_energy	1151	500
cost_kwh	0	0
cost_mmbtu	0	0
prorated_aggregated	NO	NO
reference_bill_id	Null	Null

Example of the bill records after the aggregation action is executed:

Bill Record Field	First Initial Record	Second Initial Record	New Record
time_period	2015-07	2015-07	2015-07
date_due	2015-08-31	2015-08-31	2015-08-31
date_service_start	2015-07-01	2015-07-15	2015-07-01
date_service_end	2015-07-31	2015-07-31	2015-07-31
bill_id	E_JFKA_2015_07	E_JKFA_2015_07_2	AGG-2015-07-JFK A
bl_id	JFK A	JFK A	JFK A
vn_id	ELECTRICAL CO-OP	ELECTRICAL CO-OP	ELECTRICAL CO-OP
vn_ac_id	34-567-89	34-567-89	34-567-89
bill_type_id	ELECTRIC	ELECTRIC	ELECTRIC
amount_income	0	0	0
amount_expense	27245	10000	37245
qty_volume	0	0	0
qty_power	292	140	432
qty_kwh	0	0	0
qty_energy	1151	500	1651
cost_kwh	0	0	0
cost_mmbtu	0	0	0
prorated_aggregated	NO	NO	AGGREGATED
reference_bill_id	AGG-2015-07-JFK A	AGG-2015-07-JFK A

The Missing Periods pane shows bills from the Archived Bills table that have gaps in the Billing Periods entered for the bill. That is, in a sequence of bills, there is a missing month. A period appears as missing when it doesn't exist in both the Bills (bill) and the Archived Bills (bill_archive) table for the selected Building Code and Bill Type. When the application generates bills for the gaps, they are generated into the Bills (bill) table, not the Archived Bills tables, so you will need to approve the generated bills so they are also archived.

Note: The application looks for gaps between only the existing first and last bill for the selected Bill Type and Building Code.

For each of the found gaps, the Missing Periods pane shows the data that will be used to create the record in the Bills table:

• Missing Time Period. The time period that doesn't have a bill.
• Bill ID. An automatic Bill ID generated by the application, with the structure: “GAP-” to indicate it’s a gap found between bills, followed by the time period value, and followed by “-“ and the building code)
• Vendor Code / Vendor Account Code: A vendor and account code generated by the application based on the Vendor Code for the same building for the just previous time period.
• Site Code. The application enters the site for the Building Code entered in the Filter console.
• Date Due. The system enters the current date.
  • Date Service Start / Date Service End: 
    • If the gap is only one month, the Date Service Start is the Date Service End of the previous bill found., and the Date Service End is the Date Service Start of the next bill found.
    • If the gap is more than one month, then the Date Service Start of the first bill is the Date Service End Date of the previous bill found. The Date Service Start of the next bill is always the Date Service End of the previous one, and the Date Service End Dates are the Service Start Date + 30 days. The Date Service End for the last bill is the Date Service Start for the next bill found.

    This results in dates very close to the actual bill dates. You can still edit the values, but the edit would most likely be within four days of the generated dates.

To edit and generate the bills:

1. To review or edit the missing periods before generating bills, in the Missing Periods pane, click the Edit action in the row for a bill.

   A popup window shows the data for the generated bill. You can edit any values except quantities and amounts that are calculated in the database, the Building Code and Bill Type used for the search, and the Missing Time Period. Since the Missing Time Period is the period that needs to exist to process the bills, you should not change the system-generated Missing Time Period.

2. Edit the values as needed. For example, edit the Date Due.
3. Click Generate Bill.

   The application generates the bill using any edits you made, and then refreshes the exception report view so that the bill disappears from the list

By default, all quantities and amounts are set to 0 in the database.

Note: All quantities and amount values in the Bills table are saved as 0. All these values are calculated from bill_lines records, and the application does not generate bill_lines for the bills generated for the missing periods. Before you can submit bills for approval, the existing application code checks for bill lines existing for the bill. You now add bill lines to the bill as described in the next section.

Adding bill amount and line items for the bills generated for missing periods

When bills are prorated or aggregated, the application takes care of the amounts for the generated bills, but, when the application generates bills for missing periods, it generates the bill with 0 amount, and with no bill lines. Before sending for approval bills that are generated for missing periods, you must add bill lines to the bill.

To process bills generated for missing periods:

1. Use the Enter bills task to enter the amounts for the bill and the bill lines. The amount you enter depends on the circumstances that caused the gap.

   For example:

   • If you did not enter a bill for a month, you can check the amount billed for that missing month and enter it.
   • If the amounts for the previous or next bill for the missing period are not correct, you might want to fill in the amount for the gap with amounts from these bills. You would manually enter the amount you want to distribute to the gap period, and then edit the amounts for the previous and next bills to reflect the adjustment you are making.
2. Approve the bill. After entering the bill and line item amounts, you approve the generated bill.

   See Enter Bills, Line Items and Vendor Accounts, and Send Bills for Approval.

If you update a Bill Code, a cascading action updates the reference_bill_id field for any records pointing to this record.

For most Energy reports, you can drill down to the details of the bill_lines for a selected bill. If a bill has not been prorated or aggregated (that is, its prorated_aggregated field is NO), you are able to drill down to the bill lines.

If a bill has been prorated or aggregated, then the following occur when you drill down:

• If the bill was prorated by time, then you are shown bill lines for the original bill.
• If the bill resulted from an aggregation action, you are shown details for the original bill and its bill lines. Bills that are aggregated do not have their own bill lines). For bills that were aggregated, the application should show the following information from the bill_archive and bill_lines_archive tables where the reference bill code is equal to the selected bill code.

You can use the following fields to track the bills generated by a proration or aggregation action.

The Prorated / Aggregated? field. The value for this field indicates if the bill is a normal monthly bill, if it has been prorated into several bills, or if it resulted from several bills being aggregated to a single new bill

• NO. This is the default value. It indicates a regular monthly bill.
• PRORATED-TIME. This indicates a bill that was created by prorating the original bill into several bills.
• AGGREGATED. This indicates a bill that resulted from several bills for the same vendor and Time Period being aggregated to one bill.
• PRORATED-LOCATION. This indicates a bill that resulted from a single bill for multiple buildings being prorated into a separate bill for each building.

The Reference Bill Code. This field tracks the original bill:

• If a proration action is taken, the new generated records have this value pointing to the original bill from which the proration came.
• If an aggregation action is taken, the original records have this value that points to the resulting bill code after the aggregated record is generated.

Note the following about working with bills that have already been prorated or aggregated:

• You cannot take a bill created by aggregation, and aggregate it again, since the application will try to give the same auto-generated id and this will produce an error. The exception is if you aggregate two bills, then change the name of the bill created, and then aggregate it with another one. In this case you can do it.
• A bill resulting from a aggregation cannot be prorated by location , since it doesn't have bill lines, and thus the application will show an error message
• You can prorate by time a bill that has been prorated by location, but be aware that in reports you are not able to see bill lines for these bills. This is because the parent bill (the bill used for the second proration) doesn't have bill lines, it's the bill used or the first proration (second levels up) that has bill lines.