PAXRVRVW, Performance Issue Changing Revenue Status From Unreleased/Release (Doc ID 2042877.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Billing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

PAXRVRVW: Revenue Summary
 Performance issue when changing the Revenue Status from Unreleased/release in Revenue Summary

SQL ID: d0dqpkty7r67a Plan Hash: 2967555621

SELECT NULL
FROM
SYS.DUAL WHERE NOT EXISTS ( SELECT NULL FROM PA_DRAFT_INVOICES WHERE
 (PROJECT_ID, DRAFT_INVOICE_NUM) IN (SELECT PROJECT_ID,DRAFT_INVOICE_NUM
 FROM PA_CUST_REV_DIST_LINES WHERE PROJECT_ID = :B2 AND DRAFT_REVENUE_NUM =
 :B1 UNION SELECT PROJECT_ID,DRAFT_INVOICE_NUM FROM
 PA_CUST_EVENT_REV_DIST_LINES WHERE PROJECT_ID = :B2 AND DRAFT_REVENUE_NUM =
 :B1 UNION SELECT DISTINCT DII.PROJECT_ID,DII.DRAFT_INVOICE_NUM FROM
 PA_DRAFT_INVOICE_ITEMS DII, PA_EVENTS E WHERE DII.PROJECT_ID=:B2 AND
 DII.EVENT_NUM IS NOT NULL AND E.EVENT_NUM = DII.EVENT_NUM AND NVL(E.TASK_ID,
 -99) = NVL(DII.EVENT_TASK_ID,-99) AND EXISTS (SELECT 1 FROM PA_EVENT_TYPES
 ET WHERE E.EVENT_TYPE = ET.EVENT_TYPE AND ET.EVENT_TYPE_CLASSIFICATION =
 'AUTOMATIC') AND EXISTS (SELECT 1 FROM PA_CUST_EVENT_RDL_ALL WHERE
 PROJECT_ID = DII.PROJECT_ID AND DRAFT_REVENUE_NUM = :B1 AND EVENT_NUM =
 DII.EVENT_NUM AND NVL(TASK_ID,-99) = NVL(DII.EVENT_TASK_ID,-99) )) AND
 RELEASED_DATE IS NOT NULL)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 195.79 339.06 46209 47496 10 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 195.79 339.06 46209 47496 10 1

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