Page Comparison

{float:right|width=300px|background=lightgrey|border=solid blue 2px|margin=10px|padding=8px}
*Contents*
{toc:all=true|depth=4|excerpt=true|indent=14px}
{float}
*Document Logs*
*Change log:*
|| *Date:* || *Author:* || *Version:* || *Changes:* ||
| 14 March 2006 | Laurits Tygesen, J. Ebbinghaus | 1.0 | Updated screenshots. |
| 23 March 2006 | Torben Jensen, J. Ebbinghaus | 1.1 | Updated Invoice Production and Involved Engines with reference to [Invoicing Process|Customers:Invoicing Process] |
----
h2. Intended Readership

This document is intended for anyone who requires an overview and understanding of the general billing and rating process involved at the MNO.

h2. Overview

The document has the below four sections.

* A description of the overall billing concepts, as well as relevant involved data models. This is the foundation for all billing events.
* A description of the CDR handling procedures and involved dataflows and mediation processes.
* A description of the steps specifically involved in the CDR-based rating process.
* A description of the logic, rules, and relevant database tables involved in the allocation of recurrent fees.

h2. Overall Billing Concepts


Each box in the above figure represents a class/table.

 !BillingObjModel.jpg!
_Figure 1: Billing Object Model_
* Account -- Overall relationship of the structure. An account indicates type (Private/Company) as well as registered sales channel.
* Users -- represent physical persons/entities related to an account. One account can contain multiple users, and will have one associated registered owner (referenced by account.owner_id)
* BilllingGroup -- A BillingGroup represents individual possible groupings of entities which generate invoices. Each billing group can produce invoices containing traffic for each associated member in the invoicing period.
* Subscription -- represents the logical product sold to a consumer -- a subscription is related to a product (Mobile, Fixed line, VoIP etc.). The subscription status_id indicates the lifecycle status of the current subscriptions. Values can be found in table status with type_id=700
* Service -- the physical element of the subscription, such as a fixed line phone or mobile phone. The service will depend on the type of subscription. For Mobile services the most important attributes are phone_number and IMSI.
* SIM card -- the physical element delivered to a subscriber. The SIMcard contains the IMSI/ICC/PIN and PUK codes of a given physical SIMcard. If it is in use, the SIMcard will reference the service through SIMcard.assigned_to_service_id. The status_id of the SIMcard indicates the current status (barred, active, etc) of an active SIMcard. Possible status values can be found in table status where type_id=1. If a customer has had their SIMcard exchanged, multiple SIMcards can reference the same service. The current active SIMcard is indicated where service.imsi = simcard.imsi
* BillingGroupMember -- represents the membership of a subscription in a billing group. Start_date indicates the starting date of the membership, and end_date indicates the final date of membership. If the membership is currently active end_date will be null.
* Invoice -- An invoice header record. One record exists for each invoice (open or closed) that exists for a given billing group.
* Invoice_detail_line -- contains the atomic parts of an invoice. Each line contains the details and price for one billing event and is associated with exactly one invoice. Invoice_detail_lines can be generated by a subscription (a specific call by a specific phone in a group) or by an action outside the subscription (one-time fees, late-charges, etc.).
* InvoicePayment -- contains the registered payments for an invoice. The sum value of payments are aggregated into the table invoice in the column payed_amount
* RatePlan -- references the entry point into the rate plan and the pricing models and indicates which rate plan is active for a given subscription in a given period.

h2. Invoices -- Lifecycle and Relations

For post-paid billing groups, each billing event must be associated with an invoice. A billing event could be a phone call, a sent SMS, receipt of a ringtone, etc.

Invoices have the following lifecycle (referencing the invoice table):

* Open -- indicated by is_open = 1 in the invoice table. In this state, the record represents a running tally and not yet an official invoice. Billing events can be added to this invoice and the invoice totals can therefore be changed. The customer has not yet been presented with any invoice for this object and no dunning activities or due dates exist.
* Closed -- indicated by is_open = 0. In this state, the invoice has become a "real" invoice -- including invoice number and due date. This invoice must be fulfilled (paid/Credited) but the total amount of the invoice can no longer be modified.
* Fulfilled -- indicated by total_excl_vat+total_vat = payed_amount. If the customer has paid all obligations of the invoice, it is in this state. This is a "reversible" state -- that is, an invoice can be fulfilled, but then later, due to reversal of payment, be unfulfilled.

The invoice table contains 3 types of invoices -- indicated by the column invoice_type_id. The type can be:

* 0 -- indicating a Running Invoice. This is the type if invoice where the system can make automated charges, such as rating events, monthly fees, etc.
* 1 -- Manual invoice. This is an invoice produced by a customer service representative. This kind of invoices contains manually added items only. No automated charges can be added to this type of invoice.
* 2 -- Credit Note. A manually generated credit note.

