My Oracle Support Banner

Bad Performance When Running Depot Repair Receipt Update Concurrent (Doc ID 2731784.1)

Last updated on NOVEMBER 30, 2020

Applies to:

Oracle Depot Repair - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.5 version, Logistics Management

ACTUAL BEHAVIOR
---------------

Bad performance when running Depot Repair Receipt Update concurrent.


Find that following query takes long to complete:
SELECT
 CPT.PRODUCT_TRANSACTION_ID, SUM (RT.QUANTITY)
  FROM APPS.CSD_PRODUCT_TRANSACTIONS CPT,
  APPS.PO_REQUISITION_LINES_ALL PRL,
  APPS.RCV_SHIPMENT_LINES RSL,
  APPS.RCV_TRANSACTIONS RT
  WHERE CPT.ORDER_HEADER_ID = NVL (:B1, CPT.ORDER_HEADER_ID)
  AND CPT.REQ_HEADER_ID = PRL.REQUISITION_HEADER_ID
  AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
  AND RT.TRANSACTION_TYPE = 'RECEIVE'
  AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
  AND CPT.ACTION_CODE IS NULL
  AND CPT.ACTION_TYPE IS NULL
  AND NVL (CPT.QUANTITY_SHIPPED, 0) > NVL (CPT.QUANTITY_RECEIVED, 0)
  AND RSL.MMT_TRANSACTION_ID IN
  (SELECT MMT.TRANSACTION_ID
  FROM APPS.CSD_PRODUCT_TRANSACTIONS CPT,
  APPS.MTL_MATERIAL_TRANSACTIONS MMT
  WHERE CPT.DELIVERY_DETAIL_ID = MMT.PICKING_LINE_ID
  AND CPT.ORDER_HEADER_ID =
  NVL (:B1, CPT.ORDER_HEADER_ID)
  UNION ALL
  SELECT MMT.TRANSACTION_ID
  FROM APPS.WSH_DELIVERY_DETAILS WDD,
  APPS.CSD_PRODUCT_TRANSACTIONS CPT,
  APPS.MTL_MATERIAL_TRANSACTIONS MMT
  WHERE CPT.DELIVERY_DETAIL_ID =
  WDD.SPLIT_FROM_DELIVERY_DETAIL_ID
  AND CPT.ORDER_HEADER_ID = WDD.SOURCE_HEADER_ID
  AND WDD.SOURCE_CODE = 'OE'
  AND CPT.ORDER_HEADER_ID =
  NVL (:B1, CPT.ORDER_HEADER_ID)
  AND WDD.DELIVERY_DETAIL_ID = MMT.PICKING_LINE_ID
  AND NOT EXISTS
  (SELECT 'exists'
  FROM APPS.CSD_PRODUCT_TRANSACTIONS CPT1
  WHERE WDD.DELIVERY_DETAIL_ID =
  CPT1.DELIVERY_DETAIL_ID
  AND CPT.ORDER_HEADER_ID =
  CPT1.ORDER_HEADER_ID))
GROUP BY CPT.PRODUCT_TRANSACTION_ID;

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Execute CSD_UPDATE_PROGRAMS_PVT (Depot Repair Update Programs/Concurrent Programs)

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.