9 Replies Latest reply: Aug 21, 2013 1:24 AM by 984260 RSS

    Leave Restriction

    803639
      Dear All,
      We have implemented Leave Management in Oracle HR. Employees apply annual leave through self service. Below are issues we face:

      ***I gave a condition in BG_ABSENCE_DURATION FF to restrict employees to apply leave including a particular holiday. As expected, it is showing an error message to employees when they include a holiday X in their vacation. (Ex: If holiday X falls on 02-FEB-2012, he is not allowed to apply from 01-FEB-2012 to 15-FEB-2012). But if he clicks Next, he can proceed and submit_ the request. Even that leave request can be approved by Manager also.
      ***But during confirmation, system wont accept the leave to be confirmed and shows the error which we handled in FF.

      Desired Result:
      System should restrict employees at first step itself (during applying leave not during confirmation). Please let me know how to handle this.

      Regards
      Siva Kumar HVK
        • 1. Re: Leave Restriction
          Asif Ali Khan
          Hi,

          You can over come from this issue by developing USER HOOK where you can write your own business logic and validate accordingly.
          You can follow this note id.

          Note: 73170.1 -- Understanding and Using API User Hooks


          Thanks
          • 2. Re: Leave Restriction
            803639
            Hi Asif,
            Thanks for the reply. Below are the steps done. But No Result.

            /*** HR_API_HOOKS ****/
            select ahk.api_hook_id,
            ahk.api_module_id,
            ahk.hook_package,
            ahk.hook_procedure
            from hr_api_hooks ahk,
            hr_api_modules ahm
            where ahm.module_name='CREATE_PERSON_ABSENCE'
            and ahm.api_module_type = 'BP'
            and ahk.api_hook_type = 'AP'
            and ahk.api_module_id=ahm.api_module_id


            CREATE OR REPLACE package APPS.XXHR_ABS_VALID_UH as
            procedure BYB_ABSENCE_OVERLAP(p_person_id number,
            p_date_start DATE,
            p_date_end DATE);
            end XXHR_ABS_VALID_UH;


            CREATE OR REPLACE package body APPS.XXHR_ABS_VALID_UH as
            procedure BYB_ABSENCE_OVERLAP(p_person_id number,
            p_date_start DATE,
            p_date_end DATE) IS
            l_count number;
            l_person_id number;

            begin
            -- l_count:= 0;

            dbms_output.put_line('BEFORE BEGIN l_count:'||l_count);
            dbms_output.put_line('PERSON ID :'||p_person_id);

            BEGIN
            SELECT count(*) into l_count
            FROM PER_ABSENCE_ATTENDANCES PAA,
            PER_ABSENCE_ATTENDANCE_TYPES PAAT,
            PER_ALL_PEOPLE_F PAPF
            WHERE PAPF.PERSON_ID = PAPF.PERSON_ID
            AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
            AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
            AND PAPF.PERSON_ID = p_person_id
            AND ((p_date_start BETWEEN PAA.DATE_START AND PAA.DATE_END) OR (p_date_end BETWEEN PAA.DATE_START AND PAA.DATE_END));
            EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line('ERROR '||sqlerrm);
            l_count:=0;
            END;
            dbms_output.put_line('l_count'||l_count);
            --dbms_output.put_line('Person ID'||l_person_id);
            --dbms_output.put_line('l_count'||l_count);
            --dbms_output.put_line('l_count'||l_count);

            IF l_count>=1 THEN

            dbms_output.put_line('Inside the exception validation l count is'||l_count);

            dbms_standard.raise_application_error
            (num => -20999
            ,msg => 'DUPLICATED ABSENCES ARE NOT ALLOWED'||' '||l_count||' '||p_date_start||' '||p_date_end||' '|| p_person_id);
            END IF;
            END BYB_ABSENCE_OVERLAP;
            end XXHR_ABS_VALID_UH;


            /*** TESTING FROM DB SIDE ****/
            DECLARE
            P_PERSON_ID NUMBER;
            P_DATE_START DATE;
            P_DATE_END DATE;
            BEGIN
            P_PERSON_ID := 18344;
            P_DATE_START := '14-FEB-2012';
            P_DATE_END := '20-FEB-2012';
            APPS.XXHR_ABS_VALID_UH.BYB_ABSENCE_OVERLAP( P_PERSON_ID, P_DATE_START, P_DATE_END );
            COMMIT;
            END;



            /*** LINKING THE USER HOOK WITH PROCESS ****/

            DECLARE
            l_api_hook_call_id NUMBER;
            l_object_version_number NUMBER;
            BEGIN
            hr_api_hook_call_api.create_api_hook_call
            (p_validate => FALSE,
            p_effective_date => TO_DATE('01-MAR-2012'),
            p_api_hook_id => 3840,
            p_api_hook_call_type => 'PP',
            p_sequence => 3010,
            p_enabled_flag => 'Y',
            p_call_package => 'XXHR_ABS_VALID_UH',
            p_call_procedure => 'BYB_ABSENCE_OVERLAP',
            p_api_hook_call_id => l_api_hook_call_id,
            p_object_version_number => l_object_version_number
            );
            END;

            /*** Ran pre- processor in cmd prompt ***/
            Under $PER_TOP/admin/sql
            @hrahkall.sql


            /*** Executed below procedure ***/
            execute hr_api_user_hooks_utility.create_hooks_all_modules;


            /*** Checked the package HR_PERSON_ABSENCE_BK1 for my custom procedure ****/
            Available
            part of code given below:
            elsif l_legislation_code = 'NL' then
            HR_NL_DAILY_SICK_AND_RECOVERY.INSERT_PERSON_ABSENCE_CHANGES
            (P_ABSENCE_ATTENDANCE_ID => P_ABSENCE_ATTENDANCE_ID
            ,P_EFFECTIVE_DATE => P_EFFECTIVE_DATE
            ,P_PERSON_ID => P_PERSON_ID
            ,P_DATE_PROJECTED_START => P_DATE_PROJECTED_START
            ,P_DATE_START => P_DATE_START
            ,P_ABS_INFORMATION1 => P_ABS_INFORMATION1
            ,P_DATE_PROJECTED_END => P_DATE_PROJECTED_END
            ,P_DATE_END => P_DATE_END
            );
            end if;
            end if;
            if hr_api.call_cus_hooks then
            XXHR_ABS_VALID_UH.BYB_ABSENCE_OVERLAP
            *(P_PERSON_ID => P_PERSON_ID*
            *,P_DATE_START => P_DATE_START*
            *,P_DATE_END => P_DATE_END*
            *);*
            end if;hr_multi_message.end_validation_set;


            TESTING:
            I went to employee self service screen and tried to apply a leave which is present in db already.
            I didnt get any error but leave processed successfully.

            Let me know if I have missed any step in between?? Should I restart any server kind of??

            Thanks & Regards
            Siva Kumar
            • 3. Re: Leave Restriction
              803639
              Hi All,
              I'm not sure if these are the only steps to be done for user hooks in leave of absence.
              1. Found the API_HOOK_ID, api_module_id, package & procedure as follows:
              3839     1731     HR_PERSON_ABSENCE_BK1     CREATE_PERSON_ABSENCE_B

              2. Created a custom package procedure which is working absolutely fine as I checked with p_person_id, p_date_start, p_date_end

              3. Linked the custom procedure with api_hook_id as follows:
              DECLARE
              l_api_hook_call_id NUMBER;
              l_object_version_number NUMBER;
              BEGIN
              hr_api_hook_call_api.create_api_hook_call
              (p_validate => FALSE,
              p_effective_date => TO_DATE('01-MAR-2012'),
              p_api_hook_id => 3839,
              p_api_hook_call_type => 'PP',
              p_sequence => 3005,
              p_enabled_flag => 'Y',
              p_call_package => 'XXHR_ABS_VALID_UH',
              p_call_procedure => 'BYB_ABSENCE_OVERLAP',
              p_api_hook_call_id => l_api_hook_call_id,
              p_object_version_number => l_object_version_number
              );
              END;
              4. which created a record in HR_API_HOOK_CALLS with STATUS 'N'

              5. Ran pre-processor hrahkall.sql which completed successfully

              6. execute hr_api_user_hooks_utility.create_hooks_all_modules -- successfully completed.

              7. TESTED by going to SSHR Apply Leave page. It is NOT stopping the employee when overlapping leave is applied.

              Hope you understood steps. Let me know if there are steps missed.

              Regards
              Siva.
              • 4. Re: Leave Restriction
                921609
                hi,

                user hook registration steps are fine.you are able to see your procedure in HR_PERSON_ABSENCE_BK1.
                it means user hook proper.

                check your procedure and code properly and put debug script at each level so you can where user procedure fails.

                my comments to your query.....

                SELECT count(*) into l_count
                FROM PER_ABSENCE_ATTENDANCES PAA,
                PER_ABSENCE_ATTENDANCE_TYPES PAAT,
                PER_ALL_PEOPLE_F PAPF
                WHERE PAPF.PERSON_ID = PAA.PERSON_ID ----------- changed by me---------------------------------- PAPF.PERSON_ID
                AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
                AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
                AND PAPF.PERSON_ID = p_person_id
                AND ((p_date_start BETWEEN PAA.DATE_START AND PAA.DATE_END) OR (p_date_end BETWEEN PAA.DATE_START AND PAA.DATE_END));


                Regards,
                • 5. Re: Leave Restriction
                  Asif Ali Khan
                  Hi,

                  I just revised your procedure kindly compile in test it and let me know the result.

                  CREATE OR REPLACE package body APPS.XXHR_ABS_VALID_UH as
                  procedure BYB_ABSENCE_OVERLAP(p_person_id number,
                  p_date_start DATE,
                  p_date_end DATE) IS
                  l_count number;
                  l_person_id number;
                  user_error exception ;

                  begin
                  -- l_count:= 0;
                  dbms_output.put_line('BEFORE BEGIN l_count:'||l_count);
                  dbms_output.put_line('PERSON ID :'||p_person_id);
                  BEGIN
                  SELECT count(*) into l_count
                  FROM PER_ABSENCE_ATTENDANCES PAA,
                  PER_ABSENCE_ATTENDANCE_TYPES PAAT,
                  PER_ALL_PEOPLE_F PAPF
                  WHERE PAPF.PERSON_ID = PAPF.PERSON_ID
                  AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
                  AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
                  AND PAPF.PERSON_ID = p_person_id
                  AND ((p_date_start BETWEEN PAA.DATE_START AND PAA.DATE_END) OR (p_date_end BETWEEN PAA.DATE_START AND PAA.DATE_END));
                  EXCEPTION
                  WHEN OTHERS THEN
                  dbms_output.put_line('ERROR '||sqlerrm);
                  l_count:=0;
                  END;
                  dbms_output.put_line('l_count'||l_count);
                  --dbms_output.put_line('Person ID'||l_person_id);
                  --dbms_output.put_line('l_count'||l_count);
                  --dbms_output.put_line('l_count'||l_count);
                  IF l_count>=1 THEN
                  hr_utility.set_message (800, 'DUPLICATED ABSENCES ARE NOT ALLOWED');
                  hr_utility.raise_error;
                  raise user_error;

                  --dbms_output.put_line('Inside the exception validation l count is'||l_count);
                  --dbms_standard.raise_application_error
                  --(num => -20999
                  --,msg => 'DUPLICATED ABSENCES ARE NOT ALLOWED'||' '||l_count||' '||p_date_start||' '||p_date_end||' '|| p_person_id);
                  END IF;
                  exception
                  when user_error
                  then
                  hr_utility.set_message (800, 'DUPLICATED ABSENCES ARE NOT ALLOWED');
                  hr_utility.raise_error;
                  END BYB_ABSENCE_OVERLAP;
                  end XXHR_ABS_VALID_UH;

                  Thanks
                  Asif
                  • 6. Re: Leave Restriction
                    803639
                    Hi Asif,
                    No change. It is still processing the leave.

                    I raised an SR. I'll update you once I know the problem.

                    There shouldnt be any problem with custom package. May be patches..

                    Regards
                    Siva Kumar.
                    • 7. Re: Leave Restriction
                      Asif Ali Khan
                      Hi Siva,

                      No i do not think for this patch is required i will give a you a procedure code just use that procedure inside u custom procedure and debug whether the flow is correct or not if it's correct then it should work .

                      If you update your mail id i can send you the code directly.


                      Thanks
                      • 8. Re: Leave Restriction
                        803639
                        Hi Asif,
                        Sorry for the delayed response.

                        Please send me the code to my email signorehvk@gmail.com

                        Thank you
                        Siva Kumar
                        • 9. Re: Leave Restriction
                          984260

                          Hi Asif,

                           

                          I am also getting same issue. I have created the custom package which will is used to validate the different leave types as per the UDT values for different countries.

                           

                          I have successfully linked the custom package to API HOOK and then after ran the preprocessor. The status is valid and showing in the HR_PERSON_ABSENCE_BK1.CREATE_PERSON_ABSENCE_A. However, while submitting the absence the custom validation package is not working.

                           

                          Appreciate the expert comments.

                           

                          Thank you.