ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEEE79D2E0530100007F1FF2 PER Person Full Name Y select ppx.full_name value, xxen_util.meaning(ppx.sex,'SEX',3)||' '||pptv.user_person_type description from per_people_x ppx, per_person_types_v pptv where ppx.person_type_id=pptv.person_type_id order by ppx.full_name US A3429201C088E3B1E053B46B635880B5 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 Y US PER Absence Calendar Absence Calendar by John Keymer :p_year :person_full_name 1 10 :person_full_name LOV PER Person Full Name 8E2FF36EDEEE79D2E0530100007F1FF2 Y select ppx.full_name value, xxen_util.meaning(ppx.sex,'SEX',3)||' '||pptv.user_person_type description from per_people_x ppx, per_person_types_v pptv where ppx.person_type_id=pptv.person_type_id order by ppx.full_name Y US Employee 2 20 :p_year Number 2008 Y US Year