"Usage Based Billing" has been discussed several times on the list and has been called many different names.
RecurringOrdersQuotes will likely not suffice for many businesses that require this sort of billing.

The following Thread in the forums prompted this:

Original Message
From: Phil Daintree
Sent: Sun 1/23/2005 3:14 PM
Subject: Mobile Phone operator

I am afraid webERP is not really a good fit for this type of operation. I
think you are right each phone call is a line on a sales order effectively
which is billed once a month. Of course calls are dummy items.

A couple of issues for webERP:
1. As things stand, sales orders do not allow multiple entries for the same item. This would need to be resolved.
2. Properties of a phone call would need to be recorded, eg destination, duration etc there are no facilities for this
3. Presumably some other software would be capturing the calls and firing them off to webERP - an interface is required to read in the data from the other application and create the order

Naturally because the software is open-source all things are possible with a
little effort. In fact one of our developers has experience in this field
although it is possible he won't see this forum message - I know he monitors
the mailing lists - the preferred support mechanism. I will forward this to
him privately so he knows someone else in the same field wants similar

If you wish to proceed to make webERP capable for your needs then I would like
to be involved in the spec. There are a number of vertical markets that
require more detail of the sale to be recorded (analagous to a phone call)
eg. freight companies need to know the weight and cubic of a package and its
destination. Hotels require the dates of arrival and departure and name of
the guest etc It would good to make some generic method for recording this
additional data against sales of an item be it a mobile phone call a courier
parcel or an accomodation charge.

Phil Daintree
webERP Project Admin

Original Message
From: Jesse Peterson
Sent: Sunday, January 23, 2005 19:08
Subject: RE: Mobile Phone operator ....

This is 'usage based billing'. He also needs to be able to define a 'service' and then associate taxes and other MRCs with it. A customer would be able to have 1 or more services. For our purposes, it also has to be very flexible b/c typically every type of service (cell phone call, land line call, internet usage, etc.) has different sources for billing data, billing parameters, service provisioning parameters, etc. In addition, typically usage needs 'rate plans' so that the usage as a whole can be handled the same way, but the cost/charge for each call will differ based on various things - call length, destination location, origination location, and more. This is a very complex system to build, though I have done all of the parts of it aside from taxing. However none of that is standardized and is not in anyway in a form that is easily integrated with, say, weberp, nor is it in a form I would want to suggest that others could semi-easily use.

There would will be alot data config/entry screens required, but the bulk of what's necessary here can/should not be handled as a web app.

I have been envisioning making weberp support this, but it will require way more time than I've had recently.

This essentially is a more sturdy, robust version of the RecurringOrdersQuotes you guys implemented. I would have said something further, but as I said, I don't know that I have time soon to do this, so I didn't want to confuse matters.

As a bottom line answer, weberp as it stands would probably not be a good fit without considerable work.

Misc Notes
In the recurring billing systems I've dealt with, a RecurringOrdersQuotes would be a service that expired after X days. For domains and similar services, reporting would be done on any services that expired in Y days so that notices, etc. could be taken care of. Services also could be setup as weekly, bi-weekly, monthly, quarterly, and yearly (etc..) Recurring Charges. Some sort of job - sometimes scheduled, sometimes manually run - would then process and create invoices for services whose billing dates are up.

<Phil>I am quite close on this - I shelved it when Danie started firing development at me to include. I have exactly this form in the CVS - the order is created in the normal fashion then instead of hitting place order there is another option to create recurring order - the frequency of recurrence and the start/end dates can be specified - also there is a flag to auto-invoice. On hitting the Process button the recurring template order is committed to the db. Now I just need to do the script to read this data and create the orders and the invoice. I am wondering whether to make this script to read in an order as generic as possible for other applications that may wish to read in an order from a third party application. Perhaps one script specific to this recurring orders thing to read the template and populate the order cart object and another that takes a cart object and turns it into an order.

I have a potential client or at least a new recruit to the developers team who wishes to have this - if he can afford it. There are essentially two areas to consider before we can do this ....

