OEXPRAVA: Item Price Does Not Show Up In Pricing and Availibility Form Due to Log Showing Error in price break calculation: ORA-06503: PL/SQL: Function returned without value ORA-06512: at "APPS.OZF_VOLUME_CALCULATION_PUB" (Doc ID 1533846.1)

Last updated on AUGUST 02, 2017

Applies to:

Oracle Order Management - Version 12.1.3 and later
Oracle Trade Management - Version 12.1.3 and later
Oracle iStore - Version 12.1.2 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, , Form OEXPRAVA

When attempting to use pricing & availability form, we are not able to see price in the Summary Tab.
When we try to query for a Price for an item for a customer, results come back with blank value in Unit Selling Price, Unit List Price and Price List name.
The following errors occur in the Order Management (OM) Log file.

ERROR
-----------------------
In Procedure : Get_Satisfied_Range
  continuous_flag = Y
  prorated_flag = N
  accum context, attribute, etc. not cached
  accum attribute = PRICING_ATTRIBUTE19
  line index = 2
  accum value not passed on request line.
 * Calling Custom API for accumulation *
  request line attr(s):
  1: line index 2, PRODUCT, ITEM, PRICING_ATTRIBUTE2, 5766,
  2: line index 2, QUALIFIER, CUSTOMER, QUALIFIER_ATTRIBUTE16, 17810, 10
  need to cache line_id, price_eff_date!
  - order_id (-1244), prev_order_id ()
  - line_index (2), prev_line_index ()
  order ID -1244, line ID -1244
Error in price break calculation: ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "APPS.OZF_VOLUME_CALCULATION_PUB", line 1868
ORA-06512: at "APPS.QP_TM_RUNTIME_SOURCE", line 17
ORA-06512: at "APPS.QP_CALCULATE_PRICE_PUB", line 1687
ORA-01403: no data found
Error in calculate_priceORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_CALCULATE_PRICE_PUB", line 2298
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "APPS.OZF_VOLUME_CALCULATION_PUB", line 1868
ORA-06512: at "APPS.QP_TM_RUNTIME_SOURCE", line 17
ORA-06512: at "APPS.QP_CALCULATE_PRICE_PUB", line 1687
ORA-01403: no data found
Exception Price Request: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_CLEANUP_ADJUSTMENTS_PVT", line 3597
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_PREQ_PUB", line 7095
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_CALCULATE_PRICE_PUB", line 2298
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "APPS.OZF_VOLUME_CALCULATION_PUB", line 1868
ORA-06512: at
Exception Price Request: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_PREQ_PUB", line 14403
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_CLEANUP_ADJUSTMENTS_PVT", line 3597
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_PREQ_PUB", line 7095
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_CALCULATE_PRICE_PUB", line 2298
O
PRICE ITEM EXCEPTION WHEN OTHERS CODE=-6502 MESSAGE=ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_PREQ_PUB", line 14641
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_PREQ_PUB", line 14403
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_CLEANUP_ADJUSTMENTS_PVT", line 3597
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.QP_PREQ_PUB", line 7095
ORA-06502:
 Reading the line record=1
line_type_code= price uom= curr= unit price= adj price= count=1 status code= text=
LEAVING OE_MSG_PUB.INITIALIZE
Item Validation Org= 107
Calling Cost Currency=USD SOB Currency=USD item_id=49585 org_id=107 con type=

NOTES
----------
Recently applied patch 16061437:R12.ONT.B
Issue is only present in production environment.

This issue may also occur while adding an item to a cart in iStore
1. Create an Offer in Trade Management with Customer name as the Marketing eligibility. You can use any qualifiers
2. Activate the offer
3. After the offer is active the pricing modifier gets created
4. Create an Order in iStore for the same customer which is setup in the Marketing eligibility.
5. When adding an item in the cart the following message is received
    ORA-01403: no data found ORA-01403: no data found in Package OZF_VOLUME_CALCULATION_PUB Procedure get_numeric_attribute_value
    Routine :QP_PREQ_PUB.Calculate_price ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at "APPS.QP_CALCULATE_PRICE_PUB", line 2298
    ORA-06503: PL/SQL: Function returned without value

 

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Responsibility: Order Management Super User or equivalent
2. Navigate to: Orders, Returns > Pricing and Availability
3. Enter details and click on Price and Availability button.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Outside Rep Users from are unable to query price for products and that is impacting their order taking process. This directly impacts our business.

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