Exception Error Occurs When Searching For Suppliers Using Keyword Search - JBO-27122 ORA-00600
(Doc ID 2744943.1)
Last updated on JUNE 09, 2021
Applies to:
Oracle iSupplier Portal - Version 12.2.5 and laterOracle Payables - Version 12.2.5 and later
Information in this document applies to any platform.
Symptoms
In Oracle iSupplier Portal, Oracle Purchasing, Release 12.2.5 and later, an error occurs when searching for suppliers using a keyword.
Due to this issue, users cannot search for suppliers by keyword values.
As a workaround there are many other methods of searching for suppliers that can be used, such as Supplier Name or Supplier Number.
Error
Error Page
You have encountered an unexpected error. Please contact your System Administrator for assistance.
Click here for exception details.
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (/*Standard Supplier - Ind (or) Foreign Ind */
select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ hp.party_name,
hp.party_id as party_id,
hp.duns_number_c as duns,
pv.vendor_id,
pv.vendor_name,
pv.individual_1099 as taxpayer_id,
pv.vat_registration_num as tax_reg_num,
pv.segment1,
pv.VENDOR_NAME_ALT as alternate_name,
pv.end_date_active as end_date_active,
pv.start_date_active as start_date_active,
pv.ONE_TIME_FLAG,
pv.VENDOR_TYPE_LOOKUP_CODE,
pv.PARENT_VENDOR_ID,
..
from hz_parties hp,
ap_suppliers pv,
ap_suppliers parent,
ap_terms_tl terms,
fnd_lookup_values pay_group,
AP_INCOME_TAX_TYPES aptt,
po_lookup_codes plc
where pv.party_id = hp.party_id AND
pv.employee_id is null AND
parent.vendor_id (+) = pv.parent_vendor_id and
pv.terms_id = terms.term_id (+)
..
union ALL
/*Standard Supplier - Not Ind and Not Foreign Ind */
select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ hp.party_name,
hp.party_id as party_id,
hp.duns_number_c as duns,
pv.vendor_id,
pv.vendor_name,
pv.num_1099 as taxpayer_id,
pv.vat_registration_num as tax_reg_num,
pv.segment1,
..
union all
/* Employee Type Supplier */
select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ hp.party_name,
hp.party_id as party_id,
hp.duns_number_c as duns,
pv.vendor_id,
pv.vendor_name,
null as taxpayer_id,
pv.vat_registration_num as tax_reg_num,
pv.segment1,
pv.VENDOR_NAME_ALT as alternate_name,
pv.end_date_active as end_date_active,
pv.start_date_active as start_date_active,
pv.ONE_TIME_FLAG,
pv.VENDOR_TYPE_LOOKUP_CODE,
pv.PARENT_VENDOR_ID,
NULL as parent_vendor_name,
pv.PAYMENT_PRIORITY,
NULL as parent_Segment1,
pv.TAX_REPORTING_NAME,
pv.terms_id,
..
from hz_parties hp,
ap_suppliers pv,
ap_terms_tl terms,
fnd_lookup_values pay_group,
AP_INCOME_TAX_TYPES aptt,
po_lookup_codes plc,
per_employees_x pecx
where pv.party_id = hp.party_id AND
pv.employee_id is not null AND
pv.party_id = pecx.party_id and
pv.employee_id = pecx.employee_id and
pv.terms_id = terms.term_id (+)
and terms.language (+) = userenv('LANG') and
terms.enabled_flag (+) ='Y' and
pv.pay_group_lookup_code = pay_group.lookup_code (+) and
pay_group.lookup_type (+)='PAY GROUP' and
pay_group.view_application_id (+)= 201 and
pay_group.language (+)=userenv('lang') and
pv.type_1099 = aptt.income_tax_type (+) and
pv.VENDOR_TYPE_LOOKUP_CODE = plc.LOOKUP_CODE (+) and
plc.lookup_type (+) = 'VENDOR TYPE' and
DECODE( NVL(FND_PROFILE.value('POS_SM_SDH_CONFIG'), 'NONE'), 'NONE', 'A', NVL(hp.status, 'A') ) != 'M') QRSLT WHERE (vendor_id in (select entity_id from pos_supplier_entity_data s
where contains(s.entity_data, '( (%%XXXXXX%) )' ) >0)) ORDER BY VENDOR_NAME ASC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:741)
at oracle.apps.pos.supplier.webui.SuppSummCO.processFormRequest(SuppSummCO.java:1307)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:924)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:424)
at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1548)
..
..
## Detail 0 ##
java.sql.SQLException: ORA-00600: xxxxxxx [17182], [0x7F65133A7110], [], [], [], [], [], [], [], [], [], []
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)
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:4602)
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:441)
at oracle.apps.pos.supplier.server.SuppSummAMImpl.execKeywordSearch(SuppSummAMImpl.java:392)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
..
..
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (/*Standard Supplier - Ind (or) Foreign Ind */
select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ hp.party_name,
hp.party_id as party_id,
hp.duns_number_c as duns,
pv.vendor_id,
pv.vendor_name,
pv.individual_1099 as taxpayer_id,
pv.vat_registration_num as tax_reg_num,
pv.segment1,
pv.VENDOR_NAME_ALT as alternate_name,
pv.end_date_active as end_date_active,
pv.start_date_active as start_date_active,
pv.ONE_TIME_FLAG,
pv.VENDOR_TYPE_LOOKUP_CODE,
pv.PARENT_VENDOR_ID,
..
from hz_parties hp,
ap_suppliers pv,
ap_suppliers parent,
ap_terms_tl terms,
fnd_lookup_values pay_group,
AP_INCOME_TAX_TYPES aptt,
po_lookup_codes plc
where pv.party_id = hp.party_id AND
pv.employee_id is null AND
parent.vendor_id (+) = pv.parent_vendor_id and
pv.terms_id = terms.term_id (+)
..
union ALL
/*Standard Supplier - Not Ind and Not Foreign Ind */
select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ hp.party_name,
hp.party_id as party_id,
hp.duns_number_c as duns,
pv.vendor_id,
pv.vendor_name,
pv.num_1099 as taxpayer_id,
pv.vat_registration_num as tax_reg_num,
pv.segment1,
..
union all
/* Employee Type Supplier */
select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ hp.party_name,
hp.party_id as party_id,
hp.duns_number_c as duns,
pv.vendor_id,
pv.vendor_name,
null as taxpayer_id,
pv.vat_registration_num as tax_reg_num,
pv.segment1,
pv.VENDOR_NAME_ALT as alternate_name,
pv.end_date_active as end_date_active,
pv.start_date_active as start_date_active,
pv.ONE_TIME_FLAG,
pv.VENDOR_TYPE_LOOKUP_CODE,
pv.PARENT_VENDOR_ID,
NULL as parent_vendor_name,
pv.PAYMENT_PRIORITY,
NULL as parent_Segment1,
pv.TAX_REPORTING_NAME,
pv.terms_id,
..
from hz_parties hp,
ap_suppliers pv,
ap_terms_tl terms,
fnd_lookup_values pay_group,
AP_INCOME_TAX_TYPES aptt,
po_lookup_codes plc,
per_employees_x pecx
where pv.party_id = hp.party_id AND
pv.employee_id is not null AND
pv.party_id = pecx.party_id and
pv.employee_id = pecx.employee_id and
pv.terms_id = terms.term_id (+)
and terms.language (+) = userenv('LANG') and
terms.enabled_flag (+) ='Y' and
pv.pay_group_lookup_code = pay_group.lookup_code (+) and
pay_group.lookup_type (+)='PAY GROUP' and
pay_group.view_application_id (+)= 201 and
pay_group.language (+)=userenv('lang') and
pv.type_1099 = aptt.income_tax_type (+) and
pv.VENDOR_TYPE_LOOKUP_CODE = plc.LOOKUP_CODE (+) and
plc.lookup_type (+) = 'VENDOR TYPE' and
DECODE( NVL(FND_PROFILE.value('POS_SM_SDH_CONFIG'), 'NONE'), 'NONE', 'A', NVL(hp.status, 'A') ) != 'M') QRSLT WHERE (vendor_id in (select entity_id from pos_supplier_entity_data s
where contains(s.entity_data, '( (%%XXXXXX%) )' ) >0)) ORDER BY VENDOR_NAME ASC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:741)
at oracle.apps.pos.supplier.webui.SuppSummCO.processFormRequest(SuppSummCO.java:1307)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:924)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:424)
at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1548)
..
..
## Detail 0 ##
java.sql.SQLException: ORA-00600: xxxxxxx [17182], [0x7F65133A7110], [], [], [], [], [], [], [], [], [], []
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)
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:4602)
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:441)
at oracle.apps.pos.supplier.server.SuppSummAMImpl.execKeywordSearch(SuppSummAMImpl.java:392)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
..
..
Steps To Reproduce
The issue can be reproduced at will with the following steps:
1. Log in to Oracle Applications.
2. Navigate to a Purchasing responsibility > Supply Base > Suppliers.
3. On the Suppliers main page, in the Supplier Search region, there is a field "Keyword". Enter any value in the Keyword field, and click the Go button. The error occurs at this point.
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |