Friends . .In the golden olden days - use pl/sql block looping tables variables counters etc etc . .Stumbled upon a method whereby a combination of lead lag case and .inline views can produce a decent report ad hoc . .I was amazed at the results. Cant explain the theory behind this though. (coz I dont understand the relational theory myself) . .Will leave that to Janet and John . .-- Query to output overlapping absences .select ppf.employee_number ppf.last_name a.* .from .( .select date_start date_end last_enddate person_id .case when last_enddate is null then null . when last_enddate < date_start-1 then 'Gap' . when last_enddate > date_start then 'Overlap' .-- when date_end is not null and next_startdate is null then 'Missing next' . else null .end another_flag .from ( . select paa.* . lag(date_end) over (partition by person_id order by date_start) . last_enddate . lead(date_start) over (partition by person_id order by date_start) . next_startdate . from per_absence_attendances paa . order by person_id date_start . ) .) a .per_all_people_f ppf .where another_flag = 'Overlap' .and a.person_id = ppf.person_id .and a.date_start between ppf.effective_start_date and ppf.effective_end_date . . .-- Please point out any errors and constructive criticism welcome .cheers .Vin

Bolt on question . .Why does teh absence form just produce a warning and not an error when overlapping absences. Is this standard behaviour? . .How do other sites cope with this behaviour? .Custom library job??? or .Has the functionality been enhanced by a new patch?

Vin I seem to recall that if the absences are 'Sickness' then it's an error. . .Intrigued by the query I shall have a look at that next week! Nice one...

Seen some similar code from our Datawarehouse boys here at XYZ. Some of the functions these guys use are mind-blowing. . .Regarding the overlapping issue i'd tend to agree with CT. Unless the absence is SICKNESS you can quite happily have overlapping absences. . .I would normally reserve final judgement on this topic to Mr Grewal the disputed king of absence however since here rarely frequents this establishment we may just have to leave the question-mark hanging...

[quote:b4277b3dfd]Mr Grewal the disputed king of absence[/quote:b4277b3dfd] .No truer word spoken on any HRMS-related bulletin board... where is the old fraud anyway?

I am afraid Absences can overlap.

Hurrah he lives! (Note to self - sell all remaining Oracle shares ASAP)

In fact he needed a follow up e-mail to log on ... . .and all he came up with was 6 words and a full stop.

Grewal you can't leave us hanging like that....please explain.....

Actually i've just had a chat with the Mr Absence and he explains is 6 words (and a full-stop) thus... .Overlapping absences are allowed because it needs to cater for multiple assignments. Until such time as the Absence model changes to Assignment-level (rather than its currently antiquated person-level) we're always going to get kicked by this. . .WHen the model does switch he assures me that overlapping SICKNESS absences for the same assignment will not be allowed. . .I'll give him a nudge and see if he wishes to add any further enlightenment.

Hey for all you funkies out there who don't have access to a tame techie of the Kumar/Cooper/Tucker calibre here is my patent method (using Excel) that will also find any duplicate or overlapping absences.

See you, Sandra


How to find overlapping or duplicate Absence records in Oracle HRMS

1. Log into Oracle via UK HRMS Manager

2. Navigate to View>Lists>Employees by Absence Type

3. Pick in your required Absence Type e.g. Certified Sick and click Find

4. Navigate to File>Export

5. Click on the “Continue to end” button

6. Make a cup of coffee, this process takes about 5 minutes per 1000 records!

7. When prompted, choose SAVE and save the file somewhere on the fileserver.

8. Now open MS Excel and navigate to File>Open

9. Find the file you just saved making sure you search for Files of Type “All Files”
and click Open

10. The import wizard opens, just click on Finish (default settings are fine for this particular export) as long as “Delimited” is selected

11. The data is displayed in a new Excel workbook.

12. Auto-size the columns then click anywhere in the data and navigate to Data>Sort
Use sort params of :-
Employee Number (ascending)
Start Date (ascending)
End Date (ascending)

13. In cell F1 put a heading e.g. “Duplicates and Overlaps”

14. In cell F2 copy and paste the following formula :-

=IF(AND(B2=B1,D1>=C2),"X","")

NB: If you just want to find the duplicates then use the formula :-
=IF(CONCATENATE(B2,C2,D2)=CONCATENATE(B1,C1,D1),"X","")

15. Now click into cell F2 and using the handle on the bottom right, click and drag the
formula in column F down to the last row of data.

16. Now highlight row 1 and navigate to Data>Filter>Autofilter

17. Click the filter on column F and choose “X”

18. This will show you any records which duplicate or overlap the record on the row above.

19. Job done!

Hi all
This is my first venture into the HRcon forum to warn you to 'Beware the overlapping absence' as we have had some horrendous issues here at Network Rail during payroll processing. We have applied a patch that prevents our users enrolling overlapped absences (although we still allow them to enter overlaps as per standard functionality). This has helped and hindered us, as Development (in their infinite wisdom) forgot to check that we could back out these absence enrollments to correct the records - the same patch that prevented the enrollment also prevented the backing out...you have been warned!! We are about to apply yet another patch to enable us to correct our data so that several employees stop erroring each month! Can give you the details of the patches if you're interested...
Ailsa

Hello Ailsa, and thanks for the warning. Please feel free to post the details of any relevant patch numbers as part of this thread.

Cheers

Clive

Clive
The patches I referred to are 3581819 to fix the initial issue (although searching for this patch will redirect you to a later version in Metalink) plus 4424554 to fix the issue or de-enrolling the absences.
Development also confirmed that the patches required
for Family Pack J and K are :
1. 4477390 -- GB PQP PATCH ON TOP OF 11i.HR_PF.J - AUGUST 2005
2. 4570501 -- GB OSP SCHEMES DO NOT TAKE INTO ACCOUNT ABSENCE SCHEDULE
+
Latest HRGLOBAL (if they haven't allready applied it)
Ailsa