Fatal SQL Error Received when over 1000 UNSPC Category Codes are Loaded and the Catalog is Searched in the eProcurement Requisition (Doc ID 1600416.1)

Last updated on JULY 19, 2016

Applies to:

PeopleSoft Enterprise SCM eProcurement - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

A FATAL SQL error is received when accessing the Catalog tree in eProcurement.

This error occurrs when over 1000 UNSPC Category Codes are loaded and catalog is searched in the eProcurement Requisition.

Error:  A SQL error occurred. Please consult your system log for details.

A fatal PeopleCode SQL error occurred. Please consult your system log for details.

Fatal SQL error occurred. (2,125) PV_NEWSRCH_WRK.PV_ADD_PB.FieldFormula  Name:FillResultsRowset  PCPC:174515  Statement:2745
Called from:PV_NEWSRCH_WRK.PV_ADD_PB.FieldFormula  Name:CatBrowseSQLSearch  Statement:2886
Called from:PV_REQ_CATBRW_RES.Activate  Statement:26

A SQL error has occurred, review the SQL to determine the cause of the error.

Review the program for errors.  If necessary, run a PeopleCode trace to determine the program that failed.


App Server Log Error Message:  Error Position:  8540
Return:  1795 - ORA-01795: maximum number of expressions in a list is 1000

Issue can be recreated with the following steps:

  1. Login as Catalog Administrator. Navigate to Main Menu > Catalog Management > Catalogs > Maintain Catalogs
  2. Take an Existing Catalog and Navigate to Enterprise Manager tab
  3. Add the designated user say VP1 as Manager
  4. Navigate to Categories Tab and Add UNSPC as catalog Hierarchy
  5. Login as VP1
  6. Add a Linked Supplier of type Catalog Management Supplier for the Catalog chosen. Enter the Trading Partner ID as provided in Partners tab in Maintain Catalogs
  7. Create a new run control for CUP process with source Catalog Management. Select the designated catalog in the catalog ID field and add UNSPC as Hierarchy ID
  8. Add new value to the Tree name field
  9. Populate the account and currency fields in the Item Defaults tab and save the run control. Yes to message asking to create a new tree
  10. Run the PV_CP_LOAD process to success and posted
  11. When complete check that the UNSPC hierarchy from Catalog Management was loaded into tree manager. It can be verified at Tree Manager
  12. Navigate to eProcurement > Requisition and click on the new catalog from the list
  13. Error is received







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