Query to Extract all pending approvals from Self Service

SELECT TO_CHAR (hat.creation_date, 'DD-MON-YYYY HH:MI:SS AM')

          trans_creation_date,

       hat.transaction_id,

       hat.process_name,

       hat.transaction_ref_table,

       per.person_id,

       per.employee_number,

       per.full_name,

       hat.item_key,

       (SELECT LISTAGG (psah.user_name, CHR (10))

                  WITHIN GROUP (ORDER BY psah.user_name)

          FROM apps.pqh_ss_transaction_history psth,

               apps.pqh_ss_approval_history psah

         WHERE     psah.transaction_history_id = psth.transaction_history_id

               AND psth.item_key = hat.item_key)

          last_action_taken_by,

       (SELECT LISTAGG (psah.action, CHR (10))

                  WITHIN GROUP (ORDER BY psah.creation_date)

          FROM apps.pqh_ss_transaction_history psth,

               apps.pqh_ss_approval_history psah

         WHERE     psah.transaction_history_id = psth.transaction_history_id

               AND psth.item_key = hat.item_key)

          last_action,

       (SELECT LISTAGG (

                  TO_CHAR (psah.last_update_date,

                           'DD-MON-YYYY HH24:MI:SS AM'),

                  CHR (10))

               WITHIN GROUP (ORDER BY psah.creation_date)

          FROM apps.pqh_ss_transaction_history psth,

               apps.pqh_ss_approval_history psah

         WHERE     psah.transaction_history_id = psth.transaction_history_id

               AND psth.item_key = hat.item_key)

          last_action_date,

       (SELECT LISTAGG (wf.recipient_role, CHR (10))

                  WITHIN GROUP (ORDER BY wf.recipient_role)

          FROM wf_notifications wf

         WHERE     wf.item_key = hat.item_key

               AND wf.message_name = 'HR_EMBED_RN_NTF_APPR_MSG'

               AND wf.status = 'OPEN')

          next_approver

  FROM apps.hr_api_transactions hat,

       apps.hr_lookups hl,

       apps.per_all_people_f per

 WHERE     hl.lookup_type = 'PQH_SS_TRANSACTION_STATUS'

       AND hl.lookup_code = hat.status

       AND UPPER (hl.meaning) = 'PENDING APPROVAL'

       AND hat.selected_person_id = per.person_id

       AND TRUNC (hat.creation_date) BETWEEN per.effective_start_date

                                         AND per.effective_end_date

       AND hat.selected_person_id =

              NVL (:l_person_id, hat.selected_person_id)

       AND per.business_group_id = :l_business_group_id