Billing



Designing Reports

When designing reports you must put on your developer hat! Designing a report involves using the BIRT design client to design your report. Designs are captured in a ".rptdesign" file. You'll want to apply configuration management techniques to any .rptdesign file (e.g., cvs checkin)

Generating Bills

Some scheduled tasks (may be outside the NPS in this case) should be available to kick the BIRT to generate all the PDF bills... This task will scan the Account Billing Info table periodically, and generate a bill for each entry that has a past Bill Date.

<account_name> equal "*" denotes a combined bill for all user within the <account_domain>.

Domain Wide Service Charges

Domain wide service charges, such as T1, Profession Service, Monthly Fee, can be subscribed as Service Items to the "domain@<domain>" account in the Service Subscription Table and similarily identified in the Service Record Table.
So when BIRT query the Service Record Table for all users in a specific domain, these "collective" charges will be retrieved from the "domain" user, and can be presented by in some special section of the bill.
BIRT has a very clean and straight forward way of handlinig collectice charges. They call it "grouping", which can be used to sort/sum/muiltiple/whatever on the individual groups that the developer defines.

Multi-Tier Rating

Multi-Tier rating will be implemented by Multiple Post Rating to the same set of CDR.

As an example, some CDR's would need to be rated twice, once with the rate a carrier is selling it's service for, and once with it's cost, so the margin a carrier makes can be calculated. Let's say if you click on 'generate invoice' for a user/domain/reseller, if there could be another link to 'generate cost & margin', which would take a different rate plan and run all CDR's through this rate plan.

A Post Rating Scheduler driven by the Post Rating Schedule will pull the CDRs from the designated Account, apply post rating using the rate plan of the Charge To Account and charge the result to the Charge To Account.

Design

Service Package

This specify all the features and the associated charges provisioned for each user


Plan Description
Dial Plan This specify the Dial Plan applied to the user
Dial Permission This specify the Dial Permission applied to the user
Service Plan This specify the Service Plan applied to the user
Service Subscription This specify the count of subscription for each Service Items as listed in the Service Plan
Rate plan This specify the Rate Plan applied to the user
Billing Account Info This register the billing info for the account
Misc e.g. Grace period for tax, extra charges......


Service Plan


Field type Descriptions
Plan Name varchar(127) Name of the Service Plan
<Plan Name> is unique within each domain, but can be re-used in different domain.
Plan Domain varchar(127) Domain that the Service Plan belong to
<Plan Name>@<Plan Domain> uniquely identify a specific Servie Plan in the system.
Charge Period varchar(31) The period, e.g. 1 week, 1 month, that the charge associated with the Service Plan should be applied.
Description varchar(128) General description of the Service Plan


Service Item

This is a list of service item belonging to a all the Service Plan


Field type Descriptions
Plan Name varchar(127) Name of the Service Plan that this Service Item belong to
Plan Domain varchar(127) Domain that this Service item belong to
Item Category varchar(32) See Service Item Category
Item Name varchar(128) Name of the service item, e.g. VMail, Monthly Subscription
Item Parameter varchar(127) Parameter specific to the Item, e.g. # minutes for Minute Bucket
Default Count double Default count of the service item.
Optional Service Item MUST have "0" Default Count, and election of such are explicitly specified in Service Item Subscription table.
Item Unit varchar(32) e.g. month, line
Unit Charge double Charge per unit
Tax double Tax rate for this service item
Item Description varchar(128) General description of the item

Note

  • All charges for Service Item are for the next period
  • Need special handling for 1st and last period

Minute Buckets (Not Implemented Yet)

Minute Buckets is a special Service Item, which is used by Bill generator

  • Defined as a Service Item
    • Define the Bucket ID and Amount in minutes
    • Bucket window is with respect to the Service Plan Period
    • CDR are included in the bucker based on their Release Time
  • Two passes CDR processing
    • Each rate entry will have a Bucket ID, and applied during Rating at 1st pass
    • During Srvice Charge event, each rated CDR entry for the past Service Period will be examined and tallied for bucket usage
      • Register the Post-Bucket Charge, and may be a time stamp for Bucket processing
    • Straddling Entry will fully charged. May be an option to make them free by a system parameter
    • Need pro-rating of Charged and Bucket for 1st and last Service Period

