Not Able To Create / Open Existing Offer ORACLE.JBO.SQL.StmtException: JBO-27122 (Doc ID 2810085.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

On : 12.2.6 version, Offers

When Users are clicking on existing Offers or trying to create new Offers in Trade Management, the below error is seen. Issue seen after adding a new Product Context flexfield that is associated to an Independent Valueset that was implemented.  If table validate valueset is used, then no error.


ERROR
---------
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27120: SQL error during query execution. Statement: select flex_value FLEX_VALUE , flex_value FLEX_DISPLAY , description FLEX_DESCRIPTION from fnd_flex_values_vl where (fnd_flex_values_vl.flex_value_set_id = :1) and enabled_flag = 'Y' and nvl(fnd_date.canonical_to_date(:2), trunc(SysDate)) between nvl(trunc(start_date_active), nvl(fnd_date.canonical_to_date(:3), trunc(SysDate))) and nvl(trunc(end_date_active), nvl(fnd_date.canonical_to_date(:4), trunc(SysDate))) and (upper(flex_value ) like :L1 and (flex_value like :L2 or flex_value like :L3 or flex_value like :L4 or flex_value like :L5)) order by FLEX_DISPLAY


Steps:
---------
1. Navigate to Oracle Trade Management User responsibility
2. Select Trade Planning > Offers
3. Select an existing offer, receive error

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.