Error "JBO-27122: SQL Error During Statement Preparation" While Opening a Worklist Notification
(Doc ID 2973777.1)
Last updated on OCTOBER 02, 2023
Applies to:
Oracle Self-Service Human Resources - Version 12.2.11 and laterInformation in this document applies to any platform.
Symptoms
When trying to open a Worklist notification, following error appears
ERROR
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT rownum l_sequence
,a.l_notification_id
,a.l_role role
,a.l_action action
,a.l_comments
,a.l_date l_date,
a.item_key item_key
FROM (
SELECT DISTINCT
wn.notification_id l_notification_id
,wf_directory.getroledisplayname (nvl (wn.more_info_role
,wn.recipient_role)) l_role
,decode (l.lookup_code
,'SFL'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'PENDING'))
,decode (wn.status
,'CANCELED'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN'))
,l.meaning)) l_action
,na.text_value l_comments
,ias.end_date l_date,
i.item_key
FROM wf_activities a
,wf_notification_attributes na
,wf_process_activities pa
,wf_items i
,wf_item_activity_statuses ias
,wf_lookups_tl l
,wf_user_roles wur
,wf_notifications wn
WHERE i.item_type = :1
AND i.item_key = :2
AND ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL
AND wn.notification_id = na.notification_id (+)
AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code
,'A') NOT IN ('SFL','RESUBMIT')
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date
AND nvl (a.end_date
,i.begin_date)
AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code
,'SFL') = l.lookup_code
AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name
AND ias.notification_id = wn.group_id
UNION
SELECT pah.notification_id l_notification_id
,wf_directory.getroledisplayname (pah.user_name) l_role
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action)) l_action
,pah.user_comment l_comments
,pah.last_update_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = pah.notification_id and rownum =1) item_key
FROM pqh_ss_approval_history pah
WHERE pah.transaction_item_type = :3
AND pah.transaction_item_key = :4
AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT'
,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
UNION
SELECT DISTINCT
wn.notification_id l_notification_id
,wf_directory.getroledisplayname (wn.recipient_role) l_role
,decode (l.lookup_code
,'SFL'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'PENDING'))
,decode (wn.status
,'CANCELED'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN'))
,l.meaning)) l_action
,decode (wn.status
,'CANCELED'
,NULL
,nvl (na.text_value
,
(
SELECT user_comment
FROM wf_comments
WHERE notification_id = wn.notification_id
AND action = l.lookup_code
AND rownum = 1
))) l_comments
,nvl (ias.end_date
,ias.begin_date) l_date,
i.item_key
FROM wf_activities a
,wf_notification_attributes na
,wf_process_activities pa
,wf_items i
,wf_item_activity_statuses_h ias
,wf_lookups_tl l
,wf_user_roles wur
,wf_notifications wn
WHERE i.item_type = :5
AND i.item_key = :6
AND ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL
AND wn.notification_id = na.notification_id (+)
AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code
,'A') NOT IN ('SFL','RESUBMIT')
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date
AND nvl (a.end_date
,i.begin_date)
AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code
,'SFL') = l.lookup_code
AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name
AND ias.notification_id = wn.group_id
UNION
SELECT wn.notification_id l_notification_id
,wf_directory.getroledisplayname (nvl (wn.more_info_role
,wn.recipient_role)) l_role
,decode (wn.status
,'CANCELED'
,decode (pah.action
,'TIMEOUT'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN')))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))) l_action
,decode (pah.notification_id
,wn.notification_id
,pah.user_comment
,NULL) l_comments
,pah.last_update_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = wn.notification_id and rownum =1) item_key
FROM pqh_ss_approval_history pah
,wf_notifications wn
WHERE pah.transaction_item_type = :7
AND pah.transaction_item_key = :8
AND pah.action IN ('TIMEOUT','RESUBMIT')
AND wn.group_id =
(
SELECT group_id
FROM wf_notifications
WHERE notification_id = pah.notification_id
AND rownum = 1
)
UNION
select wn.notification_id l_notification_id
,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
-- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action
,C.USER_COMMENT l_comments
-- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = wn.notification_id and rownum =1) item_key
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :9
and IAS.ITEM_KEY = :10
and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION
select wn.notification_id l_notification_id
,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
-- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action
,C.USER_COMMENT l_comments
-- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = wn.notification_id and rownum =1) item_key
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :11
and IAS.ITEM_KEY = :12
and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION
SELECT 0
,wf_directory.getroledisplayname (owner_role) l_role
,wf_core.translate ('SUBMIT')
,appr.text_value note
,begin_date l_date,
i.item_key
FROM wf_items i
,wf_item_attribute_values appr
WHERE i.item_type = :13
AND i.item_key = :14
AND i.item_type = appr.item_type (+)
AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS'
ORDER BY l_notification_id
,l_date
) a
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:836)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
.
.
.
.
.
## Detail 0 ##
java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: 15
at oracle.jdbc.driver.OraclePreparedStatement.setObjectAtName(OraclePreparedStatement.java:15906)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObjectAtName(OraclePreparedStatementWrapper.java:911)
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT rownum l_sequence
,a.l_notification_id
,a.l_role role
,a.l_action action
,a.l_comments
,a.l_date l_date,
a.item_key item_key
FROM (
SELECT DISTINCT
wn.notification_id l_notification_id
,wf_directory.getroledisplayname (nvl (wn.more_info_role
,wn.recipient_role)) l_role
,decode (l.lookup_code
,'SFL'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'PENDING'))
,decode (wn.status
,'CANCELED'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN'))
,l.meaning)) l_action
,na.text_value l_comments
,ias.end_date l_date,
i.item_key
FROM wf_activities a
,wf_notification_attributes na
,wf_process_activities pa
,wf_items i
,wf_item_activity_statuses ias
,wf_lookups_tl l
,wf_user_roles wur
,wf_notifications wn
WHERE i.item_type = :1
AND i.item_key = :2
AND ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL
AND wn.notification_id = na.notification_id (+)
AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code
,'A') NOT IN ('SFL','RESUBMIT')
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date
AND nvl (a.end_date
,i.begin_date)
AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code
,'SFL') = l.lookup_code
AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name
AND ias.notification_id = wn.group_id
UNION
SELECT pah.notification_id l_notification_id
,wf_directory.getroledisplayname (pah.user_name) l_role
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action)) l_action
,pah.user_comment l_comments
,pah.last_update_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = pah.notification_id and rownum =1) item_key
FROM pqh_ss_approval_history pah
WHERE pah.transaction_item_type = :3
AND pah.transaction_item_key = :4
AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT'
,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
UNION
SELECT DISTINCT
wn.notification_id l_notification_id
,wf_directory.getroledisplayname (wn.recipient_role) l_role
,decode (l.lookup_code
,'SFL'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'PENDING'))
,decode (wn.status
,'CANCELED'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN'))
,l.meaning)) l_action
,decode (wn.status
,'CANCELED'
,NULL
,nvl (na.text_value
,
(
SELECT user_comment
FROM wf_comments
WHERE notification_id = wn.notification_id
AND action = l.lookup_code
AND rownum = 1
))) l_comments
,nvl (ias.end_date
,ias.begin_date) l_date,
i.item_key
FROM wf_activities a
,wf_notification_attributes na
,wf_process_activities pa
,wf_items i
,wf_item_activity_statuses_h ias
,wf_lookups_tl l
,wf_user_roles wur
,wf_notifications wn
WHERE i.item_type = :5
AND i.item_key = :6
AND ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL
AND wn.notification_id = na.notification_id (+)
AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code
,'A') NOT IN ('SFL','RESUBMIT')
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date
AND nvl (a.end_date
,i.begin_date)
AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code
,'SFL') = l.lookup_code
AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name
AND ias.notification_id = wn.group_id
UNION
SELECT wn.notification_id l_notification_id
,wf_directory.getroledisplayname (nvl (wn.more_info_role
,wn.recipient_role)) l_role
,decode (wn.status
,'CANCELED'
,decode (pah.action
,'TIMEOUT'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN')))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))) l_action
,decode (pah.notification_id
,wn.notification_id
,pah.user_comment
,NULL) l_comments
,pah.last_update_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = wn.notification_id and rownum =1) item_key
FROM pqh_ss_approval_history pah
,wf_notifications wn
WHERE pah.transaction_item_type = :7
AND pah.transaction_item_key = :8
AND pah.action IN ('TIMEOUT','RESUBMIT')
AND wn.group_id =
(
SELECT group_id
FROM wf_notifications
WHERE notification_id = pah.notification_id
AND rownum = 1
)
UNION
select wn.notification_id l_notification_id
,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
-- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action
,C.USER_COMMENT l_comments
-- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = wn.notification_id and rownum =1) item_key
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :9
and IAS.ITEM_KEY = :10
and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION
select wn.notification_id l_notification_id
,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
-- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action
,C.USER_COMMENT l_comments
-- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date,
(Select distinct item_key from wf_notifications wf1 where wf1.notification_id = wn.notification_id and rownum =1) item_key
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :11
and IAS.ITEM_KEY = :12
and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION
SELECT 0
,wf_directory.getroledisplayname (owner_role) l_role
,wf_core.translate ('SUBMIT')
,appr.text_value note
,begin_date l_date,
i.item_key
FROM wf_items i
,wf_item_attribute_values appr
WHERE i.item_type = :13
AND i.item_key = :14
AND i.item_type = appr.item_type (+)
AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS'
ORDER BY l_notification_id
,l_date
) a
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:836)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
.
.
.
.
.
## Detail 0 ##
java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: 15
at oracle.jdbc.driver.OraclePreparedStatement.setObjectAtName(OraclePreparedStatement.java:15906)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObjectAtName(OraclePreparedStatementWrapper.java:911)
The issue can be reproduced at will with the following steps:
1. Login to "Employee Self-Service" responsibility.
2. Navigate to any function like Absence Management or Extra Information or Special Information
3. Enter the details and submit the transaction.
4. Login as Approver and open the Worklist notification.
5. Encounter the above error.
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 |