Interviews Not Working On IRec, Error: ORA-01427: single-row subquery returns more than one row

(Doc ID 2268688.1)

Last updated on AUGUST 10, 2017

Applies to:

Oracle iRecruitment - Version 12.1 HRMS RUP9 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Candidate Management

When attempting to create an interview the following error occurs.


Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: select distinct ppf.full_name as EmpName,
  ppf.person_id as EmpId,
  nvl(rec.full_name,'') as RecName,
  nvl(rec.person_id,0) as RecId,
  paf.assignment_id,
  vac.vacancy_id,
  vac.Business_group_id as BGId,
  (select ephone.phone_number
  from per_phones ephone
  where ephone.parent_id in
  (select person_id from per_all_people_f where party_id=ppf.party_id)
  AND ephone.PARENT_TABLE(+)='PER_ALL_PEOPLE_F'
  AND ephone.PHONE_TYPE(+)='H1'
AND trunc(sysdate) between nvl(ephone.date_from,trunc(sysdate)) and nvl(ephone.date_to,trunc(sysdate)) ) as Emp_Phone,
  (select rphone.phone_number
  from per_phones rphone
  where rphone.Parent_ID in
  (select person_id from per_all_people_f where party_id=rec.party_id)
  AND rphone.PARENT_TABLE(+)='PER_ALL_PEOPLE_F'
  AND rphone.PHONE_TYPE(+)='W1'
  AND trunc(sysdate) between nvl(rphone.date_from,trunc(sysdate)) and nvl(rphone.date_to,trunc(sysdate)) ) as Rec_Phone
From per_all_people_f ppf,
  per_all_people_f rec,
  per_all_vacancies vac,
  per_all_assignments_f paf
where vac.vacancy_id=paf.vacancy_id
and ppf.person_id=paf.person_id
and rec.person_id(+)=vac.recruiter_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and (vac.recruiter_id is null or trunc(sysdate) between rec.effective_start_date and rec.effective_end_date)
and paf.assignment_id=:1
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:764)
at oracle.apps.irc.interview.webui.MassInterviewCreateCO.processRequest(MassInterviewCreateCO.java:64)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1183)
at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:976)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:943)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:663)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(OAFormBean.java:385)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:976)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:943)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:663)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2681)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1963)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:557)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:439)
at _OA._jspService(_OA.java:213)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:734)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:308)
at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:68)
at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:214)
at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:219)
at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:407)
at _OA._jspService(_OA.java:222)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:429)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:911)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:745)
## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row
 



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

1. iRecruitment
2. Active Applicants
3. Select Applicant
4. Click on Create Interview

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