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:

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 = ORA-20001: 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 = ORA-20001: 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

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