My Oracle Support Banner

JBO-27122 Error raises when opening or while applying job for an applicant in iRecruitment (Doc ID 2654846.1)

Last updated on APRIL 13, 2021

Applies to:

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

Symptoms


When attempting to apply job, 
the following error occurs.

ERROR

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) AND EXISTS (select null from per_people_f ppfs where ppfs.person_id = PPF.person_id)) ORDER BY (select min(asg2.effective_start_date) from per_all_assignments_f asg2 where asg2.assignment_id=PAF.assignment_id) DESC, VAC.NAME DESC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1183)
...
...
...

## 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)
...
...



STEPS

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

  1. Create an application through Templates. 
  2. Click on Application and Fill the details and Save
  3. Now accept the applicant and hire
  4. Now he becomes employee.ex-applicant
  5. Please observe the LAST_UPDATE_DATE in both the tables, it is same for all the records in per_all_assignments_f and same for last two records in irc_assignment_statuses.

    (This issue does not happen every time as there may be a difference of one second in the last update date for two records. So, it may take some attempts to replicate this issue.)
  6. Now in this scenario, when the person applies for internal job
    he/she gets above error.

    (i.e. same last_update_date in irc_assignment_statuses table for more than one record)


Changes

 

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!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.