4 Replies Latest reply: May 8, 2012 2:33 AM by Giuseppe Bonavita RSS

    Pending Leaves Data

    882688
      Dear all brothers ,

      I want a query to retrieve pending leaves data for employees , like start_date , end_date , leave_type and these things.

      Thank you in advance , i appreciate your help.
        • 1. Re: Pending Leaves Data
          Vigneswar Battu
          See a sample one -

          select hla.location_code Location,hou.name HR_Organization,
          papf.full_name,papf.National_identifier, paat.name,paa.date_start,paa.date_end,paa.date_notification,
          paa.absence_days,paa.absence_hours
          from apps.per_absence_attendances paa
          ,apps.per_absence_attendance_types paat
          ,apps.per_all_people_f papf
          ,apps.per_all_assignments_f paaf
          ,apps.hr_locations_all hla
          ,apps.hr_all_organization_units hou
          where papf.business_group_id = <BG_ID>
          and paaf.business_group_id = <BG_ID>
          and paa.business_group_id = <BG_ID>
          and paat.absence_attendance_type_id = paa.absence_attendance_type_id
          and papf.person_id = paa.person_id
          and paaf.person_id = papf.person_id
          and sysdate between papf.effective_start_date and papf.effective_end_date
          and sysdate between paaf.effective_start_date and paaf.effective_end_date
          and hou.organization_id(+) = paaf.organization_id
          and hla.location_id(+) = paaf.location_id
          order by hou.organization_id,papf.person_id,date_start desc
          • 2. Re: Pending Leaves Data
            Avinash
            Hi,

            Hope below query helps to get details of pending leaves:-

            select Papf.employee_number "Emp No"
            ,papf.full_name "Emp Name"
            ,hat.creation_date "Leave Applied"
            ,(select fu.user_name from
            apps.fnd_user fu
            where user_id = hat.last_updated_by) "Last Approver"
            ,decode(hat.status, 'E','Error', 'Y', 'Pending For Approval', 'D', 'Deleted', 'Other') "Current Status"
            ,hat.item_key "Item Key"
            ,hats.information1 "Absence Start Date"
            ,hats.information2 "Absence End Date"
            ,(select paat.name from apps.per_absence_attendance_types paat
            where absence_attendance_type_id = hats.information5) "Absence Type"
            ,hats.information6 "Absence Category"
            ,hats.information9 "Absence Status"
            from apps.hr_api_transactions hat
            ,apps.hr_api_transaction_steps hats
            ,apps.per_all_people_f papf
            where 1 = 1
            and papf.person_id = hat.selected_person_id
            and hat.transaction_id = hats.transaction_id
            and hat.status = 'Y'

            Thanks,
            Avinash
            • 3. Re: Pending Leaves Data
              882688
              Thank you guys for your help, also this code can help us in retrieving data :

              SELECT  PAPF.EMPLOYEE_NUMBER , PAPF.FULL_NAME , APPSLINK_DECODES.DECODE_ORGNIZATION (PAAF.ORGANIZATION_ID , USERENV('LANG')) ORGANIZATION
              *,HATS.INFORMATION1 START_DATE, HATS.INFORMATION2 END_DATE*
              *,PAAT.NAME , HATS.INFORMATION6 , HATS.INFORMATION8 DUARATION, HATS.INFORMATION9 LEAVE_CONFIRMED*
              FROM    hr_api_transactions hat
              *,hr_api_transaction_steps HATS*
              *,per_all_people_f papf*
              *,PER_ABSENCE_ATTENDANCE_TYPES PAAT*
              *,PER_ALL_ASSIGNMENTS_F PAAF*
              WHERE  PAPF.PERSON_ID = HAT.CREATOR_PERSON_ID
              AND    HAT.TRANSACTION_ID = HATS.TRANSACTION_ID
              AND    PAPF.PERSON_ID = PAAF.PERSON_ID
              AND    PAAT.ABSENCE_ATTENDANCE_TYPE_ID = HATS.INFORMATION5
              AND    PAPF.BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID
              AND    TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
              AND    TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
              AND    (:P_PERSON_ID IS NULL OR PAPF.PERSON_ID = :P_PERSON_ID)
              AND    (:P_LEAVE_TYPE IS NULL OR HATS.INFORMATION5 = :P_LEAVE_TYPE)
              AND    PAAT.DATE_END IS NULL
              AND    PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
              • 4. Re: Pending Leaves Data
                Giuseppe Bonavita
                I've got this query in my pocket, it might help you



                select (select name from per_Absence_Attendance_types where absence_attendance_type_id = hatv1.number_value) abs_type
                ,(select name from per_Abs_Attendance_reasons where hatv2.number_value = abs_attendance_reason_id) abs_Reas_code
                ,hatv3.date_value date_start
                ,hatv4.date_value date_end
                ,hatv5.number_value absence_days
                ,hat.transaction_id
                ,hat.item_type
                ,hat.item_key
                ,hats.transaction_Step_id
                from hr_api_transaction_steps hats
                ,hr_api_transactions hat
                ,hr_api_transaction_values hatv1
                ,hr_api_transaction_values hatv2
                ,hr_api_transaction_values hatv3
                ,hr_api_transaction_values hatv4
                ,hr_api_transaction_values hatv5
                where hat.creator_person_id = 31572
                and hat.transaction_id = hats.transaction_id
                and hats.api_name = 'HR_LOA_SS.PROCESS_API'
                and hats.transaction_step_id = hatv1.transaction_step_id
                and hats.transaction_step_id = hatv2.transaction_step_id
                and hats.transaction_step_id = hatv3.transaction_step_id
                and hats.transaction_step_id = hatv4.transaction_step_id
                and hats.transaction_step_id = hatv5.transaction_step_id
                and hatv1.name = 'P_ABSENCE_ATTENDANCE_TYPE_ID'
                and hatv2.name = 'P_ABS_ATTENDANCE_REASON_ID'
                and hatv3.name = 'P_DATE_START'
                and hatv4.name = 'P_DATE_END'
                and hatv5.name = 'P_ABSENCE_DAYS'