My Oracle Support Banner

Billing Activity Searches For Old Items Which Are Open From Last 4 Months (Doc ID 2277527.1)

Last updated on NOVEMBER 09, 2023

Applies to:

Oracle Communications Billing and Revenue Management - Version and later
Information in this document applies to any platform.


On Oracle Communications Billing and Revenue Management(BRM), versions, while using the 'pin_bill_accts' utility, it is observed that the below SQL Query is fetching the events of last four months instead of the current billing cycle.

SQL query:

select sum ( item_t.due ), sum ( item_t.adjusted ), sum ( item_t.disputed ), sum ( item_t.writeoff ), sum ( item_t.recvd ), sum ( item_t.transfered ) from item_t ,bill_t
where item_t.created_t >= :1 and item_t.created_t <= :2 and ( item_t.status = :3 and item_t.ar_billinfo_obj_ID0 = :4
and item_t.ar_billinfo_obj_id0 <> item_t.billinfo_obj_id0 and bill_t.poid_ID0 = item_t.bill_obj_ID0 and bill_t.end_t > :"SYS_B_0" and bill_t.end_t <= :5 );

Here the issue is with the inputs which are passed to this search query and while bill run, this query is fetching the events of last four billing cycles instead of the current billing cycle.

For example, see sample log snippet below, where this query searches the item records from "Dec 31 00:00:00 2016" to "May 01 23:00:00 2017".


Why this SQL Query searches backdated items or old events also?
How to modify this query to search records for the current billing cycle only(last one month instead of last 4 months)?


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.