Pricing Attributes in a Pricing Formula Are Not Working When Using the Between Operator as it Results in Error "There are undefined Step Numbers in the formula"
(Doc ID 1624103.1)
Last updated on MARCH 01, 2024
Applies to:
Oracle Advanced Pricing  Version 11.5.10 and laterOracle Purchasing  Version 11.5.10 and later
Oracle Order Management  Version 11.5.10.0 and later
Information in this document applies to any platform.
Symptoms
In iprocurement when attempting to add an item to cart where an Advance Pricing formula has been setup using the Operator value as 'Between', the following error(s) occurs.
PO Log shows:
"l_price_status_text = Formula: XXXXXX Pricing Formula
There are undefined Step Numbers in the formula."
End of subprogram.
x_return_status = E
x_base_unit_price = 15,04
x_unit_price = 15,04
After Call Advanced Pricing API(GBPA)
";SQLERRM = ORA20001: An error has occured during Advanced Pricing Call. Please report following error to your System Administrator.
Formula: XXXXXX Pricing Formula
There are undefined Step Numbers in the formula."
";SQLERRM = ORA20001: An error has occured during Advanced Pricing Call. Please report following error to your System Administrator.
Formula: XXXXXX Pricing Formula
There are undefined Step Numbers in the formula."
OM/QP Log Shows:
In Routine:QP_PREQ_GRP.Formula_Processing
PRICING_PHASE_ID: 1
Processing Formula ID :<Formula_Id>
Line Index: 1
Start Formula...
Formula Line Type = PRA, Step = 1
l_pra_rec.value_from = 00000000000000000000015.04000000000000000000000000000000000000
Just after pra cur value from to number conversion
Formula Line Type = ML, Step = 2
Formula Line Type = NUM, Step = 3, Value = 0
Before populating plsql table of formula lines
After populating plsql table of formula lines
Before Calling  QP_BUILD_FORMULA_RULES.Get_Formula_Values
For Formula : nvl(1,3)+(nvl(1,3)*2)
After Calling  QP_BUILD_FORMULA_RULES.Get_Formula_Values
Return Status from Get_Formula_Values S
Formula is nvl(TO_NUMBER('15,04'),TO_NUMBER('0'))+(nvl(TO_NUMBER('15,04'),TO_NUMBER('0'))*TO_NUMBER(''))
Formula evaluated to
Formula Return Status S
##### Total Time in QP_FORMULA_PRICE_CALC_PVT(in sec) : 0 #####
Formula return status: E
Formula Base Price :
The issue can be reproduced at will with the following steps.
1. Setup the advance pricing setup by using the below values:
Operator = Between
Value From = XXX
Value To = ZZZ
2. Go to iProcurement and search for an item and add to cart.
Error
You have encountered an unexpected error. Please contact the System Administrator for assistance.
3. Change the Advance Pricing setup as below:
Operator: =
Value From: XYZ
Value To:
4. Go to iProcurement and search for the same item and add to cart.
5. Item is added to cart without any errors.
So the error is seen when Operator value is added as 'Between' but no error is seen when Operator value '='.
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 