Performance Issues When trying to Launch Payment Manager (Doc ID 1918712.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version 12.1.1 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
Payment Manager

Symptoms

When click the Payment Manager link, it takes more than 60 secs to launch.
When launch the Payment Manager to process PPRs, it takes a long time to load the page and errors out on several occasions.

A trace files identified that the following code is taking along time.
 

SELECT COUNT(*)
FROM
IBY_PAY_SERVICE_REQUESTS IBY WHERE IBY.CALLING_APP_ID = 200 AND
 IBY.PROCESS_TYPE = 'STANDARD' AND ( IBY.PAYMENT_SERVICE_REQUEST_STATUS IN (
 'INFORMATION_REQUIRED', 'PENDING_REVIEW_DOC_VAL_ERRORS',
 'PENDING_REVIEW_PMT_VAL_ERRORS', 'PENDING_REVIEW') OR (
 IBY.PAYMENT_SERVICE_REQUEST_STATUS = 'PAYMENTS_CREATED' AND EXISTS ( SELECT
 'NEED_ACTION_BY_ME' FROM IBY_PAYMENTS_ALL PMT, IBY_PAY_INSTRUCTIONS_ALL
 INSTR WHERE IBY.PAYMENT_SERVICE_REQUEST_ID = PMT.PAYMENT_SERVICE_REQUEST_ID
 AND INSTR.PAYMENT_INSTRUCTION_ID = PMT.PAYMENT_INSTRUCTION_ID AND
 (INSTR.PAYMENT_INSTRUCTION_STATUS IN ('CREATION_ERROR',
 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED',
 'FORMATTED_READY_FOR_PRINTING', 'SUBMITTED_FOR_PRINTING',
 'CREATED_READY_FOR_PRINTING', 'CREATED_READY_FOR_FORMATTING', 'FORMATTED',
 'CREATED') OR (INSTR.PAYMENT_INSTRUCTION_STATUS = 'TRANSMITTED' AND
 IBY_FD_USER_API_PUB.IS_TRANSMITTED_PMT_INST_COMPL(INSTR.PAYMENT_INSTRUCTION
 _ID) = 'N')) AND IBY_UTILITY_PVT.CHECK_USER_ACCESS(INSTR.PAYMENT_INSTRUCTIO
 N_ID) = 'Y' ) ) )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.10          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    256.19     734.06     183184    1659857          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    256.24     734.17     183184    1659857          0           1


SELECT 'N'
FROM
DUAL WHERE EXISTS ( SELECT 'Inaccessible org' FROM IBY_PAYMENTS_ALL WHERE
 PAYMENT_INSTRUCTION_ID = :B1 AND MO_GLOBAL.CHECK_ACCESS(ORG_ID) = 'N')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  11944    155.33     632.20     182872     705501          0           0
Fetch    11944      0.06       0.05          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    23889    155.40     632.25     182872     705501          0           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