1. Sales orders need to be able to have the same code entered more than once. This is miserable in that the cart object is geared around the StockID as a unique identifier for the line. This affects every script using this class .... SelectCreditItems SelectOrderItems CreditInvoice and many more of the most miserable scripts in the system

2. Sales order line items need to be able to have additional fields entered. I am thinking that this should be dependent on the stock category of the item and that like items in that category would require the additional fields. The reason for this is to allow details for vertical markets. Most service type businesses need to record very specfic details of the service, couriers need to record the destination, weight, cube of a package, the pickup time, time through the depot, time dropped off, name of the person who received. A mobile phone operator would need to know the destination being called, the duration of the call and the time the call was made. You get the idea.

I am thinking that the LineItems array could itself have an array of properties of the line item and the number of properties and descriptions of those properties be defined against the stock category (or maybe the stock item itself). If an item of the stock category was selected for sale then these details and headings come up for entry as part of the order line. We would need a new table for sales order line details properties and we would also need another table for stockmovesproperties. In this way different classes of product could have completely different sets of properties - or no additional properties at all.

Anyone any thoughts?

Do you rememeber my StockModules?? Some dust would need to be taken off them and a lot of code looked at, but they would be a step in the right direction.
To make people think we are serious, though, something must allow Stock and Services to be differentiated a little better... even if it's not more than a commonly used Type.
This would also lend towards any future OO approaches.
I will chew on this and get back ...

<Phil>Gosh Jesse ... way too tactful! I TOLD YOU SO would have been more appropriate!!! ok ok you were right ... AGAIN! You've got to remember I'm just a humble bean counter and it takes a while to come round.
Still prefer to hang this off stock categories though if possible using the db to store a template of the array fields. It is really just an extension of our cart object $Items->LineItems[$i]->Properties[$j]
Maybe dummy should be called service ....

Woohoo, I was finally tactful! (oops, ruined that...)

One difference I've realized - Services are ordered once and invoiced recurringly (until a cancel or change order occurs, at which time things may or may not change). They are not recurring orders, or at least I have a hard time thinking of them as. Am I understanding the implemented effort correctly?

This can not be by Category, it must be per StockMaster record (btw, if we documented the tables here, those could be nice links to them - scripts too, a decent developer guide ... ).
I could even say that another level of categorization could be helpful - or maybe a completely separate catalog...

'dummy' is a good default - the only way I would get rid of it would be to rename it - say, 'generic'.
Going back to my StockModules, everything would be a generic module by default, and one could also select/write a module to interface with their/some systems as necessary. Hopefully we would have a small set to offer as examples. Again, standard functionality would be represented by the generic module.

In terms of system-wide apis/interfaces, these modules will need to implement:
* edit/update forms (or portions there of)
* functions/methods to 'bring in' their update/insert variables
* description creation (returns a short / long description for use in various logging operations)
* more...

Completely generating everything necessary out of a db seems like an incredibly daunting task. Personally, though highly biased, I'd also rather write a module to accomodate my special stock requirements so it works just right. This should lead to some integration modules with other systems, or just a nice library of modules w/ fairly common requirements.

still, more later...

<Phil>I need more flesh on the stock modules thing - the idea about a simple array of properties per line item maybe too simplistic but acheives what we need I think. You know me its got to be simple.... can you flesh it out for me a bit please Jesse

I've fleshed out a rough spec:

The following scripts all use the cart class:


The cart class has an array of line items that uses the StockID of the line as the array key - to allow duplicate items we need to change this class to have a key which is simply a line counter - perhaps called LineID instead of stockID The class needs to increment this counter each time a line is added. This involves modifications to the class itself and all the above scripts. Quite a singnificant project on its own.

To allow properties of line items we need to extend the cart class with another two variables PropertyNames array amd PropertyValues array.

When a stock item is selected the category of the item is retreived with the description etc and a new table contains the properties that need to be defined for stock categories - this will just be a table of the property names for items of this stock category. The stock categories form will need to be modified to allow entry, editing and deletion of properties for each stock category. Again all the above scripts need to be able to allow entry of these properties and display them. This is now a very serious mission.

