SVC_QUEUE_CAPACITIES_OPT

Details

Schema: FUSION
Object owner: SVC
Object type: VIEW

Columns

Name
QUEUE_ID REAL_TIME_CAPACITY NON_REAL_TIME_CAPACITY

Name

QUEUE_ID

REAL_TIME_CAPACITY

NON_REAL_TIME_CAPACITY

Query

SQL_Statement
SELECT /+ push_pred(freecaps) / queueagents.queue_id , Coalesce(SUM(freecaps.free_rt_capacity) / Count(), 0) / 100 AS real_time_capacity ,Coalesce(SUM(freecaps.free_non_rt_capacity) / Count(), 0) / 100 AS non_real_time_capacity FROM (SELECT rl.resource_party_id ,Max(last_assignment_date) max_assignment_date ,Round(100 - SUM(CASE WHEN rl.channel_type_cd != 'ORA_SVC_NONE' THEN Decode(rl.agent_capacity, 0, 0, rl.current_use / rl.agent_capacity) ELSE 0 END) * 100, 4) free_rt_capacity ,Round(100 - SUM(CASE WHEN rl.channel_type_cd = 'ORA_SVC_NONE' THEN Decode(rl.agent_capacity, 0, 0, rl.current_use / rl.agent_capacity) ELSE 0 END) * 100, 4) free_non_rt_capacity FROM (SELECT l.resource_party_id ,l.channel_type_cd ,l.object_type_cd ,l.last_assignment_date ,l.current_use ,Nvl(decode(po.enabled, 'Y', ac.max_capacity, null), gc.max_capacity) as agent_capacity FROM (SELECT ov.profile_option_value as enabled FROM fusion.fnd_profile_option_values ov, fusion.fnd_profile_options o WHERE ov.application_id = '10516' AND ov.profile_option_id = o.profile_option_id AND o.profile_option_name = 'SVC_OMNI_OVERRIDE_AGENT_CAPACITIES' AND ov.level_name = 'SITE' AND ov.level_value = 'SITE' AND ov.enterprise_id = '1' AND ROWNUM = 1) po, fusion.svc_resource_loads l join fusion.svc_resource_capacities gc ON l.channel_type_cd = gc.channel_type_cd AND nvl(l.object_type_cd, 0) = nvl(gc.object_type_cd, 0) AND gc.party_id IS NULL left join fusion.svc_resource_capacities ac ON l.channel_type_cd = ac.channel_type_cd AND nvl(l.object_type_cd, 0) = nvl(ac.object_type_cd, 0) AND l.resource_party_id = ac.party_id ) rl GROUP BY rl.resource_party_id ) freecaps , (SELECT queueresources.queue_id , resourceteammembers.team_resource_id as party_id FROM fusion.svc_queue_resources queueresources ,fusion.jtf_rs_team_members resourceteammembers WHERE ( queueresources.object_type_cd = 'ORA_SVC_RESOURCE_TEAM' AND queueresources.enabled_flag = 'Y' AND Upper(queueresources.deleted_flag) = 'N' ) AND queueresources.object_id = resourceteammembers.team_id AND resourceteammembers.delete_flag = 'N' UNION SELECT queueresources.queue_id , queueresources.object_id as party_id FROM fusion.svc_queue_resources queueresources WHERE ( queueresources.object_type_cd = 'ORA_SVC_RESOURCE' AND queueresources.enabled_flag = 'Y' AND Upper(queueresources.deleted_flag) = 'N' ) ) queueagents WHERE freecaps.resource_party_id = queueagents.party_id GROUP BY queueagents.queue_id

SQL_Statement

SELECT /*+ push_pred(freecaps) */

queueagents.queue_id

,

Coalesce(SUM(freecaps.free_rt_capacity) / Count(*), 0) / 100 AS real_time_capacity

,Coalesce(SUM(freecaps.free_non_rt_capacity) / Count(*), 0) / 100 AS non_real_time_capacity

FROM

(SELECT rl.resource_party_id

,Max(last_assignment_date) max_assignment_date

,Round(100 - SUM(CASE

WHEN rl.channel_type_cd != 'ORA_SVC_NONE' THEN Decode(rl.agent_capacity, 0, 0, rl.current_use / rl.agent_capacity)

ELSE 0 END) * 100, 4) free_rt_capacity

,Round(100 - SUM(CASE

WHEN rl.channel_type_cd = 'ORA_SVC_NONE' THEN Decode(rl.agent_capacity, 0, 0, rl.current_use / rl.agent_capacity)

ELSE 0

END) * 100, 4) free_non_rt_capacity

FROM

(SELECT l.resource_party_id

,l.channel_type_cd

,l.object_type_cd

,l.last_assignment_date

,l.current_use

,Nvl(decode(po.enabled, 'Y', ac.max_capacity, null), gc.max_capacity) as agent_capacity

FROM (SELECT ov.profile_option_value as enabled

FROM fusion.fnd_profile_option_values ov, fusion.fnd_profile_options o

WHERE ov.application_id = '10516'

AND ov.profile_option_id = o.profile_option_id

AND o.profile_option_name = 'SVC_OMNI_OVERRIDE_AGENT_CAPACITIES'

AND ov.level_name = 'SITE'

AND ov.level_value = 'SITE'

AND ov.enterprise_id = '1'

AND ROWNUM = 1) po,

fusion.svc_resource_loads l

join fusion.svc_resource_capacities gc

ON l.channel_type_cd = gc.channel_type_cd

AND nvl(l.object_type_cd, 0) = nvl(gc.object_type_cd, 0)

AND gc.party_id IS NULL

left join fusion.svc_resource_capacities ac

ON l.channel_type_cd = ac.channel_type_cd

AND nvl(l.object_type_cd, 0) = nvl(ac.object_type_cd, 0)

AND l.resource_party_id = ac.party_id

) rl

GROUP BY rl.resource_party_id

) freecaps

,

(SELECT

queueresources.queue_id

,

resourceteammembers.team_resource_id as party_id

FROM fusion.svc_queue_resources queueresources

,fusion.jtf_rs_team_members resourceteammembers

WHERE ( queueresources.object_type_cd = 'ORA_SVC_RESOURCE_TEAM'

AND queueresources.enabled_flag = 'Y'

AND Upper(queueresources.deleted_flag) = 'N' )

AND queueresources.object_id = resourceteammembers.team_id

AND resourceteammembers.delete_flag = 'N'

UNION

SELECT

queueresources.queue_id

,

queueresources.object_id as party_id

FROM fusion.svc_queue_resources queueresources

WHERE ( queueresources.object_type_cd = 'ORA_SVC_RESOURCE'

AND queueresources.enabled_flag = 'Y'

AND Upper(queueresources.deleted_flag) = 'N' )

) queueagents

WHERE freecaps.resource_party_id = queueagents.party_id

GROUP BY queueagents.queue_id