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 FEBRUARY 01, 2024

Applies to:

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:

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

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