The other issue is that aside from simply the entry of these properties they need to be stored in the DB.

Again salesorderitems expects the orderno, stkcode index to be a unique primary key. This won't do, a new field id and autoincrment counter field would be the go. I am thinking that an additional table for salesorderitemsproperites with a field to refer to the salesorderitemlineid and then the propertyname and propertyvalue, this structure needs to be mirrored for the stockmoves happily it has a separate key stkmoveno but a new table for stockmoveproperties is required referencing back to the stkmoveno with a row for each propertyname, propertyvalue pair.

Then we have the display of orders the printing and display of invoices and credit notes.

I must say the more I look at this the scarier it becomes. Normally a bit of analysis calms the nerves but this is quite deep into the system.


I believe the way around the Cart class Line Items key issue is to to make the key be the "StockID" + "ServiceID" - services will have their own UniqID, kind of like serial items, that identifies them - for example, a phone number, ip address, or some other generated ID.

Services will also need their own properties and will need to be configured when setup - that is way more complicated than selling an item. Additionally, services will usually require a status to be maintained separate from the customer account, order, etc. These are necessary to track provisioning, cancellations, etc. that are not instantaneous operations - it could take a few minutes to a few weeks to successfully setup a service. Obviously it can not be billed until it is completely setup and working. Due to the complexity involved, it seems to me it will be a big enough chore to write the code, screens, & reports necessary for a single service - even while taking advantage of OO advantages - that I'm having a hard time getting my head around making something so flexible that it can be configured on the fly and saved in a DB. The good thing is, that once one is working, copying the structure and creating a new 'module' should not be a big deal.

Also, I want to make sure you understand the kind of usage I'm talking about. Right now, my basic Long Distance telephone services are: normal 1+ LD, TollFree numbers, Postpaid Calling/Travel cards, dedicated circuits, VoIP Phones, and VoIP carrier connections . I process 200,000 calls per day on average that have to get billed out to about 10k - 15k customers. My systems to pull in and process usage from my equipment and our carriers typically run for 4 - 5 hours a day - the end result of that is the 'usage' I would want to feed into weberp for billing. We run a couple billing cycles a week that actually create the monthly invoice for these customers. Rating that usage - a rating engine is one thing we would have to provide -is a very involved process. Rates are typically setup by service type. Each phone call, for example, may have several charges associated with it based on origination (where did it come from), termination (where did it go), and special charges (was it a payphone call?). Even the Orig & Term rates may be based on one or more parameters ( the NPA, LATAs (calling regions), OCNs (carrier/provider) ). Finally, taxes are prepared based on Municipality, County, State, and Federal taxing schemes (here in the US, the only source I know for this data is purchasing it from tax software companies, so the interface to take in that data to our Taxing System would likely have to be updated frequently and many would have to exist ). In addition, sometimes usage committments have to be enforced ( going 'over' your plan minutes, for example). Once that's all done, MRCs would be added in, and the Invoice finally created.
From a customer standpoint, it is often necessary to have another level or two possible - more than just out Customers & Branches, though this would not be a complete killer.
From a sales standpoint we definitely would need several levels of sales available to support the levels that services are resold at so that commissions can be calculated (I'm only interested in making weberp have the data available for this, not trying to create a commissions systems b/c that's damn near impossible).

Those numbers and requirements are the things that make me say that alot of the processing required can not be and should not be performed in a simple web application. This should scare you - it's big and nasty. I've wanted to build this and make it available for a couple years - weberp serves as a good core to bolt this onto. It will certainly need an extreme amount of planning to accomodate everything above - I've been thinking about this for a while now. Finally, I have not dealt with other usage based businesses (say electricity, water, etc.), but I believe the requirements brought forward for telecom will accomodate anything they would need.



Well then..... this would appear to be quite a mission and I must say I don't feel qualified to appreciate all the pit falls - it sounds like you've been down a few Jesse, so when you get some time I suspect you'll want to have a go at this. I frightened the new guy off this.... the mixed key sounds like it might be an answer</Phil>
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki