My Oracle Support Banner

JBO-27122 When Searching Address By Address Details In ISupplier Portal (Doc ID 2482790.1)

Last updated on JANUARY 16, 2020

Applies to:

Oracle iSupplier Portal - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

On iSupplier Portal 12.2.4 when attempting to search by address details in Profile Management, the following error occurs:

ERROR
-----------------------
Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement : SELECT * FROM (select hps.party_site_id, hps.party_site_name
, hzl.address1, hzl.address2, hzl.address3, hzl.address4
, hzl.city, hzl.state, hzl.province, hzl.county
, hzl.country, hzl.postal_plus4_code, hzl.postal_code
, hzl.location_id, to_number(null) as address_request_id
from hz_party_sites hps, hz_locations hzl
where hps.party_id = :1
and hps.status = 'A'
and hps.location_id = hzl.location_id
and nvl(hps.end_date_active, sysdate) >= sysdate
and hps.party_site_id not in
( select party_site_id from pos_address_requests
where request_status = 'PENDING'
and request_type IN ('ADD', 'UPDATE') and party_site_id is not null)
and hps.party_site_id not in
( select -1 from pos_cont_addr_requests
where (contact_party_id = :2 or contact_req_id = :3 )
and request_type IN ('ADD', 'UPDATE')
and request_status = 'PENDING'
and party_site_id is not null )

union

select to_number(null) as party_site_id, party_site_name
, par.address_line1, par.address_line2, par.address_line3, par.address_line4
, par.city, par.state, par.province, par.county
, par.country, null as postal_plus4_code, par.postal_code
, to_number(null) as location_id, address_request_id
from pos_address_requests par, pos_supplier_mappings psm
where psm.mapping_id = par.mapping_id
and psm.party_id = :4
and par.REQUEST_STATUS = 'PENDING'
and par.request_type in ('ADD', 'UPDATE' )
and address_request_id not in
( select -1 from pos_cont_addr_requests
where (contact_party_id = :5 or contact_req_id = :6 )
and address_req_id is not null )) QRSLT WHERE (( UPPER(VIEW_ATTR) like UPPER(:7) AND (VIEW_ATTR like :8 OR VIEW_ATTR like :9 OR VIEW_ATTR like :10 OR VIEW_ATTR like :11))) ORDER BY PARTY_SITE_NAME ASC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:3210)
...
## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00904: "VIEW_ATTR": invalid identifier

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)
...



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Go to iSupplier Portal > Admin > Profile Management > Contact Directory
2. Click On addresses link for a contact
3. Click Add another row and search for an address
4. Choose Search by Address Details and enter any criteria (such as "Ha%") > Click Go
5. Error occurs


Due to this issue, users cannot search by address details

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
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.