This discussion is archived
14 Replies Latest reply: Nov 2, 2012 3:31 AM by 898045 RSS

Stop Leaves for same dates at Absence Entry Level

898045 Newbie
Currently Being Moderated
Hi,

I have a requirement where I have to put a validation on SSHR absence screen to stop employees to apply for same type of leaves or for same period.

For e.g -> Suppose employee A has applied for a Privilege leave from 01-Oct-2012 to 02-oct-2012. Now he should not be able to apply any Privilege Leave until this
applied leave gets approved. Additionaly, he should not be able to apply any other leave for 01-Oct-2012 and 02-OCt-2012. These validations has to come in effect at the entry screen only.

As soon as A tries to violate any of the above rules while applying for leaves, he should get an error.
I tried to use a 'user hook' at create_absence api to achieve this. But there are some shortcomings in that.

Is there any other way I can achieve this?

Regards,
Saurabh
  • 1. Re: Stop Leaves for same dates at Absence Entry Level
    Ashish_Apps Journeyer
    Currently Being Moderated
    Hi Saurabh,

    First of all let me congratulate you for posing the the question so clearly.

    Now, coming to the solution, i have used user_hook approach and it works fine . Can you please letme know the shortcomings.
  • 2. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Hi Ashish,

    User hook is getting called at the time when employee applies for the leave. The same logic is getting called when the leave is going to be committed.
    Please have a look at the function below which returns true if the new leave matches any of the criteria mentioned above and then throws error so that employees are not allowed to go ahead with the erroneous leave.

    So when the leave is going to get comitted, it calls below function again via user hook and then treats itself(the existing leave) as pending leave and hence throws the error.




    CREATE OR REPLACE FUNCTION APPS.IS_LEAVE_ALREADY_APPLIED_func(p_assignment_id VARCHAR2,
    p_Absence_Type_Id VARCHAR2,
    p_l_s_Date DATE,
    p_l_e_Date DATE)
    RETURN VARCHAR2 IS

    l_decision VARCHAR2(10):= 'FALSE';

    CURSOR extisting_unapproved_entry(l_assignment_id VARCHAR2, l_Absence_Type_Id VARCHAR2) IS
    Select
    pssh.*
    FROM
    hr_api_transactions hat,
    pqh_ss_transaction_history psth,
    pqh_ss_step_history pssh,
    Per_Absence_Attendance_Types paat
    WHERE
    hat.status IN ('W', 'Y')
    AND hat.assignment_id = psth.assignment_id
    AND hat.assignment_id = l_assignment_id
    AND hat.transaction_id = pssh.transaction_history_id
    AND pssh.transaction_history_id = psth.transaction_history_id
    -- AND pssh.INFORMATION5 = l_Absence_Type_Id
    AND pssh.INFORMATION5 = paat.absence_attendance_type_id
    AND to_date(pssh.INFORMATION1, 'YYYY-MM-DD')> '1-Jan-2012'
    AND not exists (select ABSENCE_ATTENDANCE_ID from per_absence_attendances
    where ABSENCE_ATTENDANCE_ID = pssh.pk1)
    AND exists (select paat.absence_attendance_type_id from Per_Absence_Attendance_Types paat
    where absence_attendance_type_id = l_Absence_Type_Id
    );

    record_unapproved_entry extisting_unapproved_entry%ROWTYPE;
    l_item_key hr_api_transactions.item_key%TYPE;

    BEGIN

    OPEN extisting_unapproved_entry(p_assignment_id, p_Absence_Type_Id);
    LOOP
    FETCH extisting_unapproved_entry INTO record_unapproved_entry;

    IF extisting_unapproved_entry%ROWCOUNT = 0
    THEN
    l_decision:= 'FALSE';

    ELSE
    IF (((p_l_s_Date between to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND to_date(record_unapproved_entry.information2, 'YYYY-MM-DD')) OR
    (p_l_e_Date between to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND to_date(record_unapproved_entry.information2, 'YYYY-MM-DD')) OR
    ( p_l_s_Date <= to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND p_l_e_Date >= to_date(record_unapproved_entry.information2, 'YYYY-MM-DD'))) OR
    (record_unapproved_entry.INFORMATION5 = p_Absence_Type_Id))
    THEN
    l_decision:= 'TRUE';
    goto l_endofloop;
    ELSE
    l_decision:= 'FALSE';
    END IF;
    END IF;
    EXIT WHEN extisting_unapproved_entry%NOTFOUND;
    END LOOP;

    <<l_endofloop>>
    RETURN l_decision;

    END IS_LEAVE_ALREADY_APPLIED_func;
  • 3. Re: Stop Leaves for same dates at Absence Entry Level
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    Dear Saurabh,

    you might need to change the formula which calculated the leave duration (which usually is BG_ABSENCE_DURATION).
    There you might return an error for entries not compliant with your business.


    Thanks
    Regards
    Giuseppe
  • 4. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Hi Giuseppe,

    i was just wondering how would I capture an application error based on 'Calculate Duration' Button.
    Can you elaborate?

    Thanks,
    Saurah
  • 5. Re: Stop Leaves for same dates at Absence Entry Level
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    Dear Saurah,


    add the following code to the formula:

    duration = 'FAILED'
    invalid_msg = 'XX_YOUR_MESSAGE'

    return duration, invalid_msg



    Thanks
    Regards
    Giuseppe
  • 6. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Giuseppe,

    Let me change the logic and test whether it works fine.
    Will post the results here.

    Thanks,
    Saurabh
  • 7. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Giuseppe,

    I changed the logic, but when i am reasigning the leave while the leave is in transition, it again errors out before comitting to the database.

    Does the BG_ABSENCE_DURATION formula get all the validations at the time of applying the leaves as well as at the time of comitting to the db?
  • 8. Re: Stop Leaves for same dates at Absence Entry Level
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    The formula is called before submission; my understanding was that the validation was needed before submission.

    The DB commit validation is achieved through User Hook (or business event).
  • 9. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Giuseppe,

    I will rephrase my doubt.

    Requirement is to validate the leaves just at the time of applying not at the time of commiting to the db.
    But, what I observed was that the logic written in this fast formula got called while comitting to the db also.
    Hence it threw an error.

    Is it so, does the validations written in this fast formula will be called just before comitting to the db also?

    Regards,
    Saurabh
  • 10. Re: Stop Leaves for same dates at Absence Entry Level
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    Sorry Saurabh,

    what do you mean with "while comitting to the db also"? does it mean when using professional form?


    Thanks
    Regards
    Giuseppe
  • 11. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Hi Giuseppe,

    Hope you are doing well.
    Sorry for the delayed response, since I was off-sick yesterday !!

    'While Comitting to the ab also' means that after all the approvals through SSHR, when the transactions is goint to be comitted, again this BG_Absence_Duration formula is getting called.
  • 12. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Hi all,

    I put a validation in BG_Absence_Duration formula to stop employees to apply for similar leaves if existing leave is still not approved.
    For e.g -> IF A has applied Privilege Leave from 01-Oct-2012 to 02-Oct-2012. Then he shoould not be able to apply for a new Privilege leave unless
    the existing leave is approved.

    It works fine while applying, but after approvals none of the leaves are going to database throwing the same error which comes when we apply for a new PL.

    Probably , the problem is that this (BG_Absence_Duration) formula is getting called while applying for leave as well as after all the approvals.

    May somebody help?
  • 13. Re: Stop Leaves for same dates at Absence Entry Level
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    Dear Saurabh,

    maybe you can work trying to prevent the transaction passing the validation upon submission then failing upon approval.


    Regards
    Giuseppe
  • 14. Re: Stop Leaves for same dates at Absence Entry Level
    898045 Newbie
    Currently Being Moderated
    Dear Giuseppe,

    I tried that way, but not able to do with the function posted in previous post.
    I dont have information at the end of the transaction to know whether last approver is approving that record.

    If I have that information at the end of the transaction, then I can filter such records to go through.

    Probably, I will go to the last resort and extend the contoller of the page to put the validation through Framework.


    Thanks,
    Saurabh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points