Performance Issue Trying To Open Payments With Multiple POs

(Doc ID 2315535.1)

Last updated on OCTOBER 09, 2017

Applies to:

Oracle iProcurement - Version 12.1.2 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR.
---------------
When attempting to open a payments with multiple POs, it does not open.

EXPECTED BEHAVIOR
-----------------------
When attempting to open a payments with multiple POs, it should open.


-- Trace File --

TKPROF: Release 10.1.0.5.0 - Production on Thu Feb 23 06:17:24 2017

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: dxbs11_ora_26870348_WEL2253.trc
Sort options: exeela prsela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SELECT * FROM (select 'Select_flag',
poh.po_num,
poh.comments,
poh.creation_date,
poh.po_release_id,
to_char(null) agent_name,
poh.currency_code,
to_char(0) as amount,
poh.ACCEPTANCE_DUE_DATE,
poh.vendor_id,
poh.po_header_id,
poh.agent_id,
poh.type_lookup_code,
poh.type_name,
poh.vendor_contact_id,
poh.vendor_site_id,
poh.acceptance_required_flag,
poh.approved_date,
poh.revision_num,
to_char(null) PO_STATUS_CODE ,
to_char(null) PO_STATUS_DISP ,
poh.org_name,
poh.attribute1,
poh.attribute2,
poh.attribute3,
poh.attribute4,
poh.attribute5,
poh.attribute6,
poh.attribute7,
poh.attribute8,
poh.attribute9,
poh.attribute10,
poh.attribute11,
poh.attribute12,
poh.attribute13,
poh.attribute14,
poh.attribute15,
poh.attribute_category,
decode(poh.po_release_id,null,'PosHdrAttach','PosRelAttach') as SwitchAttach,
'N' as retrieve_flag,
poh.CANCEL_FLAG,
poh.CLOSED_CODE,
poh.FROZEN_FLAG,
poh.USER_HOLD_FLAG,
poh.global_agreement_flag,
to_char(null) consigned_consumption_flag,
poh.vendor_name,
poh.vendor_site_code,
poh.po_release_id po_release_id2,
poh.vendor_number ,
poh.vendor_id vendor_id2, org_id,
poh.segment1,
poh.release_num,
to_char(null) business_document_type,
to_char(null) business_document_id,
to_char(null) business_document_version,
0 as ship_to_location_id,
security_level_code,
access_level_code
from pos_view_po_summary_v poh) QRSLT WHERE ((((po_release_id is null and po_header_id in (select distinct ail.po_header_id from ap_invoice_lines_all ail where ail.po_release_id is null and ail.invoice_id in (select invoice_id from ap_invoice_payments_all where check_id = :1) ) ) or (po_release_id is not null and po_release_id in (select distinct ail.po_release_id from ap_invoice_lines_all ail where ail.po_release_id is not null and ail.invoice_id in (select invoice_id from ap_invoice_payments_all where check_id = :2 )) )) ) AND ( mo_global.check_access(ORG_ID) = 'Y' )) ORDER BY approved_date desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.13 16 101 0 0
Execute 1 0.38 1.44 0 8 0 0
Fetch 1 111.83 592.37 396049 15186630 4 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 112.24 593.95 396065 15186739 4 2

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Go to iProcurement responsibility
2. Select requisitions
3. Search for the below PO number
4. Select View payments and select GO on the top
5. Select Multiple under column PO number
The page keep on opening, it has a performance issue when try to open it

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