Search By Partial Employee Name In iAssets Fails With ORA-00904 EMPLOYEE_NAME (Doc ID 2203436.1)

Last updated on JANUARY 10, 2017

Applies to:

Oracle iAssets - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms


When attempting to search by partial employee name in iAssets, the following error occurs.

Error Page
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT p.person_id AS PERSON_ID,
  p.full_name AS PERSON_NAME,
  p.employee_number EMPLOYEE_NUMBER,
  bg.name ORGANIZATION_NAME
  FROM per_all_people_f p,
  per_business_groups bg,
per_periods_of_service s
  WHERE bg.business_group_id = p.business_group_id
  AND p.person_id = s.person_id
  AND sysdate BETWEEN p.effective_start_date AND p.effective_end_date
  AND NVL(s.actual_termination_date,sysdate) >= sysdate) QRSLT WHERE (( UPPER(EMPLOYEE_NAME) like UPPER( :1) ))

## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00904: "EMPLOYEE_NAME": invalid identifier



The issue can be reproduced at will with the following steps:

==============================================
1. Under Asset Search page for iAssets User, enter an employee name search that includes wildcards, such as %ich%
2. The following message is received:

Error Page
You have encountered an unexpected error. Please contact the System Administrator for assistance.
Click here for exception details.

3. Clicking the link for 'here' returns the following:
Error Page
Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT p.person_id AS PERSON_ID,
  p.full_name AS PERSON_NAME,
  p.employee_number EMPLOYEE_NUMBER,
  bg.name ORGANIZATION_NAME
  FROM per_all_people_f p,
  per_business_groups bg,
per_periods_of_service s
  WHERE bg.business_group_id = p.business_group_id
  AND p.person_id = s.person_id
  AND sysdate BETWEEN p.effective_start_date AND p.effective_end_date
  AND NVL(s.actual_termination_date,sysdate) >= sysdate) QRSLT WHERE (( UPPER(EMPLOYEE_NAME) like UPPER( :1) ))

## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00904: "EMPLOYEE_NAME": invalid identifier


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