Hi All,
I was hoping to get some help from some SQL and PL/SQL experts on a problem which I’ve been battling to solve, without lots of subquery and self joins and performance problems.
I would ideally like to create a PL/SQL function that would return an Assignment Effective Start Date from an employees record in HR eBusiness Suite based on a few conditions.
I find it easiest to explain with the below table example, which is for a single person, lets say person_id = 3546:
*<PRE> ID Effective Start Date Effective End Date </PRE>*
<PRE> 01 01-DEC-2009 31-DEC-2009 </PRE>
<PRE> 01
*01-NOV-2009* 30-NOV-2009 </PRE>
<PRE> 88 01-OCT-2009 31-OCT-2009 </PRE>
<PRE> 01 01-SEP-2009 30-SEP-2009 </PRE>
<PRE> 67 01-AUG-2009 31-AUG-2009 </PRE>
Above are all the assignments for person_id 3546 with the ID of the Job/Grade they have been assigned. What I need to achieve is, that if I run a report as of the 15-DEC-2009, I would like see the 'Effective Start Date' of '01-NOV-2009' for the most recent ID that has been assigned to the person, in this case ID 01. It shouldn't group ID 01 and give the the date of '01-SEP-2009'.
Some more examples:
If I change the as of date to '10-NOV-2009' and run the report, i would get an 'Effective Start Date' of '01-NOV-2009' for ID 01.
If I change the as of date to '15-OCT-2009' and run the report, i would get an 'Effective Start Date' of '01-OCT-2009' for ID 88.
If I change the as of date to '29-SEP-2009' and run the report, i would get an 'Effective Start Date' of '01-SEP-2009' for ID 01 .
Have i explained enough?? And has anyone some ideas?
Many thanks,
Lance