My Oracle Support Banner

R12 AP/IBY: Payment Instructions Poor Performance Searching By Status = Formatted (Doc ID 2219790.1)

Last updated on FEBRUARY 28, 2019

Applies to:

Oracle Payables - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

Performance problem in the Funds disbursement process home page.

When going to the Payment Instructions page to Search by Status = Formatted,
after some minutes, the following error is displayed:

ERROR
--------
Failure of server APACHE bridge:
No backend server available for connection: timed out after 10 seconds or
idempotent set to OFF or method not odempotent.

 

This SQL statement took 18 minutes according to the trace generated:

SQL_ID = fsh7rfuas3nv5

SELECT * FROM (SELECT payment_instructions.PAYMENT_INSTRUCTION_ID,
payment_instructions.PAYMENT_PROFILE_ID ,
payment_instructions.PROCESS_TYPE ,
DECODE(payment_instructions.PAYMENT_INSTRUCTION_STATUS , 'TRANSMITTED',
DECODE(IBY_FD_USER_API_PUB.Is_transmitted_Pmt_Inst_Compl(payment_instructions.
PAYMENT_INSTRUCTION_ID),'N','TRANSMITTED_RETRY_COMPLETION','TRANSMITTED'),
payment_instructions.PAYMENT_INSTRUCTION_STATUS) PAYMENT_INSTRUCTION_STATUS ,

...
...
FROM IBY_PAY_INSTRUCTIONS_SEC_V payment_instructions,
fnd_lookups fnd_status,
IBY_PAYMENT_PROFILES payment_profiles
WHERE decode (payment_instructions.PAYMENT_INSTRUCTION_STATUS,
'TRANSMITTED',
DECODE(IBY_FD_USER_API_PUB.Is_transmitted_Pmt_Inst_Compl(payment_instructions.
PAYMENT_INSTRUCTION_ID),
'N','TRANSMITTED_RETRY_COMPLETION','TRANSMITTED'),payment_instructions.PAYMENT
_INSTRUCTION_STATUS) = fnd_status.lookup_code (+)
AND fnd_status.lookup_type(+)='IBY_PAY_INSTRUCTION_STATUSES'
AND payment_instructions.payment_profile_id =
payment_profiles.payment_profile_id) QRSLT
WHERE (( UPPER(STATUS) like UPPER(:1) AND (STATUS like :2 OR STATUS like
:3 OR STATUS like :4 OR STATUS like :5)))
ORDER BY PAYMENT_INSTRUCTION_ID

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.