My Oracle Support Banner

Unexpected Proportions Affecting Production Results (Doc ID 2509655.1)

Last updated on DECEMBER 04, 2019

Applies to:

Oracle Demantra Demand Management - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.6 version, Technical

ACTUAL BEHAVIOR
---------------
Proportions computed by the proport procedure are not consistent with proport parameters chosen.
Eg, glob_prop calculated by proport procedure for item_id = zzzzz and location_id = aaaaa is 2000 while customer expects to be 1333,33

Referring to item_id = xxxx and location_id = yyyy.

Hist_glob_prop = 12, and sum of sales in last 12 months in 4000. First sale in HGP range is Nov 2017, hence glob_prop = 4000 / (number of months).

BUT code says number of months is months_between(last_date, first_date) = 2.

'safe_division(p.sum_all, DECODE(TRUNC(MONTHS_BETWEEN( p.hgp_last_date , NVL( p.first_sale_in_hgp , p.hgp_start_date))),0,1, TRUNC(MONTHS_BETWEEN( p.hgp_last_date , NVL( p.first_sale_in_hgp , p.hgp_start_date)))) /* Months */ ,0)';

I believe this is wrong because when the average is computed for weeks, number of weeks =

'safe_division(p.sum_all, TRUNC( safe_division( (p.hgp_last_date - CASE WHEN (p.sale_found_in_hgp = 1 AND p.sale_found_in_oeh = 0) THEN p.first_sale_in_hgp
  WHEN (p.sale_found_in_hgp = 0 AND p.sale_found_in_oeh = 1) THEN p.hgp_start_date
  WHEN (p.sale_found_in_hgp = 1 AND p.sale_found_in_oeh = 1) THEN p.hgp_start_date
  ELSE p.hgp_last_date END) ,7,0) + 1 ) /* Weeks */ ,0)';

NOTE the "+1" in the expression for week glob_prop

My expectation is that glob_prop = average sales over the time period. Nov 2017 to Jan 2018 is 3 months of history, not 2.



EXPECTED BEHAVIOR
-----------------------
Referring to item_id = xxxx and location_id = yyyy.

Hist_glob_prop = 12, and sum of sales in last 12 months in 4000. First sale in HGP range is Nov 2017, last sale is Jan 2018, hence glob_prop = 4000 / (number of months) = 4000/3

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Execute proport procedure
2. Check glob_prop and p1, p2, ... p12 results
3. Does not meet customer expectation


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.