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

Last updated on JUNE 25, 2017

Applies to:

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

Goal

On Oracle Communications Billing and Revenue Management(BRM), 7.5.0.16.0 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".

Question:

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)?

Solution

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