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?

Team Member Error

 

 

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.

 

select
  *
from
  tsdates
where
  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.

 

Daylight Savings Time window

 

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.

 

update
  tsdates
set
  start_date = trunc(start_date + 1)
where
  start_date != trunc(start_date)
;

update
  tsdates
set
  end_date = trunc(end_date + 1)
where
  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.

About the Author

Dan MacMillan - Integration Specialist

Dan has been developing software professionally for over 20 years, joining Emerald Associates in 2003.  His experience includes accounting, supply chain management, drilling program management, project management, and contract management integration, automation and dashboarding elements.

Dan learned how to program computers as a child by watching his older brother making games on his Commodore 64.  His interest in computers and programming drove him to teach himself BASIC, 6502 and 80386 assembly language programming, and then C so that he could write hobby programs. Moving from hobby to professional, Dan did his computer science studies at SAIT in Calgary.

In his first professional programming job, Dan had the autonomy to make mistakes, live with them, learn from them and fix them.  He realized that quality in software derives, not from what it does, but from the way it is written, and yields benefits such as having fewer bugs, and being easier to read and change.  On that project, Dan transitioned from programming hobbyist to craftsman with a “quality first” focus in his work.

Leave a comment

Please login to leave a comment.