Unexpected Error and JBO-27122: SQL error during statement preparation when Trying to View Applicants for Vacancy

(Doc ID 1997217.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

When attempting to view applicants for one particular vacancy, the following error occurs:

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

Error Page
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 PPF.FULL_NAME AS PERSON_NAME, PPF.FIRST_NAME AS PERSON_FIRST_NAME, PPF.LAST_NAME AS PERSON_LAST_NAME, ADDR.DERIVED_LOCALE, PPF.EMAIL_ADDRESS, ADDR.ADDRESS_ID, PHN.PHONE_NUMBER, PHN.PHONE_ID, PPF.DATE_OF_BIRTH, ADDR.ADDRESS_LINE1, to_char(IRC_SKILLS_MATCHING_PKG.vacancy_match_percent(irc_utilities_pkg.GET_RECRUITMENT_PERSON_ID(PPF.PERSON_ID,:1), PAV.VACANCY_ID , :2 )) AS MATCH_PERCENT, (select count(*) from PER_ALL_ASSIGNMENTS_F PAF2, PER_ALL_PEOPLE_F PPF2 where PAF2.ASSIGNMENT_TYPE = 'A' and :3 between PAF2.effective_start_date AND PAF2.effective_end_date AND PAF2.person_id = PPF2.person_id and PAF2.effective_start_date between PPF2.effective_start_date AND PPF2.effective_end_date AND PPF2.party_id = PPF.party_id having count(*)>0) AS Jobs_Applied_For, (select MIN(PAF2.effective_start_date) from PER_ALL_ASSIGNMENTS_F PAF2 where PAF2.assignment_id=PAF.assignment_id) AS Last_Application, PPF.PERSON_ID AS APL_PERSON_ID, PPF.EFFECTIVE_START_DATE, PPF.EFFECTIVE_END_DATE, VCN.CONSIDERATION_STATUS, VCN.PARTY_ID AS CONSIDERATION_PARTY_ID, VCN.VACANCY_ID AS CONSIDERATION_VACANCY_ID, (select meaning from HR_LOOKUPS LKP where LKP.LOOKUP_TYPE = 'IRC_CONSIDERATION' AND LKP.LOOKUP_CODE = VCN.consideration_status) AS CONSIDERATION, PAF.APPLICANT_RANK, PAF.ASSIGNMENT_ID, PAF.ASSIGNMENT_STATUS_TYPE_ID AS ASSIGNMENT_STATUS_TYPE_ID1, PAF.CHANGE_REASON, PAF.EFFECTIVE_START_DATE AS P_A_F_EFFECTIVE_START_DATE, PAF.EFFECTIVE_END_DATE AS P_A_F_EFFECTIVE_END_DATE1, PAF.ASSIGNMENT_TYPE, AST.USER_STATUS AS ASSIGNMENT_STATUS, AST.ASSIGNMENT_STATUS_TYPE_ID, PAV.VACANCY_ID, PAV.NAME AS VACANCY_NAME, decode(to_char(PAF.EFFECTIVE_END_DATE,'DD-MM-RRRR'), '31-12-4712','N','Y') AS EndDated, PPF.PER_INFORMATION_CATEGORY, PPF.PER_INFORMATION1, PPF.PER_INFORMATION2, PPF.PER_INFORMATION3, PPF.PER_INFORMATION4, PPF.PER_INFORMATION5, PPF.PER_INFORMATION6, PPF.PER_INFORMATION7, PPF.PER_INFORMATION8, PPF.PER_INFORMATION9, PPF.PER_INFORMATION10, PPF.PER_INFORMATION11, PPF.PER_INFORMATION12, PPF.PER_INFORMATION13, PPF.PER_INFORMATION14, PPF.PER_INFORMATION15, PPF.PER_INFORMATION16, PPF.PER_INFORMATION17, PPF.PER_INFORMATION18, PPF.PER_INFORMATION19, PPF.PER_INFORMATION20, PPF.PER_INFORMATION21, PPF.PER_INFORMATION22, PPF.PER_INFORMATION23, PPF.PER_INFORMATION24, PPF.PER_INFORMATION25, PPF.PER_INFORMATION26, PPF.PER_INFORMATION27, PPF.PER_INFORMATION28, PPF.PER_INFORMATION29, PPF.PER_INFORMATION30, DOC.DOCUMENT_ID, DOC.FILE_NAME, NVL2( DOC.DOCUMENT_ID,'T','F') AS PreviewEnabled, PPF.ATTRIBUTE_CATEGORY, PPF.ATTRIBUTE1, PPF.ATTRIBUTE2, PPF.ATTRIBUTE3, PPF.ATTRIBUTE4, PPF.ATTRIBUTE5, PPF.ATTRIBUTE6, PPF.ATTRIBUTE7, PPF.ATTRIBUTE8, PPF.ATTRIBUTE9, PPF.ATTRIBUTE10, PPF.ATTRIBUTE11, PPF.ATTRIBUTE12, PPF.ATTRIBUTE13, PPF.ATTRIBUTE14, PPF.ATTRIBUTE15, PPF.ATTRIBUTE16, PPF.ATTRIBUTE17, PPF.ATTRIBUTE18, PPF.ATTRIBUTE19, PPF.ATTRIBUTE20, PPF.ATTRIBUTE21, PPF.ATTRIBUTE22, PPF.ATTRIBUTE23, PPF.ATTRIBUTE24, PPF.ATTRIBUTE25, PPF.ATTRIBUTE26, PPF.ATTRIBUTE27, PPF.ATTRIBUTE28, PPF.ATTRIBUTE29, PPF.ATTRIBUTE30, VCN.VACANCY_CONSIDERATION_ID, (irc_utilities_pkg.GET_RECRUITMENT_PERSON_ID(PPF.PERSON_ID, :4)) AS PERSON_ID, to_char(RowNum) AS RowNumber, PPF.PARTY_ID, POV.VENDOR_ID, POV.VENDOR_NAME, PPF.CURRENT_APPLICANT_FLAG, AST.PER_SYSTEM_STATUS, REGATMPT.ATTEMPT_ID AS REG_ATTEMPT_ID, to_number(decode(REGATMPT.RAW_SCORE, -1000, null, REGATMPT.RAW_SCORE)) AS REG_ATTEMPT_SCORE, nvl2(REGATMPT.MASTERY_SCORE,decode(greatest(REGATMPT.MASTERY_SCORE,REGATMPT.RAW_SCORE), REGATMPT.RAW_SCORE, 'P', 'F'),nvl2(FND_PROFILE.VALUE('IRC_REGISTER_TEST'),nvl2(INP.ATTEMPT_ID,'A','N'),null)) AS REG_ATTEMPT_STATUS, APLATMPT.ATTEMPT_ID AS APL_ATTEMPT_ID, to_number(decode(APLATMPT.RAW_SCORE, -1000, null, APLATMPT.RAW_SCORE)) AS APL_ATTEMPT_SCORE, nvl2(APLATMPT.MASTERY_SCORE,decode(greatest(APLATMPT.MASTERY_SCORE,APLATMPT.RAW_SCORE), APLATMPT.RAW_SCORE, 'P', 'F'),nvl2(PAV.ASSESSMENT_ID,nvl2(IAD.ATTEMPT_ID,'A','N'),null)) AS APL_ATTEMPT_STATUS, IAD.QUALIFIED AS QUALIFIED, IAD.ASSIGNMENT_DETAILS_ID, IAD.CONSIDERED AS CONSIDERED, PAF.BUSINESS_GROUP_ID, (SELECT COUNT(*) FROM PER_EVENTS PE4, IRC_INTERVIEW_DETAILS IID4 WHERE PE4.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID AND IID4.EVENT_ID = PE4.EVENT_ID AND SYSDATE BETWEEN IID4.START_DATE AND IID4.END_DATE AND IID4.STATUS = 'COMPLETED') AS NO_OF_COMPLETED_INTERVIEWS, (select meaning from hr_lookups where lookup_code = IRCREFERRALINFOEO.source_type and lookup_type ='REC_TYPE') AS SOURCE_TYPE, decode (IrcReferralInfoEO.source_type, 'ER',(select full_name from per_all_people_f where person_id = IrcReferralInfoEO.source_person_id and Sysdate between Effective_Start_Date and Effective_End_Date), IrcReferralInfoEO.source_name ) AS SOURCE_NAME, nvl((select 'Y' from per_all_assignments_f paaf where paaf.assignment_id = paf.assignment_id and paaf.assignment_type = paf.assignment_type and paaf.assignment_type = 'A' and paaf.effective_end_date = paf.effective_end_date and paaf.effective_end_date iosh.status_change_date ) AND iosh.offer_status_history_id = ( SELECT max (iosh2.offer_status_history_id) FROM irc_offer_status_history iosh2 WHERE iosh2.offer_id = iosh.offer_id AND iosh2.status_change_date = iosh.status_change_date ) AND ( otheroffer.offer_status IN ('APPROVED','CORRECTION','EXTENDED' ,'PENDING','UNAPPROVED') OR ( otheroffer.offer_status = 'CLOSED' AND iosh.change_reason IN ('APL_ACCEPTED') ) )) ,0,'No','Yes')) AS ACTIVE_OFFER_EXISTS, (SELECT hl.meaning FROM hr_lookups hl, IRC_OFFER_STATUS_HISTORY ioh WHERE hl.LOOKUP_TYPE(+) = 'IRC_OFFER_STATUS_CHANGE_REASON' AND hl.lookup_code(+) = ioh.change_reason AND ioh.offer_id(+) = IOF.offer_id AND NOT EXISTS (SELECT 1 FROM irc_offer_status_history iosh1 WHERE iosh1.offer_id (+) = ioh.offer_id AND iosh1.status_change_date > ioh.status_change_date ) AND ioh.offer_status_history_id = (SELECT MAX(iosh2.offer_status_history_id) FROM irc_offer_status_history iosh2 WHERE iosh2.offer_id (+) = ioh.offer_id AND iosh2.status_change_date(+) = ioh.status_change_date ) ) AS OFFER_CHANGE_REASON, IAS.STATUS_CHANGE_COMMENTS, IAS.ASSIGNMENT_STATUS_ID, ADDR.OBJECT_VERSION_NUMBER, IAS.STATUS_CHANGE_DATE, PHN.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER1, 'NOT_APPLICABLE' AS REHIRE_RECOMMENDATION, (case when exists (SELECT NULL FROM per_all_assignments_f paf4 WHERE paf4.vacancy_id = paf.vacancy_id AND paf4.person_id = paf.person_id AND paf4.assignment_type = 'E') then 'Y' else 'N' end) AS IS_HIRED, (CASE WHEN EXISTS ( SELECT 'Y' FROM irc_offers iof WHERE iof.applicant_assignment_id = paf.assignment_id AND EXISTS ( SELECT 1 FROM irc_offer_status_history iosh WHERE iof.offer_id = iosh.offer_id AND iof.latest_offer = 'Y' AND ( iof.offer_status <> 'CLOSED' OR ( iof.offer_status = 'CLOSED' AND iosh.change_reason IN ('APL_ACCEPTED','APL_HIRED') )) AND NOT EXISTS ( SELECT 1 FROM irc_offer_status_history iosh1 WHERE iosh1.offer_id = iosh.offer_id AND iosh1.status_change_date > iosh.status_change_date ) AND iosh.offer_status_history_id = ( SELECT max (iosh2.offer_status_history_id) FROM irc_offer_status_history iosh2 WHERE iosh2.offer_id = iosh.offer_id AND iosh2.status_change_date = iosh.status_change_date )) ) THEN 'Y' ELSE 'N' END) AS HAS_ACTIVE_OFFER, xxtaj_hr_utils.get_lookup_meaning(PPF.PER_INFORMATION18,'AE_NATIONALITY') AS XXTAJNationality, xxtaj_hr_utils.get_yrs_of_exp(PPF.PERSON_ID) AS XXTAJYears_Exp, xxtaj_hr_utils.get_dri_lic_type(PPF.PERSON_ID) AS XXTAJDrivLicensingType, xxtaj_hr_utils.get_yts_of_exp_mng(ppf.person_id) AS XXTAYears_Exp_Mean, xxtaj_hr_utils.get_dri_lic_type_mng(ppf.person_id) AS XXTAJDrivLicTypeMean, xxtaj_hr_utils.get_lookup_meaning(PPF.SEX,'SEX') AS XXTAJGender, xxtaj_hr_utils.get_work_specialization_mng(ppf.person_id) AS WorkSpecialization, xxtaj_hr_utils.get_professional_area(ppf.person_id) AS XXTAJProfessionalArea, ADDR.country AS XXTAJCountryCode, ADDR.ADDR_ATTRIBUTE1 AS XXTAJEmirate, xxtaj_hr_utils.get_lookup_meaning(ADDR.ADDR_ATTRIBUTE1,'AE_EMIRATE') AS XXTAJEmirateDesc, xxtaj_hr_utils.get_country_desc(ADDR.country) AS XXTAJCountryDesc, xxtaj_hr_utils.get_contract_type(PPF.PERSON_ID) AS XXTAJContractType FROM PER_ADDRESSES ADDR, PER_PHONES PHN, PER_ALL_PEOPLE_F PPF, IRC_VACANCY_CONSIDERATIONS VCN, PER_ALL_ASSIGNMENTS_F PAF, PER_ASSIGNMENT_STATUS_TYPES_V AST, PER_ALL_VACANCIES PAV, IRC_NOTIFICATION_PREFERENCES INP, IRC_DOCUMENTS DOC, PO_VENDORS POV, IRC_ASSIGNMENT_STATUSES IAS, OTA_ATTEMPTS REGATMPT, IRC_ASSIGNMENT_DETAILS_F IAD, OTA_ATTEMPTS APLATMPT, IRC_REFERRAL_INFO IrcReferralInfoEO, IRC_OFFERS IOF WHERE :5 BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND irc_utilities_pkg.GET_RECRUITMENT_PERSON_ID(PPF.PERSON_ID, :6) = INP.person_id(+) and INP.PERSON_ID = DOC.PERSON_ID(+) AND INP.PERSON_ID = PHN.PARENT_ID(+) and PHN.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F' AND PHN.phone_type(+) = 'H1' AND :7 between nvl(PHN.date_from(+),:8) and NVL(PHN.date_to(+),:9) AND VCN.VACANCY_ID(+) = :10 AND VCN.PERSON_ID(+) = INP.PERSON_ID AND PPF.PERSON_ID = PAF.PERSON_ID AND PAF.VACANCY_ID = :11 AND PAF.VACANCY_ID = PAV.VACANCY_ID AND PAF.ASSIGNMENT_ID=IAS.ASSIGNMENT_ID AND IAS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID(+) and not exists (select 1 from irc_assignment_statuses ias2 where ias2.assignment_id = paf.assignment_id and trunc(ias2.status_change_date) > trunc(ias.status_change_date) ) and ias.last_update_date = (select Max(ias3.last_update_date) from irc_assignment_statuses ias3 where ias3.assignment_id = paf.assignment_id and trunc(ias3.status_change_date) = trunc(ias.status_change_date) ) AND INP.person_ID = ADDR.person_ID(+) AND NVL(INP.ADDRESS_ID, DECODE(ADDR.ADDRESS_TYPE(+),'REC',ADDR.ADDRESS_ID(+),-1)) = addr.ADDRESS_ID(+) AND :12 between ADDR.DATE_FROM(+) and nvl(ADDR.DATE_TO(+),:13) and doc.type(+) like '%RESUME' and doc.end_date(+) is null AND decode(hr_general.get_xbg_profile,'Y' ,pav.business_group_id, hr_general.get_business_group_id) = pav.business_group_id and ( FND_PROFILE.VALUE('IRC_AGENCY_NAME') is null or exists (select null from IRC_AGENCY_VACANCIES IAV where PAV.VACANCY_ID = IAV.VACANCY_ID and IAV.AGENCY_ID = FND_PROFILE.VALUE('IRC_AGENCY_NAME') AND IAV.MANAGE_APPLICANTS_ALLOWED = 'Y' ) or inp.agency_id = FND_PROFILE.VALUE('IRC_AGENCY_NAME')) and INP.AGENCY_ID = POV.VENDOR_ID(+) and INP.ATTEMPT_ID = REGATMPT.ATTEMPT_ID(+) AND PAF.ASSIGNMENT_ID = IAD.ASSIGNMENT_ID(+) and :14 between IAD.EFFECTIVE_START_DATE(+) and IAD.EFFECTIVE_END_DATE(+) and IAD.LATEST_DETAILS(+) = 'Y' and IAD.ATTEMPT_ID = APLATMPT.ATTEMPT_ID(+) AND PAF.assignment_id = IrcReferralInfoEO.object_id(+) and IrcReferralInfoEO.object_type(+) = 'APPLICATION' and sysdate between IrcReferralInfoEO.start_date(+) and IrcReferralInfoEO.end_date(+) and IOF.LATEST_OFFER(+) ='Y' and IOF.APPLICANT_ASSIGNMENT_ID(+) = PAF.ASSIGNMENT_ID AND ((nvl(DOC.document_id,1) = nvl((SELECT MAX(idoc1.document_id) FROM irc_documents idoc1 WHERE idoc1.person_id = inp.person_id AND idoc1.type IN('RESUME', 'AUTO_RESUME') AND idoc1.end_date is null and last_update_date = ( SELECT MAX(idoc2.last_update_date) FROM irc_documents idoc2 WHERE idoc2.person_id = inp.person_id AND idoc2.type IN('RESUME','AUTO_RESUME') AND idoc2.end_date is null)),1)) AND PAF.assignment_type='A' AND :15 BETWEEN PAF.effective_start_date AND PAF.effective_end_date AND NOT EXISTS (SELECT NULL FROM per_all_assignments_f paf3 WHERE paf3.vacancy_id = paf.vacancy_id AND paf3.person_id = paf.person_id AND paf3.assignment_type ='E') ) and exists (select null from per_all_assignments_f paf4 where paf4.assignment_id = paf.assignment_id and paf4.effective_end_date = to_date('31/12/4712','DD/MM/RRRR') and paf4.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:762)

(..)

## Detail 0 ##
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.IRC_SKILLS_MATCHING_PKG", line 345
ORA-06512: at "APPS.IRC_SKILLS_MATCHING_PKG", line 100
ORA-06512: at "APPS.IRC_SKILLS_MATCHING_PKG", line 690
ORA-06512: at line 1

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.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)

(..)

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

1. Responsibility: iRecruitment Recruiter.
2. Navigation: iRecruitment Home.
3. Click ‘Search for Vacancies’ link.
4. Enter vacancy name.
5. Press Go button.
6. Click on number in Active Applicants column.
7. Error message is displayed.

WORKAROUND
--------------------------

It has been verified that the root cause for the issue is that the affected vacancy has 122 Skills. As a workaround, skills from the vacancy can be removed.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot view applicants for one particular vacancy. Users are unable to find the applicants' details for this vacancy and hence unable to verify their Resume/arrange for interview/applicant selection process.



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