Unexpected Error and JBO-27122: SQL error during statement preparation when Trying to View Applicants for Vacancy
(Doc ID 1997217.1)
Last updated on JULY 09, 2024
Applies to:
Oracle iRecruitment - Version 12.1.1 and laterInformation in this document applies to any platform.
Symptoms
When attempting to view applicants for one particular vacancy, the following error occurs:
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)
(..)
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.
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 |
| Cause |
| Solution |
| References |