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

SVC_RESOURCE_FREE_CAPS_V

Details

  • Schema: FUSION

  • Object owner: SVC

  • Object type: VIEW

Columns

Name

RESOURCE_PARTY_ID

MAX_ASSIGNMENT_DATE

FREE_RT_CAPACITY

FREE_NON_RT_CAPACITY

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,

MAX(last_assignment_date) max_assignment_date,

ROUND(100- SUM(CASE WHEN op.realtime_flag = 'Y' THEN DECODE(c.max_capacity,0,1,l.current_use/c.max_capacity) ELSE 0 END) *100,4) free_rt_capacity,

ROUND(100- SUM(CASE WHEN op.realtime_flag = 'N' THEN DECODE(c.max_capacity,0,1,l.current_use/c.max_capacity) ELSE 0 END) *100,4) free_non_rt_capacity

FROM

svc_resource_loads l, resource_capacities c, 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))

GROUP BY resource_party_id