Billing groups have a relation to billing_cycles which indicates how invoices on a given cycle should be moved from status "open" (is_open=1) to status "closed" (is_open=0).

Once the invoices for a billing cycle have been closed they get an associated invoice number assigned as well as an invoice due date.
Invoices will enter the dunning activities according to the dunning process rules implemented. The current invoice dunning status is found in Last_step_id and date_last_step.

Once an invoice is closed all usage of the invoice is the collection of invoice_detail_lines associated with this invoice.
To optimize invoice processing aggregated values of this usage will be computed and stored in the table Invoice_aggregate_lines. This table is maintained by a trigger on the invoice_detail_line table.


h2. Invoice Detail Lines

Invoice_detail_lines are the core elements of all invoices and the results of all billing events. For any item to appear on an invoice, an invoice_detail_line must exist.

The value in this table is divided into total_excl_vat and total_vat -- and the total value of a single line is the sum of the above 2 values.
Invoice_detail_line values are stored in a variable number of decimals (configured in the parameter INVOICE_DETAIL.NUMBER_OF_DECIMALS) -- typically this will be between 2 and 4 decimal places. Use the Parameter Setup GUI in the main Rator menu to configure this (see Parameter Setup for details.

The records in this table also contain columns such as quantity, duration, and charge_date (usage date registered).


The column Description contains the detail line description presented on a detail specification in clear text. The columns level1 through level5 contain different usage aggregation level headings.

The invoice_detail_line references a charge_item (found in the table charge_item) by the column charge_item_id. The charge item contains formal values for VAT pct, GL code reference etc.


h2. Invoice Production

See the document [Invoicing Process|Customers:Invoicing Process] for information about how to close a batch of invoices and for information about the PrintShop file, read "Invoice PrintShop Format".


h2. Involved Engines

* eInvoice Engine
* eInvoiceMail Engine.

h2. Wholesale Object Model

 !Wholesale Object Model.jpg!

_Figure 2: Wholesale Object Model_
* CDR files -- Files retrieved from the MNO's provisioning platform.  The files will be loaded into two table structures by the Wholesale CDR  Loader engine (See document "Wholesale CDR Loader Engine.doc" for  details).
* MNO_CDR_HEADER - contains one row per CDR file loaded. The status  in the MNO CDR header indicates the current state of the CDR file.
* MNO_CDR - contains one row per CDR in the CDR file and is linked to  the MNO_CDR_HEADER to indicate from which file the CDR was originally  loaded.
* ServiceProvider -- Contains one record per registered wholesale customer in the MNO's setup.
* ServiceProvideImsiRange -- contains the list of registered IMSI ranges provided for the given service provider.
* RatePlan -- references the entry point into the rate plan and  pricing models and indicates which rate plan is active for a given  subscription in a given period.
* MNO_sp_file_header maps CDRs after rating them together in a  logical bundle/group which will be send as one "file" to the  service-provider retail rating process.
* MNO_billing_work_rates -- a table which contains mappings of CDR "code" values to traffic types and indicated states.

h2. SP Object Model

 !SPObjModel.jpg!

_Figure_ _3: SP Object Model_
* Billing_source_MNO -- contains one record per Batch of CDRs received  from the wholesale rating process. The billing_source_MNO contains the  reference to the MNO_sp_file_header from the wholesale database.
* Billing_record_MNO -- contains one record per CDR retrieved from the  wholesale database in a given batch. Billing_record_MNO contains  information on the type of traffic as well as basic information such as  its volume/duration, date of call, IMSI, a-number, and b-number. This  table also contains a reference to the original CDR file id from the  MNO's wholesale database (ORIGINAL_ID) which allows tracing a record  back to the original CDR file sent by the MNO.
* Invoice_detail_line -- If a CDR has been rated, the outcome will  generate one or more invoice_detail_lines containing the prices and line  descriptions for each generated billing event.
* Rate_errors -- If a CDR fails during the rating process for any  reason, the value of billing_record_MNO.status 4. In this case,  additional information about the rating issues can be found in the table  rate_errors.

h2. CDR Files -- Location and Loading

Raw CDR files are loaded into the MNO's wholesale database.
During the load process, a record is generated in the table  MNO_CDR_HEADER. The status field in this table indicates the current  state of the file where
* 0=load in process
* 1=load successful
* >10 indicates rating process in progress.

A number greater than 10 indicates the identification number of the  rating engine processing the batch. Status=99 indicates general failures  in the file.
Before loading the file, a check is made to ensure that no previous  copies of this file have been loaded. This is based on filename  recognition.

During the load process it is validated that the CDR file contains  the specific header and trailer records indicating the file is complete  and that control numbers match to identify what has been loaded. If the  file fails any of the checks during the load process it is backed out  assuming an incomplete file download.

Each CDR will be inserted "as is" where each column of the file is  mapped to its equivalent database column (name identity). See document  "MNO Wholesale CDR Loader Engine.doc" for additional information,

h2. Duplicate Check

During the load of each CDR a trigger on the MNO_CDR table will check  if we have received a previous copy of the same CDR in a different CDR  file. The CDR is defined as a duplicate if the combination of msg_id and  rate_dt is already in the table. If a CDR is detected as duplicate, it  will be set to status=98.

Additionally special duplicated CDRs are handled where the CDR is  received from the MNO as "split" CDRs the second instance of the CDR  will in this case be set with status=98.

h2. The Mediation Process

The purpose of the mediation process is to standardize the mapping of CDR traffic type. This is done by setting two values:
* Rating_code, a major traffic category, and
* rating_key which can be used in the price plan setup as an alternative number analysis (as to the b-number).

These values are set on a database insert trigger on the MNO_CDR  table and maps the CDR column element_id into traffic types. Definitions  of element_id can be found in the MNO CDR description documents.

h2. Wholesale to Retail Billing

During the wholesale rating process it is determined which service  provider is responsible for the usages of a single CDR. This causes the  CDR to be mapped to a record in the  mno_sp_file_header.
The lifecycle status of a batch of CDRs is determined by the value in the status column of the MNO_sp_file_header table.
* Status=0 indicates that CDRs can still be attached to this batch.
* Status=1 indicates the batch is "closed" and ready for shipment to the SP.
* Status=2 indicates the batch has been successfully shipped to the SP.

Shipment of batches is currently a database stored procedure that  executes in the MNO_wholesale schema and uses an Oracle DB link to copy  the batch to a different schema/oracle instance.

h2. Involved Engines

DataLoader (Wholesale CDR Loader) -- which handles loading of "raw"  ascii files from the MNO's platform into the wholesale database.

An Oracle stored procedure (running through the oracle scheduled  jobs). The procedure can be found in the package CDR_DELIVERY. One  procedure will exist per SP setup.

h2. Object Model

 !RatingObjModel.jpg!

_Figure 3: Rating Object Model_

Many of the above tables have been described in the previous chapters.

h2. Price Plans and Number Plans

Prices are all setup in general price plans using the structure of  number plans. See the billing administration guide for setup of price  plans and number plans.

In general a price plan contains a list of mappings of rating codes  to number plans. A number plan contains the properties for rating  methods (references to code segments that will perform rating for a  specific type of traffic) as well as indicate how the price-tree in then  number plan should be traversed.

h2. Rating Engines

To be able to setup multiple rating engines, each engine will have  its own identification number. A single CDR file can only be rated by  one rating engine; however, if multiple unrated CDR-files have been  loaded, it is possible to start multiple rating engines. Each rating  engine will "take" a CDR-file by setting the status field on the CDR  file to the identification number of the rating engine.

The process when a rating engine starts is therefore:

select from billing_source where status= <identification of the engine>
If a record is found, the rating engine is restarted and must continue to rate CDRs for the above found file(s).

If No record is found, the engine will perform with the statement:

update billing_souce set status=<identification of the engine>  where status=1 and rownum <2;
This will "tag" a CDR file to a specific rating engine if any files are ready for rating. Followed by the statement:

select from billing_source where status= <identification of the engine>
If there is still no hit, the engine will "sleep" for a time period of typically 60 seconds and then try again to find records.

h2. Wholesale Rating

 !Wholesale Object Model.jpg!

_Figure 4: Wholesale Rating Model_

The purpose of the wholesale rating is to tag the MNO_CDR record with a  wholesale price depending on the recognized service provider owning the  IMSI which had generated the call. In addition to this, the CDRs will be  added into a batch of CDRs associated with the specific SP. Once a CDR  is rated, the MNO_CDR status will be set to 1. If for any reason the CDR  can not be rated, it will be set to status=4 and additional information  for the rating problems can be found in the table rate_errors.

h2. Retail Rating

 !RetailRating.jpg!

_Figure 5: Retail Rating_

Once a CDR batch is received from the wholesale platform, the retail rating engine will locate billing_source_MNO batch.

Each CDR contains the IMSI identification of the SIMcard that made  the original call. The SIMcard is used by a service which belongs to a  subscription. The CDR contains a charge_date which is the date the user  made the call. If the subscription is a member of a specific billing  group on the date of the charge, the membership will provide the  reference to the relevant rate plan. The membership will also provide  the reference to the billing_group which holds the invoice which the  usage must be charged. If the CDR rates successfully the  billing_record_MNO will be set to status=1 and any charges will be added  to the table invoice_detail_line.

h2. Suspense Queue Handling

If a CDR cannot rate for any reason -- like no pricing element found,  the type of usage, or a SIMcard is not found or similar reason - the CDR  will have status=4 and relevant error codes can be found in the  rate_errors tables.

h2. Rating Methods and Mathematics

During the rating plan the actual method for rating is determined  when the specific priceplan element is identified. The purpose of the  rating is to generate records in the invoice_detail_line table.

During the rating process, decimal numbers are used unrounded -- that  is, all mathematical functions are calculated with floating point  data-types representing up to 64 digits. Both ex VAT and VAT values are  calculated.

*Calculation example:*
|| Price per minute | 15 cents including VAT12,93103 cents excluding VAT ||
|| VAT: | 16% ||
|| Number of decimal places: | 4 ||
|| Call duration: | 1 min 45 seconds. ||
|| Price ex VAT 12,93103*105/60 | 22,6293025 ||
|| VAT 16% of 22,6293025 | 3,6206884 ||
|| Price ex VAT charged | 22,6293 ||
|| VAT Charged: | 3,6207 ||
| | Note  that in these examples rounding of values to the number of digits set  up in the system parameter INVOICE_DETAIL.NUMBER_OF_DECIMALS is done  only after the calculations of values excluding and including VAT  (Value-Added Tax) have been made. |
Typically this is between 2 and 4 decimal places.

h2. Engines Involved

RatingEngine_xx where xx is the engine identification number.

RatingEngine_Error -- This is a copy of RatingEngine which handles the  CDRs in the suspense que only. It is functionally identical to the  RatingEngine.

h2. Recurrent Fees

 _Figure 5: Object Model_
Subscription Fees represent the tickets we want to charge either as a  one-time fee or as a recurrent fee. Subscription Fee records are  controlled by events such as a customer service representative  inserting/updating billing_group_member (for monthly fees),  service_option for service related fees etc.

h2. Billing Concepts and Dates

A subscription fee can either be added once or on a recurring basis.  Each added instance of the fee will generate an invoice_Detail_line  where billing_record_id references the subscription fee object.

The subscription fees will only be added to subscriptions which are  either active or have a  termination pending, so fees will never be  added to subscriptions which are either initial (not activated yet) or  terminated.

The addition of fees is based on the following dates in the subscription_fees record:
|| END_DATE_LAST_PERIOD | indicating until what date the fee has been  charged. This value indicates that no charges are missing for this fee  prior to this date and can either mean fees have been charged up to this  date, or that this is the first date for which the fee will be  eligible. ||
|| NEXT_CHARGE_DATE | next time the system will try to add this charge. ||
|| FEE_END_DATE | the end date for this fee. If a fee is currently active this value will be null. ||
|| PERIOD_LENGTH | the number of months for which the fee applies; typically 1. ||
|| PLAN_ELEMENT_ID | the reference to the plan elements which contain the price for this fee. ||

h2. Prepaid vs. Postpaid Fees

A recurrent fee can either appear as prepaid or postpaid. A prepaid  fee indicates that the charges for the fee will be made in advance of  the charge period, and a postpaid fee will be charged in arrears (or the  "end") of the fee period.

*Example:*

If a monthly fee (let us say the basic subscription fee) is defined as _prepaid_, it will be charged on January 28{^}th^ for the period of February meaning the customer will receive an invoice  for the fee BEFORE the period for which the fee is relevant. The  customer pays PRIOR to the actual use of the service.

If a fee is _postpaid_, it will be charged on February 28{^}th^ for the period of February and the invoice sent to the customer will  therefore contain charges in arrears. This means that the customer will  receive an invoice for the monthly basic subscription fee AFTER the  period for which the fee is relevant. The customer pays AFTER to the  actual use of the service.

For prepaid fees, it might be necessary to refund charges to the  customer if the service is discontinued before the payment period has  ended.

h2. Pro-Rating of Fees, Rules and Concepts

When it is determined that a recurrent fee must be charged, the fee  biller has to calculate the number price for the fee. This can either be  a full period -- in which case the total amount of the fee is charged -  or a partial period.


Partial periods can appear if the charge has been applied in the  charging period or has been terminated in the charging period (or a  combination of both)

The prorating rules are:
* If the fee was started in this period, the number of days left to  charge is calculated. The charge is then calculated as  (total_period_charge/30)\* number of days to charge -- that is, each day  is charged with 1/30{^}th^ of the total period value.

* If the fee was terminated inside the billing period, it is  calculated how many days the billing was active, and charges are made as  1/30{^}th^ per active day in the period. For a single period the maximum of 30/30{^}th^ (or 1 full fee) can be charged,

* If the fee was charged as prepaid, it might have been charged for a  longer period than just to the actual fee end date. In this case, the  number of additional dates are counted, and refunded with the value of  1/30{^}th^ of the fee value for each extra charged day -- to the maximum of 1 full fee per 30 day period.

h2. Involved Engines

Subscription FeeBiller.