1. Tables and Views for Sales and Fusion Service
  2. SVC_RESOURCE_CHANNEL_CAPS_V

SVC_RESOURCE_CHANNEL_CAPS_V

Details

  • Schema: FUSION

  • Object owner: SVC

  • Object type: VIEW

Columns

Name

RESOURCE_PARTY_ID

CHANNEL_TYPE_CD

OBJECT_TYPE_CD

PRESENCE_CD

AUTO_AGENT_FLAG

ACCEPT_WORK_FLAG

REALTIME_FLAG

FREE_CAPACITY

USED_CAPACITY

CAPACITY_IMPACT

LAST_ASSIGNMENT_DATE

USED_CAPACITY_ABS

MAX_CAPACITY_ABS

Query

SQL_Statement

with resource_capacities

as

(

select * from svc_resource_capacities c

where (

c.party_id is null and (select profile_option_value as enabled from FND_PROFILE_OPTION_values

where PROFILE_OPTION_ID = (select PROFILE_OPTION_ID from 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) = 'N') or

((select profile_option_value as enabled from FND_PROFILE_OPTION_values

where PROFILE_OPTION_ID = (select PROFILE_OPTION_ID from 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'

)

SELECT

l.resource_party_id,

l.channel_type_cd,

c.object_type_cd,

p.presence_cd,

p.auto_agent_flag,

l.accept_work_flag,

op.realtime_flag realtime_flag,

DECODE(c.max_capacity,0,0,round((1-(l.current_use/c.max_capacity))*100,4)) free_capacity,

DECODE(c.max_capacity,0,100,round((l.current_use/c.max_capacity)*100,4)) used_capacity,

DECODE(c.max_capacity,0,101,round((1/c.max_capacity*100),4)) capacity_impact,

l.last_assignment_date,

l.current_use used_capacity_abs,

c.max_capacity max_capacity_abs

FROM svc_resource_loads l, resource_capacities c, svc_resource_presences p, svc_omni_channel_props op

WHERE

l.channel_type_cd = c.channel_type_cd

AND ( l.object_type_cd = c.object_type_cd

OR ( l.object_type_cd IS NULL

AND c.object_type_cd IS NULL )

)

AND ( op.channel_type_cd = l.channel_type_cd

AND ( (op.network_type_cd IS NULL

AND ( ( l.object_type_cd IS NULL )

OR l.channel_type_cd = 'ORA_SVC_NONE' ) )

OR op.network_type_cd = l.object_type_cd) )

AND (l.resource_party_id = c.party_id

OR (NOT EXISTS (SELECT 1 FROM resource_capacities

WHERE party_id = l.resource_party_id

AND ( l.channel_type_cd = channel_type_cd

AND ( l.object_type_cd = object_type_cd

OR ( l.object_type_cd IS NULL

AND object_type_cd IS NULL ) )

AND ( op.channel_type_cd = l.channel_type_cd

AND (( op.network_type_cd IS NULL

AND ( ( l.object_type_cd IS NULL )

OR l.channel_type_cd = 'ORA_SVC_NONE' ) )

OR op.network_type_cd = l.object_type_cd )) ))

AND c.party_id is null

)

)

and (p.resource_party_id = l.resource_party_id)