Supplier Constraints Via Batch (supcnstr) And UI Have Performance Issue W/Forecast-able Items (Doc ID 2939462.1)

Last updated on APRIL 06, 2023

Applies to:

Symptoms

ACTUAL BEHAVIOR
---------------
Supplier Constraints via batch (supcnstr) and UI have Performance Issue w/Forecast-able Items

When applying supplier constraints to a purchase order, either via the supplier constraint batch (supcnstr) or the UI Scale Order process, and that purchase order contains a forecast-able items there is a performance issue. The code will query either ITEM_FORECAST or DAILY_ITEM_FORECAST doing a full table scan. For Kroger, the ITEM_FORECAST table has ~3 billion rows and the DAILY_ITEM_FORECAST has ~ 2.3 billion rows and thus a full table scan will run forever.


EXPECTED BEHAVIOR
-----------------------
No performance issues occurring in UI for forecast-able Items
or in running of batch SUPCNSTR

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
 1) Setup Dynamic Replenishment for forecast-able items and set to 'Semi-Automatic
2) Load Weekly Item Forecasts for items in #1 (and make sure ITEM_FORECAST data has significant volume).
3) Run prepost replroq pre
4) Run replroq.ksh
5) Run prepost reqext pre
6) Run batch_reqext.ksh
7) Run prepost reqext post
8) Run prepost rpl rep
9) Run rplext.ksh
10) Run rplbld
11) Run supcnstr
12) Notice supcnstr has performance issues


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue batch is taking a long time to process

Cause

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