JBO-27122 Exception Error raised Upon Opening Candidate Profile

(Doc ID 2362433.1)

Last updated on FEBRUARY 26, 2018

Applies to:

Oracle iRecruitment - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.4 version, Candidate Management

When attempting to click on the candidate name to open the detail page the following error occurs.

ERROR
-----------------------

Error Page

Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT PAF.ASSIGNMENT_ID, PAF.EFFECTIVE_START_DATE, PAF.EFFECTIVE_END_DATE, PAF.ASS_ATTRIBUTE_CATEGORY, PAF.ASS_ATTRIBUTE1, PAF.ASS_ATTRIBUTE2, PAF.ASS_ATTRIBUTE3, PAF.ASS_ATTRIBUTE4, PAF.ASS_ATTRIBUTE5, PAF.ASS_ATTRIBUTE6, PAF.ASS_ATTRIBUTE7, PAF.ASS_ATTRIBUTE8, PAF.ASS_ATTRIBUTE9, PAF.ASS_ATTRIBUTE10, PAF.ASS_ATTRIBUTE11, PAF.ASS_ATTRIBUTE12, PAF.ASS_ATTRIBUTE13, PAF.ASS_ATTRIBUTE14, PAF.ASS_ATTRIBUTE15, PAF.ASS_ATTRIBUTE16, PAF.ASS_ATTRIBUTE17, PAF.ASS_ATTRIBUTE18, PAF.ASS_ATTRIBUTE19, PAF.ASS_ATTRIBUTE20, PAF.ASS_ATTRIBUTE21, PAF.ASS_ATTRIBUTE22, PAF.ASS_ATTRIBUTE23, PAF.ASS_ATTRIBUTE24, PAF.ASS_ATTRIBUTE25, PAF.ASS_ATTRIBUTE26, PAF.ASS_ATTRIBUTE27, PAF.ASS_ATTRIBUTE28, PAF.ASS_ATTRIBUTE29, PAF.ASS_ATTRIBUTE30, PAF.APPLICANT_RANK, JOB.NAME AS JOB_NAME, JOB.JOB_ID, AST.USER_STATUS, AST.ASSIGNMENT_STATUS_TYPE_ID AS ASSIGNMENT_STATUS_TYPE_ID_FK, VAC.NAME AS VACANCY_NAME, VAC.VACANCY_ID, ORG.NAME AS ORG_NAME, ORG.ORGANIZATION_ID, LOC.DERIVED_LOCALE, LOC.LOCATION_ID, decode(to_char(PAF.EFFECTIVE_END_DATE,'DD-MM-RRRR'), '31-12-4712','N','Y') AS END_DATED, PAF.ASSIGNMENT_STATUS_TYPE_ID, PPF.PERSON_ID, PPF.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE1, PPF.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE1, nvl ((select 'N' from per_vacancies pv where pv.vacancy_id = vac.vacancy_id),'Y') AS DISABLE_SELECTION, PST.NAME AS POSTING_NAME, PST.POSTING_CONTENT_ID, PST.JOB_TITLE, PST.BRIEF_DESCRIPTION, HLK.MEANING AS PROFESSIONAL_AREA, HLK.LOOKUP_TYPE, HLK.LOOKUP_CODE, AST.EXTERNAL_STATUS, PAF.CREATION_DATE, PAF.BUSINESS_GROUP_ID, (select min(asg2.effective_start_date) from per_all_assignments_f asg2 where asg2.assignment_id=PAF.assignment_id) AS APPLICATION_DATE, DECODE(JOB.JOB_ID,NULL, VAC.NAME, VAC.NAME || ', ' || JOB.NAME) AS VAC_WITH_JOB FROM PER_ALL_ASSIGNMENTS_F PAF, PER_JOBS_VL JOB, PER_ASSIGNMENT_STATUS_TYPES_V AST, PER_ALL_VACANCIES VAC, HR_ALL_ORGANIZATION_UNITS_VL ORG, HR_LOCATIONS_ALL_VL LOC, PER_ALL_PEOPLE_F PPF, PER_ALL_PEOPLE_F LINKPPF, IRC_POSTING_CONTENTS_VL PST, IRC_SEARCH_CRITERIA VSC, HR_LOOKUPS HLK WHERE PAF.ASSIGNMENT_TYPE='A' AND PAF.VACANCY_ID = VAC.VACANCY_ID AND PAF.LOCATION_ID = LOC.LOCATION_ID(+) AND AST.ASSIGNMENT_STATUS_TYPE_ID = (SELECT assignment_status_type_id FROM irc_assignment_statuses WHERE assignment_id = paf.assignment_id and assignment_status_id = (select ASSIGNMENT_STATUS_ID from irc_assignment_statuses where assignment_id = paf.assignment_id and last_update_date = (select max(last_update_date) from irc_assignment_statuses where assignment_id = paf.assignment_id and sysdate >= STATUS_CHANGE_DATE))) AND decode(hr_general.get_xbg_profile,'Y' ,vac.business_group_id, hr_general.get_business_group_id) = vac.business_group_id AND PAF.JOB_ID = JOB.JOB_ID(+) AND PAF.ORGANIZATION_ID = ORG.ORGANIZATION_ID(+) AND LINKPPF.PERSON_ID = PAF.PERSON_ID AND PPF.PARTY_ID = LINKPPF.PARTY_ID AND trunc(sysdate) between PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND trunc(sysdate) between LINKPPF.EFFECTIVE_START_DATE AND LINKPPF.EFFECTIVE_END_DATE AND PPF.PERSON_ID = :1 AND (NOT EXISTS ( select null from per_all_people_f nonemp where :2 between nonemp.effective_start_date and nonemp.effective_end_date and nonemp.party_id = ppf.party_id and nonemp.current_employee_flag='Y') OR EXISTS (select null from per_all_people_f ppf3, per_all_assignments_f eaa, per_vacancies svc where :3 between ppf3.effective_start_date and ppf3.effective_end_date and ppf.party_id = ppf3.party_id and eaa.person_id = ppf3.person_id and svc.vacancy_id = eaa.vacancy_id and eaa.assignment_type = 'A')) and PAF.POSTING_CONTENT_ID = PST.POSTING_CONTENT_ID(+) AND VAC.VACANCY_ID = VSC.OBJECT_ID(+) AND VSC.OBJECT_TYPE(+) = 'VACANCY' AND HLK.LOOKUP_TYPE(+) = 'IRC_PROFESSIONAL_AREA' AND VSC.PROFESSIONAL_AREA = HLK.LOOKUP_CODE(+) AND ( (fnd_profile.value('IRC_AGENCY_NAME') is null ) OR (EXISTS (SELECT NULL FROM irc_notification_preferences inp WHERE irc_utilities_pkg.get_recruitment_person_id(ppf.person_id, TRUNC(sysdate)) = inp.person_id AND inp.agency_id = fnd_profile.VALUE('IRC_AGENCY_NAME') ) ) OR (vac.vacancy_id in (select vacancy_id from irc_agency_vacancies iav where iav.vacancy_id = vac.vacancy_id and iav.agency_id = fnd_profile.VALUE('IRC_AGENCY_NAME') and iav.MANAGE_APPLICANTS_ALLOWED = 'Y' ) ) ) AND (PAF.EFFECTIVE_START_DATE = (select max(effective_start_date) from per_all_assignments_f where assignment_id = PAF.assignment_id and effective_start_date <= trunc(sysdate) and ASSIGNMENT_TYPE = 'A' ) AND EXISTS (select null from per_people_f ppfs where ppfs.person_id = PPF.person_id))
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(Unknown Source)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:783)
at oracle.apps.irc.candidateManagement.webui.CandidateApplicantDetailsCO.processRequest(CandidateApplicantDetailsCO.java:356)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:631)

...
...
...

## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)



STEPS TO REPRODUCE
---------------------------


1. iRecruitment recruiter -> Recruitment Home Page -> Candidates Tab

2. Search for the candidate and click on detail.

3. The above mentioned error raises

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