How to fetch reason for the particular state

Summary:

Hi Team,

I need to fetch the phase, state, and reason, I am able to fetch only phase and state, please let me know how to fetch the reason and how to map the reason tables with irc_states_vl  table and irc_phases_vl table.

Please find the below query.

select distinct ic.candidate_number,

ppnfv.full_name,

irv.requisition_number,

ipv.name as phase,

isv.name as state

from irc_requisitions_vl irv,

irc_submissions isub,

irc_phases_vl ipv,

irc_states_vl isv,

per_person_names_f_v ppnfv,

irc_candidates ic

where 1=1

AND irv.REQUISITION_ID (+) = isub.REQUISITION_ID