Performance Issue in PER_TIME_PERIODS Table When Accessing Absence Management Self-Service Human Resources (SSHR) Page, It Continues to Load and It Is Not Accessible (Doc ID 2728966.1)

Last updated on OCTOBER 02, 2023

Applies to:

Symptoms

Query with function call FND_PROFILE always appears in the top consuming / expensive  SQL queries


The issue can be reproduced at will with the following steps:

While running the WORKLOAD REPOSITORY report , observed the below SQL is highly consuming the time.

SELECT COUNT(*) FROM PER_TIME_PERIODS PTP WHERE PTP.PAYROLL_ID = :B2 AND DECODE(FND_PROFILE.VALUE('HR_CALCULATE_NUMBER_OF_PAY_PERIODS_BASED_ON'), 'REGULAR_PAYMENT_DATE', REGULAR_PAYMENT_DATE, END_DATE) BETWEEN TO_DATE('01/01/'||TO_CHAR(:B1 , 'YYYY'), 'DD/MM/YYYY') AND TO_DATE('31/12/' || TO_CHAR(:B1 , 'YYYY'), 'DD/MM/YYYY')

OR

Unable to access Absence Management Self-Service Human Resources (SSHR) page when clicking on Absence Management, it continues to load and it is not accessible.

From AWR Report a performance issue can be seen in PER_TIME_PERIODS table regarding the below query:

SELECT COUNT(*) FROM PER_TIME_PERIODS PTP
WHERE PTP.PAYROLL_ID = :B2
AND DECODE(FND_PROFILE.VALUE('HR_CALCULATE_NUMBER_OF_PAY_PERIODS_BASED_ON'),
'REGULAR_PAYMENT_DATE', REGULAR_PAYMENT_DATE, END_DATE) BETWEEN
TO_DATE('01/01/'||TO_CHAR(:B1 , 'YYYY'), 'DD/MM/YYYY') AND TO_DATE('31/12/'
|| TO_CHAR(:B1 , 'YYYY'), 'DD/MM/YYYY')

The issue can be reproduced at will with the following steps:

    1. Using Employee Self-Service responsibility.
    
    2. Navigate to Absence Management.
    
    3. Observe that Absence Management SSHR page continues to load and it is not accessible.

Cause

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