SVC_RESOURCE_WORK_IMPACT

Details

Schema: FUSION
Object owner: SVC
Object type: VIEW

Columns

Name
RESOURCE_PARTY_ID REALTIME_FLAG WORK_TYPE CAPACITY_IMPACT MAX_ASSIGNMENT_DATE FREE_CAPACITY

Name

RESOURCE_PARTY_ID

REALTIME_FLAG

WORK_TYPE

CAPACITY_IMPACT

MAX_ASSIGNMENT_DATE

FREE_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, op.realtime_flag, case when op.realtime_flag = 'Y' then l.channel_type_cd else l.object_type_cd end WORK_TYPE, ROUND(DECODE(c.max_capacity,0,101,100/c.max_capacity), 4) capacity_impact, MAX(last_assignment_date) OVER(PARTITION BY resource_party_id) max_assignment_date, ROUND(100- SUM(DECODE(c.max_capacity,0,0,l.current_use/c.max_capacity)) OVER(PARTITION BY resource_party_id, op.realtime_flag) *100,4) free_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))

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,

op.realtime_flag,

case when op.realtime_flag = 'Y' then l.channel_type_cd else l.object_type_cd end WORK_TYPE,

ROUND(DECODE(c.max_capacity,0,101,100/c.max_capacity), 4) capacity_impact,

MAX(last_assignment_date) OVER(PARTITION BY resource_party_id) max_assignment_date,

ROUND(100- SUM(DECODE(c.max_capacity,0,0,l.current_use/c.max_capacity)) OVER(PARTITION BY resource_party_id,

op.realtime_flag) *100,4) free_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))