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 FEBRUARY 03, 2017

Applies to:

Oracle Purchasing - Version 11.5.10 and later
Oracle Advanced Pricing - 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

On Applications 12.1.1 :

In iprocurement when attempting to add an item to cart where advance pricing formula has been setup using the Operator value as 'Between', the following error occurs.

-- Error


PO Log shows:
---------------------
"l_price_status_text = Formula: SITA HP 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: SITA HP 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: SITA HP 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 :7036
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 :


-- Steps
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 = GBA
Value To = GBZ

2. Go to iProcurement and search for an item and add to cart.
- Error is seen:
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: GBC
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 seen if Operator value foe Advance Pricing formula is used as '='.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms