14 Replies Latest reply: Nov 2, 2012 5:31 AM by 898045 RSS

    Stop Leaves for same dates at Absence Entry Level

    898045
      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
          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
            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
              Giuseppe Bonavita
              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
                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
                  Giuseppe Bonavita
                  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
                    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
                      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
                        Giuseppe Bonavita
                        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
                          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
                            Giuseppe Bonavita
                            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
                              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
                                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
                                  Giuseppe Bonavita
                                  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
                                    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