Query to Get Self Approved Leave Transaction from History

SELECT per.employee_number,

       per.full_name,

       paaf.supervisor_id,

       TO_CHAR (TO_DATE (pssh.information1, 'YYYY-MM-DD'), 'DD-MON-YYYY')

          date_start,

       TO_CHAR (TO_DATE (pssh.information2, 'YYYY-MM-DD'), 'DD-MON-YYYY')

          date_end,

       paat.name absence_type

  FROM pqh_ss_transaction_history psth,

       pqh_ss_step_history pssh,

       per_people_x per,

       per_assignments_x paaf,

       per_absence_attendance_types paat

 WHERE     process_name = 'HR_GENERIC_APPROVAL_PRC'

       AND psth.selected_person_id = per.person_id

       AND per.business_group_id = :l_business_group_id

       AND per.person_id = paaf.person_id

       AND paaf.assignment_type = 'E'

       AND paaf.assignment_status_type_id IN (1, 2)

       AND pssh.transaction_history_id = psth.transaction_history_id

       AND paat.absence_attendance_type_id = pssh.information5

       AND EXISTS

              (SELECT 'X'

                 FROM pqh_ss_approval_history psah, fnd_user fu

                WHERE     action = 'APPROVED'

                      AND fu.user_name = psah.user_name

                      AND fu.employee_id = psth.selected_person_id

                      AND psah.transaction_item_key = psth.item_key

                      AND TRUNC (psah.creation_date) = TRUNC (SYSDATE))