select * from
(
select
papf.person_number,
ppnf.Full_name,
aatft.NAME AbsenceTypeName,
apae.PER_ABSENCE_ENTRY_ID,
(extract(month from pl_ent.start_date))||'-'||(extract(year from pl_ent.start_date)) MON_YYYY,
min(pl_ent.start_date) start_date,
max(pl_ent.end_date) end_date,
sum(ABS_UNITS) duration
--pl_ent.assignment_id,
--(extract(month from pl_ent.start_date))||'-'||(extract(year from pl_ent.start_date)) MON_YYYY
from
PER_ALL_PEOPLE_F papf ,
PER_ALL_ASSIGNMENTS_f paam,
PER_PERSON_NAMES_F ppnf ,
ANC_PER_ABS_ENTRIES apae,
ANC_ABSENCE_TYPES_VL aatft,
--ANC_ABSENCE_TYPES_F aatf,
ANC_PER_ABS_PLN_SUMM_ENT summ,
ANC_PER_ABS_PLAN_ENTRIES pl_ent
where 1=1
and papf.person_id = paam.person_id
and papf.person_id = ppnf.person_id
AND paam.person_id = apae.person_id
and apae.absence_type_id = aatft.absence_type_id
and apae.absence_type_id = aatft.absence_type_id
and apae.PER_ABSENCE_ENTRY_ID = summ.PER_ABSENCE_ENTRY_ID
and summ.PER_ABS_PLN_SUMM_ENTRY_ID = pl_ent.PER_ABS_PLN_SUMM_ENTRY_ID
and paam.assignment_id = pl_ent.assignment_id
and paam.assignment_type='E'
and paam.effective_latest_change='Y'
and ppnf.name_type='GLOBAL'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between aatft.effective_start_date and aatft.effective_end_date
--and paam.legal_entity_id = 300000115626584
--and aatft.absence_type_id IN(300000120663723,300000001955092)
/*and (
(to_number(extract(year from sysdate)) = to_number(extract(year from apae.start_date))
and to_number(extract(month from sysdate)-1) = to_number(extract(month from apae.start_date))
and to_number(extract(day from apae.start_date)) between 01 and 31 )
OR
(to_number(extract(year from sysdate)) = to_number(extract(year from apae.end_date))
and to_number(extract(month from sysdate)-1) = to_number(extract(month from apae.end_date))
and to_number(extract(day from apae.end_date)) between 01 and 31 )
)*/
and papf.person_number='5105196'
--order by papf.person_number,pl_ent.start_date
group by papf.person_number,ppnf.FULL_NAME,aatft.NAME,apae.PER_ABSENCE_ENTRY_ID,(extract(month from pl_ent.start_date))||'-'||(extract(year from pl_ent.start_date))
)
WHERE 1=1
---add more conditions here
and trunc(start_date, 'mm') = trunc(add_months(sysdate,-1), 'mm')
order by PERSON_NUMBER,start_date
