SQL Error In Customer Support Agent KPI Dashboard (Doc ID 1579500.1)

Last updated on JULY 21, 2020

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.