Balance sheets and pages

The class BalanceSheet shows the current financial status of a contract. The concept of the class is much the same as hotels usually handle the account status for their guests:

Charges are written to the customer's balance sheet as they arise. When a guest checks out any charges of the balance sheet are written to the invoice. Except from "Something from the mini-bar last night?" there is no need for the attendant to run around in hotel collecting the records from the bar or restaurant, rather the invoice is issued almost immediately.

For the hotel administration this system has yet another advantage: The current open balance can be easily retrieved and compared to the guest's credit worthiness.

In OSB the concept is extended: The balance sheet does provides access to all charges of a contract. It consists of a balance page that stores all information relevant for a billing period and the related invoice.This includes charges debited in advance, but may as well include the available free minutes.

Diagram Responsiblities BalanceSheet and BsPage

Once a contract is activated a balance sheet exists throughout its lifecycle. BalanceSheet contains and controls several BsPage each of them storing the charges for one invoice.

This design covers the following topics:

Principle for assignment of charges
Use cases
Locking of balance sheets
Creation of new balance pages
Charges assigned to a balance page

What follows below is the original design which was later replaced by balance pages.
Though there were a lots of changes during the implementation of the library, the original concepts are still valid.

OSB maintains exactly one open balance sheet for each contract, except of course for deactivated contracts after the final invoice. If an application closes a balance sheet the class itself will automatically open a new balance sheet if necessary. Similarly, the class will also create a new balance sheet automatically whenever charges for a contract, that currently does not have an open balance sheet, are risen (the exact criteria are explained in the section First and last invoice for a contract).

Design considerations: Forwarding of unbilled charges to next balance sheet
The balance sheet contains unbilled data since contract was last billed. OSB normally will not bill upto the current date and time, but rather will define the end date of the period to bill some days in the past. If we stick to the requirement that charges are assigned to a distinct balance sheet, then we require that billing must forward any unbilled charges to the newly created balance sheet. For database performance reasons, this is not acceptable.
Let's summarize again what we want to achieve: The balance sheet should tell us, what are the current, up-to-date unbilled charges for each contract. After an invoice is created it must be possible to identify the billed charges (Rebilling!) In batch billing operation we do not want to update unbilled charges, e.g. setting something like charge.BALANCESHEET_ID. If billing is run in "on-demand" mode such updates could be acceptable because the delay for one invoice is about 1 second. BUT: while this is OK for single customer that wants to get his invoice, it is out of discussion to waist approx. 275 hours to database I/O when creating 1'000'000 invoices! Basically there are two different solutions how to fulfill the requirements above: OSB tries to anticipate the invoicing periods of a contract and opens a new balance sheet whenever needed. By this we'd have multiple billed balance sheets, one (ev. 2) unbilled balance sheet and one current balance sheet for each contract. If a contract is billed with a billing period that doesn't match the predictions, OSB would need to completely reorganize the unbilled balance sheet. Whenever a charge is assigned to a contract, we store the timestamp in the database and update the unbilled amounts in the current balance sheet of the contract. The billing application invoices all charges assigned to the contract within the invoice's period. Unbilled amounts in the old and new balance sheet are maintained accordingly.
The current implementation follows approach 1. It is however not possible to change the billing period of a balance page.

Design considerations: Forwarding of unbilled charges to next balance sheet

The balance sheet contains unbilled data since contract was last billed. OSB normally will not bill upto the current date and time, but rather will define the end date of the period to bill some days in the past.
If we stick to the requirement that charges are assigned to a distinct balance sheet, then we require that billing must forward any unbilled charges to the newly created balance sheet. For database performance reasons, this is not acceptable.

Let's summarize again what we want to achieve:

The balance sheet should tell us, what are the current, up-to-date unbilled charges for each contract.
After an invoice is created it must be possible to identify the billed charges (Rebilling!)
In batch billing operation we do not want to update unbilled charges, e.g. setting something like charge.BALANCESHEET_ID.
If billing is run in "on-demand" mode such updates could be acceptable because the delay for one invoice is about 1 second. BUT: while this is OK for single customer that wants to get his invoice, it is out of discussion to waist approx. 275 hours to database I/O when creating 1'000'000 invoices!

Basically there are two different solutions how to fulfill the requirements above:

