How to Perform Time Reporting Period Maintenance

Products

• Clarity PPM On Premise

Issue/Introduction

In order to remove timesheet data, the time reporting periods will need to be closed as a prerequisite.
As the number of timesheets are unknown in Classic > Administration > System Options > Security and Diagnostics > Health Report > Application Data,
is there a method to review how many timesheets are in the system other than the Modern > Timesheets workspace?

Resolution

1.Find out the number of time periods involved.

As an example, if needing to go over the total number of timesheets over a specific 8-year window (96 months), use the following:

--Oracle
select count(ts.prid) as timesheet_count
from prtimesheet ts
join prtimeperiod tp on ts.prtimeperiodid = tp.prid
where tp.prstart >= add_months(sysdate, -96)
and tp.prstart <= sysdate;

--SQL Server
select count(ts.prid) as timesheet_count
from prtimesheet ts
join prtimeperiod tp on ts.prtimeperiodid = tp.prid
WHERE tp.prstart >= DATEADD(month, -96, GETDATE())
AND tp.prstart <= GETDATE();

--Postgresql
select count(ts.prid) as timesheet_count
from prtimesheet ts
join prtimeperiod tp on ts.prtimeperiodid = tp.prid
where tp.prstart >= now() - interval '96 months'
and tp.prstart <= now();

The queries can then be converted or enhanced
to obtain specific timesheet properties and even time entry properties.

A portlet can then be created to review timesheets, that can be deleted.

2. Once the time periods are marked for deletion, the job:Delete Investments and Time Reporting Periods will delete the time periods.