Balance Mismatch In Item And Balance Tables (Doc ID 1402468.1)

Last updated on JULY 19, 2012

Applies to:

Oracle Communications Billing and Revenue Management - Version to [Release 7.3.1]
Information in this document applies to any platform.


In a customer's production 731 system with AAA-CM-Timos chain for prepaid rating and CM-DMOracle chain for billing activities; at frequent intervals of time, there were mismatches observed in the amounts reflected in item_t and balance tables.

A balance mismatch query is executed frequently to find the any mismatch between item_t and bal_grp_sub_bal_t as shown below.

select b.account_obj_id0,b.bal_grp_obj_id0,
NVL(b.ItemDue,0) "ACTUAL",
NVL(c.balAmt,0) "BALANCE",
from (select /*+ PARALLEL(x,4,1) */ account_obj_id0, bal_grp_obj_id0
, sum(DUE) ItemDue from item_t x
where CURRENCY = 840 and ITEM_TOTAL<>0 group by
account_obj_id0, bal_grp_obj_id0
) b,
(select sum(current_bal) balAmt, bg.account_obj_id0,(bg.poid_id0) bg_poid
from bal_grp_sub_bals_t bgs ,bal_grp_t bg
where bgs.rec_id2 = 840
and bgs.obj_id0 = bg.poid_id0
group by bg.account_obj_id0,poid_id0) c
where c.account_obj_id0 = b.account_obj_id0 and (b.bal_grp_obj_id0 =
c.bg_poid) and round (NVL(b.ItemDue,0),3)
!= round(NVL(balAmt,0),3);


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