Field type Descriptions
Plan Name varchar(127) Name of the Service Plan that this Service Item belong to
Plan Domain varchar(127) Domain that this Service item belong to
Item Category varchar(32) "buk"
Item Name varchar(127) Name of the service item, e.g. 500 Minute Bucket
Item Parameter varchar(127) <bucket id>:<# minutes in bucket>, e.g. D:500 for domestic 500 minutes
Item Count double Total minutes in bucket X = <Item Count> * <# minutes in bucket X>
Item Unit varchar(31) e.g. 500 minutes
Unit Charge double Charge per bucket count
Tax double Tax rate for this service item
Item Description varchar(128) General description of the item

Extended Tax (Not Implemented Yet)

  • Implement similar to minute bucket
    • need Tax ID in Rate and CDR entry
    • as service item with zero charge and non-zero tax for non-usage base taxes
    • as service item with zero charge, zero tax but tax_id and rate in parameter for usage base taxes

Service Item Subscription

This list the additional Service Items subscribed by the user, e.g. 10 Vanity Number, 10G of storage....


Field type Descriptions
Account User varchar(127) <user> component of the account.
<Account User>@<Account Domain> together uniquely identify the user in the system.
<Account User>=="domain" denotes a service subscription by a domain.
<Account User> must not be "*" for this table.
Account Domain varchar(127) <domain> component of the account.
Plan Name varchar(127) Name of the Service Plan that this Service Item belong to
This Service Item may not pre-exist in the corresponding Service Plan, i.e. The Service Item Subscription can add new "Service Item" to the corresponding Service Plan for a specific Account.
Plan Domain varchar(127) Domain that this Service item belong to
Item Category varchar(32) See Service Item Category
Item Name varchar(127) Name of the service item, e.g. VMail, Monthly Subscription
Item Parameter varchar(127) Parameter specific to the Item, e.g. # minutes for Minute Bucket
Item Count double Count of the service item in ADDITION to the "Default Count" in the corresponding Service Plan.
This can either increase the count of an included items in the Service Plan, or elect an Optional Item to the Service Plan.
Item Unit varchar(31) e.g. month, line
Unit Charge double Charge per unit
Tax double Tax rate for this service item
Item Description varchar(128) General description of the item


Post Rating Schedule

To be used by Post Rating Scheduler to poll the CDR from an NMS, applying rating, and record the charges to the CDR in the NPS.


Name type Descriptions
Account User varchar(127) <user> component of the account.
<Account User>@<Account Domain> together uniquely identify the user in the system.
<Account User>=="*" denotes a post rating for all users in a domain.
<Account User>=="domain" denotes post rating for the "domain" user, and not the whole domain.
Account Domain varchar(127) <domain> component of the account.
Charge To User varchar(127) <user> component of the charge to account.
<Charge To User>@<Charge To Domain> together uniquely identify the user in the system to be charged.
<Charge To User>="*" denotes charge to the corresponding <Account User>
Charge To Domain varchar(127) <domain> component of the account to be charged
Last CDR Time varchar(31) Release Time of last polled CDR
(This is a read-only field)
Next Poll varchar(31) Next CDR Release Time to poll from


  • The output CDR entries written to the NPS's CDR by the Post Rating are marked with cdr_type=="post"
  • The Post Rating Schedule is being scanned every PostRatingScanPeriod, and PostRatingPollSize time interval of CDR are polled by each scan.
    • The ratio PostRatingPollSize/PostRatingScanPeriod determines the amount of time to process a period of CDR. e.g a ratio of 6 will take 1 day to process every 6 day of CDR.
    • Call Per Second (CPS) must be be considered in choosing the PostRatingPollSize to prevent the number of CDR per poll to become too large (i.e. no more then a few thosand). e.g. 10 CPS will result in 3000 CDR per poll if PostRatingPollSize=="5 minute".
    • See NPS System Parameters for more details of these parameters.
  • When adding a new Post Rating Schedule entry, set the Next Poll to the desired CDR Release Time where the specific Post Rating should start polling the CDR from.
    • Next Poll for an existing Post Rating Schedule entry can be modified to skip over a period of CDR, or to re-rate an already rated period.

Service Record

This will be an additional table parallel to the CDR to register all service charges. Any non-usage base charge should be removed from CDR and collected in this Service Record.

Entries Type

  • Recurrent Monthly Service Plan items
  • Adjustment to Account
  • Payment Received with Total and Date Received


Field type Descriptions
Account User varchar(127) <user> component of the account.
<Account User>@<Account Domain> together uniquely identify the user in the system.
<Account User>=="domain" denotes a summary service charge applied to the domain.
<Account User> must not be "*" for this table.
Account Domain varchar(127) <domain> component of the account.
Plan Name varchar(127) Name of the service plan, e.g. Basic VoIP
Item Category varchar(31) See Service Item Category
Item Name varchar(127) Name of the service item, e.g. VMail, Monthly Subscription
Item Count int(10) Count of the service item
Item Unit varchar(31) e.g. month, line
Unit Charge double Charge per unit
Tax double Tax
Total Charge double Total charge
Time Serviced datetime Date and Time that the charges was incurred
Time Recorded datetime Date and Time that the charges was recorded
Account Debited bool Tag whether this charge has already been subtracted from the user's account balance

Service Item Category


Category Descriptions
adj Adjustment to account balance
ctx Tax applied to the Usage Charge before credit from Minute bucket
buk Minute Bucket
nrc Non Recurrent Charge
pmt Payment received
srv Recurrent Service
tax Tax applied to account
utx Tax applied to the amount of Usage Minute.


Account Billing Info

To be used by Bill generator


Name type Descriptions
Account Number varchar(127) Account Number
(Imported from external account system)
Account User varchar(127) <user> component of the account.
<Account User>@<Account Domain> together uniquely identify the user in the system.
<Account User>=="*" denotes a combined bill for all users in a domain.
<Account User>=="domain" denotes a bill for the "domain" user, and not the whole domain.
Account Domain varchar(127) <domain> component of the account.
Company Name varchar(128)
First Name varchar(128)
Last Name varchar(128)
Street Address varchar(128)
State varchar(32)
Zip Code varchar(16)
Country varchar(32)
Phone varchar(32)
Fax varchar(32)
E-Mail varchar(64)
Status enum('active','inactive')
Time Zone varchar(64) e.g. US/Pacific
Last Bill Date varchar(32) Date of last bill.
Billing Period varchar(32) e.g. week, month, quarter
Billing Date varchar(32) Date to generate next bill

Bills

This table lists all the generated bills and their summary.

  • The Billing Period may be different from the Service Plan Period
    • Each bill includes an integral number of Service Period
    • There can be more then one Service Period in each bill.
    • The billed usage window correspond to the billed Service Period(s)
    • Billing period is >= Bill From Date and < Bill Date
  • Credit/Debit from "Usage Record" and "Service Record" are applied to the corresponding account at the instance a Bill entry is inserted.
  • Non-Recurrent Charge will be entries in Service Record with special Category code.
  • This file info is written by BIRT each time it generate a new bill.
  • There will be a periodic auto export mechanism, similar to the NMS's CDR export.
  • 1st Bill if there was no previous bill
    • 1st Bill period start from Date 1st Use for a single account or MIN(Date 1st user) for a domain