OSB tries to anticipate the invoicing periods of a contract and opens a new balance sheet whenever needed. By this we'd have multiple billed balance sheets, one (ev. 2) unbilled balance sheet and one current balance sheet for each contract. If a contract is billed with a billing period that doesn't match the predictions, OSB would need to completely reorganize the unbilled balance sheet.
Whenever a charge is assigned to a contract, we store the timestamp in the database and update the unbilled amounts in the current balance sheet of the contract. The billing application invoices all charges assigned to the contract within the invoice's period. Unbilled amounts in the old and new balance sheet are maintained accordingly.

The current implementation follows approach 1. It is however not possible to change the billing period of a balance page.

In the database data of BalanceSheet are stored in a collection of tables, all (logically) referring to the master table BALANCESHEET. This table determines the if the charges related are already billed or not. A contract's balance sheet may be updated only if the appropriate row in the master table is locked.

Use cases

Billing

The diagram below shows how the billing application uses the balance sheet of a contract: In contrast to other applications, billing usually processes the whole balance sheet, closes the balance sheet after the creation of the invoice and creates the new balance sheet for the contract just billed. Other actors, e.g. customer administration, only browse the balance sheets and may add single charges.

Diagram BalanceSheet use cases for billing application

When the billing application starts invoicing a contract it first locks the balance sheet. This prevents any other application from modifying data that most probably will become obsolete within a short time. During the invoice generation for a contract, billing retrieves information on advance charges and reads usage- and one-time charges. After the invoice is created the application closes the current balance sheet (the one just worked out) and writes information for the next billing period (e.g. advance charges) to the newly opened balance sheet.

First and last invoice for a contract

Billing creates invoices for contracts which have an open balance sheet that fulfills the criteria of the billcycle assigned to the contract (e.g., has been opened more than one month ago). If there exists no open balance sheet, then the billing engine does not process the contract, thus no subscription charges are calculated and no invoice is created at all.

In other words, this means that as long as the contract is active, there must be an accompanying open balance sheet for the contract and its owner so that the billing application can regularly invoice subscription fees and other charges.

In line with this logic, the first invoice for a contract is created as soon as the open balance sheet fulfills the criteria of the billcycle being processed. This requires that activation of a contract implies opening the first balance sheet, so that billing can charge subscription right from the activation date, even if there are no other charges to be billed for the contract.

Subsequent balance sheets will be opened by the library, whenever the currently open balance sheet is closed. No new balance sheet is opened if the contract was deactivated before the close date of the current balance sheet and there are no charges present for the contract after this date. The field BALANCE_SHEET.LAST_ASSIGNED shows when the last billing relevant modification was made to the balance sheet and the library updates this field whenever new charges are added to a balance sheet.

Even after a contract has been deactivated and its last invoice was created, further charges may incur. This can happen, e.g., when delayed roaming records arrive at the operator's site. In such a case, if no open balance sheet exists, the library must open one so that it can assign the charge to it.

Options for IntervalBillcycle: Create first invoice when the open balance sheet is first seen, independent of the interval. If the contract is deactivated, invoice all available charges, even if they are assigned to the balance sheet after the current billing interval.

Option for both billcycle types: Indicate first and last invoice in ToBill. By definition, the last invoice is one whose billing interval contains the contract deactivation date.

+ ToDo: Should status changes also cause a change of LAST_ASSIGNED?
+ ToDo billing: all ToBill structures for the same contract must be processed by the same thread

Customer care

Some typical use cases of the balance sheet for a customer care operator are shown in diagram

Diagram BalanceSheet use cases in customer care

A customer care user should be able to browse and preview the current charges for a contract. For some services provided to a customer he can manually rise a one-time charge, or he modifies the amount of disputed charged. If the customer or the contract changes OSB may rise an event, similar if a personalized product or product item is modified. In both cases OSB checks if the event is chargeable and eventually adds the charge to the current balance sheet.

Rating

For rating there is only one use case related to the balance sheet:

Diagram BalanceSheet use case for rating

After rating has processed the incoming call records, the rated UsageRecord must be added to the balance sheet of each contract. This may sound trivial at a first glance but there are consequences from that:

Rating doesn't need to care about a contract's total usage. The class BalanceSheet is responsible to maintain the appropriate sums.
Rating has to sort the usage records by contract. Failing to do so will result in excessive locks of BALANCESHEET.
The balance sheets should be updated only after a UsageRecordStream is completely finished. Again this is to avoid needless locking.

Reporting

Diagram shows other potential usage of balance sheets. The usage cases described here are different: For their realization there shouldn't be a need to read and process the individual charges.

Diagram Other use cases of balance sheets

