My Oracle Support Banner

SPWA When Attempting To View Demand Pegging In SPWA , Webpage Message Displays ADF-FACES-60097 (Doc ID 2707644.1)

Last updated on SEPTEMBER 03, 2020

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.6.3 version, User Interface

ACTUAL BEHAVIOR
---------------
When attempting to execute a query in the Supply Planning Work Area (SPWA), it is taking almost 15 minutes to return data. Tracing the process shows the following SQL taking a long time:

SQL ID: azztb69zrkvjb Plan Hash: 2959513278

SELECT PegDownEO.PLAN_ID, PegDownEO.SOURCE_TABLE,
  PegDownEO.PEGGING_ID, PegDownEO.CATEGORY_SET_ID,
  PegDownEO.TRANSACTION_ID, PegDownEO.ALLOCATED_QUANTITY,
  PegDownEO.ORGANIZATION_CODE, PegDownEO.CATEGORY_NAME,
  PegDownEO.ORGANIZATION_ID, PegDownEO.INVENTORY_ITEM_ID,
  PegDownEO.ITEM_SEGMENTS, PegDownEO.DESCRIPTION,
  ...
 FROM
 MSC_PEG_ORDERS_V2 PegDownEO WHERE exists (select 1 from msc_org_access moa,
  msc_plans mp where PegDownEO.plan_id = mp.plan_id and
  (moa.sr_instance_id, moa.organization_id) in ((mp.sr_instance_id,
  mp.organization_id), (PegDownEO.sr_instance_id, PegDownEO.organization_id))
  and moa.responsibility_id = fnd_global.resp_id and sysdate
  between moa.effective_from_date and nvl(moa.effective_to_date, sysdate))
  AND ((pegging_id, transaction_id) in (select mfp2.pegging_id,
  mfp2.transaction_id from msc_full_pegging mfp2, msc_full_pegging mfp where
  mfp2.plan_id = :Bind_PlanId and mfp.demand_id = :Bind_TransactionId
  and mfp.plan_id = mfp2.plan_id and mfp.prev_pegging_id =
  mfp2.pegging_id)) AND (:Bind_PlanId = PegDownEO.PLAN_ID AND
  :Bind_CategorySetId = PegDownEO.CATEGORY_SET_ID AND :Bind_SourceTable is
  not null AND :Bind_TransactionId is not null) ORDER BY PEGGING_ID

EXPECTED BEHAVIOR
-----------------------
The performance of this form should be much faster.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Select a Supply Plan
2. Under Plan Links Choose Supplies and Demands
3. In Search enter Organization and Item and click Search
4. Under Actions, choose View Demand Pegging
5. Select arrow to expand one of the items Arrow changes to a pulsing circle
 - started at 08:17 am Pacific
 - Data returned : 08:48



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
Cause
Solution


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