Name type Descriptions
Account User varchar(127) <user> component of the account.
<Account User>@<Account Domain> together uniquely identify the user in the system.
Account Domain varchar(127) <domain> component of the account.
Bill From Date yyyy-mm-dd Start date for this billing period
Bill # varchar(31) 10-digit sequence number
Bill Date yyyy-mm-dd Billing date
Billing period is >= Bill From Date and < Bill Date
Creation Date yyyy-mm-dd hh:mm:ss Date that the bill is created
Last Bill Date yyyy-mm-dd Date of last bill.
Last Bill Total double Total of last bill
Total Payment double Total Payment in the Service Record for this billing period
Total Adjustment double Total Adjustment in the Service Record for this billing period
Past Due double Last Bill Total + Total Adjustments - Total Payment Total
Minute Usage double Total number of minute consumed in this billing period
Minute Charge double Total usage charges, e.g. minutes
Service Charge double Total of all items belonging to the account's Service Plan in the Service Record for this billing period.
Non-Recurrent Charge double Total of all items NOT belonging to the account's Service Plan, NOT Payment and NOT Adjustment in the Service Record for this billing period.
Tax double Tax applied
New Charge double Usage Charge + Non-Recurrent Charge + Recurrent Charge + Tax
Total Charge double New Charge + Past Due
Filename varchar(127) Filename of the Bill
File Date yyyy-mm-dd hh:mm:ss Date that the bill file was generated
Status varchar(31) Status of this bill to help keep tracking of the file generation, sending of invoice, and receive of payment.
(enum value TBD)

BIRT Billing

Purpose

  • Auto generate bills using BIRT and updating the bills table.

Process Steps

  • Reads configuration from /etc/netsapiens/billing.conf
  • Looks at the bills table
  • For every row with a empty status field and a Bill_date that is prior to current date.
    • Servlet requests pdf.
    • If PDF generated, saves to \usr\local\Netsapiens\Nps\bills\
    • Saves PDF's using syntax:
      • Domain = DOMAIN_BILLDATE.pdf
      • User = DOMAIN_USER_BILLDATE.pdf
    • Updates Bills table adding in the filename, file_date, and status.
    • If completed properly the status will be "Bill Created" otherwise "Bill Failed"
  • goes back to sleep

Install

  • This is meant to work in partnering with Netsapiens NPS (1-1138x4 or higher)
  • Copy over the two necessary .rpm files. netsapiens-billing-x.x.x-x.i386.rpm and netsapiens-birt-x.x.x-x.i386.rpm. They are on the share under netsapiens/code archive/billing
  • You can install them on separate servers or the same server.
  • Install birt by running #rpm -i netsapiens-birt-x.x.x-x.i386.rpm
  • Test by using firefox and going to http://SERVER:9090/netsapiens-birt
  • Next step is to install the bill generation app. This need to be installed on the main server (until we get file replication working)
  • Install billing by running #rpm -i netsapiens-billing-x.x.x-x.i386.rpm
  • Next we need to update the billing config, /etc/netsapiens/billing.conf
  • Start by coping over the sample file, #cp /etc/netsapiens/billing.conf.sample /etc/netsapiens/billing.conf
  • edit the file, #vi /etc/netsapiens/billing.conf
  • The applications variables are:
    • tac_url - URL of tac-server for the nps. e.g. https://localhost/tac/nps
    • birt_host - Hostname of birt server, server where you installed netsapiens-birt e.g. localhost
    • tac_login - login used by tac-server
    • tac_password - password used by tac-server
    • idle_time_min - minutes the app waits before search for more bills
    • ms_between_bill - milliseconds between each pdf generation. helps avoid system locks
    • window_start - beginning of valid window, so you can force it to only run during off hours, HH:MM:SS
    • window_stop - ending of valid window, so you can force it to only run during off hours, HH:MM:SS
  • Save the fill and then restart billing by typing #touch /usr/local/Netsapiens/tomcat/webapps/billing.war

Monitoring

  • You can view logs of both apps by viewing /usr/local/Netsapiens/tomcat/logs/catalina.out

Appendix: BIRT Tutorial

Please see BIRT Tutorial.

Comments