iRecruitment Recruiter Home page JBO-27122: SQL java.sql.SQLException: ORA-20002: 3000: Invalid value(s) passed for arguments. ITEMTYPE=HRSSA ITEMKEY=NULL ANAME=API_FROM

(Doc ID 2029724.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms

On : 12.1.3 version, General iRecruitment

When attempting to navigate iRecruitment Recruiter Home page the following error occurs.

ERROR
-----------------------
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT NtfEO.NOTIFICATION_ID, NtfEO.GROUP_ID, NtfEO.MESSAGE_TYPE, NtfEO.MESSAGE_NAME, NtfEO.RECIPIENT_ROLE, NtfEO.STATUS, NtfEO.ACCESS_KEY, NtfEO.MAIL_STATUS, NtfEO.PRIORITY, NtfEO.BEGIN_DATE, NtfEO.END_DATE, NtfEO.DUE_DATE, NtfEO.RESPONDER, NtfEO.USER_COMMENT, NtfEO.CALLBACK, NtfEO.CONTEXT, NtfEO.ORIGINAL_RECIPIENT, NtfEO.FROM_USER, NtfEO.TO_USER, NtfEO.LANGUAGE, NtfAttribsEO.NOTIFICATION_ID AS NOTIFICATION_ID1, NtfAttribsEO.NAME, NtfAttribsEO.TEXT_VALUE, NtfAttribsEO.NUMBER_VALUE, NtfAttribsEO.DATE_VALUE, ItemTypesEO.NAME AS NAME1, LookupsEO.LOOKUP_TYPE, LookupsEO.LOOKUP_CODE, DECODE(NtfEO.more_info_role, fnd_global.user_name, FND_MESSAGE.GET_STRING('FND','FND_MORE_INFO_REQUESTED') ||hr_sflutil_ss.GETSUBJECT(NVL(TransactionsEO.transaction_id,TransactionsEO1.transaction_id), NtfEO.notification_id) , hr_sflutil_ss.GETSUBJECT(NVL(TransactionsEO.transaction_id,TransactionsEO1.transaction_id), NtfEO.notification_id) ) AS SUBJECT FROM WF_NOTIFICATIONS NtfEO, WF_NOTIFICATION_ATTRIBUTES NtfAttribsEO, WF_ITEM_TYPES_VL ItemTypesEO, WF_LOOKUPS LookupsEO, WF_NOTIFICATION_ATTRIBUTES NtfAttribsEO1, hr_api_transactions TransactionsEO, hr_api_transactions TransactionsEO1 WHERE NtfEO.NOTIFICATION_ID = NtfAttribsEO.NOTIFICATION_ID (+) and NtfAttribsEO.NAME(+) = 'IRC_ASSIGNMENT_ID' and NtfEO.message_type = ItemTypesEO.name and LookupsEO.lookup_type = 'WF_NOTIFICATION_STATUS' and NtfEO.status = LookupsEO.lookup_code and (NtfEO.recipient_role = :1 or NtfEO.more_info_role = :2)and NtfEO.status = 'OPEN' and NtfEO.NOTIFICATION_ID = NtfAttribsEO1.NOTIFICATION_ID (+) and NtfAttribsEO1.NAME(+) = 'HR_TRANSACTION_REF_ID_ATTR' and NtfAttribsEO1.NUMBER_VALUE = TransactionsEO.TRANSACTION_ID (+) and substr(NtfEO.context,1,instr(NtfEO.context,':') -1) = TransactionsEO1.item_type (+) and substr(NtfEO.context,instr(NtfEO.context,':')+1, instr(NtfEO.context,':',1,2)-instr(NtfEO.context,':') -1 ) = TransactionsEO1.item_key (+) and TransactionsEO.status (+) not in ('E','D') and TransactionsEO1.status (+) not in ('E','D') and (NtfEO.more_info_role is null or NtfEO.more_info_role = fnd_global.user_name) and (decode(NtfEO.message_type, 'HRSFL', TransactionsEO.product_code, 'HRSSA', TransactionsEO1.product_code, 'IRC') = 'IRC' or exists ( select null from wf_notifications ntfeo2 where ntfeo2.message_type = 'HRSSA' and ntfeo2.item_key is not null and wf_engine.getItemAttrText(ntfeo2.message_type,ntfeo2.item_key,'API_FROM') = 'IRC' and ntfeo.notification_id = ntfeo2.notification_id )) AND (NtfEO.message_type IN(:3,:4,:5,:6)) ORDER BY PRIORITY asc, BEGIN_DATE desc
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)

at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:619)
## Detail 0 ##
java.sql.SQLException: ORA-20002: 3000: Invalid value(s) passed for arguments. ITEMTYPE=HRSSA ITEMKEY=NULL ANAME=API_FROM
ORA-06512: at "APPS.WF_ENGINE", line 1663
ORA-06512: at line 1

 

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

1. Login in application

2. iRecruitment Recruiter

3. iRecruitment Home Page

4. Error


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