E1: 41F: PCW02 - Multiple Select Statements when adding to Item Structure Compromises Processing and Can Cause Web Exception

(Doc ID 2310932.1)

Last updated on SEPTEMBER 25, 2017

Applies to:

JD Edwards EnterpriseOne Apparel Management - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

EnterpriseOne 9.2

When configuring a level in PCW02 (from PCW51) and attempting to add a Level Code, the system appears to be doing multiple unnecessary selects inside of Loop causing timeout/performance issues. With a significant amount of data, when configuring a level in PCW02 (from PCW51) and attempting to add a Level Code, the system returns a web exception indicating that "there was a problem with the server while running the business function Create_FCW02. Items are not being created properly in the FCW02 [Item Structure Definition]."

Review the logs and note that Create_Childs_FCW02 is doing the same SELECTs against the FCW02 over and over again inside of a WHILE loop. For example the following SELECT is executed 23 times.
SELECT  *  FROM PRODDTA.FCW02  WHERE  ( ISC9STRC = 'SKT' AND ISC9LVNO = 3.000000 AND ISC9LVCO = '01        ' AND ISC9PLNM = 'SKIRT-AP.COT/WHT' AND ISC9LVL0 = 'SKIRT-AP' )  ORDER BY ISC9STRC ASC,ISC9LVNO ASC,ISC9LVNM ASC. This causes a performance issue when the FCW02 has a large amount of data. The thought is that we only need to select the record once and either store the data in variables or a data pointer.

Changes

 

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