R12: Generate Draft Invoices For A Single Award Has Performance Issue After 12.2 Upgrade (Doc ID 2179073.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Grants Accounting - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

GMSINVGEN_SINGLE: GMS: Generate Draft Invoices for a Single Award performance issue.

SQL ID: 56r58wqa7z89j Plan Hash: 621305928

SELECT /*+INDEX(pt PA_PROJECT_TYPES_U1)*/ EI.EXPENDITURE_ITEM_ID
 EXPENDITURE_ITEM_ID, ADL.ADL_LINE_NUM ADL_LINE_NUM, ADL.AWARD_ID AWARD_ID
FROM
PA_EXPENDITURE_ITEMS_ALL EI, PA_EXPENDITURE_TYPES ET, PA_PROJECTS_ALL P,
 PA_PROJECT_TYPES PT, PA_TASKS T3, PA_TASKS T5, GMS_AWARD_DISTRIBUTIONS ADL
 WHERE ADL.AWARD_ID = :B2 AND EI.EXPENDITURE_ITEM_ID =
 ADL.EXPENDITURE_ITEM_ID AND EI.SYSTEM_LINKAGE_FUNCTION <> 'BTC' AND
 P.PROJECT_STATUS_CODE <> 'CLOSED' AND PT.PROJECT_TYPE = P.PROJECT_TYPE AND
 PT.DIRECT_FLAG = 'N' AND T3.PROJECT_ID = P.PROJECT_ID AND EI.TASK_ID =
 T3.TASK_ID AND T3.TOP_TASK_ID = T5.TASK_ID AND T5.READY_TO_BILL_FLAG = 'Y'
 AND ADL.COST_DISTRIBUTED_FLAG = 'Y' AND EI.EXPENDITURE_ITEM_DATE <=
 NVL(TRUNC(:B1 ),SYSDATE) AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE AND
 ADL.DOCUMENT_TYPE = 'EXP' AND ADL.FC_STATUS = 'A' AND ADL.ADL_STATUS = 'A'
 AND EI.BILL_HOLD_FLAG='O'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.10 0.10 0 6 9 0
Fetch 1 0.56 34.48 3925 17219 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.66 34.59 3925 17225 9 0

Changes

 Upgraded to 12.2

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