HR: Queries related to Absence (PENDING,APPROVED-HR_API_TRANSACTIONS)

 The below list of queries fetch the list of absences 

--To get pending leaves:

 

SELECT hat.transaction_id, hats.transaction_step_id

  FROM hr_api_transactions hat, hr_api_transaction_steps hats

 WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

   AND hat.transaction_group = 'ABSENCE_MGMT'

   AND hat.transaction_identifier = 'ABSENCES'

   AND hat.transaction_ref_id IS NOT NULL

   AND hat.status = 'Y'

   AND hat.transaction_id = hats.transaction_id

   AND hat.CREATOR_PERSON_ID = :p_person_id;

   

-- Query to get Approved Leaves :

SELECT pat.NAME absence_type, paa.date_start leave_start_date,

                paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,

                paa.abs_information2, paa.abs_information3,paa.abs_information4

           FROM per_absence_attendances paa,

                per_absence_attendance_types pat,

                per_all_people_f papf

          WHERE paa.absence_attendance_type_id =

                                                pat.absence_attendance_type_id

            AND papf.person_id = paa.person_id

            AND TRUNC (paa.date_start) BETWEEN TRUNC

                                                    (papf.effective_start_date)

                                           AND TRUNC (papf.effective_end_date)

            AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)

                                   AND TRUNC (NVL (paa.date_end,

                                                   paa.date_start

                                                  )

                                             )

            AND papf.person_id = :p_person_id;

-- List of Absence types and their additional informations 

 SELECT pat.NAME absence_type, paa.date_start leave_start_date,

                paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,

                paa.abs_information2, paa.abs_information3,paa.abs_information4

           FROM per_absence_attendances paa,

                per_absence_attendance_types pat,

                per_all_people_f papf

          WHERE paa.absence_attendance_type_id =

                                                pat.absence_attendance_type_id

            AND papf.person_id = paa.person_id

            AND TRUNC (paa.date_start) BETWEEN TRUNC

                                                    (papf.effective_start_date)

                                           AND TRUNC (papf.effective_end_date)

            AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)

                                   AND TRUNC (NVL (paa.date_end,

                                                   paa.date_start

                                                  )

                                             )

            AND papf.person_id = :p_person_id;