My Oracle Support Banner

Accounts with other DOMs are Processed By Billing Process Even If Control File Using Specific DOM is Used (Doc ID 2224333.1)

Last updated on DECEMBER 14, 2017

Applies to:

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

Goal

On :  7.4.0.14.0 version, Billing

Issue:

Even if control file using specific DOM, other DOMs are processed by billing process. For example, if on the day Nov16 the pin_bill_accts is executed using the DOMs 16 29, the accounts with DOM=16 and DOM=29 are charged and billed but the accounts with DOM=30 are also billed and incorrectly, because the DOM 30 was not informed when the pin_bill_accts was called.

Initial Analysis:

Inside the script's logic, it loops for the specific date parameter that was used (in this case, 16 and 29), and it finds the control file that corresponds to the DOM. This file is then put in a temporary 'array'. 

After that, it will execute the command for each file that was put in the temporary 'array':

pin_bill_accts -file PinBillRunControl_DOM_16.xml -verbose

pin_bill_accts -file PinBillRunControl_DOM_29.xml -verbose

In the execution of the file for DOM 29, there are some accounts that has DOM equal to 30, which is not expected to be billed or generate charges.

Further analysis:

It is noticed this issue occurs when DOMs are accumulated to process and one tries to process only one DOM that is not the last one, only to generate the bills (as the delayed billing setting is used here, the accounts was already charged).

So, in order to generate the bill for the DOMs 03 and 04 when tried to run the billing process only for the DOM 03, through a pin_bill_accts xml control file corresponding to this DOM, bills were produced for accounts with  DOM04 and DOM03 instead of only DOM03 as expected.

Found that the flist below, used by pin_billaccts to query the accounts to be processed:

0 PIN_FLD_POID POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_FLAGS INT [0] 0
0 PIN_FLD_RESULTS_LIMIT INT [0] 900000
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1 PIN_FLD_ACTG_NEXT_T TSTAMP [0] (1482285600) Wed Dec 21 00:00:00 2016
0 PIN_FLD_ARGS ARRAY [2] allocated 20, used 1
1 PIN_FLD_PAY_TYPE ENUM [0] 10007
0 PIN_FLD_ARGS ARRAY [3] allocated 20, used 1
1 PIN_FLD_BILLING_STATUS ENUM [0] 0
0 PIN_FLD_ARGS ARRAY [4] allocated 20, used 1
1 PIN_FLD_ACCOUNT_OBJ POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [5] allocated 20, used 1
1 PIN_FLD_POID POID [0] NULL poid pointer
0 PIN_FLD_ARGS ARRAY [6] allocated 20, used 1
1 PIN_FLD_ATTRIBUTE INT [0] 1
0 PIN_FLD_ARGS ARRAY [7] allocated 20, used 1
1 PIN_FLD_ACTG_CYCLE_DOM INT [0] 3
0 PIN_FLD_SEARCH_PCM_FLAGS INT [0] 8388608
0 PIN_FLD_TEMPLATE STR [0] "select X from /billinfo 1, /account 2 where 1.F1 <= V1 and 1.F2 != V2 and 1.F3 = V3 and 1.F4 = 2.F5 and 2.F6 != V6 and ( F7 = V7 or ( inf2_localtz_days(ACTG_NEXT_T) in (3)) )"
0 PIN_FLD_RESULTS ARRAY [0] allocated 20, used 2
1 PIN_FLD_POID POID [0] NULL poid pointer
1 PIN_FLD_ACCOUNT_OBJ POID [0] NULL poid pointer

Translating this to database query:

select * from pin01.billinfo_t b ,pin01.account_t a
where b.ACTG_NEXT_T <= 1482285600
 and b.PAY_TYPE != 10007
 and b.BILLING_STATUS = 0
 and b.ACCOUNT_OBJ_ID0 = a.POID_id0
 and a.ATTRIBUTE != 1
 and ( ACTG_CYCLE_DOM = 3 or ( pin01.inf2_localtz_days(ACTG_NEXT_T) in (3)) )


It appears that the issue occurs on function "pin01.inf2_localtz_days" as this uses timezone to convert due running query below:

select pin01.inf2_localtz_days(1480816800) --04/DEC/2016 02:00GMT
  , pin01.inf2_localtz_days(1480820400) --04/DEC/2016 03:00GMT
from dual

Resulted on 3 for the first field and 4 for the second field.

It appears that the BRM function "INF2_LOCALTZ_DAYS" is using DBTIMEZONE to extract the day instead of SESSIONTIMEZONE, presuming this because on running the same query on different databases which are with different DBTIMEZONE, different results are obtained.

But even changing the "session time zone" to "-05:00", "+00:00" or any other, the result was always the same based on DBTIMEZONE.

As database was created with DBTIMEZONE "-03:00", and during DST when on GMT -2, when the function tries to convert the date stored timestamp "04/dec/2016 02:00GMT" to local time, due this setting, is getting changed to "03/dec/2016 23:00" instead of "04/dec/2016 00:00"

============================
select DBTIMEZONE from dual
Result:
DBTIMEZONE
-------------
-03:00

SELECT SESSIONTIMEZONE FROM DUAl

Result:
SESSIONTIMEZONE
------------------

Question:

Is the analysis is correct and is there any solution/fix to this issue?

Solution

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
Goal
Solution
References


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