From the J&J archives of 07/04/2003:

Morning all

Here's a little anecdote primarily (but not exclusively) aimed at our non-HR/Payroll friends who may be called upon at some stage to go ferreting around for information on HR/Payroll/Benefits DateTracked tables. Apologies to anyone who thinks I'm stating the bl**din' obvious but if it helps only one person to avoid the problem then I would deem it a worthwhile excercise

Last week whilst on site at a large council in the Cheshire area doing some payroll-related work I was accosted by a rather excitable council employee who took great pains to explain to me that Oracle HR has a very serious bug in it. (A bug? In Oracle HR? Shome mishtake shurely!!!)

Apparently as well as HR and Payroll they also have Oracle Purchasing and Oracle Projects. This gentleman being of the 'Projects' persuasion was being informed by Projects that certain submitted timecards were not valid as the system could find no supervisor to approve them. He had done the usual TAR-raising activity whereupon it was found that the rejected timecards were all for employees whose supervisors had a change to their assignment record effective the day after the timecard submission date. The reason is quite simple but may reveal something of a misconception on the part of the Oracle Projects Development Team where DateTrack is concerned.

Basically the bit of code trying to get the supervisor included this:

Select <stuff>
FROM per_all_assignments_f
WHERE assignment_id = <whatever>
AND sysdate BETWEEN effective_start_date
AND effective_end_date
AND ...

All very easy. But wait!! It's not finding a row where there's a change starting tomorrow. Why not?

Well the thing about DateTrack is that there is never a time element involved (otherwise it would be called 'TimeTrack' and quite frankly that concept frightens me). It follows that the smallest unit of date effective change is 1 day. If you think of a date-effective change taking place from tomorrow in terms of just a date then in the table it might look like this:

ID Effective Start Date Effective End Date
123 01-JAN-2003 07-APR-2003
123 08-APR-2003 31-DEC-4712

The basic rule of date effective changes is being obeyed in that we have a contiguous span of time ie no missing days.

If however you look at the dates in terms of a date and time then it would look like this:
ID Effective Start Date Effective End Date
123 01-JAN-2003 00:00:00 07-APR-2003 00:00:00
123 08-APR-2003 00:00:00 31-DEC-4712 00:00:00

On the face of it things aren't any different. But look at the effective end date/time of the old record and compare it with the effective start date/time of the new record. There is in fact a 'gap' here of 24 hours! Because the code was using 'sysdate' to get the current assignment row it was in effect treating date track dates as date/time values and for these poor unfortunates was indeed 'falling down the gap'.

Moral of the story: Be like the elephant - use your trunc()!! (Cue loud groans shaking of heads leaping from nearest window etc)

OK how about: Using trunc(sysdate) in the above query would cure the problem instantly

Hope this helps somebody

Cheers

Janet & John

CT . .You can now consider this a worthwhile exercise - I have blatantly plagiarised this post to explain to our OTL Dev friends in the states the concept of DateTrack… they are still thinking about it. . .Jay

Ah let the royalty cheques commence.... KER CHIIIIINGG

<cough>sponsorship</cough>

How far are you walking and which charity is it for?

Ahh Clive

You have no idea how much I miss you correcting my pathetic attempts at SQL!!! Can I bribe you with imports of US goods??!!!

Hello Joules, how the devil are you? I can't believe they haven't cottoned-on to you and removed all traces of SQL access from your PC...