My Oracle Support Banner

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

To view full details, 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 a vibrant support community of peers and Oracle experts.