Long Reserve Times Causing Deferred Requisition Approval (Doc ID 1412173.1)

Last updated on OCTOBER 22, 2013

Applies to:

Oracle Purchasing - Version 12.1.1 and later
Information in this document applies to any platform.
***Checked for relevance on 22-OCT-2013***

Symptoms


The Workflow background process for Requisition Approval (REQAPPRV) does not finish and requisitions cannot be processed.

This issue seems to be performance related as the Reserve process for Document Approval is taking a very long time to complete:  

- A Requisition without Encumbrance takes as little as 5 secs to Approve.  
- A Requisition with Encumbrance enabled is taking up to 7+ minutes to Approve. 

Due to this issue, users experience a backlog of Requisitions to process which then go to Deferred processing status waiting.

SQL Trace show the following performance issue:

SQL ID: 7s4gywnxw4jdn
Plan Hash: 4000387872
INSERT INTO XLA_DIAG_SOURCES ( EVENT_ID , LEDGER_ID , SLA_LEDGER_ID ,
DESCRIPTION_LANGUAGE , OBJECT_NAME , OBJECT_TYPE_CODE , LINE_NUMBER ,
SOURCE_APPLICATION_ID , SOURCE_TYPE_CODE , SOURCE_CODE , SOURCE_VALUE ,
SOURCE_MEANING , CREATED_BY , CREATION_DATE , LAST_UPDATE_DATE ,
LAST_UPDATED_BY , LAST_UPDATE_LOGIN , PROGRAM_UPDATE_DATE ,
PROGRAM_APPLICATION_ID , PROGRAM_ID , REQUEST_ID ) SELECT EVENT_ID , :B12 ,
:B11 , :B10 , OBJECT_NAME , OBJECT_TYPE_CODE , LINE_NUMBER ,
SOURCE_APPLICATION_ID , SOURCE_TYPE_CODE , SOURCE_CODE ,
SUBSTR(SOURCE_VALUE,1,1996) , SUBSTR(SOURCE_MEANING ,1,200) , :B9 ,
TRUNC(SYSDATE) , TRUNC(SYSDATE) , :B9 , :B8 , TRUNC(SYSDATE) , :B7 , :B6 ,
:B5 FROM ( SELECT XET.EVENT_ID EVENT_ID , L1.LINE_NUMBER LINE_NUMBER , CASE
R WHEN 1 THEN 'PO_REQ_EXTRACT_DETAIL_V' WHEN 2 THEN
'PO_REQ_EXTRACT_DETAIL_V' WHEN 3 THEN 'PO_REQ_EXTRACT_DETAIL_V' WHEN 4 THEN
'PO_REQ_EXTRACT_DETAIL_V' WHEN 5 THEN 'PO_REQ_EXTRACT_DETAIL_V' WHEN 6 THEN
'PO_REQ_EXTRACT_DETAIL_V' WHEN 7 THEN 'PO_REQ_EXTRACT_DETAIL_V' WHEN 8 THEN
'PO_REQ_EXTRACT_DETAIL_V' WHEN 9 THEN 'PO_REQ_EXTRACT_DETAIL_V' WHEN 10
...
...
...
WHEN 16 THEN TO_CHAR(L3.CURRENCY_CONVERSION_DATE) WHEN 17 THEN
TO_CHAR(L3.CURRENCY_CONVERSION_RATE) WHEN 18 THEN
TO_CHAR(L3.CURRENCY_CONVERSION_TYPE) WHEN 19 THEN
TO_CHAR(L1.ADJUSTMENT_STATUS) WHEN 20 THEN TO_CHAR(L1.REQ_ENCUM_UPG_OPTION)
WHEN 21 THEN TO_CHAR(L1.REQ_UPG_ENC_TYPE_ID) ELSE NULL END SOURCE_VALUE ,
NULL SOURCE_MEANING FROM XLA_EVENTS_GT XET , PO_REQ_EXTRACT_DETAIL_V L1 ,
PO_REQ_LINES_REF_V L3 , (SELECT ROWNUM R FROM ALL_OBJECTS WHERE ROWNUM <=
21 AND OWNER = :B1 ) WHERE XET.EVENT_DATE BETWEEN :B4 AND :B3 AND
XET.EVENT_CLASS_CODE = :B2 AND L1.EVENT_ID = XET.EVENT_ID AND
L1.REQ_LINE_ID = L3.REQ_LINE_ID (+) )



call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   1      0.00     0.00       0          0          0          0
Execute 1      344.34   344.87     56904      5651749    33         21
Fetch   0      0.00     0.00       0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   2      344.34   344.87     56904      5651749    33         21




Steps To Reproduce

1. Submit Requisition with Encumbrance for approval.
 

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