Credit Aggregate Summary Page Runs Into Error JBO-27122: SQL error during statement preparation (Doc ID 2133603.1)

Last updated on MAY 18, 2016

Applies to:

Oracle Customers Online - Version 12.1.3 and later
Oracle Credit Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Customer Tab

While clicking on the button "View Aggregate Credit Summary" for multiple customers the page runs into the following error message:

ERROR
-----------------------
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: select
hzp.party_id PartyId,
hzp.party_name PartyName,
hzp.party_number PartyNumber,
null AccountName,
null AccountNumber,
ARH_ADDR_PKG.FORMAT_ADDRESS(
null,
HZP.ADDRESS1,
HZP.ADDRESS2,
HZP.ADDRESS3,
HZP.ADDRESS4,
HZP.CITY,
HZP.COUNTY,
HZP.STATE,
HZP.PROVINCE,
HZP.POSTAL_CODE,
HZP.COUNTRY ) ConcAddress,
null Location,
hzp.duns_number_c DunsNumber,
con.party_name CreditContact,
(phone_contact.phone_country_code||
phone_contact.phone_area_code||
phone_contact.phone_number||
phone_contact.phone_extension) PhoneNumber,
(fax_contact.phone_country_code||
fax_contact.phone_area_code||
fax_contact.phone_number||
fax_contact.phone_extension) FaxNumber,
email_contact.email_address EmailAddress,
per.full_name CreditAnalyst,
arpt_sql_func_util.get_lookup_meaning(
'AR_CMGT_CREDIT_CLASSIFICATION',req.credit_classification) CreditClasification,
cf.limit_currency CreditCurrency,
to_number(dp.data_point_value) ReceivableBalance
FROM hz_parties con,
hz_parties hzp,
hz_contact_points phone_contact,
hz_contact_points fax_contact,
hz_contact_points email_contact,
JTF_RS_RESOURCE_EXTNS rsextns,
PER_ALL_PEOPLE_F per,
ar_cmgt_cf_dtls dp,
ar_cmgt_credit_requests req,
ar_cmgt_case_folders cf
WHERE cf.party_id = hzp.party_id
AND req.credit_analyst_id = rsextns.resource_id (+)
AND rsextns.source_id = per.person_id(+)
AND trunc(sysdate) between per.effective_start_date(+)
and per.effective_end_date(+)
AND req.contact_party_id = con.party_id (+)
AND cf.credit_request_id = req.credit_request_id
AND cf.case_folder_id = dp.case_folder_id
AND dp.data_point_id = 34 --Receivable balance
AND con.party_id = phone_contact.owner_table_id (+)
AND cf.type='DATA'
AND phone_contact.owner_table_name (+) = 'HZ_PARTIES'
AND phone_contact.primary_flag (+) = 'Y'
AND phone_contact.contact_point_type (+) = 'PHONE'
AND con.party_id = phone_contact.owner_table_id (+)
AND fax_contact.owner_table_name (+) = 'HZ_PARTIES'
AND fax_contact.primary_flag (+) = 'Y'
AND fax_contact.contact_point_type (+) = 'FAX'
AND con.party_id = fax_contact.owner_table_id (+)
AND email_contact.owner_table_name (+) = 'HZ_PARTIES'
AND email_contact.primary_flag (+) = 'Y'
AND email_contact.contact_point_type (+) = 'EMAIL'
AND con.party_id = email_contact.owner_table_id (+)
AND ((cf.party_id = :1) AND (cf.cust_account_id = :2) AND (cf.site_use_id = :3))
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Responsibility: Customers Online Data Librarian
2. Navigation: Customers Tab > Organizations
3. Query an existing customer > Go to Accounts
4. Click on View Aggregate Credit Summary button and the error occurs

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