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(nvl(rl.agent_capacity, rl.global_capacity), 0, 0, rl.current_use / nvl(rl.agent_capacity, rl.global_capacity)) ELSE 0 END) * 100, 4) free_rt_capacity, round(100 - SUM( CASE WHEN rl.channel_type_cd = 'ORA_SVC_NONE' THEN decode(nvl(rl.agent_capacity, rl.global_capacity), 0, 0, rl.current_use / nvl(rl.agent_capacity, rl.global_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, gc.max_capacity AS global_capacity, ac.max_capacity AS agent_capacity FROM fusion.svc_resource_loads l JOIN fusion.svc_resource_capacities gc ON l.channel_type_cd = gc.channel_type_cd AND ((l.object_type_cd is null and gc.object_type_cd is null) OR (l.object_type_cd = gc.object_type_cd)) AND gc.party_id IS NULL LEFT JOIN fusion.svc_resource_capacities ac ON l.channel_type_cd = ac.channel_type_cd AND ((l.object_type_cd is null and ac.object_type_cd is null) OR (l.object_type_cd = ac.object_type_cd)) AND l.resource_party_id = ac.party_id AND ( ( SELECT profile_option_value AS enabled FROM fusion.fnd_profile_option_values WHERE profile_option_id = ( SELECT profile_option_id FROM fusion.fnd_profile_options WHERE profile_option_name = 'SVC_OMNI_OVERRIDE_AGENT_CAPACITIES' ) AND level_name = 'SITE' AND level_value = 'SITE' AND application_id = '10516' AND enterprise_id = '1' AND ROWNUM = 1 ) ) = 'Y' ) rl GROUP BY rl.resource_party_id ) freecaps, ( SELECT queueresources.queue_id, parties.party_id FROM fusion.svc_queue_resources queueresources, fusion.jtf_rs_team_members resourceteammembers, hz_parties parties 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' AND resourceteammembers.team_resource_id = parties.party_id AND parties.party_type = 'PERSON' UNION SELECT queueresources.queue_id, parties.party_id FROM fusion.svc_queue_resources queueresources, hz_parties parties WHERE ( queueresources.object_type_cd = 'ORA_SVC_RESOURCE' AND queueresources.enabled_flag = 'Y' AND upper(queueresources.deleted_flag) = 'N' ) AND queueresources.object_id = parties.party_id AND parties.party_type = 'PERSON' ) 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(nvl(rl.agent_capacity, rl.global_capacity), 0, 0, rl.current_use / nvl(rl.agent_capacity, rl.global_capacity))

ELSE 0 END) * 100, 4) free_rt_capacity,

round(100 - SUM( CASE WHEN rl.channel_type_cd = 'ORA_SVC_NONE' THEN

decode(nvl(rl.agent_capacity, rl.global_capacity), 0, 0, rl.current_use / nvl(rl.agent_capacity, rl.global_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,

gc.max_capacity AS global_capacity,

ac.max_capacity AS agent_capacity

FROM fusion.svc_resource_loads l

JOIN fusion.svc_resource_capacities gc

ON l.channel_type_cd = gc.channel_type_cd

AND ((l.object_type_cd is null and gc.object_type_cd is null) OR (l.object_type_cd = gc.object_type_cd))

AND gc.party_id IS NULL

LEFT JOIN fusion.svc_resource_capacities ac

ON l.channel_type_cd = ac.channel_type_cd

AND ((l.object_type_cd is null and ac.object_type_cd is null) OR (l.object_type_cd = ac.object_type_cd))

AND l.resource_party_id = ac.party_id

AND ( (

SELECT profile_option_value AS enabled

FROM fusion.fnd_profile_option_values

WHERE profile_option_id = (

SELECT profile_option_id

FROM fusion.fnd_profile_options

WHERE profile_option_name = 'SVC_OMNI_OVERRIDE_AGENT_CAPACITIES' )

AND level_name = 'SITE'

AND level_value = 'SITE'

AND application_id = '10516'

AND enterprise_id = '1'

AND ROWNUM = 1

) ) = 'Y' ) rl

GROUP BY rl.resource_party_id ) freecaps,

( SELECT

queueresources.queue_id,

parties.party_id

FROM

fusion.svc_queue_resources queueresources,

fusion.jtf_rs_team_members resourceteammembers,

hz_parties parties

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'

AND resourceteammembers.team_resource_id = parties.party_id

AND parties.party_type = 'PERSON'

UNION

SELECT

queueresources.queue_id,

parties.party_id

FROM fusion.svc_queue_resources queueresources,

hz_parties parties

WHERE

( queueresources.object_type_cd = 'ORA_SVC_RESOURCE'

AND queueresources.enabled_flag = 'Y'

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

AND queueresources.object_id = parties.party_id

AND parties.party_type = 'PERSON' ) queueagents

WHERE freecaps.resource_party_id = queueagents.party_id

GROUP BY queueagents.queue_id