Open Leave Requests In - Progress Workflows Oracle SSHR

select papf.employee_number "Staff No.",

       papf.full_name "Staff Name",

       wf.status,

       paat.name "Leave Type",

       hrs.information1 "Start Date",

       hrs.information2 "End Date",

       DECODE(hrs.information5,80,hrs.information7 ||' Hrs',hrs.information8 || ' Days') "Duration",

       wf.begin_date "Applied on",

       wf.due_date "Due Date",

       wf.from_user "Last Approver",

       fnd.user_name,

       wf.to_user "Pending With",

       wf.subject,

       wf.from_role,

       wf.recipient_role,

       wf.original_recipient,

       hra.item_key      

  from wf_notifications             wf,

       hr_api_transactions          hra,

       hr_api_transaction_steps     hrs,

       per_absence_attendance_types paat,

       fnd_user                     fnd,

       per_people_x                 papf

where wf.item_key = hra.item_key

   and hrs.transaction_id = hra.transaction_id

   and paat.absence_attendance_type_id = hrs.information5

   AND fnd.employee_id = hra.creator_person_id

   AND papf.PERSON_ID = fnd.employee_id

   AND WF.MESSAGE_TYPE = 'HRSSA'

   AND WF.MESSAGE_TYPE = 'HRSSA'

   AND WF.STATUS IN ('OPEN', 'CANCELLED')

   AND Wf.subject like '%Leave%'

   AND wf.subject not like 'Application%Error%'

   AND wf.from_user not like 'SYSADMIN'

order by wf.begin_date