02 Jun 2010

Oracle BRM billing process

Oracle BRM 21 Comments

First, let’s take a look at an overall picture of what the Oracle BRM billing process does:

  1. Compiles the total amount of balance impacts that have occurred in the past month. This can include usage fees and subscription fees.
  2. Changes the status of all the bill items associated with the bill from pending to open so that they stop accumulating charges, and payments can be applied to them. In addition, a payment due date is added to the bill.
  3. Automatically requests payments from a credit card processor or requests payments by sending invoices.
  4. Automatically updates a customer’s account balance when a payment is recorded in the BRM database.

Picture 1: Oracle BRM billing process

So, how is all this done in practice?

Before running a billing on production, it is necessary that all the prerequisite steps be executed. The first step is to prepare a copy of the production environment – this can either be a preproduction or some testing environment. This step includes the installation of the latest production release onto this environment and the copying of the production database. Next, the environment configurations have to reflect those of production. Here are the main configurations, which should be set appropriately:

cm configuration ($HOME/pin/sys/cm/pin.conf),

o Verify loglevel entry is set to 1

o Change the entry for agent_return parameter to 0

o Change the entry for simulate_agent parameter to 1

These two last changes are done so as not to involve any provisioning action during the bill-run.

dm_oracle configuration ($HOME/pin/sys/dm_oracle/pin.conf),

o Set dm_bigsize parameter to 8388608 or higher

o Set dm_shmsize parameter to 33554432 or higher

o Set dm_n_fe to 8

o Set dm_max_per_fe to 16

o Set dm_n_be to 24

o Set dm_trans_be_max to 22

pin_bill_accts configuration ($HOME/pin/apps/pin_billd/pin.conf),

o Verify loglevel. Change it to the appropriate value depending on what needs to be checked during the bill test, either 1 or 3.

o Change children parameter for pin_billd and pin_mta to 5

o Change per_batch parameter for pin_billd and pin_mta to 20000

o Change fetch_size parameter for pin_billd and pin_mta to 150000

pin_inv_accts configuration ($HOME/pin/apps/pin_inv/pin.conf),

o Verify loglevel. Change it to the appropriate value depending on what needs to be checked during the bill test, either 1 or 3.

o Change children parameter for pin_billd and pin_mta to 5

o Change per_batch parameter for pin_billd and pin_mta to 2000

o Change fetch_size parameter for pin_billd and pin_mta to 15000

To get the best possible simulation of production, the billrun pin_virtual_time has to be set to the date when the real billrun on production will be executed. Once that’s done, the billrun can be started.

We run billing monthly with pin_bill_day script which creates about 100.000 bills per hour. The script creates bills for accounts where the billing date is any day before midnight of the day we run billing. So what does pin_billd_day script actually do? It runs the following billing utilities:

  • pin_deferred_act: Executes deferred actions; for example if an account should become inactive, this utility performs the status change on the scheduled date.
  • pin_bill_accts: Calculates the balance due for accounts and creates a bill for the balance due.
  • pin_collect: Collects the balance due for accounts that use credit cards and directs debit payment methods.
  • pin_refund: Finds accounts that have refund items and makes online refund transactions.
  • pin_inv_accts: Creates an invoice for each account that is billed.
  • pin_cycle_fees: Applies the cycle forward fee balance impact to the customer’s account and cancels products that have an expired pending cancellation date.

To verify the progress and performance of the pin_bill_day script, we run queries against the database to get information on how many bills are done, how many are yet to be done, if there are any bills with errors, etc.

After the billing part is over and all the bills are created, invoicing starts. After the invoices are created, they are exported to XML documents, which are then converted into PDF format.

Besides the queries mentioned earlier, there are also other queries that need to be executed once billing and invoicing has finished, in order to verify the accuracy of the data generated by this process. We execute batch of queries; here are some of them:

Has billing failed?

select * from billinfo_t where billing_state = 4 and bill_info_id<>’Bill Unit(1)’;

–Expected results: No rows found

Are there any unbilled accounts?

select poid_id0, first_name,last_name,a.status from account_t a, account_nameinfo_t an where

a.poid_id0=an.obj_id0 and

created_t<<bill_cycle_end_date> and

not exists (select b.account_obj_id0 from bill_t b

where end_t=<bill_cycle_end_date> and

b.account_obj_id0=a.poid_id0);

–Expected results: No rows found

Are there any bills without a bill number?

select * from bill_t where end_t=<bill_cycle_end_date> and bill_no is null;

— Expected: No rows found

Any problems found are then investigated and fixed through the versioning control system, where fixes are later included in the next release version.

21 Responses to “Oracle BRM billing process”

  1. Ayazul says:

    Hi,

    May I know, which platform are you using? What’s the sizing of the hardware to support this implementation?

    Regards.

  2. admin says:

    Hi Ayazul,

    Our client uses two HP-UX Intanium servers.

    BRM servers:
    • 2 x HP Integrity RX6600
    • 4 x Intel Itanium Dual Core 1.8 GHz
    • 64 GB memory
    • HP UX B.11.23 64 bit
    • multiple Gbit ETH and 4Gb FC

    BRM DB servers:
    • 2 x IBM x36502
    • 2x Intel Xeon Dual Core 3.16 GHz
    • 48 GB memory; Red Hat Enterprise Linux 4 (Nahant Update 8)
    • 4 TB disk storage allocated
    • multiple Gbit ETH and 4Gb FC
    • Oracle 10.2 EE RAC database
    Automatic Storage Management used for BRM in Active / Standby mode
    Secondary BRM standby db RAC node used for mediation, reporting and other purposes

    Storage:
    • 1 x HP XP 24000
    • dedicated disks for BRM database ASM managed
    • 64 GB cache4 Gbit FC bandwidth for BRM database

    Best regards,
    Ales

  3. Abirami says:

    can u pls clarify my doubts in oracle brm , after billing in same account some plans got incative and some not , y its happening if any flag or some other field data missed ,, if then let me know the class and field type …….. we r using pin_bill_day for billing …….

  4. Ales says:

    Hi, Abirami

    There can be many different reasons why some deals, plans or products get inactive after billing.

    Billing scripts run one or more billing utilities, pin_bill_day runs the following billing utilities:
    * pin_deferred_act
    * pin_bill_accts
    * pin_collect
    * pin_refund
    * pin_inv_accts
    * pin_deposit
    * pin_cycle_fees

    You can customize pin_bill_day to specify which billing utilities to run and set the parameters for each billing utility to specify how to run them.

    By default, pin_deferred_act utility is included in the pin_bill_day script and is used to execute deferred actions. For example, if a CSR has scheduled an account to become inactive, the pin_deferred_act utility performs the status change on the scheduled date. This should be the most common reason why some plan become inactive after you do billing.

    The second reason could be running pin_cycle_fees utility, that is also included in pin_bill_day script. pin_cycle_fees utility cancel products that have an expired pending cancellation date. For example, if a product is set to cancel at a future date, the pin_cycle_fees utility cancels the product.

    As I mentioned you can set the parameters for each billing utility to specify how to run them and what actions they will perform. I suggest that you first pay attention on dates that specify when some plan should be cancel, cycle_end_t (cycle end time) and purchase_end_t (purchase end time) are set when you activate some plan, and they trigger status change (in your case to inactive) when you run billing.

    I hope my answer will be useful to you, for more detail explanation I would need more specific information about accounts (plans) that become inactive and about billing utilities configuration.

    Best Regards, Ales Cvetic

  5. sayan misra says:

    hi,

    I have an issue here . In my databse there are three types of Products- cycle forward, one-off,cycle arrear . Cycle arrear products are just included in product list . However, when I am running pin_bill_day on 1st of the next month two duplicate events are getting generated in the system . As a result, two same lines are reflecting in the invoice which is generating invoice with double amount of what it should be . After initial analysis I found that due to two executables of pin_bill_day duplicate lines are coming for cycle arrear products . One is pin_bill_accts and another is pin_cycle_fees .

    So, can you please let me know any way so that I can filter cycle arrear product fee calculation on pin_cycle_fees executed by pin_bill_day of 1st of the next month . Eager to know from you or let me know if further clarification is needed .

    Regards,
    Sayan

  6. Bharathi says:

    Hi,

    I am new to Oracle BRM and would like to learn it. could you please help me as to where to start with. Are there any documents available online for the same.

  7. admin says:

    Hi,

    You can try searching around the internet. You should find some YouTube videos; moreover, you should find some Oracle BRM documents. As an Oracle Gold partner, we have Oracle Communications Billing and Revenue Management ( abbreviations Oracle BRM, OBRM, OCBRM) books. I suggest that you start with Oracle BRM Essentials, and then continue, based on your needs (OCBRM Development, OCBRM Pipeline development, OCBRM Pricing, OBRM administration, etc.).

    Regards,
    Ales

  8. Bharathi says:

    Thanks Ales, appreciate your suggestion.

  9. parveen says:

    I have one issue about “lack of forward cycle” (most error “bad parse of proto / hostname / port” in the logs), we found again the presence of several cases of “lack of cycle forward.”
    The error “” bad parse of proto / hostname / port “no longer detected.

    why it was happened ?
    can you please give the solution on that issue?

  10. Ayazul says:

    Hi Guys,

    I have a question. This is about delayed billing. What’s the impact of enabling delayed billing?

    Currently we are facing issues with bill run in which it has decreased the performance significantly since we have to run billing twice, partial billing and finalize billing. As of now, it is taking > 2 days to complete to complete the first stage, and issues traced at the database level is TX – row lock contention on bal_grp_t by this statement “select poid_DB, poid_ID0, poid_TYPE, poid_REV from bal_grp_t where bal_grp_t.billinfo_obj_ID0 = :1 order by bal_grp_t.poid_id0 for update of bal_grp_t.poid_id0” and also db file sequential read.

    Maybe you can provide an advice related to this issue.

    Regards.

  11. Ognjen Antonic says:

    Hi Ayazul!

    Delayed billing basically enables you to bill events which are older than the bill cycle for which you are creating the bill. You use it, if you are receiving events, which you need to bill, but were made during billing cycle for which billrun was allready done. For example mobile operators providing international roaming may receive events even up to 30 days after event/call was completed so they need to bill also “old” events.

    Regarding your billing preformance issue and mentioned SQL statement, this TX – row lock contention on bal_grp_t is just the symptom, not the cause of the problem. SQL statement above is being used in BRM as a main mechanism of locking inside a transaction. BRM by default does not lock all the objects being modified inside a transaction. Instead it just locks the balance group of the account for which objects are being modified. For example during billrun operation, or during operation of charging an event.
    This row lock contention just shows you, that operation/transaction on the account was not yet finished, yet another operation/transaction allready begun and is requesting the lock on the same object and is of course waiting for the first one to do its job and finish its transaction by either doing a commit or rollback.

    To diagnose slow SQL operations during billrun, just use Oracle Database Enterprise Manager and go to Preformance, Top Activity section and observe top SQL statements running at that time. Watch for those which consume a lot of I/O or excessive CPU, they can be candidates for tuning. Sometimes just database statistics used by SQL Optimizer went stale and optimizer chose suboptimal plan. Sometimes you need to build a new index or maybe change the SQL statement if you are making some custom non out of the box billing procedures.

    As your mileage may vary, it is difficult to diagnose your preformance problems just via this blog. Sometimes root problems lie deeper, like in bad architecture design, custom code, badly tuned database server (for example not enough disk spindles for the database as in modern time IT sales people just like to sell terabytes and forget IOPS).

    Best regards,

    Ognjen Antonic

  12. venkatesh says:

    Hi,
    what difference between the functionality of Pin_billd and Pin_bill_accts.?what are the input parameters for them?

    need to know difference between balance_group and item and event?

  13. shami says:

    Hi,

    The current balance present “bal_sub_bals_t” table is not reflecting in Customer center Balance tab. What could be the problem? and how to resolve this issue. Please help.

  14. Varun says:

    i have customer who cancelled there subscriptions and i am suppose to give them refund but there credit card is expired so i am not able to process that and it is coming in my discrepancy every month,so what should i do to resolve such issue except saying to customer to update the credit card ???

  15. Raj Kris says:

    Hi: In one of the earlier posts (Oct 28,2011 by admin), there is a reference to Oracle BRM essentials Book for Oracle Gold Partners. Could you please provide me more info on how to get access to these books? Are these books sold by you or Oracle? Thanks, in advance.

  16. Rana says:

    The billing and invoicing are always run in single mode and hence the per_batch value is of no importance.

  17. nihar says:

    Hi,

    I want to know if there are any restrictions on how many bill s can be generated on an account in BRM.

    If yes can you please let us know where we can check the same.

    Regards

  18. Aleš Pristovnik says:

    Basically there are no restrictions and you can have many bills per account. To accomplish this you need to create more balance groups and each assign to newly created bill unit (/billinfo)

    To know more please read at http://ow.ly/TKGc308tB9F

  19. Igor says:

    Hi Rana,

    I disagree that billing and invoicing are always run in single mode. Per_batch atribute
    specifies the number of objects processed by each worker thread in a batch mode.

    To run in more threads you need to set children attribute to 2 or more.
    Children attribute specifies the number of worker threads spawned to perform the specified work. The default is 5.

    The example shows the configuration with 10 threads.
    – pin_mta children 10
    – pin_mta per_batch 600
    – pin_mta per_step 1500
    – pin_mta fetch_size 6000

    Thanks,
    Igor

  20. Karl says:

    Hello guys,

    How do you verify billing and invoicing?
    How do you verify that all the bills are created?
    How do you verify that all the invoices are created?

    Thanks,
    Karl

  21. John says:

    Besides the queries to verify the progress, performance and completion of pin_bill_accts and pin_inv_accts utilities run, there are a set of queries that can be executed once billing and invoicing has finished to verify the accuracy of data generated by these processes. The queries are the following:

    — Is there any difference between event and item?
    select i.account_obj_id0,i.poid_id0,i.name,item_total,sum(amount),sum(amount)-item_total
    from event_bal_impacts_t eb,item_t i
    where eb.item_obj_id0=i.poid_id0
    and i.bill_obj_id0 in (select poid_id0 from bill_t where start_t=)
    and eb.resource_id=978
    group by i.account_obj_id0,i.poid_id0,name,item_total
    having sum(amount)-item_total0
    order by sum(amount)-item_total desc;
    –Expected: No rows found

    John

Leave a Reply