Payment Query To Be Optimised (Doc ID 751314.1)

Last updated on FEBRUARY 18, 2011

Applies to:

Oracle Communications Billing and Revenue Management - Version: 6.0.3.0.0 and later   [Release: 6.0.3 and later ]

Symptoms

Query performance during account level write off of hierarchical account can be improved.

The query in question ( from dm_oracle.pinlog )

SQL_STMT dm_search_robj: cmd="select distinct event_t.poid_DB, event_t.poid_ID0,
event_t.poid_TYPE, event_t.poid_REV, event_t.account_obj_DB, event_t.account_obj_ID0, event_t.
account_obj_TYPE, event_t.account_obj_REV from event_t ,event_bal_impacts_t ,item_t where (
event_bal_impacts_t.impact_type = :1 or event_bal_impacts_t.impact_type = :2 ) and
item_t.ar_bill_obj_ID0 = :3 and item_t.poid_ID0 = event_bal_impacts_t.item_obj_ID0 and
event_t.poid_id0 = event_bal_impacts_t.obj_id0"

This takes couple of mins to get the tax event. It will be good have the account_obj_id0 in the search template which is the AR account poid.

-- Steps To Reproduce:
1. Create a parent account
2. Create two child accounts
3. Run billing for the child and parent
4. Do an account level writeoff

Input flist for write-off:

#PCM_OP_AR_ACCOUNT_WRITEOFF
# number of field entries allocated 7, used 6
0 PIN_FLD_POID POID [0] 0.0.0.1 /account 611140 0
0 PIN_FLD_PROGRAM_NAME STR [0] "AR_ACCOUNT_WRITEOFF"
0 PIN_FLD_FLAGS INT [0] 4
0 PIN_FLD_STR_VERSION INT [0] 1
0 PIN_FLD_STRING_ID INT [0] 106
0 PIN_FLD_AR_BILLINFO_OBJ POID [0] 0.0.0.1 /billinfo 614212 0



Cause

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