Who's Online
0 registered (), 1 Guest and 0 Spiders online.
Key: Admin, Global Mod, Mod
Recent Posts
Defaulting PO Information on assignment screen
by SBi
Today at 02:24 PM
E-Bus Configuration and Management
by PeterP
Today at 01:28 PM
UMX : Oracle User Management Application
by natkins
Today at 12:58 PM
HRMS organization structures
by Geoff_Dixon
Today at 11:53 AM
HR_PF.K RUP4
by bcooper
Yesterday at 06:50 PM
Pay_Action_Parameter
s

by John Akinyode
09/03/10 11:05 AM
Error ORA-01002: fetch out of sequence
by delboy
03/03/10 04:46 PM
Multiple html sessions opening
by MarinaH
03/03/10 12:16 AM
Tories to revamp PAYE system
by Geoff_Dixon
26/02/10 01:48 PM
Employee to Contingent System Person Type
by CT
25/02/10 12:46 PM
Top Posters (30 Days)
CT 21
delboy 19
bcooper 14
MarinaH 14
natkins 14
SBi 10
gaztorres 8
Geoff_Dixon 7
Gus 4
Simon_Mc 3
(Views)Popular Topics
Family Pack K issues thread 5172
Review of my Release 12 laptop 3113
CREATE_GRADE api returns:PLS-00306: wrong number o 3085
Log-on problems for existing users 2997
Creating hr jobs ORA-20001: HR_289477_JOB_GROUP_ID 2340
October UK-OUG? 1956
Viewing Output of another user 1887
uploading bank branches to PAYROLL 1850
Still trying to locate... 1836
Family Pack K 1822
Topic Options
Rate This Topic
#1124 - 23/03/05 02:12 PM Janet & John do Date-track Queries
CT Offline
veteran
*****

Registered: 11/03/05
Posts: 751
Loc: Back at Fort Apache, Gosport!
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

Top
#1125 - 28/07/05 07:38 AM Re: Janet & John do Date-track Queries
jhatten Offline
hacker

Registered: 15/03/05
Posts: 95
Loc: London
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

Top
#1126 - 08/08/05 09:53 PM Re: Janet & John do Date-track Queries
CT Offline
veteran
*****

Registered: 11/03/05
Posts: 751
Loc: Back at Fort Apache, Gosport!
Ah let the royalty cheques commence.... KER CHIIIIINGG
_________________________
L&K
CT

Top
#1127 - 10/08/05 03:57 PM Re: Janet & John do Date-track Queries
bcooper Offline
veteran
*****

Registered: 11/03/05
Posts: 886
Loc: Earth, Europe, England, here
<cough>sponsorship</cough>
_________________________
I'm blogging again - albeit sporadically
Also my random musings courtesy of Twitter

Top
#1128 - 11/08/05 08:27 AM Re: Janet & John do Date-track Queries
CT Offline
veteran
*****

Registered: 11/03/05
Posts: 751
Loc: Back at Fort Apache, Gosport!
How far are you walking and which charity is it for?
_________________________
L&K
CT

Top
#1129 - 22/08/06 12:10 PM Re: Janet & John do Date-track Queries [Re: CT]
jemson Offline
regular

Registered: 16/04/05
Posts: 25
Loc: New York City
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??!!!
_________________________
Joules

Top
#1130 - 23/08/06 04:29 PM Re: Janet & John do Date-track Queries [Re: jemson]
CT Offline
veteran
*****

Registered: 11/03/05
Posts: 751
Loc: Back at Fort Apache, Gosport!
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...
_________________________
L&K
CT

Top


Moderator:  CT 
March
Su M Tu W Th F Sa
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
Forum Stats
421 Members
48 Forums
1122 Topics
5226 Posts

Max Online: 15 @ 20/10/09 02:30 PM
Today's Birthdays
No Birthdays
Recent vacancies
Developer - Permanent - based in Bracknell
by Geoff_Dixon
Today at 11:59 AM
Top Posters
bcooper 886
CT 751
Geoff_Dixon 340
delboy 255
SBi 249
vkumar 223
kp_rapolu 200
cbrookes 197
Gavin Harris 101
jhatten 95