Encounter "ORA-00904: "HR_APPRAISALS_UTIL_SS"."GET_ACTION_DATE": invalid identifier" Error While Opening Worklist Notifications
(Doc ID 2921973.1)
Last updated on FEBRUARY 13, 2024
Applies to:
Oracle Self-Service Human Resources - Version 12.2 HRMS RUP12 and laterInformation in this document applies to any platform.
Symptoms
When trying to open Worklist Notifications, following error appears.
ERROR
Error Page
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
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
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
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
,decode(c.proxy_role,null,c.from_user,
(select wlr.display_name from wf_local_roles wlr where wlr.name = c.proxy_role) || ' (' || REPLACE(:5,'&PROXY_USER_NAME',c.from_user) || ')') 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
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
,wf_comments c
WHERE i.item_type = :6
AND i.item_key = :7
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 = (select distinct wur.role_name from wf_user_roles wur where wur.role_name = ias.assigned_user) or ias.assigned_user like 'email%')
AND ias.notification_id = wn.group_id
AND ias.notification_id = c.notification_id
AND c.action in ('RESPOND','RESPOND_WA')
UNION
SELECT wn.notification_id l_notification_id
,wf_directory.getroledisplayname (nvl (wn.more_info_role
,wn.recipient_role)) l_role
,decode(pah.action
,'SFL'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))
,decode (wn.status
,'CANCELED'
,decode (pah.action
,'TIMEOUT'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))
, 'RESUBMIT'
, decode(wn.RECIPIENT_ROLE
,pah.USER_NAME
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'RESUBMIT'))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN'))
)
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN')))
,'CLOSED'
,decode(wn.RESPONDER
,wn.RECIPIENT_ROLE
,(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 ((select ame.notification_id
from ame_trans_approval_history ame
where ame.notification_id=pah.notification_id
--AND ame.name=pah.user_name)
),wn.notification_id
,nvl((select ame.user_comments
from ame_trans_approval_history ame
where ame.notification_id=pah.notification_id
--AND ame.name=pah.user_name)
),pah.user_comment)) l_comments
,pah.last_update_date l_date
FROM pqh_ss_approval_history pah
,wf_notifications wn
WHERE pah.transaction_item_type = :8
AND pah.transaction_item_key = :9
AND pah.action IN ('TIMEOUT','RESUBMIT','SFL')
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
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :10
and IAS.ITEM_KEY = :11
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
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :12
and IAS.ITEM_KEY = :13
and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION
SELECT 0 l_notification_id
,wf_directory.getroledisplayname (owner_role) l_role
,wf_core.translate ('SUBMIT')
,appr.text_value note
,begin_date l_date
FROM wf_items i
,wf_item_attribute_values appr
WHERE i.item_type = :14
AND i.item_key = :15
AND i.item_type = appr.item_type (+)
AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS'
AND NOT EXISTS ( SELECT 1
FROM wf_item_attribute_values appr2
WHERE appr2.item_type = i.item_type
AND appr2.item_key = i.item_key
AND appr2.name = 'APPRAISAL_ID'
AND appr2.number_value IS NOT NULL)
UNION
SELECT 0 l_notification_id
,wf_directory.getroledisplayname (owner_role) l_role
,wf_core.translate ('SUBMIT')
,appr.text_value note
,hr_appraisals_util_ss.get_action_date(i.item_key,
owner_role,
begin_date) l_date
FROM wf_items i
,wf_item_attribute_values appr
WHERE i.item_type = :16
AND i.item_key = :17
AND i.item_type = appr.item_type (+)
AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS'
AND EXISTS ( SELECT 1
FROM wf_item_attribute_values appr2
WHERE appr2.item_type = i.item_type
AND appr2.item_key = i.item_key
AND appr2.name = 'APPRAISAL_ID'
AND appr2.number_value IS NOT NULL)
ORDER BY l_date, l_notification_id
) a
.
.
.
.
.
.
.
.
.
## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00904: "HR_APPRAISALS_UTIL_SS"."GET_ACTION_DATE": invalid identifier
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
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
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
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
,decode(c.proxy_role,null,c.from_user,
(select wlr.display_name from wf_local_roles wlr where wlr.name = c.proxy_role) || ' (' || REPLACE(:5,'&PROXY_USER_NAME',c.from_user) || ')') 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
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
,wf_comments c
WHERE i.item_type = :6
AND i.item_key = :7
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 = (select distinct wur.role_name from wf_user_roles wur where wur.role_name = ias.assigned_user) or ias.assigned_user like 'email%')
AND ias.notification_id = wn.group_id
AND ias.notification_id = c.notification_id
AND c.action in ('RESPOND','RESPOND_WA')
UNION
SELECT wn.notification_id l_notification_id
,wf_directory.getroledisplayname (nvl (wn.more_info_role
,wn.recipient_role)) l_role
,decode(pah.action
,'SFL'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))
,decode (wn.status
,'CANCELED'
,decode (pah.action
,'TIMEOUT'
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,pah.action))
, 'RESUBMIT'
, decode(wn.RECIPIENT_ROLE
,pah.USER_NAME
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'RESUBMIT'))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN'))
)
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS'
,'BEATEN')))
,'CLOSED'
,decode(wn.RESPONDER
,wn.RECIPIENT_ROLE
,(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 ((select ame.notification_id
from ame_trans_approval_history ame
where ame.notification_id=pah.notification_id
--AND ame.name=pah.user_name)
),wn.notification_id
,nvl((select ame.user_comments
from ame_trans_approval_history ame
where ame.notification_id=pah.notification_id
--AND ame.name=pah.user_name)
),pah.user_comment)) l_comments
,pah.last_update_date l_date
FROM pqh_ss_approval_history pah
,wf_notifications wn
WHERE pah.transaction_item_type = :8
AND pah.transaction_item_key = :9
AND pah.action IN ('TIMEOUT','RESUBMIT','SFL')
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
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :10
and IAS.ITEM_KEY = :11
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
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C,
wf_notifications wn
where IAS.ITEM_TYPE = :12
and IAS.ITEM_KEY = :13
and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION
SELECT 0 l_notification_id
,wf_directory.getroledisplayname (owner_role) l_role
,wf_core.translate ('SUBMIT')
,appr.text_value note
,begin_date l_date
FROM wf_items i
,wf_item_attribute_values appr
WHERE i.item_type = :14
AND i.item_key = :15
AND i.item_type = appr.item_type (+)
AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS'
AND NOT EXISTS ( SELECT 1
FROM wf_item_attribute_values appr2
WHERE appr2.item_type = i.item_type
AND appr2.item_key = i.item_key
AND appr2.name = 'APPRAISAL_ID'
AND appr2.number_value IS NOT NULL)
UNION
SELECT 0 l_notification_id
,wf_directory.getroledisplayname (owner_role) l_role
,wf_core.translate ('SUBMIT')
,appr.text_value note
,hr_appraisals_util_ss.get_action_date(i.item_key,
owner_role,
begin_date) l_date
FROM wf_items i
,wf_item_attribute_values appr
WHERE i.item_type = :16
AND i.item_key = :17
AND i.item_type = appr.item_type (+)
AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS'
AND EXISTS ( SELECT 1
FROM wf_item_attribute_values appr2
WHERE appr2.item_type = i.item_type
AND appr2.item_key = i.item_key
AND appr2.name = 'APPRAISAL_ID'
AND appr2.number_value IS NOT NULL)
ORDER BY l_date, l_notification_id
) a
.
.
.
.
.
.
.
.
.
## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00904: "HR_APPRAISALS_UTIL_SS"."GET_ACTION_DATE": invalid identifier
STEPS
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, SIT etc.
3. Enter the details and submit the transaction.
4. Login as Approver and open the Worklist Approval 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 |