Get Unexpected Error when Clicking on Applicant Name after Applying RUP 7 (Doc ID 1955379.1)

Last updated on AUGUST 10, 2017

Applies to:

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

Symptoms

When clicking on applicant name link against a vacancy, the following error occurs:


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

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

Excerpts of error stack received:

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 status_change_date = (select max(status_change_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(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.candidateManagement.webui.CandidateApplicantDetailsCO.processRequest(CandidateApplicantDetailsCO.java:350)
    at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)

(..)

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

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1177)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
    at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
    at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
    at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)

(..)

WHAT IS WORKING
----------------------------------

This is only an issue with some applicants and the rest of the applicants can be viewed with no errors.


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

1. Responsibility: iRecruitment Manager or iRecruitment Recruiter.
2. Navigation: iRecruitment Home - Vacancies tab.
3. Search for Vacancy.
4. Press Go button.
5. Select vacancy.
6. Press View Applicants button.
7. Click on Applicant Name link.
8. Get error message.


WORKAROUND
----------------------------------
NONE

BUSINESS IMPACT
----------------------------------
Due to this issue, managers and recruiters Recruitment are unable to process applications or make offers for a portion of candidates.

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