BILLING AND RATING PROCESS

Document Logs
Change log:

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.

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.

Overall Billing Concepts

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

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.

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.

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.

Invoice Production

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

Involved Engines

• eInvoice Engine
• eInvoiceMail Engine.

CDR Processing

Wholesale Object Model

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.

SP Object Model

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.

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,

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.

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.

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.

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.

Rating

Object Model

Figure 3: Rating Object Model

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

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.

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.

Wholesale Rating

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.

Retail Rating

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.

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.

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:

Typically this is between 2 and 4 decimal places.

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.

Recurrent Fees

Figure 6: 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.

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:

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.

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.

Involved Engines

Subscription FeeBiller.