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