Performance Requisition Search Timing Out (Doc ID 1666574.1)

Last updated on SEPTEMBER 19, 2017

Applies to:

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

Symptoms

On : 12.1.3 version, Catalog Shopping

When attempting to search requisitions in advanced search, the following error occurs.

ERROR
-----------------------
Internal Server Error

Log file:
------------
fnd.functionSecurity.Authorization 1 Found function 1016367 under menu 1002953
ups.oracle.apps.icx.por.reqmgmt.webui.UpsExExpSearchCO 3 OAF LOG: Event : Call Process Form Request, in: ups.oracle.apps.icx.por.reqmgmt.webui.UpsExExpSearchCO: Entering Process Form Request
ups.oracle.apps.icx.por.reqmgmt.webui.UpsExExpSearchCO 2 processFormRequest() begin
ups.oracle.apps.icx.por.reqmgmt.webui.UpsExExpSearchCO 2 processFormRequest() end
fnd.framework.webui.OAPageContextImpl 3 OAF LOG: Event : Test Function For Grant, in: oracle.apps.fnd.framework.webui.OAPageContextImpl: ICX_POR_REQMGMT_EXPSRCH
fnd.functionSecurity.Authorization 1 Authorization.testFunction 1016367

Trace file:
-----------
SELECT * FROM (SELECT v.vendor_id,
  v.vendor_name,
  s.vendor_site_id,
  s.vendor_site_code,
  ou.name AS organization_name,
  DECODE(c.first_name,
  null, c.LAST_NAME,
  c.last_name || ', ' || c.first_name) contact_name,
  c.email_address contact_email_address,
  c.area_code || ' ' || c.phone contact_phone,
  c.fax_area_code || ' ' || c.fax contact_fax,
  s.address_line1 ||
  decode(s.address_line2, null, null, ' ' || s.address_line2) ||
  decode(s.address_line3, null, null, ' ' || s.address_line3) || ', ' ||
  decode(s.city, null, null, s.city || ', ') ||
  decode(s.province, null, null, s.province || ', ') ||
  decode(s.state, null, null, s.state || ', ') ||
  (select territory_short_name from fnd_territories_vl t where t.territory_code = s.country) site_address
FROM po_vendors v,
  po_vendor_sites_all s,
  hr_all_organization_units ou,
  po_vendor_contacts c
WHERE NVL(v.enabled_flag, 'Y') = 'Y' AND
  NVL(v.vendor_type_lookup_code, 'VENDOR') <> 'EMPLOYEE' AND
  TRUNC(sysdate) BETWEEN TRUNC(NVL(v.start_date_active, sysdate)) AND
  TRUNC(NVL(v.end_date_active, sysdate + 1)) AND
  s.vendor_id(+) = v.vendor_id AND
  NVL(s.purchasing_site_flag, 'N') <> 'N' AND
  TRUNC(NVL(s.inactive_date, sysdate + 1)) > TRUNC(sysdate) AND
  ou.organization_id = s.org_id AND
  TRUNC(sysdate) BETWEEN TRUNC(ou.date_from) AND
  TRUNC(NVL(ou.date_to, sysdate + 1)) AND
  c.vendor_site_id(+) = s.vendor_site_id AND
  TRUNC(NVL(c.inactive_date(+), sysdate + 1)) > TRUNC(sysdate)
ORDER BY v.vendor_name, s.vendor_site_code ASC) QRSLT WHERE (( UPPER(VENDOR_NAME) like UPPER(:1) AND (VENDOR_NAME like :2 OR VENDOR_NAME like :3 OR VENDOR_NAME like :4 OR VENDOR_NAME like :5)))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.05 54 2886 0 0
Execute 1 0.22 0.22 0 0 0 0
Fetch 1 1.26 68.68 14585 4741 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.56 68.95 14639 7627 0 1

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Go to iProcurement > Requisitions
2. Search for requisition using supplier name
3. See the error

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot search requisitions in iProcurement.

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