declare
b varchar2(70);
d number;
e number;
f varchar2(30);
g number;
h number;
i number;
cursor xyz is select nvl(s.Stuid,0),nvl(S.Tuition,0),nvl(max(ff.sno),0),(nvl(ff.tot,0)+nvl(Arrear,0))-nvl(paid,0) bal,s.fam_id
from student s,fees ff
where s.stuid=ff.stuid(+) AND S.STATUS='PRESENT' and ff.fstat in ('N_RECEIVED','RECEIVED')
GROUP BY S.Stuid,S.Tuition,(nvl(ff.tot,0)+nvl(Arrear,0))-nvl(paid,0),s.fam_id
order by s.stuid;
begin
open xyz;
loop
fetch xyz into b,d,g,h,i;
exit when xyz%notfound;
if h > 0 then
insert into fees
(stuid,fmonth,tmonth,fyear,ftution,fstat,annual,sno,tot,arrear,bal,chl_no,fam_id)
values
(b,:fmonth,:tmonth,:year1,d*:NM,'N_RECEIVED',:ANN,g+1,(d*:NM)+nvl(:ann,0),nvl(h,0),
nvl(h,0)+nvl(:ann,0)+nvl(d,0),chal.nextval,i);
else
insert into fees (stuid,fmonth,tmonth,fyear,ftution,fstat,annual,sno,tot,arrear,bal,chl_no,fam_id)
values
(b,:fmonth,:tmonth,:year1,d*:NM,'N_RECEIVED',:ANN,g+1,d*:NM+nvl(:ann,0),0,
nvl(h,0)+nvl(:ann,0)+nvl(d,0),chal.nextval,i);
end if;
end loop;
commit;
close xyz;
next_item;
end;