SQL Error In Customer Support Agent KPI Dashboard
(Doc ID 1579500.1)
Last updated on FEBRUARY 06, 2022
Applies to:
Oracle Customer Support - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
On : 12.1.3 version, Create Service Request
When attempting to access the KPI overview in the customer support specialist the following error occurs.
Error
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: select
decode(per.lookup_code,'TODAY' ,1,
'LAST_7_DAYS' ,2,
'LAST_30_DAYS',3,
'LAST_90_DAYS',4,
5) period_order,
per.meaning time_period,
decode(sign(nvl(wait_time_owner,0)-60),-1, to_char(nvl(Wait_Time_Owner,0),'999999990') ||' '|| min.meaning, to_char(nvl(wait_time_owner,0)/60,'9999999990.9') ||' '|| hrs.meaning)Wait_Time_Owner,
decode(sign(nvl(wait_time_support,0)-60),-1,to_char(nvl(Wait_Time_Support,0),'9999999990') ||' '|| min.meaning, to_char(nvl(Wait_Time_Support,0)/60,'9999999990.9')||' '|| hrs.meaning)Wait_Time_Support,
decode(sign(nvl(wait_time_internal,0)-60),-1,to_char(nvl(Wait_Time_Internal,0),'9999999990') ||' '|| min.meaning, to_char(nvl(Wait_Time_Internal,0)/60,'9999999990.9')||' '|| hrs.meaning)Wait_Time_Internal,
decode(sign(nvl(wait_time_external,0)-60),-1,to_char(nvl(Wait_Time_External,0),'9999999990') ||' '|| min.meaning, to_char(nvl(Wait_Time_External,0)/60,'9999999990.9')||' '|| hrs.meaning)Wait_Time_External,
decode(sign(nvl(wait_time_cust,0)-60),-1,to_char(nvl(Wait_Time_Cust,0),'9999999990') ||' '|| min.meaning, to_char(nvl(Wait_Time_Cust,0)/60,'9999999990.9')||' '|| hrs.meaning)Wait_Time_Cust,
decode(sign(nvl(a.MT_Resolved,0)-60),-1,to_char(nvl(a.MT_Resolved,0),'9999999990') ||' '|| min.meaning, to_char(nvl(a.MT_Resolved,0)/60,'9999999990.9')||' '|| hrs.meaning)Mean_Time_Resolve,
decode(sign(nvl(a.MT_Resolved,0)-60),-1,(nvl(a.MT_Resolved,0)),
(nvl(a.MT_Resolved,0)/60))Mean_Time_Resolve_sort,
to_char(nvl(sr_resolved,0), '9999999990') sr_resolved,
to_char(nvl(res_agrmt_missed,0), '9999999990') Res_Agrmt_Missed
from
(
Select period_type,
sum(total_wait_agent) / sum(total_sr_resolved) Wait_Time_Owner,
sum(total_wait_support) / sum(total_sr_resolved) Wait_Time_Support,
sum(total_wait_internal) / sum(total_sr_resolved) Wait_Time_Internal,
sum(total_wait_external) / sum(total_sr_resolved) Wait_Time_External,
sum(total_wait_customer) / sum(total_sr_resolved) Wait_Time_Cust,
sum(total_resolve_time) / sum(total_sr_resolved) MT_Resolved,
sum(total_sr_resolved) Sr_Resolved,
decode(jtf.resource_count,0,0,
nvl(sum(total_resl_sla_missed)/jtf.resource_count,0) ) Res_Agrmt_Missed
From csy_group_respn_resol_mv grpmv,
cs_incident_severities_b sev,
(select :1 resource_count from dual) jtf
where sev.incident_severity_id = grpmv.incident_severity_id
and total_sr_resolved <> 0
and sev.importance_level in (:2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16)
and grpmv.owner_group_id in (select group_id
from jtf_rs_group_members mem
where resource_id = :17)
group by grpmv.period_type
) a,
FND_LOOKUP_VALUES per,
--csy_periods_v b,
--cs_lookups min,
--cs_lookups hrs
FND_LOOKUP_VALUES min,
FND_LOOKUP_VALUES hrs
where a.period_type(+) = per.lookup_code
and min.lookup_type = 'CSY_PROMPTS'
and hrs.lookup_type = 'CSY_PROMPTS'
and min.lookup_code = 'MINS'
and hrs.lookup_code = 'HOURS'
and per.lookup_type = 'CSY_PERIODS'
and per.LANGUAGE = userenv('LANG')
and per.View_APPLICATION_ID = 170
and per.SECURITY_GROUP_ID = fnd_global.lookup_security_group(per.LOOKUP_TYPE, per.VIEW_APPLICATION_ID)
and min.LANGUAGE = userenv('LANG')
and min.View_APPLICATION_ID = 170
and min.SECURITY_GROUP_ID = fnd_global.lookup_security_group(min.LOOKUP_TYPE, min.VIEW_APPLICATION_ID)
and hrs.LANGUAGE = userenv('LANG')
and hrs.View_APPLICATION_ID = 170
and hrs.SECURITY_GROUP_ID = fnd_global.lookup_security_group(hrs.LOOKUP_TYPE, hrs.VIEW_APPLICATION_ID)
order by 1
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.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:720)
Steps to Replicate
The issue can be reproduced at will with the following steps:
> Go to Agent Dashboard.
> Click on Mean Time to Resolve group KPI
The exception occurs.
Changes
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 |
Changes |
Cause |
Solution |
References |