Using Composite Variables

[Kalin]

Hi All,

I've build a function but for some reason I can't get it to work.

The function does compile but it seems like there is a problem with the declaration of 'weekday'.

Can anyone help me ??

For ease of reading I translated the function and types to English. So any typos can be ignored.

CREATE OR REPLACE TYPE workday as object (
day date,
work number,
leave number,
over number,
compensation number);


CREATE OR REPLACE TYPE worksheet AS VARRAY (400) OF workday;

CREATE OR REPLACE function get_worksheet(p_datebegin in date, p_dateend in date, p_employee in varchar2) return worksheet
as
p_date number;
p_worksheet worksheet := worksheet();
p_monday workday;
p_tuesday workday;
p_wednesday workday;
p_thursday workday;
p_friday workday;
p_saturday workday;
p_sunday workday;
cursor c_week (i_datebegin in date,
i_dateend in date,
i_employee in varchar2)
is select distinct weeknumber,
to_number(to_char(weeknumber, 'DDD')) yearday
from uur_workhours uren
where employee = i_employee
and weeknumber between trunc(i_datebegin, 'DAY')
and trunc(i_dateend, 'DAY');
cursor c_workhours (i_employee in varchar2,
i_weeknr in date)
is select registration_holiday werk,
sum(monday) monday,
sum(tuesday) tuesday,
sum(wednesday) wednesday,
sum(thursday) thursday,
sum(friday) friday,
sum(saturday) saturday,
sum(sunday) sunday
from hour_workhours hours,
hour_activity activ
where hours.activiteit = activ.id
and employee = i_werknmr
and weeknumber = i_weeknr
group by activ.registration_holiday;
begin
for cycle in 1 .. 366 loop
p_worksheet.extend;
end loop;
for r_week in c_week(p_datebegin, p_dateend, p_employee) loop
for r_workhours in c_workhours (p_employee, r_week.weeknumber) loop
if r_workhours.work in (0,1) then
p_monday.work := r_workhours.monday;
p_tuesday.work := r_workhours.tuesday ;
p_wednesday.work := r_workhours.wednesday ;
p_thursday.work := r_workhours.thursday ;
p_friday.work := r_workhours.friday ;
p_saturday.work := r_workhours.saturday ;
p_sunday.work := r_workhours.sunday;
elsif r_workhours.work in (2) then
p_monday.leave := r_workhours.monday;
p_tuesday.leave := r_workhours.tuesday ;
p_wednesday.leave := r_workhours.wednesday ;
p_thursday.leave := r_workhours.thursday ;
p_friday.leave := r_workhours.friday ;
p_saturday.leave := r_workhours.saturday ;
p_sunday.leave := r_workhours.sunday;
elsif r_workhours.work in (3) then
p_monday.over := r_workhours.monday;
p_tuesday.over := r_workhours.tuesday ;
p_wednesday.over := r_workhours.wednesday ;
p_thursday.over := r_workhours.thursday ;
p_friday.over := r_workhours.friday ;
p_saturday.over := r_workhours.saturday ;
p_sunday.over := r_workhours.sunday;
elsif r_workhours.work in (4) then
p_monday.compensation := r_workhours.monday;
p_tuesday.compensation := r_workhours.tuesday ;
p_wednesday.compensation := r_workhours.wednesday ;
p_thursday.compensation := r_workhours.thursday ;
p_friday.compensation := r_workhours.friday ;
p_saturday.compensation := r_workhours.saturday ;
p_sunday.compensation := r_workhours.sunday;
end if;
end loop;
p_monday.day := r_week.weeknumber;
p_tuesday.day := r_week.weeknumber + 1;
p_wednesday.day := r_week.weeknumber + 2;
p_thursday.day := r_week.weeknumber + 3;
p_friday.day := r_week.weeknumber + 4;
p_saturday.day := r_week.weeknumber + 5;
p_sunday.day := r_week.weeknumber + 6;
p_worksheet(r_week.yearday) := p_monday;
p_worksheet(r_week.yearday + 1) := p_tuesday;
p_worksheet(r_week.yearday + 2) := p_wednesday;
p_worksheet(r_week.yearday + 3) := p_thursday;
p_worksheet(r_week.yearday + 4) := p_friday;
p_worksheet(r_week.yearday + 5) := p_saturday;
p_worksheet(r_week.yearday + 6) := p_sunday;
end loop;
return p_worksheet;
end;
/ Grtz,

Kalin