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