1. Run the SQL to confirm:
select * from prcalendar
where PRRESOURCEID in
( select PRRESOURCEID from prcalendar
group by PRRESOURCEID
having count(PRRESOURCEID) > 1 )
2. If there are two records, note the PRID for each row.
9. Take a backup of the table RPT_CALENDAR before purging the data in it.
3. Confirm which one is used in prj_resources for this resource:
select prcalendarid from prj_resources where prid ='<resource id from step 1>
4. Backup the PRCALENDAR table
5. Delete one of the duplicate entries:
delete from prcalendar where prid = <id of the one which should be removed>
6. Connect to Clarity - Home - Resources - Click on Calendar tab for the affected resource
7. Make sure there is no error message.
8. Now, run Load Data Warehouse - Full Load
These steps above will work fine if DWH is not being used for that Clarity PPM environment. But, for an environment where DWH and Advanced Reporting is used and configured, the RPT_CALENDAR table needs to be cleaned as well. Because, when the Load DWH job gets executed, the data from the RPT_CALENDAR table gets populated to the PRCALENDAR table. So, the RPT_CALENDAR table cannot have any duplicate records in order to fix this problem.
Hence, after step number 7, as mentioned above, follow the below steps:
8. The Clarity PPM services need to be stopped again.
10. Truncate table RPT_CALENDAR;
11. Start all the services.
12. Run “Update Report Tables” job with “Update Reporting Calendar “checked and wait for job to complete.
13. Run the Load Data Warehouse job in full load.