20 March 2017
Team Member Error - "Current Timesheet Period Does Not Exist"
How To Fix The Error in Team Member When Daylight Savings Time Kicks In
We recently turned our clocks forward an hour for the daylight savings adjustment. Today our users could not log into Team Member - they got an error indicating that the “Current Timesheet Period Does Not Exist”. What gives?
There is a Knowledge Base article [2206622.1] on My Oracle Support discussing a related bug that was fixed in version 16.2.3. We are running 16.2.5. The workarounds on the article did not help us.
I ran some queries in the database and I saw that the timesheet periods in the window covered by Daylight Savings Time were off by one hour. Our timesheet weeks run from Saturday to Friday. Instead of a timesheet period that starts Saturday, March 18 at midnight, we had one that started Friday, March 17 at 11:00 PM. This confused the system.
Why is it an issue in the first place?
P6 is oriented around a 24 hour clock, and the times stored in the database are “local time” (times without a specified time zone). In the real world in areas affected by Daylight Savings Time adjustments, not all days are 24 hours long. Some are 25 hours long, and some are 23 hours long.
The proper way to fix this would be to record all times in UTC and allow a per-project (or possibly per-WBS) time zone to be set which would be used for scheduling. However, that would be a massive engineering effort and without expending that effort, the only alternatives remaining result in these kinds of “off by one hour” issues in the system. There is an option in the configuration utility to ignore daylight savings time. Enabling that option makes some things better and other things worse.
We may have generated these timesheet periods before we upgraded P6 to a version not affected by this bug. Either way, it needed to be fixed.
First I ran a query to find the timesheet periods that were affected.
start_date != trunc(start_date) or end_date != trunc(end_date)
I expected the affected periods to only fall in the Daylight Savings Time window. And they were.
I then ran two simple update statements to correct the problem. These update statements are provided without warranty. It is the responsibility of anyone using them to confirm their correctness for your specific environment and requirements. You should always have current, up-to-date backups before running any update statements. In our specific case, I needed to set the date to midnight of the following day for the affected records.
start_date = trunc(start_date + 1)
start_date != trunc(start_date)
end_date = trunc(end_date + 1)
end_date != trunc(end_date)
After confirming the data I committed the transaction and logged into Team Member. I was able to get in with no errors.