How to stop emp from applying duplicate leaves ?

Hi ,

  We have a requirements where we need to stop employee from applying duplicate leaves through Employee Self Service ( even if there is a pending transaction for the same date).

For Example : Employee will apply for Annual leave from 1-Apr-2014 to 5-Apr-2014 and it goes for Manager Approval. Before Manager take an action, again employee applies for a sick/annual leave from  4-Apr-2014 to 6-Apr-2014 and it goes for Manager Approval. Manager will approve both the leave and both get saved to Database.

To solve the above issue, I wrote a function which has below select query.

So my function checks, if there is a pending transaction for the applied date, if it's there, then it will return a flag true and wont let the employee to proceed. Now the function works fine when employee submits leave ( since initially there is no pending leave for the applied date) and when Manager approves , the function gets called again and finds that there is a pending transaction and the data doesn't get saved in the database.