After Applying BUG 25403529 in PUM22 - HRS_DUP_PRC On SQL SERVER Gets Error 'The ORDER BY clause is invalid...' (Doc ID 2270735.1)

Last updated on MAY 26, 2017

Applies to:

PeopleSoft Enterprise HCM Talent Acquisition Manager - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Other Issue with Talent Acquisition Manager
SQL Server database
PT8.55.15
HCM 9.2 PUM 22

Microsoft SQL Server Error in delivered App Engine HRS_DUPE_PRC

When running App Engine HRS_DUPE_PRC to identify duplicate applicants, there is an SQL error in Function CreateSQL


ERROR

07:20:52.721 0.002000 Cur#3.5072.HRDMOA RC=8603 Dur=0.002000 COM Stmt=SELECT APPSET.APP FROM (SELECT COUNT(DISTINCT A.HRS_PERSON_ID), MIN(A.HRS_PERSON_ID) AS APP ,B.FIRST_NAME_SRCH,B.LAST_NAME_SRCH,C.ADDRESS1,C.CITY,C.STATE,D.COUNTRY_CODE, D.PHONE, D.EXTENSION,E.EMAIL_ADDR FROM PS_HRS_APPLICANT A ,PS_HRS_APP_NAMES B ,PS_HRS_APP_ADDRESS C ,PS_HRS_APP_PHONE D ,PS_HRS_APP_EMAIL E WHERE A.STATUS_CODE='010' AND A.HRS_PERSON_ID = B.HRS_PERSON_ID AND A.HRS_PERSON_ID = C.HRS_PERSON_ID AND A.HRS_PERSON_ID = D.HRS_PERSON_ID AND A.HRS_PERSON_ID = E.HRS_PERSON_ID GROUP BY B.FIRST_NAME_SRCH,B.LAST_NAME_SRCH,C.ADDRESS1,C.CITY,C.STATE,D.COUNTRY_CODE, D.PHONE, D.EXTENSION,E.EMAIL_ADDR HAVING COUNT(DISTINCT A.HRS_PERSON_ID) > 1 ORDER BY APP) APPSET
527 07:20:52.722 0.000000 Cur#3.5072.HRDMOA RC=0 Dur=0.000000 EPO error pos=0
528 07:20:52.723 0.001000 Cur#3.5072.HRDMOA RC=0 Dur=0.000000 RCD rtncd=8603
529 07:20:52.724 0.001000 Cur#0.5072.notSamTran RC=0 Dur=0.000000 XER rtncd=-1506856896 msg=[Microsoft][SQL Server Native Client 11.0][SQL Server]Executing SQL directly; no cursor.
[Microsoft][SQL Server Native Client 11.0][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table express

STEPS

The issue can be reproduced at will with the following steps:
1. HRS_DUP_PRC -- Recruiting > Administration >Process Duplicate Applicants
2. Click the Create New Search
3. Select any search criteria and click the Run
4. Process goes to No Success with the following error --same as customer reported



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