My Oracle Support Banner

Contact Center: Performance Issue with Employee Popup (Doc ID 1359786.1)

Last updated on JULY 14, 2020

Applies to:

Oracle Customer Care - Version 12.0.6 and later
Information in this document applies to any platform.

Symptoms

On release 12.0.6:

Performance issue with Employee popup in Contact Center form.

This is the query to find the Employee data , which takes long:

SELECT HR.PERSON_ID ,
HR.PARTY_ID ,
HR.EMPLOYEE_NUMBER ,
HR.FIRST_NAME ,
HR.MIDDLE_NAME ,
HR.LAST_NAME ,
HR.FULL_NAME ,
HR.PREFIX ,
HR.EMAIL_ADDRESS ,
PHO.PHONE_NUMBER ,
HRL.MEANING ,
PHO.PHONE_ID ,
LOC.ADDRESS_LINE_1
|| DECODE(LOC.ADDRESS_LINE_2 , NULL , NULL , ';'
|| LOC.ADDRESS_LINE_2 )
|| DECODE(LOC.ADDRESS_LINE_3 , NULL , NULL , ';'
|| LOC.ADDRESS_LINE_3 ) ADDRESS ,
LOC.ADDRESS_LINE_1 ,
LOC.ADDRESS_LINE_2 ,
LOC.ADDRESS_LINE_3 ,
LOC.TOWN_OR_CITY CITY ,
TR.TERRITORY_SHORT_NAME COUNTRY ,
LOC.POSTAL_CODE ,
LOC.COUNTRY COUNTRY_CODE
FROM PER_WORKFORCE_X HR ,
PER_PHONES PHO ,
HR_LOOKUPS HRL ,
HR_LOCATIONS LOC ,
HR_TERRITORIES_V TR
WHERE HR.PERSON_ID = PHO.PARENT_ID (+)
AND PHO.PHONE_TYPE (+) = 'W1'
AND PHO.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND SYSDATE BETWEEN PHO.DATE_FROM (+) AND NVL(PHO.DATE_TO (+) , SYSDATE )
AND PHO.PHONE_TYPE = HRL.LOOKUP_CODE (+)
AND HRL.LOOKUP_TYPE (+) = 'PHONE_TYPE'
AND LOC.LOCATION_ID (+) = HR.LOCATION_ID
AND TR.TERRITORY_CODE (+) = LOC.COUNTRY
AND HR.PERSON_ID = NVL(:b1 , HR.PERSON_ID )
AND HR.EMPLOYEE_NUMBER IS NOT NULL
AND HR.EMPLOYEE_NUMBER = NVL(:b2 , HR.EMPLOYEE_NUMBER )
AND ( :b3 = 'N'
OR ( :b3 = 'Y'
AND NVL(HR.TERMINATION_DATE , SYSDATE ) >= SYSDATE ) )
UNION ALL
SELECT HR.PERSON_ID ,
HR.PARTY_ID ,
HR.NPW_NUMBER EMPLOYEE_NUMBER ,
HR.FIRST_NAME ,
HR.MIDDLE_NAME ,
HR.LAST_NAME ,
HR.FULL_NAME ,
HR.PREFIX ,
HR.EMAIL_ADDRESS ,
PHO.PHONE_NUMBER ,
HRL.MEANING ,
PHO.PHONE_ID ,
LOC.ADDRESS_LINE_1
|| DECODE(LOC.ADDRESS_LINE_2 , NULL , NULL , ';'
|| LOC.ADDRESS_LINE_2 )
|| DECODE(LOC.ADDRESS_LINE_3 , NULL , NULL , ';'
|| LOC.ADDRESS_LINE_3 ) ADDRESS ,
LOC.ADDRESS_LINE_1 ,
LOC.ADDRESS_LINE_2 ,
LOC.ADDRESS_LINE_3 ,
LOC.TOWN_OR_CITY CITY ,
TR.TERRITORY_SHORT_NAME COUNTRY ,
LOC.POSTAL_CODE ,
LOC.COUNTRY COUNTRY_CODE
FROM PER_WORKFORCE_X HR ,
PER_PHONES PHO ,
HR_LOOKUPS HRL ,
HR_LOCATIONS LOC ,
HR_TERRITORIES_V TR
WHERE HR.PERSON_ID = PHO.PARENT_ID (+)
AND PHO.PHONE_TYPE (+) = 'W1'
AND PHO.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND SYSDATE BETWEEN PHO.DATE_FROM (+) AND NVL(PHO.DATE_TO (+) , SYSDATE )
AND PHO.PHONE_TYPE = HRL.LOOKUP_CODE (+)
AND HRL.LOOKUP_TYPE (+) = 'PHONE_TYPE'
AND LOC.LOCATION_ID (+) = HR.LOCATION_ID
AND TR.TERRITORY_CODE (+) = LOC.COUNTRY
AND HR.PERSON_ID = NVL(:b1 , HR.PERSON_ID )
AND HR.NPW_NUMBER IS NOT NULL
AND HR.ASSIGNMENT_TYPE = 'C'
AND HR.NPW_NUMBER = NVL(:b2 , HR.NPW_NUMBER )
AND ( :b3 = 'N'
OR ( :b3 = 'Y'
AND NVL(HR.TERMINATION_DATE , SYSDATE ) >= SYSDATE ) )  

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.