ORA-00904 BQ.COMMITTED_AMOUNT And JBO-27122 Clicking On Shopping Lists After Patch

(Doc ID 2073334.1)

Last updated on JULY 27, 2016

Applies to:

Oracle iProcurement - Version 12.1.3 and later
Information in this document applies to any platform.


After <Patch 21947965>:R12.ICX.B Oracle iProcurement Recommended Patch Collection (Oct. 2015) was applied, shopping list is giving the following error:

Error Message:

JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM ( SELECT bq.relevance_score Relevance,bq.un_number UN_NUMBER,bq.hazard_class HAZARD_CLASS,'l#' || bq.inventory_item_id || '#' || bq.po_line_id ||'#' || bq.req_template_name || '#' ||bq.req_template_line_num || '#' || :ORG_ID_KEY1 ItemKey,bq.source_type SourceType,DECODE(bq.source_type,'TEMPLATE',decode(least(length(bq.req_template_name) , 20),20,substr(bq.req_template_name,1,17) ||'...',bq.req_template_name),'INTERNAL_TEMPLATE',decode(least(length(bq.req_template_name) , 20),20,substr(bq.req_template_name,1,17) ||'...',bq.req_template_name),'QUOTATION',ICX_CAT_UTIL_PVT.get_message('ICX_CAT_QUOTATION_SOURCE','NUMBER',bq.document_number)
icx.icatalog.shopping.server.PublicListVOBuilder alias = PoLineId
icx.icatalog.shopping.server.PublicListVOBuilder colExp = bq.document_number
icx.por.req.server.RequisitionAMImpl Throwable 0 = java.sql.SQLSyntaxErrorException: ORA-00904: "BQ"."COMMITTED_AMOUNT": invalid identifier
icx.icatalog.shopping.server.PublicListVOBuilder alias = DocumentNumber
icx.icatalog.shopping.server.PublicListVOBuilder colExp = bq.line_num
icx.icatalog.shopping.server.PublicListVOBuilder alias = DocumentLineNumber
icx.icatalog.shopping.server.PublicListVOBuilder colExp = bq.min_release_amount
icx.icatalog.shopping.server.PublicListVOBuilder alias = MIN_RELEASE_AMOUNT
icx.icatalog.shopping.server.PublicListVOBuilder colExp = bq.committed_amount
icx.icatalog.shopping.server.PublicListVOBuilder alias = COMMITTED_AMOUNT
icx.icatalog.shopping.server.PublicListVOBuilder colExp = decode(bq.allow_price_override_flag, 'Y', (select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y'), (select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'N'))
icx.icatalog.shopping.server.PublicListVOBuilder alias = ALLOW_PRICE_OVERRIDE_FLAG

The issue can be reproduced at will with the following steps:
1. Create a new template in the instance: Purchasing Super User > Setup > Purchasing > Requisition Templates
2. Go to  iProcurement Page
3. Click on Shopping lists
4. Select any template or a new one just created.
5. See the error


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