The master table BALANCESHEET should store all "management relevant" information, i.e., sum up the the single charges by reasonable groups: Usage, one-time and subscription charges. Usage charges could be divided into a few groups, e.g., national, international and roaming calls.

Members

Public members of the class BalanceSheet are shown in diagram

Diagram public members of BalanceSheet

Database

In the database the class BalanceSheet is stored in a collection of tables shown in diagram I think the table names are self explanatory, for detailed information please refer to the table definitions below.

Diagram Database structure for BalanceSheet

In diagram the relation between BALANCESHEET and the three data tables USAGERECORD, ONETIME_CHARGE and ADVANCE_CHARGE is shown as a dependency only: The charges are not directly assigned to a balance sheet. If we want to know to which balance sheet a charge belongs, we must use <CHARGE>.ASSIGNED with BALANCESHEET.OPENED and BALANCESHEET.CLOSED.

Contract transfer

The OWNER_ID is stored at the balance sheet to facilitate determining balance-sheets (contracts) to be billed for a billcycle (see billcycle). With the current logic, a contract transfer means that the CONTRACT.OWNER_ID and BALANCE_SHEET.OWNER_ID change. The next bill will therefore be for the new owner.
This basic contract transfer logic can be extended by duplicating the open balance sheet when a contract transfer takes place. After the transfer, there would be two open balance sheets for the same contract; one for the old and one for the new owner. The OPENED date of the new open balance sheet defines the start of the billing period for the first invoice for the new owner. The same date must be stored at the balancesheet of the old owner (in a new field, not in CLOSED). It indicates the end date of the billing period for the old owner and that this invoice is his last invoice (advance charges). It will be good - if not technically necessary anyway - to keep the history of other relevant data (contract) in case of changes such as a contract transfer.

Master table

The table BALANCESHEET is the master of all tables that store balance sheet related data. By this we mean that

Data in any balance sheet table may be modified only if the corresponding entry of BALANCESHEET is locked.
Billing updates only BALANCESHEET.CLOSED after an invoice is created and not each individual item.

Except in special situations, that yet have to be determined, charges that are related to a closed balance sheet must not be modified: I can not see a reason why this should be needed, but there are two very good reasons for not allowing it: Firstly OSB relies on the balance sheet data for re-billing (see requirements), and secondly somebody must update all related data accordingly.

Table definitions

BALANCESHEET		master table for information about unbilled and billed charges
identification
BALANCESHEET_ID	integer not null	unique id of balance sheet (PK)
CONTRACT_ID	integer not null	contract of the balance sheet
OWNER_ID	integer not null	the associate id who owns the balance sheet
BILLCYCLE_ID	integer not null	billcycle identifier
summary statements
USAGE_CHARGES	number	total usage charges
ONETIME_CHARGES	number	sum of one-time charges
ADVANCE_CHARGES	number	sum of advance subscription debits
administration
OPENED	date not null	timestamp when balance sheet was opened
CLOSED	date	timestamp when balance sheet was closed (billed), NULL indicates the current balance sheet of a contract.
LAST_ASSIGNED	date	timestamp when the last billing relevant modification was made

ONETIME_CHARGE		onetime charges
identification
ONETIME_CHARGE_ID	integer not null	unique identifier (PK)
CONTRACT_ID	integer not null	charged contract
PERSONALIZED_PRODUCT_ID	integer not null	charged personalized product: allows to identify the product user
PRODUCT_PART	integer	charged product part
STATUS_ID	integer	identifier of status change
...	...	<other info to identify the origin of the charge>
charge definition
CURRENCY	currency	currency charged
AMOUNT	number	amount charged
CHARGE_TRAITS_ID	integer not null	traits of charge
REMARK	varchar2	remark for manually assigned charges
administration
ASSIGNED	date not null	timestamp when charge was risen

ADVANCE_CHARGE		invoiced advance charges
identification
ADVANCE_CHARGE_ID	integer not null	unique identifier (PK)
CONTRACT_ID	integer not null	charged contract
PERSONALIZED_PRODUCT_ID	integer not null	charged personalized product: allows to identify the product user
PRODUCT_PART	integer	charged product part
charge definition
CURRENCY	currency	currency charged
AMOUNT	number	amount charged
CHARGE_TRAITS_ID	integer not null	traits of charge
FROM	date not null	start of charged period (inclusive)
UP_TO	date	end of charged period (not inclusive)
administration
ASSIGNED	date not null	timestamp when charge was invoiced