R12: PARGDR_SINGLE Performance Issue When Running PRC: Generate Draft Revenue for a Single Project

(Doc ID 2162280.1)

Last updated on SEPTEMBER 18, 2017

Applies to:

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

Symptoms

PRC: Generate Draft Revenue for a Single Project
Performance issue:
SELECT 1
FROM
DUAL WHERE EXISTS( SELECT /*+ INDEX(Rtask PA_TASKS_N8) INDEX(ei
 PA_EXPENDITURE_ITEMS_N9)*/ 1 FROM PA_EXPENDITURE_ITEMS_ALL EI, PA_TASKS
 RTASK WHERE RTASK.PROJECT_ID = :B5 AND EI.TASK_ID = RTASK.TASK_ID AND
 RTASK.CHARGEABLE_FLAG = 'Y' AND ((:B4 = 'Y' AND
 (RTASK.REVENUE_ACCRUAL_METHOD = 'WORK' OR RTASK.INVOICE_METHOD = 'WORK'))
 OR (:B4 = 'N' AND (:B3 = 'WORK' OR :B2 = 'WORK'))) AND
 EI.COST_DISTRIBUTED_FLAG = 'Y' AND EI.REVENUE_DISTRIBUTED_FLAG = 'N' AND
 EI.EXPENDITURE_ITEM_DATE <= :B1 AND EXISTS( SELECT /*+ INDEX(crdl
 PA_CUST_REV_DIST_LINES_U1)*/ NULL FROM PA_CUST_REV_DIST_LINES CRDL,
 PA_DRAFT_REVENUES DRX1 WHERE EI.PROJECT_ID = CRDL.PROJECT_ID AND
 ((EI.EXPENDITURE_ITEM_ID = CRDL.EXPENDITURE_ITEM_ID) OR
 (EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NOT NULL AND
 EI.ADJUSTED_EXPENDITURE_ITEM_ID = CRDL.EXPENDITURE_ITEM_ID)) AND
 NVL(CRDL.REVERSED_FLAG,'N') = 'N' AND NVL(CRDL.LINE_NUM_REVERSED,0) = 0 AND
 DRX1.PROJECT_ID = CRDL.PROJECT_ID AND DRX1.DRAFT_REVENUE_NUM =
 CRDL.DRAFT_REVENUE_NUM AND DECODE(DRX1.GENERATION_ERROR_FLAG,:B6 ,
 DECODE(DRX1.RELEASED_DATE,NULL,1,0),0) = DECODE(DRX1.RELEASED_DATE,NULL,1,0)
  UNION ALL SELECT 1 FROM PA_EXPENDITURE_ITEMS EI2 WHERE EI2.PROJECT_ID =
 EI.PROJECT_ID AND EI2.EXPENDITURE_ITEM_ID = EI.ADJUSTED_EXPENDITURE_ITEM_ID
 AND EI2.REVENUE_DISTRIBUTED_FLAG = 'Y' AND EI2.RAW_REVENUE = 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