JBO-27122 Exception Using Hub Advanced Search (Doc ID 2009629.1)

Last updated on JULY 15, 2015

Applies to:

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

Symptoms

On : 12.1.3 version, Supplier Advanced Search

SQL Exception using Hub Advanced Search

Adding duplicate criteria in a multi-element Supplier Hub Advanced Search results in SQL exception.
Due to this issue, users cannot utilize hub for supplier maintenance.

Error
 

Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT AP.VENDOR_ID AS VENDOR_ID, AP.PARTY_ID AS PARTY_ID, AP.SEGMENT1 AS SUPP_NUMBER, AP.VENDOR_NAME AS SUPP_NAME, AP.EMPLOYEE_ID AS EMPLOYEE_ID, (AP.VENDOR_NAME) AS AP_VENDOR_NAME, (AP.SEGMENT1) AS AP_SEGMENT1, (AP.VENDOR_NAME_ALT) AS AP_VENDOR_NAME_ALT, (AP.STANDARD_INDUSTRY_CLASS) AS AP_STANDARD_INDUSTRY_CLASS, (AP.NI_NUMBER) AS AP_NI_NUMBER, (SELECT meaning
FROM fnd_lookup_values_vl
WHERE fnd_lookup_values_vl.lookup_type = 'VENDOR TYPE'
AND enabled_flag = 'Y'
AND sysdate BETWEEN nvl(start_date_active, sysdate -1)
AND nvl(end_date_active, sysdate + 1)
AND lookup_code = AP.VENDOR_TYPE_LOOKUP_CODE) AS AP_VENDOR_TYPE_LOOKUP_CODE, (select vendor_name
from ap_suppliers
where vendor_id = AP.PARENT_VENDOR_ID) AS AP_PARENT_ID_FOR_NAME, (select segment1
from ap_suppliers
where vendor_id = AP.PARENT_VENDOR_ID) AS AP_PARENT_ID_FOR_NUM FROM AP_SUPPLIERS AP WHERE ( 1 = 1 ) AND (( UPPER(AP.SEGMENT1) like :0) OR ( UPPER(AP.SEGMENT1) like :1) ) AND ( UPPER(AP.VENDOR_NAME_ALT) like :2) ) AND AP.EMPLOYEE_ID is NULL
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:620)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
...
...
## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4566)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:900)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:814)
at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:808)
at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)
at oracle.apps.ego.common.server.EgoViewObjectImpl.executeQuery(EgoViewObjectImpl.java:38)
at oracle.apps.pos.sdh.ext.advsearch.webui.SuppAdvSearchHelper.prepareSearchResults(SuppAdvSearchHelper.java:1287)
at oracle.apps.pos.sdh.ext.advsearch.webui.SuppAdvSearchResultTableCO.processRequest(SuppAdvSearchResultTableCO.java:117)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)
...
...




Steps To Reproduce
-----------------------
1. Log in and access Supplier Data Librarian
2. Navigate: Home > Advanced Search
3. Create personalized criteria set.
4. Run and encounter reported error.
 

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