Performance Issue When Creating Return Order By Copying From Existing Sales Order (Doc ID 2259791.1)

Last updated on APRIL 27, 2017

Applies to:

Oracle Order Management - Version 12.2 and later
Information in this document applies to any platform.

Symptoms



Find performance issue when Copying an existing Sales Order to create a Return Order (RMA)
System takes 7 to 20 minutes to complete this copy action.

Performance trace for the copy shows the most expensive sql is

SELECT U.LOT_NUMBER, ABS(SUM(U.TRANSACTION_QUANTITY)) TRANSACTION_QUANTITY,
  ABS(SUM(U.SECONDARY_TRANSACTION_QUANTITY)) SECONDARY_TRANSACTION_QUANTITY
FROM
 MTL_TRANSACTION_LOT_NUMBERS U, MTL_LOT_NUMBERS L, MTL_MATERIAL_TRANSACTIONS
  M WHERE M.TRANSACTION_ID = U.TRANSACTION_ID AND U.INVENTORY_ITEM_ID = :B1
  AND M.TRANSACTION_SOURCE_TYPE_ID = 2 AND M.TRX_SOURCE_LINE_ID = :B3 AND
  M.ORGANIZATION_ID = :B2 AND M.INVENTORY_ITEM_ID = :B1 AND U.ORGANIZATION_ID=
  L.ORGANIZATION_ID AND U.INVENTORY_ITEM_ID=L.INVENTORY_ITEM_ID AND
  U.LOT_NUMBER = L.LOT_NUMBER GROUP BY U.LOT_NUMBER
 

CHANGES

Apps upgrade done from 12.1.3 to 12.2.4
db upgrade from 11.2.0.4 to 12.1.0.2

STEPS

The issue can be reproduced at will with the following steps:
1. Create a Sales Order and book it and progress this till Shipping
2. Query this Sales Order in Order Organizer
3. Do a Actions > Copy
4. Change the New Order Type to an RMA Order type and enter the reason code.
5. Click on Ok
6. Order Copy process takes 7 to 20 minutes to copy completely to the RMA.


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