First, let’s take a look at an overall picture of what the Oracle BRM billing process does:
- Compiles the total amount of balance impacts that have occurred in the past month. This can include usage fees and subscription fees.
- 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.
- Automatically requests payments from a credit card processor or requests payments by sending invoices.
- Automatically updates a customer’s account balance when a payment is recorded in the BRM database.
So, how is all this done in practice?
Before running a brm 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 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 brm 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 have finished, in order to verify the accuracy of the data generated by this process. We execute a 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.
Hi,
May I know, which platform are you using? What’s the sizing of the hardware to support this implementation?
Regards.
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
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 …….
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
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
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.
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
Thanks Ales, appreciate your suggestion.
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?
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.
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
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?
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.
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 ???
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.
The billing and invoicing are always run in single mode and hence the per_batch value is of no importance.
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
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
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
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
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
How do you tackle Performance challenges in Oracle BRM?
Patrick
Hi,
By executing the following Oracle BRM MTA billing utilities, getting the following errors :
[pin_billd]$ pin_bill_accts -v
pin_bill_accts: symbol lookup error: pin_bill_accts: undefined symbol:
pcm_set_multithread
[pin_billd]$ pin_collect
pin_collect: symbol lookup error: pin_collect: undefined symbol:
pcm_set_multithread
[ pin_billd]$ pin_inv_accts
pin_inv_accts: symbol lookup error: pin_inv_accts: undefined symbol:
pcm_set_multithread
Please help
It’s been some time since I did my last post. Let me share some SQL queries, which might help any Oracle BRM operator. Due to blog security restriction you have to replace SLT to select.
— Is there any account having items and not having bills?;
SLT a.poid_id0,first_name,last_name,a.status
from account_t a,account_nameinfo_t an
where an.obj_id0=a.poid_id0
and not exists (SLT 1 from bill_t b
where bill_no is not null
and end_t=
and b.account_obj_id0=a.poid_id0)
and exists (SLT 1 from item_t i
where a.poid_id0=i.account_obj_id0
and effective_t=
–and item_no is not null
and item_total!=0.00)
and a.created_t<
— Expected: No rows found
— One more way;
SLT a.poid_id0,first_name,last_name,a.status
from account_t a,account_nameinfo_t an
where an.obj_id0=a.poid_id0
and not exists (SLT 1 from bill_t b
where bill_no is not null
and end_t=
and b.account_obj_id0=a.poid_id0)
and exists (SLT 1 from item_t i,billinfo_t bi
where a.poid_id0=i.account_obj_id0
and i.account_obj_id0=bi.account_obj_id0
and i.bill_obj_id0=bi.last_bill_obj_id0
and item_total!=0.00)
and a.created_t<
— Expected: No rows found
— Is there any difference between item and bill?) b,
SLT poid_id0 bill,current_total bill_total,item_total
from (SLT poid_id0,current_total from bill_t
where end_t=
(SLT bill_obj_id0,sum(round(item_total,2)) item_total
from item_t
group by bill_obj_id0) i
where i.bill_obj_id0=b.poid_id0
and abs(current_total-item_total)>0.01;
— Expected: No rows found
I am adding a few SQL queries to verify Oracle BRM billing and invoicing:
— Is there any not billed item?) or status=1 or ar_bill_obj_id0=0 or ar_billinfo_obj_id0=0)
SLTdistinct account_obj_id0,bill_obj_id0 from item_t i
where exists
(SLT1 from bill_t b
where b.poid_id0=i.bill_obj_id0
and end_t=
and (effective_t!=
;
— IS there any not billed account?
SLTpoid_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<
and not exists (SLTb.account_obj_id0 from bill_t b
where end_t=
and b.account_obj_id0=a.poid_id0);
–Expected result: No rows found
— Other way);
SLT* from billinfo_t bi
where billing_state=0
and last_bill_t=
and not exists
(SLT1 from bill_t b where b.account_obj_id0=bi.account_obj_id0
and bill_no is not null and end_t=
–Expected results: No rows found
— Is there any account with two bills
SLTcount(*),account_obj_id0
from bill_t
where end_t=
group by account_obj_id0
having count(*)>1;
–Expected results: No rows found
— Is there any bill without bill no?
SLT* from bill_t
where end_t=
and bill_no is null;
— Expected: No rows found
Analysis of ldd libcmpin.so,libportal.so,libportal64.so and libpcmcpp67.so shown that the root cause of this error is related to ( path settings)
libportal.so => /brmdata/opt/ifw/lib/libportal.so (0xf76c7000)
Please check the variable $LD_LIBRARY_PATH , by default it is pointing to IFW.
To fix the errors please run:
source $PIN_HOME/source.me.csh
and continue the operations.
Your Oracle BRM utilities should be fine now.
I am using attached queries as well to review my Oracle BRM billing and invoicing.
— Is there any bill with duplicate bill no?
SLT count(*),bill_no from invoice_t
group by bill_no
having count(*)>1;
— Expected: No rows found
— Billing failed?
SLT * from billinfo_t
where billing_state = 4
— and bill_info_id<>‘Bill Unit(1)’;
–Expected results: No rows found
— Bills have proper billinfo?
SLT b.poid_id0,b.account_obj_id0,current_total,due,total_due,subords_total,parent_id0,bp.poid_id0
from bill_t b,billinfo_t bi
where b.account_obj_id0=bi.account_obj_id0
and end_t=
and (b.billinfo_obj_id0<>bi.poid_id0 or b.ar_billinfo_obj_id0<>bi.ar_billinfo_obj_id0);
— Expected: No rows found
— Bills for children have proper parent bill?
SLT b.poid_id0,b.account_obj_id0,b.current_total,b.due,b.total_due,b.subords_total,b.parent_id0
from bill_t b,billinfo_t bi,billinfo_t bip,bill_t bp
where b.account_obj_id0=bi.account_obj_id0
and bi.poid_id0!=bi.ar_billinfo_obj_id0
and bi.ar_billinfo_obj_id0=bip.poid_id0
and bip.account_obj_id0=bp.account_obj_id0
and b.end_t=
and bp.end_t=
and b.parent_id0<>bp.poid_id0;
— Expected: No rows found
— Bills for chilren are correct?
SLT b.poid_id0,b.account_obj_id0,current_total,due,total_due,subords_total,parent_id0
from bill_t b,billinfo_t bi
where b.account_obj_id0=bi.account_obj_id0
and bi.poid_id0!=bi.ar_billinfo_obj_id0
and end_t=
and (current_total<>due or subords_total<>0 or parent_id0=0);
— Expected: No rows found
— Bills for real parents are correct?
SLT b.poid_id0,b.account_obj_id0,current_total,due,total_due,subords_total,parent_id0,ar_hierarchy_size
from bill_t b,billinfo_t bi
where b.account_obj_id0=bi.account_obj_id0
and bi.poid_id0=bi.ar_billinfo_obj_id0
and end_t=
and subords_total<>0
and (current_total<>0 or parent_id0<>0 or subords_total<>due or ar_hierarchy_size=0);
Expected: No rows found
— Sum of bills for chilren and parents is equal?
SLT poid_id0,subords_total,children.current_total
from bill_t b,
(SLT parent_id0,sum(current_total) current_total
from bill_t where parent_id0<>0
group by parent_id0) children
where b.poid_id0=children.parent_id0
and end_t=
and children.current_total<>subords_total;
— Expected: No rows found
— Billinfo last_bill_t is correctly set? –01.01 ;
SLT distinct a.poid_id0,first_name,last_name,a.status,pin2date(last_bill_t)
from billinfo_t bi,account_t a, account_nameinfo_t an
where account_obj_id0=a.poid_id0
and an.obj_id0=a.poid_id0
and bi.last_bill_t!=
and billing_status in (0,4)
and a.created_t<
— Expected: No rows found
— Billinfo next_bill_t is correctly set?;
SLT distinct a.poid_id0,first_name,last_name,a.status,pin2date(last_bill_t)
from billinfo_t bi,account_t a, account_nameinfo_t an
where account_obj_id0=a.poid_id0
and an.obj_id0=a.poid_id0
and bi.next_bill_t!= 1201820400 –01.02
and billing_status in (0,4)
and a.created_t<
— Expected: No rows found
— Billinfo future_bill_t is correctly set?;
SLT distinct a.poid_id0,first_name,last_name,a.status,pin2date(last_bill_t)
from billinfo_t bi,account_t a, account_nameinfo_t an
where account_obj_id0=a.poid_id0
and an.obj_id0=a.poid_id0
and bi.future_bill_t!=1204326000 –01.03
and billing_status in (0,4)
and a.created_t<
— Expected: No rows found
— Billinfo last_bill_obj_id0 is generated?;
SLT a.poid_id0,first_name,last_name,a.status
from billinfo_t bi,account_t a,account_nameinfo_t an
where account_obj_id0=a.poid_id0
and an.obj_id0=a.poid_id0
and not exists
(SLT 1 from bill_t b
where b.poid_id0=last_bill_obj_id0
and b.account_obj_id0=bi.account_obj_id0
and last_bill_t=end_t)
and billing_status in (0,4)
and a.created_t<
— Expected: No rows found
— Billinfo next_bill_obj_id0 is generated?;
SLT a.poid_id0,first_name,last_name,a.status
from billinfo_t bi,account_t a, account_nameinfo_t an
where account_obj_id0=a.poid_id0
and an.obj_id0=a.poid_id0
and not exists
(SLT 1 from bill_t b
where b.poid_id0=bill_obj_id0
and b.account_obj_id0=bi.account_obj_id0
and bi.last_bill_t=b.start_t)
and billing_status in (0,4)
and a.created_t<
— Expected: No rows found
— Billinfo future_bill_obj_id0 is generated?;
SLT a.poid_id0,first_name,last_name,a.status
from billinfo_t bi,account_t a, account_nameinfo_t an
where account_obj_id0=a.poid_id0
and an.obj_id0=a.poid_id0
and not exists
(SLT 1 from bill_t b
where b.poid_id0=next_bill_obj_id0
and b.account_obj_id0=bi.account_obj_id0
and bi.last_bill_t=b.start_t)
and billing_status in (0,4)
and a.created_t<
— Expected: No rows found
OK!!
— Is there any bill for which invoice is not created?
SLT b.poid_id0,b.account_obj_id0,bill_no,due
from bill_t b,billinfo_t bi
where b.account_obj_id0=bi.account_obj_id0
and b.end_t=
and pay_type in (10001,10007)
and b.billinfo_obj_id0=b.ar_billinfo_obj_id0
and not exists
(SLT 1 from invoice_t i
where i.bill_obj_id0 = b.poid_id0);
Expected: No rows found
— Is there any invoice that doesn’t correspond to bill
SLT * from bill_t b,invoice_t i
where b.poid_id0=i.bill_obj_id0
and (b.bill_no<>i.bill_no or b.end_t<>i.bill_date_t);
— Expected: No rows found
— Is there any invoice with duplicate bill no?
SLT count(*),bill_no from invoice_t
group by bill_no
having count(*)>1;
— Expected: No rows found
Best regards,
Ales