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;