GL Report Performance Enhancement (Doc ID 1140673.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.1.0.0 to 7.3.1.0.0 [Release 7.3.1]
All Platforms
Checked for relevance on 11-Apr-2012.
***Checked for relevance on 06-Oct-2013***
*** Checked for relevance on 05-01-2016 ***


Symptoms

Some of the sql queries in the insert trigger are full scanning journal_t. There is an index to help the GL report process on 5 columns, but because one of the columns is being altered in the query, this index is NOT being used. In this example, the +0 is causing the problem. So, the user just did a -0 on the other side of the condition.

Here is an example of the original SQL query:

SELECT SUM (pin.PIN_ROUND_BEID(DB_GROSS , ROUNDING , ROUNDING_MODE ) ) DB_GROSS ,
SUM (pin.PIN_ROUND_BEID(CR_GROSS , ROUNDING , ROUNDING_MODE ) ) CR_GROSS ,
SUM (pin.PIN_ROUND_BEID(DB_DISC , ROUNDING , ROUNDING_MODE ) ) DB_DISC ,
SUM (pin.PIN_ROUND_BEID(CR_DISC , ROUNDING , ROUNDING_MODE ) ) CR_DISC ,
SUM (pin.PIN_ROUND_BEID(DB_TAX , ROUNDING , ROUNDING_MODE ) ) DB_TAX ,
SUM (pin.PIN_ROUND_BEID(CR_TAX , ROUNDING , ROUNDING_MODE ) ) CR_TAX , RESOURCE_ID , GL_ID , NULL , NULL FROM
(SELECT DB_AR_NET_AMT + DB_AR_DISC_AMT DB_GROSS , CR_AR_NET_AMT + CR_AR_DISC_AMT CR_GROSS , DB_AR_DISC_AMT DB_DISC ,
CR_AR_DISC_AMT CR_DISC , DB_AR_TAX_AMT DB_TAX , CR_AR_TAX_AMT CR_TAX , RESOURCE_ID , GL_ID
FROM LEDGER_REPORT_GL_SEGMENTS_T GS , ITEM_T I , JOURNAL_T J WHERE GS.OBJ_ID0 = :b1 AND GS.GL_SEGMENT = I.GL_SEGMENT
AND ( ( I.EFFECTIVE_T BETWEEN 0 AND 0 ) OR I.EFFECTIVE_T >= :b2 ) AND I.POID_DB IS NOT NULL AND J.ITEM_OBJ_ID0 = I.POID_ID0
AND J.GL_ID != 0 AND ( ( J.RESOURCE_ID + 0 ) BETWEEN :b3 AND :b4 ) AND J.CREATED_T < :b2 ) ITEM_INFO ,
(SELECT REC_ID2 REC_ID , ROUNDING , ROUNDING_MODE FROM CONFIG_BEID_RULES_T WHERE EVENT_TYPE = :b5 AND PROCESSING_STAGE = :b6 ) BEID_INFO
WHERE ITEM_INFO.RESOURCE_ID = BEID_INFO.REC_ID GROUP BY GL_ID , RESOURCE_ID

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