Update Report Tables job fails with error "ORA-06512" in RPT_CALENDAR_SP

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The following error appears when the Update Report Tables job is run:

Couldn't execute SQL: BEGIN
RPT_CALENDAR_SP();
RPT_INV_HIERARCHY_SP();
END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "RPT_CALENDAR_SP", line 255
ORA-06512: at line 2

Environment

All Clarity versions

Cause

The weekly time slices do not start on the designated first day of the workweek.

When setting up the DWH time slices from the Administration > Data Administration > Time Slices page, ensure that the start day of the slice ID matches that of the Administration > Project Management > Settings > General > First Day of Work Week

Use the following queries to quickly find out what the days are set to:

select sr.id, sr.request_name, SR.FROM_DATE
from PRJ_BLB_SLICEREQUESTS SR
where SR.IS_DWH_REQUEST = 1
and SR.PERIOD = 1 --weekly
   order by SR.id

---

select prweekstart from PRSITE

• 0 - Sunday
• 1 - Monday
• 2 - Tuesday
• 3 - Wednesday
• 4 - Thursday
• 5 - Friday
• 6 - Saturday

Resolution

Reslice the DWH slice request to start on a day consistent with the First Day of Work Week set in Administration ->Settings

1. Go to Administration > Data Administration > Time Slices page
2. Modify all the Weekly DWH time slice IDs to conform to the First Day of Work Week day:
3. Sort all the weekly DWH time slices by ID.
4. Open each ID link in a new browser tab.
5. Modify the 'From Date' to an agreed date that has the same day of the week as that listed in Project Management ->Settings

Tip: To check what your First day of Work Week is set to from the UI side:

1. In Clarity, go to Administration > Project Management > Settings >
2. View the value set in General > First Day of Work Week