12 mu

--letter(deptno,deptname,lastletterno)
create table letter
(
	deptno number primary key,
	deptname varchar(20) not null,
	lastletterno number
);


--insertion

insert into letter values(10,'comp',101);
insert into letter values(20,'ac',102);
insert into letter values(30,'state',103);




---------------function
create or replace function f1
(dno in number,flag in varchar,sub_ref in varchar)
return varchar
is
c varchar(20);
temp number:=0;
ch varchar2(40);
cursor c1
is
select deptname
from letter
where deptno=dno;
cursor y1
is
select to_char(sysdate,'yy-')
from dual;
cursor y2
is
select to_char(add_months(sysdate,12),'yy')
 from dual;

cursor  lno
is
select lastletterno
from letter
where deptno=dno;

begin
	if dno=NULL then
		c:=0;
	else
		open c1;
			fetch c1 into c;
			ch:=concat(ch,c);
			ch:=concat(ch,'/');
		close c1;

	end if;

	if  flag=NULL  then
		c:=0;

	elsif (flag='Y' or flag='y') then
		if(sub_ref=NULL) then
			c:=0;
		else

			if (substr(sub_ref,1,1)='b' or substr(sub_ref,1,1)='B') then
				temp:=1;
				ch:=concat(ch,substr(sub_ref,2));
				ch:=concat(ch,'/');
				open y1;
				fetch y1 into c;
				ch:=concat(ch,c);
				close y1;
				open y2;
				fetch y2 into c;
				ch:=concat(ch,c);
				ch:=concat(ch,'/');
				close y2;
			elsif (substr(sub_ref,1,1)='a' or substr(sub_ref,1,1)='A') then
				temp:=1;
				open y1;
				fetch y1 into c;
				ch:=concat(ch,c);
				close y1;
				open y2;
				fetch y2 into c;
				ch:=concat(ch,c);
				ch:=concat(ch,'/');
				close y2;
				ch:=concat(ch,substr(sub_ref,2));
				ch:=concat(ch,'/');

			else
				temp:=1;
				open y1;
				fetch y1 into c;
				ch:=concat(ch,c);
				close y1;
				open y2;
				fetch y2 into c;
				ch:=concat(ch,c);
				ch:=concat(ch,'/');
				close y2;
			end if;
		end if;
	end if;
	if temp=0 then
		ch:=concat(ch,substr(sub_ref,2));
		ch:=concat(ch,'/');
	end if;
	open lno;
	fetch lno into c;

		update letter
		set lastletterno=lastletterno+1
		where deptno=dno;
		ch:=concat(ch,c);

	close lno;
	return ch;
end f1;
/
--
begin
dbms_output.put_line(f1(30,'Y',NULL));
end;
/