Using BLIND Search in the ASSET NUMBER page In Maintenance Super User Responsibility gives error: oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL (Doc ID 960850.1)

Last updated on OCTOBER 28, 2016

Applies to:

Oracle Enterprise Asset Management - Version 11.5.10 and later
Information in this document applies to any platform.
***Checked for relevance on 15-May-2011***


Symptoms

On 11.5.10 in Production:
On the asset search page, if the user does not enter any search criteria and tries to perform a BLIND search in the Maintenance Super User responsibility (thru Simple or Advanced Search), the following error occurs:

ERROR
-------
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.

Statement: SELECT * FROM (SELECT meanav.concatenated_segments,
meanav.serial_number,
CASE
when (length(descriptive_text) > 30)
THEN
concat(substr(descriptive_text, 1, 30), '...')
ELSE
descriptive_text
END
AS descriptive_text,
category_name as asset_category,
asset_criticality,
wip_accounting_class_code,
eam_location,
asset_number,
maintainable_flag,
nvl(meanav.network_asset_flag,
'N') as network_asset_flag,
meanav.owning_department,
meanav.current_organization_id,
meanav.inventory_item_id,
'N' select_flag,
meanav.asset_criticality_code,
meanav.fixed_asset_tag as fixed_asset_no,
meanav.gen_object_id,
meanav.eam_item_type,
meanav.maintenance_object_id,
meanav.maintenance_object_type,
pn_location_id,
meaavd.concatenated_segments as Attribute,
pl.location_code,
meanav.prod_organization_id,
meanav.equipment_item_id,
meanav.eqp_serial_number,
msi.concatenated_segments as EquipmentItem,
mp.organization_code,
pl.location_alias
FROM mtl_eam_asset_numbers_v meanav,
fa_additions_b fa,
mtl_eam_asset_attr_values_dfv meaavd,
mtl_eam_asset_attr_values meaav,
mtl_eam_asset_attr_groups meaag,
pn_locations_all pl,
mtl_system_items_b_kfv msi,
mtl_parameters mp
WHERE
meanav.fa_asset_id = fa.asset_id(+) AND
meanav.current_organization_id = meaav.organization_id(+) AND
meanav.serial_number = meaav.serial_number(+) AND
meanav.inventory_item_id = meaav.inventory_item_id(+) AND
meaav.rowid = meaavd.row_id(+) AND
meaav.association_id = meaag.association_id(+) AND
meaav.organization_id = meaag.organization_id(+) AND
(meaag.enabled_flag = :1 or meaag.enabled_flag is null) AND
meanav.pn_location_id = pl.location_id(+) AND
meanav.prod_organization_id = mp.organization_id(+) AND
meanav.prod_organization_id = msi.organization_id(+) AND
meanav.equipment_item_id = msi.inventory_item_id(+) AND
meanav.eam_item_type = 1
ORDER BY meanav.serial_number) QRSLT WHERE (current_organization_id = :2 AND (
UPPER(NETWORK_ASSET_FLAG) = :3 AND (NETWORK_ASSET_FLAG like :4 OR NETWORK_ASSET_FLAG like :5
OR NETWORK_ASSET_FLAG like :6 OR NETWORK_ASSET_FLAG like :7)))
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:888)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2637)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1659)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:497)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:418)
at _oa__html._OA._jspService(_OA.java:88)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)

## Detail 0 ##
java.sql.SQLException: ORA-00942: table or view does not exist

Steps To Reproduce:
---------------------
The issue can be reproduced at will with the following steps:
1. Maintenance Super User => Asset Numbers
2. Simple Search: Asset Number: %
3. Error shows up.

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