PER Absence Calendar

with calendar as (
  select dt dt,
         to_char(dt, 'DD') day,
         to_char((trunc(dt, 'IW') - trunc(trunc(dt,'MM'),'IW'))/7 + 1) 
           || '_' 
           || to_char((trunc(dt) - trunc(dt,'IW')) + 1) week_day,
         to_char(dt,'Month') day_month,
         to_char(dt, 'MM') month_no
    from (
      select to_date('0101' || :p_year, 'ddmmyyyy') + level - 1 dt
        from dual
     connect by level <= to_date('3112' || :p_year,'ddmmyyyy') - to_date('0101' || :p_year,'ddmmyyyy') + 1)
)
select day_month month, 
       mon_1, tue_1, wed_1, thu_1, fri_1, sat_1, sun_1,
       mon_2, tue_2, wed_2, thu_2, fri_2, sat_2, sun_2,
       mon_3, tue_3, wed_3, thu_3, fri_3, sat_3, sun_3,
       mon_4, tue_4, wed_4, thu_4, fri_4, sat_4, sun_4,
       mon_5, tue_5, wed_5, thu_5, fri_5, sat_5, sun_5,
       mon_6, tue_6
  from (
    select cal.day, cal.week_day, cal.day_month, cal.month_no, absence.absence_category
      from calendar cal
      left join (select paa.person_id, 
                        paat.absence_category, 
                        nvl(paa.sickness_start_date, paa.date_start) start_date, 
                        coalesce(paa.sickness_end_date,paa.date_end, trunc(sysdate)) end_date
                   from per_absence_attendances paa 
                   join per_people_x ppx on ppx.person_id = paa.person_id
                   join per_absence_attendance_types paat on paat.absence_attendance_type_id = paa.absence_attendance_type_id
                  where ppx.person_id in (select ppx.person_id from per_people_x ppx where ppx.full_name=:person_full_name)) absence
            on cal.dt between absence.start_date and absence.end_date) v
pivot (max(day || nvl2(absence_category, chr(10) || '[' || absence_category || ']',null)) 
  for week_day in ('1_1' mon_1,'1_2' tue_1,'1_3' wed_1,'1_4' thu_1,'1_5' fri_1,'1_6' sat_1,'1_7' sun_1,
                   '2_1' mon_2,'2_2' tue_2,'2_3' wed_2,'2_4' thu_2,'2_5' fri_2,'2_6' sat_2,'2_7' sun_2,
                   '3_1' mon_3,'3_2' tue_3,'3_3' wed_3,'3_4' thu_3,'3_5' fri_3,'3_6' sat_3,'3_7' sun_3,
                   '4_1' mon_4,'4_2' tue_4,'4_3' wed_4,'4_4' thu_4,'4_5' fri_4,'4_6' sat_4,'4_7' sun_4,
                   '5_1' mon_5,'5_2' tue_5,'5_3' wed_5,'5_4' thu_5,'5_5' fri_5,'5_6' sat_5,'5_7' sun_5,
                   '6_1' mon_6,'6_2' tue_6))
order by month_no