Cannot Search By Policy Number In The Suspense Unified Search Screen (Doc ID 2773323.1)

Last updated on MAY 03, 2021

Applies to:

Symptoms

On : 11.2.0.23 version, General

Cannot search by policy number in the suspense unified search screen. Can search by the suspense number but not any other non integer field.

ERROR
-----------------------
Error Message : javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number Error Code: 1722 Call: SELECT COUNT( DISTINCT AsSuspense.SuspenseGUID ) Count FROM AsSuspense LEFT OUTER JOIN AsSuspenseField suspenseField ON suspenseField.SuspenseGUID = AsSuspense.SuspenseGUID AND suspenseField.FieldName = ? WHERE EXISTS ( SELECT AsCompany.CompanyGUID FROM AsCompany JOIN AsRole ON AsRole.ClientGUID = AsCompany.ClientGUID AND AsRole.companyGuid = cast(? as CHAR(36)) AND AsRole.RoleCode = '02' WHERE AsCompany.CompanyGUID = AsSuspense.CompanyGUID ) AND AsSuspense.SuspenseNumber = '' OR AsSuspense.PolicyNumber = '' bind => [FromPolicyNumber, E383342F-9088-402C-9743-8D96A3392735] Query: DataReadQuery(sql=" SELECT COUNT( DISTINCT AsSuspense.SuspenseGUID ) Count FROM AsSuspense LEFT OUTER JOIN AsSuspenseField suspenseField ON suspenseField.SuspenseGUID = AsSuspense.SuspenseGUID AND suspenseField.FieldName = ? WHERE EXISTS ( SELECT AsCompany.CompanyGUID FROM AsCompany JOIN AsRole ON AsRole.ClientGUID = AsCompany.ClientGUID AND AsRole.companyGuid = cast(? as CHAR(36)) AND AsRole.RoleCode = '02' WHERE AsCompany.CompanyGUID = AsSuspense.CompanyGUID ) AND AsSuspense.SuspenseNumber = '' OR AsSuspense.PolicyNumber = '' ")

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) Configure the Unified Search BR a shown below
2) Attempt to search via a policy number
3) See stack

Cause

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