Query to get ABsence Types and Absence reasons

 select typ.name absence_type, rea.name absence_reason from 
ANC_ABSENCE_TYPES_VL typ,
ANC_ABSENCE_REASONS_VL rea,
ANC_ABSENCE_TYPE_REASONS_F typ_rea
where 1=1
and typ_rea.ABSENCE_TYPE_ID = typ.ABSENCE_TYPE_ID
and typ_rea.ABSENCE_REASON_ID = rea.ABSENCE_REASON_ID
and trunc(sysdate) between typ.effective_start_date and typ.effective_end_date
and trunc(sysdate) between rea.effective_start_date and rea.effective_end_date
and trunc(sysdate) between typ_rea.effective_start_date and typ_rea.effective_end_date
and typ.LEGISLATION_CODE='IN'
order by 1,2

ORC Report - Requisition phase, state and Selection Process

 select 
pr.name process_name,
req.requisition_number, 
pha.name phase_name ,
sta.name state_name,
cand.CANDIDATE_NUMBER,
pha_cand.name Subm_phase_name,
sta_cand.name Subm_state_name
from 
irc_requisitions_vl req,
IRC_PHASES_VL pha,
IRC_STATES_VL sta,
IRC_PROCESSES_B proc,
IRC_PROCESSES_B proc_tmp,
IRC_PROCESSES_VL pr,
IRC_SUBMISSIONS subm,
IRC_CANDIDATES cand,
IRC_PHASES_VL pha_cand,
IRC_STATES_VL sta_cand
where 1=1
and req.CURRENT_PHASE_ID = pha.phase_id
and req.CURRENT_STATE_ID = sta.state_id
and req.requisition_id = subm.requisition_id
and subm.PROCESS_ID = proc.process_id
and proc.PROCESS_TEMPLATE_ID = proc_tmp.process_id
and proc_tmp.process_id = pr.process_id
and subm.person_id = cand.person_id
and subm.CURRENT_PHASE_ID = pha_cand.phase_id
and subm.CURRENT_STATE_ID = sta_cand.state_id
and req.requisition_number='1234'
--and pr.name='Test Candidate selection process'