Party Relationship Search Is Not Working Second Time

(Doc ID 2146644.1)

Last updated on JUNE 05, 2017

Applies to:

Oracle Receivables - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

On :  12.2.5 version, Customers Module

Upon adding a new row to the Party Relationships table in the Party Relationships tab of the customer information page, the user queries the party through the customer field. Once the results appear in the new screen, the search criteria is cleared and a blank search is conducted. Once the search executes, an exception error returns on the SQL being called to fetch the values.

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT * FROM (select HzPartiesEO.party_id,
       HzPartiesEO.party_name,
       HzPartiesEO.person_first_name,
       HzPartiesEO.person_last_name,
       HzPartiesEO.party_type,
       partytype.meaning type_lookup,
       HzPartiesEO.party_number,
       HzPartiesEO.tax_reference,
       HzPartiesEO.jgzz_fiscal_code taxpayer_id,
       HzPartiesEO.duns_number_c duns_number,
       HzPartiesEO.known_as,
       HzPartiesEO.known_as2,
       HzPartiesEO.known_as3,
       HzPartiesEO.known_as4,
       HzPartiesEO.known_as5,
       HzPartiesEO.organization_name_phonetic,
       HzPartiesEO.person_first_name_phonetic,
       HzPartiesEO.person_last_name_phonetic,
       ps.location_id,
       terr.territory_short_name country,
       HzPartiesEO.primary_phone_contact_pt_id,
       HzPartiesEO.primary_phone_country_code,
       HzPartiesEO.primary_phone_area_code,
       HzPartiesEO.primary_phone_number,
       HzPartiesEO.primary_phone_line_type,
       HzPartiesEO.primary_phone_extension,
       HzPartiesEO.email_address email,
       HzPartiesEO.url primary_url,
       certification.meaning certification_level_meaning,
       registrystatus.meaning registry_meaning,
       HzPartiesEO.status party_status,
  HZ_FORMAT_PUB.format_address(ps.location_id,null,null,', ')formatted_address,
  HzPartiesEO.category_code,
  HzPartiesEO.sic_code
from   hz_parties HzPartiesEO,
       hz_party_sites ps,
       fnd_territories_vl terr,
       fnd_lookup_values partytype,
       fnd_lookup_values certification
       ,fnd_lookup_values registrystatus
where  HzPartiesEO.party_type in ('PERSON', 'ORGANIZATION')
and    registrystatus.lookup_code = HzPartiesEO.status
and    ps.party_id(+) = HzPartiesEO.party_id
and    ps.identifying_address_flag(+) = 'Y'
and    terr.territory_code(+) = HzPartiesEO.country
and    partytype.view_application_id = 222
and    partytype.lookup_type = 'PARTY_TYPE'
and    partytype.language = userenv('LANG')
and    partytype.lookup_code = HzPartiesEO.party_type
and    certification.view_application_id(+) = 222
and    certification.lookup_type(+) = 'HZ_PARTY_CERT_LEVEL'
and    certification.language(+) = userenv('LANG')
and    certification.lookup_code(+) = HzPartiesEO.certification_level
and    registrystatus.lookup_type = 'HZ_CPUI_REGISTRY_STATUS'
and    registrystatus.language = userenv('LANG')
and    nvl(ps.status, 'A') = 'A') QRSLT  WHERE (PARTY_TYPE = :1 AND  PARTY_STATUS = :2)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Query customer on customer form.
2. Navigate to Party Relationships tab.
3. Query Customer in LOV.
4. Clear search and do blank search with GO button.

Changes

For HzPuiPartySearchRltsVO.xml Binding Style was Oracle Named which was causing raise of exception while querying with bind variables were not created as expected. The Binding Style was modified to Oracle Positional.

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