Mismatch In "bill_t.total_due" And "sum (item_t.due)" If REL And BILL_NOW Are Run Concurrently (Doc ID 732735.1)

Last updated on SEPTEMBER 24, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version to [Release 7.2.1]
Information in this document applies to any platform.
Checked for relevance on 15-Nov-2013.


In a customer's production system, discrepancies have been seen during a situation when REL and BILL_NOW were run at the same time for a given account. In other words, soon after the two processes completed, the value of "bill_t.total_due" was found to be less than the sum of the individual item_t.due values for that bill.

Now for a moment, let us see the curent behavior of the product :
In a situation when two such utilities (REL and BILL_NOW) are run against the same account, we already have locking mechanism in place such that when one of the application processing ends (say REL, that started before BILL_NOW), the locks on the DB Objects that the application was holding gets released. In this case, REL obtains locks on account_t table. It then processes the rated CDRs and performs all the necessary balance updates. Once the rated CDR loading is complete, the lock on the account_t table is released. That means, while processsing the rated CDR through REL, if a BILL_NOW application is run, it will wait till the REL completes processing the CDR and the lock released. So once that is done, BILL_NOW will obtain the lock on account_t and executes the necessary logic.

With the above logic in place, one would not expect a discrepancy as observed in above symptom.

Scenario :
1)  Create an account with a Standard GSM Telephony plan on 10/01
2)  Move the PVT to 10/15
3)  Load a CDR file with some 10000 records for the account
4)  Execute pin_rel to load the rated CDR files and at the same time, execute bill_now
5)  Compare the amounts of item, due of bill and amount in event bal impacts for the Bill_NOW and we could see a mismatch here

Below is a further refined scenario that can be used to simulate the same issue in a controlled manner if the above scenario does not show up the issue :
1)  01/01/2010 - Create an account with Standard GSM Telephony plan
2)  01/05/2010 - Pass a CDR file having 10000 CDR records
3)  Set the loglevel of pin_rel to 3. Now run "tail -f rel.pinlog" and execute pin_rel to load the above rated file.
4)  At the same time, run bill_now from Customer Center as soon as you see the message "Starting the update stored procedure..." in rel.pinlog and prior to the message "The preupdate stored procedure completed successfully."
5)  Now check for the sum of amount in "item_t" against the bill generated and total amount in "event_bal_impacts_t" against the same bill using the below queries :

select poid_id0, total_due from bill_t where bill_no = 'B1-7'; -- this is an example bill number

select sum(item_t.due), sum(item_t.ITEM_TOTAL) from item_t where ar_bill_obj_id0 = 123456;

select sum(amount), sum(amount_deferred) from event_bal_impacts_t where item_obj_id0 in (select poid_id0 from item_t where ar_bill_obj_id0 = 123456);

Notice that Total Due from bill_t doesn't match SUM(AMOUNT) of item_t and SUM(ITEM_T.DUE) of bill_t of the respective "item_obj_id0".


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms