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

Last updated on MARCH 08, 2017

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 5 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

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