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 laterInformation 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 ) )
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 |