E1: 41F: PCW02 - Multiple Select Statements when adding to Item Structure Compromises Processing and Can Cause Web Exception
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.
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.
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