Can you show some of the sp?  That would help.

That way you can get some help on how to pass the logic: select max(effective_date) where emply_status='A' to it.  I think that's what you're after.